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

Question

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

I have 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 on node.js with sequelize 1.4.1 version.

mapped 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 instance 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));
   })

error i get

*Executing: INSERT INTO "stillbirth" ("state","year","count","id") VALUES ('Andhra Pradesh',2004,11,NULL) RETURNING ; could not save the row {"length":110,"name":"error","severity":"ERROR","code":"23502","file":"execMain.c","line":"1359","routine":"ExecConstraints"}

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

Can someone help me as to what am i missing here ?

node.js
postgresql
sequelize.js

Answer 1

You have to instantiate Sequelize with a special flag called omitNull:

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

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

You might need to update to v1.5.x or 1.6.0-betaX

Answer 2

There is a workaround this without using omitNull.

Just do this:

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 parameter for save method you force sequelize to insert only the properties of that array omiting the id, leaving to the DB to auto create the id for you. =)