Sequelize provides the primaryKey option that you can add to your Sequelize model attributes.
The primaryKey option allows you to assign the PRIMARY KEY constraint to your model columns.
Consider the following Sequelize model example:
const User = sequelize.define("User", {
user_id : {
type: Sequelize.INTEGER,
autoIncrement: true,
primaryKey: true
},
firstName: {
type: Sequelize.STRING,
},
isActive: {
type: Sequelize.BOOLEAN,
defaultValue: false,
},
},
{
timestamps: false,
});
When you sync() the above Sequelize model, the user_id column will be created as the PRIMARY KEY column of the Users table in your database.
Sequelize will generate and run the following SQL statement for MySQL database:
CREATE TABLE IF NOT EXISTS `Users` (
`user_id` INTEGER auto_increment ,
`firstName` VARCHAR(255),
`isActive` TINYINT(1) DEFAULT false,
PRIMARY KEY (`user_id`)
) ENGINE=InnoDB;
For PostgreSQL, the statement will be as shown below:
CREATE TABLE IF NOT EXISTS "Users" (
"user_id" SERIAL ,
"firstName" VARCHAR(255),
"isActive" BOOLEAN DEFAULT false,
PRIMARY KEY ("user_id")
);
When you don’t include a column with a primaryKey option in your model, then Sequelize automatically includes an id attribute into your model.
The following Sequelize model has no primaryKey option included in its attributes:
const User = sequelize.define("User", {
firstName: {
type: Sequelize.STRING,
},
isActive: {
type: Sequelize.BOOLEAN,
defaultValue: false,
},
},
{
timestamps: false,
});
When synced, Sequelize will generate the following statement. Notice how it has the id column added:
CREATE TABLE IF NOT EXISTS `Users` (
`id` INTEGER auto_increment ,
`firstName` VARCHAR(255),
`isActive` TINYINT(1) DEFAULT false,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
If your table doesn’t have a primary key column at all, then you need to use the removeAttribute() method to remove the id attribute from the model:
const User = sequelize.define("User", {
firstName: {
type: Sequelize.STRING,
},
isActive: {
type: Sequelize.BOOLEAN,
defaultValue: false,
},
},
{
timestamps: false,
});
User.removeAttribute("id");
Without removing the id attribute, Sequelize will look for the id column in your database table.
You can also add multiple primary keys to your SQL table by specifying the primaryKey option to multiple attributes.
The following Sequelize model:
const User = sequelize.define("User", {
firstName: {
type: Sequelize.STRING,
primaryKey: true,
},
lastName: {
type: Sequelize.STRING,
primaryKey: true,
},
},
{
timestamps: false,
});
Will generate the following SQL statement on sync():
CREATE TABLE IF NOT EXISTS `Users` (
`firstName` VARCHAR(255) ,
`lastName` VARCHAR(255) ,
PRIMARY KEY (`firstName`, `lastName`)
) ENGINE=InnoDB;
Now you’ve learned how to generate columns with primary keys using Sequelize. Great work! 👍
