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

🔥 Prolific Module Author

👨‍👩‍👦‍👦 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}"
  }
}

commandbox-dotenv

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"
    }
}

commandbox-githooks

Thanks! 👋🏻

Twitter: @_elpete
ForgeBox: elpete
CFML Slack