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
さようなら Date。 ようこそTemporal! 3年間先行利用して得られた知見の共有
8beeeaaat
3
1.5k
HTMLの品質ってなんだっけ? “HTMLクライテリア”の設計と実践
unachang113
4
2.9k
複雑なドメインに挑む.pdf
yukisakai1225
5
1.2k
🔨 小さなビルドシステムを作る
momeemt
4
680
為你自己學 Python - 冷知識篇
eddie
1
350
JSONataを使ってみよう Step Functionsが楽しくなる実践テクニック #devio2025
dafujii
1
530
「手軽で便利」に潜む罠。 Popover API を WCAG 2.2の視点で安全に使うには
taitotnk
0
860
250830 IaCの選定~AWS SAMのLambdaをECSに乗り換えたときの備忘録~
east_takumi
0
390
Navigation 2 を 3 に移行する(予定)ためにやったこと
yokomii
0
270
1から理解するWeb Push
dora1998
7
1.9k
testingを眺める
matumoto
1
140
請來的 AI Agent 同事們在寫程式時,怎麼用 pytest 去除各種幻想與盲點
keitheis
0
120
Featured
See All Featured
Improving Core Web Vitals using Speculation Rules API
sergeychernyshev
18
1.1k
Music & Morning Musume
bryan
46
6.8k
GitHub's CSS Performance
jonrohan
1032
460k
CoffeeScript is Beautiful & I Never Want to Write Plain JavaScript Again
sstephenson
162
15k
Balancing Empowerment & Direction
lara
3
620
Being A Developer After 40
akosma
90
590k
Testing 201, or: Great Expectations
jmmastey
45
7.7k
Connecting the Dots Between Site Speed, User Experience & Your Business [WebExpo 2025]
tammyeverts
8
520
Code Review Best Practice
trishagee
70
19k
The Psychology of Web Performance [Beyond Tellerrand 2023]
tammyeverts
49
3k
Speed Design
sergeychernyshev
32
1.1k
Reflections from 52 weeks, 52 projects
jeffersonlam
352
21k
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