-
Notifications
You must be signed in to change notification settings - Fork 923
How to declare a param as nullable? #451
-
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?
Beta Was this translation helpful? Give feedback.
All reactions
-
👍 7
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 ...
Replies: 8 comments 1 reply
-
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.
Beta Was this translation helpful? Give feedback.
All reactions
-
👍 5
-
I think third is best way to solve this.
Beta Was this translation helpful? Give feedback.
All reactions
-
👍 5
-
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.
Beta Was this translation helpful? Give feedback.
All reactions
-
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
Beta Was this translation helpful? Give feedback.
All reactions
-
I learned something today
Beta Was this translation helpful? Give feedback.
All reactions
-
You and me both. I had no idea.
Beta Was this translation helpful? Give feedback.
All reactions
-
Ran into this again today. Are there any plans to support nullable => true in the future?
Beta Was this translation helpful? Give feedback.
All reactions
-
❤️ 2
-
@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 ... ) )
Beta Was this translation helpful? Give feedback.
All reactions
-
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
Beta Was this translation helpful? Give feedback.
All reactions
-
❤️ 7