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.

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.jsmigrations/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',}))

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.