How does autoIncrement work in NodeJs's Sequelize?

Question

Sequelize's document doesn't say a whole lot about autoIncrement. It only includes the following example:

// autoIncrement can be used to create auto_incrementing integer columns
incrementMe: { type: Sequelize.INTEGER, autoIncrement: true }

Based off of this example I have the following code:

db.define('Entries', {
    id: {
        type: Seq.INTEGER,
        autoIncrement: true,
        primaryKey: true
    },
    title: {
        type: Seq.STRING,
        allowNull: false
    },
    entry: {
        type: Seq.TEXT,
        allowNull: false
    }
}

Now when I want to create an entry I want to do something like this:

models.Entry.create({title:'first entry', entry:'yada yada yada'})

However, when I execute that code I get a database error:

Null value in column "id" violates not null constraint

I would assume Sequelize would put the integer in for me or define the database table to do that itself. Apparently not? What am I missing here to ensure that the id is automatically incremented and filled?

Thanks much.

score 31 · Accepted Answer · edited Jun 30 '20 at 16:49

31

Normally Sequelize.js adapt itself with the database. So the autoincrement attribute corresponds to a serial type in PostgreSQL.

I already worked with Sequelize and what I remember is that you don't need to specify an id as a primary key. Sequelize will do it for you.

Try to not add an id attribute and see if Sequelize will not add it for you or not.

edited Jun 30 '20 at 16:49

gvlasov

18,638
21
74
110

answered Apr 01 '13 at 11:52

Yacine Rezgui

1,771
1
18
15

sorry shifted project priorities, but i will get back to you this weekend on this. – Eric H. Apr 02 '13 at 17:39
1

this is right for my purposes, but still wondering what the purpose of the `autoIncrement` is if you have to enter it everytime. Perhaps I mis-configured. – Eric H. Apr 14 '13 at 00:05
the problem is how to give custom primarykey names ! – Rizwan Patel May 13 '16 at 10:56
You can do defaultValue: invokeGenerateCustomId() in the options – Christian4423 May 05 '17 at 17:06
@yacine I need to specify the id field as am going to use that as a foreign key to refer another table so can you tell me a way? – Kannan T Oct 03 '17 at 17:23
still doesn't work for me. says "Field 'id' doesn't have a default value", – David Okwii Feb 17 '21 at 14:36

score 13 · Answer 2 · answered Aug 30 '13 at 06:17

13

omitNull config option by default is false. Set it to true, and the id will be taken care of by Postgres:

sequelize = new Sequelize('mydb', 'postgres', null, {
  host: 'localhost',
  port: 5432,
  dialect: 'postgres',
  omitNull: true
})

Visitor = sequelize.define('visitor', {
  email: Sequelize.STRING
})

Visitor.create({email: 'foo@bar.com'})

answered Aug 30 '13 at 06:17

mxgrn

1,733
16
20

8

As per [this reported issue](https://github.com/sequelize/sequelize/issues/2813), `omitNull` should no longer be used. Use `autoIncrement: true` instead in the corresponding column at the model definition and simply don't provide a value when you do the `create()`. – Thalis K. Feb 15 '15 at 11:29
3

@ThalisK. : This does not work for me. Have the same problem as the author. – akohout Jul 14 '15 at 08:42
@mxgrn, thanks its works for me, if you dont ommit null value, postgresql will put null value inside statement. https://github.com/sequelize/sequelize/issues/925 – Agung Prasetyo Aug 17 '15 at 07:29
@akohout Have you find a solution? – Kannan T Oct 03 '17 at 17:27

score 6 · Answer 3 · edited Mar 11 '21 at 21:26

It works for me if I add the "id" field manually (in this case, Sequelize will not add it automatically) in the model like this:

Visitor = sequelize.define('visitor', {
  id: {
        type: Sequelize.INTEGER,
        primaryKey: true,
        autoIncrement: true
  },
  email: Sequelize.STRING
})

Resultant SQL command is: CREATE TABLE IF NOT EXISTS "visitor" ("id" SERIAL, "email" varchar(255)...

score 3 · Answer 4 · answered Jun 30 '20 at 17:54

3

In my case the problem was that the table definition was not updated. If there is no valuable data, try removing the table and then have Sequelize recreate that table.

answered Jun 30 '20 at 17:54

gvlasov

18,638
21
74
110

score 1 · Answer 5 · answered Jun 22 '21 at 08:53

1

In my case this was because I had specified autoincrement: true instead of autoIncrement: true.

Worth checking if you are still having problems. Tables needed dropping and rebuilding for it to work.

answered Jun 22 '21 at 08:53

meesern

2,518
27
29

score 0 · Answer 6 · answered Jul 26 '23 at 03:55

Make sure that when you created your table, you did so with the SERIAL data type for your id column, like so:

CREATE TABLE Entries (
    id SERIAL PRIMARY KEY,
    # other values...
);

My problem was that I was making id the INTEGER data type. In addition to that sin, I was trying to do so with the AUTO_INCREMENT constraint, which doesn't exist in Postgres (to my knowledge).

As has been stated above, you can also drop all your tables and remake them by utilizing sequelize.sync(), which will create the perfect tables for your model.

How does autoIncrement work in NodeJs's Sequelize?

6 Answers6