Skip to content

Instantly share code, notes, and snippets.

@abrkn
Created August 7, 2020 09:26
Show Gist options
  • Save abrkn/d2a86ca91911e499e0d39745f71f8c84 to your computer and use it in GitHub Desktop.
Save abrkn/d2a86ca91911e499e0d39745f71f8c84 to your computer and use it in GitHub Desktop.
#!/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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment