How to Configure Database in Laravel and Create a Table using Migration?

            Laravel has made the lives of many developers easier making their work much more efficient and quicker with its easy to use PHP frameworks. With object-oriented libraries, MVC support, Database migration and so much more, it has truly made complexities elegantly simple.

So, if you haven’t got your Laravel already, get now!

Those of you, who are already using Laravel, let’s dive into database configuration and tabulation using migrations.

How do you configure your database?

The configuration for your database can be found in a file named ‘config/database.php’. In the given file, define all of your database connections, as well as the default connections.

What are migrations?

In simple terms, migrations are version control for your database that allows you to sync databases between development machines. It enables the team that is working on a certain project to modify and share the database schema, easily and efficiently. Typically, migrations are paired with Laravel’s schema builder. 

To create a migration

In order to generate migration, use the Artistan command make:migration.

$php artisan make:migration create_users_table

Your migrations will be placed in your directory database/ migrations. The order of the migrations is determined by the timestamps in the migration file.

To create a new table

To name the table and specify if the migration should create a new table or not, use –table and -create. It pre-fills the new migration stub file with the specified table.

$php artisan make:migration create_users_table –create=users

$php artisan make:migration add_votes_to_users_table –table=users

For custom output

To specify a custom output path for the migration, use –path while executing the make:migration command. However, make sure that the path you use is relative to your application’s base path.

To run your migration

To run your migrations, use the Artistan command: migrate. In case, you are using Homestead virtual machine, run the command from within your virtual machine.

$php artisan migrate

It is crucial to note that some migrations may cause loss of data. During such instances, permission to proceed appears on the screen so that you are well aware of the consequences. However, if you don’t want to give permission every time and want the migration executed without permission you can use, –force flag.

$php artisan migrate –force

To rollback migrations

To rollback, your latest migration, or a batch of migrations, use the rollback command.

$php artisan migrate:rollback

To roll back a specific number of migrations, use the step option to the above command.

$php artisan migrate:rollback –step=4

The given command rolls back the migration by four steps. You can change the number according to the number of migrations you want to roll back.

To roll back all your migrations, use command migrate: reset

$php artisan migrate:reset

To refresh migration

To re-create your entire database, use command migrate:refresh. This rolls back all your migrations and re-execute the migrate command.

$php artisan migrate:refresh

// Refresh the database and run all database seeds…

$php artisan migrate:refresh –seed

To re-create only a specific number of migrations, use the step option.

$php artisan migrate:refresh –step=5

This option refreshes 5 migrations. One can change the number according to the requirement.

To recreate the migrations by dropping at the tables, use command migrate:fresh

$php artisan migrate:fresh

$php artisan migrate:fresh –seed

To create tables

To create a new table, use the create method on the Schema facade. This method accepts two arguments:

  • First: the name of the table
  • Second: Closure which receives a Blueprint object could be used to define a new table.

Schema::create(‘users’, function (Blueprint $table) {

$table->id();

});

To check for the existence of column or table

To check for the existence of a table or column using the following methods.

if (Schema::hasTable(‘users’)) {

//

}

if (Schema::hasColumn(‘users’, ’email’)) {

//

}

Database connection

To perform schema operation on a database connection, which is not your default connection use the connection method.

Schema::connection(‘foo’)->create(‘users’, function (Blueprint $table) {

$table->id();

});

To define table options

To define table options, use the following commands on the schema builder.

Command Description
$table->engine = ‘InnoDB’; To specify the table storage engine (MySQL).
$table->charset = ‘utf8mb4’; To specify a default character set for the table (MySQL).
$table->collation = ‘utf8mb4_unicode_ci’; To specify a default collation for the table (MySQL).
$table->temporary(); To create a temporary table (except SQL Server).

To rename tables

To rename a database table, use the rename method

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

For tables with foreign key constraints, make sure to choose a unique name instead of letting Laravel assign a convention-based name. Else, the foreign key constraint name will refer to the old table name.

To drop table

To drop an existing table, use one of the following methods.

Schema::drop(‘users’);

Schema::dropIfExists(‘users’);

To create column

