Migration production safety questions (concurrency, rollbacks, out-of-order application)
Hi, Community!
New to Drizzle, investigating it as a candidate for next larger quite heavy production project, decided to test something new instead of battle-tested
knex to get better TS support 🙂 DB is PostgreSQL, but I think it's not essential
So have few questions which come from experience 🙂 I could spend time indo digging into code, but decided to ask first if someone is aware of behavioural details here.
1. Concurrent migration application
Usually when you number of pods, you may have in theory multiple migration invocations.
As I've checked SQL executed by Drizzle, it searches for last applied transaction, then applies next one in transaction together with inserting. I see no kind of blocking (e.g. exclusive lock on migration table) to ensure transaction can be applied only once.
__drizzle_migrations has ID serial only, so in case of consurrent invocations code will be applied twice - and may be inserted twise. For sure most of the case it can be not the problem since DDL will probably fail. But if you write custom migration with some data mutations - that may backfire easily.
Did I get it right, or there's some mechanics to protect from concurrent migrations?
2. Rollback migration code (node SQL migration itself)
Case for reliable prod - you do migrations, you roll code, you see problem, you rollback making your old code working on new already migrated DB schema.
In that case migration can be called from older code (e.g. container) - and see new unfamiliar migration already applied. How will Drizzle operate in that case? Will it fail, go nuts?
3. Prevent out-of-order migration
That's comes from development flow:
- You're writing develop code and prepare migration
- You've deployed it to server, did some tests
- You do rebase, and now there's new migration file
- On deploying to test server turns out one migration got skipped
Will Drizzle detect that kind of a problem on it's own? (making it short to match limit 🙂 )3 Replies
So far to me looks like those are not cases supported by Drizzle reliably, so I'm thinking about writing custom migrator to apply migrations.
But looking for some guidance here since could miss a thing.
Hey @Anton Sidelnikov did you found out something?
Hi David. I was lazy enough to spend time on actual deep tests really (not a blocker for me), but I think since migrator is a separate tool in drizzle, I would just write tool to do in proper way, it's not that hard.
Gonna be for tool postgres. Ping you if published?