Blog

Eric Peterson

December 24, 2017

Spread the word


Share your thoughts

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.

Recent Entries

Into the Box 2024 Last Early Bird Days!

Into the Box 2024 Last Early Bird Days!

Time is ticking, with less than 60 days remaining until the excitement of Into the Box 2024 unfolds! Don't let this golden opportunity slip away; our exclusive Early Bird Pricing is here for a limited time only, available until March 31st. Why wait? Secure your seat now and take advantage of this steal!

Maria Jose Herrera
Maria Jose Herrera
March 20, 2024
Ortus February Newsletter 2024

Ortus February Newsletter 2024

Welcome to Ortus Solutions’ monthly roundup, where we're thrilled to showcase cutting-edge advancements, product updates, and exciting events! Join us as we delve into the latest innovations shaping the future of technology.

Maria Jose Herrera
Maria Jose Herrera
March 06, 2024
Unveiling the Future of CFML Development - 3rd Round of Sessions

Unveiling the Future of CFML Development - 3rd Round of Sessions

Welcome back to our journey into the future of CFML development! As excitement continues to build for Into the Box 2024, we're thrilled to bring you the latest updates on what promises to be a transformative event. Continuing our blog post series, let's delve deeper into the third wave of session releases and discover the key surprises awaiting attendees. Learn More

Maria Jose Herrera
Maria Jose Herrera
March 01, 2024