CommandBox Migrations

CommandBox Migrations

Manage your database migrations from CommandBox

Cbddee54e0016667b9bcb0fdec4ab21e?s=128

Eric Peterson

April 27, 2018
Tweet

Transcript

  1. 2.

    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
  2. 3.

    ABOUT ME ERIC PETERSON ! Utah " Ortus # ForgeBox,

    ColdBox Elixir $ Prolific Module Author % 1 wife, 2 kids (1 on the way)
  3. 5.

    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
  4. 7.

    component { function up() { // logic to migrate up

    goes here } function down() { // logic to roll back goes here } }
  5. 9.

    WHY Database Migrations? ▸ Co-located inside your app's codebase ▸

    Apply schema changes in order ▸ Able to bring up new instances / databases on demand
  6. 11.

    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
  7. 13.

    CFMIGRATIONS CAN BE PULLED IN YOUR APP TO RUN MIGRATIONS

    WITHOUT COMMANDBOX (USEFUL FOR INTEGRATION TESTING)
  8. 14.

    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)
  9. 16.

    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" } }
  10. 19.

    { "cfmigrations": { "connectionInfo": { "class": "${DB_CLASS}", "connectionString": "${DB_CONNECTIONSTRING}", "username":

    "${DB_USER}", "password": "${DB_PASSWORD}" }, "defaultGrammar": "MySQLGrammar" } }
  11. 29.

    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.
  12. 30.

    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
  13. 31.

    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 } }
  14. 33.

    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
  15. 34.

    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
  16. 35.

    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
  17. 36.

    COLUMN MODIFIERS table.integer( "age" ).nullable(); table.boolean( "is_active" ).default( false );

    ▸ Can be called on any column ▸ default, nullable, unsigned, comment
  18. 37.

    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)
  19. 38.

    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)
  20. 39.

    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
  21. 40.

    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)
  22. 41.
  23. 43.