Migrations

Background

Migrations are CFCs which are specifically designed to run in a particular order and only once. They are typically used to change the state of a environment. For example, each member of a development team is running a copy of their site locally including their own database. There is a schema change which each of them need to apply to their local database. A migration is created that will run and add the new schema to each of their local environments.

We are going to use them here to create a schema in our database and populate it with some starter data.

Install the Module

1. From CommandBox run install commandbox-migrations

Init Migrations

1. Because of some backwards compatability, migrations needs a special file in the root called box.json which we are going use later on but need it to exist now. Type init

2. To set up migrations to work for our site type migrate init . This will create a .cfmigrations.json file in the root of the site. Open it up in your IDE. Notice how the connectionInfo node has all the properties of a datasource? Because migrations can run from CommandBox as well as conventionally in a site, the method of obtaining a datasource can vary so it stores its own and can pull from a .env file in each environment.

3. In the .cfmigrations.json file, in the connectionInfo node, remove the connectionString and add "type":"${DB_TYPE}"

Install Migrations onto our DB

In order to know what migrations have already been run, it uses a table called cfmigrations on the db to track what has run and when.

1. In CommandBox, from the root of the site, type migrate install.

2. Put the following into your index.cfm page. If there is no error, the cfmigrations table is installed.

           <cfquery name="migrations" datasource="justEnough">
               select * from cfmigrations
           </cfquery>
   
           <cfdump var="#migrations#" />
       

Run Migrations

1. From CommandBox, type migrate create add_users_table

2. This created a folder structure resources/database/migrations and put a file with a prefix and add_users_table at the end. This prefix is a timestamp when the file was created. Migrations will run the files in chronological order and record that it's been run in the cfmigrations table.

3. Copy this into that file\

   component {
       
       function up( schema, qb ) {
           schema.create( "users", function( table ) {
               table.increments( "id" );
               table.string( "email" );
               table.string( "password" );
               table.timestamp( "created_date" );
               table.timestamp( "modified_date" );
               table.timestamp( "last_logged_in" ).nullable();
           } );
   
           var nowDate = dateFormat(now(),"yyyy-mm-dd");
           queryExecute("
               insert into users (email, password, created_date) values ('[email protected]','password1','#nowDate#');
           ");
           queryExecute("
               insert into users (email, password, created_date) values ('[email protected]','password2','#nowDate#');
           ")
           queryExecute("
               insert into users (email, password, created_date) values ('[email protected]','password3','#nowDate#');
           ")
   
   
       }
   
       function down( schema, qb ) {
           
       }
   
   }

4. Migrations have two functions - up and down. Up makes the change and down undoes it if needed.

5. Any code can be be put into those functions. What is here is a mix of code from a module called QB which we will talk about in a later session and general queryExecute( ) statements.

6. From CommandBox run migrate up

7. Add to your index.cfm and reload. You should see the user table populated and the data populated.

           <cfquery name="users" datasource="justEnough">
               select * from users
           </cfquery>
   
           <cfdump var="#users#" />