Convert SQL Primary Keys and Auto-Increment to Sequelize

Primary Keys and Auto-Increment in Sequelize

Primary keys are the foundation of every database model. The converter handles all common SQL patterns for defining primary keys and auto-incrementing columns.

SERIAL (PostgreSQL)

PostgreSQL's SERIAL is shorthand for an auto-incrementing integer with a sequence:

id SERIAL PRIMARY KEY
-- becomes -->
id: { type: DataTypes.INTEGER, primaryKey: true, autoIncrement: true, allowNull: false }

BIGSERIAL maps to DataTypes.BIGINT with the same options.

AUTO_INCREMENT (MySQL)

MySQL's AUTO_INCREMENT keyword maps directly to autoIncrement: true:

id INT AUTO_INCREMENT PRIMARY KEY
-- becomes -->
id: { type: DataTypes.INTEGER, primaryKey: true, autoIncrement: true, allowNull: false }

IDENTITY (SQL Server)

SQL Server uses IDENTITY(1,1) for auto-incrementing columns:

id INT IDENTITY(1,1) PRIMARY KEY
-- becomes -->
id: { type: DataTypes.INTEGER, primaryKey: true, autoIncrement: true, allowNull: false }

UUID Primary Keys

For UUID-based primary keys, the SQL type maps to DataTypes.UUID:

id UUID PRIMARY KEY DEFAULT gen_random_uuid()
-- becomes -->
id: { type: DataTypes.UUID, primaryKey: true, defaultValue: DataTypes.UUIDV4, allowNull: false }

Sequelize Behavior

When autoIncrement: true is set, Sequelize:

1. Omits the column from INSERT statements (lets the database assign the value)
2. Reads the generated value back after insertion
3. Sets it on the model instance automatically

In TypeScript mode, auto-increment fields are marked as optional in the CreationAttributes type since they do not need to be provided when creating a record.