Simple migration script (/bin/sh)

migrations.sh

#!/bin/sh
set -eu

readonly psqlOptions="-h postgres postgres postgres"

initdb () {
	psql $psqlOptions -q -c "CREATE TABLE IF NOT EXISTS __version (id integer PRIMARY KEY, value varchar(255));"

	psql $psqlOptions -q -c "INSERT INTO __version VALUES (1, '') ON CONFLICT DO NOTHING"
}

version () {
	# intentionally unquoted to get rid of whitespaces
	echo $(psql $psqlOptions -t -c "SELECT value FROM __version")
}

updateVersion() {
	local version=$1
	psql $psqlOptions -c "UPDATE __version SET value = '$version'"
}

list () {
	find . -name '*.sql' | while read file; do
		file=${file%.sql}
		echo "${file#./}"
	done | sort -V
}

isNew () {
	local version=$1
	local current=$2

	if [ "$current" = "$version" ]; then
		return 1
	fi

	echo "$current\n$version" | sort -V | head -n 1 | grep -F "$current" >/dev/null
}

filterNew () {
	local current
	current=$(version)
	while read version; do
		if isNew "$version" "$current"; then
			echo "$version"
		fi
	done
}

migrate () {
	while read version; do
		echo "Migration: $version"
		psql $psqlOptions -q -v ON_ERROR_STOP=1 -f "./$version.sql"
		updateVersion "$version"
	done
}

main () {
	if [ $# -gt 0 ]; then
		case "$1" in
			init )
				initdb
				exit
				;;
			list )
				list
				exit
				;;
			new )
				list | filterNew
				exit
				;;
			version )
				version
				exit
				;;
			* )
				exit 1
		esac
	fi

	initdb >/dev/null 2>&1
	list | filterNew | migrate
}

main "$@"