tutorialcreate

Tutorial :: Creating a Table

We'll begin this tutorial by creating a simple migration to create a table. We'll assume you've already followed the Getting Started instructions and installed the DBMigrate plugin and setup your database.

In this example we'll create a table to store member login information for a website.

Option 1. Beginning from a template

Click on the DBMigrate link under Plugins in the CFWheels framework debugging footer, then scroll to the bottom of the page to the section labeled "Create new migration file from template".

Select the Create table template.

You can select whether to use Timestamp prefixing (best when working as a team) or Numeric prefixing (simpler and more readable).

Then enter a description of what the migration does such as creates member table.

If you select numeric prefixing, this will create a file called 001_creates_members_table.cfc in the /db/migrate/ folder.

Open the file and change [tableName] to members.

Option 2. Beginning from a blank file

First, create a migration CFC file called 001_members.cfc in the /db/migrate/ folder.

This CFC needs to extend plugins.dbmigrate.Migration. (click here for more details)

Provide a descriptive hint attribute for the tag to indicate what this migration does.

Add two functions, one named up for making changes to the database (i.e. creating the table), and one named down for reversing those changes. Then for each function add some tags.

Your empty migration file will look something like this:

<cfcomponent extends="plugins.dbmigrate.Migration" hint="creates member table">
  <cffunction name="up">
    <cfscript>
    
    </cfscript>
  </cffunction>
  <cffunction name="down">
    <cfscript>
    
    </cfscript>
  </cffunction>
</cfcomponent>

Now in the up function we are going to add some script to create the members table. To begin we use the createTable function to create a Table Definition object.

t = createTable('members');

A primary key named id will automatically be added by default.

Adding columns

Next we add some columns to the table definition. First we add a string column to store the members' email address:

t.string('email');

Next we'll add columns for the members' name. This time we'll added two columns together (using a list of names) and also specify a character limit rather than use the default of 255. Note we are using camel case for column names and also need to now specify argument names.

t.string(columnNames='firstname,lastname',limit=50);

We'll also store a hashed password column.

t.string(columnNames='password',limit=32);

Next we want to record whether or not the member is a site administrator using a boolean column. This time we'll also specify a default value for the column and indicate we do not want to allow nulls.

t.boolean(columnNames='isadministrator',default=false,null=false);

Finally we want to add some automated timestamps for CFWheels to automatically record when the record was created (createdat), last updated (updatedat) and a timestamp for soft deletion (deletedat). We can do this using a single function named [APITableDefinitionTimestamps timestamps].

t.timestamps();

Now we have our table definition, we want to commit these instructions to the database. For new tables we do this using the [APITableDefinitionCreate create] function.

t.create();

For the down function in the migration we want to reverse this change (i.e. remove the table from the database. We do this using the [APIMigrationDropTable dropTable] function.

dropTable('members');

So our finally migration file looks like this:

<cfcomponent extends="plugins.dbmigrate.Migration" hint="creates member table">
  <cffunction name="up">
    <cfscript>
    t = createTable('members');
    t.string('email');
    t.string(columnNames='firstname,lastname',limit=50);
    t.string(columnNames='password',limit=32);
    t.boolean(columnNames='isadministrator',default=false,null=false);
    t.timestamps();
    t.create();
    </cfscript>
  </cffunction>
  <cffunction name="down">
    <cfscript>
    dropTable('members');
    </cfscript>
  </cffunction>
</cfcomponent>

Save your file and load up your application in a browser. Click on the DBMigrate link under Plugins in the CFWheels framework debugging footer. You should see that the current database version is 0 and your create members table migration should be listed under the available migrations.

Use the select box to choose this migration and click the go button. This will migrate the database to version 001 and provide feedback to say that it Created table members.

Check your database to confirm. You will also notice that the plugin created an extra table named schemainfo to store the current version number.

Now try migrating the database back to version 0 and check the database again to confirm the members table has been dropped.

Before you continue re-run the migration to return to version 001.

Next we'll look at modifying an existing table.