Set Foreign Key Constraints when creating tables with migrations Sequelize

Question

I normally use migrations in my NodeJS projects for creating tables. I don't use sync({ force: true }). So, each time I need a change in DB schema, I go with a migration. This way, I can keep exact scenario as in production.

My question is how to add foreign key constraints when creating a table with migration. The problem is that I then have to create migrations in a certain order. Because, let's say I am creating these tables.

1) users - id, name, email, password

2) posts - id, title, content, user_id, category_id

3) categories - id, name

Here, if I run the migrations in this order, I get an error.

ERROR: Cannot add foreign key constraint

It's because, categories table has not yet been created by the time posts table migration is running.

So, what options do I have here.

1. Create migrations in the correct order so they will run in the same order.

2. Add foreign key columns later with different migrations (just for adding a column).

3. Or use sync({ force: true }) so, tables will be created when adding a column in models and adding associations.

4. Or any other solution

Could you please explain what should I do here..

Thanks in advance.

Answer 1

The first case is the most reliable and easy to maintain because after each migration your structure and data are in consistent state.

The second case can be better then the first one only if you wish to insert a huge amount of data in both tables so that way you save time because records will be inserted much more faster without foreign keys.

For first and second cases you have fine-grained control over all incremental structure changes and ability to rollback some migrations as well as to deploy different versions of your app and DB.

The third case will be suitable for small projects where a DB structure changes very rarely and when it's important to deploy and run an app and a DB fast and without thinking about migrations.