Database.Middle.How would you handle schema migrations in a production database?

Handling schema migrations in a production database requires planning, safety, and automation to avoid downtime or data loss. Here’s a complete strategy:

✅ 1. Use a Schema Migration Tool

Use a reliable tool to version and manage migrations:

Java: Flyway, Liquibase
Node.js: Knex.js, TypeORM migrations
Python: Alembic (SQLAlchemy), Django migrations
Raw SQL: Use plain .sql files with a custom runner

These tools:

Track which migrations have run
Apply pending changes
Roll back when needed

⚙️ 2. Follow a Migration Workflow

Step-by-step:

A. Version Control

Keep all migration scripts in Git with the application code.
One migration = one clear purpose (e.g., add column, create index).

B. Write Idempotent Migrations (If Possible)

Avoid breaking re-runs:

-- PostgreSQL example
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'my_enum') THEN
        CREATE TYPE my_enum AS ENUM ('A', 'B');
    END IF;
END
$$;

C. Test Locally and on Staging

Spin up a replica or use a staging DB.
Apply migrations, run smoke tests, verify data integrity.

🧩 3. Backward-Compatible Changes First

To enable zero-downtime deployments, use a 3-step migration:

⏳ Phase 1: Prepare

Add new column, leave old untouched
Allow writing to both columns (if needed)

🔁 Phase 2: Migrate

Copy data from old column/table to new
Update application code to use the new structure

🧹 Phase 3: Cleanup

Drop old column/index/table only after verifying full migration

💡 Example: Renaming a Column (the safe way)

Don’t: ALTER TABLE users RENAME COLUMN name TO full_name;

Do:

ALTER TABLE users ADD COLUMN full_name TEXT;
Migrate data: UPDATE users SET full_name = name;
Update app to use full_name
After rollout: ALTER TABLE users DROP COLUMN name;

📦 4. Deploy in Coordination With Code

Apply migrations before deploying the code that depends on them.
Automate this in your CI/CD pipeline.
Consider feature flags for new schema usage.

🛑 5. Be Careful with Destructive Changes

Avoid these during peak hours:

DROP TABLE
ALTER COLUMN TYPE
DROP COLUMN

Instead:

Schedule during maintenance windows
Consider blue-green deployments

🔙 6. Have Rollback and Backups Ready

Backup DB before critical migrations
Avoid complex rollback SQL — instead, use forward-only with hotfixes
Consider tools like gh-ost or pt-online-schema-change for large tables (MySQL)

✅ Summary Table

Step	Purpose
Use migration tools	Versioned, repeatable schema changes
Apply changes safely	Backward-compatible + staged rollout
Test before prod	Catch errors in staging
Coordinate with app code	Avoid runtime errors
Avoid blocking ops in prod	Prevent downtime & locks
Rollback plan and backups	For safety in failure cases