Optional WHERE parameters via `$1 IS NULL OR`

[maxhawkins]

CREATE TABLE users ( name TEXT NOT NULL );

-- name: ListUsers :many
SELECT *
FROM users
WHERE
  ($1 IS NULL OR name = $1);

Sometimes I do something like this to optionally query by a field. Since there's no type associated with $1 IS NULL sqlc fails: nodes.ResTarget has nil name.

I can fix this by re-ordering the WHERE clause:

SELECT *
FROM users
WHERE
  (name = $1 OR $1 IS NULL);

But now the generated type is string instead of sql.NullString

[ddnm102]

Hi!
Related to this issue, how can i generate a pointer type for parameters?

-- schema.sql

CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    full_name TEXT NOT NULL
);

-- queries.sql

-- name: UserList :many
SELECT * FROM users
WHERE 
(email = $1 or $1 IS NULL) AND
(full_name = $2 or $2 IS NULL) ;

Currently, SQLC generated:

type UserListRequest struct {
  Email     string  `json:"email"`
  FullName  string  `json:"full_name"`
}

But, Because we want Email, FullName are options (when user do not provide -> will skip it in SQL statement), so, we need:

type UserListRequest struct {
  Email     *string  `json:"email"`
  FullName  *string  `json:"full_name"`
}

[ludusrusso]

You can force type by casting it with something like $1::varchar but I did not find a way to make the input value optional! Did you solved the issue?

[mrsufgi]

Seems like a pressing issue, optional parameters is something really common.

Changing the model (schema) to "nullable" with a default is not a viable option (forcing sql.NullXXX) and I'm not sure how overrides can help, since the input is not a part of the schema, we need a way to create nullable inputs ⭕

I would be happy to submit a PR but I would appritiate some pointers to where to start <3

[kyleconroy]

I'm going to split this into two separate issues. The original query in this issue is still causing sqlc generate to fail with the following error message:

unsupported reference type: <nil>

The other part of this issue deals with marking parameters as nullable. I've created a new issue (#1155) to track this where we can discuss possible solutions.

[danielbprice]

This has become a real bummer for me. I am trying to port some boring "take REST request and turn it into SQL query" code to Go and I would like to use sqlc; the REST API has optional inputs which become clauses on the WHERE clause, just like the above example. I feel really stuck at this point, and I think I will have to abandon using sqlc in this project as a result. I'm back to writing boilerplate again. Maybe someone else has a clever workaround I've missed?

[louis77]

@danielbprice I'm stuck at exactly the same issue. I found no solution to this and it looks like this is because sqlc completely relies on a 1:1 mapping between the schema definition and the query, which is, more often than not, not reality. I have about 300 different queries and more than half of them I'm unable to map it with sqlc. So it's time to say hello to good old sqlboiler.

[imraan-go]

@danielbprice I'm stuck at exactly the same issue. I found no solution to this and it looks like this is because sqlc completely relies on a 1:1 mapping between the schema definition and the query, which is, more often than not, not reality. I have about 300 different queries and more than half of them I'm unable to map it with sqlc. So it's time to say hello to good old sqlboiler.

Yep. I had to abandon using this. Just use some orm like gorm or bun.