Database migration

Database migration can be understood as version management of the database structure, which can effectively solve the management of the database structure across members of the team.

Generate migrations

Generate a migration file via gen:migration, the command is followed by a filename parameter, usually for what the migration is intended to do.

php bin/hyperf.php gen:migration create_users_table

The generated migration files are located in the migrations folder in the root directory, and each migration file includes a timestamp so that the migration program can determine the order of migrations.

The --table option can be used to specify the name of the data table. The specified table name will be generated in the migration file by default. The --create option is also used to specify the name of the data table, but the difference from --table is that this option generates a migration file for creating a table, while --table is a migration file for modifying the table.

php bin/hyperf.php gen:migration create_users_table --table=users
php bin/hyperf.php gen:migration create_users_table --create=users

Migration structure

The migration class will contain 2 methods by default: up and down. The up method is used to add a new data table, field or index to the database, and the down method is the inverse of the up method, which is the opposite of the operation in up, so that it is executed during rollback.

<?php

use Hyperf\Database\Schema\Schema;
use Hyperf\Database\Schema\Blueprint;
use Hyperf\Database\Migrations\Migration;

class CreateUsersTable extends Migration
{
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        Schema::create('true', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        Schema::dropIfExists('true');
    }
}

Run migration

Run all pending migration files by executing the migrate command:

php bin/hyperf.php migrate

Force the migration

Some migration operations are destructive, which means that data loss may result. To prevent someone from running these commands in a production environment, the system will confirm with you before these commands are run, but if you wish to ignore these confirmations, force To run a command, you can use the --force flag:

php bin/hyperf.php migrate --force

Rollback migration

If you want to roll back the last migration, you can use the migrate:rollback command to roll back the last migration. Note that a migration may contain multiple migration files:

php bin/hyperf.php migrate:rollback

You can also set the number of rollback migrations by appending the step parameter to the migrate:rollback command. For example, the following command will roll back the last 5 migrations:

php bin/hyperf.php migrate:rollback --step=5

If you wish to roll back all migrations, you can do so with migrate:reset:

php bin/hyperf.php migrate:reset

Rollback & Migrate

migrate:refresh The command not only rolls back the migration but also runs migrate command, which rebuilds some migrations efficiently:

php bin/hyperf.php migrate:refresh

// Rebuild database structure and perform data population
php bin/hyperf.php migrate:refresh --seed

Specify the number of rollbacks and rebuilds with the --step parameter. For example, the following command will rollback and re-execute the last 5 migrations:

php bin/hyperf.php migrate:refresh --step=5

Rebuild database

The entire database can be efficiently rebuilt with the migrate:fresh command, which deletes all databases before executing the migrate command:

php bin/hyperf.php migrate:fresh

// Rebuild database structure and perform data population
php bin/hyperf.php migrate:fresh --seed

Schema

In the migration file, the Hyperf\Database\Schema\Schema class is mainly used to define the data table and manage the migration process.

Create table

Create a new database table with the create method. The create method accepts two parameters: the first parameter is the name of the data table, and the second parameter is a Closure, which will receive a Hyperf\Database' to define the new data table \Schema\Blueprint object:

<?php

use Hyperf\Database\Schema\Schema;
use Hyperf\Database\Schema\Blueprint;
use Hyperf\Database\Migrations\Migration;

class CreateUsersTable extends Migration
{
    public function up(): void
    {
        Schema::create('users', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->timestamps();
        });
    }
}

You can use the following commands on the database structure generator to define options for a table:

// Specify the table storage engine
$table->engine = 'InnoDB';
// Specifies the default character set for data tables
$table->charset = 'utf8';
// Specifies the default collation of the data table
$table->collation = 'utf8_unicode_ci';
// Create a temporary table
$table->temporary();

Rename table

If you wish to rename a data table, you can use the rename method:

Schema::rename($from, $to);

Rename table with foreign key

Before renaming a table, you should verify that all foreign key constraints on the table have an explicit name in the migration file, rather than letting the migration program set a name by convention, otherwise, the foreign key's constraint name will refer to the old table name .

Drop table

To drop an existing table, use the drop or dropIfExists methods:

Schema::drop('users');

Schema::dropIfExists('users');

Check if the data table or field exists

The hasTable and hasColumn methods can be used to check whether a data table or field exists:

if (Schema::hasTable('users')) {
    //
}

if (Schema::hasColumn('name', 'email')) {
    //
}

Database connection options

