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
100
qb — A Query Builder for the rest of us
elpete
0
240
Making Modules
elpete
0
250
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
200
Integration Testing in a Modern World
elpete
0
480
Other Decks in Programming
See All in Programming
[SRE NEXT] 複雑なシステムにおけるUser Journey SLOの導入
yakenji
0
790
Quality Gates in the Age of Agentic Coding
helmedeiros
PRO
1
110
AIともっと楽するE2Eテスト
myohei
9
3.1k
iOS開発スターターキットの作り方
akidon0000
0
190
バイブスあるコーディングで ~PHP~ 便利ツールをつくるプラクティス
uzulla
1
280
MCPで実現できる、Webサービス利用体験について
syumai
7
2.1k
リッチエディターを安全に開発・運用するために
unachang113
1
250
11年かかって やっとVibe Codingに 時代が追いつきましたね
yimajo
0
160
マッチングアプリにおけるフリックUIで苦労したこと
yuheiito
0
240
抽象化という思考のツール - 理解と活用 - / Abstraction-as-a-Tool-for-Thinking
shin1x1
1
820
それ CLI フレームワークがなくてもできるよ / Building CLI Tools Without Frameworks
orgachem
PRO
10
2k
AWS Summit Japan 2024と2025の比較/はじめてのKiro、今あなたは岐路に立つ
satoshi256kbyte
1
250
Featured
See All Featured
Designing Dashboards & Data Visualisations in Web Apps
destraynor
231
53k
BBQ
matthewcrist
89
9.7k
Embracing the Ebb and Flow
colly
86
4.8k
Practical Tips for Bootstrapping Information Extraction Pipelines
honnibal
PRO
21
1.3k
Build your cross-platform service in a week with App Engine
jlugia
231
18k
Designing for Performance
lara
610
69k
KATA
mclloyd
30
14k
Speed Design
sergeychernyshev
32
1k
ピンチをチャンスに:未来をつくるプロダクトロードマップ #pmconf2020
aki_iinuma
126
53k
Build The Right Thing And Hit Your Dates
maggiecrowley
37
2.8k
Raft: Consensus for Rubyists
vanstee
140
7k
Responsive Adventures: Dirty Tricks From The Dark Corners of Front-End
smashingmag
251
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