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](, 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_CONNECTIONSTRING=jdbc:mysql: //localhost:3306/test_db?useUnicode=true&characterEncoding=UTF-8&useLegacyDatetimeCode=true

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.

Add Your Comment

Recent Entries

CBWIRE 3.0.0 Released

CBWIRE 3.0.0 Released

We are very excited to announce the release of version 3 of CBWIRE, our ColdBox module that makes building modern, reactive apps a breeze. This version brings with it a new component syntax, 19 enhancements and bug fixes, and improved documentation. Our biggest goal with this release was to improve the developer experience and to provide a low barrier to entry to getting started with CBWIRE.

Grant Copley
Grant Copley
May 22, 2023
ColdBox 7.0.0 Released

ColdBox 7.0.0 Released

Introducing ColdBox 7: Revolutionizing Web Development with Cutting-Edge Features and Unparalleled Performance

We are thrilled to announce the highly anticipated release of ColdBox 7, the latest version of the acclaimed web development HMVC framework for ColdFusion (CFML). ColdBox 7 introduces groundbreaking features and advancements, elevating the development experience to new heights and empowering developers to create exceptional web applications and APIs.

Designed to meet the evolving needs of modern web development, ColdBox 7 boasts a range of powerful features that streamline the development process and enhance productivity. With its robust HMVC architecture and developer-friendly tools, ColdBox 7 enables developers to deliver high-performance, scalable, and maintainable web applications and APIs with ease.

Esme Acevedo
Esme Acevedo
May 16, 2023
TestBox v5.0.0 Released!

TestBox v5.0.0 Released!

We are excited to announced the release of Testbox version 5, which brings a host of new features and improvements for developers. TestBox is a powerful and flexible tool that helps developers write comprehensive BDD/TDD tests for their applications, ensuring code quality and reducing the likelihood of bugs and errors. With TestBox v5, developers can take advantage of new features such as batch code coverage testing, improved reporting capabilities, method spies, and better integration with other tools in the Ortus suite.

These new features make TestBox even more versatile and user-friendly, and provide developers with a powerful tool for building high-quality, reliable applications.


Luis Majano
Luis Majano
May 11, 2023