Database Migrations

Database Migrations in Laravel

Introduction

Migrations are version control for our database. It allows our team to modify and share the applications database.

Migrations are paired with schema builder to build an applications database schema quickly.

The Laravel Schema façade provides database agnostic support for creating and manipulating table for all of Laravels supported database systems.

Database Migrations

Generating Migrations

We use the make:migration Artisan command for creating a migration.

Generating Migrations

New migration will be placed in our database/migrations directory.

Each of the migration file names contains a timestamp that allows determining the order of the migrations.

The - -table and - -create options can also be used to show the name of the table, and the migration will be creating a new table.

The generated migration pre-filled these options for stub file with the specified table.

Syntax:

To define the custom output path for the generated migration, we use the –path option while executing the make:migration command.

The make:migration path should be relative to our applications base path.

Migration Structure

The migration class contains two different types of methods:  first one is up method, and the second one is down method.

The up method adds new columns, new tables, or index into our database.

The down method should reverse the operations that are performed by the up method.

Within both of these methods, we use the Laravel schema builder to create and modify tables.

For example:-

Laravel database migration

Running Migrations

The migrate Artisan command is used to run all of the outstanding migrations.

php artisan migrate

Migrations to Run in Production

Migration operations are destructive, that means it may cause us to lose our data.

We use the –flag force to force those commands that run without prompt.

Rolling Back Migrations

We use the rollback command to rollback the latest migration operation.

The command rolls back the last “batch” of migrations that include multiple migration files:

A limited number of migrations rollback by providing the step option to the rollback command.

For example:

Rolling Back Migrations

The above command will rollback the last 10 migrations.

The migrate:reset command will back all of our applications migrations.

command will rollback

php artisan migrate:reset

Rollback and Migrate in Single Command

The migrate:fresh command is used to roll back all of our migrations, and it is executed by the migrate command.

Rollback and Migrate in Single Command

The above code re-creates our entire database.

We rollback $ re-migrate, a limited number of migrations for providing the step option to the refresh command.

The following command will use to rollback & re-migrate the last 15 migrations:

Drop All Tables & Migrate

The migrate:fresh command will drop all the tables from the database, and then migrate command will execute it:

Tables

Creating Tables

The create method is used to create a new database table on the Schema façade.

The method accepts two types of arguments.

The name of the table is the first argument. The second is the Closure that receives a Blueprint object which is used to define the new table.

Creating Tables

We use any of the schema builders column methods that define the tables column for creating the table.

Column Existence

To check the existence of a column or a table, we can use the hasTable and hasColumn methods

Syntax:

Column Existence

Database Connection

The connection method is used to perform a schema operation on a database connection.

Syntax:

Database Connection
Command Description
$table->engine = 'InnoDB'; It specify the table storage engine (MySQL).
$table->charset = 'utf8'; It specify a default character set for the table (MySQL).
$table->collation = 'utf8_unicode_ci'; It specify a default collation for the table (MySQL).
$table->temporary(); It create a temporary table (except SQL Server).

The above commands are used on the schema builder to specify the tables options.

Renaming Tables

The rename method renames the existing database table.

Syntax:

Example:

Dropping Tables

The drop or dropIfExists methods are used for dropping an existing database table.

Syntax:

Renaming Tables with Foreign Keys

Before renaming a table, we should verify that foreign key constraints on the table have an explicit name in our migration files.

Otherwise, the foreign key constraint name refers to the old table name.

Columns

Creating Columns

The table method is used to update existing tables.

This method has similar arguments like the create method.

The two arguments that are used for creating columns are the name of the table and closure, which receives a Blueprint instance that we use for adding columns to the table.

Syntax:

Column Types:

The schema builder contains various column types, which defines a building of our tables:

