Upgrade to Pro
— share decks privately, control downloads, hide ads and more …
Speaker Deck
Features
Speaker Deck
PRO
Sign in
Sign up for free
Search
Search
CommandBox Migrations
Search
Eric Peterson
April 27, 2018
Programming
0
160
CommandBox Migrations
Manage your database migrations from CommandBox
Eric Peterson
April 27, 2018
Tweet
Share
More Decks by Eric Peterson
See All by Eric Peterson
Tips for Writing Modules for All Frameworks (But Especially for ColdBox)
elpete
0
110
qb — A Query Builder for the rest of us
elpete
0
250
Making Modules
elpete
0
260
CFML Sessions for Dummies
elpete
0
150
Live Testing a Legacy App
elpete
0
130
Mixing Up the Front-end with ColdBox Elixir
elpete
0
210
Integration Testing in a Modern World
elpete
0
490
Other Decks in Programming
See All in Programming
ユーザーも開発者も悩ませない TV アプリ開発 ~Compose の内部実装から学ぶフォーカス制御~
taked137
0
160
JSONataを使ってみよう Step Functionsが楽しくなる実践テクニック #devio2025
dafujii
1
530
時間軸から考えるTerraformを使う理由と留意点
fufuhu
16
4.8k
はじめてのMaterial3 Expressive
ym223
2
300
Azure SRE Agentで運用は楽になるのか?
kkamegawa
0
2.2k
Laravel Boost 超入門
fire_arlo
3
210
Zendeskのチケットを Amazon Bedrockで 解析した
ryokosuge
3
300
Testing Trophyは叫ばない
toms74209200
0
860
知っているようで知らない"rails new"の世界 / The World of "rails new" You Think You Know but Don't
luccafort
PRO
1
110
testingを眺める
matumoto
1
140
機能追加とリーダー業務の類似性
rinchoku
2
1.3k
Flutter with Dart MCP: All You Need - 박제창 2025 I/O Extended Busan
itsmedreamwalker
0
150
Featured
See All Featured
Let's Do A Bunch of Simple Stuff to Make Websites Faster
chriscoyier
507
140k
YesSQL, Process and Tooling at Scale
rocio
173
14k
Fashionably flexible responsive web design (full day workshop)
malarkey
407
66k
GitHub's CSS Performance
jonrohan
1032
460k
Designing for humans not robots
tammielis
253
25k
Designing Experiences People Love
moore
142
24k
What’s in a name? Adding method to the madness
productmarketing
PRO
23
3.7k
The Myth of the Modular Monolith - Day 2 Keynote - Rails World 2024
eileencodes
26
3k
Fight the Zombie Pattern Library - RWD Summit 2016
marcelosomers
234
17k
We Have a Design System, Now What?
morganepeng
53
7.8k
実際に使うSQLの書き方 徹底解説 / pgcon21j-tutorial
soudai
PRO
188
55k
The Illustrated Children's Guide to Kubernetes
chrisshort
48
50k
Transcript
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