Database migration: maintain data tables by version control

---

brief introduction

Migration is like database version control. This mechanism allows teams to easily edit and share the database table structure of applications. Migration is usually paired with Laravel's schema builder to easily build the database table structure of the application. If you have frequently told team members that you need to manually add columns to the local database table structure to maintain the local development environment, this is the problem that database migration is committed to solving.

Larvel's Schema facade provides database system independent support for creating and manipulating tables, and provides consistent, elegant, and streaming APIs in all database systems supported by Larvel.

Generate Migration

Using Artisan Commands make:migration You can create a new migration:

 php artisan make:migration create_users_table

The new migration is located in database/migrations Directory, each migration file name contains a timestamp to allow Larvel to determine its order.

--table and --create Option can be used to specify the table name and whether the migration needs to create a new data table. These options simply need to be placed after the above migration command and the table name needs to be specified:

 php artisan make:migration create_users_table --create=users php artisan make:migration add_votes_to_users_table --table=users

If you want to specify a custom output path for generating migration, run the make:migration You can use the --path Option. The provided path should be relative to the application root directory.

Migration Structure

The migration class contains two methods: up and down 。 up Method is used to add tables, columns, or indexes to the database down The way is up The inverse operation of the method, and up The operation in is opposite.

In these two methods, you need to use Laravel's Schema builder to create and modify tables. To learn more about the methods provided by the Schema builder, refer to its file 。 Let's first look at creating flights A simple example of a table:

 <? php use Illuminate\Support\Facades\Schema; use Illuminate\Database\Schema\Blueprint; use Illuminate\Database\Migrations\Migration; class CreateFlightsTable extends Migration { /** * Run the migrations. * * @return void */ public function up() { Schema::create('flights', function (Blueprint $table) { $table->increments('id'); $table->string('name'); $table->string('airline'); $table->timestamps(); }); } /** * Reverse the migrations. * * @return void */ public function down() { Schema::drop('flights'); } }

Run Migration

To run all unexecuted migrations in the application, you can use the migrate method:

 php artisan migrate

Note: If you are using Homestead virtual machine , you need to run the above command in the virtual machine.

Force migration in production environment

Some migration operations are destructive, which means they may cause data loss. To avoid running these commands in the production environment database, you will be prompted and confirmed before running these commands. To force these commands to run without being prompted, use --force Tag:

 php artisan migrate --force

Rollback migration

To rollback the latest migration operation, you can use the rollback Command. Note that this will roll back the last run migration, which may contain multiple migration files:

 php artisan migrate:rollback

You can also pass rollback Provided on the command step Option to rollback the specified number of migrations. For example, the following command will rollback the last five migrations:

 php artisan migrate:rollback --step=5

migrate:reset The command will rollback all application migrations:

 php artisan migrate:reset

Rollback&migration in a single command

migrate:refresh The command will rollback all database migrations first, and then run migrate Command. This command can effectively rebuild the entire database:

 php artisan migrate:refresh //Rebuild Database and Populate Data php artisan migrate:refresh --seed

Of course, you can also roll back or rebuild a specified number of migrations through refresh Command provided step Options, for example, the following command will roll back or rebuild the last five migrations:

 php artisan migrate:refresh --step=5

Delete All Tables&Migration

migrate:fresh The command will first delete all tables from the database and then execute migrate Command:

 php artisan migrate:fresh php artisan migrate:fresh --seed

data sheet

Create Table

Use the create Method to create a new data table. create Method receives two parameters, the first is the table name, and the second is to obtain the Blueprint Closure of object:

 Schema::create('users', function ($table) { $table->increments('id'); });

Of course, when creating a new table, you can use any of the Column method To define the columns of the data table.

Check whether the table/column exists

You can easily use hasTable and hasColumn Method Check whether the table or column exists:

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

Database Connection&Table Options

If you want to perform table structure operations on a database connection that is not the default database connection, you can use connection method:

 Schema::connection('foo')->create('users', function (Blueprint $table) { $table->increments('id'); });

