Schema changes on client projects used to mean running an ALTER TABLE statement directly against the production database by hand, then trying to remember to mention it to the other person on the team, usually over chat, sometimes not at all if the day got busy. Predictably, that occasionally went wrong, a column added on production that never made it into anyone's notes, a staging database that quietly drifted out of sync with production until a bug only reproduced on one of the two.
What a migration file actually looks like
We adopted a lightweight migrations approach this year, numbered SQL files checked into the same Git repo as the application code, each one a small, reversible, documented change. A typical file is named something like 004_add_customer_phone_column.sql, contains the actual ALTER TABLE statement, a short comment explaining why the change is needed, and a matching down file that reverses it, dropping the column again. Running them in order against a fresh database now reliably reproduces the current schema from scratch, which we tested deliberately by tearing down a local database entirely and rebuilding it from migrations alone rather than trusting that it would work in theory.
The rollback we were glad we wrote
The reversibility requirement felt like unnecessary overhead the first few times we wrote a down migration for a change we were confident about, until a renamed column on one client project turned out to break a report a different part of the application depended on in a way we hadn't anticipated. Running the down migration for that one file took thirty seconds and put the database back exactly as it had been, no manual reconstruction of what the column used to be called, no scrambling to remember the previous type and default value. That single incident paid for every down migration we'd written up to that point and made writing them, even for changes that felt obviously safe, a non-negotiable habit going forward rather than an optional nicety.
Handling data migrations, not just schema
Not every change is purely structural. A few migrations this year needed to backfill data too, adding a new status column and then populating it based on existing data, for instance, rather than just leaving it null for every existing row. We handle these the same way, a numbered SQL file, but we're more careful with these specifically, testing the backfill logic against a copy of production data first rather than only against our smaller local development database, since a backfill that behaves correctly on a hundred rows can behave very differently against the tens of thousands a real client database might have accumulated.
Getting a teammate up to speed on this
Bringing a contractor onto one client project briefly this year gave us an unplanned test of whether this approach actually helps onboarding the way we hoped. Handing them the migrations folder and explaining the numbering convention took about five minutes, and they were able to understand the entire schema's history, including a couple of decisions that would otherwise have looked strange without context, an odd-looking column that only makes sense once you read the migration explaining why it was added as a temporary workaround for a since-resolved issue, just by reading through the files in order. That's a kind of institutional memory the old ad-hoc approach never gave us, and it's arguably the single biggest unplanned benefit of the whole switch.
Where we drew the line on tooling
We looked briefly at a couple of dedicated migration frameworks with their own command-line tools and version-tracking tables, and decided against adopting one for now. Plain numbered SQL files plus a small custom script that tracks which migrations have already run against a given database, storing that state in a single tracking table, gets us essentially the same benefit without introducing a new dependency and a new tool's own learning curve for the sake of features, branching migrations, framework-specific rollback tooling, that our actual client projects don't need yet.
What it costs us day to day
It adds a small amount of ceremony to what used to be a thirty-second manual change, writing the file, writing the down migration, running it locally before ever touching a client's database, is closer to five or ten minutes now for a simple change. But the confidence that we can rebuild a client's database structure from source control alone, and the peace of mind that a mistake is one command away from being undone rather than requiring us to reconstruct what the schema used to look like from memory, is worth every bit of that overhead, and it's the kind of overhead that gets cheaper the more of a habit it becomes rather than staying a fixed tax on every change.
Handling a migration that needs to run against a live, in-use table
The trickiest migration we wrote this year wasn't a simple column addition, it was splitting one large orders table into two related tables on a client site that was actively taking orders during business hours. We couldn't take the site offline for an extended window, so the migration ran in stages, first creating the new table and copying existing data into it while the old table kept serving live traffic unchanged, then a short, carefully timed second migration that flipped the application code over to the new tables during the client's lowest-traffic overnight window, verified with a rollback plan ready to go if anything looked wrong in the first hour after the switch. Nothing went wrong, but having that staged plan written down in advance, rather than improvising the cutover live, is what made us comfortable running it at all.
A near-miss that taught us to test migrations against production-sized data
Early on we tested a migration only against our much smaller local development database, where it ran in under a second, and were surprised when the same migration took several minutes against an actual client's production data, long enough that we would have caused a visible outage if we hadn't caught the discrepancy first by testing against a sanitized copy of production data before ever running it for real. The specific cause was a missing index on a column the migration's UPDATE statement was filtering by, invisible at the small scale of our local test data but very much visible at real scale. We now always test a migration expected to touch a meaningful number of rows against a realistically sized copy of the actual data first, not just our clean local database, which is a bit more setup effort but has caught two more slow migrations since.
How we handle migrations across multiple client environments
Most of our client projects run at minimum a local development copy, a staging server, and production, and keeping schema in sync across all three used to be its own small source of drift even before we adopted migrations properly, since staging would sometimes get manually patched to test something and never get the equivalent production fix applied, or vice versa. The migration tracking table we run alongside the SQL files records exactly which migrations have run against which environment, so checking whether staging and production schemas actually match is now a single query comparing two tracking tables rather than a manual, error-prone comparison of two live schemas column by column.
The discipline this requires that we're still building
The approach only works as well as our discipline in actually writing a migration file for every schema change, and it's still tempting, especially under deadline pressure, to run a quick ALTER TABLE directly against a database "just this once" and write the migration file afterward, or worse, not at all. We've caught ourselves doing this twice this year, both times under genuine time pressure, and both times it cost us more later reconstructing the migration file from memory than it would have cost to just write it first. We don't have a perfect technical solution to this discipline problem yet, mostly it's just an explicit item on our own mental checklist before touching a client's production database directly, and we're considering a small script that diffs a database's live schema against what the migration files predict, as a safety net for exactly this kind of slip.
We've since extended the same numbered-file convention to a couple of configuration changes that aren't strictly schema, a change to a required environment variable, say, since the same "small, reversible, documented, in order" discipline turned out to generalize well beyond just database tables.