COMMANDBOX MIGRATIONS 

WHAT THIS TALK is ▸ An introduction to qb's SchemaBuilder ▸ Co-locating your app's database structure in your app ▸ How to make changes to your database schema from CommandBox 

ABOUT ME ERIC PETERSON ! Utah " Ortus # ForgeBox, ColdBox Elixir $ Prolific Module Author % 1 wife, 2 kids (1 on the way) 

WHAT ARE database migrations? 

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 

WHAT DOES A migration FILE LOOK LIKE? 

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

WHY Database Migrations? 

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

WHY CommandBox? 

WHY CommandBox? ▸ No worries about exposing access to your database from the web ▸ Scriptable — can run as part of your deploy pipline ▸ Scaffold new migrations with a single command 

SIDE NOTE: COMMANDBOX-MIGRATIONS IS BUILT OFF OF CFMIGRATIONS 

CFMIGRATIONS CAN BE PULLED IN YOUR APP TO RUN MIGRATIONS WITHOUT COMMANDBOX (USEFUL FOR INTEGRATION TESTING) 

CONVENTIONS ▸ Migrations are located inside resources/database/migrations ▸ Migration file names start with the timestamp they were created (2017_09_03_043150_create_users_table.cfc) 

CONFIGURATION 

CONFIGURATION Contained within box.json { "cfmigrations": { "connectionInfo": { "class": "org.gjt.mm.mysql.Driver", "connectionString": "jdbc:mysql://localhost:3306/commandbox-migrations-testing", "username": "root", "password": "root" }, "defaultGrammar": "MySQLGrammar" } } 

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

WHAT ABOUT LOCAL DEV? 

COMMANDBOX-DOTENV 

.ENV FILE # .env DB_CLASS=org.gjt.mm.mysql.Driver DB_CONNECTIONSTRING=jdbc:mysql://localhost:3306/commandbox-migrations-testing DB_USER=root DB_PASSWORD=root 

AND ADD IT TO YOUR .GITIGNORE 

BUT THEN INCLUDE A .ENV.EXAMPLE FILE WITH THE VALUES BLANK THAT isn't IGNORED 

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

THIS SIGNALS TO OTHER TEAM MEMBERS WHICH ENVIRONMENT VARIABLES THEY NEED FILLED OUT. 

ONE CAVEAT: COMMANDBOX MUST BE LOADED (OR RELOADED) IN THE DIRECTORY WITH YOUR .ENV FILE 

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 for commandbox-migrations ▸ Also can be used outside of commandbox-migrations ▸ Comes bundled and configured for use because it makes life easier 

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 

CREATE 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 a named table ▸ Define the columns, modifiers, and indexes inside the callback 

COLUMNS table.string( "email" ); table.integer( "age", 3 ); ▸ Defines the column type, name, and attributes ▸ Can be used in create or alter method ▸ Columns are NOT NULL by default 

COLUMNS 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 

COLUMN MODIFIERS table.integer( "age" ).nullable(); table.boolean( "is_active" ).default( false ); ▸ Can be called on any column ▸ default, nullable, unsigned, comment 

COLUMN INDEXES table.string( "id" ).primaryKey(); table.string( "username" ).unique(); table.string( "country_id" ).references( "id" ).on( "countries" ); ▸ Add indexes on individual columns ▸ Available indexes are: primaryKey, unique, references (foreign key) 

TABLE INDEXES table.primaryKey( [ "post_id", "tag_id" ] ); table.index( "created_date" ); ▸ Add indexes on individual or multiple columns ▸ Available indexes are: index, primaryKey, unique, references (foreign key) 

ALTER 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 methods take the same column definition as create 

DROP schema.drop( "user_logins" ); schema.dropIfExists( "user_profiles" ); ▸ Drop a table ▸ Doesn't disable any constraints first ▸ (But that's why migrations are ran in order) 

DEMO 

Database Support MSSQLGrammar, MySQLGrammar, OracleGrammar, PostgresGrammar (We would love your help adding more.) 

BONUS 

COMMANDBOX-GITHOOKS { "githooks": { "postCheckout": "migrate up" } } 

THANKS! commandbox-migrations commandbox-dotenv cfmigrations qb Twitter: (_elpete) · ForgeBox: (elpete) · CFML Slack