ID column error during insert

Question

I am getting errors when trying to insert new records into my database (Postgres) using Node.js with Objection.js as the ORM/db handler. I have followed the tutorial, but I cannot find a mention of this in their current docs.

Here are my errors:

When debugging to console: error: null value in column "id" violates not-null constraint

When receiving the error via Postman, e.g. (for searchability with search engines): error: null value in column "id" violates not-null constraint

It looks like the 'id' column is being inserted to with a 'null' value from the logged query; it should not be inserting at all because it uses a default sequence value (auto-identity field, in other SQL solutions).

How do I insert new entities?

Answer 1

It turned out that I was defaulting the id to null on the TypeScript/Javascript model, and Objection.js expects the property to not be defined.

So do one of the following:

• Don't create the property (property, and thus value, will be undefined)
• Create the property, but don't set a value (value will be undefined)
• Set the value to undefined
• delete the property

The reason it was setting it to null was because other ORMs I was trying supported setting it to null. Typeorm, in particular, allows marking a column as Primary and a Generated column (meaning it doesn't try to insert the value; the database's table definition is responsible for creating the value). Also, other non-JS ORMs also support either null or an invalid id value, such as zero, to indicate that the object does not yet exist in the table, and therefore should be inserted (again, with ways to indicate that the column is both primary and generated).