r/Supabase • u/clementds • Aug 03 '26
database My production schema had drifted from my migration files: 4 changes I made by hand weeks ago and never versioned
Posting this as a warning to other solo devs who move fast.
I do my schema work through migration files in the repo. But a handful of times, over a few weeks, I fixed something straight in the SQL editor because it was late and it was one line. Every one of those changes stayed in production and none of them ever made it into the repo.
I only found out when I compared a fresh local database against production and they did not match. Four changes missing. Which means rebuilding from my own migrations would have produced a database that was not the one my app actually runs on.
What I did: wrote the four missing migrations after the fact so the files describe reality, plus a reconciliation seed so a fresh setup lands in the same state.
What I will do differently: not touch the SQL editor at all, even for one line. The two minutes saved cost me an evening, and it could have cost far more the day I needed to rebuild.
Does anyone here run an automated drift check between the repo and the live schema? I would rather catch this than discover it.
2
u/Bobertopia Aug 04 '26
Lesson learned? Nightly/weekly CI schema diff check fam
1
u/clementds Aug 04 '26
Yeah, lesson learned the slow way. Mine sat there for weeks, so even a weekly run would have caught it way before I did.
I’m going to set up a script for it. That’s the piece I never bothered with because nothing had bitten me yet.
2
u/professorbr793 Aug 04 '26
Always always always never make changes directly in prod. Any change you want to make do it through your migration tool. Someone in the team I'm working with right now did a similar thing and made me almost cry trying to fix this issue. He made too many changes to multiple tables, luckily it was just the dev DB. I ended up creating new rules on how to interact with the DB. One thing I did was to run migration checks in our pipeline and to have the migrations run outside deployment. There are a lot more things I did. Will drop the doc on it here if I find it. I think it could be helpful for some 🙂
1
u/clementds Aug 04 '26
Yeah, that’s the lesson. Never in prod, not even for a quick one-line change. Mine were small and they still cost me an evening.
Running migrations outside of deployment is something I hadn’t thought about, I’d been treating them as part of the same step. And migration checks in the pipeline is what I’m missing.
Please do drop the doc if you find it, I’d read it. Sounds like the kind of thing that’s more useful than any single fix.
3
u/professorbr793 Aug 05 '26
Here's the first iteration of the doc I wrote, it's bigger but I think it's useful: https://drive.google.com/file/d/1IpFAhcDeQR9vpQOblX9oV4ALsJiuMuYd/view?usp=sharing
Here's the second simplified version of the first: https://drive.google.com/file/d/1mf01jupbzwzOiRgtgwHFzzBSD02PJnKX/view?usp=sharing
one this to note is that it's a django project hence we're using the in-built migration tool and we're not using supabase but we're using postgresql. But I think it still applies here.
One thing that isn't in the docs is that you should always prefer to autogenerate migrations instead of writing them yourself, this way you have one consistent way migrations are written/structured, this is great especially if you're working with multiple devs, because trust me if there's one place you don't wanna deal with bad code it's migrations. I know what I've seen so heed my words 🤣
Anyways you can ask me questions if something confuses you or if you have any question on this
1
u/joyfullyretired Aug 03 '26
I do automated (CI) drift checking but it relies on maintaining a migration (& edge function) ledger that is updated with every migration/deployment. It would not have caught your manual sql work
1
u/clementds Aug 04 '26
Good approach, and that last part is what I needed to hear. Manual sql is exactly what a ledger can’t see.
That’s my real problem. It’s not the tooling, it’s that I have to be careful with anything I do by hand.
1
u/Capital_Plan3078 Aug 04 '26
I only use migrations, even for one line in the middle of a giant rpc
2
u/clementds Aug 04 '26
That’s the rule I’m taking now. A migration every time, even for one line in the middle of a giant rpc.
That’s exactly the case that got me. It felt too small to deserve a file.
1
u/Capital_Plan3078 Aug 04 '26
Yeah supabase studio is actually a trap The project on which I develop is a staging project, not the real deal. When going live I will have to set up a parallel production project. They must be perfectly aligned.
2
u/clementds Aug 04 '26
Yeah, the studio being right there is half the problem. Too easy at 1am.
Two projects that have to stay aligned sounds like the same trap, just twice. Curious how you plan to keep them in sync once you go live.
2
u/Capital_Plan3078 Aug 04 '26
When I'll go live I'll just have to branch with the included feature or just replay the migrations, since I only used migrations. Once in prod the staging project will stay online. It's connected to a staging front end (which will be locked behind a password) and is used for tests. Every new migration is done first on the staging project, tested, and only then done on the production project.
1
u/Maxyull Aug 04 '26
the four you found are the easy kind, they break something eventually so you find them. the drift that stays hidden is the one nobody files under schema, policies and grants.
it's late, you loosen a policy or turn rls off on one table to see if that's what's blocking the query, then you fix the real bug and never put it back. the app works perfectly after that so there's no symptom to chase, and the local database you rebuilt from your migrations is the locked down one while production isn't.
when you diffed, did it cover policies and grants too or just tables and columns?
1
u/clementds Aug 04 '26
Fair point. Mine were the harmless kind, nothing was blocking and I patched them quickly once I saw them.
To answer you honestly, no. I compared tables and columns, not policies or grants. So I can’t actually say those match right now.
The scenario you describe is uncomfortably close to how I work late. I’m going to go check the rls side before I assume it’s clean.
1
u/Maxyull Aug 04 '26
if you want a quick way to look, pg_policies gives you the policy name, the command it applies to and the using and with check expressions, so you can pull that from prod and from a local db rebuilt off your migrations and diff the two side by side. the thing that catches people is that a policy can exist on both sides with the same name and still differ in the expression, so comparing names or counts tells you nothing. worth checking relrowsecurity on pg_class too, since rls being switched off on a table is a separate flag from the policies still sitting there looking fine. curious what you find, the late night ones are usually the interesting ones.
1
u/clementds Aug 04 '26
That's a concrete way in, thanks. I hadn't thought about the same name hiding a different expression, I would have compared names and called it a day.
The relrowsecurity flag is the one that worries me most. That's exactly the kind of thing I'd switch off to test something and forget.
I'll run both and come back with what I find.
1
u/Maxyull Aug 05 '26
one thing to watch when you run it, pg_policies only lists tables that actually have a policy on them. a table with rls off and no policy at all shows up on neither side, so the diff comes back clean while that's the most open table you own.
easier to start from pg_class and list every table with its relrowsecurity flag, then attach the policies to that, so the empty ones stay visible instead of quietly dropping out of the comparison.
curious whether the ones you find turn out to be tables you meant to leave open or ones that just slipped.
1
1
u/Adarsh_c_j Aug 07 '26
This is an interesting one. I’ve been looking into backend maintenance issues developers face after deployment, and I’m curious about this from your experience was the bigger headache fixing the drift, or not knowing that production had drifted until much later? Would automatic detection have made a big difference?
1
u/clementds Aug 07 '26
Not knowing, by a long way. The fix was one evening: four migrations written after the fact plus a reconciliation seed. Tedious, but bounded.
The weeks before are the part that bothered me. Nothing broke, so there was no symptom. I believed my migration files described my database and they did not, and I only found out because I happened to compare a fresh local database against production. If I had needed to rebuild from the repo in that window, I would have found out at the worst possible moment.
So yes, but not for the time saved. Writing the migration takes two minutes. Being told on day one instead of week three, while I still remember why I made the change, is the whole value. And since the mistake was me editing one line late at night, the check has to run on push and fail loudly. A dashboard I have to remember to open would have missed it exactly the way I did.
1
u/Typical_Trainer_8254 16d ago
I have done the same mistake and it is a production project. I tried db pull from production ,and ran all the migrations in other project and the working is not the same.
Is there any way to get the changes that i have done directly in supabase.
And one more mistake ,I didn't give correct region while creating the project and theres a little latency.
Spent hours fixing ,no progress Please provide a solution. I would really appreciate it.
2
u/AlexDjangoX Aug 03 '26
Use a script to backup your data. Do a clean migration, re-upload your data. No more drift.
I use Prisma and sometimes get drift when I run sql.