Like Other MVC frameworks, In Laravel Interacting with databases has been made extremely easy across different kinds of databases that laravel supports. As per current version(v 5.4), Laravel supports 4 types of databases.

  1. MySQL
  2. PostgreSQL
  3. SQLite
  4. SQL Server

From these databases this course will focus on MySQL database. In this course we will focus on laravel database connectivity and migration.

Configuration

In Laravel database connection string is specified inside the configuration file for all types of databases. It is is located at ‘config/database.php’ .

Inside database.php the default connection configuration is set for each kinds of database. So, we are exepected to modify only 2 fields(username & password) if we are working on localhost Or else 3 fields. i.e including changing localhost to our server host address.

        'mysql' => [
            'driver' => 'mysql',
            'host' => env('DB_HOST', 'localhost'),//your host ip address
            'port' => env('DB_PORT', '3306'),//mysql port address
            'database' => env('DB_DATABASE', 'laravel'),//database name -> laravel
            'username' => env('DB_USERNAME', 'root'),//user name
            'password' => env('DB_PASSWORD', ''),//mysql password if
            'unix_socket' => env('DB_SOCKET', ''),
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'strict' => true,
            'engine' => null,
        ],

Now we have successfully finished the database configuration setup. But in some occasions if you want to use more than one database connections one for SELECT statements, and another for INSERT, UPDATE, and DELETE statements.  You can implement it as follows

'mysql' => [
    'read' => [
        'host' => 'localhost',//your host IP option1
    ],
    'write' => [
        'host' => 'localhost'//your host IP option2
    ],
    'driver'    => 'mysql',
    'database'  => 'laravel',//database name
    'username'  => 'root',//username
    'password'  => '',
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    'prefix'    => '',
],

Migration

When we talk about migrations we are talking about about creating tables. To create a migration, use the following command.

php artisan make:migration create_users_table

After running the above command our new migration file will be created inside ‘database/migrations’. Every migration file name contains a timestamp. This helps Laravel to determine the order of the migrations.

The content of the generated file will look like as follows.

<?php

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateUserTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('flights', function (Blueprint $table) {
            $table->increments('id');
            $table->string('username')->unique();
            $table->string('name');
            $table->string('email');
            //$table->integer('role_id')->unsigned();
            $table->timestamps();

            //$table->foreign('role_id')->references('id')->on('role');    
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::drop('flights');
    }
}

I have modified the originally generated file by adding (username,name,email) columns.

Also you can un-comment the foreign key relationship if you want to have a foreign key relationship

Now, After being sure that I have included all the columns that I need in the table, I will run the following command to migrate(to run the create table command)

php artisan migrate

After running this command you will see the table created in phpmyadmin.

What if you want to rename or drop column after creating table?

Before proceeding with this operation, You need to be sure that you have added doctrine/dbal dependency to your composer.json file.

  • Renaming Column

If you want to rename a column, simply use renameColumn method like as follows:

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

Also If you want to drop a column, simply you can use dropColumn method like as follows:

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

What if you want to drop multiple columns. You can do it as follows

Schema::table('users', function (Blueprint $table) {
    $table->dropColumn(['name', 'email']);
});

Foreign Key Constraints

You can create foreign key constraints in between two tables. For example, let’s define a role_id  column on the users table that references the id column on a roles table:

Schema::table('users', function (Blueprint $table) {
    $table->integer('role_id')->unsigned();

    $table->foreign('role_id')->references('id')->on('roles');
});

Also You can specify the what action should be taken for the on delete and on update properties of the constraint like the following:

$table->foreign('role_id')
      ->references('id')->on('roles')
      ->onDelete('cascade');

You may be interested on: CRUD Operation with MySQL DB in laravel

Reference: Laravel official documentation

Categories: LaravelPHPWeb

0 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *