How do you check a migration before it runs in production?

I’m trying to understand how teams check a Django migration before it reaches a production PostgreSQL database.

  • Do you read the sqlmigrate output in review, or only the migration file?

  • Has a RenameModel, an AlterField or a new non-null field ever locked a large table, lost data, or failed on rows that already existed in production? What happened, and what did it take to put right?

  • Do you run migrations against a copy of production or a staging database first?

  • Do you use a checker in CI (django-migration-linter, something home-made), and what does it still miss?

I’m an engineer working on tooling in this area, and I’d rather learn from what happens in real projects than guess. Thank you.

For what it’s worth, we were a completely manual shop (no CI). For our major public-facing project, updates went through two levels of review & approval before being deployed - internal and external. This meant that at least two migrates were validated before going on the production db. (Our second staging environment, the external review, also was a replica of production.)

That’s on top of making a backup of the production db before doing the migrate.

Other systems tended to be more ad-hoc. Copy the database and run the migrate. Any problems? Restore.

Thank you, Ken, this is the kind of detail I was hoping for. One thing I’m curious about though: did that second staging, the replica of production, ever catch something the first review had let through? If one comes to mind, I’d like to hear what it was.

We automated this with a pre-commit hook.

.pre-commit-config.yaml

repos:
  - repo: local
    hooks:
      - id: sql-for-migrations
        name: Add SQL for new migrations to commit message
        entry: python config/scripts/pre_commit/database_migrations.py
        language: system
        stages: [prepare-commit-msg]
        always_run: true
        pass_filenames: false

database_migrations.py

#!/usr/bin/env python3
import re
import subprocess
import sys
from pathlib import Path

SQL_BLOCK = re.compile(r'(?sm)^BEGIN;\n.*?\nCOMMIT;')


def staged_migrations() -> list[Path]:
    out = subprocess.run(
        ['git', 'diff', '--cached', '--name-only', '--diff-filter=A'],
        capture_output=True, text=True, check=True,
    ).stdout
    return [
        p for p in map(Path, out.splitlines())
        if p.parent.name == 'migrations' and p.suffix == '.py' and p.name != '__init__.py'
    ]


def sqlmigrate(path: Path) -> str:
    app_label = path.parent.parent.name
    out = subprocess.run(
        [sys.executable, 'manage.py', 'sqlmigrate', app_label, path.stem],
        capture_output=True, text=True,
    ).stdout
    match = SQL_BLOCK.search(out)
    return match.group(0) if match else ''


def main() -> None:
    commit_msg_file = sys.argv[1]
    migrations = staged_migrations()
    if not migrations:
        return

    sql = '\n'.join(filter(None, map(sqlmigrate, migrations)))
    if not sql:
        return

    with open(commit_msg_file, 'a') as f:
        f.write(f'\n\nGenerated SQL:\n```sql\n{sql}\n```\n')


if __name__ == '__main__':
    main()

Example of the resulting commit message:

BEGIN;
--
-- Add field pos_id to xxx
--
ALTER TABLE "xxx" ADD COLUMN "pos_id" uuid NULL;
COMMIT;

You could also add squawk to your CI pipeline or as a pre-commit hook. It lints the generated SQL and catches unsafe migrations.

Do you read the sqlmigrate output in review, or only the migration file?

sure

Do you run migrations against a copy of production or a staging database first?

yes

Do you use a checker in CI (django-migration-linter, something home-made), and what does it still miss?

Only human verification

Actually, to the extent that I remember, neither of the pre-production steps ever encountered an issue. Every issue I can remember was caught during development testing. (Caveat - as far as I can remember, my direct involvement with deployment of that system ended sometime in 2023.)

Within my project I do the following as tests for migrations:

  • Fresh install, then run migrations

  • Setup last version of app, then run the migrations to check upgrade.

These two tests have caught issues with migrations that work for a fresh install and not for an upgrade and that work for an upgrade and not a fresh install.

