With CFWheels 2.x, you can now create, alter and populate your database via cfscript in an organized manner. Using custom CFC files, you can create an organized database schema, and move between versions easily, either programmatically, via the provided GUI, or via the CLI.

Getting Started

If you're new to this concept, the best way to get going is by following the [migrations] link in the debug footer to load the built in GUI. Naturally, you will need your application's datasource setup and ready to go to get started.

On the first tab, we provide some simple database info, just so you can check you're running against the correct datasource. We're going to start by creating a simple template.

Creating your First Template

The create template tab allows for creation of either a blank CFC file, or from a selection of pre-populated templates. Whilst none of these templates will provide all the information required for a complete database migration, they are a good starting point and fairly heavily commented.

As we've not setup any migrations before, the system needs to know what prefix we want to use for our migration files. Each approach - Timestamp and Numeric is perfectly valid, but we recommend the Timestamp prefix if you're just starting out. Once you have a migration file, this section will disappear as it will get that info from the existing files.

For this tutorial, we're going to create the users table. So under Create a Template, we will select Create table and add a migration description of Create User Table.

Clicking on Create Migration File will then create a CFC at /db/migrate/20170420100502_Create_User_Table.cfc. The system will also display all messages at the top of the GUI whenever it does something - so for this command, we see The migration 20170420100502_Create_User_Table.cfc file was created

Populating the Create User Table Template

Next, open up the Create_User_Table.cfc template we just created. There are two functions to any migration file: up() and down().

up() will be executed when migrating your schema forward, and down() when you're rolling back.

The important concept to grasp is that anything which up() does, down() must undo.

function up() {
 hasError = false;
 transaction {
  try {
    t = createTable(name='tableName');
        t.timestamps();
        t.create();
      } catch (any ex) {
            hasError = true;
            catchObject = ex;
        }
        if (!hasError) {
            transaction action="commit";
        } else {
            transaction action="rollback";
            throw(errorCode="1", detail=catchObject.detail, message=catchObject.message, type="any");
        }
    }
}

Our default up() function will look something like this. Most of it you can actually ignore, as it's just wrapped in a transaction with some error handling. The important lines to look at are:

t = createTable(name='tableName');
t.timestamps();
t.create();

createTable() is the command to actually make the table: so we need to change this to users.

t = createTable(name='users');
t.timestamps();
t.create();

t.timestamps(); creates CFWheels automatic timestamp columns of createdAt,updatedAt and deletedAt.

The t.create(); is the final statement which executes the actual action.

What goes up...

Remember, the down() function needs to reverse these changes. so in our down() code block, we're going to change the dropTable('tableName'); to `dropTable('users');

Adding additional columns

Whilst we could execute this template in it's current state (we have an up function which creates, and a down function which drops) we wouldn't get much in the actual table. We can use the same migration file to add additional lines to create some columns to store things like firstname. Here's an example of a slightly more fleshed out migration file to give you some inspiration:

t = createTable(name='users');
t.string(
 columnNames='firstname,lastname,password',
 default='', null=false, limit='60');

t.string(
 columnNames='username,passwordresettoken,apikey',
 default='', null=true, limit='60');

t.string(
 columnNames='email,address1,address2,city,county,country,tel,www',
 default='', null=true, limit='255');

t.string(
 columnNames='title,postcode,lang,locale,timezone',
 default='', null=true, limit='15');

t.integer(columnNames='roleid', default='0', null=false, limit='11');
t.datetime(columnNames='pwresettokenat', default='', null=true);
t.datetime(columnNames='pwlastresetat', default='', null=true);

t.timestamps();
t.create();

As you can see, you can create multiple columns in a single call, set default values, whether to allow null values, and so on.

At this point, we can get going on actually creating this table

• Make sure that multiple column names in "columnNames" are only separated with ",". Don't use spaces like ", " as that space becomes part of a column name which will cause problems.

Creating Tables with composite primary keys

While t = createTable(name='users'); will create a standard auto-increment numeric ID, sometimes you need to create a table which has a composite, or non standard primary key. In this example, we're setting id=false on the createTable() call to bypass the default behavior, then specifying our primarykeys separately via primaryKey():

t = createTable(name='rolepermissions', id=false);
t.primaryKey(name="roleid", null=false, limit=11);
t.primaryKey(name="permissionid", null=false, limit=11);
t.create();

This would be a typical setup for a join table where you have a many to many relationship. Alternatively this can be useful if you need to specify a UUID as a primarykey.

Running a migration

Returning to our migration GUI, we can now see some options under the Migrations tab.

Simply click the button to migrate the database to our new version. From this screen we can also roll back to previous schema versions, or even reset the database back to 0.

Migrator Configuration Settings

Setting Column Types

The Migrator needs to run across multiple DB engines, it avoids direct use of varchar, as different adapters will need to use different column types etc. Therefore string translates to VARCHAR.

For instance, here's the mySQL variants:

• biginteger = BIGINT UNSIGNED

• binary = BLOB boolean = TINYINT',limit=1

• date = DATE datetime = DATETIME

• decimal = DECIMAL

• float = FLOAT

• integer = INT

• string = VARCHAR',limit=255

• text = TEXT

• time = TIME

• timestamp = TIMESTAMP

• uuid = VARBINARY', limit=16

Whereas SQL Server would use:

• primaryKey = "int NOT NULL IDENTITY (1, 1)

• binary = IMAGE

• boolean = BIT

• date = DATETIME

• datetime = DATETIME

• decimal = DECIMAL

• float = FLOAT

• integer = INT

• string = VARCHAR',limit=255

• text = TEXT

• time = DATETIME

• timestamp = DATETIME

• uniqueidentifier = UNIQUEIDENTIFIER

• char = CHAR',limit=10

Once you've created your migration files and committed your changes (you are all using source control - right?) you might be wondering about the different ways to migrate your database in a production environment.

Manual Migrations via GUI

Probably one of the most common and basic deployment types is a standalone virtual machine, running ACF or Lucee, with a database server such as MySQL running on the same box. In this scenario, we could probably stick with the simplest option: there is after all, probably only one instance of the site running.

• Put the site into maintenance mode (this is always good practice when deploying new code)

• Load the internal Migration GUI, migrate your database. Note: Ensure your IP address is in the maintenance mode exclusion list: the debug footer may not be available, so make a note of the url string ?controller=wheels&action=wheels&view=migrate

• Reload the application back into production mode

Automatic Migrations

You may well have a more complicated setup, such as being behind a load balancer, or having dynamic instances of your application - such as AWS ElasticBeanstalk - where logging into the same instance isn't practical; it may be your application is an API where a request could get routed to any node in the cluster, or that "sticky" sessions aren't enabled.

This means running the migrations manually via GUI isn't a practical option - you might accidentally leave a node in the cluster in maintenance mode and not be able to easily return to it etc.

In this scenario, you could use the built-in autoMigrateDatabase setting: this will automatically migrate the database to the latest schema version when the application starts.

This would fire for each node on a cluster and would fire on each application restart - however, the overhead would be minimal (one additional database call).

To activate this feature, just use set(autoMigrateDatabase=true) in your config/production.cfmsettings, to ensure it only fires in production mode.

Programmatic Migrations

It might be that full automatic migrations aren't necessary, or undesirable for some reason. You could have a script which essentially replaces the GUI functions and call the migration methods manually.

Please consult the internal documentation API reference under Configurations > Database Migrations for details of the various functions available to you.

Further considerations with automatic migrations

If you are using automatic migrations, then you could lock down production mode even further. With CFWheels 2.x there is more data available to development mode, such as the internal documentation, routing GUI and Migration GUI.

Turn off environment switching

You can force CFWheels to remain in production via set(allowEnvironmentSwitchViaUrl=false) - this will disable ?reload=maintenance style URLs where there is a configuration change, but simple reloading such as ?reload=true will still work. This setting should be approached with caution, as once you've entered into a mode with this setting on, you can't then switch out of it.