How to declare a param as nullable?

[maxhawkins]

If I have this simple table with some non-nullable columns

CREATE TABLE users (
  id BIGINT PRIMARY KEY,
  email TEXT NOT NULL,
);

When you reference those columns sqlc returns a non-nullable param

-- name: GetByID
SELECT * FROM users WHERE id = $1

==>
func (q *Queries) GetByID(ctx context.Context, id int64) (User, error)

But sometimes you want the param to be nullable:

SELECT *
FROM foo
WHERE
  id = @user_id_if_set
  OR email = @email_if_set

All these params should be nullable so you can set either of them and search either by id or email.

Is it possible to make the type of a parameter nullable for just one query?

[kyleconroy]

Is it possible to make the type of a parameter nullable for just one query?

Not currently. This goes back to #210, in which we discussed that all SQL parameters can be NULL. It's probably too late to change the existing default behavior.

There are a few options we can consider. One would be a global setting emit_nullable_params, which would make all parameters null. My guess is that this wouldn't be very helpful, as you're looking for control at the query level.

Another option would be to fix #133 and properly support NULLIF. You could then write

SELECT *
FROM foo
WHERE
  id = NULLIF(@user_id, '')
  OR email = NULLIF(@email, '')

This probably works for your query, but there are queries in the wild that will want to differentiate between the empty string and NULL.

Third, we could add an optional named parameter to the sqlc.arg function.

SELECT *
FROM foo
WHERE
  id = sqlc.arg('user_id', nullable => true)
  OR email = sqlc.arg('email', nullable => true)

I'm not set on the nullable argument name. Any suggestions would be welcome.

[imraan-go]

I think third is best way to solve this.

[maxhawkins]

Supporting NULLIF would be a fine intermediary step.

If there was some throwaway condition I could add to the query to indicate nullability that would fix the problem for me, albeit in a hacky way. Adding a NULLIF like this would be a no-op:

SELECT *
FROM foo
WHERE
  id = NULLIF(@user_id_if_set, NULL)
  OR email = NULLIF(@email_if_set, NULL)

The sqlc.arg thing seems like a better solution for the long term.

Would the Ruby-style hash params (=>) parse in a standard Postgres parser? Eventually I'd like to run my queries through a pretty-printer on save, so it would be convenient if the syntax was compatible.

[kyleconroy]

Would the Ruby-style hash params (=>) parse in a standard Postgres parser?

Yep! https://www.postgresql.org/docs/current/sql-syntax-calling-funcs.html#SQL-SYNTAX-CALLING-FUNCS-NAMED

[maxhawkins]

I learned something today

[kyleconroy]

You and me both. I had no idea.

[maxhawkins]

Ran into this again today. Are there any plans to support nullable => true in the future?

[mrsufgi]

@kyleconroy sounds like going for a sqlc.arg('email', nullable => true) is the robust way to go, as it doesn't force you into changing your SQL to meet sqlc capabilities. (by using NULLIF, COALESCE etc).

I would be happy to contribute my time and start working on a solution. optional parameters is really common in CRUD operations (ignore limit if not set, search resource by (x OR y OR ... ) )

[micklove]

For future readers, was able to resolve a similar issue using the sqlc.narg feature, 1.14.0 release I believe,
https://docs.sqlc.dev/en/stable/howto/named_parameters.html#nullable-parameters