shgrate is a simple database schema migration script for MySQL written in Bash. It can be used as general purpose schema migration utility for a project. Let say you're building an application using a framework but this framework does not provide you the schema migration - then you can use shgrate to 'version control' your database.
Most of the migration tools save the migrated actions into the database itself. shgrate takes difference approach where all the migrated script is saved in a directory so all the migrated script can be easily viewed without having to connect to the database.
shgrate needs three directories in order to do its job.
|migrations||Used to store the SQL migration scripts|
|migrated||Used to store actions which has been migrated|
|rollback||Used to store the SQL rollback scripts|
Keep it mind that these directories are configurable via environment variable or config file. But by default shgrate expect these directories are on the same directory as shgrate.
shgrate require Bash (tested with Bash 4.3.11), MySQL client binary and other shell core utilities:
Those shell utilities should be available in most Linux distribution and Unix compatible OS such as Mac OS X - even Windows using cygwin.
Clone the project repository via github:
$ git clone firstname.lastname@example.org/astasoft/shgrate
Another altertive is download the zip tarball and extract it somewhere on your box.
Basic Usage and Examples
Running shgrate with
-h option will give you list of option that shgrate supports.
Usage: ./shgrate.sh [OPTIONS] Where OPTIONS: -a NAME use database NAME -b rollback mode -c FILE read the config file from the FILE -e ENVIRON specify environment name by ENVIRON. Default is 'production' -h print this help and exit -m NAME create a migration file named NAME -o FILE save the log to the FILE -r dry run -v print the shgrate version shgrate is a simple database schema migration for MySQL written in Bash. shgrate is free software licensed under MIT. Visit the project homepage at http://github.com/astasoft/shgrate.
Common steps when using shgrate as migration tools should covered by list below.
- Prepare the database used for the migration
- Create the directories for shgrate
- Create the migration file
- Run the migration
- Rollback the changes
Let's walkthrough the steps above.
1. Prepare the database for the migration
Login to the MySQL server.
mysql> CREATE DATABASE mytestdb;
2. Create the directories for shgrate
On this example we will create these directories on the same directory as shgrate.
$ mkdir migrations migrated rollback
3. Create the migration file
When you create a migration file, shgrate automatically create the corresponded rollback file. You should edit and put your SQL statement on both files.
$ ./shgrate.sh -m create_table_user Migration file: migrations/2016_04_05_21_06_20_create_table_user.sg_migrate.sql. Rollback file: rollback/2016_04_05_21_06_20_create_table_user.sg_migrate.sql.
Below is a sample of the content of migration and rollback file we just created. You can edit the files using your favorite text editor. On the example below I put simple statement to create table called "user".
$ cat migrations/2016_04_05_21_06_20_create_table_user.sg_migrate.sql -- shgrate Migration Script -- Generated by: shgrate v1.0 -- File: 2016_04_05_21_06_20_create_table_user.sg_migrate.sql -- Date: Tue, 05 Apr 2016 21:06:20 +0800 -- Write your SQL migration below this line CREATE TABLE user ( id INT NOT NULL AUTO_INCREMENT, user_email VARCHAR(100) NOT NULL, PRIMARY KEY (id) ) Engine=InnoDB; $ cat rollback/2016_04_05_21_06_20_create_table_user.sg_migrate.sql -- shgrate Rollback Script -- Generated by: shgrate v1.0 -- File: 2016_04_05_21_06_20_create_table_user.sg_migrate.sql -- Date: Tue, 05 Apr 2016 21:06:20 +0800 -- Write your SQL rolllback migration below this line DROP TABLE user;
4. Run the migration
Before running the migration it's good practice to see what shgrate would execute by running in dry run mode using
-r option. Option
-a tells shgrate the name of database to use.
$ ./shgrate -a mytestdb -r Migrating 2016_04_05_21_06_20_create_table_user.sg_migrate.sql...done. >> Contents of file migrations/2016_04_05_21_06_20_create_table_user.sg_migrate.sql: -- shgrate Migration Script -- Generated by: shgrate v1.0 -- File: 2016_04_05_21_06_20_create_table_user.sg_migrate.sql -- Date: Tue, 05 Apr 2016 21:06:20 +0800 -- Write your SQL migration below this line CREATE TABLE user ( id INT NOT NULL AUTO_INCREMENT, user_email VARCHAR(100) NOT NULL, PRIMARY KEY (id) ) Engine=InnoDB;
After you're sure that all the statement is correct, you can proceed the real migration.
$ ./shgrate -a mytestdb Migrating 2016_04_05_21_06_20_create_table_user.sg_migrate.sql...done.
Check the database to see table "user" has been successfully created or not.
5. Rollback the changes
Doing the rollback is almost the same as doing the migration, you just need to use
-b option. Let's try to rollback the changes that we've done before but let's do it in dry run mode first.
$ ./shgrate -a mytestdb -b -r Rollback 2016_04_05_21_06_20_create_table_user.sg_migrate.sql...done. >> Contents of file migrated/production/2016_04_05_21_06_20_create_table_user.sg_migrate.sql: -- shgrate Rollback Script -- Generated by: shgrate v1.0 -- File: 2016_04_05_21_06_20_create_table_user.sg_migrate.sql -- Date: Tue, 05 Apr 2016 21:06:20 +0800 -- Write your SQL rolllback migration below this line DROP TABLE user;
Everything seems as expected do the real rollback.
$ ./shgrate -a mytestdb -b Rollback 2016_04_05_21_06_20_create_table_user.sg_migrate.sql...done.
To turn on debugging mode pass environment variable SG_DEBUG=true to shgrate. Output of the debug always saved to the log file also.
$ SG_DEBUG=true ./shgrate.sh -r -b -a shgrate DEBUG: Running in DRY RUN mode DEBUG: Using database name shgrate. DEBUG: Using MySQL client config file /home/astadev/.my.cnf. DEBUG: Migration directory is set to migrations. DEBUG: Migrated directory is set to migrated DEBUG: Rollback directory is set to rollback. DEBUG: Getting list of rollback files in migrated/production directory. Rollback 2016_04_03_23_36_49_create_table_foo.sg_migrate.sql...done. >> Contents of file migrated/production/2016_04_03_23_36_49_create_table_foo.sg_migrate.sql: -- shgrate Rollback Script -- Generated by: shgrate v1.0 -- File: 2016_04_03_23_36_49_create_table_foo.sg_migrate.sql -- Date: Sun, 03 Apr 2016 23:36:49 +0800 -- Write your SQL rolllback migration below this line DROP TABLE foo;
Using config file
Example of a shgrate config file.
$ cat /home/user/conf/shgrate-dev.env.config # shgrate sample configuration file # --------------------------------- # Suffix migration for the shgrate SG_MIGRATE_SUFFIX="sg_dev.sql" # Path to the log file SG_LOG_FILE="shgrate.log" # Environment for production e.g: 'production', 'development', 'testing' SG_ENVIRONMENT="development" # Name of the database SG_DB_NAME="testdb" # MySQL client configuration file # Path to your main or custom ~/.my.cnf SG_MYSQL_CONFIG_FILE="~/.my.cnf" # Migration directory SG_MIGRATION_DIR="/home/user/cool-project/migrations" # Migrated directory SG_MIGRATED_DIR="/home/user/cool-project/migrated" # Rollback directory SG_ROLLBACK_DIR="/home/user/cool-project/rollback"
Then tells shgrate to use the config file instead.
$ ./shgrate -c /home/user/conf/shgrate-dev.env.config
shgrate is written by Rio Astamal email@example.com
shgrate is open source licensed under MIT license.