migrator.sh

Migrator-Deep-Dive.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This is a deep dive to explain the different parts of the migrator.sh bash script.\n",
    "\n",
    "If you want some bash basics, there are lots of bash cheat sheets like this one: https://devhints.io/bash\n",
    "\n",
    "This doc is organized as explanation block followed by code block."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Setup, comment at the top:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#!/usr/bin/env bash\n",
    "\n",
    "# A really simple migration manager for PostgreSQL.\n",
    "# Useful as an add-on to a project repo that uses postgres\n",
    "# This is a stripped down version of dogfish\n",
    "# by Dan Brown <dan@stompydan.net>\n",
    "# https://github.com/dwb/dogfish"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Flag to stop immediately on error (makes bash act more like Python):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "set -e"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Temporary way to set up DB creds:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# These should be set as environment variables or hard-coded here:\n",
    "HOST=\"localhost\"\n",
    "USER=\"postgres\"\n",
    "PASSWORD=\"postgres\"\n",
    "\n",
    "PORT=\"5432\"\n",
    "DBNAME=\"postgres\"\n",
    "CREDS=\"host=$HOST user=$USER password=$PASSWORD port=$PORT dbname=$DBNAME\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Set some global variables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "schema_migrations_table=\"schema_migrations\"\n",
    "migration_id_column=\"migration_id\"\n",
    "migrations_dir=\"migrations\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Standard argument handling in bash. The only option really left is \"-h\".\n",
    "\n",
    "First look for known flags and break once an argument is hit that does not start with \"-\".\n",
    "\n",
    "`${1-}` means unpack the first argument but don't fail if the value is missing.\n",
    "https://stackoverflow.com/questions/32674713/what-does-the-dash-after-variable-names-do-here/32674802"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "while true; do\n",
    "  case ${1-} in\n",
    "    \"-h\" | \"--help\")\n",
    "      while read line; do printf '%s\\n' \"$line\"; done <<END\n",
    "migrator\n",
    "\n",
    "usage: migrator migrate|rollback [FINISH_AT_MIGRATION]\n",
    "       migrator remigrate\n",
    "       migrator create-migration [MIGRATION_NAME]\n",
    "       migrator list\n",
    "\n",
    "      finish_at_migration is the (optional) number of the migration to finish\n",
    "          processing after\n",
    "      migration_name is an optional description of the migration\n",
    "\n",
    "'remigrate' rolls back and re-applies the last migration. Useful for\n",
    "development.\n",
    "\n",
    "Commands are sent to the database using unassuming calls to psql.\n",
    "Hostname and credentials are set using environment variables.\n",
    "\n",
    "The SQL scripts themselves are named \"migrate-version-name.sql\" or\n",
    "\"rollback-version-name.sql\", where version is the numeric version number\n",
    "(usually an ISO YMDHms timestamp, without punctuation), and name is whatever\n",
    "you want. If you don't provide a rollback script for a particular version, no\n",
    "complaining will happen. You can also provide a rollback script with no migrate\n",
    "companion if you're feeling really wild.\n",
    "END\n",
    "      exit\n",
    "      ;;\n",
    "    -*)\n",
    "      echo -e \"unrecognised option '$1'\"\n",
    "      exit 1\n",
    "      ;;\n",
    "    *)\n",
    "      break 2\n",
    "      ;;\n",
    "  esac\n",
    "done"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Handle the non-flag arguments, round 1 (round 2 happens at the end)\n",
    "\n",
    "`shift` moves on to the next argument so what was `${2}` is now `${1}`.\n",
    "https://unix.stackexchange.com/questions/174566/what-is-the-purpose-of-using-shift-in-shell-scripts\n",
    "\n",
    "I think `|| true` is a construct to prevent errors, but I'm not really sure... if it ain't broke don't fix it :)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "action=${1-}; shift || true\n",
    "case $action in\n",
    "  migrate|rollback|remigrate)\n",
    "    finish_at_version=${1-}; shift || true\n",
    "    ;;\n",
    "  create-migration)\n",
    "    migration_name=${1-}; shift || true\n",
    "    ;;\n",
    "  list)\n",
    "    ;;\n",
    "esac"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Stop if one of the known actions is not given and offer helpful feedback as a message."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "if [[ -z $action ]]; then\n",
    "  echo -e \"Action not given. Use one of:\\n\n",
    "  migrator list\n",
    "  migrator migrate\\n\n",
    "  migrator rollback\\n\n",
    "  migrator remigrate\\n\n",
    "  migrator create-migration\"\n",
    "  exit 1\n",
    "fi"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Stop if the migrations directory is missing."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "if ! [[ -d ${migrations_dir} ]]; then\n",
    "  echo -e \"Migrations directory ${migrations_dir} not found\"\n",
    "  exit 1\n",
    "fi"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Find the migrations (action is a global and will be \"migration\" or \"rollback\") within the \"migrations\" folder.\n",
    "\n",
    "pushd and popd is a more convenient way to manage changing directories than cd\n",
    "https://www.tecmint.com/pushd-and-popd-linux-filesystem-navigation/\n",
    "\n",
    "sed is essentially a \"stream editor\" with regex powers for the command line.\n",
    "https://www.gnu.org/software/sed/manual/sed.html\n",
    "\n",
    "This pattern is the \"duplicate\" pattern, \"sed 's/unix/linux/p' geekfile.txt\" as explained here:\n",
    "https://www.geeksforgeeks.org/sed-command-in-linux-unix-with-examples/\n",
    "\n",
    "This might also help:\n",
    "https://explainshell.com/explain?cmd=ls+%7C+sed+-ne+%22s%2F%5E%24%7Baction%7D-%5C%28%5B%5B%3Adigit%3A%5D%5D%5C%7B1%2C%5C%7D%5C%29%5B-_a-zA-Z0-9%5D*%5C.sql%24%2F%5C1+%26%2Fp%22\n",
    "\n",
    "As usual, regex is black magic. This is a version of the regex you can drop into an online regex explainer like regexr.org:\n",
    "\n",
    "^migrate-\\([[:digit:]]\\{1,\\}\\)[-_a-zA-Z0-9]*\\.sql$\n",
    "\n",
    "This is a sample output from this function, given these are the files in the migrations folder and action=migrate:\n",
    "* migrate-20200306033831-intial-migration.sql\n",
    "* migrate-20200306034031-add-column-to-table.sql\n",
    "* rollback-20200306033831-intial-migration.sql\n",
    "* rollback-20200306034031-add-column-to-table.sql\n",
    "\n",
    "20200306033831 migrate-20200306033831-intial-migration.sql\n",
    "\n",
    "20200306034031 migrate-20200306034031-add-column-to-table.sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "function available_migrations_to_scripts() {\n",
    "  set -e\n",
    "  pushd \"${migrations_dir}\" >/dev/null\n",
    "  # TODO: work out how to not use `ls` here: won't deal with newlines in\n",
    "  # file names and all that classic stuff. But then the regex will filter\n",
    "  # out any weirdness, so not that bad.\n",
    "  #\n",
    "  # Quieten shellcheck for this one, we know about it:\n",
    "  # shellcheck disable=SC2012\n",
    "  ls | sed -ne \"s/^${action}-\\([[:digit:]]\\{1,\\}\\)[-_a-zA-Z0-9]*\\.sql$/\\1 &/p\"\n",
    "  popd >/dev/null\n",
    "}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "functions that create the set of migrations using the above function\n",
    "\n",
    "awk '{print $1}' gets the first element of each line (separated by whitespace)\n",
    "\n",
    "So available_migrations gives:\n",
    "\n",
    "20200306033831\n",
    "\n",
    "20200306034031"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "function available_migrations() {\n",
    "  available_migrations_to_scripts | awk '{print $1}'\n",
    "}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "available_migration_script_for_id takes an id (entry from the output of line from available_migrations) and returns the filename:\n",
    "\n",
    "available_migration_script_for_id 20200306033831\n",
    "\n",
    "-->\n",
    "\n",
    "migrate-20200306033831-intial-migration.sql\n",
    "\n",
    "This works by using `egrep` (search tool) with the flag `-m1` (stop after 1 word is found) with the argument `^$1\\>` which says look for something that starts with the input (`^` follwed by `$1`) and is a whole word/ ends with a space (`\\>`)\n",
    "\n",
    "`awk '{print $2}'` selects the second word"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "function available_migration_script_for_id() {\n",
    "  available_migrations_to_scripts | egrep -m1 \"^$1\\>\" | awk '{print $2}'\n",
    "}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Wrapper around psql, automatically passes in creds. Not sure about all the options, but if it ain't broke...\n",
    "\n",
    "return $? just sets the output of this command to the output from the last command, not really sure why it is used here."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "function call_psql() {\n",
    "  psql \"${CREDS}\" --no-psqlrc --single-transaction --quiet --tuples-only --no-align --no-password\n",
    "  return $?\n",
    "}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Query the `schema_migrations` table for all the previously run migrations.\n",
    "\n",
    "\n",
    "`<<XXX  ....  XXX` is a construct for wrapping (multiple lines of) text as a \"file\" so it can be passed to a command that reads a file (like `cat`).\n",
    "\n",
    "Ex:\n",
    "\n",
    "    cat <<END\n",
    "    Hi.\n",
    "    ho.\n",
    "    END\n",
    "\n",
    "Prints\n",
    "          \n",
    "    Hi.\n",
    "    ho."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "function applied_migrations() {\n",
    "  set -e\n",
    "  call_psql <<END\n",
    "  SELECT ${migration_id_column}\n",
    "  FROM ${schema_migrations_table}\n",
    "  ORDER BY ${migration_id_column} ASC;\n",
    "END\n",
    "}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Store the migration or rollback information in the schema_migrations table (to be used after the migration is completed).\n",
    "\n",
    "INSERT after a migration, DELETE after a rollback."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "function post_apply_sql() {\n",
    "  if [[ $action == \"migrate\" ]]; then\n",
    "    echo \"INSERT INTO \\\"${schema_migrations_table}\\\" (\\\"${migration_id_column}\\\") VALUES ('$1');\"\n",
    "  else\n",
    "    echo \"DELETE FROM \\\"${schema_migrations_table}\\\" WHERE \\\"${migration_id_column}\\\" = '$1';\"\n",
    "  fi\n",
    "}\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Figure out which migrations to actually apply by comparin (using `comm`) the outputs from `applied_migrations` and `available_migrations`.\n",
    "\n",
    "For migrations, use `\"-13\"` which means \"Print only lines from file2 that are not present in file1.\"\n",
    "\n",
    "For rollbacks, uss `\"-12\"` which means \"Print only lines present in both file1 and file2.\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "function migrations_to_apply() {\n",
    "  local comm_cols=\"-13\"\n",
    "  [[ $action == \"rollback\" ]] && comm_cols=\"-12\"\n",
    "  comm ${comm_cols} <(applied_migrations) <(available_migrations)\n",
    "}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Apply a given migration or rollback based on an ID.\n",
    "\n",
    "Runs psql on the given .sql file for that ID, then runs post_apply_sql.\n",
    "\n",
    "Example:\n",
    "\n",
    "apply_migration_id 20200306033831\n",
    "\n",
    "Migrating to 20200306033831\n",
    "done."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "function apply_migration_id() {\n",
    "  if [[ $action == \"migrate\" ]]; then\n",
    "    echo -n Migrating to \"$1...\"\n",
    "  else\n",
    "    echo -n Rolling back \"$1...\"\n",
    "  fi\n",
    "  call_psql <<END\n",
    "$(< \"${migrations_dir}/$(available_migration_script_for_id \"$1\")\")\n",
    "$(post_apply_sql \"$1\") \n",
    "END\n",
    "  local result=$?\n",
    "  [[ $result -eq 0 ]] && echo done.\n",
    "  return $result\n",
    "}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Use sed to stop once the version is found.\n",
    "\n",
    "Example:\n",
    "\n",
    "    truncate_migrations_if_requested \"ghi\" <<END\n",
    "    abc\n",
    "    def\n",
    "    ghi\n",
    "    jkl\n",
    "    END\n",
    "\n",
    "Prints:\n",
    "\n",
    "    abc\n",
    "    def\n",
    "    ghi\n",
    "\n",
    "If the version is not found, will just return everything."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "function truncate_migrations_if_requested() {\n",
    "  if [[ -n $finish_at_version ]]; then\n",
    "    sed -e \"/^${finish_at_version}\\$/q\"\n",
    "  else\n",
    "    tee\n",
    "  fi\n",
    "}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Putting it all together, this is the function that actually gets called via `migrator migrate` and `migrator rollback`.\n",
    "\n",
    "Steps:\n",
    "* set `action` variable to either \"migrate\" or \"rollback\"\n",
    "* create the schema_migrations if it does not exist in postgres\n",
    "* Stop early if a `finish_at_version` argument is passed but no file matches it in the migrations directory.\n",
    "* Set `sort_dir` and `rolling_back` variables based on the `$action`\n",
    "* Get all the migrations to apply (by calling `migrations_to_apply`)\n",
    "* Sort the migrations to apply\n",
    "* Truncate up to the specified version if `finish_at_version` is supplied\n",
    "* Loop over these and call `apply_migration_id` on each one\n",
    "* Currently only allow rollbacks one-by-one"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "function migrate() {\n",
    "  action=$1  # IMPORTANT: THIS IS SET AS A GLOBAL FOR USE IN OTHER FUNCTIONS\n",
    "  call_psql <<END\n",
    "  CREATE TABLE IF NOT EXISTS \"${schema_migrations_table}\" (\n",
    "    \"${migration_id_column}\" VARCHAR(128) PRIMARY KEY NOT NULL\n",
    "  );\n",
    "END\n",
    "\n",
    "  if [[ -n $finish_at_version ]] && ! migrations_to_apply | grep -q \"^${finish_at_version}\\$\"; then\n",
    "    echo -e \"Migration ${finish_at_version} would not have been reached\"\n",
    "    exit 1\n",
    "  fi\n",
    "\n",
    "  local sort_dir=\"\"\n",
    "  local rolling_back=\"false\"\n",
    "  if [[ $action == \"rollback\" ]]; then\n",
    "    sort_dir=\"-r\"\n",
    "    rolling_back=\"true\"\n",
    "  fi\n",
    "\n",
    "  for migration_id in $(migrations_to_apply | sort ${sort_dir} | truncate_migrations_if_requested); do\n",
    "    apply_migration_id \"$migration_id\"\n",
    "    # Only roll back the most recent migration.\n",
    "    # TODO: make rolling back number of migrations configurable\n",
    "    $rolling_back && break\n",
    "  done\n",
    "  echo \"Finished $action\"\n",
    "}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Switch to call the correct function for the given action.\n",
    "\n",
    "`create-migration <name>` calls `touch` to generate filenames like `migrate-<generated date>-<name>.sql` and `rollback-<generated date>-<name>.sql`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "case $action in\n",
    "  remigrate)\n",
    "    migrate rollback\n",
    "    migrate migrate\n",
    "    ;;\n",
    "  migrate)\n",
    "    migrate migrate\n",
    "    ;;\n",
    "  rollback)\n",
    "    migrate rollback\n",
    "    ;;\n",
    "  create-migration)\n",
    "    date=$(date +%Y%m%d%H%M%S)\n",
    "    migration_name=$(echo \"${migration_name}\" | tr ' ' '-')\n",
    "    upfile=${migrations_dir}/migrate-${date}-${migration_name}.sql;\n",
    "    downfile=${migrations_dir}/rollback-${date}-${migration_name}.sql;\n",
    "    touch \"${upfile}\" \"${downfile}\"\n",
    "    echo \"Created\n",
    "          ${upfile}\n",
    "          ${downfile}\"\n",
    "    ;;\n",
    "  list)\n",
    "    action=\"migrate\"\n",
    "    echo \"available migration:\"\n",
    "    echo \"$(available_migrations)\"\n",
    "    action=\"rollback\"\n",
    "    echo \"available rollbacks:\"\n",
    "    echo \"$(available_migrations)\"\n",
    "    echo \"Applied migrations:\"\n",
    "    echo \"$(applied_migrations)\"\n",
    "    ;;\n",
    "esac"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}

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