Of note during development, sqlite db is used and production is for postgres. Additionally the app is only developed with postgres support in mind.

Automated unit/integration testing: migrate from zero, including seed data migrations, and run tests.

Review: nominally for all lines changed but doesn’t usually scrutinize autogenerated code like migrations

Automated pre-production deployment: stage environment has a copy of prod data. The main branch is automatically deployed there and has migrations run.

Common failures: can’t recall any schema problems. Data migrations that update a lot of existing rows sometimes time out and we optimize and/or move the change to a management command or shell_plus snippet to run outside the deployment pipeline

A sort of related idea I’ve had, but not started doing, is a test that runs the most important queries with --print-sql and saves the sqlfluff nicely formatted version to a file, for people to see what schema and code changes are doing to the most important queries.

Thank you for sharing the hook itself, that’s generous. One question: has reading that SQL in the commit ever stopped a migration that looked fine in the Python file? I’d like to hear what it was.

Thank you. Testing both the fresh install and the upgrade from the last version is something nobody else here has mentioned. Could you give one example of what the upgrade test caught that the fresh install passed? And since development is on SQLite and production on PostgreSQL: has anything passed both tests and still failed on PostgreSQL?

Thank you, this is a clear picture. When a data migration times out on stage, how do you find out: is someone watching the deploy, or does something tell you? And your idea of saving the most important queries’ SQL to a file: would you want it to show only how the SQL text changed, or also when the database starts running the query differently?

Certainly. Moving data via a migration. 9/10 times, data migration errors that work for fresh install where they fail an upgrade is due to the fact that data migrations occur in a loop. fresh install, there are zero items–so no code in the loop scope runs. Where as for an upgrade there is data to be moved so code within the loop scope executes.

Additionally, any custom function used as a migration that uses logic that is different for fresh and updates are also captured.

Also from memory there have been some others that were SQL related. Although it’s been so long since one of these occurred, that I can’t provide an example.

Ok, I’ll deliniate two answers here as it’s not clear which you are asking:

  • Passed both tests (SQLite DB) failed Postgres

    Yes. Nothing immediate comes to mind. However what I do recall is that some migrations work on sqlite then fail on postgres as additional db operations are required to achieve a same-for-same migration wise. from memory this may have been index renames, “although don’t quote me though.”

  • Passed both tests (Postgres DB) failed Postgres

    No

If desired, my project is on Github - Centurion ERP if you would like to see how I do things.

Thank you, this is exactly what I was after. The loop that never runs on a fresh install, because there is nothing to loop over, is a case I had not thought of. And thanks for pointing me to Centurion ERP, I will read how the two tests are set up.

The reason I am asking all this: part of what I am building is a small tool for the pull request, which will be open source. It runs the migrations on a real PostgreSQL, first as the code was before the change and then with it, and writes a comment saying what they did to the tables: what was dropped, renamed or added. Today that database is empty, so it would not have caught your loop. I would like it to run against seed data or a restored backup as well, on an instance of its own and never on staging or production. How exactly is not decided yet, and I would be glad to hear what your upgrade test uses for its data. It would already show a migration that passes on SQLite and fails on PostgreSQL. It is not released yet. When it is, would you want to try it on Centurion? A plain no is a useful answer too.

seed data or a backup will achieve the same thing as long as seed data uses all fields for the model or multiple entries with a combination that covers all fields. Although restoring a backup and as long as its not too large is a good way to test your runbook on restoring a backup as well as confirming the backup file. If the backup is too large, then have staging as a read-only pull replica of production.

A separate instance will cover most issues except upgrading unless you add it. I’m curious as to why “never” on staging? Staging can be setup many ways and is generally there as the environment that is for testing and that you can break without consequences.

I was lazy in this regard. I leave data from my tests in the database.

Unfortunatly my answer is no. I don’t find what DB changes occurred as part of a PR as something useful to track. Centurion will eventually have an online migration system (still in early planning) So what occurs migrations wise wont provide accurate db changes as part of a PR.