db-migrate.sh

#!/usr/bin/env bash
#
# Run database migrations with the power of bash
#
set -eu -o pipefail
cd "$(dirname "$0")/.."

if [ "$#" -lt 2 ]; then
  1>&2 echo "Usage: $0 postgres://url/db <migration dir>"
  exit 1
fi

db_url="$1"
dir="$2"
migration_table="pg_migrate_test"

if [ ! -d "$dir" ]; then
  1>&2 echo "ERROR: Directory $dir not found"
  exit 1
fi

if ! psql -v ON_ERROR_STOP=1 -c "create table if not exists $migration_table (migration_id text primary key)" "$db_url"; then
  1>&2 echo "ERROR: Could not find/create table $migration_table"
  exit 1
fi

# Space separated
existing_migrations=$(psql -v ON_ERROR_STOP=1 -t -X -A -c "select migration_id from $migration_table order by migration_id asc" "$db_url")

migrated_any=

find $dir -iname '*.sql' | sort | while read migration_path; do
  migration_fn=`basename "$migration_path"`
  migration_id=`echo $migration_fn | grep -Eo '^[0-9]*'`

  if [[ "$existing_migrations" =~ [[:\<:]]$migration_id[[:\>:]] ]]; then
    # Migration has already been run
    continue
  fi

  # Run migration
  if ! psql -v ON_ERROR_STOP=1 -f "$migration_path" "$db_url" > /dev/null; then
    1>&2 echo "FAIL: $migration_fn"
    exit 1
  fi

  echo "$migration_fn: OK"

  psql -v ON_ERROR_STOP=1 -c "insert into $migration_table (migration_id) values ('$migration_id')" "$db_url" > /dev/null

  migrated_any=1
done

if [ -z "$migrated_any" ]; then
  echo "No new migrations to run"
fi