Daniel's been thinking about database migrations — the kind you run in a TypeScript backend where the schema lives in SQL and the app layer is TypeScript. He wants to know what actually happens from the moment you decide the schema has to change, to the moment that change is live in production. The whole arc. Not just the SQL, not just the ORM command — the full procedure.
And he's right to ask, because the answer you usually get is "run the migration command," which is about ten percent of the actual job. The other ninety percent is everything that keeps you from waking up to a production database that's been locked for six hours.
So we're going to trace the full lifecycle. The decision, the mechanics of writing and testing, and then the deployment strategies that keep production alive. Let's start at the very beginning — the moment you realize the schema has to change.
The first thing to understand is the core tension here. Your database is a shared, long-lived resource. It outlasts application versions, it outlasts frameworks, it outlasts your current ORM choice. Your application code, by contrast, is disposable. You'll rewrite it, refactor it, swap out libraries. The database stays.
Which makes a migration something more than a SQL script. It's the bridge between those two realities — the durable thing and the disposable thing.
Martin Fowler laid this out in his work on evolutionary database design, and the fundamental insight is that the database schema is not a static artifact. It evolves alongside the application. Migrations are how you make that evolution safe and repeatable. Every migration is a versioned, ordered change that must be applied exactly once, in order, across every environment.
And the TypeScript slash SQL split adds a wrinkle. You've got the ORM layer — Prisma, Drizzle, whatever — where you define your schema in code. And then you've got the raw SQL that actually runs against the database. The migration is where those two worlds meet, and that meeting is not always friendly.
Right. The ORM gives you a schema definition file — in Prisma's case, schema dot prisma. You edit that file, you run a command, and the ORM diffs your schema against the current database state and generates SQL. That generated SQL is your migration. But it's a starting point, not the finished product.
And this is the first place people get burned. They assume the ORM's output is correct and complete. It's not. It's a diff — a mechanical comparison of two states. It doesn't understand intent.
Prisma's own documentation is explicit about this. Migrations are the source of truth, and you must review the generated SQL before applying it. The diff might add a column with a default value, but it won't know that you also need to backfill existing rows. It won't know that you need to update a view that depends on the table. It won't know that dropping that column will cascade into three other services.
So the migration is the artifact, and the developer's judgment is the safety mechanism. That's the dynamic for the whole process.
Now let's get into the weeds. What does the actual workflow look like, step by step?
Step one. You realize the schema needs to change. This could be a new feature — you're adding user avatars, so you need an avatar URL column. It could be a performance issue — you're denormalizing something to avoid a join. Or it could be a correction — you stored something as a string that should have been an enum.
The first question at this stage is always: can this be done without a migration? Sometimes you can add a column with a default value in a way that doesn't require touching existing rows. Sometimes you can solve the problem in the application layer. But if the data model itself has to change, you're writing a migration.
Step two. You edit your schema definition file. In Prisma, that's schema dot prisma. You add the new column, or change the type, or add the new table. Then you run prisma migrate dev.
And this is where the ORM earns its keep. It connects to your development database, compares the current state to your schema file, and generates the SQL that would bring the database in line with the schema. It creates a new migration file — a timestamped SQL file in your migrations directory.
But here's the thing. The file it generates is a draft. You open it. You read it. And you ask: is this actually what I want?
I've been burned by not doing this. I once had a migration that looked fine — it added a column — but the generated SQL also dropped and recreated a foreign key constraint because the ORM detected some drift I hadn't noticed. If I'd run that in production without reading it, I would have lost referential integrity for the duration of the migration. Which, on a table with a few million rows, could have been minutes.
So you review the SQL. You might add a backfill step. You might add an index creation that the ORM didn't generate because it doesn't know your query patterns. You might split one migration into two because the single-step approach would lock the table for too long.
Step three. Testing. You apply the migration to your local database first. Does it run? Does it complete in a reasonable time? Then you apply it to a staging database — ideally one with a production-sized dataset, or at least a representative sample.
The key question at this stage is reversibility. Can you roll this migration back if something goes wrong?
Fowler's work emphasizes this heavily. Every migration should be designed to be reversible, even if you never actually roll back. The rollback is your insurance policy. If your migration adds a column, the rollback drops it. If it changes a column type, the rollback changes it back — and you'd better have a plan for what happens to data that doesn't fit the old type.
Which brings us to the expand slash contract pattern. This is the gold standard for safe schema changes.
Also called parallel change. The idea is deceptively simple. You never change a schema in one step. You expand first, then contract. Three phases.
Let's make this concrete. Say you're adding a status column to a users table. Right now, users are either active or not, but that's implicit — there's no column for it. You want to add a status column that can be active, inactive, or suspended.
Phase one, expand. You add the status column to the schema, but you make it nullable or give it a default value. The old application code doesn't know about this column and doesn't touch it. The new application code writes to it. Both versions of the application work against the same database.
Then you deploy the new application code. It's writing to the status column now. Existing users still have null or the default — they haven't been backfilled yet. That's fine. The application handles both cases.
Then you run a backfill script. This isn't part of the migration itself — it's a separate process, often a script or a one-off job, that populates the status column for all existing rows based on whatever logic determines the correct status. This might take minutes or hours depending on the table size, but it runs while the application is live and serving traffic.
Phase three, contract. Once the backfill is complete and you've verified that every row has a correct status value, you can make the column required. That's a second migration. And eventually, if there was an old way of representing this data — maybe a boolean is active column — you drop that in a third migration.
Three migrations for what looks like one change. That's the pattern. And the reason is that each step is safe on its own. If the expand migration fails, you haven't changed anything the old application depends on. If the backfill fails, you've got a nullable column and the application handles that. If the contract migration fails, the data is already in the new column — you just haven't enforced the constraint yet.
The alternative — one migration that adds the column, backfills, and makes it required — works fine in development. In production, it's a single point of failure that can take down every service that touches that table.
Writing the migration is one thing. Getting it live in production without taking everything down — that's a whole different game.
The deployment problem. In development, you run prisma migrate dev and it applies the migration to your local database. If something goes wrong, you reset the database and start over. Nobody's credit card charge is failing because your local users table is locked.
In production, there's live traffic. Queries are hitting the database constantly. A migration that locks a table for even thirty seconds can cause a cascading failure across every service that depends on that table.
And the deployment ordering problem makes this harder. You've got two things to deploy: the migration, and the new application code. Which goes first?
If you deploy the migration first, the old application code breaks because the schema changed in a way it doesn't expect. If you deploy the application first, the new code breaks because it's querying a column that doesn't exist yet.
The solution is the multi-step deployment we just described, but coordinated across application versions. You expand the schema first — add the new column as nullable. That migration goes to production. The old application code doesn't care about the new column. It keeps running.
Then you deploy the new application code. It writes to the new column, reads from it when it's available, and falls back to the old behavior when it's not. This is the dual-write phase.
Then you backfill. Then you deploy the contract migration that makes the new column required or drops the old one. And finally, you deploy application code that removes the fallback logic.
Four or five deployments for one schema change. That's the reality of zero-downtime migrations. And the tooling matters enormously here. Prisma's production command is prisma migrate deploy — not prisma migrate dev. The dev command can reset your database. It's designed for development, where losing data is annoying but not catastrophic. The deploy command applies all pending migrations in order and stops. It doesn't generate anything, doesn't reset anything.
The fact that these are two different commands with different behavior and different safety profiles — and that running the wrong one in the wrong environment can destroy your production database — is the kind of thing that keeps developers up at night.
And there's another safety mechanism worth mentioning. The shadow database. Prisma uses a shadow database — a temporary copy of your database — to detect drift between your schema file and the actual database state. It catches situations where someone manually edited the production database and the migrations no longer match the schema.
Which happens more often than anyone wants to admit.
Far more often. Someone runs an ad-hoc query to fix a production issue, or a DBA adds an index without going through the migration workflow, and suddenly the schema file and the database are out of sync. The shadow database catches that drift before you run a migration that assumes a state that no longer exists.
So the tooling gives you guardrails. But the human factor is still the biggest source of failure. What actually goes wrong?
The most common failure mode is not reviewing the generated SQL. You trust the ORM, you run the migration, and it does something you didn't expect — like dropping and recreating a constraint, or adding a default value that's wrong for your data.
Second failure pattern: applying a migration without a backup. If the migration corrupts data, and you don't have a backup, you're rebuilding from whatever you can salvage.
Third: not testing the rollback path. Everyone tests the forward migration. Almost nobody tests the rollback. And then when something goes wrong in production and you need to roll back, you discover that the rollback script has a syntax error or doesn't handle some edge case.
Fourth: running a migration during peak traffic. A migration that takes two seconds on a quiet Sunday morning might take two minutes during a weekday rush, because the database is under load and the ALTER TABLE has to wait for locks.
And the knock-on effect that doesn't get enough attention: migrations are a coordination problem, not just a technical problem. In a team, multiple developers are working on different features, each of which might need a schema change. If two developers create migration files at the same time, you get version control conflicts in the migrations directory.
And migration files are ordered. They have sequence numbers or timestamps. If you merge two branches that both added a migration, the order matters. The wrong order can break the entire chain.
The solution is discipline. One migration per change, reviewed like code. Migration files go through the same pull request process as application code. And if you're working on a feature that needs a schema change, you coordinate with the team to avoid conflicts.
There's also the question of what happens when you have dozens or hundreds of migrations accumulated over years. Each one is a small, ordered step. Applying them all from scratch — say, when a new developer sets up their local environment — can take a long time.
That's where migration squashing comes in, which we've covered before. But the principle remains: in development, you can squash. In production, you apply each migration in order, exactly once.
Let's talk about what a real deployment sequence looks like, end to end. You've got a users table. You're adding a preferred language column so you can send emails in the right language.
Monday morning. You edit schema dot prisma, add the preferred language column as an optional string. You run prisma migrate dev locally. It generates a migration file. You open it. It's a simple ALTER TABLE ADD COLUMN. Looks fine.
But you notice it doesn't add an index. You know you'll be querying by preferred language for email segmentation, so you add a CREATE INDEX statement to the migration. You test it locally — it runs in under a second on your development database.
You push the migration to staging. The staging database has production-like data — about two million users. The migration takes four seconds. Acceptable. You test the application against the new schema — the old code ignores the column, the new code reads it and falls back to a default language when it's null.
Tuesday. You deploy the migration to production during a low-traffic window. The migration runs. The column exists, nullable, no default. The production application — still the old version — doesn't know about it. Nothing breaks.
Wednesday. You deploy the new application code. It writes preferred language when users set it in their profile. For users who haven't set it, the column is null, and the application falls back to English.
Thursday. You run a backfill script. For every user with a null preferred language, it checks the user's country code and sets a reasonable default. This takes about twenty minutes — it's not a migration, it's an application-level script, so it doesn't lock anything.
Friday. You verify the backfill. Every user has a preferred language. You deploy a second migration that adds a NOT NULL constraint and a default value. The migration runs. The column is now required.
The following Monday. You deploy application code that removes the null-check fallback. The column is always populated. The old code path is dead. You're done.
Five deployments, two migrations, one backfill script, and zero downtime. That's what a real production migration looks like.
And the whole thing took a week. For one column.
Which sounds absurd until you compare it to the alternative. One migration, one deployment, done in ten minutes — and a fifty percent chance that something goes wrong and you're explaining to your boss why the users table was locked for the duration of the ALTER TABLE.
The fear is the feature. The caution is what keeps production running.
I want to touch on one more thing before we move on. The Prisma shadow database mechanism. It's worth understanding why it exists. In a perfect world, your database state always matches your migration history. Every change goes through the migration workflow. In reality, someone runs a manual query, or a different tool modifies the schema, or a previous migration was applied out of order. The shadow database creates a clean copy of your database, applies all the migrations to it, and then compares the result to your schema file. If they don't match, you've got drift. And drift means your next migration is building on a foundation you don't fully understand.
It's like checking the blueprint against the actual building before you start construction on the new wing. You might discover that someone added a load-bearing wall that isn't in the plans.
And the shadow database is a relatively recent addition to the migration toolchain. For years, developers just trusted that the database matched the schema. The number of production incidents caused by that assumption is... substantial.
I'm thinking about what happens when a migration fails halfway through. You're adding a column to a large table, the ALTER TABLE runs for ten minutes, and then it times out or hits a disk space limit. You're left with a partially applied migration.
That's the nightmare scenario. The migration tool records which migrations have been applied in a special table — Prisma calls it underscore prisma migrations. If a migration fails partway through, that table might show the migration as applied when it actually wasn't, or vice versa. You're in an inconsistent state, and the tooling might not be able to recover automatically.
Which is why you test on staging with production-scale data. A migration that runs in half a second on your laptop might run for an hour on a table with fifty million rows. You need to know that before you run it in production.
And you need to know what the database does during that hour. Is the table locked for writes? For reads? Different databases handle ALTER TABLE differently. MySQL's InnoDB engine can add a column without locking the table in many cases. PostgreSQL can add a nullable column instantly. But changing a column type, or adding a constraint, or creating an index — those can lock.
The specifics depend on your database engine, your version, and the exact operation. Which is why the generated SQL from the ORM is not the end of the story. You need to understand what that SQL actually does on your particular database.
You know, all this talk about production failures reminds me — Hilbert, you've got a story about this, don't you?
Hilbert: Nineteen ninety-seven. I was working at a small telecom in Ohio. They called me the database administrator. What I actually was, was the only person who knew the password to the production database.
How did you get that job?
Hilbert: The previous person quit. I was in the right hallway at the wrong time.
What happened?
Hilbert: ALTER TABLE. Adding a column to the billing records table. About eight million rows. I ran it on a Friday afternoon because I figured if something went wrong, I'd have the weekend to fix it.
Oh no.
Hilbert: The table locked. The entire billing system went down. Customer service couldn't look up accounts. The call center was just apologizing for six hours. I spent the whole weekend in the office with a senior engineer named Frank, restoring from tape backups.
How long did the restore take?
Hilbert: Fourteen hours. And then I had to manually re-enter three days of billing data from printed reports. They kept printed reports of every transaction. Stack of paper about this thick. I typed in four thousand, six hundred and twelve transactions by hand over the course of a Sunday.
Did you ever find out what the column was for?
Hilbert: Marketing campaign. They wanted to track which customers had received a promotional mailer. Campaign got cancelled the following Monday.
Of course it did.
Hilbert: I still have the printed reports in a box in my apartment. I don't know why. Frank retired in two thousand three. He sends me a Christmas card every year. It just says "ALTER TABLE" with a question mark.
That is... that's a lot to carry.
Hilbert: The tools are better now. But the fear is the same. I still get nervous running a migration, and I think that's healthy. The database is the one thing you can't just redeploy. You break it, you're restoring from backups. And backups take fourteen hours.
Fourteen hours and a stack of paper.
Hilbert: The paper was the worst part. Dot matrix printer paper. The kind with the perforated edges. You tear off the edges and then you have to separate the pages, and the ink smudges if you touch it wrong. I had ink on my hands for a week.
I'm stuck on the fact that you still have the reports.
Hilbert: They're in a box labeled "taxes." I haven't done my own taxes since nineteen ninety-four. I just like the box.
Hilbert, I have to ask — after that weekend, did you change how you approached migrations?
Hilbert: I never ran one on a Friday again. And I always made sure Frank was in the building. That's it. That was my entire risk management strategy for about four years. Frank in the building, not a Friday. It worked.
Frank sounds like he earned that Christmas card.
Hilbert: Frank once fixed a corrupted index by hand-editing a hex dump of the database file. Different era. Different kind of person.
I don't think I'd sleep for a month after a weekend like that.
Hilbert: You'd be surprised what you get used to. Anyway, the point is, the migration workflow you described — expand, contract, backfill, review the SQL — that's all correct. But none of it replaces the feeling in your stomach when you hit enter on a production migration. That feeling is the real safety mechanism.
I'm going to be thinking about the box labeled taxes for the rest of the day.
Hilbert: It's a good box. Sturdy.
Alright, let's wrap this up. But there's one question we haven't answered. As databases get more complex and teams get more distributed, is this workflow going to change? Prisma and other tools are starting to offer AI-generated migrations — you describe the change in natural language, and it writes the SQL. Does that make things safer, or does it introduce new failure pattern?
I think it introduces the same failure pattern we've been talking about, just at a higher level of abstraction. If you don't review the generated SQL now, you'll skip reviewing the AI-generated SQL too. The tool gets smarter, but the human responsibility doesn't go away. The migration is still the moment where the abstract world of code meets the concrete world of data. It's the most dangerous thing a developer does, and the most important to get right.
And the fear Hilbert's talking about — that's not a bug. It's the thing that makes you read the SQL twice. It's the thing that makes you test on staging. It's the thing that makes you back up the database before you run the migration, even though you have automated backups. The fear is the feature.
The migration is a versioned, ordered, repeatable change to a shared, long-lived resource. The procedure is: decide, write, review, test, expand, deploy, backfill, contract, verify. The tooling helps with the mechanics. The discipline is all yours.
Thanks to our producer Hilbert Flumingtop for keeping this show running — and apparently for keeping a box of dot matrix reports in his apartment for three decades.
If you've got a weird prompt you want us to tackle, send it in — we'll take it from there. This has been My Weird Prompts. We'll be back soon.
