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