Let's tackle a powerful module today. It will take quite a bit of configuration, but hopefully you will see the usefulness of this module by the end of this post. Meet [CommandBox Migrations](https://www.forgebox.io/view/commandbox-migrations), a tool for managing and running your database migrations with CommandBox.

CommandBox Migrations

What are database migrations?

Database migrations are popular concepts in other language frameworks like Laravel and Ruby on Rails. A database migration defines a schema change for your database in code. It is committed to your application's source control and can be ran up and down as needed. They bring the benefit of co-locating schema and source code which helps new contributors to get up and running quickly, pull requests to include any necessary schema changes, and local development environments to be completely portable.

CommandBox Migrations

CommandBox Migrations is a CommandBox runner for your database migrations. Running them from CommandBox helps mitigate any security concerns with exposing your migrations via a traditional CFML application.

The commands are straight-forward:

1. migrate install Prepares the database for migrations.
2. migrate generate FILENAME Creates a new migration file with the correct timestamp prepended to the name.
3. migrate up [--once] Migrate all ran migrations up (if any). Passing the --once flag will only run the next available migration up.
4. migrate down [--down] Migrate all ran migrations down (if any). Passing the --once flag will only run the next available migration down.
5. `migrate refresh Run all ran migrations down and then all available migrations up again.
6. migrate uninstall Removes commandbox-migrations including all ran migrations from the database.

Before you can use CommandBox Migrations, you need to configure CommandBox to be able to connect to your database. You do this by specifying your configuration in a cfmigrations key in your box.json:

"cfmigrations": {
    "defaultGrammar": "MySQLGrammar",
    "connectionInfo": {
        "class": "${Setting: DB_CLASS not found}",
        "connectionString": "${Setting: DB_CONNECTIONSTRING not found}",
        "username": "${Setting: DB_USER not found}",
        "password": "${Setting: DB_PASSWORD not found}"
    }
}

There are two sections to this configuration. The first property, defaultGrammar, is for qb's Schema Builder, an optional (but highly recommended) library to help write you migrations.

The second property, connectionInfo, is the Lucee-specific syntax to define your datasource. The syntax above is for environment variable expansion. This keeps your secrets out of source control, an excellent practice. So, how do we specify these environment keys for CommandBox Migrations? We use CommandBox DotEnv.

CommandBox DotEnv

CommandBox DotEnv is a module worthy of being covered in its own right, but here we will just focus on its ability to provide environment variables to CommandBox.

We can define a .env file in the root of our application. When we start or reload CommandBox in the directory with the .env file, CommandBox DotEnv will load the secrets contained in to CommandBox, available for environment variable expansion, as seen in the configuration for CommandBox Migrations above. Our sample .env file might look like so:

DB_CLASS=org.gjt.mm.mysql.Driver
DB_CONNECTIONSTRING=jdbc:mysql: //localhost:3306/test_db?useUnicode=true&characterEncoding=UTF-8&useLegacyDatetimeCode=true
DB_USER=test
DB_PASSWORD=pass1234

With that configuration set up, and starting CommandBox up in the same directory as our .env file, we should be able to migrate install!

qb's Schema Builder

You can create a migration file using the migrate generate FILENAME command. This command is necessary as it prepends the correct timestamp to your file, ensuring that migrations are ran in the correct order.

An empty migration file looks like so:

component {

    function up( schema, query ) {
        // code to run the migration up
    }

    function down( schema, query ) {
        // code to run the migration down
    }

}

You can simply write a queryExecute call here to perform your migration, but CommandBox Migrations comes equipped with a better way: qb's Schema Builder.

Schema Builder is part of qb and provides a fluent, database-agnostic way to create schema changes. You can check out all the docs here. We'll only take a look at one example in this blog post.

component {

    function up( schema, query ) {
        schema.create( "users", function( table ) {
            table.increments( "id" );
            table.string( "email" ).unique();
            table.string( "password" );
            table.timestamp( "created_date" );
            table.timestamp( "updated_date" );
        } );
    }

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

}

This code creates a users table with five different columns. This code will generate the correct SQL for your database based on the key configured in your box.json's cfmigrations.defaultGrammar property.

Now, running migrate up will show a message in your console that one migration has been ran. You can check your database and see the schema changes. Awesome!

ODW Video Recording

I showed a demo of this at ODW 2017. Check it out below:

ODW 2017 - commandbox-migrations — Database Migrations using CommandBox from Luis Majano on Vimeo.

Wrap Up

Whew! There's a lot of pieces to make this one work. (This could be simplified with a new application template, if you want to contribute.) However, the portability and power provided will help you control your database schema, develop locally, and get new employees up and running quickly.