Skip to content

Instantly share code, notes, and snippets.

@vnayar
Created January 8, 2024 08:25
Show Gist options
  • Save vnayar/692d9c0d18dda4057f9a58add3449ebd to your computer and use it in GitHub Desktop.
Save vnayar/692d9c0d18dda4057f9a58add3449ebd to your computer and use it in GitHub Desktop.
#!/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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment