Building a Database Migration Runner in Bash

1. Why build a custom migration runner

Once a project runs multiple environments such as development, staging, and production, manually running SQL scripts quickly becomes a risk. Without a fixed process, nobody can reliably tell which schema change has already been applied on which environment. A migration runner solves this problem by treating every change as its own versioned file and logging the applied state inside the database itself.

Many frameworks already ship their own migration runner, for example Laravel or Symfony with Doctrine. For projects without such a framework, for plain PHP legacy applications, or for cross platform infrastructure scripts, a custom, lightweight migration runner in Bash is worth it, since it needs no additional runtime and works directly with the mysql command line client.

The core advantage of a self written migration runner lies in full control over its behavior: how errors are handled, how a rollback is triggered, how checksums are verified. The following sections build a production ready migration runner step by step.

2. Core concept: migration file, version, and tracking

Every migration consists of two parts: a number that fixes the order, and a descriptive name. A proven naming scheme for a migration runner is 0001_create_users_table.sql, optionally followed by a separate 0001_create_users_table.down.sql file for the rollback. This convention makes the order unambiguous simply by sorting file names, without any extra metadata file.

For the migration runner to know which migrations have already been applied, it needs its own table in the target database that acts as a log. This table stores the version, execution time, and a checksum of the migration file. That way it is always possible to determine exactly which schema state a database is at, regardless of who ran the migration.

3. Creating the tracking table in the database

The tracking table itself is created automatically the first time the migration runner starts, if it does not exist yet. That makes the runner idempotent from the start: running it again on a fresh database works the same way as on one that has already been migrated. The checksum column is essential here, since it later allows detecting migration files that were changed after the fact.

A migration runner should give the tracking table a unique index on the version number. That way the database itself prevents the same migration from accidentally being recorded twice, even if two instances of the script were ever started in parallel.

