Writing Migrations with Foreign Keys Using SequelizeJS
How do I create tables with foreign key relationships with one another through the Sequelize QueryInterface?
The .createTable()
method takes in a dictionary of columns. You can see the list of valid attributes in the documentation for .define()
, specifically by looking at the [attributes.column.*]
rows within the params table.
To create an attribute with a foreign key relationship, use the "references" and "referencesKey" fields:
For example, the following would create a users
table, and a user_emails
table which references the users table.
queryInterface.createTable('users', {
id: {
type: Sequelize.INTEGER,
primaryKey: true,
autoIncrement: true
}
}).then(function() {
queryInterface.createTable('user_emails', {
userId: {
type: Sequelize.INTEGER,
references: { model: 'users', key: 'id' }
}
})
});
What columns and helper tables are required by sequelize? For example, it appears that specific columns such as createdAt or updatedAt are expected.
It appears that a standard model will expect an id
, updatedAt
, and createdAt
column for each table.
queryInterface.createTable('users', {
id: {
type: Sequelize.INTEGER,
primaryKey: true,
autoIncrement: true
},
createdAt: {
type: Sequelize.DATE
},
updatedAt: {
type: Sequelize.DATE
}
}
If you set paranoid
to true
on your model, you also need a deletedAt
timestamp.
This is to create migration file for adding a column.
Here I want to add a column area_id in users table. Run command:
sequelize migration:create --name add-area_id-in-users
Once it gets executed creates a migration file timestamp-add-region_id-in-users in the migrations folder.
In the created migration file paste the below code:
'use strict';
module.exports = {
up: (queryInterface, Sequelize) => {
return Promise.all([
queryInterface.addColumn('users', 'region_id',
{
type: Sequelize.UUID,
references: {
model: 'regions',
key: 'id',
},
onUpdate: 'CASCADE',
onDelete: 'SET NULL',
defaultValue: null, after: 'can_maintain_system'
}),
]);
},
down: (queryInterface, Sequelize) => {
return Promise.all([
queryInterface.removeColumn('users', 'region_id'),
]);
}
};
Here in the users table I am going to create a column named region_id along with type and relation/foreign key/references. That's it.
I want to offer another more manual alternative because when using manual migrations and queryInterface I ran across the following problem: I had 2 files in the migration folder like so
migrations/create-project.js
migrations/create-projectType.js
because project
had column projectTypeId
it referenced projectType
, which wasnt created yet due to the order of the files and this was causing an error.
I solved it by adding a foreign key constraint after creating both tables. In my case I decided to write it inside create-projectType.js
:
queryInterface.createTable('project_type', {
// table attributes ...
})
.then(() => queryInterface.addConstraint('project', ['projectTypeId'], {
type: 'FOREIGN KEY',
name: 'FK_projectType_project', // useful if using queryInterface.removeConstraint
references: {
table: 'project_type',
field: 'id',
},
onDelete: 'no action',
onUpdate: 'no action',
}))