Sequelize AUTO INCREMENT option for tables

To add the AUTO_INCREMENT attribute into the columns of your tables with Sequelize, you need to add the autoIncrement attribute into your Sequelize Model definition.

For example, suppose you want to add an auto increment id column into the Users table.

You can create the User model in your JavaScript code with the following definitions:

const User = sequelize.define("User", {
  id : {
    type: Sequelize.INTEGER,
    autoIncrement: true,
    primaryKey: true
  },
  firstName: {
    type: Sequelize.STRING,
  },
  isActive: {
    type: Sequelize.BOOLEAN,
    defaultValue: false,
  },
},
{
  timestamps: false,
});

When you call the sync() method from the above model, Sequelize will generate and run the following SQL statement:

CREATE TABLE IF NOT EXISTS `Users` (
  `id` INTEGER auto_increment , 
  `firstName` VARCHAR(255), 
  `isActive` TINYINT(1) DEFAULT false, 
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

In the latest Sequelize version, an id column above will be added even when you don’t specify it in the model.

You can try omitting the id column from the User model:

const User = sequelize.define("User", {
  firstName: {
    type: Sequelize.STRING,
  },
  isActive: {
    type: Sequelize.BOOLEAN,
    defaultValue: false,
  },
},
{
  timestamps: false,
});

The generated SQL statement should be the same.

Please note that SQL databases only allow one column with AUTO_INCREMENT attribute applied and it must be a primary key.

Defining and syncing a Sequelize Model with two autoIncrement columns will cause Sequelize to throw the following error:

Error: Invalid Instance definition. Only one autoincrement field allowed.

The autoIncrement option will get converted to SERIAL when you connect to PostgreSQL database.

Now you’ve learned about the autoIncrement option in the Sequelize library. Nice work! 👍