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
120
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
75
qb — A Query Builder for the rest of us
elpete
0
180
Making Modules
elpete
0
200
CFML Sessions for Dummies
elpete
0
120
Live Testing a Legacy App
elpete
0
100
Mixing Up the Front-end with ColdBox Elixir
elpete
0
180
Integration Testing in a Modern World
elpete
0
400
Other Decks in Programming
See All in Programming
APIのない大学ログインWebサービスをWKWebViewとJavaScriptでアプリ化した話
akidon0000
1
330
Introduction to GitOps
hwchiu
0
110
Rustのweb開発を助ける 便利なツール紹介
yuki0418
1
190
CSC307 Lecture 13
javiergs
PRO
0
150
SRE チーム立ち上げ前に考えたこと・取り組んだこと / Considerations and Preparations Before Establishing an SRE Team
mackey0225
3
320
Temporalを取り巻く仕様を整理する
sajikix
0
110
英語
s_shimotori
1
220
Async Await: Mastering Python's Time-Bending Tricks - EuroPython2024
yanbo
1
290
유연한 Composable 설계
l2hyunwoo
0
380
Javaの現状2024夏 / Java current status 2024 summer
kishida
4
1.4k
AWSでゲームサーバーを運用! Amazon GameLiftのお話
iriikeita
0
200
Findy - エンジニア向け会社紹介 / Findy Letter for Engineers
findyinc
2
81k
Featured
See All Featured
Design and Strategy: How to Deal with People Who Don’t "Get" Design
morganepeng
121
18k
The Language of Interfaces
destraynor
151
23k
KATA
mclloyd
20
13k
Become a Pro
speakerdeck
PRO
15
4.8k
Faster Mobile Websites
deanohume
303
30k
Mobile First: as difficult as doing things right
swwweet
219
8.8k
5 minutes of I Can Smell Your CMS
philhawksworth
200
19k
4 Signs Your Business is Dying
shpigford
178
21k
The Success of Rails: Ensuring Growth for the Next 100 Years
eileencodes
35
6.3k
"I'm Feeling Lucky" - Building Great Search Experiences for Today's Users (#IAC19)
danielanewman
224
21k
Creating an realtime collaboration tool: Agile Flush - .NET Oxford
marcduiker
16
1.6k
From Idea to $5000 a Month in 5 Months
shpigford
377
46k
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