Laravel provides Migration, and Schema classes for manipulating database tables. It is independent of database type. Migration is useful in version control. For creating a migration, first give command:
- php artisan make:migration table_name
It will create a new file in folder database >> migrations with name year_month_day_seconds_table_name.php. For example, above command will create file with name something like 2014_10_12_000000_table_name.php. The default code of this file is:
- <?php
-
- use Illuminate\Database\Migrations\Migration;
- use Illuminate\Database\Schema\Blueprint;
- use Illuminate\Support\Facades\Schema;
-
- class TableName extends Migration {
- /**
- * Run the migrations.
- *
- * @return void
- */
- public function up()
- {
- //
- }
- /**
- * Reverse the migrations.
- *
- * @return void
- */
- public function down()
- {
- //
- }
- }
The Blueprint class provides SQL for creating columns in tables. TableName class has two functions, up() and down(). The code for manipulating the table is written in the up() function. The down() function is used to undo the work of up() function. For example, if up() function contains the code for creating a table then down() function will take the code for deleting that table.
Creating and deleting table:
Schema::create() and Schema::drop() are used for creating and deleting table, respectively. In database >> migrations folder, there is a default file named 2014_10_12_000000_create_users_table.php. The code inside this file is:
- <?php
-
- use Illuminate\Database\Migrations\Migration;
- use Illuminate\Database\Schema\Blueprint;
- use Illuminate\Support\Facades\Schema;
-
- class CreateUsersTable extends Migration {
- /**
- * Run the migrations.
- *
- * @return void
- */
- public function up()
- {
- Schema::create('users', function(Blueprint $table)
- {
- $table->id();
- $table->string('name');
- $table->string('email')->unique();
- $table->string('password', 60);
- $table->rememberToken();
- $table->timestamps();
- });
- }
- /**
- * Reverse the migrations.
- *
- * @return void
- */
- public function down()
- {
- Schema::drop('users');
- }
- }
The create() function takes two arguments: table name and operations on the columns of that table (it is a closure). The drop() function takes only table name as argument. In above code, we are creating a table users in up function and deleting this table in down function.
Operations on columns:
Add:
Syntax for adding column is:
- <?php
- $table->column_type('column_name')->more_operation();
List of all column types is here: Schema Builder in Laravel.
Change:
For changing column use change() function. For example, for changing name column (size 20 to 50) use:
- <?php
- $table->string('name', 50)->change();
Rename:
For rename use renameColumn() function.
- <?php
- $table->renameColumn('from', 'to');
Delete:
For deleting a column use dropColumn() function like
- <?php
- $table->dropColumn('votes');
Adding and dropping Indexes:
For adding indexes use indexes functions like:
- <?php
- $table->string('email')->unique();
Similarly, for dropping indexes use corresponding functions like
- <?php
- $table->dropUnique('users_email_unique');
Adding Foreign Key:
For adding a foreign key, first define the column as unsigned type and then add reference (foreign column) and foreign table with it.
- <?php
-
-
- $table->unsignedBigInteger('user_id');
- $table->foreign('<span data-pwa-id="pwa-9B9976868EE5B619D6246A30B6906C48" data-pwa-category="" data-pwa-hint="Unknown word: author_id" data-pwa-suggestions="third~authorized~thread~authorised~Thursday" data-pwa-dictionary-word="author_id" class="pwa-mark-done">user_id</span>')
- ->references('id')
- ->on('users')
- ->onDelete('cascade');
Here we are referencing user_id column to id column of users table.
Also see Django vs Laravel vs Ruby on rails
Checking Existence:
For checking existence of a table/ column use Schema::hasTable('table_name') / Schema::hasColumn('table_name', 'column_name').
Dropping Timestamps & SoftDeletes:
For deleting timestamps and soft deletes use functions $table->dropTimestamps(); and $table->dropSoftDeletes(); respectively.
Migration commands:
- php artisan migrate: Update database. If you will run this command after configuring database settings then laravel will automatically create/ modify the database tables according to migrations defined in migrations folder. By default laravel will form three tables (users, failed_jobs and migrations). migrations table in database handles the migrations (store migration entries defined in migration folder).
- php artisan migrate:rollback: Undo last migration.
- php artisan migrate:reset: Undo all migrations. It will delete all tables from database except migrations table.
- php artisan migrate:refresh: Undo all migrations and then migrate again.