Writing database migrations_Mastering Yii-QQ阅读女频幻言网

上QQ阅读APP看书，第一时间看更新

Writing database migrations

When building and maintaining modern web applications, the underlying structure of our database may need to change to account for changes in requirements or scopes. To ensure that our database schema can evolve in tandem with our source code, Yii2 provides built-in support to manage database migrations. Using database migrations, we can treat our database as an extension of the source code and easily change it when our source code changes.

An overview of schema

When working with database migrations, we'll often be working with the yii\db\Schema class. When paired properly, we can often write our migrations in a way that enables them to be run across a variety of database types. For example, when working locally, we might need to use a local SQLite database even if our application will ultimately run on a MySQL database.

At the heart of this class is a variety of different schema types that Yii2 will be able to properly map to the appropriate data type within our database. These include data types such as INT, DATETIME, and TEXT.

Tip

For a complete list of the available constants made available by the Schema class, ensure that you refer to the Yii2 guide at http://www.yiiframework.com/doc-2.0/yii-db-schema.html#constants.

Within our migrations, we can call any of these constants by running this:

Schema::<CONSTANT>

In the example of an integer, we can use this:

Schema::TYPE_INTEGER

Using these constants in our migration, we can ensure that our migrations map to the appropriate data type within our database and work across a variety of database types.

Writing migrations

As shown in the previous chapter, we can create a new migration by invoking the migrate/create command from the yii command-line tool. Using the source code from the previous chapter as a starting point, we'll do this by running the following from the command line:

./yii migrate/create init

Running this command will create a new migration in the migrations folder of our application.

Tip

Depending upon the file permissions on your system, Yii2 may not be able to create the migrations folder if it does not exist. If the migrations folder doesn't exist yet, ensure that you create it before running the migrate/create command.

When running migrations, Yii2 will execute them in the order in which they were created. To determine this order, Yii2 will look at the filename or the migration that contains the name of the migration specified from the migrate/create command as well as the exact timestamp the migration was created at.

In our case, the filename is m150523_194158_init.php, which means that this migration was created on May 23, 2015 at 7:41:58 PM UTC.

Tip

Because of this naming convention, any migration that you create will have a distinct and unique filename. If you're following along, ensure that you're working in the file that was created from the ./yii command.

After running the migrate/create command, Yii2 provides us with a skeleton migration that will look similar to the following code block:

<?php

use yii\db\Schema;
use yii\db\Migration;

class m150523_194158_init extends Migration
{
    public function up() {}

    public function down()
    {
        echo "m150523_194158_init cannot be reverted.\n";
        return false;
    }
    
    /*
    // Use safeUp/safeDown to run migration code within a transaction
    public function safeUp() {}
    
    public function safeDown() {}
    */
}

Migrations in Yii2 can operate in one of these two ways: we can either bring a migration up, or we can bring it down. These two operations correspond to one of four functions: up(), safeUp(), down(), and safeDown(). The up() and down() methods are the base methods required to run migrations and will execute any database command issued inside them even if there is an error. Alternatively, we can use the safeUp() and safeDown() methods, which are functionally identical to the up() and down() methods, with the exception that the entire operation is wrapped within a transaction. If our database supports transactions, running our migrations from the safe methods can help us catch migration errors at runtime before an error can cause problems with our entire database.

Tip

Because of the additional safety they offer, safeUp() and safeDown() should be our go-to methods when writing migrations. Additionally, if safeUp() or safeDown() are used, the unsafe methods cannot be used.

Let's start by adding a simple table to our database in order to store our users. We'll start by simply storing an ID, an email address, a password, the username, and some timestamp metadata indicating when our user was created and last updated. Within our migration, we can write this as follows:

class m150523_194158_init extends Migration
{
    public function safeUp()
    {
         return $this->createTable('user', [
            'id'           => Schema::TYPE_PK, // $this->primaryKey()
            'email'        => Schema::TYPE_STRING, // $this->string(255) // String with 255 characters
            'password'     => Schema::TYPE_STRING,
            'name'         => Schema::TYPE_STRING,
            'created_at'   => Schema::TYPE_INTEGER, // $this->integer()
            'updated_at'   => Schema::TYPE_INTEGER
        ]);
    }
    
    public function safeDown()
    {
        return $this->dropTable('user');
    }
}

Tip

As illustrated previously, Yii2 supports two different ways to declare schema types for columns. We can either directly use the constants defined by the Schema class, or we can use the native migration methods, such as primaryKey(), integer(), string(), and text(). Using the migration methods is preferred because it permits us to add additional attributes to our column, such as the column size and length. For a complete list of methods offered by the migration class, refer to the Yii2 guide at http://www.yiiframework.com/doc-2.0/yii-db-migration.html.

In the previous example, we outlined two methods: createTable(), which will create a new database table within our application, and dropTable(), which will drop the table from our database.

Tip

A common convention when working with a database is to write field names with underscores and use singular names for table and column names. While Yii2 is smart enough to work with any field names you specify, following this convention will make your code more readable and working with your databases less complicated. While you don't have to explicitly follow this convention, following a convention can save you a lot of time in the future.

Running migrations

Running our migrations can be done through the yii command, as shown in the previous chapter:

./yii migrate/up

Since we're using a SQLite database in our example, we can easily explore what just happened when we ran the migrate/up command. Using the sqlite command-line tool, we can explore our SQLite database:

