Good content takes time and effort to come up with.

Please consider supporting us by just disabling your AD BLOCKER and reloading this page again.







Laravel 7.x Multiple Database Connections, Migrations, Relationships & Querying


LARAVEL DATABASE QUERYING MIGRATIONS MYSQL RELATIONSHIPS LARAVEL
02nd May 2020 8 mins read
Share Article     Share On WhatsApp   Buy Me Coffee | StackCoder Buy Coffee


Trying to connect more than 1 database in Laravel, then you have come to the right place.


In this article you will learn how to connect to 2 MYSQL databases in Laravel.

  1. Creating 2 Databases
  2. Setting Up Connection To 2 Databases In Laravel
  3. Creating Folder Structure, Migrations For 2 Different Databases
  4. Run Migrations & Rollback Them
  5. Creating Relationships In Models Across Databases
  6. Running Query To Get Data From Different Databases


First and then foremost question is why the heck do I need more than 1 database for my project. One of the answer is making your project modular or think that you want certain things to keep independent of that of current database.


Code Available @ GitHub


Use this link to get the full project on GitHub Laravel Multiple Database Connection

Prerequisites


You need to have a fresh installation of Laravel or existing Laravel project.


If you don't have a Laravel then, first go to the folder (htdocs or html most of the times) where you want to install the fresh copy of Laravel & use the following command to setup one.


multidb is the name of the project that I will be creating. Change as per you needs.


composer create-project --prefer-dist laravel/laravel multidb

Step 1 - Creating 2 Databases In MYSQL


NOTE: I have created blog1 & blog2 database in MYSQL for the sake of example. You may have some other database name. I am creating both in MYSQL, you might have requirement that blog1 database may be in MYSQL & blog2 might be in Postgres Database


You can use GUI tools like PHPMyAdmin, MYSQL Workbench, Sequel Pro for creating databases faster.


If you want to create database from command prompt then first login to MYSQL terminal using the following command


sudo mysql -uroot -p 


It asks for password enter your database password.


Now create databases blog1 & blog2, please change database name as per your requirements


In MYSQL command prompt use the following commands to create. I am using UTF8 character set.


CREATE DATABASE blog1 CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE DATABASE blog2 CHARACTER SET utf8 COLLATE utf8_general_ci;

Step 2 - Multiple MYSQL Connection .env Settings


Default you have the following connection, which connects to blog1 database, I am using MYSQL for both databases, make sure to choose the database driver which you need.


.env Settings


blog1 Database in .env


DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=blog1
DB_USERNAME=root
DB_PASSWORD=root


blog2 Database in .env


DB_CONNECTION_SECOND=mysql
DB_HOST_SECOND=127.0.0.1
DB_PORT_SECOND=3306
DB_DATABASE_SECOND=blog2
DB_USERNAME_SECOND=root
DB_PASSWORD_SECOND=root


Overall your connection will look like the following in .env


DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=blog1
DB_USERNAME=root
DB_PASSWORD=root

DB_CONNECTION_SECOND=mysql
DB_HOST_SECOND=127.0.0.1
DB_PORT_SECOND=3306
DB_DATABASE_SECOND=blog2
DB_USERNAME_SECOND=root
DB_PASSWORD_SECOND=root

app/config/database.php Settings


Observe the following line carefully, it tells which connection to make default


'default' => env('DB_CONNECTION', 'mysql'),


Note: Copy mysql to mysql2 like the following in app/config/database.php file. And in mysql2 array check _SECOND settings which will be loaded from .env file


In app/config/database.php