-- schema_migrations.sql — tracking table for the migration runner
CREATE TABLE IF NOT EXISTS schema_migrations (
  version      VARCHAR(20) NOT NULL,
  name         VARCHAR(255) NOT NULL,
  checksum     CHAR(64) NOT NULL,
  applied_at   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (version)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

4. Discovering and sorting migration files

The migration runner must find every migration file in a fixed directory and process them in ascending order. A common mistake is trusting the filesystem's default alphabetical ordering without explicitly enforcing it with sort. On some filesystems the order returned by find is not guaranteed without -print0 | sort -z.

All .down.sql files must be consistently excluded, since they belong to the rollback path and must never be processed as part of the normal forward migration. A clean migration runner filters these out through a naming pattern instead of relying on a separate directory.

#!/usr/bin/env bash
# discover-migrations.sh — find and sort pending migration files
set -euo pipefail

readonly MIGRATIONS_DIR="./migrations"

discover_pending() {
  local db_name="$1"
  local -a applied=()
  local -a pending=()

  # Load already applied versions into an array
  while IFS= read -r version; do
    applied+=("$version")
  done < <(mysql -N -B -e "SELECT version FROM schema_migrations ORDER BY version;" "$db_name")

  # Find all forward migration files, sorted, excluding rollback files
  while IFS= read -r -d '' file; do
    local base version
    base="$(basename "$file")"
    [[ "$base" == *.down.sql ]] && continue
    version="${base%%_*}"

    if [[ ! " ${applied[*]:-} " == *" $version "* ]]; then
      pending+=("$file")
    fi
  done < <(find "$MIGRATIONS_DIR" -maxdepth 1 -name "*.sql" -print0 | sort -z)

  printf '%s\n' "${pending[@]:-}"
}

5. Checksums for integrity verification

A subtle but dangerous problem: an already applied migration file gets modified afterward, for instance because someone wanted to fix a typo. Without verification, the migration runner would never notice this change, because the tracking table still shows it as already executed. With sha256sum, this risk can be eliminated by comparing the current checksum against the stored one on every run.

If the checksum differs, the migration runner should abort the process immediately with a clear error message, instead of silently ignoring the mismatch. Only this way is it guaranteed that all environments really went through the same migration history, and not just the same file name.

#!/usr/bin/env bash
# verify-checksum.sh — detect tampering with already applied migrations
set -euo pipefail

verify_applied_checksums() {
  local db_name="$1"

  while IFS=$'\t' read -r version stored_checksum; do
    local file
    file="$(find ./migrations -maxdepth 1 -name "${version}_*.sql" ! -name "*.down.sql" | head -n 1)"

    if [[ -z "$file" ]]; then
      echo "[ERROR] Migration file for version ${version} is missing on disk" >&2
      exit 1
    fi

    local current_checksum
    current_checksum="$(sha256sum "$file" | cut -d' ' -f1)"

    if [[ "$current_checksum" != "$stored_checksum" ]]; then
      echo "[ERROR] Checksum mismatch for ${version}: file was modified after being applied" >&2
      exit 1
    fi
  done < <(mysql -N -B -e "SELECT version, checksum FROM schema_migrations;" "$db_name")

  echo "[OK] All applied migrations match their stored checksum"
}

6. Running migrations transactionally

Every migration should run as a single atomic transaction: either both the schema change and the entry in the tracking table are committed, or both are rolled back. The migration runner achieves this by running the migration file's SQL and the insert into schema_migrations within one session using START TRANSACTION and COMMIT.

Important to know: DDL statements such as CREATE TABLE or ALTER TABLE trigger an implicit commit in MySQL and therefore cannot be rolled back together with the tracking insert in the same transaction. A realistic migration runner must be aware of this limitation and instead rely on clear error messages and manual rollback scripts, rather than trusting automatic transactional safety for DDL.

#!/usr/bin/env bash
# apply-migration.sh — run one migration and record it atomically
set -euo pipefail

apply_migration() {
  local db_name="$1"
  local file="$2"

  local base version name checksum
  base="$(basename "$file")"
  version="${base%%_*}"
  name="${base#*_}"
  name="${name%.sql}"
  checksum="$(sha256sum "$file" | cut -d' ' -f1)"

  echo "[RUN] ${version}: ${name}"

  # DML statements can be wrapped in a transaction; DDL auto-commits in MySQL
  mysql "$db_name" <<-SQL
    SOURCE ${file};
    INSERT INTO schema_migrations (version, name, checksum)
    VALUES ('${version}', '${name}', '${checksum}');
SQL

  echo "[OK] ${version} applied and recorded"
}

7. Rollback with down migrations

For every forward migration, a matching .down.sql file should optionally exist that specifically reverses the change. When rolling back, the migration runner first has to determine the most recently applied version from the tracking table, run the matching down file, and afterward remove the entry from schema_migrations.

If a down file is missing, the migration runner should refuse the rollback and print a clear error message, rather than silently doing nothing. Especially with destructive changes like DROP COLUMN, a missing rollback path is a deliberate signal that this migration cannot be reversed in production without data loss.

#!/usr/bin/env bash
# rollback.sh — revert the most recently applied migration
set -euo pipefail

rollback_last() {
  local db_name="$1"

  local last_version
  last_version="$(mysql -N -B -e \
    "SELECT version FROM schema_migrations ORDER BY version DESC LIMIT 1;" "$db_name")"

  if [[ -z "$last_version" ]]; then
    echo "[INFO] No migrations to roll back" >&2
    return 0
  fi

  local down_file
  down_file="$(find ./migrations -maxdepth 1 -name "${last_version}_*.down.sql" | head -n 1)"

  if [[ -z "$down_file" ]]; then
    echo "[ERROR] No down migration found for ${last_version}, refusing to roll back" >&2
    exit 1
  fi

  echo "[ROLLBACK] ${last_version}"
  mysql "$db_name" < "$down_file"
  mysql "$db_name" -e "DELETE FROM schema_migrations WHERE version = '${last_version}';"
  echo "[OK] ${last_version} rolled back"
}

8. Command line interface: up, down, status

A user friendly migration runner offers three simple subcommands: up runs every pending migration, down rolls back the last migration, and status shows which migrations have already been applied and which are still missing. This structure deliberately mirrors established tools like Flyway or golang-migrate, so developers can find their way around immediately.

The status output of a good migration runner should clearly distinguish between applied and pending migrations, ideally color coded in the terminal. That way a developer can see at a glance whether their local database is up to date before starting work.

#!/usr/bin/env bash
# migrate.sh — CLI entry point: up, down, status
set -euo pipefail
source ./lib/discover-migrations.sh
source ./lib/verify-checksum.sh
source ./lib/apply-migration.sh
source ./lib/rollback.sh

readonly DB_NAME="${DB_NAME:?Set DB_NAME environment variable}"

usage() { echo "Usage: $0 {up|down|status}" >&2; exit 1; }

cmd_up() {
  verify_applied_checksums "$DB_NAME"
  while IFS= read -r file; do
    [[ -z "$file" ]] && continue
    apply_migration "$DB_NAME" "$file"
  done < <(discover_pending "$DB_NAME")
}

cmd_status() {
  echo "Applied migrations:"
  mysql "$DB_NAME" -e "SELECT version, name, applied_at FROM schema_migrations ORDER BY version;"
  echo "Pending migrations:"
  discover_pending "$DB_NAME"
}

case "${1:-}" in
  up)     cmd_up ;;
  down)   rollback_last "$DB_NAME" ;;
  status) cmd_status ;;
  *)      usage ;;
esac

9. Migration runner compared to alternatives

A Bash based migration runner is not the only option, but it is the most pragmatic choice for certain setups. Anyone already using a PHP or Java framework with built in migration management should prefer that. For polyglot infrastructure repositories or plain SQL projects without an application framework, a custom migration runner offers maximum control with minimal dependencies.

For many deployment pipelines that already rely on Bash scripts, the effort of building a custom migration runner is small compared to the benefit: one consistent tool for every database repository, regardless of which application language sits on top.

10. Summary

A custom migration runner in Bash makes schema changes traceable across multiple environments, without needing a full framework just to manage SQL files. A tracking table logs applied versions, checksums detect later tampering, and a clean CLI with up, down, and status makes daily use simple.

It remains important to know the limits of transactionality with DDL statements and to design rollback paths deliberately, rather than assuming they exist by default. A migration runner that accounts for these points is a robust, maintainable solution for schema versioning even for smaller teams without a dedicated database framework.

11. FAQ: Database migration runner in Bash