Home

Bering is a tool for creating and maintaining relational database schemas. It focuses on incremental development and refactoring, and so is well suited to agile programming practices.

Bering started out as a port of an early version of ActiveRecord migrations, but has a few different features.

It designed for situations where an application needs to support multiple deployment platforms, but it is useful even if there's only one.

Using Bering

The definition of a Bering-managed schema is a series of groovy scripts in a particular location in your source base. The default location depends on if you are using the AntTask or the MavenPlugin; check the docs for each for details. For now, consider the location to be db/migrate. That directory will contain one or more numbered release directories, each of which would contain one or more numbered migration scripts.

A migration script

Say you were going to create a system for tracking mice used in genetic/breeding experiments. Your first step might be to add some way to store information about individual mice. A bering migration script to do this would look something like this:

class CreateMouseTable extends edu.northwestern.bioinformatics.bering.Migration {
  void up() {
    createTable("mice") { t ->
      t.addColumn("name", "string", nullable: false, limit: 255)
      t.addColumn("birth_date", "date", nullable: false)
      t.addColumn("location", "string")
    }
  }

  void down() {
    dropTable("mice")
  }
}

Ups and downs

When you apply this migration to your database, bering calls the up method. It will translate the calls into SQL appropriate to the target database and execute it. If you were using PostgreSQL, it would look like this:

CREATE TABLE mice (
  id SERIAL NOT NULL,
  name VARCHAR(255) NOT NULL,
  location TEXT
)

You will probably find from time to time that you make a mistake in a migration script -- or introduce a bug -- so you need to roll back the schema change. This is what the down method is for. Bering allows you to revert your changes, so long as you implement down as the reverse of up. In this case, down is simple -- you added a table in up so you drop it in down. (The mechanism for actually reverting your changes depends on how you are using Bering. See AntTask or MavenPlugin for details.)

Capabilities

Bering migration scripts can do more than just create and drop tables. They can modify existing tables & columns, insert data, and execute arbitrary SQL. Bering allows if-else-type branching based on the particular platform it is executing against.

(More documentation on this is needed. For now, you can check out the protected methods in the Migration class.)

Supported types

The table.addColumn(...) method supports the following types which map to the indicated JDBC types:

        "string" => Types.VARCHAR
        "integer" => Types.INTEGER
        "float" => Types.FLOAT
        "numeric" => Types.NUMERIC
        "boolean" => Types.BIT
        "date" => Types.DATE
        "time" => Types.TIME
        "timestamp" => Types.TIMESTAMP

Releases and scripts

One table does not an application make, of course. You need many migration scripts, creating and refactoring many tables and their relationships, to build a real application. Bering expects the scripts to be sorted into numbered directories by release, and the scripts themselves must be numbered, too. Here's an example. (The script above is 001_mickey\001_create_mouse_table.groovy.)

~/proj-dir/svn/trunk/db/migrate $ ls -R

./001_mickey:
001_create_mouse_table.groovy
002_create_mating_table.groovy

./002_frisby:
001_add_audit_columns.groovy
002_create_cage_tables.groovy
003_add_guids.groovy

./003_frankie:
001_move_auditing_to_separate_tables.groovy

This project has 3 releases and a total of 6 migration scripts (so far).

Naming

Release names aren't required. All bering cares about is the integer at the beginning -- the rest of the directory name is for human readability.

Script names, on the other hand, are important. After the number, the script's name should match the class name of the migration defined in the script, except that script name uses lowercase+underscores, while the class name should use InnerCaps. For example, the migration class in the script above is CreateMouseTable. This should always be stored in a file named ###_create_mouse_table.groovy.

Applying your scripts to a database

Bering's main function is keeping track of which migration scripts you've applied to a particular database at any given time. After you write that first script and execute it, Bering knows that your database is at release 1, migration 1 (Bering's shorthand for this is 1-1). As you add more scripts and have Bering apply them, it will keep track so it can automatically apply the correct ones each time.

Bering keeps track of the schema revision in the database itself, so no matter who or what applied the scripts last time, each new run will apply only the newly added scripts.

Bering keeps track of the current migration number for each release in your project. It checks for and applies new scripts in each release, applying all outstanding scripts in each release before proceeding to the next.

Practically speaking, you will generally only add new scripts to the most current release. However, if it happens that you encounter a bug in a deployed system which required schema changes, Bering's multiple release support will allow you to patch it without interrupting your ongoing work on the next release.

One side effect of the way Bering tracks scripts is that if a migration script has already been applied to a database, it will not be applied again if it is changed, unless it is done explicitly. In team-developed projects, this means that it's good practice to let your colleagues know if you change a script. Or, better, to not modify a script once it hits your source control repository.