If multiple databases are managed at the same time, different migrations will correspond to different database connections, then we can define different database connections in the migration file by overriding the $connection class attribute of the parent class:

<?php

use Hyperf\Database\Schema\Schema;
use Hyperf\Database\Schema\Blueprint;
use Hyperf\Database\Migrations\Migration;

class CreateUsersTable extends Migration
{
    // This corresponds to the connection key in config/autoload/databases.php
    protected $connection = 'foo';
    
    public function up(): void
    {
        Schema::create('users', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->timestamps();
        });
    }
}

Fields

Create fields

Define the definition or change to be performed by the migration file within the Closure of the second parameter of the table or create method. For example, the following code defines a string field of name:

<?php

use Hyperf\Database\Schema\Schema;
use Hyperf\Database\Schema\Blueprint;
use Hyperf\Database\Migrations\Migration;

class CreateUsersTable extends Migration
{   
    public function up(): void
    {
        Schema::create('users', function (Blueprint $table) {
            $table->string('name');
        });
    }
}

Available field definition methods

Modify fields

Prerequisites

Make sure to add the doctrine/dbal dependency to the composer.json file before modifying the fields. The Doctrine DBAL library is used to determine the current state of a field and create the SQL query required to make the specified adjustments to that field:

composer require "doctrine/dbal:^3.0"

Update field properties

change Methods can modify existing field types to new types or modify other properties.

<?php

Schema::create('users', function (Blueprint $table) {
    // Modify the length of the field to 50
    $table->string('name', 50)->change();
});

Or modify the field to be nullable:

<?php

Schema::table('users', function (Blueprint $table) {
    // Modify the length of the field to 50 and allow null
    $table->string('name', 50)->nullable()->change();
});

Only the following field types can be "modified": bigInteger, binary, boolean, date, dateTime, dateTimeTz, decimal, integer, json, longText, mediumText, smallInteger, string, text, time, unsignedBigInteger, unsignedInteger and unsignedSmallInteger.

Rename field

Fields can be renamed via the renameColumn method:

<?php

Schema::table('users', function (Blueprint $table) {
    // Rename field from from to to
    $table->renameColumn('from', 'to')->change();
});

Field renaming of type enum is not currently supported.

Delete field

Fields can be dropped via the dropColumn method:

<?php

Schema::table('users', function (Blueprint $table) {
    // Remove the name field
    $table->dropColumn('name');
    // Delete multiple fields
    $table->dropColumn(['name', 'age']);
});

Available command aliases

Index

Create index

Unique index

Use the unique method to create a unique index:

<?php

// Create index at definition time
$table->string('name')->unique();
// Create indexes after fields are defined
$table->unique('name');

Compound index

<?php

// Create a compound index
$table->index(['account_id', 'created_at'], 'index_account_id_and_created_at');

Define index name

The migrator automatically generates a reasonable index name, and each index method accepts an optional second argument to specify the name of the index:

<?php

// Define a unique index name as unique_name
$table->unique('name', 'unique_name');
// Define a composite index named index_account_id_and_created_at
$table->index(['account_id', 'created_at'], '');

Available index types

Rename index

You can rename an index with the renameIndex method:

<?php

$table->renameIndex('from', 'to');

delete index

You can drop an index in the following way. By default, the migration program will automatically concatenate the database name, the field name of the index, and the index type as the name. Examples are as follows:

You can also pass an array of fields to the dropIndex method and the migrator will generate an index name based on the table name, field and key type:

<?php

Schema:table('users', function (Blueprint $table) {
    $table->dropIndex(['account_id', 'created_at']);
});

foreign key constraints

We can also create foreign key constraints at the database layer through the foreign, references, on methods. For example, let's let the posts table define a user_id field that references the id field of the users table:

Schema::table('posts', function (Blueprint $table) {
    $table->unsignedInteger('user_id');

    $table->foreign('user_id')->references('id')->on('users');
});

You can also specify the desired action for the on delete and on update properties:

$table->foreign('user_id')
      ->references('id')->on('users')
      ->onDelete('cascade');

You can drop foreign keys with the dropForeign method. Foreign key constraints are named in the same way as indexes, followed by a _foreign suffix:

$table->dropForeign('posts_user_id_foreign');

Or pass an array of fields and have the migrator generate the names according to the agreed-upon rules:

$table->dropForeign(['user_id'']);

You can turn foreign key constraints on or off using the following methods in the migration file:

// Enable foreign key constraints
Schema::enableForeignKeyConstraints();
// Disable foreign key constraints
Schema::disableForeignKeyConstraints();