Command Description
$table->bigIncrements(‘id’); The Auto-incrementing UNSIGNED BIGINT (primary key) equivalent column.
$table->bigInteger(‘votes’); It BIGINT equivalent column.
$table->binary(‘data’); It BLOB equivalent column.
$table->boolean(‘confirmed’); It BOOLEAN equivalent column.
$table->char(‘name’, 50); The CHAR command equivalent column with an optional length.
$table->date(‘created_at’); DATE equivalent column.
$table->dateTime(‘created_at’); DATETIME equivalent column.
$table->dateTimeTz(‘created_at’); DATETIME (with timezone) equivalent column.
$table->decimal(‘amount’, 15, 12); DECIMAL equivalent column with a combination of (total digits) and scale (decimal digits).
$table->double(‘amount’, 15, 12); DOUBLE equivalent column with a combination of (total digits) and scale (decimal digits).
$table->enum(‘level’, [‘easy’, ‘hard’]); ENUM equivalent column.
$table->float(‘amount’, 18, 12); FLOAT equivalent column with a combination of (total digits) and scale (decimal digits).
$table->geometry(‘positions’); GEOMETRY equivalent column.
$table->geometryCollection(‘positions’); GEOMETRYCOLLECTION equivalent column.
$table->increments(‘id’); Auto-incrementing UNSIGNED INTEGER (primary key) equivalent column.
$table->integer(‘votes’); INTEGER equivalent column.
$table->ipAddress(‘visitor’); IP address equivalent column.
$table->json(‘options’); JSON equivalent column.
$table->jsonb(‘options’); JSONB equivalent column.
$table->lineString(‘positions’); LINESTRING equivalent column.
$table->longText(‘description’); LONGTEXT equivalent column.
$table->macAddress(‘device’); MAC address equivalent column.
$table->mediumIncrements(‘id’); Auto-incrementing UNSIGNED MEDIUMINT (primary key) equivalent column.
$table->mediumInteger(‘votes’); MEDIUMINT equivalent column.
$table->mediumText(‘description’); MEDIUMTEXT equivalent column.
$table->morphs(‘taggable’); Adds taggable_id UNSIGNED BIGINT and taggable_typeVARCHAR equivalent columns.
$table->uuidMorphs(‘taggable’); Adds taggable_id CHAR(36) and taggable_type VARCHAR(255) UUID equivalent columns.
$table->multiLineString(‘positions’); MULTILINESTRING equivalent column.
$table->multiPoint(‘positions’); MULTIPOINT equivalent column.
$table->multiPolygon(‘positions’); MULTIPOLYGON equivalent column.
$table->nullableMorphs(‘taggable’); Adds nullable versions of morphs() columns.
$table->nullableUuidMorphs(‘taggable’); Adds nullable versions of uuidMorphs() columns.
$table->nullableTimestamps(); Alias of timestamps() method.
$table->point(‘position’); POINT equivalent column.
$table->polygon(‘positions’); POLYGON equivalent column.
$table->rememberToken(); Adds a nullable remember_tokenVARCHAR(100) equivalent column.
$table->set(‘flavors’, [‘strawberry’, ‘vanilla’]); SET equivalent column.
$table->smallIncrements(‘id’); Auto-incrementing UNSIGNED SMALLINT (primary key) equivalent column.
$table->smallInteger(‘votes’); SMALLINT equivalent column.
$table->softDeletes(); Adds a nullable deleted_atTIMESTAMP equivalent column for soft deletes.
$table->softDeletesTz(); Adds a nullable deleted_atTIMESTAMP (with timezone) equivalent column for soft deletes.
$table->string(‘name’, 100); VARCHAR equivalent column with a optional length.
$table->text(‘description’); TEXT equivalent column.
$table->time(‘sunrise’); TIME equivalent column.
$table->timeTz(‘sunrise’); TIME (with timezone) equivalent column.
$table->timestamp(‘added_on’); TIMESTAMP equivalent column.
$table->timestampTz(‘added_on’); TIMESTAMP (with timezone) equivalent column.
$table->timestamps(); Adds nullable created_at and updated_at TIMESTAMP equivalent columns.
$table->timestampsTz(); Adds nullable created_at and updated_at TIMESTAMP (with timezone) equivalent columns.
$table->tinyIncrements(‘id’); Auto-incrementing UNSIGNED TINYINT (primary key) equivalent column.
$table->tinyInteger(‘votes’); TINYINT equivalent column.
$table->unsignedBigInteger(‘votes’); UNSIGNED BIGINT equivalent column.
$table->unsignedDecimal(‘amount’, 16, 12); UNSIGNED DECIMAL equivalent column with a combination of (total digits) and scale (decimal digits).
$table->unsignedInteger(‘votes’); UNSIGNED INTEGER equivalent column.
$table->unsignedMediumInteger(‘votes’); UNSIGNED MEDIUMINT equivalent column.
$table->unsignedSmallInteger(‘votes’); UNSIGNED SMALLINT equivalent column.
$table->unsignedTinyInteger(‘votes’); UNSIGNED TINYINT equivalent column.
$table->uuid(‘id’); UUID equivalent column.
$table->year(‘birth_year’); YEAR equivalent column.