return [
    /** Change mysql or mysql2 to make which one you wan to make it default */
    'default' => env('DB_CONNECTION', 'mysql'),

    'connections' => [
        /** Other database code */
        'mysql' => [
            'driver' => 'mysql',
            'url' => env('DATABASE_URL'),
            'host' => env('DB_HOST', '127.0.0.1'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE', 'forge'),
            'username' => env('DB_USERNAME', 'forge'),
            'password' => env('DB_PASSWORD', ''),
            'unix_socket' => env('DB_SOCKET', ''),
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'prefix_indexes' => true,
            'strict' => true,
            'engine' => null,
            'options' => extension_loaded('pdo_mysql') ? array_filter([
                PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
            ]) : [],
        ],

        'mysql2' => [
            'driver' => 'mysql',
            'url' => env('DATABASE_URL_SECOND'),
            'host' => env('DB_HOST_SECOND', '127.0.0.1'),
            'port' => env('DB_PORT_SECOND', '3306'),
            'database' => env('DB_DATABASE_SECOND', 'forge'),
            'username' => env('DB_USERNAME_SECOND', 'forge'),
            'password' => env('DB_PASSWORD_SECOND', ''),
            'unix_socket' => env('DB_SOCKET_SECOND', ''),
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'prefix_indexes' => true,
            'strict' => true,
            'engine' => null,
            'options' => extension_loaded('pdo_mysql') ? array_filter([
                PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
            ]) : [],
        ],
        /** Other database code */
    ]
];


Note: I have kep mysql as my default connection which means blog1 is default database

Step 3 - Creating Migrations For 2 Databases


Now you have setup your database connections lets run migrations for 2 different databases


I want to segregate the databases in separate folders so that I can migrate them very easily and even can check which tables belongs to which database I usually prefer the following folders structure:


database/migrations/blog1/


database/migrations/blog2/


Example:
I want to create users in blog1 database with migrations inside database/migrations/blog1/ folder.
I want to create posts in blog2 database with migrations inside database/migrations/blog2/ folder.


Migrations In Blog1 & Blog2 Database

Migrations In Blog1 & Blog2 Database


Use the following command to make Model & Migrations


NOTE: Make blog1 & blog2 folders inside database/migrations first else you will get error as No such file or directory


php artisan make:model Models/User
php artisan make:model Models/Post
php artisan make:migration create_users_table --path="database/migrations/blog1" --table=users
php artisan make:migration create_posts_table --path="database/migrations/blog2" --table=posts


With the above now your migrations code must look somewhat similar to the following


In users migrations class


Tip: Here no need to specify which database connection it should go for as by default its blog1


public function up()
{
  Schema::create('users', function (Blueprint $table) {
    $table->bigIncrements('id');
    $table->string('email');
    $table->string('password');
    $table->string('name');
    $table->timestamps();
  });
}

public function down()
{
  Schema::dropIfExists('users');
}


In posts migrations class


Tip: Observer Schema::connection('mysql2') it tells that the table posts must go for blog2 database.


use Illuminate\Database\Query\Expression;
/** Other code */
public function up()
{
    Schema::connection('mysql2')->create('posts', function (Blueprint $table) {
        $table->bigIncrements('id');
        $table->unsignedBigInteger('user_id');
        $table->foreign('user_id')->references('id')->on(new Expression('blog1.users'));
        $table->string('title');
        $table->longText('body');
        $table->dateTime('published_on')->nullable();
        $table->timestamps();
    });
}

public function down()
{
    Schema::connection('mysql2')->dropIfExists('posts');
}


Observer the following for foreign key across blog1 databases


$table->unsignedBigInteger('user_id');
$table->foreign('user_id')->references('id')->on(new Expression('blog1.users'));

Step 4 - Run Migrations & Rollback Them


This is a tricker part. Now as we know we have stored the migrations in blog1 & blog2 folder respectively. Now we need to run them seperately.


Running Migrations


Tip: --database & --path is very important else your migrations wont work


php artisan migrate --database="mysql" --path="database/migrations/blog1"


php artisan migrate --database="mysql2" --path="database/migrations/blog2"



Rollback Migrations


Tip: --database & --path is very important else Laravel will throw error


php artisan migrate:rollback --database="mysql2" --path="database/migrations/blog2"


php artisan migrate:rollback --database="mysql" --path="database/migrations/blog1"


Note: Also note that when you do rollback ie when you try to delete users table first before posts table then you will get parent table cant be deleted. So use the following code before doing so.


In down() method of users & posts migrations add the following code if you don't want the foreign key errors to be thrown


class CreateUsersTable extends Migration
{
    public function down()
    {
        \DB::select("SET FOREIGN_KEY_CHECKS = 0");
        Schema::dropIfExists('users');
        \DB::select("SET FOREIGN_KEY_CHECKS = 1");
    }
}


 class CreatePostsTable extends Migration
    public function down()
    {
        \DB::select("SET FOREIGN_KEY_CHECKS = 0");
        Schema::connection('mysql2')->dropIfExists('posts');
        \DB::select("SET FOREIGN_KEY_CHECKS = 1");
    }
}

