How do you perform Database Migrations without service downtime (Zero-Downtime)?

Short answer

Follow the Expand and Contract pattern so the database schema supports both the old and new application code during deployment.

Explanation

In production, servers cannot be stopped while tables are migrated. To delete or change the type of a column, proceed in stages: 1) add a new column without changing the old one, 2) deploy new application code that writes to both columns, 3) migrate old data in the background, 4) update the code to read only from the new column, and 5) remove the old column in a separate later migration.

Code example

-- Release 1 (EXPAND): add the new nullable column — old and new application code both keep working
ALTER TABLE users ADD COLUMN full_name text;

-- Backfill in small batches so locks stay short
UPDATE users SET full_name = first_name || ' ' || last_name
WHERE full_name IS NULL AND id BETWEEN 1 AND 10000;

-- Release 2: the app writes BOTH representations and reads full_name

-- Release 3 (CONTRACT): once nothing reads the old columns, drop them
ALTER TABLE users DROP COLUMN first_name, DROP COLUMN last_name;

Common mistakes

• Making exclusive changes that lock large tables (Table Lock), such as adding columns with incompatible default values.

Follow-up questions

• How do you safely implement Database Transactions and isolation levels in Node.js?

Sources

• Prisma docs — Prisma Migrate