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
170
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
CI_CD「健康診断」のススメ。現場でのボトルネック特定から、健康診断を通じた組織的な改善手法
teamlab
PRO
0
200
そのpreloadは必要?見過ごされたpreloadが技術的負債として爆発した日
mugitti9
2
3.2k
私達はmodernize packageに夢を見るか feat. go/analysis, go/ast / Go Conference 2025
kaorumuta
2
520
(Extension DC 2025) Actor境界を越える技術
teamhimeh
1
250
SpecKitでどこまでできる? コストはどれくらい?
leveragestech
0
670
overlayPreferenceValue で実現する ピュア SwiftUI な AdMob ネイティブ広告
uhucream
0
180
登壇は dynamic! な営みである / speech is dynamic
da1chi
0
290
The Past, Present, and Future of Enterprise Java
ivargrimstad
0
240
Server Side Kotlin Meetup vol.16: 内部動作を理解して ハイパフォーマンスなサーバサイド Kotlin アプリケーションを書こう
ternbusty
2
150
大規模アプリのDIフレームワーク刷新戦略 ~過去最大規模の並行開発を止めずにアプリ全体に導入するまで~
mot_techtalk
0
430
NetworkXとGNNで学ぶグラフデータ分析入門〜複雑な関係性を解き明かすPythonの力〜
mhrtech
3
1.2k
クラシルを支える技術と組織
rakutek
0
200
Featured
See All Featured
Product Roadmaps are Hard
iamctodd
PRO
54
11k
How to train your dragon (web standard)
notwaldorf
96
6.3k
How to Think Like a Performance Engineer
csswizardry
27
2k
The MySQL Ecosystem @ GitHub 2015
samlambert
251
13k
The Success of Rails: Ensuring Growth for the Next 100 Years
eileencodes
46
7.7k
A designer walks into a library…
pauljervisheath
209
24k
Music & Morning Musume
bryan
46
6.8k
Agile that works and the tools we love
rasmusluckow
331
21k
Building Applications with DynamoDB
mza
96
6.7k
Become a Pro
speakerdeck
PRO
29
5.5k
How To Stay Up To Date on Web Technology
chriscoyier
791
250k
Being A Developer After 40
akosma
91
590k
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