id: dbmate-migration-reversibility-gate
namespace: company.team
description: |
When a push changes the migrations folder, apply every migration with dbmate
to a throwaway PostgreSQL database, roll back the newest one and apply it
again, then compare the result with the accepted migrations and schema of the
base branch kept in the KV store. The merge is blocked when the rollback does
not restore the schema, or when a migration already accepted on the base
branch was edited or deleted. Dropped tables and columns are reported. A clean
run on the base branch moves the baseline forward. The throwaway database is
always dropped.
inputs:
- id: repository_url
type: STRING
defaults: "https://github.com/acme/orders-service.git"
displayName: "Repository URL"
description: "Git repository that holds the dbmate migrations."
- id: branch
type: STRING
defaults: "main"
displayName: "Branch"
description: "Branch to check, usually the branch of the pull request."
- id: base_branch
type: STRING
defaults: "main"
displayName: "Base Branch"
description: "Branch whose accepted migrations and schema form the baseline.
Only clean runs on this branch update it."
- id: migrations_dir
type: STRING
defaults: "db/migrations"
displayName: "Migrations Directory"
description: "Folder with the dbmate migration files, relative to the repository root."
- id: server_url
type: STRING
defaults: "postgres://migration_gate@postgres-ci.internal:5432"
displayName: "Disposable PostgreSQL Server"
description: "postgres://user@host:port of a CI-only PostgreSQL server, without
database name. The user needs CREATEDB; never point this at production."
- id: ssl_mode
type: SELECT
values: [ "require", "verify-full", "disable" ]
defaults: "require"
displayName: "SSL Mode"
description: "sslmode used to reach the server."
variables:
database_url: "{{ inputs.server_url }}/migration_gate_{{ execution.id | lower
}}?sslmode={{ inputs.ssl_mode }}"
baseline_key: "{{ ('dbmate-baseline-' ~ inputs.repository_url ~ '-' ~
inputs.base_branch) | slugify }}"
triggers:
- id: on_migration_push
type: io.kestra.plugin.core.trigger.Webhook
description: Point a GitHub push webhook (JSON) at this trigger. It only starts
the gate when a pushed commit adds, changes or removes a file under
db/migrations/. Shipped disabled.
disabled: true
key: "{{ secret('MIGRATION_GATE_WEBHOOK_KEY') }}"
when: "{{ (trigger.body.commits ?? []) | toJson | jq('[.[] | (.added + .modified
+ .removed)[] | select(startswith(\"db/migrations/\"))] | length') | first
> 0 }}"
inputs:
repository_url: "{{ trigger.body.repository.clone_url }}"
branch: "{{ trigger.body.ref | replace({'refs/heads/': ''}) }}"
base_branch: "{{ trigger.body.repository.default_branch }}"
tasks:
- id: migration_check
type: io.kestra.plugin.core.flow.Sequential
description: Run the check on a database created for this execution, and drop
that database in `finally` whatever happened.
tasks:
- id: workspace
type: io.kestra.plugin.core.flow.WorkingDirectory
description: Clone, migrate, roll back, migrate again and compare, all in one
working directory.
tasks:
- id: clone
type: io.kestra.plugin.git.Clone
description: Shallow clone of the pushed branch. For a private repository add
`username` and `password` from a secret.
url: "{{ inputs.repository_url }}"
branch: "{{ inputs.branch }}"
depth: 1
- id: run_migrations
type: io.kestra.plugin.scripts.shell.Commands
description: With the official dbmate 2.36.0 image, create a database for this
execution, apply every migration, roll back the newest, apply it
again, and dump the schema after each step. Exit codes are
captured so a failing step is reported instead of crashing the
task.
containerImage: ghcr.io/amacneil/dbmate:2.36.0
env:
DATABASE_URL: "{{ render(vars.database_url) }}"
PGPASSWORD: "{{ secret('MIGRATION_GATE_DB_PASSWORD') }}"
DBMATE_MIGRATIONS_DIR: "{{ inputs.migrations_dir }}"
DBMATE_NO_DUMP_SCHEMA: "true"
commands:
- |
dbmate create
rc=0; dbmate migrate > migrate.log 2>&1 || rc=$?; cat migrate.log; echo migrate=$rc > steps.txt
if [ $rc -eq 0 ]; then
dbmate --schema-file after_up.sql dump
rc=0; dbmate rollback > rollback.log 2>&1 || rc=$?; cat rollback.log; echo rollback=$rc >> steps.txt
if [ $rc -eq 0 ]; then
dbmate --schema-file after_rollback.sql dump
rc=0; dbmate migrate > reapply.log 2>&1 || rc=$?; cat reapply.log; echo reapply=$rc >> steps.txt
if [ $rc -eq 0 ]; then dbmate --schema-file after_reapply.sql dump; fi
fi
fi
touch after_up.sql after_rollback.sql after_reapply.sql migrate.log rollback.log reapply.log
timeout: PT20M
- id: compare_with_baseline
type: io.kestra.plugin.scripts.python.Script
description: Diff the first up with rollback + up, then compare the migration
files (by content hash) and the schema objects with the base
branch's accepted baseline from the KV store. With no baseline
yet, a clean run on the base branch adopts the current state.
containerImage: python:3.12-slim
inputFiles:
baseline.json: "{{ kv(render(vars.baseline_key), errorOnMissing=false) | toJson
}}"
outputFiles:
- after_up.sql
- after_reapply.sql
- schema.diff
- baseline.json
script: |
import datetime, difflib, hashlib, json, os, re
migrations_dir = "{{ inputs.migrations_dir }}"
branch, base = "{{ inputs.branch }}", "{{ inputs.base_branch }}"
files = sorted(f for f in os.listdir(migrations_dir) if re.match(r"^\d+_.+\.sql$", f)) if os.path.isdir(migrations_dir) else []
hashes = {f: hashlib.sha256(open(os.path.join(migrations_dir, f), "rb").read()).hexdigest() for f in files}
newest = files[-1] if files else "none"
steps = dict(line.strip().split("=", 1) for line in open("steps.txt", encoding="utf-8") if "=" in line)
stored = json.load(open("baseline.json", encoding="utf-8"))
def schema(name):
if not os.path.exists(name) or os.path.getsize(name) == 0:
return None
return [l.rstrip() for l in open(name, encoding="utf-8") if l.strip() and not l.startswith(("--", "\\restrict", "\\unrestrict"))]
def objects(lines):
"""Tables, columns, types, indexes and constraints named in a pg_dump schema."""
found, table, altered = set(), None, None
for line in lines or []:
m = re.match(r"CREATE TABLE (\S+) \(", line)
if m:
table = m.group(1); found.add(f"table {table}"); continue
if table:
if line.startswith(")"):
table = None
else:
col = re.match(r"\s+(\w+|\"[^\"]+\")\s", line)
if col and not col.group(1).upper() in ("CONSTRAINT",):
found.add(f"column {table}.{col.group(1)}")
continue
m = re.match(r"CREATE TYPE (\S+)", line) or re.match(r"CREATE (?:UNIQUE )?INDEX (\S+) ON", line)
if m:
found.add(("type " if line.startswith("CREATE TYPE") else "index ") + m.group(1)); continue
m = re.match(r"ALTER TABLE (?:ONLY )?(\S+)$", line)
if m:
altered = m.group(1); continue
m = re.match(r"\s+ADD CONSTRAINT (\S+)", line)
if m and altered:
found.add(f"constraint {altered}.{m.group(1)}")
return found
def last_error(log):
lines = [l.strip() for l in open(log, encoding="utf-8", errors="replace") if l.strip()] if os.path.exists(log) else []
errors = [l for l in lines if l.lower().startswith("error")]
return (errors or lines or ["no output"])[-1][:300]
def status(step):
rc = steps.get(step)
return "skipped" if rc is None else ("ok" if rc == "0" else f"failed (exit {rc})")
up, rolled_back, again = schema("after_up.sql"), schema("after_rollback.sql"), schema("after_reapply.sql")
diff, reason = [], ""
if not files:
reason = f"no migration files found in {migrations_dir}"
elif steps.get("migrate") != "0":
reason = f"the migrations do not apply to an empty database: {last_error('migrate.log')}"
elif steps.get("rollback") != "0":
reason = f"dbmate rollback of {newest} failed: {last_error('rollback.log')}"
elif steps.get("reapply") != "0":
reason = f"{newest} cannot be applied again after its rollback, so the down migration leaves objects behind: {last_error('reapply.log')}"
elif up is None or again is None:
reason = "a schema dump is missing or empty, check the run_migrations logs"
else:
diff = list(difflib.unified_diff(up, again, "after first up", "after rollback + up", lineterm="", n=1))
if diff:
changed = sum(1 for l in diff if l[:1] in "+-" and not l.startswith(("+++", "---")))
reason = f"the schema after rollback + up differs from the first up ({changed} line(s)): the down migration of {newest} does not exactly undo its up"
# Compare with what the base branch already accepted.
current = objects(up)
accepted = (stored or {}).get("migrations", {})
edited = sorted(f for f, h in accepted.items() if f in hashes and hashes[f] != h)
removed = sorted(f for f in accepted if f not in hashes)
new = sorted(f for f in hashes if f not in accepted) if stored else []
dropped = sorted(set((stored or {}).get("objects", [])) - current) if up else []
if not reason and (edited or removed):
reason = (f"migration(s) already accepted on {base} were changed: "
+ ", ".join([f"{f} edited" for f in edited] + [f"{f} deleted" for f in removed])
+ ". Databases that already ran them will never run them again; add a new migration instead")
on_base = branch == base
adopted = stored is None and on_base and not reason
history = (stored or {}).get("history", [])
point = {"at": datetime.datetime.now(datetime.timezone.utc).isoformat(timespec="seconds"), "branch": branch,
"migrations": len(files), "newest": newest, "objects": len(current)}
json.dump({"migrations": hashes, "objects": sorted(current), "history": (history + [point])[-30:]},
open("baseline.json", "w", encoding="utf-8"), indent=1)
open("schema.diff", "w", encoding="utf-8").write("\n".join(diff) + "\n")
print("::" + json.dumps({"outputs": {
"passed": reason == "", "reason": reason, "newest": newest, "migrations": len(files),
"up": status("migrate"), "rollback": status("rollback"), "reapply": status("reapply"),
"rollback_changed_schema": bool(up and rolled_back and up != rolled_back),
"diff_lines": [l for l in diff if l[:1] in "+-" and not l.startswith(("+++", "---"))][:40],
"had_baseline": stored is not None, "adopted": adopted, "on_base_branch": on_base,
"new_migrations": new, "edited": edited, "removed": removed, "dropped": dropped[:30],
"accepted_count": len(accepted), "history_points": len(history),
}}) + "::")
finally:
- id: drop_database
type: io.kestra.plugin.scripts.shell.Commands
description: Drop the throwaway database, even when a step or the comparison
failed (dbmate drop uses IF EXISTS).
containerImage: ghcr.io/amacneil/dbmate:2.36.0
env:
DATABASE_URL: "{{ render(vars.database_url) }}"
PGPASSWORD: "{{ secret('MIGRATION_GATE_DB_PASSWORD') }}"
commands:
- dbmate drop
- id: log_results
type: io.kestra.plugin.core.log.Log
description: Log each step, the comparison with the base branch and, when the
schemas differ, the changed lines.
message: |-
Migration gate for {{ inputs.repository_url }} ({{ inputs.branch }}): {{ outputs.compare_with_baseline.vars.migrations }} migration(s), newest {{ outputs.compare_with_baseline.vars.newest }}. up: {{ outputs.compare_with_baseline.vars.up }}, rollback: {{ outputs.compare_with_baseline.vars.rollback }}, up again: {{ outputs.compare_with_baseline.vars.reapply }}. {% if outputs.compare_with_baseline.vars.had_baseline %}Compared with {{ outputs.compare_with_baseline.vars.accepted_count }} migration(s) accepted on {{ inputs.base_branch }}: new {{ outputs.compare_with_baseline.vars.new_migrations | length }}, edited {{ outputs.compare_with_baseline.vars.edited | length }}, deleted {{ outputs.compare_with_baseline.vars.removed | length }}.{% else %}No baseline for {{ inputs.base_branch }} yet{{ outputs.compare_with_baseline.vars.adopted ? '; this run becomes the baseline' : '' }}.{% endif %}{% for line in outputs.compare_with_baseline.vars.diff_lines %}
{{ line }}{% endfor %}
- id: report_drops
type: io.kestra.plugin.core.flow.If
description: Dropping a table or column the base branch has is sometimes
intended, but it deletes data on deploy, so it is listed at WARN level for
the reviewer.
condition: "{{ outputs.compare_with_baseline.vars.dropped | length > 0 }}"
then:
- id: log_drops
type: io.kestra.plugin.core.log.Log
level: WARN
message: |-
{{ inputs.branch }} removes {{ outputs.compare_with_baseline.vars.dropped | length }} object(s) that exist on {{ inputs.base_branch }}; their data is lost on deploy:{% for o in outputs.compare_with_baseline.vars.dropped %}
- {{ o }}{% endfor %}
- id: warn_noop_rollback
type: io.kestra.plugin.core.flow.If
description: A rollback that changes nothing is legitimate for a data-only
migration, but usually means an empty down section.
condition: "{{ outputs.compare_with_baseline.vars.rollback == 'ok' and not
outputs.compare_with_baseline.vars.rollback_changed_schema }}"
then:
- id: log_noop_rollback
type: io.kestra.plugin.core.log.Log
level: WARN
message: "Rolling back {{ outputs.compare_with_baseline.vars.newest }} did not
change the schema. Check that its down section is not empty."
- id: gate
type: io.kestra.plugin.core.flow.If
description: Block the merge with the reason. On a clean run of the base branch,
store its migrations and schema as the new baseline; a clean run on
another branch leaves the baseline alone until the merge.
condition: "{{ outputs.compare_with_baseline.vars.passed }}"
then:
- id: update_baseline
type: io.kestra.plugin.core.flow.If
condition: "{{ outputs.compare_with_baseline.vars.on_base_branch }}"
then:
- id: save_baseline
type: io.kestra.plugin.core.kv.Set
description: Store the accepted migration hashes, schema objects and a run
history (last 30) for the base branch.
key: "{{ render(vars.baseline_key) }}"
value: "{{ read(outputs.compare_with_baseline.outputFiles['baseline.json']) }}"
kvType: JSON
- id: log_baseline_saved
type: io.kestra.plugin.core.log.Log
message: "Migration gate passed on {{ inputs.base_branch }}; baseline {{
render(vars.baseline_key) }} now accepts {{
outputs.compare_with_baseline.vars.migrations }} migration(s),
newest {{ outputs.compare_with_baseline.vars.newest }}."
else:
- id: log_passed
type: io.kestra.plugin.core.log.Log
message: "Migration gate passed on {{ inputs.branch }}: {{
outputs.compare_with_baseline.vars.newest }} rolls back cleanly
and no migration accepted on {{ inputs.base_branch }} was changed.
The baseline changes only after the merge."
else:
- id: block_merge
type: io.kestra.plugin.core.execution.Fail
errorMessage: "Merge blocked for {{ inputs.branch }}: {{
outputs.compare_with_baseline.vars.reason }}"
outputs:
- id: passed
type: BOOL
value: "{{ outputs.compare_with_baseline.vars.passed }}"
- id: schema
type: FILE
value: "{{ outputs.compare_with_baseline.outputFiles['after_up.sql'] }}"