Auto increment id with sequelize in MySQL

Question

I have the following model in NodeJS with sequelize and a MySQL database:

var Sequelize = require('sequelize');
var User = sequelize.define('user', {        
        id: {
            type: Sequelize.INTEGER,
            autoIncrement: true,
            primaryKey: true
        },
        ...
};

I am trying to add a new user to my databse with the below code:

sequelize.transaction().then(function(t) {
        User.create({/* User data without id */}, {
            transaction: t
        }).then(function() {
            t.commit();
        }).catch(function(error) {
            t.rollback();
        });
    });

After that, I am getting the next error:

Executing (47f19f7b-a02d-4d72-ba7e-d5045520fffb): START TRANSACTION;
Executing (47f19f7b-a02d-4d72-ba7e-d5045520fffb): SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Executing (47f19f7b-a02d-4d72-ba7e-d5045520fffb): SET autocommit = 1;
Executing (47f19f7b-a02d-4d72-ba7e-d5045520fffb): INSERT INTO `user` (`id`, /* next fields */) VALUES (DEFAULT, /* next values */);
Executing (47f19f7b-a02d-4d72-ba7e-d5045520fffb): ROLLBACK;

And the error message:

[SequelizeDatabaseError: ER_NO_DEFAULT_FOR_FIELD: Field 'id' doesn't have a default value]
  name: 'SequelizeDatabaseError',
  message: 'ER_NO_DEFAULT_FOR_FIELD: Field \'id\' doesn\'t have a default value'

However, if I manually set the id value, it works. It seems sequelize is trying to set a default value in the id field, instead setting an autoincrement integer. I have defined this field as autoIncrement in my database too.

How could I do this insertion? Do I have to set the id manually?

EDIT

This is my table definition:

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uid` varchar(9) NOT NULL,
  `name` varchar(20) NOT NULL,
  `email` varchar(30) DEFAULT NULL,
  `birthdate` date NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uid_UNIQUE` (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

@Genzotto Even I am facing this same issue, But I didn't understand why did you give the initial value for the primary key(autoincrement)? and How did you do it? — Rasitha A K, Jan 25 '19 at 06:08
https://stackoverflow.com/questions/4678110/how-to-reset-sequence-in-postgres-and-fill-id-column-with-new-data — Abhi_007, Aug 06 '19 at 09:54

score 59 · Accepted Answer · answered Nov 18 '15 at 09:10

You must be sure you're not even sending the id key at all.

I have done a quick minimal test and it seemed to work great:

var Sequelize = require('sequelize');
var sequelize = new Sequelize('cake3', 'root', 'root', {
    define: {
        timestamps: false
    },
});
var User = sequelize.define('user1', {        
        id: {
            type: Sequelize.INTEGER,
            autoIncrement: true,
            primaryKey: true
        },
        name: {
            type: Sequelize.STRING
        }
});

sequelize.transaction().then(function(t) {
    User.create({name:'test'}, {
        transaction: t
    }).then(function() {
        t.commit();
    }).catch(function(error) {
        console.log(error);
        t.rollback();
    });
});

Table dump:

CREATE TABLE `user1s` (
  `id` int(11) NOT NULL,
  `name` varchar(20) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
ALTER TABLE `user1s`
  ADD PRIMARY KEY (`id`);
ALTER TABLE `user1s`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=1;

Your code helped me to discover my problem. In my table definition, I setted the id column as AUTO_INCREMENT, but I didn't set its initial value (AUTO_INCREMENT=1). Now it works beautifly. Thank you very much! — Genzotto, Nov 19 '15 at 08:38
Have you checked if you have NO_AUTO_VALUE_ON_ZERO? https://github.com/sequelize/sequelize/issues/5493 — igorsf, Oct 26 '16 at 01:39

score 1 · Answer 2 · edited Nov 27 '20 at 14:39

1

In migration, add this line of code:

await queryInterface.sequelize.query("ALTER TABLE table_name AUTO_INCREMENT = 1000000;");

edited Nov 27 '20 at 14:39

Anatoly

20,799
3
28
42

answered Nov 27 '20 at 14:28

Fernando Sánchez

41
1

Please add more details how to use this line of code – Anatoly Nov 27 '20 at 14:42

Auto increment id with sequelize in MySQL

2 Answers2

Linked