Creating the database with migrations

Now that our application can be bootstrapped, we can create our database. To do this, we are going to create a migration. Migrations are a feature of Yii that allow the creation and modification of your database to be a part of your application. Rather than creating schema modifications in pure SQL, we can use migrations to grow our database as a part of our application. In addition to acting as a revision system for our database schema, migrations also have the added benefit of allowing us to transmit our database with our application without having to worry about sharing data that would be stored in our database.

To create our database, open up your command-line interface of choice, navigate to your tasks directory, and run the following command:

$ php protected/yiic.php migrate create tasks

The yiic command will then prompt you to confirm the creation of the new migration:

Yii Migration Tool v1.0 (based on Yii v1.1.14)

Create new migration '/var/www/tasks/protected/migrations/m131213_013354_tasks.php'? (yes|no) [no]:yes
New migration created successfully.

Tip

To prevent naming conflicts with migrations, yiic will create the migration with the following naming structure: m<timestamp>_<name>. This has the added benefit of allowing us to sequentially apply or remove specific migrations based upon the order in which they were added. The exact name of your migration will be slightly different than the one listed in the preceding command.

After confirming the creation of the migration, a new file will be created in the protected/migrations folder of our application. Open up the file, and add the following to the up method:

$this->createTable('tasks', array(
   'id' => 'INTEGER PRIMARY KEY',
   'title' => 'TEXT',
   'data' => 'TEXT',
   'project_id' => 'INTEGER',
   'completed' => 'INTEGER',
   'due_date' => 'INTEGER',
   'created' => 'INTEGER',
   'updated' => 'INTEGER'
));

$this->createTable('projects', array(
   'id' => 'INTEGER PRIMARY KEY',
   'name' => 'TEXT',
   'completed' => 'INTEGER',
   'due_date' => 'INTEGER',
   'created' => 'INTEGER',
   'updated' => 'INTEGER'
));

Notice that our database structure matches the schema that we identified earlier in the chapter.

Next, replace the contents of the down method with instructions to drop the database table if we call migrate down from the yiic command. Have a look at the following code:

$this->dropTable('projects');
$this->dropTable('tasks');

Now that the migration has been created, run migrate up from the command line to create the database and apply our migration. Run the following commands:

$ php protected/yiic.php migrate up
Yii Migration Tool v1.0 (based on Yii v1.1.14)
Total 1 new migration to be applied:
 m131213_013354_tasks

Apply the above migration? (yes|no) [no]:yes
*** applying m131213_013354_tasks
*** applied m131213_013354_tasks (time: 0.009s)
Migrated up successfully.

Now, if you navigate to protected/data/, you will see a new file called tasks.db, the SQLite database that was created by our migrations.

Tip

Migration commands can be run non-interactively by appending --interactive=0 to the migrate command. This can be useful if you want to automate deployments of your code to remote systems or if you run your code through an automated testing service.