Migrations

A migration file is a component with two methods up and down. The function up should define how to apply the migration. The function down should define how to undo the change down in up. For QBMigrationManager migrations (which is the default), the up and down functions are passed an instance of SchemaBuilder@qb and QueryBuilder@qb as arguments. To learn more about the functionality and benefits of SchemaBuilder, QueryBuilder, and qb, please read the QB documentation here. In brief, qb offers a fluent, expressive syntax that can be compiled to many different database grammars, providing both readability and flexibility.

Here's the same example as above using qb's SchemaBuilder:

component {

    function up( schema, qb ) {
    	schema.create( "users", function( t ) {
            t.increments( "id" );
            t.string( "email" );
            t.string( "password" );
        } );
    }

    function down( schema, qb ) {
        schema.drop( "users" );
    }

}

Migration files need to follow a specific naming convention — YYYY_MM_DD_HHMMSS_[describe_your_changes_here].cfc. This is how cfmigrations knows in what order to run your migrations. Generating these files is made easier with the migrate create command from commandbox-migrations.

Using the injected qb instance, you can insert or update required data for your application. If you want to create test data for your application, take a look at seeders below instead.

There is no limit to what you can do in a migration. It is recommended that you separate changes to different tables to separate migration files to keep things readable.

Tips and tricks

Setting Schema

It's important to set the schema attribute for cfmigrations. Without it, cfmigrations can't tell the difference between a migration table installed in the schema you want and any other schema on the same database. You can set the schema by calling the setSchema( string schema ) method.

Default values in MS SQL server

MS SQL server requires some special treatment when removing columns with default values. Even though syntax is almost the same, MS SQL creates a special default constraint like DF_tablename_columname. When migrating down, this constraint has to be removed before dropping the column. In other grammars no special named constraint is created.

Example:

component {

    function up( schema, query   ) {
        schema.alter( "users", function ( table ) {
            table.addColumn( table.boolean( "hassuperpowers").default(0) );
        });
    }

    function down( schema, query  ) {
        schema.alter( "users", function( table ) {
            table.dropConstraint( "DF_users_hassuperpowers");
            table.dropColumn( "hassuperpowers" ) ;
        } );
    }

}

Updating database content in a migration file

Sometimes you want to do multiple content updates or inserts in a migration. In this case you can use the QueryBuilder for the updates. When doing your second update you have to reset the Querybuilder object by using the newQuery method.

Example:

component {

    function up( SchemaBuilder schema, QueryBuilder query ) {
	query.from('users')
	    .where( "username", "superuser")
	    .update( {"hassuperpowers" = true} )
	query.newQuery().from('users')
	    .where('username','RandomUser')
	    .update( {"hassuperpowers" = false} )
    }

    function down( SchemaBuilder schema, QueryBuilder query ) {
        ......
    }

}