To update existing tables, use table method in the Schema facade. Quiet similar to create method, table method to accepts two arguments.

  • First: the name of the table
  • Second: a Closure that receives a Blueprint instance you may use to add columns to the table

Schema::table(‘users’, function (Blueprint $table) {

$table->string(’email’);

});

Click here for a list of various column types Schema builder contains you may specify while building your tables.

You can also, use several “modifiers” while adding columns to a database.

 

To make changes in the column

Before making any modifications or changes to your column, remember to add doctrine/dbal dependency to your compose.json file. It determines the current state of the column and creates required SQL queries.

composer require doctrine/dbal

  • To modify the types and attributes of the existing column, use the change method.

Schema::table(‘users’, function (Blueprint $table) {

$table->string(‘city’, 25)->change();

});

This increases the size of the city column to 25.

However, the change method is not applicable to all column types. Only integer, json, longText, mediumText, smallInteger, bigInteger, binary, boolean, date, dateTime, dateTimeTz, decimal, string, text, time, unsignedBigInteger, unsignedInteger, unsignedSmallInteger and uuid can be changed.

  • To rename a column, use renameColumn method on the schema builder.

Schema::table(‘users’, function (Blueprint $table) {

$table->renameColumn(‘from’, ‘to’);

});

Please note, column type enum cannot be renamed.

  • To drop a column, use dropColumn method on the schema builder.

Prerequisite: Besides, adding doctrine/dbal dependency to your compose.json file you also need to run the composer update command in your terminal to install the library.

Schema::table(‘users’, function (Blueprint $table) {

$table->dropColumn(‘votes’);

});

To drop multiple columns from a table, pass an array of column names to the dropColumn method.

Schema::table(‘users’, function (Blueprint $table) {

$table->dropColumn([‘votes’, ‘avatar’, ‘location’]);

});

You cannot drop multiple columns within single migration if you’re using SQLite database.

To create index

One of the best things about Laravel is that it supports various types of indices.

  • To create a new column and specify that its value should be unique. For instance, …

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

  • To create index after defining the column.

$table->unique(’email’);

  • One can also create a compound index by passing a number of columns to an index method.

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

  • To assign a unique index name yourself instead of using the one assigned by Laravel, pass a second argument. Laravel assigns the table name, the name of the indexed column, and the index type separated by an underscore (_) to assign index names.

$table->unique(’email’, ‘unique_email’);

To rename an index

To rename an index, use the renameIndex method. It accepts current index name as the first argument and the desired new name as the second argument.

$table->renameIndex(‘from’, ‘to’)

To drop an index

Various commands can be used to drop an index depending upon the type of index.

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

Foreign Key Constraints 

To force referential integrity at the database level, Laravel supports foreign key constraints.

  • To define a user_id column on the posts table that references the id column on a users table:

Schema::table(‘posts’, function (Blueprint $table) {

    $table->unsignedBigInteger(‘user_id’);

    $table->foreign(‘user_id’)->references(‘id’)->on(‘users’);

});

  • As the above syntax is complicated, it can also be simplified as follows.

Schema::table(‘posts’, function (Blueprint $table) {

$table->foreignId(‘user_id’)->constrained();

});

  • In case, your table name does not match the convention, pass it as an argument to the constrained method, and specify your desired name.

Schema::table(‘posts’, function (Blueprint $table) {

$table->foreignId(‘user_id’)->constrained(‘users_table’);

});

  • For “on delete” and “on update” properties of the constraint.

$table->foreignId(‘user_id’)->constrained() ->onDelete(‘cascade’);

  • To drop a foreign key, use the dropForeign method by passing it to be deleted as an argument.

$table->dropForeign(‘posts_user_id_foreign’);

  • Alternatively, pass an array containing the column name that holds the foreign key to the dropForeign method.

$table->dropForeign([‘user_id’]);

  • To enable or disable foreign constraints.

Schema::enableForeignKeyConstraints();

Schema::disableForeignKeyConstraints();

So, this is how you configure database in Laravel and create a table using migration.

We genuinely hope that this guide was useful to you. Feel free to leave your doubts in the comments section. We would be happy to help.