migrate

A simple posix-compliant shell script to facilitate database migrations for psql & mysql

Usage

Create two directories called up and down. These will contain, respectively, the migrations and rollback scripts for your project. If these are not subdirectories of the directory where you are executing the migration script, or if you want to use different names, set the correct paths in the configuration file (see below).

Each migration in the up directory should be matched by an equally named rollback script in the down directory. You can easily create these using the supplied create command, which will timestamp the files for automatic sorting.

Each script should be a valid SQL script for the database you are using and have a filename ending in .sql

Configuration file

Rename the supplied config.sample file to config and edit it with the data required for your database. The following fields need to be set:

• _DBTYPE: The type of database to connect to. Supported values are psql and mysql
• _DBHOST: The ip or hostname the SQL server can be reached on
• _DBPORT: The port number the SQL server can be reached on (generally this is 5432 for psql and 3306 for mysql)
• _DBDB: The database to connect to
• _DBUSER: The user to connect as
• _DBPASS: The password to use when connecting. If you have set up password-less logins in your user account, or your database doesn't require a password, use an empty string
• _DBTABLE: The table used to keep track which migrations have been run. The script will create the table if needed.

There are also two optional settings:

• _DIRUP: The name of the directory containing the migrations. Defaults to 'up'
• _DIRDOWN: The name of the directory containing the rollback scripts. Defaults to 'down'

Commands

Usage:

./migrate.sh <command>

where <command> is one of the following:

create <name>

Creates a new migration and rollback file, with format YYYYMMDDHHIISS_<name>.sql

list

Lists all currently installed migrations

migrate [<steps>| all ]

Installs new migrations.

If <steps> is numeric, will perform <steps> migrations

If 'all' is specified, will perform all available migrations

If nothing is specified, <steps> defaults to '1'

new

Shows all migrations waiting to be deployed

rollback [<steps>| all ]

Similar to migrate, but will roll back the most recent migration(s)

help

Display the help message