Shell Scripting — Database Centric: Database Migrations with Shell Scripts

# ── Directory structure ───────────────────────────────────
# migrations/
#   V001__create_users_table.sql
#   V002__add_email_index.sql
#   V003__add_orders_table.sql
#   V004__add_user_plan_column.sql

# Naming: V{version}__{description}.sql
# Version is zero-padded, sequential
# Double underscore separates version from description

# ── Sample migration file: V003__add_orders_table.sql ─────
# -- Migration: V003 — Add orders table
# -- Author: Vriddh Team
# -- Date: 2026-05-01

# CREATE TABLE orders (
#   id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
#   user_id     BIGINT UNSIGNED NOT NULL,
#   total       DECIMAL(10,2)   NOT NULL DEFAULT 0.00,
#   status      VARCHAR(20)     NOT NULL DEFAULT 'pending',
#   created_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
#   FOREIGN KEY (user_id) REFERENCES users(id)
# );

# INSERT INTO schema_migrations (version, description, applied_at)
# VALUES ('003', 'add_orders_table', NOW());

# ── Create tracking table ─────────────────────────────────
mysql --defaults-file=/etc/myapp/mysql.conf myapp <<SQL
CREATE TABLE IF NOT EXISTS schema_migrations (
  version     VARCHAR(10)  NOT NULL PRIMARY KEY,
  description VARCHAR(255) NOT NULL,
  checksum    VARCHAR(64)  NOT NULL,
  applied_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  applied_by  VARCHAR(100) NOT NULL DEFAULT USER()
);
SQL

#!/usr/bin/env bash
# migrate.sh — Database migration runner

set -euo pipefail

MIGRATIONS_DIR="${1:-migrations}"
DRY_RUN="${DRY_RUN:-false}"
DB_CONF="/etc/myapp/mysql.conf"
DB_NAME="myapp"

db() { mysql --defaults-file="${DB_CONF}" -BNs -D "${DB_NAME}" -e "$1"; }
db_file() { mysql --defaults-file="${DB_CONF}" -D "${DB_NAME}" < "$1"; }

echo "=== Database Migration Runner${DRY_RUN:+ (DRY RUN)} ==="

# Ensure tracking table exists
db "CREATE TABLE IF NOT EXISTS schema_migrations (
  version VARCHAR(10) PRIMARY KEY,
  description VARCHAR(255) NOT NULL,
  checksum VARCHAR(64) NOT NULL,
  applied_at DATETIME NOT NULL DEFAULT NOW()
)"

APPLIED=0
SKIPPED=0

for file in $(ls -1 "${MIGRATIONS_DIR}"/V*.sql | sort); do
  filename=$(basename "${file}")
  version=$(echo "${filename}" | sed -E 's/^V([0-9]+)__.*/\1/')
  desc=$(echo "${filename}" | sed -E 's/^V[0-9]+__(.*)\.sql/\1/')
  checksum=$(sha256sum "${file}" | cut -d' ' -f1)

  # Check if already applied
  existing=$(db "SELECT checksum FROM schema_migrations WHERE version='${version}'")
  if [[ -n "${existing}" ]]; then
    if [[ "${existing}" != "${checksum}" ]]; then
      echo "ERROR: Migration V${version} checksum mismatch!" >&2
      exit 1
    fi
    echo "  SKIP  V${version} — already applied"
    (( SKIPPED++ ))
    continue
  fi

  echo "  APPLY V${version}: ${desc}"

  if "${DRY_RUN}"; then
    echo "    [DRY RUN] Would execute: ${file}"
  else
    db_file "${file}"
    db "INSERT INTO schema_migrations (version,description,checksum)
        VALUES ('${version}','${desc}','${checksum}')"
    echo "    ✔ Applied"
  fi
  (( APPLIED++ ))
done

echo "  Applied: ${APPLIED} | Skipped: ${SKIPPED}"

# ── Show migration status ─────────────────────────────────
db() { mysql --defaults-file="${DB_CONF}" -BNs -D myapp -e "$1"; }

echo "Applied migrations:"
db "SELECT version,description,applied_at FROM schema_migrations ORDER BY version" | \
  awk -F'\t' '{ printf "  V%-5s %-40s %s\n", $1, $2, $3 }'

echo ""
echo "Pending migrations:"
for f in migrations/V*.sql; do
  v=$(basename "${f}" | sed -E 's/^V([0-9]+)__.*/\1/')
  applied=$(db "SELECT COUNT(*) FROM schema_migrations WHERE version='${v}'")
  [[ "${applied}" -eq 0 ]] && echo "  PENDING: $(basename "${f}")"
done

# ── Rollback last migration ───────────────────────────────
# Convention: for V003__add_orders.sql, have V003__add_orders.rollback.sql
rollback_last() {
  last_version=$(db "SELECT version FROM schema_migrations ORDER BY version DESC LIMIT 1")
  rollback_file="migrations/V${last_version}"*".rollback.sql"

  [[ -f "${rollback_file}" ]] || { echo "No rollback file for V${last_version}"; return 1; }

  echo "Rolling back V${last_version}..."
  mysql --defaults-file="${DB_CONF}" -D myapp < "${rollback_file}"
  db "DELETE FROM schema_migrations WHERE version='${last_version}'"
  echo "  ✔ Rolled back V${last_version}"
}