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; 

Answer 1

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;