How to prevent Sequelize from inserting NULL for primary keys using Postgres

Question

I created a table in postgresql 9

create table stillbirth(id serial primary key, state varchar(100), count int not null, year int not null); 

trying to write a sample in node.js with sequelize version 1.4.1.

matched the above table as

 var StillBirth = sequelize.define('stillbirth', { id: {type: Sequelize.INTEGER, primaryKey: true, autoIncrement: true}, state: Sequelize.STRING, year: Sequelize.INTEGER, count: Sequelize.INTEGER }, {timestamps: false, freezeTableName: true}); 

Now when I try to create a new copy of Stillbirth and save it, I get errors.

/ ** new instance create code ** /

 StillBirth .build({state: objs[j].state, year: objs[j].year, count: objs[j].count}) .save() .error(function(row){ console.log('could not save the row ' + JSON.stringify(row)); }) .success(function(row){ console.log('successfully saved ' + JSON.stringify(row)); }) 

the error i get

* Fulfillment: INSERT INTO "dead birth" ("state", "year", "account", "id") VALUES ("Andhra Pradesh", 2004,11, NULL) RETURN; Could not save line {"length": 110, "name": "error", "severity": "ERROR", "code": "23502", "file": "execMain.c", "line": " +1359 "," normal ":" ExecConstraints "}

If you look at the sql that generates it, it puts null for the primary key, which ideally should be generated by db.

Can someone help me with what I do not see here?

Answer 1

You need to create a Sequelize using a special flag named omitNull :

 var sequelize = new Sequelize('db', 'user', 'pw', { omitNull: true }) 

This will disable inserting undefined values as NULL . http://sequelizejs.com/#usage-options

You may need to upgrade v1.5.x or 1.6.0-betaX

Answer 2

There is a workaround without using omitNull.

Just do the following:

 StillBirth .build({state: objs[j].state, year: objs[j].year, count: objs[j].count}) .save(['state','year','count']) .error(function(row){ console.log('could not save the row ' + JSON.stringify(row)); }) .success(function(row){ console.log('successfully saved ' + JSON.stringify(row)); }) 

By sending an array of properties as a parameter to the save method, you force secelize to insert only the properties of this array that omit id, leaving the database to automatically create an identifier for you. =)

Answer 3

To extend the answer from sdepold , as he recommended, you can omitNull to prevent the continuation from adding null values ​​to the generated SQL. All in all, this is good, and it also allows for partial updates.

 var sequelize = new Sequelize('db', 'user', 'pw', { omitNull: true }) 

However, there is one caveat. How to set a column to null , if it is legal, what do you want to do? The answer is that you can pass omitNull as part of your persistence.

 user.address = null; user.save({omitNull: false}); 

OR

 user.update({address: null}, {omitNull: false});