CODEWITHSUNDEEP
node.jspostgresqlsequelize.js
anishek
anishek

Reputation: 1675

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 ?

Upvotes: 11

Views: 14528

Answers (4)

Vladimir
Vladimir

Reputation: 205

It is also possible to define which attributes can be set via the create method. Using that would for example allow you to restrict the User model to set only a username and an address but not an admin flag:

User.create({ username: 'barfooz', isAdmin: true }, { fields: [ 'username' ] }).then(user => {
  // let's assume the default of isAdmin is false:
  console.log(user.get({
    plain: true
  })) // => { username: 'barfooz', isAdmin: false }
})

Upvotes: 2

Jeff Fairley
Jeff Fairley

Reputation: 8314

To expand on the answer from sdepold, as he recommended, you can omitNull to prevent sequelize from adding null values to the generated SQL. In general, this is good, and it also allows you to perform partial updates.

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

There is one caveat, though. How do you set a column to null if that's legitimately what you want to do?? The answer is that you can pass omitNull as part of your save.

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

OR

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

Upvotes: 4

lao
lao

Reputation: 1680

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. =)

Upvotes: 1

sdepold
sdepold

Reputation: 6241

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

Upvotes: 20

Related Questions