Database Migration

You might say

I need to add a phone column to our user table, but production already has tens of thousands of rows. How do we update the schema without losing data or causing downtime?

An ordered, versioned, and traceable set of steps for changing database structure or required data.Suppose users already contains live rows and a required status column is needed. A safe migration adds a compatible column, backfills old rows, verifies that no NULL values remain, and only then enforces the constraint. The migration tool records applied versions, but migration files are not data backups and do not guarantee lossless reversal.

Know first

Database SQL Deployment

DB MigrationSchema Migration

How database migrations safely evolve schema across versions

Live users table and migration logStage 1 · Existing rows have no status

idnamestatus

101Zhang SanNULL

102Li SiNULL

103Wang WuNULL

0001_init.sqlApplied
Direct ADD status NOT NULLRejected because old rows violate the constraint
0002_add_status_nullable.sqlNot run
0003_backfill_status.sqlNot run

Applied historymigration history: 0001_init

EvidenceThe result shows existing NULL values prevent the required constraint from being added directly

Inspect existing data before choosing the migration order; a rejected change is not a database crash.

How to add a required field when rows already exist

Existing rows may not satisfy a new required constraint: Old rows in users have no status value. Enforcing status NOT NULL immediately can fail while those rows remain empty. Even if the database supports a default during the change, the value must reflect a real business rule rather than a guess.

Stay compatible, backfill, then tighten: First add a nullable status column so old and new application versions can keep running. Next backfill valid values under an explicit rule and record the affected rows. Only after NULL count reaches zero should the migration enforce NOT NULL.

Verify data results and old/new application behavior: Check the migration version, log, affected-row count, and remaining NULL values; any NULL means enforcement must wait. In staging, also confirm existing users can be read, new users receive valid status values, and both application versions work during transition. Migration history is not a backup, so destructive changes still need a recovery plan.

You can say this to an AI Agent

The users table already has live rows and needs a required status column. First inspect the migration tool, current values, and old-app compatibility. Then add a nullable column, backfill valid values, verify NULL count is zero, and finally enforce NOT NULL. Run it in staging and report the version, affected rows, NULL check, and old/new app read-write results. Do not treat a down migration as a backup.

Learn next

1Database→2Transaction→3Rollback→

Recommended tool

Web fine-tuning tool · Selector Pro

Select page elements for precise AI edits

Click a button, text, or card, note the change, then copy its exact location to AI.

Open Selector Pro website

You need to add a required column status (NOT NULL) to an existing user table with live data. What is the safest migration strategy?

A. Manually add the column via a database GUI in production without checking any migration files into gitB. Staged migration: add a nullable column first, backfill existing rows, and enforce NOT NULL after verifying zero NULLsC. Drop the live user table and recreate it from scratch, asking existing users to sign up and re-enter data