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

Question

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 ?

sdepold · Accepted Answer · 2013-01-15 07:43:36Z

20

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

edited Jan 15, 2013 at 7:43

answered Jan 15, 2013 at 7:37

sdepold

6,2262 gold badges43 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

anishek Over a year ago

Thanks that worked with the 1.5.x version, surprisingly the NPM registry npmjs.org/package/sequelize doesnt mention anything about the 1.5.0 version.

lao Over a year ago

The problem with this solution is that when you try to perform an update like this user.save({address:null}); it doesn't change the address in the DB. Is there any way to perform an update an set an attribute to null?

Erik Reppen Over a year ago

Given the rate their docs mutate, that link is long dead. Sure would like to know what's going on with this because it just started happening to me and I want to know why.

Jeff Fairley Over a year ago

@lao, this is a global setting. You can always pass a local override: user.save({address:null}, {omitNull: false})

Jeff Fairley · Accepted Answer · 2015-10-05 21:12:51Z

4

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

answered Oct 5, 2015 at 21:12

Jeff Fairley

8,3667 gold badges48 silver badges57 bronze badges

Comments

Vladimir · Accepted Answer · 2018-11-16 10:22:23Z

2

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 }
})

answered Nov 16, 2018 at 10:22

Vladimir

2054 silver badges5 bronze badges

Comments

lao · Accepted Answer · 2014-08-02 14:32:21Z

1

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

answered Aug 2, 2014 at 14:32

lao

1,69020 silver badges23 bronze badges

Collectives™ on Stack Overflow

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

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related