Modifying Columns

We have to add the doctrine/dbal dependency to our composer.json before modifying a column.

The DBAL library is used to determine the current state of the column, and it will create the SQL queries that are needed to make the specified adjustments to the column.

Updating Column Attributes

The change method allows us to modify the existing column types to a new type or modify the columns attributes.

The change method in action increases the size of the name column from 25 to 50.

Syntax:

Example:

We also modify a column to be nullable:

Syntax:

Example:

Renaming Columns

The renameColumn method is used to rename a column on the Schema builder.

Add the doctrine/dbal dependency before renaming a column to composer.json file.

Syntax:

Example:

  • Renaming a column, which also has a column type enum is not currently supported.

Dropping Columns

The dropColumn method is used to drop a column.

We need to add the doctrine/dbal dependency into our composer.json file, and it will run the composer update command in our terminal to install the library.

To drop multiple columns from a table, we use the dropColumn method by passing an array of column names.

Available Command

Command Description
$table->dropMorphs('morphable'); It drop the morphable_id and morphable_type columns.
$table->dropRememberToken(); It drop the remember_token column.
$table->dropSoftDeletes(); It drop the deleted_at column.
$table->dropSoftDeletesTz(); Alias of dropSoftDeletes() method.
$table->dropTimestamps(); It drop the created_at and updated_at columns.
$table->dropTimestampsTz(); Alias of dropTimestamps() method.

INDEX

Creating Index

For creating the index, we use the unique method onto the column definition

Syntax:

Otherwise, we create the index after defining the column

Example:-

$table->unique('name');

We can create an array of columns for an index method to create a compound index:

Laravel will automatically generate the index name according to their name, but we can pass a second argument  by specifying the name

Available Index Types

Each of the index methods accepts a second argument to define the name of the index.

If it omits, the name will be taken from the names of the table and columns(s).

Command Description
$table->primary('id'); Adds a primary key.
$table->primary(['id', 'parent_id']); Adds composite keys.
$table->unique('email'); Adds a unique index.
$table->index('state'); Adds a plain index.
$table->spatialIndex('location'); Adds a spatial index. (except SQLite)

Index Lengths & MySQL/Maria DB

By default, the Laravel uses the utf8mb4 character set that includes the support for storing the “emojis” in the database.

If we want to run the older version of the MySQL or Maria DB, we need to manually configure the default string length that is generated by migrations. It is for MySQL in order to create an index for them.

We configure this by calling the Schema::defaultStringLength method into our AppServiceProvider:

We enable the innodb_large_prefix option for our database.

Renaming & Dropping Index

The renameIndex method is used to rename an index. The renameIndex method accepts the current index name as its first argument and the desired name as its second argument:

For dropping an index, we must specify the indexs name.

By default, the Laravel will automatically assign a reasonable name to the index.

Command Description
$table->dropPrimary(‘users_id_primary’); It drop a primary key from the “users” table.
$table->dropUnique(‘users_email_unique’); It drop a unique index from the “users” table.
$table->dropIndex(‘geo_state_index’); It drop a basic index from the “geo” table.
$table->dropSpatialIndex(‘geo_location_spatialindex’); It drop a spatial index from the “geo” table (except SQLite).

 We pass an array of columns into the method, which drop the index. The similar index name will be generated according of the table name, columns and key type:

We also specify the desired action for “on delete” and “on update” properties of the constraint:

Foreign Key Constraints

Laravel provides support for creating foreign key constraints that are used to force referential integrity at the database level.

For example:

A user_id column of the posts table, refers to the id column on a user’s table:

We also define the required action for the “on delete” and “on update” properties of the constraint:

The dropForeign method is used to drop a foreign key.

Foreign key constraints use the same naming as its index.

The table name and the columns will concatenate in the constraint name with the help of “—foreign”:

Or, we pass an array value that will automatically use the conventional constraint name while dropping:

We enable or disable foreign key constraints within our migrations by using the following methods:

  • By default, the SQLite disables foreign key constraints. While using SQLite, make sure to enable foreign key support in our database configuration before attempting to create them in our migrations.