Migr8.rb is a database schema version management tool.

• Easy to install, easy to setup, and easy to start
• No configuration file; instead, only two environment variables
• Designed carefully to suit Git or Mercurial
• Supports SQLite3, PostgreSQL, and MySQL
• Written in Ruby (>= 1.8)

1. Donwload migr8.rb.

    $ curl -Lo migr8.rb https://bit.ly/migr8_rb
    $ chmod a+x migr8.rb
    ### or
    $ gem install migr8
   

2. Set environment variables: $MIGR8_COMMAND and $MIGR8_EDITOR.

    $ export MIGR8_COMMAND="sqlite3 dbfile1"            # for SQLite3
    $ export MIGR8_COMMAND="psql -q -U user1 dbname1"   # for PostgreSQL
    $ export MIGR8_COMMAND="mysql -s -u user1 dbname1"  # for MySQL
   
    $ export MIGR8_EDITOR="open -a TextMate"     # for TextMate (MacOSX)
    $ export MIGR8_EDITOR="emacsclient"          # for Emacs
    $ export MIGR8_EDITOR="vim"                  # for Vim
   

3. Create managiment files and table.

    $ ./migr8.rb init         # create files in current directory,
                              # and create a table in DB.
   

4. Now you can manage DB schema versions.

    $ ./migr8.rb                                 # show current status
    $ ./migr8.rb new -m "create 'users' table"   # create a migration
           # or  ./migr8.rb new --table=users
    $ ./migr8.rb                                 # show status again
    $ ./migr8.rb up                              # apply migration
    $ ./migr8.rb                                 # show status again
    $ ./migr8.rb hist                            # list history
   

5. You may got confliction error when git rebase or git pull. In this case, you must resolve it by hand. (This is intended design.)

    $ git rebase master         # confliction!
    $ ./migr8.rb hist -o        # open 'migr8/history.txt', and
                                # resolve confliction manually
    $ ./migr8.rb hist           # check whether history file is valid
    $ git add migr8/history.txt
    $ git rebase --continue
   

(!!Attention!! this is experimental feature and may be changed in the future.)

It is possible to embed eRuby code into up and down scripts.

Syntax:

• <% ... %> : Ruby statement
• <%= ... %> : Ruby expression, escaping ' into '' (or \' on MySQL)
• <%== ... %> : Ruby expression, no escaping

For example:

vars:
  - table: users

up: |
  insert into ${table}(name) values
  <% comma = "  " %>
  <% for name in ["Haruhi", "Mikuru", "Yuki"] %>
    <%= comma %>('<%= name %>')
    <% comma = ", " %>
  <% end %>
  ;

down: |
  <% for name in ["Haruhi", "Mikuru", "Yuki"] %>
  delete from ${table} where name = '<%= name %>';
  <% end %>

The above is the same as the following:

up: |
  insert into users(name) values
      ('Haruhi')
    , ('Mikuru')
    , ('Yuki')
  ;

down: |
  delete from users where name = 'Haruhi';
  delete from users where name = 'Mikuru';
  delete from users where name = 'Yuki';

In eRuby code, values in vars are available as instance variables. For example:

version:     uhtu4853
desc:        register members
author:      kyon
vars:
  - table:   users
  - members: [Haruhi, Mikuru, Yuki]

up: |
  <% for member in @members %>
  insert into ${table}(name) values ('<%= member %>');
  <% end %>

down: |
  <% for member in @members %>
  delete from ${table} where name = '<%= member %>';
  <% end %>

If you want to see up and down scripts rendered, run migr8.rb show action. For example:

$ ./migr8.rb show uhtu4853
version:     uhtu4853
desc:        register members
author:      kyon
vars:
  - table:     "users"
  - members:   ["Haruhi", "Mikuru", "Yuki"]

up: |
  insert into users(name) values ('Haruhi');
  insert into users(name) values ('Mikuru');
  insert into users(name) values ('Yuki');

down: |
  delete from users where name = 'Haruhi';
  delete from users where name = 'Mikuru';
  delete from users where name = 'Yuki';

Notice that migration file using eRuby code is not compatible with other Migr8 implemtation.

• migr8.rb up -a applys all migrations, while migr8.rb up applys a migration.

• migr8.rb -D up saves SQL executed into migr8/history.txt file.

• migr8.rb redo is equivarent to migr8.rb down; migr8.rb up.

• migr8.rb new -p generates migration file with plain skeleton, and migr8.rb new --table=name generates with table name.

• migr8.rb unapply -x unapplies migration which is applied in DB but corresponding migration file doesn't exist. (Describing in detail, migr8.rb unapply -x abcd1234 runs down script in _migr_history table, while migr8.rb unapply abcd1234 runs down script in migr8/migrations/abcd1234.yaml file.) This may help you when switching Git/Hg branch.

• migr8.rb generates sql file and run it with sql command such as psql (PostgreSQL), sqlite3 (SQLite3) or mysql (MySQL). Therefore you can use non-sql command in migration file. For example:

    up: |
      -- read data from CSV file and insert into DB (PostgreSQL)
      \copy table1 from 'file1.csv' with csv;
  

• MySQL doesn't support transactional DDL. It will cause troubles when you have errors in migration script (See https://www.google.com/search?q=transactional+DDL for details). On the other hand, SQLite3 and PostgreSQL support transactional DDL, and DDL will be rollbacked when error occurred in migration script. Very good.