[Laravel 5.2 Document] database--migration

1. Introduction

Migrations, like versioning of databases, allow teams to easily edit and share the database table structure of the application, migrating common and laravel structure builder pairs to make it easy to build the database table structure of the application.

Laravel's schema façade provides support for creating and manipulating tables unrelated to the database system, providing consistent, elegant, and smooth APIs in all database systems supported by Laravel.

2. Generate Migration

Use the Artisan command make:migration to create a new migration:

PHP Artisan make:migration create_users_table

The new migration is in the Database/migrations directory, and each migrated file name contains a timestamp to allow Laravel to determine its order.

The--table and--create options can be used to specify the table name and whether the migration will create a new data table. These options need to be simply placed behind the migration command above and specify the table name:

PHP artisan make:migration add_votes_to_users_table--table=usersphp artisan make:migration create_users_table-- Create=users

If you want to specify a custom output path for the build migration, you can use the--path option when you execute the make:migration command, and the path provided should be relative to the application root.

3. Migration Structure

The migration class contains two methods: up and down. The up method is used to add tables, columns, or indexes to the database, and the down method is the inverse of the up method, as opposed to the up operation.

In both of these methods you will use the Laravel table Builder to create and modify tables, for example, let's take a look at a simple example of creating a flights table:

 
  Increments (' id ');            $table->string (' name ');            $table->string (' airline ');            $table->timestamps ();        });    }    /**     * Undo Migration     *     * @return void     *    /Public Function down ()    {        Schema::d rop (' flights ');}    }

4. Run the migration

To run all the non-performing migrations in your app, you can use the Migrate method of the Artisan command. If you are using a homestead virtual machine, you should run the following command in your virtual machine:

PHP Artisan Migrate

If you are running with a "class not found" error prompt, try running the composer Dump-autoload command and then rerun the migration command.

To force migration in a production environment

Some migration operations are devastating, which means they can cause data loss, and in order 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, you can use--force:

PHP Artisan Migrate--force

Roll back migration

To roll back the most recent migration operation, you can use the rollback command, note that this will roll back the last batch of running migrations and may contain multiple migration files:

PHP Artisan Migrate:rollback

The Migrate:reset command will roll back all the app migrations:

PHP Artisan Migrate:reset

Roll back/migrate in a single command

The Migrate:refresh command will roll back all the database migrations before running the migrate command. This command can effectively reconstruct the entire database:

PHP artisan migrate:refreshphp Artisan Migrate:refresh--seed

5. Write the migration

Create a table

Use the Create method on the schema façade to create a new data table. The Create method receives two parameters, the first one is the table name, and the second is the closure of the Blueprint object that defines the new table:

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

Of course, when creating a new table, you can use any of the column methods in the table structure Builder to define the columns of the data table.

Check if a table/column exists

You can easily use the hastable and Hascolumn methods to check whether a table or column exists:

if (schema::hastable (' users ')) {    //}if (' users ', ' email ') {    //}}

Connectivity & Storage Engine

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

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

To set the storage engine for the table, set the engine properties on the table builder:

Schema::create (' Users ', function ($table) {    $table->engine = ' InnoDB ';    $table->increments (' id ');});

Rename/delete table

To rename an existing data table, use the Rename method:

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

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

Schema::d rop (' users '); Schema::d ropifexists (' users ');

Create columns

To update an existing table, using the table method on the schema façade, like the Create method, the table method receives two parameters: a table name and a closure that gets the blueprint instance that is used to add columns to the table:

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

Available column types

Of course, the table structure Builder contains a series of column types that you can use to build tables:

Command	Description
$table->bigincrements (' id ');	Self-increment ID, type bigint
$table->biginteger (' votes ');	Equivalent to the bigint type in the database
$table->binary (' data ');	Equivalent to the BLOB type in the database
$table->boolean (' confirmed ');	is equivalent to a Boolean type in the database
$table->char (' name ', 4);	Equivalent to the char type in the database
$table->date (' created_at ');	is equivalent to the date type in the database
$table->datetime (' created_at ');	Equivalent to a datetime type in a database
$table->decimal (' Amount ', 5, 2);	equals the decimal type in the database, with a precision and range
$table->double (' column ', 15, 8);	equals the double type in the database, with precision, a total of 15 digits, and 8 digits after the decimal point.
$table->enum (' Choices ', [' foo ', ' Bar ']);	Equivalent to the enum type in the database
$table->float (' Amount ');	is equivalent to the FLOAT type in the database
$table->increments (' id ');	Database primary key auto-increment ID
$table->integer (' votes ');	Equivalent to the INTEGER type in the database
$table->json (' options ');	Equivalent to the JSON type in the database
$table->jsonb (' options ');	Equivalent to the JSONB type in the database
$table->longtext (' description ');	Equivalent to the Longtext type in the database
$table->mediuminteger (' numbers ');	Equivalent to the Mediumint type in the database
$table->mediumtext (' description ');	Equivalent to the Mediumtext type in the database
$table->morphs (' taggable ');	Add an integer type of taggable_id column and a string type of taggable_type column
$table->nullabletimestamps ();	Same as timestamps () but does not allow null values.
$table->remembertoken ();	Add a remember_token column: VARCHAR (+) NULL.
$table->smallinteger (' votes ');	Equivalent to the SMALLINT type in the database
$table->softdeletes ();	A new Deleted_at column is added for soft deletion.
$table->string (' email ');	is equivalent to a VARCHAR column in the database.
$table->string (' name ', 100);	Equivalent to the VARCHAR in the database, with a length
$table->text (' description ');	Equivalent to the TEXT type in the database
$table->time (' Sunrise ');	Equivalent to the time type in the database
$table->tinyinteger (' numbers ');	Equivalent to the TINYINT type in the database
$table->timestamp (' added_on ');	Equivalent to the TIMESTAMP type in the database
$table->timestamps ();	Add Created_at and Updated_at columns.
$table->uuid (' id ');	UUID equivalent to the database

Column modifiers

In addition to the column types listed above, you can use some other column modifiers when adding columns, for example, to make Lieme think null, you can use the Nullable method:

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

The following is a list of all available column modifiers that do not contain an index modifier:

modifier	Description
->first ()	Place the column as the first column in the table (MySQL only)
->after (' column ')	After the column is placed in another column (MySQL only)
->nullable ()	Allows the value of this column to be null
->default ($value)	Specify default values for columns
->unsigned ()	Set the integer column UNSIGNED

modifying columns

Prerequisite

Before you modify a column, make sure that you have added Doctrine/dbal dependencies to the Composer.json file, doctrine the Dbal library is used to determine the current state of the column and create an SQL query when needed to make the specified adjustments to the column.

Update Column Properties

The change method allows you to modify the existing column as a new type, or to modify the properties of the columns. For example, you might want to increase the size of a column of type string, let's increase the size of the name column from 25 to 50:

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

We can also modify the column to allow NULL values:

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

renaming columns

To rename a column, you can use the Renamecolumn method on the table structure builder to ensure that the doctrine/dbal dependency has been added to the Composer.json file before renaming a column:

Schema::table (' Users ', function ($table) {    $table->renamecolumn (' from ', ' to ');});

Note: Renaming columns of enum type is not supported at this stage.

Delete Column

To delete a column, use the Dropcolumn method on the table structure Builder:

Schema::table (' Users ', function ($table) {    $table->dropcolumn (' votes ');});

You can pass the column an array group to the Dropcolumn method to remove multiple columns from the table:

Schema::table (' Users ', function ($table) {    $table->dropcolumn ([' votes ', ' Avatar ', ' location ');});

Note: Before deleting a column from the SQLite database, you need to add a doctrine/dbal dependency to the Composer.json file and run the composer Update command in the terminal to install the library. In addition, the SQLite database temporarily does not support the deletion or modification of multiple columns in a single migration.

Create an index

The table structure Builder supports multiple types of indexes, first, let's look at an example that specifies the column value as a unique index. To create an index, you can use the unique method:

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

In addition, you can create an index after the column is defined, for example:

$table->unique (' email ');

You can even pass column an array groups to the index method to create a composite index:

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

Laravel will automatically generate a reasonable index name, but you can pass the second parameter to the method used to specify the index name:

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

Available index types

Command	Description
$table->primary (' id ');	Add primary key Index
$table->primary ([' First ', ' last ']);	Add a hybrid index
$table->unique (' email ');	Add a unique index
$table->unique (' state ', ' my_index_name ');	Specify a custom index name
$table->index (' state ');	Add a normal index

Delete Index

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

Command	Description
$table->dropprimary (' users_id_primary ');	To remove the primary key index from the users table
$table->dropunique (' Users_email_unique ');	To remove a unique index from the users table
$table->dropindex (' Geo_state_index ');	Remove a normal index from the "GEO" table

FOREIGN KEY constraints

Laravel also provides support for creating foreign key constraints to enforce referential integrity at the database level. For example, we define a user_id column in the posts table that references the ID column of the Users table:

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

You can also specify the desired action for the "on delete" and "On Update" Properties of the constraint:

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

To delete a foreign key, you can use the Dropforeign method. FOREIGN KEY constraints and indexes use the same naming conventions--Join table names, foreign key names, and then add the "_foreign" suffix:

$table->dropforeign (' posts_user_id_foreign ');

