Database migrations basics (schema changes safely)

Topic: Databases core

Apply schema and data changes in versioned, reversible steps using migration scripts or tools (e.g. Flyway, Liquibase, or custom SQL). Test on staging first; backup before production migration. Use this when introducing or evolving schema in a way that is auditable and rollback-safe.

Intent: How-to

Quick answer

  • Migrations are ordered scripts (e.g. V1__create_users.sql, V2__add_index.sql). Each runs once; tool or table tracks applied version. Forward-only or provide down migrations for rollback.
  • Rules: make changes backward-compatible when possible (add column nullable before backfill, then add constraint). Avoid long-running locks; use CONCURRENTLY for index creation in PostgreSQL. Backup before production run.
  • Run in order: dev then staging then production. Same migration version must produce same result. Test rollback or fix-forward procedure; document who runs migrations and how to recover from failure.

Prerequisites

Steps

  1. Choose migration approach

    Use a tool (Flyway, Liquibase, Alembic) or a custom version table and ordered SQL files. Each migration has a unique version or name; record applied migrations in DB.

  2. Write backward-compatible changes

    Add column as nullable first; backfill; then add NOT NULL if needed. Drop column in a later migration after code no longer uses it. Create index CONCURRENTLY (PostgreSQL) to avoid long lock.

  3. Test on staging

    Apply migrations on a copy of production data or staging; run application tests. Verify rollback or fix-forward if migration fails mid-way.

  4. Production run

    Backup database; run migrations during maintenance window or with low traffic. Monitor for locks and duration; have rollback or fix plan. Document applied version and any manual steps.

Summary

Apply schema changes as versioned migrations; keep changes backward-compatible where possible; test on staging and backup before production. Use this to evolve schema safely and auditably.

Prerequisites

Steps

Step 1: Choose migration approach

Use a migration tool or versioned SQL; track applied migrations in the database.

Step 2: Write backward-compatible changes

Add columns nullable first; use CONCURRENTLY for indexes; drop in a later migration.

Step 3: Test on staging

Run migrations on staging; run app tests; verify rollback or fix procedure.

Step 4: Production run

Backup; run migrations; monitor; document version and rollback plan.

Verification

  • Migrations are versioned and applied in order; staging test passes; production run is documented and reversible where needed.

Troubleshooting

Migration fails — Fix forward or rollback; restore from backup if necessary. Long lock — Use CONCURRENTLY (PostgreSQL) or run during low traffic; avoid locking whole table when possible.

Next steps

Continue to