Database migration best practices for production teams

Production migrations fail for predictable reasons: too many changes in one file, untested rollbacks, environments that do not resemble production, and releases that skip review evidence.

These practices map directly to the DBLift workflow teams use today. Each one says which tier it needs: OSS, Pro, or Enterprise.

1. One logical change per migration (OSS)

Keep each migration focused so debugging and rollback stay understandable.

ALTER TABLE users ADD COLUMN phone VARCHAR(20);

Avoid bundling unrelated DDL in a single file. If one step fails, you want a narrow blast radius and a clear undo script.

2. Test the full forward and backward path (OSS)

Before production, run the same sequence you expect in CI:

dblift migrate --dry-run
dblift migrate
dblift info
dblift undo --target-version 1.0.1
dblift migrate

If you cannot define a safe undo path, treat the change as a high-risk release and plan manual mitigation explicitly.

3. Start from the schema you have (OSS)

Do not replay years of manual history. Two commands cover the two situations, and you run exactly one of them.

# Schema exists, nothing tracked it: mark the current state as the starting point
dblift baseline

# Flyway already tracked it: copy the history table instead
dblift import-flyway

Never baseline on top of an import. Baseline collapses every applied version into one row. If flyway_schema_history exists, import it and stop there. The Flyway article walks through the import.

4. Separate offline lint from database validation (OSS + Pro)

Use both checks for different risks:

# Offline SQL policy (Pro / Enterprise)
dblift validate-sql migrations/ --dialect postgresql --fail-on warning

# Database-backed integrity (OSS core)
dblift validate

Offline lint catches policy and syntax issues early. validate confirms checksums, ordering, and applied state against the configured database.

5. Pin package versions in CI (OSS)

The GitHub Action installs the package for you. Pin the version so a pipeline run is reproducible, and bump it on purpose.

- uses: dblift/action@v1
  with:
    command: validate
    extras: postgresql
    version: "3.10.1"

- uses: dblift/action@v1
  with:
    command: migrate
    extras: postgresql
    version: "3.10.1"

When Enterprise commands run in the same job, hand the Action the full requirement list instead of extras and version:

- uses: dblift/action@v1
  with:
    packages: 'dblift[postgresql]==3.10.1 dblift-enterprise'
    args: validate

Pass credentials through environment variables such as DBLIFT_DB_URL instead of committing secrets to the repository.

6. Add release evidence before high-risk deploys (Enterprise)

For Enterprise teams, generate review artifacts before apply:

dblift plan \
  --snapshot-model .dblift/uat.snapshot.json \
  --format html,json \
  --output-dir artifacts/

dblift preflight \
  --snapshot-model .dblift/uat.snapshot.json \
  --skip-replay \
  --format html,json \
  --output-dir artifacts/

Keep the HTML and JSON outputs with your change record. Then apply:

dblift migrate

Plan and preflight work from snapshot models. They are not substitutes for schema diff workflows — use diff when comparison is the goal.

7. Prefer additive changes for zero-downtime work (OSS)

When altering heavily used tables, split the change so every step is safe to run while the application is up:

1. Add the new column or table
2. Backfill in controlled batches
3. Switch application reads/writes
4. Remove obsolete objects in a later migration

A required column is the classic case. Adding it as NOT NULL in one statement rewrites the table under a lock; three small migrations do not.

ALTER TABLE orders ADD COLUMN status VARCHAR(20);

def migrate(context):
    if context.dry_run:
        return
    while context.execute(
        "UPDATE orders SET status = 'open' "
        "WHERE id IN (SELECT id FROM orders WHERE status IS NULL LIMIT 5000)"
    ):
        pass

ALTER TABLE orders ALTER COLUMN status SET NOT NULL;

This pattern reduces lock time and makes rollback safer: undoing V2_0_2 is one statement, and the data backfill stays in place.

8. Document breaking changes in the migration itself (OSS)

When a release removes columns or changes contracts, state the impact in the migration header comment and link the undo script. Reviewers and auditors should not have to infer risk from DDL alone.

Practical release checklist

1. OSS — Small, named migration files with undo coverage where feasible
2. Pro — validate-sql on pull requests
3. OSS — validate against an integration database
4. Enterprise — plan / preflight artifacts retained with the release
5. OSS — migrate --dry-run immediately before production apply

For a guided walkthrough, use the Getting started guide and the Governance and Reports pages for current evidence examples.