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