Broken query when parametized query is undefined
Postgres dialect generates broken query when one of the parametized query is undefined
generates
15 Replies
https://node-postgres.com/features/queries#parameterized-query
node-pg convert both null and undefined to null
This is not how drizzle treats undefined
In Drizzle, undefined and null have very different meanings
For this you can just do:
No worries, just checking if there is any other way to do it more elegantly rather than casting to null or conditionally checking everytime
Do you have more info on how drizzle treats undefined and null very differently?
undefined is ignored, while null is an actual valid value that could be inserted/selected in a database
For example, if you have column with a default value, say
createdAt, if you insert null, an actual null will be inserted to the database
but if you pass undefined, then it will be ignored and the default value will be inserted to the dbI get it that undefined is converted to
default in ORM queries, but when using sql`` templating, is undefined ignored for any good reason? Sorry if im missing something
both node-pg (https://node-postgres.com/features/queries#parameterized-query) and postgres.js (https://github.com/porsager/postgres#transform-undefined-values) allows transformation of undefined values in parametized query while drizzle totally omit undefined value in paramsDrizzle also actively ignores undefined in
select cases to allow patterns like passing different dynamic filters to a select.
You could do select * from users where id = ${userId}${deletedUser ? ' and deleted = true' : undefined};
Do you know if those drivers have patterns like this?I only used node-pg, but we have to do query and params separatedly
In any case, if you think this would be a good feature to have, please add an issue in the GH repo
I dont think this works in drizzle
This works
select * from users where id = ${userId}${deletedUser ? sql.raw(' and deleted = true') : undefined}; since we cannot parametized the 2ndYep, but you get the idea
Yeah it makes sense to ignore undefined if dynamic filters are widely used
Casting to null would cause too many troubles
Actually, now that I think about it, the query builders internally rely on this to build the query
Modifying undefined at this point in time is a huge breaking change I supposed
Would be cool to have something that can transform undefined to null like in postgres.js, maybe at sql templating level instead of driver level, something like inlineParams?
Just to avoid doing
undefined ?? null? IDKJust to avoid accidentally breaking queries that uses undefined, and for people migrating from other drivers where undefined just works as params.
Thanks for the help 🙌