CommandBox Migrations

Manage your database migrations from CommandBox

Transcript

1. COMMANDBOX
   MIGRATIONS
   

   View Slide

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
   

   View Slide

3. ABOUT ME
   ERIC PETERSON
   !
   Utah
   "
   Ortus
   #
   ForgeBox, ColdBox Elixir
   $
   Prolific Module Author
   %
   1 wife, 2 kids (1 on the way)
   

   View Slide

4. WHAT ARE
   database migrations?
   

   View Slide

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
   

   View Slide

6. WHAT DOES A migration FILE
   LOOK LIKE?
   

   View Slide

7. component {
   function up() {
   // logic to migrate up goes here
   }
   function down() {
   // logic to roll back goes here
   }
   }
   

   View Slide

8. WHY
   Database Migrations?
   

   View Slide

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
   

   View Slide

10. WHY
    CommandBox?
    

    View Slide

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
    

    View Slide

12. SIDE NOTE:
    COMMANDBOX-MIGRATIONS
    IS BUILT OFF OF
    CFMIGRATIONS
    

    View Slide

13. CFMIGRATIONS
    CAN BE PULLED IN YOUR APP TO RUN
    MIGRATIONS WITHOUT
    COMMANDBOX
    (USEFUL FOR INTEGRATION TESTING)
    

    View Slide

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)
    

    View Slide

15. CONFIGURATION
    

    View Slide

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"
    }
    }
    

    View Slide

17. DON'T DO IT THIS WAY!!
    

    View Slide

18. USE
    ENVIRONMENT VARIABLES
    

    View Slide

19. {
    "cfmigrations": {
    "connectionInfo": {
    "class": "${DB_CLASS}",
    "connectionString": "${DB_CONNECTIONSTRING}",
    "username": "${DB_USER}",
    "password": "${DB_PASSWORD}"
    },
    "defaultGrammar": "MySQLGrammar"
    }
    }
    

    View Slide

20. WHAT ABOUT LOCAL DEV?
    

    View Slide

21. COMMANDBOX-DOTENV
    

    View Slide

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

    View Slide

23. AND ADD IT TO
    YOUR .GITIGNORE
    

    View Slide

24. BUT THEN INCLUDE
    A .ENV.EXAMPLE FILE WITH
    THE VALUES BLANK THAT
    isn't IGNORED
    

    View Slide

25. # .env.example
    DB_CLASS=
    DB_CONNECTIONSTRING=
    DB_USER=
    DB_PASSWORD=
    

    View Slide

26. THIS SIGNALS TO OTHER TEAM MEMBERS
    WHICH ENVIRONMENT VARIABLES THEY
    NEED FILLED OUT.
    

    View Slide

27. ONE CAVEAT:
    COMMANDBOX MUST BE LOADED (OR
    RELOADED) IN THE DIRECTORY WITH
    YOUR .ENV FILE
    

    View Slide

28. SCHEMABUILDER
    PART OF qb
    

    View Slide

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.
    

    View Slide

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
    

    View Slide

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
    }
    }
    

    View Slide

32. SCHEMABUILDER
    API
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

36. COLUMN MODIFIERS
    table.integer( "age" ).nullable();
    table.boolean( "is_active" ).default( false );
    ▸ Can be called on any column
    ▸ default, nullable, unsigned, comment
    

    View Slide

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)
    

    View Slide

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)
    

    View Slide

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
    

    View Slide

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)
    

    View Slide

41. DEMO
    

    View Slide

42. Database Support
    MSSQLGrammar, MySQLGrammar,
    OracleGrammar, PostgresGrammar
    (We would love your help adding more.)
    

    View Slide

43. BONUS
    

    View Slide

44. COMMANDBOX-GITHOOKS
    {
    "githooks": {
    "postCheckout": "migrate up"
    }
    }
    

    View Slide

45. THANKS!
    commandbox-migrations
    commandbox-dotenv
    cfmigrations
    qb
    Twitter: (_elpete) · ForgeBox: (elpete) · CFML Slack
    

    View Slide