migrator.sh

Migrator-Deep-Dive.ipynb

migrator.sh

#!/usr/bin/env bash

# A really simple migration manager for PostgreSQL.
# Useful as an add-on to a project repo that uses postgres
# This is a stripped down version of dogfish
# by Dan Brown <dan@stompydan.net>
# https://github.com/dwb/dogfish

set -e

# These should be set as environment variables or hard-coded here:
HOST="localhost"
USER="postgres"
PASSWORD="postgres"

PORT="5432"
DBNAME="postgres"
CREDS="host=$HOST user=$USER password=$PASSWORD port=$PORT dbname=$DBNAME"

schema_migrations_table="schema_migrations"
migration_id_column="migration_id"
migrations_dir="migrations"



while true; do
  case ${1-} in
    "-h" | "--help")
      while read line; do printf '%s\n' "$line"; done <<END
migrator

usage: migrator migrate|rollback [FINISH_AT_MIGRATION]
       migrator remigrate
       migrator create-migration [MIGRATION_NAME]
       migrator list

      finish_at_migration is the (optional) number of the migration to finish
          processing after
      migration_name is an optional description of the migration

'remigrate' rolls back and re-applies the last migration. Useful for
development.

Commands are sent to the database using unassuming calls to psql.
Hostname and credentials are set using environment variables.

The SQL scripts themselves are named "migrate-version-name.sql" or
"rollback-version-name.sql", where version is the numeric version number
(usually an ISO YMDHms timestamp, without punctuation), and name is whatever
you want. If you don't provide a rollback script for a particular version, no
complaining will happen. You can also provide a rollback script with no migrate
companion if you're feeling really wild.
END
      exit
      ;;
    -*)
      echo -e "unrecognised option '$1'"
      exit 1
      ;;
    *)
      break 2
      ;;
  esac
done

action=${1-}; shift || true
case $action in
  migrate|rollback|remigrate)
    finish_at_version=${1-}; shift || true
    ;;
  create-migration)
    migration_name=${1-}; shift || true
    ;;
  list)
    ;;
esac

if [[ -z $action ]]; then
  echo -e "Action not given. Use one of:\n
  migrator list
  migrator migrate\n
  migrator rollback\n
  migrator remigrate\n
  migrator create-migration"
  exit 1
fi

if ! [[ -d ${migrations_dir} ]]; then
  echo -e "Migrations directory ${migrations_dir} not found"
  exit 1
fi


function available_migrations_to_scripts() {
  set -e
  pushd "${migrations_dir}" >/dev/null
  # TODO: work out how to not use `ls` here: won't deal with newlines in
  # file names and all that classic stuff. But then the regex will filter
  # out any weirdness, so not that bad.
  #
  # Quieten shellcheck for this one, we know about it:
  # shellcheck disable=SC2012
  ls | sed -ne "s/^${action}-\([[:digit:]]\{1,\}\)[-_a-zA-Z0-9]*\.sql$/\1 &/p"
  popd >/dev/null
}

function available_migrations() {
  available_migrations_to_scripts | awk '{print $1}'
}

function available_migration_script_for_id() {
  available_migrations_to_scripts | egrep -m1 "^$1\>" | awk '{print $2}'
}

function call_psql() {
  psql "${CREDS}" --no-psqlrc --single-transaction --quiet --tuples-only --no-align --no-password
  return $?
}

function applied_migrations() {
  set -e
  call_psql <<END
  SELECT ${migration_id_column}
  FROM ${schema_migrations_table}
  ORDER BY ${migration_id_column} ASC;
END
}

function post_apply_sql() {
  if [[ $action == "migrate" ]]; then
    echo "INSERT INTO \"${schema_migrations_table}\" (\"${migration_id_column}\") VALUES ('$1');"
  else
    echo "DELETE FROM \"${schema_migrations_table}\" WHERE \"${migration_id_column}\" = '$1';"
  fi
}

function migrations_to_apply() {
  local comm_cols="-13"
  [[ $action == "rollback" ]] && comm_cols="-12"
  comm ${comm_cols} <(applied_migrations) <(available_migrations)
}

function apply_migration_id() {
  if [[ $action == "migrate" ]]; then
    echo -n Migrating to "$1..."
  else
    echo -n Rolling back "$1..."
  fi
  call_psql <<END
$(< "${migrations_dir}/$(available_migration_script_for_id "$1")")
$(post_apply_sql "$1") 
END
  local result=$?
  [[ $result -eq 0 ]] && echo done.
  return $result
}

function truncate_migrations_if_requested() {
  if [[ -n $finish_at_version ]]; then
    sed -e "/^${finish_at_version}\$/q"
  else
    tee
  fi
}

function migrate() {
  action=$1  # IMPORTANT: THIS IS SET AS A GLOBAL FOR USE IN OTHER FUNCTIONS
  call_psql <<END
  CREATE TABLE IF NOT EXISTS "${schema_migrations_table}" (
    "${migration_id_column}" VARCHAR(128) PRIMARY KEY NOT NULL
  );
END

  if [[ -n $finish_at_version ]] && ! migrations_to_apply | grep -q "^${finish_at_version}\$"; then
    echo -e "Migration ${finish_at_version} would not have been reached"
    exit 1
  fi

  local sort_dir=""
  local rolling_back="false"
  if [[ $action == "rollback" ]]; then
    sort_dir="-r"
    rolling_back="true"
  fi

  for migration_id in $(migrations_to_apply | sort ${sort_dir} | truncate_migrations_if_requested); do
    apply_migration_id "$migration_id"
    # Only roll back the most recent migration.
    # TODO: make rolling back number of migrations configurable
    $rolling_back && break
  done
  echo "Finished $action"
}

case $action in
  remigrate)
    migrate rollback
    migrate migrate
    ;;
  migrate)
    migrate migrate
    ;;
  rollback)
    migrate rollback
    ;;
  create-migration)
    date=$(date +%Y%m%d%H%M%S)
    migration_name=$(echo "${migration_name}" | tr ' ' '-')
    upfile=${migrations_dir}/migrate-${date}-${migration_name}.sql;
    downfile=${migrations_dir}/rollback-${date}-${migration_name}.sql;
    touch "${upfile}" "${downfile}"
    echo "Created
          ${upfile}
          ${downfile}"
    ;;
  list)
    action="migrate"
    echo "available migration:"
    echo "$(available_migrations)"
    action="rollback"
    echo "available rollbacks:"
    echo "$(available_migrations)"
    echo "Applied migrations:"
    echo "$(applied_migrations)"
    ;;
esac