Database migration with GitHub Actions
How I built a GitHub Action to manage versioned SQL database migrations across MySQL, MariaDB, PostgreSQL and MSSQL — with dry-run, integrity checks, SHA-256 checksums and Docker-based testing, all from a CI/CD pipeline.
Database schema changes are one of the riskiest parts of any deployment. A migration applied twice, out of order, or missing entirely can corrupt data or take an application offline. In this post I walk through a GitHub Action I built to manage versioned SQL migrations across multiple database drivers — with dry-run preview, SHA-256 checksums and integrity checks, all without an ORM or a migration framework dependency.
The action is open source and available at guibranco/github-database-migration-action.
The problem with manual SQL scripts
Most teams start with a changes.sql file or a shared folder of scripts. It works until it doesn’t:
- Someone applies
v3beforev2on the staging server - A script gets applied twice because nobody tracked it
- Production is one migration behind and nobody knows which one
- A rollback is needed but there’s no record of what changed
Frameworks like Laravel (Artisan), Doctrine, Flyway and Liquibase solve this elegantly — but they require a specific language runtime, an ORM or a Java dependency. If your stack is polyglot, or if you simply want the migrations to live and run in the CI/CD pipeline without installing additional tools, a GitHub Action is a natural fit.
How the action works
The action is a Docker-based GitHub Action written in POSIX shell (sh). It connects directly to the database using the native client for each driver and manages a schema_version table that tracks every applied migration file.
The schema_version table
On first run, the action creates this table automatically — one definition per supported driver:
-- MySQL / MariaDB
CREATE TABLE IF NOT EXISTS `schema_version` (
`Sequence` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`Filename` VARCHAR(255) NOT NULL,
`Checksum` CHAR(64) NOT NULL,
`Date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`Sequence`),
UNIQUE (`Filename`),
UNIQUE (`Checksum`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- PostgreSQL
CREATE TABLE IF NOT EXISTS schema_version (
sequence SERIAL PRIMARY KEY,
filename VARCHAR(255) NOT NULL UNIQUE,
checksum CHAR(64) NOT NULL UNIQUE,
date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
-- SQL Server (MSSQL)
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name='schema_version')
CREATE TABLE schema_version (
Sequence INT IDENTITY(1,1) PRIMARY KEY,
Filename NVARCHAR(255) NOT NULL,
Checksum CHAR(64) NOT NULL,
Date DATETIME NOT NULL DEFAULT GETDATE(),
CONSTRAINT UQ_sv_Filename UNIQUE (Filename),
CONSTRAINT UQ_sv_Checksum UNIQUE (Checksum)
);
Each row records the filename, a SHA-256 checksum of the file’s content, and the timestamp of when it was applied. The UNIQUE constraint on Filename prevents double-application, and the UNIQUE on Checksum prevents applying a different file that happens to share a name.
Migration file naming
All .sql files inside a migrations/ directory at the repository root are processed in alphabetical order. The recommended convention is to prefix files with a zero-padded sequence number:
migrations/
├── 0001_create_users_table.sql
├── 0002_add_email_index.sql
├── 0003_create_orders_table.sql
└── 0004_add_foreign_keys.sql
INSERT statement that records the migration.
The four operations
[APPLIED] or [PENDING]. Does not modify the database. Useful as a PR status check.[PENDING] migrations — the ones that would be applied. Does not execute any SQL. Safe to run on production.schema_version.Supported databases
| Driver | Value | Client used | Notes |
|---|---|---|---|
| MySQL | mysql | mysql CLI | Full support |
| MariaDB | mariadb | mysql CLI | Full support (compatible with MySQL client) |
| PostgreSQL | postgresql | psql | Full support |
| SQL Server | mssql | tsql (FreeTDS) | Full support |
| Oracle 11g | oracle11g | n/a | Not bundled — requires custom Docker image with Oracle Instant Client |
FROM guibranco/github-database-migration-action and add Oracle Instant Client and sqlplus manually.
Configuration and inputs
- uses: guibranco/github-database-migration-action@latest
env:
DATABASE_PWD: ${{ secrets.DATABASE_PWD }}
with:
operation: migrate # dry-run | migrate | check | integrity
driver: mysql # mysql | mariadb | postgresql | mssql
host: 127.0.0.1
user: app_user
database: my_database
integrity_commands_file: integrity-checks.sql # only for integrity operation
with:DATABASE_PWD), not as an input. GitHub Actions masks environment variables set from secrets, so the password never appears in logs. Passing it as an input would expose it in the workflow run summary.
Full workflow examples
Check on every pull request
Show migration status without touching the database — great as a PR reviewer aid:
name: Migration status
on:
pull_request:
jobs:
status:
runs-on: ubuntu-latest
services:
mysql:
image: mysql:8.0
env:
MYSQL_ROOT_PASSWORD: root
MYSQL_DATABASE: mydb
MYSQL_USER: app
MYSQL_PASSWORD: secret
ports: ["3306:3306"]
options: >-
--health-cmd="mysqladmin ping -h 127.0.0.1 -uroot -proot"
--health-interval=10s
--health-timeout=5s
--health-retries=5
steps:
- uses: actions/checkout@v4
- name: Check migration status
uses: guibranco/github-database-migration-action@latest
env:
DATABASE_PWD: secret
with:
operation: check
driver: mysql
host: 127.0.0.1
user: app
database: mydb
Migrate on merge to main
Apply pending migrations automatically when code reaches the main branch:
name: Deploy migrations
on:
push:
branches: [main]
paths:
- 'migrations/**.sql'
jobs:
migrate:
runs-on: ubuntu-latest
environment: production
steps:
- uses: actions/checkout@v4
- name: Dry-run first
uses: guibranco/github-database-migration-action@latest
env:
DATABASE_PWD: ${{ secrets.PROD_DB_PASSWORD }}
with:
operation: dry-run
driver: postgresql
host: ${{ secrets.PROD_DB_HOST }}
user: ${{ secrets.PROD_DB_USER }}
database: ${{ secrets.PROD_DB_NAME }}
- name: Apply migrations
uses: guibranco/github-database-migration-action@latest
env:
DATABASE_PWD: ${{ secrets.PROD_DB_PASSWORD }}
with:
operation: migrate
driver: postgresql
host: ${{ secrets.PROD_DB_HOST }}
user: ${{ secrets.PROD_DB_USER }}
database: ${{ secrets.PROD_DB_NAME }}
- name: Verify integrity
uses: guibranco/github-database-migration-action@latest
env:
DATABASE_PWD: ${{ secrets.PROD_DB_PASSWORD }}
with:
operation: integrity
driver: postgresql
host: ${{ secrets.PROD_DB_HOST }}
user: ${{ secrets.PROD_DB_USER }}
database: ${{ secrets.PROD_DB_NAME }}
integrity_commands_file: integrity-checks.sql
Testing with a Docker service (multi-driver matrix)
Run the full migration suite against multiple databases in parallel:
name: CI — Migration tests
on: [push, pull_request]
jobs:
test:
runs-on: ubuntu-latest
strategy:
matrix:
include:
- driver: mysql
image: mysql:8.0
env: {MYSQL_ROOT_PASSWORD: root, MYSQL_DATABASE: test, MYSQL_USER: test, MYSQL_PASSWORD: test}
port: "3306:3306"
health: "mysqladmin ping -h 127.0.0.1 -uroot -proot"
- driver: mariadb
image: mariadb:11.4
env: {MARIADB_ROOT_PASSWORD: root, MARIADB_DATABASE: test, MARIADB_USER: test, MARIADB_PASSWORD: test}
port: "3306:3306"
health: "healthcheck.sh --connect --innodb_initialized"
- driver: postgresql
image: postgres:16
env: {POSTGRES_DB: test, POSTGRES_USER: test, POSTGRES_PASSWORD: test}
port: "5432:5432"
health: "pg_isready -U test"
services:
db:
image: ${{ matrix.image }}
env: ${{ matrix.env }}
ports: [${{ matrix.port }}]
options: --health-cmd="${{ matrix.health }}" --health-interval=10s --health-retries=5
steps:
- uses: actions/checkout@v4
- name: Run migrations (${{ matrix.driver }})
uses: guibranco/github-database-migration-action@latest
env:
DATABASE_PWD: test
with:
operation: migrate
driver: ${{ matrix.driver }}
host: 127.0.0.1
user: test
database: test
Integrity checks
The integrity operation runs a file of SQL queries (one per line) and verifies that each one returns at least one row. If any query returns empty, the step fails and the pipeline stops.
integrity-checks.sql:
-- Verify the users table exists and has at least one row
SELECT 1 FROM users LIMIT 1
-- Verify a required index exists (MySQL example)
SELECT 1 FROM information_schema.statistics
WHERE table_name = 'users' AND index_name = 'idx_users_email'
-- Verify a foreign key constraint exists
SELECT 1 FROM information_schema.key_column_usage
WHERE constraint_name = 'fk_orders_user_id'
-- Verify a required seed record exists
SELECT 1 FROM roles WHERE name = 'admin'
# Lines starting with # are comments and are skipped
integrity immediately after migrate in the same deployment workflow. It acts as a smoke test confirming that the expected schema objects and seed data are in place before the application starts routing production traffic.
Implementation highlights
A few design decisions in the shell script are worth calling out:
MIG: prefix for reliable result parsing. Each driver client adds different noise to its output (column headers, row counts, locale messages). To reliably extract filenames from the schema_version query, every SELECT wraps the value in a MIG: prefix, then grep '^MIG:' filters out everything else regardless of the driver.
exec_sql_mysql "SELECT CONCAT('MIG:', Filename) FROM schema_version ORDER BY Sequence;"
# output contains: MIG:0001_create_users_table.sql
SHA-256 checksums. Every migration file is hashed with sha256sum before and after application. The hash is stored in schema_version. If the same filename is re-submitted with different content, the UNIQUE constraint on Checksum will reject it — preventing silent content drift in migration files.
File-level cache. Applied migrations are loaded once into a temporary file (/tmp/applied_migrations.txt) at startup, and grep -qFx checks against that cache rather than querying the database for every file. This keeps the action fast even with hundreds of migration files.
POSIX sh — no Bash. The script uses #!/bin/sh (not #!/bin/bash) for maximum portability across Alpine Linux (used in the Docker image) and any other base image.
Versioned, auditable, CI-native database migrations
The schema_version table gives you a permanent audit trail of every migration ever applied to a database — who (the CI system), when and what checksum. The four operations cover the full lifecycle: preview changes on PRs (check), confirm what will run (dry-run), apply (migrate) and verify (integrity). No ORM, no migration framework, no extra runtime required.
The action works with any workflow trigger and any environment — local Docker services for testing, staging databases via secrets, or production behind a VPN. Drop a migrations/ folder in your repository, wire up the secrets and let the pipeline manage the rest.
Repository: github.com/guibranco/github-database-migration-action
Categories