Best practice for making manual database changes that are incompatible with completed migrations
After bringing up the issue with the AI bot here: https://discord.com/channels/937751382725886062/1420316663349252136
I've been trying to figure out the best course of action for running prisma migrations on a table that has been manually edited. From this discussion my understanding is that I shouldn't ever make a change to the table that doesn't make it forever backwards and forwards compatible or else I have to do a db reset every time. The need to reset my db every time A) won't work in production and B) means locally all of my test data is deleted which would take me forever to add back in especially if the schema is even slightly different (previous times I've tried to re import seed data if the data has any unused columns or tables the whole import fails instead of ignoring them).
What's the solution? Don't ever make any changes manually? Make changes that are always backwards compatible? Always be okay with doing `db reset and just manually re add the data?
Migrations have been the biggest headache so far dealing with prisma and has made me consider using a different library or method for managing my databases multiple times because it feels like every time I run npx prisma migrate dev there's a 70% chance it's going to fail or tell me to reset my database which concerns me for once it's in production.
4 Replies
You're in no rush, so we'll let a dev step in. Enjoy your coffee, or drop into
#ask-ai if you get antsy for a second opinion!Migrations are a common part of dealing with SQL databases, and I do think Prisma actually makes it quite easy, compared to other ORMs and frameworks.
Without knowing much about you, or the problem you are facing this sounds like it might be an issue of "fighting the tools", instead of using them properly.
It is entirely possible that there is a usecase that isn't well covered by migrations, but why have you had a table manually edited in the first place? Why not write a migration to handle the changes? That way you know your codebase and your actual database schema is in sync, making Prisma able to do its job.
To solve it, you could just make a migration that reflects the changes that was manually made.
But yes, generally speaking, you should probably not make manual changes.
Say you removed column
userName, you could simply remove userName from your schema, and run prisma migrate dev. This will create a new migration, that puts Prismas schema in sync with your actual database.
I am not sure if Prisma covers this edge case, as I can't say I've been in it, but theoretically it should be possible.
All that said and done, I would take a step back and figure out: why did you need to manually edit the database in the first place?
There is not necessarily anything wrong about it, its just a bit of an anti pattern (or more accurately, its just unnecessary) when working with an ORM. Especially one that is so adamant about keeping the schema in sync, as Prisma is.
At the end of the day, you should never end in a situation where running db reset regularly is a required part of your workflow. It should never happen on production either.
Hope that helps! 🙏Thanks for the detailed response. The main issue I've been having was adding row level security. I made a manual RLS migration (hand wrote the sql file idk if this is bad practice or not), then made a change to my schema that broke my RLS so I quickly manually removed my RLS and reran the new migration. It seemed like it should have worked but my migrate dev command was failing because it was just building the shadow db without the knowledge I had disabled RLS manually.
Going forward I reckon I should make all changes no matter how big or small as a prisma migration instead of manually on the db. I assumed that if the change wasn't created automatically from the
schema.prisma file I would need to do it on the db but I think it just means I need to write my own migration files. The main issue now though is this new way of working will likely have me making migrations for small things which over time can quickly pile up.Yes, you are right, that is one of the caveats of working with migrations.
But I think you can sort of see it as a changelog instead. All your migrations represent an accurate history of exactly what happened to your database.
Extrapolating a little bit that means you could effectively replay that history on any database and get a working state for your app. That's pretty cool!
That being said, it's also a somewhat solved problem.
It's entirely possible to squash migrations (Prisma has an article on it here (https://www.prisma.io/docs/orm/prisma-migrate/workflows/squashing-migrations)), meaning you can reduce the number, and its something that many do over time.
It's also, by many, considered a good practice to squash multiple migrations into one, before creating a PR. So if your flow is branching from and back into
main, you would only have 1 migration coming into main at a time, because you squash the migrations on your development branches. Thus it takes longer for the files to build up.
But yes, in essense you are correct, they can quickly pile up.
I wouldn't know exactly what an RLS migration is, but I know it is totally possible to manually write SQL files with Prisma, so as long as you keep your database changes in migration files, under the Prisma folder, you should be totally fine to keep doing what you're doing. You can get the best of both worlds 😄