Writing Migrations with Foreign Keys Using SequelizeJS

Question

The Background

I'm building a project with SequelizeJS, a popular ORM for NodeJS. When designing a schema, there appears to be two tactics:

1. Create model code and use the .sync() function to automatically generate tables for your models.
2. Create model code and write manual migrations using QueryInterface and umzug.

My understanding is that #1 is better for rapid prototyping, but that #2 is a best practice for projects that are expected to evolve over time and where production data needs to be able to survive migrations.

This question pertains to tactic #2.

The Question(s)

My tables have relationships which must be reflected through foreign keys.

• How do I create tables with foreign key relationships with one another through the Sequelize QueryInterface?

• What columns and helper tables are required by Sequelize? For example, it appears that specific columns such as createdAt or updatedAt are expected.

Answer 1

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.

Answer 2

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

Answer 3

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.

Answer 4

So first one goes as the below solution and the second question: you need not explicitly mention createdAt and updatedAt because they are generated by the Sequelize for you.

The solution is:

queryInterface.createTable('Images', {

  //...

}).then(

  return queryInterface.addConstraint('Images', ['postId'], {

    type: 'foreign key',

    name: 'custom_fkey_images',

    references: { //Required field

      table: 'Posts',

      field: 'id'

    },

    onDelete: 'cascade',

    onUpdate: 'cascade'

  })
)