Automatic migrations are causing issues when running our app on an ECS cluster in AWS

[johndenverscar]

Issue type:

[X] question
[ ] bug report
[ ] feature request
[ ] documentation issue

Database system/driver:

[ ] cordova
[ ] mongodb
[ ] mssql
[X] mysql / mariadb
[ ] oracle
[ ] postgres
[ ] sqlite
[ ] sqljs
[ ] react-native
[ ] expo

TypeORM version:

[X] latest
[ ] @next
[ ] 0.x.x (or put your version here)

Steps to reproduce or a small repository showing the problem:

I have inherited an application running an Express service that uses TypeORM.
On app startup, the migrations are automatically run, which, for the most part, doesn't cause an issue.
However, I have a rather large migration that inserts a bunch of data and takes about 5 minutes to run.
When deploying changes to the application, it's deployed to 3 or more containers at the same time using a CICD tool and pipeline that I'm not allowed to change.
When all of the applications start up, the long-running migration causes a race condition where all three try to run the migration at the same time, causing duplicate date to be inserted into the DB.

Things I can't change:

1. The number of instances we deploy to at once. This has to be 3 or more at a single time.
2. The timing of the deployments. All containers have to be deployed simultaneously.

Is there a way I can use TypeORM such that only one of these app containers tries to run the migrations? Or do I need to write my migrations so that they are entirely idempotent?

[vlapo]

It is possible customize instance behaviour e.g. using env variables?

For example set env variable RUN_MIGRATIONS=true for only one instance and change code of your application to read variable and run migrations only if presented RUN_MIGRATIONS.

[johndenverscar]

That was an idea that we had came up with early on. Unfortunately, we can only set environment variables on a per-environment basis (DEV, QA, PROD). We have very little flexibility in the way of per-instance/per-container environment control, unfortunately.

[vlapo]

I think the only way is implement your own lock. It should be some kind of lock file shared between instances or create new table and use transactions to write some value informing all instances about running migration on another one.

It should be really dangerous implement this directly in typeorm because of complexity (e.g. migration failed but lock is not released, migration instance is terminated and so on). It could lead to a lot of bugs in future.

Second solution should be run migration using script before deploy instances.

[johndenverscar]

I think the only way is implement your own lock. ... It should be really dangerous implement this directly in typeorm because of complexity

Agreed. Thank you for your quick responses!

[vlapo]

You are welcome. I was thinking about it few month ago. But we use only one instance in final phase of our project.

[alfonmga]

@admay Can I ask you how you decided to do it finally? thanks!

[greenreign]

@vlapo Couldn't this mostly be solved by typeorm without any real risk? Consider:

1. migrations are kicked off by typeorm
2. typeorm computes a hash (say all migration names)
3. typeorm checks to make sure a record for this hash (in a migrations_lock table?) does NOT exist
   1. if it does - it quits
   2. if it does not - it creates a record for this hash with a started status
4. run migrations according to existing logic
5. update migrations_lock table with completed status

Status and timestamp could be used to recover from failure scenarios like you described. (failed to release lock etc). We don't have a way to nicely recover from those scenarios today even though.

This will be much harder to accomplish by the OP themselves. They would have to reproduce all logic that typeorm uses to discover the migration files and potentially some of the validation in order to calculate the hash. That seems like a non-starter.

Perhaps there is some middle ground where typeorm can provide hooks into the migration lifecycle with contextual information (migrations to run etc). Then something like this would be easier to implement by the OP and the rest of us. Can't imagine many real production apps running on 1 instance.

[TheRusskiy]

fyi, here's my solution for migration locks: https://www.dmitry-ishkov.com/2022/03/migration-locks-for-typeorm.html

[icoleto]

The workarround that I have been thinking about is:

• set migrationsRun: false to prevent the migrations run automatically when application starts multiple times due to multiple instances
• add an additional step on your pipeline/makefile/dockerfile... that just do the job of migration (i.e npx typeorm migration: run )

Important: take into account that this approach depends on the infrastructure you use. For example, if you add this command on your dockerfile and you have a clustered node app + multiple docker instances you will probably have the same problem, so maybe this command should be included on your deploy pipeline configuration instead which is going to be executed just one time.