run-migrations.sh

#!/bin/bash

# Runs versioned database SQL migrations on a Postgres database, running them in order and only
# once.
# Mostly compatible with Flyway: https://documentation.red-gate.com/fd/migrations-184127470.html

function usage() {
    echo "Usage: $0 [options]

Searches for database migration files in './migrations/' with names in the form
'V123__description.sql'.  Migrations are applied consistently in numerical order, thus new files
with older versions will be ignored.

The following environment variables are required:
- DB_HOST
- DB_PORT
- DB_USER
- DB_PASSWORD
- DB_DATABASE

Options:
  -o      Permit older version numbers to be applied out of order, e.g. allow 'V2__a.sql'
          to be applied even if 'V3__b.sql' has already been applied.
" 1>&2
    exit 1
}

# Check if required environment variables are present. If not, skip further processing.
if [[ -z "$DB_HOST" || -z "$DB_PORT" || -z "$DB_USER" || -z "$DB_PASSWORD" || -z "$DB_DATABASE" ]] ; then
    echo "Missing 'DB_' environment variables, assuming migrations not desired."
    exit 0
fi

baseDir="$(dirname $0)"
cd $baseDir

# Indicates whether new files with older version numbers should be applied or not.
outOfOrder=0

# So far we have only one option.
while getopts ":o" arg; do
    case "${arg}" in
        o)
            outOfOrder=1
            ;;
        *)
            usage
            ;;
    esac
done
shift $((OPTIND-1))

# Invoke 'psql', but set a bunch of options needed to connect to a server.
function psql_func() {
    PGPASSWORD="$DB_PASSWORD" psql --host="$DB_HOST" --port="$DB_PORT" --user="$DB_USER" "$DB_DATABASE" \
              -v ON_ERROR_STOP=1 "$@"
}

# Assure that the migration table itself exists.
echo "Assuring table 'schema_history' exists..."

psql_func -c "
CREATE TABLE IF NOT EXISTS public.schema_history (
  version SERIAL PRIMARY KEY,
  filename VARCHAR,
  md5sum VARCHAR(32),
  created TIMESTAMP
);"

baseVersion=$(psql_func -t -c "select version from schema_history order by version desc limit 1;" | tr -d '[:blank:]')
baseVersion=${baseVersion:-0}
echo "Database migration base version: $baseVersion"

# Computes the MD5 sum of a given filename.
function getMd5() {
    local filename="$1"
    md5sum "$filename" | cut -f1 -d' '
}

# Extracts the version component of a filename in the form "V123__description.sql"
# Outputs: 123   V123__description.sql
function sortFilesByVersion() {
    local pattern="$1"
    ls $pattern | sed -nE 's#^(.*/)?V([[:digit:]]+)__(.*).sql$#\2\t\0#; T; p' | sort -n
}

# Go through the list of migrations (in version order) and see if they need to be applied.
sortFilesByVersion "migrations/*.sql" |
    while read version filename extra ; do
        # Skip the migration if it is below the baseline.
        if [[ $outOfOrder -eq 0 && $version -le baseVersion ]] ; then
            echo "Skipping migration $version - $filename (below base version)"
            continue
        fi
        migrationMd5=$(getMd5 $filename)
        dbMd5=$(psql_func -t -c "SELECT md5sum FROM schema_history WHERE version=$version" | tr -d '[:blank:]')
        # Check if this migration exists already.
        if [[ -n "$dbMd5" ]] ; then
            echo "Found existing migration..."
            # If so, we need to compare md5 values and ignore if unchanged or flag an error.
            if [[ "$migrationMd5" != "$dbMd5" ]] ; then
                echo "Migration file '$filename' hash does NOT match already applied version:"
                echo "  Local Version:    '$migrationMd5'"
                echo "  Database Version: '$dbMd5'"
                exit 1
            fi
            echo "- Migration $version -- $filename... current"
        else
            # This is a brand new migration, insert it into the DB.
            echo "- Migration $version -- $filename... applying"
            {
                echo "BEGIN;"
                cat $filename
                echo "INSERT INTO public.schema_history (version, filename, md5sum, created) VALUES (:'version', :'filename', :'md5sum', NOW());"
                echo "COMMIT;"
            } | psql_func -v version="$version" -v filename="$filename" -v md5sum="$migrationMd5" -t \
                    && echo "- Migration $version -- $filename... applied" \
                    || { echo "- Migration failed!" ; exit 1 ; }
        fi
    done