Introduction

An application for standardizing and safely migrating database schemas from one version to another.

Migrations is intended to be a more enterprise capable version of the Ruby on Rails Migration concept.

Features

Some of the features include ...

  • Multiple schema interactions with dependency resolution for both upgrade and downgrade
  • Runtime substitution of values such as tablespace names or data types
  • Version tracking of schema changes
  • Clean path for upgrading and downgrading the database
  • Multiline script support for doing things like pl/sql
  • Final raw sql script generation
  • Basic auditing of schema information including when it was modified, who modified it, and the ip it was modified from.
  • Production mode allowing for only upgrades to take place and not allowing accidental destruction of schema and data.
  • Clean migration indicators to allow understanding of which delta migrations failed and whether or not to prevent further migrations until human intervention has taken place in case of bad migration scripts.
  • Packaging (.zip or .jar) support for easy portability of db migration scripts.
  • Simple API usable for testing frameworks to do cleaner integration testing.
  • Easy to use unix and windows startup scripts

Quick Overview

Migrations works off of a simple pattern based system. The application will be told the location of a configuration file (often referred to as the migrations.xml). 1 or more folders which contain sql scripts should be present where the migration configuration is located at. Each folder represents a schema (things which could in theory reside in different database schemas even if they don't always in reality).

A .zip or .jar file containing all of the above scripts and migrations.xml can also be used instead.

Included with the downloaded applications are a basic-migration and advanced-migration folders which include example migrations.xml files as well.

The basic migrations.xml looks like 

<migration>
  <schemas>
    <schema id="rolodex" driver="org.hsqldb.jdbcDriver"
      url="jdbc:hsqldb:mem:test" username="sa" password="" />
  </schemas>
</migration>
In this example migration, there is a rolodex defined. Thus there should be a folder located here as well named rolodex. Also note that for the schema element, the attributes driver, url, username, and password are optional and if not present will be prompted for during the migration process.

Schema folders shall contain the up/down migration scripts which follow this pattern 

###_(up|down)_description.sql
Meaning that it must first start with a number 1 or higher which represents the migration version represented by that sql script. Then next section which is separated by an _ is the up/down section. Specifies whether these are upgrade or downgrade scripts respective. Both should exist in order to be able and downgrade or revert changes made. The final section is just a human sensible quick description of what the purpose of that migration script is. An example of what might exist in the myschema folder would be 
001_up_initialize.sql
001_down_destroy.sql
002_up_patch-person-table.sql
002_down_rollback-person-table.sql
003_up_create-uuid-column.sql
003_down_delete-uuid-column.sql

Once a migration configuration and sql scripts exist, anyone can simply point to the configuration location and migrate any schema to any version, use it for however long, and then migrate down to 0 to restore the database schema back to its original state.