To set the storage engine, character encoding and other options of the table, you can use the following commands on the Schema builder:

command	describe
$table->engine = 'InnoDB';	The storage engine (MySQL) of the specified table
$table->charset = 'utf8';	Specify the default character set of the data table (MySQL)
$table->collation = 'utf8_unicode_ci';	Specify the character order of the data table (MySQL)
$table->temporary();	Create temporary tables (except SQL Server)

Rename/Delete Table

To rename an existing data table, use rename method:

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

To delete an existing data table, you can use the drop or dropIfExists method:

 Schema::drop('users'); Schema::dropIfExists('users');

Renaming a table through a foreign key

Before renaming the table, you need to verify that the foreign key contained in the table has a clear name in the migration file, rather than the name assigned by Larvel based on conventions. Otherwise, the foreign key constraint name will point to the old data table.

Data Column

Create Data Columns

To update an existing table, use the table Methods, and create In the same way, table The method receives two parameters: the table name and the Blueprint Closure of instance:

 Schema::table('users', function (Blueprint $table) { $table->string('email'); });

Available data column types

Of course, the Schema builder contains a series of column types that you can use to build tables:

command	describe
$table->bigIncrements('id');	Same as the self increasing UNSIGNED BIGINT (primary key) column
$table->bigInteger('votes');	Equivalent to BIGINT type column
$table->binary('data');	Equivalent to BLOB type columns
$table->boolean('confirmed');	Equivalent to BOOLEAN type column
$table->char('name', 4);	Same as CHAR type column
$table->date('created_at');	Equivalent to DATE type column
$table->dateTime('created_at');	Equivalent to DATETIME type column
$table->dateTimeTz('created_at');	Equivalent to DATETIME type (with time zone) column
$table->decimal('amount', 5, 2);	Equivalent to DECIMAL type column with precision and range
$table->double('column', 15, 8);	Equal to DOUBLE type column, with precision, 15 digits in total, 8 digits after the decimal point
$table->enum('level', ['easy', 'hard']);	Equivalent to ENUM type column
$table->float('amount', 8, 2);	Equivalent to FLOAT type column, with precision and total digits
$table->geometry('positions');	Equivalent to GEOMETRY type column
$table->geometryCollection('positions');	Equivalent to GEOMETRYCOLLECTION type column
$table->increments('id');	Equivalent to auto incrementing UNSIGNED INTEGER (primary key) type column
$table->integer('votes');	Equivalent to INTEGER type column
$table->ipAddress('visitor');	Equivalent to IP address type column
$table->json('options');	Equivalent to JSON type column
$table->jsonb('options');	Equivalent to JSONB type column
$table->lineString('positions');	Equivalent to LINESTRING type column
$table->longText('description');	Equivalent to LONGTEXT type column
$table->macAddress('device');	Same as MAC address type column
$table->mediumIncrements('id');	Same as auto increment UNSIGNED MEDIUMINT type column (primary key)
$table->mediumInteger('numbers');	Equivalent to MEDIUMINT type column
$table->mediumText('description');	Equivalent to MEDIUMTEXT type column
$table->morphs('taggable');	Add an UNSIGNED INTEGER taggable_id Column and a VARCHAR type taggable_type column
$table->multiLineString('positions');	Equivalent to MULTILINESTRING type column
$table->multiPoint('positions');	Equivalent to MULTIPOINT type column
$table->multiPolygon('positions');	Equivalent to MULTIPOLYGON type column
$table->nullableMorphs('taggable');	morphs() Nullable version of the column
$table->nullableTimestamps();	timestamps() Alias for
$table->point('position');	Equivalent to POINT type column
$table->polygon('positions');	Equivalent to POLYGON type column
$table->rememberToken();	It is equivalent to adding an empty remember_token VARCHAR (100) column
$table->smallIncrements('id');	Equivalent to auto incrementing UNSIGNED SMALLINT (primary key) type column
$table->smallInteger('votes');	Equivalent to SMALLINT type column
$table->softDeletes();	Add a new one that can be empty deleted_at TIMESTAMP column for soft deletion
$table->softDeletesTz();	Add a new one that can be empty deleted_at TIMESTAMP (with time zone) column is used for soft deletion
$table->string('name', 100);	Equivalent to VARCHAR type column, with an optional length parameter
$table->text('description');	Equivalent to TEXT type column
$table->time('sunrise');	Same as TIME type column
$table->timeTz('sunrise');	Equivalent to TIME type (with time zone)
$table->timestamp('added_on');	Equivalent to TIMESTAMP type column
$table->timestampTz('added_on');	Equivalent to TIMESTAMP type (with time zone) column
$table->timestamps();	Add empty allowed created_at and updated_at TIMESTAMP type column
$table->timestampsTz();	Add empty allowed created_at and updated_at TIMESTAMP type column (with time zone)
$table->tinyIncrements('numbers');	Equal to the autosigning UNSIGNED TINYINT type column (primary key)
$table->tinyInteger('numbers');	Equivalent to TINYINT type column
$table->unsignedBigInteger('votes');	Equivalent to unsigned BIGINT type columns
$table->unsignedDecimal('amount', 8, 2);	Equivalent to UNSIGNED DECIMAL type column, with total digits and precision
$table->unsignedInteger('votes');	Equivalent to unsigned INTEGER type column
$table->unsignedMediumInteger('votes');	Equivalent to unsigned MEDIUMINT type columns
$table->unsignedSmallInteger('votes');	Equivalent to unsigned SMALLINT type columns
$table->unsignedTinyInteger('votes');	Equivalent to unsigned TINYINT type column
$table->uuid('id');	Equivalent to UUID type column
$table->year('birth_year');	Equivalent to YEAR type column

