Associations in Sequelize migrations

Question

My app currently uses the Sequelize sync() method to create the database, and I want to change it to use the migrations system.

One of my model has belongsTo() associations with other models, and I don't really know how to make the initial migration code for these associations.

Do I have to manually create the foreign key with SQL queries, or is there some methods available?

Answer 1

Case 1: Database initialization

If your purpose is to add relations during initialization of database structure it is better to just use sync method instead of manually adding them using migrations. If your models are properly designed and have relations defined, they will be created automatically during execution of sync method.

Take a look at sequelize express example. In models directory you have three files:

• index.js - which includes all models
• task.js - task model
• user.js - user model

Look at task.js content, starting from line 7 the following code creates a relation between User and Task models:

classMethods: {
  associate: function(models) {
    Task.belongsTo(models.User, {
      onDelete: "CASCADE",
      foreignKey: {
        allowNull: false
      }
    });
  }
}

If you correctly prepare your relations in model files, sync will create the foreign keys for you. Migrations aren't necessary in this case.

I encourage you to read the whole express-example readme.md and browse repository files to see how the things work with express and sequelize.

Case 2: Database structure migration

In case you already have some data which you want to keep, you need to use migration script, because the only way for sync to restructure your database is to destroy it completely alongside with all its data.

You can read about basic migrations in the sequelize docs. Unfortunately docs do not cover creating a relation. Let's assume you want to create the following relation: User belongs to Group. To create column on the user side of relation, you may use addColumn method.

queryInterface.addColumn(
  'user',
  'group_id',
  {
    type: Sequelize.INTEGER,
    allowNull: true
  }
)

Unfortunately there isn't a nice function (yet) to create the foreign key constraint for you, but you can do it manually using sequelize query method. Postgresql example:

queryInterface.sequelize.query("ALTER TABLE user
  ADD CONSTRAINT user_group_id_fkey FOREIGN KEY (group_id)
  REFERENCES group (id) MATCH SIMPLE
  ON UPDATE CASCADE ON DELETE CASCADE;");

Edit: Added database structure migration case

Answer 2

Adding this as an answer instead of a comment (not enough rep) for @aryeh-armon answer above. It's the table name that you need to make sure exists rather than the model name. i.e. if your model is named Job and your db table is named Jobs then the migration would look look like this instead.

jobId: {
type: Sequelize.INTEGER,
references: {
    model: "Jobs",
    key: "id"
  }
},

Answer 3

After lots of searching,I found a couple of blog posts explaining what I wanted to do.

Apparently it's not really the common way to do it, but it seems more logical to me. If you want to use only the migrations, you have to use SQL queries to create the initial migration.

Here are the posts : the first one, inspired by this one.

But anyway, I think that ezrepotein is right about creating the initial database with sync, and then migrate. It seems easier than using umzug and only use migrations.

Answer 4

you can add references to the migrations

Exmaple:

user_id: {
    type: Sequelize.BIGINT,
    references: {
        model: "users",
        key: "id"
    }
},

Just make sure the model you are referencing exists.