Migrations
A migration file is a component with two methods up
and down
. The function up
should define how to apply the migration. The function down
should define how to undo the change down in up
. For QBMigrationManager
migrations (which is the default), the up
and down
functions are passed an instance of SchemaBuilder@qb
and QueryBuilder@qb
as arguments. To learn more about the functionality and benefits of SchemaBuilder
, QueryBuilder
, and qb
, please read the QB documentation here. In brief, qb
offers a fluent, expressive syntax that can be compiled to many different database grammars, providing both readability and flexibility.
Here's the same example as above using qb's SchemaBuilder
:
Migration files need to follow a specific naming convention — YYYY_MM_DD_HHMISS_[describe_your_changes_here].cfc
. This is how cfmigrations
knows in what order to run your migrations. Generating these files is made easier with the migrate create
command from commandbox-migrations
.
Using the injected qb
instance, you can insert or update required data for your application. If you want to create test data for your application, take a look at seeders below instead.
There is no limit to what you can do in a migration. It is recommended that you separate changes to different tables to separate migration files to keep things readable.
Tips and tricks
Setting Schema
It's important to set the schema
attribute for cfmigrations
. Without it, cfmigrations
can't tell the difference between a migration table installed in the schema you want and any other schema on the same database. You can set the schema by calling the setSchema( string schema )
method.
Default values in MS SQL server
MS SQL server requires some special treatment when removing columns with default values. Even though syntax is almost the same, MS SQL creates a special default constraint like DF_tablename_columname
. When migrating down, this constraint has to be removed before dropping the column. In other grammars no special named constraint is created.
Example:
Updating database content in a migration file
Sometimes you want to do multiple content updates or inserts in a migration. In this case you can use the QueryBuilder for the updates. When doing your second update you have to reset the Querybuilder object by using the newQuery method.
Example:
Last updated