How do I write queries with a null constraint in pg-promise properly?

Question

When writing Postgres queries, constraints are usually written like WHERE a = $(a) or WHERE b IN $(b:csv) if you know it's a list. However, if a value is null, the constraint would have to be written WHERE x IS NULL. Is it possible to get the query to auto-format if the value is null or not?

Say I might want to find rows WHERE c = 1. If I know c is 1, I write the query like

db.oneOrNone(`SELECT * FROM blah WHERE c = $(c), { c })

But if c turns out to be null, the query would have to become ...WHERE c IS NULL.

Would it be possible to construct a general query like WHERE $(c), and it would automatically format to WHERE c = 1 if c is 1, and WHERE c IS NULL if c is set to null?

Answer 1

You can use Custom Type Formatting to help with dynamic queries:

const valueOrNull = (col, value) => ({
    rawType: true,
    toPostgres: () => pgp.as.format(`$1:name ${value === null ? 'IS NULL' : '= $2'}`,
                                    [col, value])
});

Then you can pass it in as a formatting value:

db.oneOrNone('SELECT * FROM blah WHERE $[cnd]', { cnd: valueOrNull('col', 123) })

UPDATE

Or you can use custom formatting just for the value itself:

const eqOrNull = value => ({
    rawType: true,
    toPostgres: () => pgp.as.format(`${value === null ? 'IS NULL' : '= $1'}`, value)
});

usage examples:

db.oneOrNone('SELECT * FROM blah WHERE $1:name $2', ['col', eqOrNull(123)])
//=> SELECT * FROM blah WHERE "col" = 123

db.oneOrNone('SELECT * FROM blah WHERE $1:name $2', ['col', eqOrNull(null)])
//=> SELECT * FROM blah WHERE "col" IS NULL

Note that for simplicity I didn't include check for undefined, but you most likely will do so, because undefined is internally formatted as null also.