sqlite3 /path/to/runtime/db.sqlite

Tip

If your package manager does not provide sqlite3, you can download the binary executables from https://www.sqlite.org/download.html.

By running the .tables command from our SQLite prompt, we can see that two tables were created when we ran the migrate/up command, migration and user:

sqlite> .tables

The first table, migration, contains a list of all the applied migrations as well as the time at which they were applied.

The second table, user, shows the resulting schema that was created by Yii from our migration class.

For instance, by specifying the TYPE_PK schema for our ID attribute, Yii2 knew that it needed to add AUTOINCRIMENT and NOT NULL attributes to our SQLite schema.

Tip

While database migrations are suited for most database changes, running them against large datasets may result in your database being unavailable to your application, resulting in downtime. Make sure that before you run a database migration through Yii2, your application should be able to handle temporary downtime. If even temporary downtime is not appropriate for your application, you may need to consider migrating your data to an updated schema in other ways.

Altering a database schema

When developing locally, we can simply use the migrate/down command to undo a specific migration (assuming we implemented a down() or safeDown() method). However, after committing and pushing our code to our DCVS system, such as Git or SVN, others may be using or working with our code. In this instance, we want to change our migrations without causing harm to their local instance; we can create new migrations that users of our code can apply in order to bring their applications up to date.

Take, for instance, the user schema that was created for us:

CREATE TABLE `user` (
        `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
        `email` varchar(255),
        `password` varchar(255),
        `name` varchar(255),
        `created_at` integer,
        `updated_at` integer
);

Rather than having a single field for our username, we may want to have two fields: one for their first name and one for their last name. We may also want to make a few changes to other fields, such as our email field, to prevent them from being NULL. We can do this by writing a new migration and altering the schema of the database itself.

We'll start by creating a new migration:

./yii migrate/create name_change --interactive=0

Tip

Remember, the --interactive=0 flag tells Yii to run our console command without prompts.

Within our new migrations/…name_change.php migration, we can write a safeUp() method to alter these columns for us:

public function safeUp()
{
    $this->renameColumn('user', 'name', 'first_name');
    $this->alterColumn('user', 'first_name', SCHEMA::TYPE_STRING);
    $this->addColumn('user', 'last_name', SCHEMA::TYPE_STRING);
    $this->alterColumn('user', 'email', SCHEMA::TYPE_STRING . ' NOT NULL');
    $this->createIndex('user_unique_email', 'user', 'email', true);
}

In Yii2, migration commands are self-explanatory in what they do. For instance, the first method, renameColumn(), will simply rename the name column to first_name. In the same vein, addColumn() will add a new column with the specified name and schema to our database, alterColumn() will alter the schema for the named column, and createIndex() will create a unique index on the email field in our database, which will ensure that no two users will share the same email address.

Tip

A complete list of commands that can be run within the migration calls can be found in the Yii2 guide at http://www.yiiframework.com/doc-2.0/yii-db-migration.html.

If we try to run these migrations against our SQLite database, however, we would be presented with an error similar to the following, indicating that SQLite doesn't have support for these methods:

./yii migrate/up

Here's the output:

*** applying m150523_203944_name_change
 > rename column name in table user to first_name \
...Exception: yii\db\sqlite\QueryBuilder::renameColumn is not \
 supported by SQLite. \
(/var/www/ch3/vendor/yiisoft/yii2/db/sqlite/QueryBuilder.php:201)

While the previously listed migration would work on MySQL or PostgreSQL, our SQLite driver doesn't provide support for these commands. Since we're using SQLite, however, we'd have to rewrite our initial migration command and notify users of our application about the change. For SQLite, we can rewrite our newly created migrations/…name_change.php migration as follows:

public function safeUp()
{
    $this->dropTable('user');
    
    $this->createTable('user', [
        'id'           => Schema::TYPE_PK,
        'email'        => Schema::TYPE_STRING . ' NOT NULL',
        'password'     => Schema::TYPE_STRING . ' NOT NULL',
        'first_name'   => Schema::TYPE_STRING,
        'last_name'    => Schema::TYPE_STRING,
        'created_at'   => Schema::TYPE_INTEGER,
        'updated_at'   => Schema::TYPE_INTEGER
    ]);
    
    $this->createIndex('user_unique_email', 'user', 'email', true);
}

public function safeDown()
{
    return true;
}

Tip

yii\db\Migration does not have a query() method that we can use to retrieve data. Consequently, if we need to query data within a migration, we will need to use Yii2's Query Builder to do this, which we'll cover later in this chapter. If our application has widespread adoption, it might be better to query for all of our users with Query Builder and store them temporarily in the memory (or a temporary store if we have a large number of records). Then, after creating our new table schema for our users table, we could then reinsert them into our database using the insert() method.

After updating our new migration, we can rerun our migration command. Since our first migration was already applied, that migration will be skipped when the migrate/up command is executed, and only our migrations/m150523_203944_change.php migration will be run:

./yii migrate/up

After running our migration, we can query our database to see what our full schema looks like within SQLite:

sqlite3 /path/to/runtime/db.sqlite

Tip

Migrations in Yii2 are extremely powerful. Take a look at the Yii2 documentation at http://www.yiiframework.com/doc-2.0/yii-db-migration.html to see everything that you can do with yii\db\Migration.