SQL migrations with bash

libmigrations.sh

#!/bin/bash

test "$1" = "--rollback" || test "$1" = "down"
export is_rollback="$?"

if ${no_run_libmigrations_twice:-true}
then
  no_run_libmigrations_twice=false
    
  if [[ -z "$DATABASE" ]]; then read -p "Database? " DATABASE; fi
  if [[ -z "$MYSQLUSER" ]]; then read -p "DB Username? " MYSQLUSER; fi
  if [[ -z "$MYSQLPASSWORD" ]]; then read -p "DB Password? " MYSQLPASSWORD; fi
  if [[ -z "$MYSQLSERVER" ]]; then MYSQLSERVER=localhost; fi

  # Some helpful functions for simple shell based migrations
  function note_migration_up() {
    echo "INSERT INTO migrations (migration) VALUES ('$(basename $0)');";
  }
  function note_migration_down() {
    echo "UPDATE migrations SET migration = CONCAT('Rolled Back ',migration,' on $(date)') WHERE migration = '$(basename $0)';";
  }
  function note_migration_step() {
    echo "UPDATE migrations SET step = '$1' WHERE migration = '$(basename $0)';";
  }

  export DATABASE MYSQLPASSWORD MYSQLSERVER MYSQLUSER=${MYSQLUSER:-$USER} 
  export -f  note_migration_up note_migration_down

fi

migrations.sh

#!/bin/bash
# initial migration for creating a basic migration tracking table

. $(dirname $0)/libmigrations.sh

if [[ -z "$DATABASE" ]]; then read -p "Database? " DATABASE; fi
if [[ -z "$MYSQLSERVER" ]]; then MYSQLSERVER=localhost; fi

if [[ "$1" = "--help" || "$1" = "help" ]]
then
  echo "Step 1: set and export the following environmental variables:"
  echo ""
  echo "DATABASE='mydatabasename'"
  echo "MYSQLSERVER='myhostname'   # defaults to localhost"
  echo "MYSQLUSER='mysqlusername'  # defaults to system's USER"
  echo "MYSQLPASSWORD='mysqlpw'    # use for development; prompts if not set"
  echo ""
  echo "Step 2: run 'migrations.sh' and enter the password when prompted"
  echo ""
  echo "Step 3: copy the layout of an existing migration:"
  echo "  source libmigrations.sh at the top of the script"
  echo "  guard the down-migration part in a conditional: if [[ \$1 = '--rollback' || \$1 = 'down' ]] "
  echo "  put '\$(note_migration_up)' in the SQL before the rest of the up-migration"
  echo "  put '\$(note_migration_down)' in SQL after the rest of the down-migration"
  echo ""
  echo "note_migration_up will prevent the up-migration from being executed repeatedly without first being rolled back"
  echo "(you will see a 'duplicate key' error if you try to repeat a migration)"
  echo ""
  echo "The scripts follow the convention that no news is good news - don't echo anything out unless there is something to diagnose."
  echo "Do not export a MYSQLPASSWORD on shared hosts: by default it will prompt instead."
  exit
fi

if [[ "$1" = "--rollback" || "$1" = "down" ]]
then
mysql -u $MYSQLUSER -h $MYSQLSERVER -p$MYSQLPASSWORD <<EOT
use $DATABASE;
drop table migrations;

-- $(note_migration_down)
EOT

exit

fi

mysql -u $MYSQLUSER -h $MYSQLSERVER -p$MYSQLPASSWORD <<EOT
use $DATABASE;
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


-- Normal migrations will put this line before the rest of the operation
-- So it would cause the operation to abort if the migration had already run.
-- INSERT INTO migrations (migration) VALUES ("${0}"); 

-- deliberately made to mimic Laravel's migration structure
CREATE TABLE migrations (
  migration varchar(255) NOT NULL,
  step varchar(255),
  batch     int(11)      AUTO_INCREMENT,
  PRIMARY KEY (batch),
  UNIQUE KEY unq_migration (migration)
);

$(note_migration_up)
$(note_migration_step done)

EOT