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

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.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": "MySQLGrammar",
    "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
    }

}

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

Database Support

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

Bonus

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

Thanks! 👋🏻

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

cfmigrations

By Eric Peterson

Loading comments...

More from Eric Peterson