db-migrations.md 12.1 KB
Newer Older
Alexander Makarov committed
1 2 3
Database Migration
==================

4
> Note: This section is under development.
Qiang Xue committed
5

Larry Ullman committed
6 7
Like source code, the structure of a database evolves as a database-driven application is developed and maintained. For example, during development, a new table may be added; Or, after the application goes live, it may be discovered that an additional index is required. It is important to keep track of these structural database changes (called **migration**), just as changes to the source code is tracked using version control. If the source code and the database become out of sync, bugs will occur, or the whole application might break. For this reason, Yii provides a database migration
tool that can keep track of database migration history, apply new migrations, or revert existing ones.
Alexander Makarov committed
8

Larry Ullman committed
9
The following steps show how database migration is used by a team during development:
Alexander Makarov committed
10

Larry Ullman committed
11 12 13 14
1. Tim creates a new migration (e.g. creates a new table, changes a column definition, etc.).
2. Tim commits the new migration into the source control system (e.g. Git, Mercurial).
3. Doug updates his repository from the source control system and receives the new migration.
4. Doug applies the migration to his local development database, thereby syncing his database to reflect the changes Tim made.
Alexander Makarov committed
15

Larry Ullman committed
16
Yii supports database migration via the `yii migrate` command line tool. This tool supports:
Alexander Makarov committed
17

Larry Ullman committed
18 19 20
* Creating new migrations
* Applying, reverting, and redoing migrations
* Showing migration history and new migrations
Alexander Makarov committed
21 22 23 24

Creating Migrations
-------------------

Larry Ullman committed
25
To create a new migration, run the following command:
Alexander Makarov committed
26

27
```
28
yii migrate/create <name>
29
```
Alexander Makarov committed
30

Larry Ullman committed
31
The required `name` parameter specifies a very brief description of the migration. For example, if the migration creates a new table named *news*, you'd use the command:
Alexander Makarov committed
32

33
```
34
yii migrate/create create_news_table
35
```
Alexander Makarov committed
36

Larry Ullman committed
37 38 39 40 41
As you'll shortly see, the `name` parameter
is used as part of a PHP class name in the migration. Therefore, it should only contain letters,
digits and/or underscore characters.

The above command will create a new
42
file named `m101129_185401_create_news_table.php`. This file will be created within the `@app/migrations` directory. Initially, the migration file will be generated with the following code:
Alexander Makarov committed
43

44
```php
Alexander Makarov committed
45 46
class m101129_185401_create_news_table extends \yii\db\Migration
{
47 48 49 50 51 52 53 54 55
    public function up()
    {
    }

    public function down()
    {
        echo "m101129_185401_create_news_table cannot be reverted.\n";
        return false;
    }
Alexander Makarov committed
56
}
57
```
Alexander Makarov committed
58

Larry Ullman committed
59 60 61 62 63 64
Notice that the class name is the same as the file name, and follows the pattern
`m<timestamp>_<name>`, where:

* `<timestamp>` refers to the UTC timestamp (in the
format of `yymmdd_hhmmss`) when the migration is created,
* `<name>` is taken from the command's `name` parameter.
Alexander Makarov committed
65

Larry Ullman committed
66 67
In the class, the `up()` method should contain the code implementing the actual database
migration. In other words, the `up()` method executes code that actually changes the database. The `down()` method may contain code that reverts the changes made by `up()`.
Alexander Makarov committed
68

Larry Ullman committed
69 70 71 72
Sometimes, it is impossible for the `down()` to undo the database migration. For example, if the migration deletes
table rows or an entire table, that data cannot be recovered in the `down()` method. In such
cases, the migration is called irreversible, meaning the database cannot be rolled back to
a previous state. When a migration is irreversible, as in the above generated code, the `down()`
Alexander Makarov committed
73 74 75 76
method returns `false` to indicate that the migration cannot be reverted.

As an example, let's show the migration about creating a news table.

77
```php
Mark committed
78 79 80

use yii\db\Schema;

Alexander Makarov committed
81 82
class m101129_185401_create_news_table extends \yii\db\Migration
{
83 84
    public function up()
    {
85
        $this->createTable('news', [
86 87 88 89 90 91 92 93
            'id' => 'pk',
            'title' => Schema::TYPE_STRING . ' NOT NULL',
            'content' => Schema::TYPE_TEXT,
        ]);
    }

    public function down()
    {
94
        $this->dropTable('news');
95
    }
Mark committed
96

Alexander Makarov committed
97
}
98
```
Alexander Makarov committed
99 100 101 102