Column Modifier

In addition to the data column types listed above, you can also use some other column "modifiers" when adding columns. For example, to allow columns to be NULL, you can use nullable method:

 Schema::table('users', function (Blueprint $table) { $table->string('email')->nullable(); });

The following is a list of all available column modifiers, which does not contain Index Modifier ：

Modifier	describe
->after('column')	Place this column after another column (MySQL)
->autoIncrement()	Set INTEGER column as auto increment primary key
->charset('utf8')	Specify data column character set (MySQL)
->collation('utf8_unicode_ci')	Specify the character order of the data column (MySQL/SQL Server)
->comment('my comment')	Add note information
->default($value)	Specify default values for columns
->first()	Set this column as the first column in the table (MySQL)
->nullable($value = true)	Allow the value of this column to be NULL
->storedAs($expression)	Create a storage generated column (MySQL)
->unsigned()	Set INTEGER column to UNSIGNED (MySQL)
->useCurrent()	Set TIMESTAMP column to use CURRENT_TIMESTAMP as default
->virtualAs($expression)	Create a virtual generated column (MySQL)

Modify Data Column

precondition

Before modifying a column, make sure that the doctrine/dbal Dependencies added to composer.json File. The Doctrine DBAL library is used to determine the current state of the column and create the SQL statement required for the specified adjustment of the column:

 composer require doctrine/dbal

Update Column Properties

change Method allows you to modify an existing column to a new type, or modify the properties of the column. For example, you may want to increase the size of the string type column, let's name Column size increased from 25 to 50:

 Schema::table('users', function (Blueprint $table) { $table->string('name', 50)->change(); });

We can also modify the column to allow NULL values:

 Schema::table('users', function (Blueprint $table) { $table->string('name', 50)->nullable()->change(); });

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

Rename Column

To rename a column, use the renameColumn Method, before renaming a column, ensure that doctrine/dbal Dependencies have been added to composer.json File and has been run composer update Command:

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

Note: temporarily not supported enum Modify and rename columns of type.

Delete Data Column

To delete a column, use the dropColumn Method, again, before doing so, make sure that the doctrine/dbal Dependencies:

 Schema::table('users', function (Blueprint $table) { $table->dropColumn('votes'); });

