cfmigrations

What this talk is about

  • Co-locating your app's database structure in your app

  • How to make changes to your database schema from CommandBox

  • An introduction to qb's SchemaBuilder

  • Using cfmigrations in testing

  • Migrating things other than database schema

Who am I?

🏡 Utah

🏬 Ortus

📦 Quick, qb, ColdBox Elixir

👨‍👩‍👦‍👦 1 wife, 3 kids

What are "database migrations"?

  • Changes to your application's database schema
  • Describes how to apply the change and how to rollback the change
  • Ran up and down in order

Why use database migrations?

  • Co-located inside your app's codebase
  • Apply schema changes in order
  • Able to bring up new instances / databases on demand

What does a migration file look like?

component {

    function up() {
        // logic to migrate up goes here
    }

    function down() {
        // logic to roll back goes here
    }

}

Stick whatever you want in there!

component {

    function up() {
        queryExecute( "
            CREATE TABLE `users` (
                `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
                `username` NVARCHAR(255) NOT NULL,
                `first_name` NVARCHAR(255) NOT NULL,
                `last_name` NVARCHAR(255) NOT NULL,
                `password` NVARCHAR(100) NOT NULL,
                `country_id` INTEGER UNSIGNED NOT NULL,
                `created_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                `modified_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                CONSTRAINT `pk_users_id` PRIMARY KEY (`id`),
                CONSTRAINT `fk_users_country_id` FOREIGN KEY (`country_id`)
                    REFERENCES `countries` (`id`) ON UPDATE NO ACTION ON DELETE CASCADE
            )
        " );
    }

    function down() {
        queryExecute( "
            DROP TABLE `users`
        " );
    }

}

Running from a web server

var migrationService = getWireBox().getInstance( "MigrationService@cfmigrations" );
migrationService.setMigrationsDirectory( "/resources/database/migrations" );
migrationService.setDefaultGrammar( "PostgresGrammar" );
migrationService.setDatasource( getUtil().getSystemSetting( "DB_DATABASE" ) );
migrationService.setTable( "cfmigrations" );
migrationService.install();
migrationService.runAllMigrations( "up" );

Running from CommandBox

install commandbox-migrations
migrate init
migrate install
migrate up

Conventions

  • All migrations live in `resources/database/migrations`
  • Files names start with the full timestamp
    • 2017_09_03_043150_create_users_table.cfc

Configuration

{
  "cfmigrations": {
    "connectionInfo": {
      "class": "org.gjt.mm.mysql.Driver",
      "connectionString": "jdbc:mysql://localhost:3306/my_db",
      "username": "root",
      "password": "root"
    },
    "defaultGrammar": "MySQLGrammar",
    "schema": "my_db"
  }
}

In box.json

Don't do it this way!

Use Environment Variables

{
  "cfmigrations": {
    "connectionInfo": {
      "class": "${DB_CLASS}",
      "connectionString": "${DB_CONNECTIONSTRING}",
      "username": "${DB_USER}",
      "password": "${DB_PASSWORD}"
    },
    "defaultGrammar": "AutoDiscover",
    "schema": "${DB_SCHEMA}"
  }
}

Perfect for local development

.env file

# .env
DB_CLASS=org.gjt.mm.mysql.Driver
DB_CONNECTIONSTRING=jdbc:mysql://localhost:3306/my_db
DB_USER=root
DB_PASSWORD=root
DB_SCHEMA=my_db

Add it to your .gitignore

But create a .env.example file with blank values that isn't ignored

# .env.example
DB_CLASS=
DB_CONNECTIONSTRING=
DB_USER=
DB_PASSWORD=
DB_SCHEMA=

This signals to other team members which environment variable are needed

SchemaBuilder

Part of qb

SchemaBuilder

  • Is to database structure what QueryBuilder is to queries
  • Fluent, expressive syntax for describing tables, columns, and constraints
  • Bridges the many database idiosyncrasies in this area

SchemaBuilder

  • Not required to use SchemaBuilder with commandbox-migrations (or cfmigrations)
  • Also can be used separate from commandbox-migrations (or cfmigrations)
  • Comes bundled and configured for use because it makes life easier

Migration File with SchemaBuilder

component {

    function up( schema, query ) {
        // An pre-configured instance
        // of `SchemaBuilder` and `QueryBuilder`
        // are passed to each migration function
    }

    function down( schema, query ) {
        // feel free to ignore them
        // if they aren't your thing
    }

}

Database Support

  • MSSQLGrammar (SQL Server)
  • PostgresGrammar
  • MySQLGrammar
  • OracleGrammar

SchemaBuilder API

schema.create( "users", function( table ) {
    table.increments( "id" );
    table.string( "email" );
    table.string( "password" );
    table.timestamp( "created_date" );
    table.timestamp( "modified_date" );
    table.timestamp( "last_logged_in" ).nullable();
} );
create

SchemaBuilder API

table.string( "email" );
table.integer( "age", 3 );
columns

SchemaBuilder API

All Columns

bigIncrements bigInteger bit boolean
char date datetime decimal
enum float increments integer
json longText mediumIncrements mediumInteger
mediumText morphs nullableMorphs raw
smallIncrements smallInteger string text
time timestamp tinyIncrements tinyInteger
unsignedBigInteger unsignedInteger unsignedMediumInteger unsignedSmallInteger
unsignedTinyInteger uuid

SchemaBuilder API

table.integer( "age" ).nullable();
table.boolean( "is_active" ).default( false );
column modifiers

SchemaBuilder API

table.string( "id" ).primaryKey();
table.string( "username" ).unique();
table.string( "country_id" )
    .references( "id" )
    .on( "countries" );
column indexes

SchemaBuilder API

table.primaryKey( [ "post_id", "tag_id" ] );
table.index( "created_date" );
table indexes

SchemaBuilder API

schema.alter( "users", function( table ) {
    table.addColumn( table.boolean( "is_active" ) );
    table.modifyColumn( "age", table.integer( "age", 3 ) );
    table.renameColumn( "name", table.string( "username" ) );
    table.dropColumn( "last_logged_in" );
    table.addConstraint( table.unique( "username" ) );
    table.dropConstraint( "full_constraint_name" );
    table.dropConstraint( table.foreignKey( "country_id" ) );
    table.renameConstraint(
        "unq_users_first_name_last_name",
        "unq_users_full_name"
    );
} );
alter

SchemaBuilder API

schema.drop( "user_logins" );
schema.dropIfExists( "user_profiles" );
drop

Demo

Migrating Outside the Database

ElasticSearch

component {

    property name="indexBuilder" inject="provider:IndexBuilder@cbElasticsearch";
    property name="aliasBuilder" inject="provider:AliasBuilder@cbElasticsearch";
    property name="client" inject="Client@cbElasticsearch";

    variables.aliasName = "sailings";
    variables.oldIndexName = "sailings_2019_08_01_141730";
    variables.newIndexName = "sailings_2019_09_04_091730";

    function up() {
        // create new index with new settings
        variables.indexBuilder.new(
            name = variables.newIndexName,
            properties = getMappingConfig(),
            settings = {
                "index.mapping.total_fields.limit": "10000",
                "index.max_result_window": "1000000",
                "number_of_shards": 8,
                "number_of_replicas": 2
            }
        ).save();

        // reindex data from old index to new index
        variables.client.reindex( variables.oldIndexName, variables.newIndexName );

        // swap the alias from the old index to the new index
        variables.client.applyAliases( [
            aliasBuilder.add( variables.newIndexName, variables.aliasName ),
            aliasBuilder.remove( variables.oldIndexName, variables.aliasName )
        ] );
    }

    function down() {
        // swap the alias from the new index back to the old index
        variables.client.applyAliases( [
            aliasBuilder.add( variables.oldIndexName, variables.aliasName ),
            aliasBuilder.remove( variables.newIndexName, variables.aliasName )
        ] );

        // delete the new index
        variables.client.deleteIndex( variables.newIndexName );
    }

    function getMappingConfig() {
        return { /* ... */ };
    }

}

Integration Testing

component extends="coldbox.system.testing.BaseTestCase" {

    property name="migrationService" inject="MigrationService@cfmigrations";

    this.loadColdBox = true;
    this.unloadColdBox = false;

    /**
     * Run Before all tests
     */
    function beforeAll() {
        super.beforeAll();
        // Wire up this object
        application.wirebox.autowire( this );

        // Check if migrations ran before all tests
        if ( ! request.keyExists( "migrationsRan" ) ) {
            migrationService.setMigrationsDirectory( "/root/resources/database/migrations" );
	        migrationService.setDefaultGrammar( "MySQLGrammar" );
            migrationService.setDatasource( "testing" );
            migrationService.runAllMigrations( "down" );
            migrationService.runAllMigrations( "up" );
            request.migrationsRan = true;
        }
    }

    /**
     * This function is tagged as an around each handler.  All the integration tests we build
     * will be automatically rolledbacked
     *
     * @aroundEach
     */
    function wrapInTransaction( spec ) {
        transaction action="begin" {
            try {
                arguments.spec.body();
            } catch ( any e ) {
                rethrow;
            } finally {
                transaction action="rollback";
            }
        }
    }

}

Demo

Bonus

// box.json
{
    "githooks": {
        "postCheckout": "migrate up"
    }
}

Thanks! 👋🏻

  • Twitter: @_elpete
  • ForgeBox: elpete
  • CFML Slack