Multiple Database Connection in Laravel 9
Mar 14, 2022 . Admin
Hi Dev,
Are you looking to multiple database connection in laravel 9? Sometimes, you may come across a situation where you need to deal with more than one database. In this article, we study multiple database connection in laravel 9 application.you can understand the concept of laravel 9 multiple database connections .env. you will do the following things for multiple database connection in laravel 9.
In this article, we will discuss the “How to use Multiple Database Connections in Laravel 9”. In some conditions, we need to connect multiple databases to the Laravel application.
We need to use multiple database connections like MySQL, MongoDB, etc. I can say when you work with a large number of projects then you will need maybe. So let's follow bellow steps.
Step 1: Download LaravelLet us begin the tutorial by installing a new laravel application. if you have already created the project, then skip following step.
composer create-project laravel/laravel example-appStep 2: Set ENV Variable:
Here, you need to set configuration variable on .env file. let's create as bellow:
.envDB_CONNECTION=mysql DB_HOST=127.0.0.1 DB_PORT=3306 DB_DATABASE=mydatabase DB_USERNAME=root DB_PASSWORD=root DB_CONNECTION_SECOND=mysql DB_HOST_SECOND=127.0.0.1 DB_PORT_SECOND=3306 DB_DATABASE_SECOND=mydatabase2 DB_USERNAME_SECOND=root DB_PASSWORD_SECOND=rootStep 3: Database Configuration:
Now, as we created variable in env file, we need to use that variable on config file so let's open database.php file and add new connections key as like bellow:
config/database.php<?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'), ]) : [], ], 'mysql_second' => [ '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'), ]) : [], ], .....Step 4: Getting Data from Multiple Database using DB:
I will give write two routes with getting products from different database connections. you can see simple example with DB.
let's add two routes in your file:
routes/web.php<?php use Illuminate\Support\Facades\Route; /* |-------------------------------------------------------------------------- | Web Routes |-------------------------------------------------------------------------- | | Here is where you can register web routes for your application. These | routes are loaded by the RouteServiceProvider within a group which | contains the "web" middleware group. Now create something great! | */ /*------------------------------------------ -------------------------------------------- Getting Records of Mysql Database Connections -------------------------------------------- --------------------------------------------*/ Route::get('/get-mysql-products', function () { $products = DB::table("products")->get(); dd($products); }); /*------------------------------------------ -------------------------------------------- Getting Records of Mysql Second Database Connections -------------------------------------------- --------------------------------------------*/ Route::get('/get-mysql-second-products', function () { $products = DB::connection('mysql_second')->table("products")->get(); dd($products); });Step 5: Multiple Database Connections with Migration:
you can create separate migrations for multiple database connections:
Default:<?php public function up() { Schema::create('blog', function (Blueprint $table) { $table->increments('id'); $table->string('title'); $table->string('body')->nullable(); $table->timestamps(); }); }Second Database:
<?php public function up() { Schema::connection('mysql_second')->create('blog', function (Blueprint $table) { $table->increments('id'); $table->string('title'); $table->string('body')->nullable(); $table->timestamps(); }); }Step 6: Multiple Database Connections with Model: Default:
<?php namespace App\Models; use Illuminate\Database\Eloquent\Factories\HasFactory; use Illuminate\Database\Eloquent\Model; class Product extends Model { use HasFactory; protected $fillable = [ 'name', 'detail' ]; }Second Database:
<?php namespace App\Models; use Illuminate\Database\Eloquent\Factories\HasFactory; use Illuminate\Database\Eloquent\Model; class Product extends Model { use HasFactory; protected $connection = 'mysql_second'; protected $fillable = [ 'name', 'detail' ]; }Step 7: Multiple Database Connections in Controller: Default:
<?php use App\Models\Product; class ProductController extends BaseController { /** * Write code on Method * * @return response() */ public function getRecord() { $products = Product::get(); return $products; } }Second Database:
<?php use App\Models\Product; class ProductController extends BaseController { /** * Write code on Method * * @return response() */ public function getRecord() { $product = new Product; $product->setConnection('mysql_second'); $something = $product->find(1); return $something; } }
I hope it can help you...