Skip to content

Instantly share code, notes, and snippets.

@ngauthier

ngauthier/migrate.sh

Created May 22, 2017
Embed
What would you like to do?
#!/usr/bin/env bash
set -e
USAGE="Usage: migrate <up|down|new|init>"
# http://www.postgresql.org/docs/9.5/static/libpq-envars.html
export PGDATABASE=${PGDATABASE:-meetspace}
cd `dirname $0`/../migrations
if [[ $(psql -A -t -c "SELECT * FROM information_schema.tables WHERE table_name='migrations'") ]]
then
versions=`psql -A -t -c "SELECT version FROM migrations;" || true`
fi
case $1 in
up)
upversions=`comm -23 <(ls *.up.sql | xargs basename -s .up.sql) <(echo "$versions")`
for v in $upversions; do
echo $v
PAGER= psql --set=ON_ERROR_STOP=true -a -f $v.up.sql
done
;;
down)
downversions=`comm -12 <(ls *.down.sql | xargs basename -s .down.sql) <(echo "$versions") | sort -r`
for v in $downversions; do
echo $v
PAGER= psql --set=ON_ERROR_STOP=true -a -f $v.down.sql
done
;;
new)
date=`date +%Y%m%d%H%M%S`
name=$date-$2
echo $name.up.sql
tee $name.up.sql <<SQL
BEGIN;
/* Your up migration code here */
INSERT INTO migrations (version) VALUES ('$name');
COMMIT;
SQL
echo $name.down.sql
tee $name.down.sql <<SQL
BEGIN;
/* Your down migration code here */
DELETE FROM migrations WHERE version='$name';
COMMIT;
SQL
;;
init)
date=`date +%Y%m%d%H%M%S`
name=$date-create-migrations
echo $name.up.sql
tee $name.up.sql <<SQL
BEGIN;
CREATE TABLE migrations (
version text not null unique,
ran_at timestamp without time zone default now() not null
);
INSERT INTO migrations (version) VALUES ('$name');
COMMIT;
SQL
echo $name.down.sql
tee $name.down.sql <<SQL
BEGIN;
DROP TABLE migrations;
COMMIT;
SQL
;;
*)
echo $USAGE
exit 1
;;
esac
@ngauthier

This comment has been minimized.

Copy link
Owner Author

@ngauthier ngauthier commented May 22, 2017

You can consider this released under MIT license. ❤️

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment