Finally, a framework for the rest of us!

CFWheels is an open source CFML (ColdFusion Markup Language) framework inspired by Ruby on Rails that provides fast application development, a great organization system for your code, and is just plain fun to use.

One of our biggest goals is for you to be able to get up and running with CFWheels quickly. We want for you to be able to learn it as rapidly as it is to write applications with it.

Get Started    

Getting Started

Database Migrations are an easy way to build and alter your database structure using cfscript and even deploy across different database engines

With CFWheels 2.x, you can now create, alter and populate your database via cfscript in an organised manner. Using custom CFC files, you can create an organised 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

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
Type
Default
Description

autoMigrateDatabase

Boolean

false

Automatically runs available migration on applicationstart.

migratorTableName

String

migratorversions

The name of the table that stores the versions migrated.

createMigratorTable

Boolean

true

Create the migratorversions database table.

writeMigratorSQLFiles

Boolean

false

Writes the executed SQL to a .sql file in the /migrator/sql directory.

migratorObjectCase

String

lower

Specifies the case of created database object. Options are 'lower', 'upper' and 'none' (which uses the given value unmodified)

allowMigrationDown

Boolean

false (true in development mode)

Prevents 'down' migrations (rollbacks)

Getting Started

Database Migrations are an easy way to build and alter your database structure using cfscript and even deploy across different database engines