Hey, While developing a web application some times we need to use multiple databases because of project requirement or large scale projects, in this time laravel allows to use multiple database connections.
Step 1 : Set Variable in .env:
.env
DB_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=root
Step2 : Database Configuration:
<?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 3 : Route
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();
});
Multiple Database Connections with Migration:
create separate migrations for multiple database connections:
Default:
<?php
.....
public function up(): void
{
Schema::create('blog', function (Blueprint $table) {
$table->increments('id');
$table->string('title');
$table->string('body')->nullable();
$table->timestamps();
});
}
.....
Second Database:
<?php
.....
public function up(): void
{
Schema::connection('mysql_second')->create('blog', function (Blueprint $table) {
$table->increments('id');
$table->string('title');
$table->string('body')->nullable();
$table->timestamps();
});
}
.....
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'
];
}
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;
}
}
