GeezerVison is showing github.com — Optional WHERE parameters via `$1 IS NULL OR` · Issue #200 · sqlc-dev/sqlc
Optional WHERE parameters via $1 IS NULL OR #200DescriptionCREATE 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 Reacted by kai, Aitva, ddnm102, Martin Lukasik, Lorenzo Masini, Antonio Macías Ojeda, Kyle Carberry, Ludovico Russo, Jeff Allen, mrsufgi and 15 more Activityddnm102 commented on Sep 10, 2020 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"`
}
Reacted by Antonio Macías Ojeda, imraan, Ludovico Russo, mrsufgi, Dmitrii Gridnev, Martin Desrumaux, Jared, Jøelsøn Røcha, Carlos Eduardo Ribeiro and Desalutar ludusrusso commented on May 8, 2021 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 commented on Jun 28, 2021 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 commented on Aug 28, 2021 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 commented on Feb 15, 2022 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 commented on Feb 28, 2022 @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 commented on Mar 4, 2022
@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. danielbprice commented on Mar 7, 2022 @louis77 @imraan-go since I wanted to stick with sqlc for now, I've taken to adding some bool inputs to do what I want. It's not super convenient but not terrible either:
-- name: GetThings :many
SELECT *
FROM things
WHERE
color = sqlc.arg('Color') AND
(NOT sqlc.arg('HasCreatedFrom')::bool OR (created >= sqlc.arg('CreatedFrom'))) AND
(NOT sqlc.arg('HasCreatedTo')::bool OR (created < sqlc.arg('CreatedTo')))
LIMIT sqlc.arg('Limit')
OFFSET sqlc.arg('Offset');
This is icky in my personal opinion but it would certainly be pretty easy to rip and replace (for my use case) if this gets fixed. 300 queries is a lot, my sympathies. Reacted by Louis Brauer, Jeroen, Nick Benzoni, Adam Curtis, Syamil Abdillah, omid9h, Alexander Staubo and Bartłomiej Zieliński kyleconroy commented on Jun 12, 2022 Reacted by Kingsley Ocran and Shukant Pal Reacted by Martin Desrumaux, Aitva, Mohamed Al-Rasbi, M, hariyot, Diogo Correia, Daniel Bong, omid9h, Khadim Fall, austincollinpena and 4 more akhmadnurmuhammad commented on Jul 21, 2022 Hi guys, how about optional where in arg ? any example ? omid9h commented on Jul 9, 2023
1.14.0 includes sqlc.narg which generates nullable arguments.
CREATE TABLE users ( name TEXT NOT NULL );
-- name: ListUsers :many
SELECT *
FROM users
WHERE
(name = sqlc.narg('name') OR sqlc.narg('name') IS NULL);https://play.sqlc.dev/p/b3a469bb92eace4537a8268b12722ff1d445336ad6a77b9068ecba582381f16a
hey kyle, how can I use this with COALESCE? (e.g. ... LIMIT COALESCE(sqlc.narg('limit_val'), 10)) currently this syntax returns unsupported reference type: <nil>
I've also tried something like:
LIMIT CASE
WHEN sqlc.narg('limit_val') IS NOT NULL THEN sqlc.narg('limit_val')
ELSE 10
END;danielbprice commented on Jul 9, 2023 @omid9h I have clauses like this all over my sqlc code. Is this what you mean?
https://play.sqlc.dev/p/9f0657e82b95ad3b78df722c96912b3c3ff3ed8ae6fc82097b5303f4564379f6
CREATE TABLE users ( name TEXT NOT NULL );
-- name: ListUsers :many
SELECT *
FROM users
WHERE
(name = sqlc.narg('name') OR sqlc.narg('name') IS NULL)
LIMIT COALESCE(sqlc.narg('user_limit'), 100);
(postgres, sqlc 1.19; sqlite also seemed to work; mysql gave me another error, but I'm not sure what the right syntax is there) omid9h commented on Jul 9, 2023
COALESCE
Hmm, that is weird. my code is like yours but I get the error.
-- name: ListSample :many
SELECT id,
title,
created_at,
updated_at
FROM sample
WHERE (
title ILIKE sqlc.narg('title')
OR sqlc.narg('title') IS NULL
)
AND (
created_at >= sqlc.narg('start_created_at')
OR sqlc.narg('start_created_at') IS NULL
)
AND (
created_at >= sqlc.narg('end_created_at')
OR sqlc.narg('end_created_at') IS NULL
)
ORDER BY created_at DESC OFFSET sqlc.narg('offset_val')
LIMIT COALESCE(sqlc.narg('limit_val'), 100);but both of these lines work:
LIMIT sqlc.narg('limit_val')@limit_val;sqlc.yaml:
version: "2"
sql:
- engine: "postgresql"
queries: "db/queries/"
schema: "db/migrations/"
gen:
go:
package: "db"
sql_package: "pgx/v5"
out: "db"sqlc version : v1.19.0 danielbprice commented on Jul 11, 2023 @omid9h, I tried to replicate your setup on the sqlc playground, and I had no luck getting it fire the error you mentioned.
https://play.sqlc.dev/p/4a9082b92e8aa9a5f1801a14b9282130a9c2ae1d5a5f47da40b7617c03fd65f3
Can you review that and see if I made a mistake? I had to guess the schema, translate your config to JSON, and tweak it for the filenames of the playground, but your query is the same. I assume what you are saying is that the code doesn't compile, right? I'm sorry I can't help you more! Maybe someone else will know. francisco-bodyguard commented on Jul 26, 2023 Hi @danielbprice , I have the exact same issue than @omid9h.
When trying LIMIT COALESCE(sqlc.narg('limit'), 50), I have the error unsupported reference type: <nil>.
(Postgres 14, sqlc 1.19.1, MacOS)
@omid9h, I tried to replicate your setup on the sqlc playground, and I had no luck getting it fire the error you mentioned. https://play.sqlc.dev/p/4a9082b92e8aa9a5f1801a14b9282130a9c2ae1d5a5f47da40b7617c03fd65f3
In your example, there is something very weird. As you see, the generated ListSampleParam looks like
type ListSampleParams struct {
Title pgtype.Text
StartCreatedAt pgtype.Timestamptz
EndCreatedAt pgtype.Timestamptz
OffsetVal pgtype.Int4
}It's missing LimitVal pgtype.Int4. I don't know if it's related on play.sqlc.dev environment or not. francisco-bodyguard commented on Jul 27, 2023 Reacted by Nils Van Hée, omid9h and Themis Megas sergeishilkocardmarket commented on Nov 21, 2025 SELECT *
FROM users
WHERE
(name = sqlc.narg('name') OR sqlc.narg('name') IS NULL);
now generates go name redeclared in this block, go does not compile
MetadataMetadataDevelopmentNo branches or pull requestsIssue actions
|