In this tutorial, we will tell you how to connect multiple databases with the Laravel Framework (Laravel 7 multiple database connections example tutorial). so we will learn to you how to connect multiple MySQL databases with laravel in this tutorial.
The laravel can easily handle the multiple databases and so we can easily access the multiple databases in the laravel. normally we handle a single database connection with Mysql and MongoDB. but we have a large amount of data. that handle for we have to need multiple database connections.
Let’s go we follow the below steps for how to connect multiple databases with laravel application.
Overview
Step 1: Setting Database Configuration
Step 2: Use the Env variable
Step 3: Create Table using Custom connection migration
Step 4: How can use with Model and Controller
Step 5: How can use with Query Builder
Step 1: Setting Database Configuration
we will open the .env file and set the first database and second database credentials detail in the .env file. See below changes in a .env file.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
//for the first database DB_CONNECTION=mysql DB_HOST=127.0.0.1 DB_PORT=3306 DB_DATABASE=database1 DB_USERNAME=root DB_PASSWORD=root // for the second database DB_CONNECTION_SECOND=mysql DB_HOST_SECOND=127.0.0.1 DB_PORT_SECOND=3306 DB_DATABASE_SECOND=database2 DB_USERNAME_SECOND=root DB_PASSWORD_SECOND=root |
Step 2: Use the Env variable
Now, we will use the constant env variable in database.php file. so see below code.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 |
<?php use Illuminate\Support\Str; return [ 'default' => env('DB_CONNECTION', 'mysql'), 'connections' => [ ..... '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' => env('DB_CONNECTION_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' => '', 'charset' => 'utf8mb4', 'collation' => 'utf8mb4_unicode_ci', 'prefix' => '', 'prefix_indexes' => true, 'strict' => true, 'engine' => null, ], ..... ?> |
Step 3: Create Table using Custom connection migration
we can easily create the table using the custom connection migration. but here we have to set the particular connection name then we can create the custom migration.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
<?php ... public function up() { Schema::connection('mysql2')->create('student', function (Blueprint $table) { $table->increments('id'); $table->string('first_name'); $table->string('last_name'); $table->string('email')->unique(); $table->timestamps(); }); } ... ?> |
Step 4: How can use with Model and Controller
Student.php
|
1 2 3 4 5 6 7 8 9 10 11 12 |
<?php namespace App; use Illuminate\Database\Eloquent\Model; class Student extends Model { protected $connection = 'mysql2'; } ?> |
StudentController.php
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
<?php class StudentController extends BaseController { public function student_detail() { $student = new Student; $student->setConnection('mysql2'); $student_detail = $student->find(1); return $student_detail; } } ?> |
Step 5: How can use with Query Builder
|
1 2 |
$student_detail = DB::connection('mysql2')->table("student")->get(); print_r($student_detail); |