Step 5 - Relations In Models


Now we need to create relationships between the models


User Model


Tip: Make sure to include protected $connection = 'mysql';


class User extends Model
{
    /** Make sure to include this line in every model */
    protected $connection = 'mysql';
    
    public function posts()
    {
        return $this->hasMany('App\Models\Post');
    }
}



Post Model


Tip: Make sure to include protected $connection = 'mysql2';


class Post extends Model
{
    protected $connection = 'mysql2';

    public function user()
    {
        return $this->belongsTo('App\Models\User');
    }
}

Step 6 - Running Queries


Now run the queries to get the different database related data.


I am using tinker for demo


php artisan tinker


Query - Get User and his posts data with following query


App\Models\User::with(['posts'])->get();


Example Output:


>>> App\Models\User::with(['posts'])->get();
=> Illuminate\Database\Eloquent\Collection {#3030
   all: [
    App\Models\User {#3042
     id: 1,
     email: "channaveer@gmail.com",
     password: "$2y$10$sMQo8KdUIqlazZaY3U.8LO6cuWo0OthweJe/hfQ2IKX1eKTQMUP0G",
     name: "channaveer",
     created_at: null,
     updated_at: null,
     posts: Illuminate\Database\Eloquent\Collection {#3026
      all: [
       App\Models\Post {#3046
        id: 3,
        user_id: 1,
        title: "FirstPost",
        body: "First Body",
        published_on: null,
        created_at: null,
        updated_at: null,
       },
       App\Models\Post {#3048
        id: 4,
        user_id: 1,
        title: "Second Post",
        body: "Second Body",
        published_on: null,
        created_at: null,
        updated_at: null,
       },
      ],
     },
    },
   ],
  }



Code Available @ GitHub


Use this link to get the full project on GitHub Laravel Multiple Database Connection

Conclusion


I really enjoyed writing this article. Hope it was helpful for you. If you like it then please do share with your friends. You can you Twitter & WhatsApp icon to share.


WHATS NEXT?


You might be interested in reading few of my other articles


How To Install Apache Web Server On Ubuntu 20.04 / Linux & Manage It

How To Create / Save / Download PDF From Blade Template In PHP Laravel

How To Add Free SSL Certificate In cPanel With ZeroSSL & Certbot

How To Securely SSH Your Server & Push Files With FileZilla

How To Push Files To CPanel / Remote Server using FTP Software FileZilla

How To Install Linux, Apache, MYSQL, PHP (LAMP Stack) on Ubuntu

How To Cache Static Files With NGINX Server

Redirect www to a non-www website or vice versa

How To Create Free SSL Certificate With Lets Encrypt/Certbot In Linux (Single / Multiple Domains)

How To Install Linux, NGINX, MYSQL, PHP (LEMP Stack) on Ubuntu

PHP Built-In Web Server & Testing Your Development Project In Mobile Without Any Software

How To Do Google reCAPTCHA Integration In PHP Laravel Forms


Happy Coding :)








Author Image
AUTHOR

Channaveer Hakari

I am a full-stack developer working at WifiDabba India Pvt Ltd. I started this blog so that I can share my knowledge and enhance my skills with constant learning.

Never stop learning. If you stop learning, you stop growing