Error: Undefined values are not allowed - Undefined should map to Null

[e3dio]

Postgres.js won't accept undefined as values in tagged template queries since it becomes ambiguous what to do with the value. If you want to set something to null, use null explicitly.

Undefined values throwing error and preventing successful query is inconvenient and unintuitive to me, if I pass a field with an undefined value I expect it to act as NULL, it seems that would make sense to treat it that way? Can we at least have option to map undefined to NULL so I don't need to check every field? I can't think of a case where it's better to throw error instead of act as NULL. It breaks queries where undefined could be a possible value. undefined most clearly maps to the NULL sql value so I think it should do this automatically

// undefined fields in object get inserted as NULL

sql`INSERT INTO table ${sql(object)}`

https://github.com/porsager/postgres/blob/master/cjs/src/types.js#L79

[porsager]

Is it a big inconvenience having to do someVar || null or someVar ?? null ?

Or is this with dynamic input - eg update x set ${ sql({ someVar: undefined }) } ?

I don't think I'm against adding eg. an undefinedAsNull: true option, but some convincing might help? ☺️

[porsager]

Alright, I'm convinced my choice for this way of handling undefined was still the right choice, but I'll be happy to add an option to change the default. Do you have a better idea for the name than undefinedAsNull?

[e3dio]

That name seems best to me, at least adding the option would help, undefined == null is a common mapping and I think should be default over error but if undefinedAsNull is option I will use it

[porsager]

It's added now and can be used like this in options now { transform: { undefined: null } } . Will be included with the next release

[aaronyo]

FWIW, I hit this same issue when dealing with multi row "dynamic" inserts.

When specifying the insert manually, it was indeed not such a big inconvenience to replace with null. But taking advantage of the dynamic sql feels a bit different.

The standard I like for my API and TypeScript definitions is to have optional parameters, rather than <type> | null parameters. So this does mean I have to replace undefined when writing to the DB, or use your new "transform" option.

Since I'm not requiring nulls elsewhere in my stack, I don't think it make sense to do so at the DB layer... I prefer to keep things thin and consistent, rather than having transformation between layers.

Anyway, I think what the default should be is a-whole-nother question. I'm happy with the transform config you added.

[Minigugus]

@aaronyo Do you suggest using the DEFAULT keyword in dynamic insert, when the transform option is missing for instance (kind of implicit { transform: { undefined: sql`DEFAULT` } })? I don't see any other solution for multi row inserts, as the columns list must be the same for all rows

[tonyxiao]

There is no easy way to differentiate between a sql NULL vs jsonb null as is. Both would end up mapping to null in the return value. See the table below for example. data column is of type jsonb

What would be the recommended way to handle this in the current paradigm?