tutorialmodify

Tutorial :: Modifying an existing table

As your project develops you will often need to make modifications to a table you've already created (due to changing specifications or things you didn't foresee when you first created the table).

If the create table migration has already been committed to your version control repository and other developers have already run that migration, then it's not ideal to change the code of the original migration file. Instead we'll create a new migration CFC to make those changes.

Perhaps you decide you want people to login with a username rather than their email address and that you also want to record a status column for each member.

We can use the changeTable function to add these extra columns.

Begin by creating a new migration CFC file named 002_member_changes.cfc in the /db/migrate folder. Give the migration a hint like "modifies member table".

Your migration CFC should look something like this:

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

Next, in the up function, get a Table Definition object for the members table using the changeTable function.

t = changeTable('members');

Then add the two new columns in the same way we initially added columns to the table.

t.string(columnNames='username',limit=20);
t.string(columnNames='status',limit=25);

Then call the change function to execute these changes.

t.change();

Note that instead of using the changeTable function, we could have executed two separate addColumn function calls. e.g.

addColumn(table='members',columnType='string',columnName='username',limit=20);
addColumn(table='members',columnType='string',columnName='status',limit=25);

For the down function we need to reverse these changes using the removeColumn function.

removeColumn(table='members',columnName='username');
removeColumn(table='members',columnName='status');

So our final migration file looks like this:

<cfcomponent extends="plugins.dbmigrate.Migration" hint="modifies member table">
  <cffunction name="up">
    <cfscript>
    t = changeTable('members');
    t.string(columnNames='username',limit=20);
    t.string(columnNames='status',limit=25);
    t.change();
    </cfscript>
  </cffunction>
  <cffunction name="down">
    <cfscript>
    removeColumn(table='members',columnName='username');
    removeColumn(table='members',columnName='status');
    </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, or if you already have it open, refresh the page. You should see that this new migration shows up in the available migrations.

Use the select box to choose this migration and click the go button. This will migrate the database to version 002 and provide feedback to say that it added the new columns.

Check your database to confirm.

Now try migrating the database back to version 001 and check the database again to confirm the columns have been removed.

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

Next we'll look at adding reference columns.