Webhook icon
Sequential icon
WorkingDirectory icon
Clone icon
Commands icon
Script icon
Log icon
If icon
Set icon
Fail icon

dbmate Migration Gate with an Accepted-Migration Baseline

Test dbmate down migrations on a throwaway PostgreSQL database and block merges that edit already-accepted migrations, with a baseline in the KV store.

Categories
Infrastructure

Two migration mistakes reach production quietly. The first is a down migration nobody ran: it drops the wrong index or forgets the enum type its up created, and fails on the night a release has to be rolled back. The second is editing a migration that is already merged: every database that already applied it will never run it again, so staging and production silently drift from what the repository says. This blueprint catches both on every push that touches the migrations. It uses dbmate on a throwaway PostgreSQL database, and keeps the base branch's accepted migrations and schema in the KV store so each push is compared with what was already accepted.

How it works

  1. on_migration_push (io.kestra.plugin.core.trigger.Webhook) receives the Git host's push webhook and only starts an execution when a pushed commit touches db/migrations/. It passes the repository URL, branch and default branch as inputs and ships disabled: true.
  2. migration_check (io.kestra.plugin.core.flow.Sequential) wraps the check so its finally block always runs. Inside, workspace (io.kestra.plugin.core.flow.WorkingDirectory) keeps every step in one working directory, and clone (io.kestra.plugin.git.Clone) checks out the branch.
  3. run_migrations (io.kestra.plugin.scripts.shell.Commands) runs the official ghcr.io/amacneil/dbmate:2.36.0 image: dbmate create makes a database named after the execution, then migrate, dump, rollback, dump, migrate, dump. Each exit code is recorded, so a failing step is reported instead of crashing the task.
  4. compare_with_baseline (io.kestra.plugin.scripts.python.Script) diffs the first up with rollback + up, then reads the base branch's baseline with kv(): the content hash of every accepted migration and the tables, columns, types, indexes and constraints of the accepted schema. It reports new, edited and deleted migrations and objects the branch drops. With no baseline yet, a clean run on the base branch adopts the current state.
  5. log_results logs every step and the comparison; report_drops lists dropped tables and columns at WARN level; warn_noop_rollback warns when the rollback did not change the schema at all.
  6. gate (io.kestra.plugin.core.flow.If) runs block_merge (io.kestra.plugin.core.execution.Fail) when the down migration does not restore the schema or an accepted migration was edited or deleted. A clean run on the base branch stores the new baseline and run history with io.kestra.plugin.core.kv.Set; a clean run on another branch leaves it alone until the merge.
  7. drop_database in the finally block of migration_check drops the throwaway database whatever happened.

What you get

  • Every down migration executed against a real PostgreSQL before it is merged, not only read in review.
  • Merges that edit or delete an already-accepted migration blocked, with the file names, before databases drift.
  • Dropped tables and columns listed for the reviewer, since they delete data on deploy.
  • A baseline per repository and base branch in the KV store with a history of the last 30 accepted runs, plus the schema dumps and schema.diff on every execution.
  • No leftover databases on the CI server, even when a step fails.

Who it's for

  • Backend teams that use dbmate and rely on dbmate rollback during incidents.
  • Platform and database teams that want the same migration rules enforced on every repository without adding a CI job to each one.

Why orchestrate this with Kestra

The check needs a database that exists only for this run, a fixed order of steps, a decision, a cleanup that cannot be skipped, and memory of what the base branch already accepted. Kestra starts it from the push webhook with a condition on the changed paths, shares one working directory between the dbmate container and the comparison, keeps the accepted state per repository in the KV store, turns the result into a pass or a blocked merge with If and Fail, and drops the database in finally.

Prerequisites

  • A PostgreSQL server reserved for CI, reachable from the worker, with a user that has CREATEDB. Use the same major version as production. Never point the gate at a production server.
  • Migrations in dbmate format (-- migrate:up and -- migrate:down sections) under db/migrations, or set migrations_dir.
  • A Kestra worker that can run containers (Docker or Kubernetes task runner) and pull ghcr.io/amacneil/dbmate:2.36.0 and python:3.12-slim.

Secrets

  • MIGRATION_GATE_DB_PASSWORD: password of the CI database user (with trust or certificate authentication any value works, it is ignored).
  • MIGRATION_GATE_WEBHOOK_KEY: a long random key that becomes part of the webhook URL.
  • For a private repository, a Git token used as password in clone.

Quick start

  1. Create the CI database user with CREATEDB and store its password as MIGRATION_GATE_DB_PASSWORD.
  2. Execute the flow on the base branch (branch = base_branch = main) with your repository_url and server_url: it checks the newest migration and adopts the baseline.
  3. Execute it on a branch that edits an older migration, and check that block_merge names the file.
  4. Add a push webhook in your Git host pointing at /api/v1/main/executions/webhook/company.team/dbmate-migration-reversibility-gate/<key> (JSON content type), adjust the db/migrations/ path in the trigger's when if needed, and enable the trigger.

How to extend

  • Report the result as a commit status on the pull request with an HTTP request to the Git host's API.
  • Roll back every migration added on the branch instead of only the newest one, by looping dbmate rollback over the new files.
  • Block on dropped tables or columns unless the pull request carries an approval label.
  • Load a sanitized data sample before the rollback to catch down migrations that fail on real rows.

Links

See How

New to Kestra?

Use blueprints to kickstart your first workflows.