My database takes are spicy but it surely was overdue. Migration recordsdata are dangerous follow. We deserve higher.
#t3stack #fullstack #database
Discuss that impressed this: https://archive.fosdem.org/2022/schedule/occasion/relational_model_dev/?utm_source=pocket_reader
ALL MY VIDEOS ARE POSTED EARLY ON PATREON https://www.patreon.com/t3dotgg
All the things else (Twitch, Twitter, Discord & my weblog): https://t3.gg/hyperlinks
S/O Mir for the superior edit 🙏
supply
1) That's as much a problem as resolving merge conflicts. 2) You can do non-rollback-able in-band updates on application startup to minimize the chance your service and DB are running mismatched versions. With some clever DevOps it's possible to enable rollbacks. Or you can use feature flags. Keep doing migration files, it's one of the best ways to handle this without paying for 10 subscription services for your CI/CD toolkit.
Yeah, it requires deprecation of fields. Utilize the `@ignore` directive in Prisma.
This is where he started growing the moustache, guys.
"Obnoxious"
Solution never branch 🙂
Summary If you have to apply a migration apply it this way to avoid downtime:
You have to write three sql queries instead of writing one.
1) The first one is to write a query to add a new column renamed one
2) Second one is to add and copy the data from previous column to new one
3) The third one is to remove the old column when all clients have the new renamed column being used.
Regards
https://github.com/bilalmohib
But a question is does this happen in drizzle orm the above case or we shouldnt use drizzle orm at all.
I thought I knew what a schema was but I guess I do not… I change my schema using ALTER TABLE. But the existence of a schema definition doesn't explain how to transform an old schema into the new. That's what migrations are… I guess I'm missing the point.
I'm confused, are you advocating against manually written migration files, or against migration files alltogether? In the work-flow you describe at time 4:52, the system creates a migration file for you, right? (at least I hope that this is what happens, so that you have a way to inspect what changes to the database are about to be made). If you consider this approach to be sound, then can you explain once more what you mean by "we don't need migrations anymore"?
The issues you raise regarding conflicting changes in different MRs can be resolved with good CI/CD and automated testing. DB migrations have been around for 30 years because they work – providing you know the dangers and how to mitigate them.
Or use Django?
I believe you may be not using best/expected practices when using migrations, so the problems listed here seem like a big stretch.
I’ve seen migrations in small projects to huge monoliths with multiple teams and these problems simply don’t exist given that people know what they are doing. Seems like you have an HR problem instead.
What's an Aunty Pattern?
I would argue that what you're doing in PlanetScale is still a migration under the hood. You just have them handle the coordination against the database. A schema migration is just going from one schema state to another. Adding more steps doesn't really change that concept.
I also struggle to see how this would work with decentralized database setups, such as products deployed on-prem, which is still a very real scenario in many instances. Especially if your customers are government institutions. Then you don't have a "production database", then you might have 20 different versions running all over the world and on different hardware. Our company needs to support customer hosted on-prem deployments on customer hardware and we support 4 database dialects. If we just stopped doing migrations, we wouldn't have a product.
Sure, it's great to assess your needs and pick the most intelligent solution for your situation but proclaiming that we don't need migrations anymore seems like a very narrow minded hot take.
SSDT BOIS!
Well for 1:22 Django migrations system actually handles that pretty well – it will force you to redo migration files if you merge two parrallel branches that affects the same tables. And as well it stores all the history and state.
And you can as well write custom (python) migration code to handle dramatic changes without downtime
Rebase solves so many issues. Git merge is beta
but how would you do this for software that is self hosted by lots of people? How can you provide updates for them without migrations? like for example open source forum software that is self hosted
As a senior data engineer I endorse this message whole-heartedly.
Migrations are horrible, don't do them, especially not automatically with ORMs.
You should separate your database logic from the web server and frontend.
Use a Git repository specifically for your database, and make the changes to your schemas there, with one file dedicated to each table/view/stored procedure.
Important Insertions/deletions/truncations can also be tracked there.
This way, if you have to deploy your website somewhere else you have your whole structure in a repo and you can rerun all the files to recreate the database.
Declarative schemas ftw
Cus kiiling dbs is fun! 🥸
All those problems have solutions or mitigations.
Running migrations in filename order (ie timestamp order) is a terrible design. It's better to have a master file that references all the migration files. That way the migration file will be run in the same order as commits. liquibase works this way, as do some others. I never had this kind of issue with liquibase.
Your CI/CD should be running migrations on a test database. Also, individual developers should each have a copy of the database locally with migrations running after each git pull. Any migration should have been run many times before it ever gets to production. This should find many types of migration issues.
There's a github project that will create a set of views and triggers to make make a new schema that behaves like the prior version of your schema I don't know if I'd trust it at scale, however.
In a past job, we would effectively fork our database during migrations. The system ran 100K+ db transactions/hour during peak (11:30am). We had a master + read replicas. We would do a deployment every day at lowest usage, at 4am. We warned users beforehand with a notification, and submit buttons were disabled. At the start of the migrations, we disabled replication, disabled mutations in the app, and applied migrations to master. This allowed the app to run and for reads to continue working during the migration. At the end of the migrations we'd re-enable replication, restart the app with mutations (re)enabled, and make the read replicas unavailable until they had caught up (master would be considered a read replica until then). I can't say it had zero affect on users, but it was negligible.
The out of order thing can be worked around with hashed migrations.
The migration application and the whole "downtime" thing is definitely one of the trickier scenarios. If I have to include data mutations that will cause breaking changes I apply it in stages, so nothing breaks. Having migration files for this does provide good documentation for changes over time however which is great.
What is the benefit of db push over the migrations? In prismas case? Prisma scheme doesn't allow all possible things you'd want on the db, like checks for nonnegative, so you'd need to write that in SQL…
Thank you for this. I have been doing db push for a while now and was thinking about using migration files. I think I'll stick with db push for now 😸
Isn't it easy enough for the migration thing to have a file that points to the latest migration? So that would conflict when two PRs are being merged. That's something I've seen. I think Django does that automatically in how their migrations work.