The base class [\yii\db\Migration] exposes a database connection via `db`
property. You can use it for manipulating data and schema of a database.

103 104 105 106 107
The column types used in this example are abstract types that will be replaced
by Yii with the corresponding types depended on your database management system.
You can use them to write database independent migrations.
For example `pk` will be replaced by `int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY`
for MySQL and `integer PRIMARY KEY AUTOINCREMENT NOT NULL` for sqlite.
108
See documentation of [[yii\db\QueryBuilder::getColumnType()]] for more details and a list
109
of available types. You may also use the constants defined in [[yii\db\Schema]] to
110
define column types.
111

112 113 114 115
> Note: You can add constraints and other custom table options at the end of the table description by
> specifying them as simple string. For example in the above migration, after `content` attribute definition
> you can write `'CONSTRAINT ...'` or other custom options.

116

Alexander Makarov committed
117 118 119 120 121 122
Transactional Migrations
------------------------

While performing complex DB migrations, we usually want to make sure that each
migration succeed or fail as a whole so that the database maintains the
consistency and integrity. In order to achieve this goal, we can exploit
Mark committed
123
DB transactions. We could use special methods `safeUp` and `safeDown` for these purposes.
Alexander Makarov committed
124

125
```php
Mark committed
126 127 128

use yii\db\Schema;

Alexander Makarov committed
129 130
class m101129_185401_create_news_table extends \yii\db\Migration
{
131 132
    public function safeUp()
    {
133
        $this->createTable('news', [
134 135 136 137 138
            'id' => 'pk',
            'title' => Schema::TYPE_STRING . ' NOT NULL',
            'content' => Schema::TYPE_TEXT,
        ]);

139
        $this->createTable('user', [
140 141 142 143 144 145 146 147
            'id' => 'pk',
            'login' => Schema::TYPE_STRING . ' NOT NULL',
            'password' => Schema::TYPE_STRING . ' NOT NULL',
        ]);
    }

    public function safeDown()
    {
148 149
        $this->dropTable('news');
        $this->dropTable('user');
150
    }
Alexander Makarov committed
151 152

}
153
```
Alexander Makarov committed
154

Mark committed
155 156
When your code uses more then one query it is recommended to use `safeUp` and `safeDown`.