You can pass the column name array to dropColumn Method so that you can delete multiple columns from the data table at once:

 Schema::table('users', function (Blueprint $table) { $table->dropColumn(['votes', 'avatar', 'location']); });

Note: SQLite database does not support deleting or modifying multiple columns in a single migration.

Valid command alias

command	describe
$table->dropRememberToken();	delete remember_token column
$table->dropSoftDeletes();	delete deleted_at column
$table->dropSoftDeletesTz();	dropSoftDeletes() Method Alias
$table->dropTimestamps();	delete created_at and updated_at column
$table->dropTimestampsTz();	dropTimestamps() Method Alias

Indexes

Create Index

The Schema builder supports multiple types of indexes. First, let's look at an example of specifying column values as unique indexes. To create this index, you can use the unique method:

 $table->string('email')->unique();

In addition, you can create indexes after defining columns, such as:

 $table->unique('email');

You can even create a composite index by passing the column name array to the index method:

 $table->index(['account_id', 'created_at']);

Larvel will automatically generate a reasonable index name, but you can also pass the second parameter to this method to specify the index name:

 $table->index('email', 'unique_email');

Available Index Types

command	describe
$table->primary('id');	Add Primary Key Index
$table->primary(['first', 'last']);	Add Composite Index
$table->unique('email');	Add Unique Index
$table->index('state');	Add Normal Index
$table->spatialIndex('location');	Add spatial index (SQLite is not supported)

Index length: MySQL/MariaDB

Larravel uses by default utf8mb4 Character set, which supports the storage of emoji emoticons in the database. If you are running MySQL earlier than 5.7.7 (or MariaDB earlier than 10.2.2), you need to manually configure the default string length generated by the migration command so that MySQL can create indexes for them. You can use the AppServiceProvider Called in Schema::defaultStringLength Method to complete the configuration:

 use Illuminate\Support\Facades\Schema; /** * Bootstrap any application services. * * @return void * @translator laravelacademy.org */ public function boot() { Schema::defaultStringLength(191); }

As an alternative, you can enable innodb_large_prefix For how to properly enable this option, refer to the database documentation.

Delete Index

To delete an index, you must specify an index name. By default, Larvel automatically assigns the appropriate name to the index -- join table name, column name, and index type. Here are some examples:

command	describe
$table->dropPrimary('users_id_primary');	Delete the primary key index from the "users" table
$table->dropUnique('users_email_unique');	Remove the unique index from the "users" table
$table->dropIndex('geo_state_index');	Remove the normal index from the "geo" table
$table->dropSpatialIndex('geo_location_spatialindex');	Delete spatial index from "geo" table (SQLite is not supported)

If you want to pass the data column array to the delete index method, the corresponding index name will be automatically generated by the data table name, column and key type:

 Schema::table('geo', function (Blueprint $table) { $table->dropIndex(['state']); //  Drops index 'geo_state_index' });

Foreign key constraints

Larave also provides support for creating foreign key constraints to enforce referential integrity at the database level. For example, we posts A reference is defined in the table users surface id Column user_id Column:

 Schema::table('posts', function (Blueprint $table) { $table->integer('user_id')->unsigned(); $table->foreign('user_id')->references('id')->on('users'); });

You can also specify the desired action for the constraint's "on delete" and "on update" attributes:

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

To delete a foreign key, use the dropForeign method. Foreign key constraints and indexes use the same naming rule - join table names, foreign key names, and then add the suffix "_foreign":

 $table->dropForeign('posts_user_id_foreign');

Alternatively, you can also pass that the convention based constraint name value array will be automatically used when deleting:

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

You can enable or disable foreign key constraints during migration by the following methods:

 Schema::enableForeignKeyConstraints(); Schema::disableForeignKeyConstraints();

Note: Due to the high risk of cascading deletion of foreign keys, we seldom use foreign keys, but implement cascading operations through code logic.