There is a specific kind of dread that hits right after you ship an internal app people actually use. The feature requests arrive. The business process shifts. And suddenly you need to change the database in a way that cannot be “just run it locally and hope.” Real rows. Real workflows. Real consequences if the deploy goes sideways.
The good news is that most schema changes are boring once you adopt a small set of habits. This post is the playbook I use with Prisma on real systems—usually SQLite or PostgreSQL, the same pair I’ve written about when SQLite is enough and when it is time to move up. The database engine matters at the edges, but the mindset is the same: never let a deploy require the old code and the new code to disagree about what the database means.
## The Core Idea: Expand, Then Contract
Think of every risky change as two phases. First you expand the schema so it can satisfy both the old behavior and the new behavior at the same time. Then you ship application code that writes the new shape. Then you backfill or clean up. Only then do you contract by removing the old columns, tables, or constraints.
If you try to rename a column, tighten a type, or add a hard NOT NULL in a single step, you are asking for a lock, a failed migration, or a production error the moment traffic hits a version skew. The expand-contract pattern is how you make schema evolution look like a series of small, reversible steps instead of one cliff.
## Adding Columns: Boring on Purpose
New field? Add it as nullable or with a default that every existing row can accept. Deploy the migration. Deploy the app that starts reading and writing the field. If you need the column to be required eventually, do that in a later migration after you are sure every row has a real value.
For internal tools, I treat “required in the UI” and “required in the database” as different milestones. The UI can enforce business rules immediately. The database constraint is the safety net you add once the data is clean.
## Renames and Type Changes: Never in Place
Renaming a column in one migration while the old binary is still running is a classic foot-gun. The safer sequence: add new_name, dual-write (or copy on read) in application code, backfill, switch reads to the new column, remove the old column in a follow-up migration.
Changing a column’s meaning or storage type is the same story. Add the new representation alongside the old one, migrate data in a controlled job or lazy migration, cut reads over, then drop the legacy field. Prisma’s migration history stays readable because each step does one thing.
## Backfills: One Job, Clear Assumptions
When you need to populate a new column from old data, be explicit about idempotency and batching. For small internal databases, a one-off script run after deploy is often enough. For larger tables, batch by primary key and log progress so you can resume.
I keep backfill logic in application-land (TypeScript) or a dedicated script that uses the same Prisma client as the app. That way the transformation rules live next to the domain code, not buried in raw SQL nobody will remember to update.
## Indexes and Foreign Keys: Order Matters
Adding an index on a big PostgreSQL table can be disruptive if you do it naively. For heavy tables, consider creating the index CONCURRENTLY (outside Prisma’s automatic migration in some setups)—or schedule the migration in a maintenance window. For SQLite, many operations still rewrite the table; know your size and test on a copy.
Foreign keys are constraints on reality. Adding them is often safe once data is clean. Enabling them on messy legacy imports is not. Validate first, constrain second.
## Prisma-Specific Habits That Pay Off
Treat generated migrations as reviewed artifacts. Read the SQL. If Prisma wants to do something destructive, ask whether this should be two migrations instead of one.
Use prisma migrate deploy in production, not migrate dev. Keep dev and prod on the same migration sequence so surprises show up on your machine first.
If you need custom SQL for a phased change, prisma migrate dev --create-only (or editing the migration file before apply) is the escape hatch. The goal is not to avoid SQL; it is to keep SQL intentional.
## Deployments and Version Skew
In the simple case—single instance, brief downtime acceptable—you can migrate then deploy and sleep fine. In the case of multiple instances or zero-downtime expectations, assume old and new code will run together for at least one deploy window. That assumption is what makes expand-contract non-negotiable.
SQLite-backed internal apps are often single-instance, which removes some distributed pain but does not remove the need for compatible migrations. You still do not want a migration that breaks the process holding the file.
## Rollbacks: Plan Forward, Not Backward
Database rollbacks are overrated. Restoring from backup is a disaster recovery lever, not a deploy undo button. I plan for “forward fix”: a migration that repairs bad state, or a quick patch release. That sounds scarier than it is, because the expand-contract approach keeps each step small enough that the forward fix is usually obvious.
## A Short Checklist Before You Ship the Migration
- Will the old app version still start and run against this schema?
- If not, have you staged the change across two deploys?
- Does every new NOT NULL have a story for existing rows?
- Is the backfill idempotent and observable?
- Did you read the generated SQL and understand the locks?
## Final Thought
Schema panic usually comes from coupling: one big bang change that tries to update the world in a single transaction. If you treat the database as something that evolves in small, compatible steps, Prisma migrations stop being a source of drama and become what they should be—a readable log of how your understanding of the business got sharper over time.