Alexander Makarov committed
157 158 159 160 161 162 163 164 165 166 167 168
> Note: Not all DBMS support transactions. And some DB queries cannot be put
> into a transaction. In this case, you will have to implement `up()` and
> `down()`, instead. And for MySQL, some SQL statements may cause
> [implicit commit](http://dev.mysql.com/doc/refman/5.1/en/implicit-commit.html).


Applying Migrations
-------------------

To apply all available new migrations (i.e., make the local database up-to-date),
run the following command:

169
```
170
yii migrate
171
```
Alexander Makarov committed
172 173 174 175 176 177

The command will show the list of all new migrations. If you confirm to apply
the migrations, it will run the `up()` method in every new migration class, one
after another, in the order of the timestamp value in the class name.

After applying a migration, the migration tool will keep a record in a database
178 179
table named `migration`. This allows the tool to identify which migrations
have been applied and which are not. If the `migration` table does not exist,
Alexander Makarov committed
180 181 182 183 184 185
the tool will automatically create it in the database specified by the `db`
application component.

Sometimes, we may only want to apply one or a few new migrations. We can use the
following command:

186
```
187
yii migrate/up 3
188
```
Alexander Makarov committed
189 190 191 192 193 194

This command will apply the 3 new migrations. Changing the value 3 will allow
us to change the number of migrations to be applied.

We can also migrate the database to a specific version with the following command:

195
```
196
yii migrate/to 101129_185401
197
```
Alexander Makarov committed
198 199 200 201 202 203 204 205 206 207 208 209 210 211

That is, we use the timestamp part of a migration name to specify the version
that we want to migrate the database to. If there are multiple migrations between
the last applied migration and the specified migration, all these migrations
will be applied. If the specified migration has been applied before, then all
migrations applied after it will be reverted (to be described in the next section).


Reverting Migrations
--------------------

To revert the last one or several applied migrations, we can use the following
command:

212
```
213
yii migrate/down [step]
214
```
Alexander Makarov committed
215 216 217 218 219 220 221 222 223 224 225 226 227 228

where the optional `step` parameter specifies how many migrations to be reverted
back. It defaults to 1, meaning reverting back the last applied migration.

As we described before, not all migrations can be reverted. Trying to revert
such migrations will throw an exception and stop the whole reverting process.


Redoing Migrations
------------------

Redoing migrations means first reverting and then applying the specified migrations.
This can be done with the following command:

229
```
230
yii migrate/redo [step]
231
```
Alexander Makarov committed
232 233 234 235 236 237 238 239 240 241 242

where the optional `step` parameter specifies how many migrations to be redone.
It defaults to 1, meaning redoing the last migration.


Showing Migration Information
-----------------------------

Besides applying and reverting migrations, the migration tool can also display
the migration history and the new migrations to be applied.

243
```
244 245
yii migrate/history [limit]
yii migrate/new [limit]
246
```
Alexander Makarov committed
247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262

where the optional parameter `limit` specifies the number of migrations to be
displayed. If `limit` is not specified, all available migrations will be displayed.

The first command shows the migrations that have been applied, while the second
command shows the migrations that have not been applied.


Modifying Migration History
---------------------------

Sometimes, we may want to modify the migration history to a specific migration
version without actually applying or reverting the relevant migrations. This
often happens when developing a new migration. We can use the following command
to achieve this goal.

263
```
264
yii migrate/mark 101129_185401
265
```
Alexander Makarov committed
266

267
This command is very similar to `yii migrate/to` command, except that it only
Alexander Makarov committed
268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292
modifies the migration history table to the specified version without applying
or reverting the migrations.


Customizing Migration Command
-----------------------------

There are several ways to customize the migration command.

### Use Command Line Options

The migration command comes with four options that can be specified in command
line:

* `interactive`: boolean, specifies whether to perform migrations in an
  interactive mode. Defaults to true, meaning the user will be prompted when
  performing a specific migration. You may set this to false should the
  migrations be done in a background process.

* `migrationPath`: string, specifies the directory storing all migration class
  files. This must be specified in terms of a path alias, and the corresponding
  directory must exist. If not specified, it will use the `migrations`
  sub-directory under the application base path.

* `migrationTable`: string, specifies the name of the database table for storing
293
  migration history information. It defaults to `migration`. The table
Alexander Makarov committed
294 295 296 297 298 299 300 301 302 303 304 305 306
  structure is `version varchar(255) primary key, apply_time integer`.

* `connectionID`: string, specifies the ID of the database application component.
  Defaults to 'db'.

* `templateFile`: string, specifies the path of the file to be served as the code
  template for generating the migration classes. This must be specified in terms
  of a path alias (e.g. `application.migrations.template`). If not set, an
  internal template will be used. Inside the template, the token `{ClassName}`
  will be replaced with the actual migration class name.

To specify these options, execute the migrate command using the following format

307
```
308
yii migrate/up --option1=value1 --option2=value2 ...
309
```
Alexander Makarov committed
310 311 312 313 314

For example, if we want to migrate for a `forum` module whose migration files
are located within the module's `migrations` directory, we can use the following
command:

315
```
Vladimir committed
316
yii migrate/up --migrationPath=@app/modules/forum/migrations
317
```
Alexander Makarov committed
318 319 320 321 322 323 324 325 326 327 328


### Configure Command Globally

While command line options allow us to configure the migration command
on-the-fly, sometimes we may want to configure the command once for all.
For example, we may want to use a different table to store the migration history,
or we may want to use a customized migration template. We can do so by modifying
the console application's configuration file like the following,

```php
Mark committed
329 330
'controllerMap' => [
    'migrate' => [
331
        'class' => 'yii\console\controllers\MigrateController',
Mark committed
332 333 334
        'migrationTable' => 'my_custom_migrate_table',
    ],
]
Alexander Makarov committed
335 336 337
```

Now if we run the `migrate` command, the above configurations will take effect
Mark committed
338 339
without requiring us to enter the command line options every time. Other command options
can be also configured this way.