Convert SQL Composite Primary Keys to Sequelize Indexes

Composite Primary Keys in Sequelize

When a SQL table defines a primary key spanning multiple columns, the converter generates a Sequelize model with the composite key represented in the indexes array rather than using primaryKey: true on individual fields.

SQL Definition

CREATE TABLE inventory (
  warehouse_id INTEGER NOT NULL,
  product_id INTEGER NOT NULL,
  quantity INTEGER NOT NULL DEFAULT 0,
  last_checked TIMESTAMP,
  PRIMARY KEY (warehouse_id, product_id)
);

Generated Sequelize Model

Inventory.init({
  warehouse_id: { type: DataTypes.INTEGER, allowNull: false },
  product_id: { type: DataTypes.INTEGER, allowNull: false },
  quantity: { type: DataTypes.INTEGER, allowNull: false, defaultValue: 0 },
  last_checked: { type: DataTypes.DATE, allowNull: true },
}, {
  sequelize,
  modelName: 'Inventory',
  tableName: 'inventory',
  indexes: [
    { fields: ['warehouse_id', 'product_id'], unique: true, name: 'PRIMARY' },
  ],
});

How It Differs from Single Primary Keys

With a single primary key, Sequelize uses primaryKey: true on the field. With a composite key, you need the indexes approach because Sequelize's primaryKey option only supports one field at a time. The unique index effectively enforces the composite primary key constraint.

Querying with Composite Keys

When querying models with composite keys, use findOne with a where clause:

const item = await Inventory.findOne({
  where: { warehouse_id: 1, product_id: 42 }
});

Note that findByPk() does not work with composite keys — it expects a single value.

Migration Considerations

When generating migrations (not models), Sequelize uses queryInterface.addConstraint for composite primary keys. The model definition alone does not create the constraint; you need either sync() or a migration that explicitly defines the composite key.

Junction Tables

Composite primary keys are most common in junction tables (many-to-many). If both columns reference other tables, the model is likely a through table for a belongsToMany association.