The schema broke with the last deploy. The query failed because a new column didn’t exist where it should have.
Adding a new column sounds simple. In practice, doing it right matters more than doing it fast. A bad migration will break code, lose data, or block deploys. A clean migration makes the change invisible to users and easy to roll back.
Start with a migration script in version control. Describe the new column with the exact type and constraints you need. Avoid nullable-by-default unless you have a reason. If the column needs a default, set it in the migration to prevent NULL writes during rollout.
For large tables, add the column without heavy locking. Many databases allow creating an empty column instantly, but adding defaults or backfilling can be slow. Use a two-step migration: first add the nullable column, then backfill in batches, then add the NOT NULL constraint.
Update application code in phases. Deploy code that can handle both the old and new schema before running the migration. Once the column exists, write to it without reading from it. Then, after you’ve backfilled and validated data, switch reads to the new column. Finally, remove old code paths.
Test migrations on production-like data snapshots. Simulate both forward and backward migrations to confirm rollback paths. Always watch for replication lag if you’re changing primary keys or indexed columns.
Automation helps. Use schema migration tools that track applied scripts, lock schema changes, and log failures. Keep migrations small, reversible, and tied to specific deploys.
The moment a schema change reaches production should feel uneventful. It means you got every detail right.
See how you can design and ship schema changes, including adding a new column, in minutes with live previews on hoop.dev.