Migration Bundler

Migration Bundler is a database schema management system written in Ruby. It currently supports SQLite and Cassandra database targets. It was built to solve the schema management challenges on the mobile SDK's and server side services developed for the Layer platform.

Migration Bundler manages a Git repository containing a database schema and an arbitrary number of migrations. The migrations are authored in SQL format and platform specific renderings of the schema and migrations are created via code generation. The current schema and all associated migrations can then be packaged into a release and shipped as a versioned, installable package.

Features

• Manages a Git repository containing a database schema and migrations
• Supports SQLite and Cassandra schemas
• Generates platform specific database management and migration utilities
• Ensures that the current schema and all migrations are valid SQLite
• Packages schema and associated migrations into versioned releases
• Publishes releases for dependency management via CocoaPods and Maven

Requirements

Migration Bundler requires a modern Ruby runtime (v1.9.x and up) and the following supporting cast:

• thor - Toolkit for building powerful commandline utilities

Usage

Migration Bundler is a commandline utility available via the mb binary. There are a number of standard commandline switches available that control the defaults and target behavior of a Migration Bundler invocation. You can obtain help directly from the application by executing mb help.

Initializing a Project

Creates a new Migration Bundler schema repository at the path specified. Please refer to the documentation below regarding the repository structure.

$ mb init [path/to/project]

The default database target is SQLite, but you can specify an alternate database by using the database switch:

$ mb init --database=cassandra://localhost:9042/keyspace

Loading the Schema

Loads the current schema defined in schema.sql into a local database named database.sqlite.

$ mb load

Creating a Migration

Creates a new migration file at migrations/[TIMESTAMP]_[NAME].sql. Please refer to the documentation below about timestamp and versioning semantics.

$ mb create [name]

Displaying Status

Displays information about the current schema and any unapplied migrations.

$ mb status

Applying Migrations

Applies all pending to a target database by directly executing the SQL migrations.

$ mb migrate [target version]

Generating Implementations

Generates platform specific implementations into migrations/[migration].m and migrations/[migration].java.

$ mb generate

Validating Schema & Migrations

Validates that the schema loads and all migrations apply forward from a clean database.

$ mb validate

Packaging a Release

Packages a release of the schema by validating the project, generating all implementations, and creating a tag into the project Git repository.

$ mb package [version]

Pushing a Release

Pushes a release to Git, CocoaPods, and Maven.

$ mb push [version]

Design & Implementation Details

Migration Bundler was designed to deliver the following properties:

1. Provide a simple mechanism for integrating a unified schema into multiple codebases in parallel.
2. Support straightforward bootstrapping of a database with the current schema.
3. Support easy migrations from any previous version of the schema up to the latest by applying an ordered sequence of migrations.
4. Enable reliable, low friction testing of the migration process.
5. Support a heavily branched, multi-developer workflow in which multiple developers are evolving the schema in parallel.

This portion of the document details how Migration Bundler has been implemented to deliver these properties.

Project Layout

A Migration Bundler project is a simple filesystem layout with version control provided by Git. When a new project is initialized, it has the following structure:

.
├── .gitignore
├── .migration_bundler.yml
├── [project-name].sql
├── [project-name].sqlite
└── migrations
    └── <date-created>-create-[project-name].sql

Subsequent commands will create files into this filesystem and perform manipulations on the database. The schema can be branched, merged, and released using standard Git techniques.

Versioning

The client schema is internally versioned using timestamps. Timestamps are used instead of monotonically incrementing integers because they better support a branched, multi-developer workflow. The latest version of the overall schema is equal to the timestamp of the most recently applied migration. The current schema version of a given database is derivable by examining the schema_migrations table, which maintains a record of all migrations that have been applied to the database since its creation.

The schema_migrations table has the following schema:

CREATE TABLE schema_migrations(
    version INTEGER UNIQUE NOT NULL
);

The version column encodes the date a migration was created. The presence of a given migration version in the table indicates that the migration has been applied to the parent database.

Computing Origin and Current Version

To compute the "origin version" (the version of the schema at the time the database was created), select the minimum value for the version column in the schema_migrations table:

SELECT MIN(version) FROM schema_migrations

The current version of the database is computable by selecting the maximum value for the version column present in the schema_migrations table:

SELECT MAX(version) FROM schema_migrations

Note that knowing the current version is not sufficient for computing if the database is fully migrated. This is because migrations that were created in the past may not yet have been merged, released and applied yet.

Computing Unapplied Migrations

In order to compute the set of migrations that have not yet been applied to a given database, one must take the following steps:

1. Compute the origin version of the database
2. Build a collection of the versions of all known migrations in the current build of the code.
3. Build a collection of all migration versions that have already been applied to the database (SELECT version FROM schema_migrations);
4. Remove any migrations from the list with a version less than the origin version of the database.
5. Diff the collections of migrations. The set that remains is the set of unapplied migrations.
6. Order the set of unapplied migrations into an array of ascending values and apply them in order from oldest to newest.

Migrations

Migrations are authored in pure SQL. A code generation process is used to platform-specific, executable implementation of the migrations in Java and Objective-C from the source SQL files.

The SQL migration files are named by concatenating a timestamp version with a brief textual description of the database change they introduce to the system. This naming convention is of the form "$VERSION_IDENTIFIER_$MIGRATION_DESCRIPTION_UNDERSCORED.sql". The VERSION_IDENTIFIER is equal to the following invocation of the date utility (on platforms with GNU coreutils): date +"%Y%m%d%H%M%S%3N" This date format includes the year, month, day, hour, minute, second and three digits of milliseconds. This ensures that there is very little chance of overlap no matter how many developers are working on the schema, while still supporting very straightforward sorting in the database.

An example migration file might be called: 201405125248499_add_unqiue_constraint_to_streams.sql

The implementation of the generated migration class in Objective-C may look like:

@interface MBAddUniqueConstraintToStreamsMigration : NSObject <MBMigratable>
@end

@implementation LYR_AddUniqueConstraintToStreamsMigration

+ (NSString *)version
{
    return @"201405125248499";
}

- (BOOL)migrateDatabase:(FMDatabase *)database error:(out NSError *__autoreleasing *)error
{
    // Apply the migration
}

@end

Platform specific migrations can also be generated.

Companion Projects

Migration Bundler itself only generates installable packages containing the schema and migrations. To utilize these schemas in an application you'll need support code. The following companion projects are available for this use:

• FMDBMigrationManager - An Objective-C SQLite migration manager implementation for use with the FMDB library.
• SQLiteMigrationManager - A Java SQLite migration manager implementation for the Android SQLiteDatabase interface.

Credits

Blake Watters

• http://github.com/blakewatters
• http://twitter.com/blakewatters
• blakewatters@gmail.com

License

Migration Bundler is available under the Apache 2 License. See the LICENSE file for more info.