In this tutorial, I will give you an example of the “How to Connect Multiple Database Connection in Laravel“, So you can easily apply it with your laravel 5, laravel 6, laravel 7, and laravel 8 application.
First, what we’re doing here, This is the example :
Need of Multiple Database Connection in Laravel
We use a single database on our project mostly but sometimes while we work on bigger projects like advanced ERP or CRM and healthcare applications where data is stored in multiple databases and we need to manage or create numbers of tables in multiple databases. so we need to connect multiple databases to manage all the large amounts of data, so we need to connect multiple databases to manage all the large amounts of Data.
Here we have created 3 databases and we have some entries in these tables for testing.
- 8bityard
- laravel_db_test_1
- laravel_db_test_2
Database Configuration
At first, we need to update our database configuration to connect to these databases, and then we create models and a controller for fetching data from these different databases tables we can use query builder or ORM to performing this task, we use Laravel ORM.
.env file :
We will find here we are already connected with a single database with the name of 8bityard with is belong to 8bityard Database. So, we want to add two more databases just copy these database properties and paste them twice we just change the property name like: “_SECOND” and “_THIRD”; we change the database name, host, user, password,
you can put any names it’s not mandatory, like : DB_CONNECTION_1, DB_CONNECTION_2, DB_CONNECTION_3
APP_NAME=Laravel
APP_ENV=local
APP_KEY=base64:CHKbTU+tDJrWLxSlOwpLJYRpyQq3CnDc9Hrw3K2cTBI=
APP_DEBUG=true
APP_URL=http://localhost
LOG_CHANNEL=stack
DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=8bitYard
DB_USERNAME=root
DB_PASSWORD=
DB_CONNECTION_SECOND=mysql
DB_HOST_SECOND=127.0.0.1
DB_PORT_SECOND=3306
DB_DATABASE_SECOND=laravel_db_test_1
DB_USERNAME_SECOND=root
DB_PASSWORD_SECOND=
DB_CONNECTION_THIRD=mysql
DB_HOST_THIRD=127.0.0.1
DB_PORT_THIRD=3306
DB_DATABASE_THIRD=laravel_db_test_2
DB_USERNAME_THIRD=root
DB_PASSWORD_THIRD=
BROADCAST_DRIVER=log
CACHE_DRIVER=file
QUEUE_CONNECTION=database
SESSION_DRIVER=file
SESSION_LIFETIME=120
After this step, we need to change on database.php file.
config\database.php :
In the databse.php file, you can see there are lots of connections available on this file like mysql,pgsql,sqlsrv.
We just copy the SQL server and paste it twice. we rename the second or third databases like mysql2,mysql3 and rename all the other databases’ properties,
DB_HOST_SECOND,DB_PORT_SECOND,DB_DATABASE_SECOND,DB_USERNAME_SECOND,DB_PASSWORD_SECOND
DB_HOST_THIRD,DB_PORT_THIRD,DB_DATABASE_THIRD,DB_USERNAME_THIRD,DB_PASSWORD_THIRD.
'connections' => [
'sqlite' => [
'driver' => 'sqlite',
'url' => env('DATABASE_URL'),
'database' => env('DB_DATABASE', database_path('database.sqlite')),
'prefix' => '',
'foreign_key_constraints' => env('DB_FOREIGN_KEYS', true),
],
'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'),
'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', ''),
'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'),
]) : [],
],
'mysql3' => [
'driver' => 'mysql',
'url' => env('DATABASE_URL'),
'host' => env('DB_HOST_THIRD', '127.0.0.1'),
'port' => env('DB_PORT_THIRD', '3306'),
'database' => env('DB_DATABASE_THIRD', 'forge'),
'username' => env('DB_USERNAME_THIRD', 'forge'),
'password' => env('DB_PASSWORD_THIRD', ''),
'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'),
]) : [],
],
'pgsql' => [
'driver' => 'pgsql',
'url' => env('DATABASE_URL'),
'host' => env('DB_HOST', '127.0.0.1'),
'port' => env('DB_PORT', '5432'),
'database' => env('DB_DATABASE', 'forge'),
'username' => env('DB_USERNAME', 'forge'),
'password' => env('DB_PASSWORD', ''),
'charset' => 'utf8',
'prefix' => '',
'prefix_indexes' => true,
'schema' => 'public',
'sslmode' => 'prefer',
],
'sqlsrv' => [
'driver' => 'sqlsrv',
'url' => env('DATABASE_URL'),
'host' => env('DB_HOST', 'localhost'),
'port' => env('DB_PORT', '1433'),
'database' => env('DB_DATABASE', 'forge'),
'username' => env('DB_USERNAME', 'forge'),
'password' => env('DB_PASSWORD', ''),
'charset' => 'utf8',
'prefix' => '',
'prefix_indexes' => true,
],
],After these all steps Please restart your server using php artisan:serve command in the terminal.
Define Connection in Models :
We have already created 3 models (Category, Contact, Newsletter) and provide the database connection (mysql2,mysql3) where these tables exist, for fetching data from different databases and import all these models.
- Category.php
- Contact.php
- Newsletter.php
app\Category.php
<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class Category extends Model
{
protected $table = 'categories';
}
app\Model\Contact.php
<?php
namespace App\Model;
use Illuminate\Database\Eloquent\Model;
class Contact extends Model
{
public $connection = "mysql2";
}
app\Model\Newsletter.php
<?php
namespace App\Model;
use Illuminate\Database\Eloquent\Model;
class Newsletter extends Model
{
public $connection = "mysql3";
}
Create Route :
routes\web.php
#Test and Connect Multiple DB
Route::get('/get-db-data','TestMultipleDbController@list_tabledata');Create Controller :
php artisan make:controller TestMultipleDbControllerApp\Http\Controllers\TestMultipleDbController
<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use App\Category;
use App\Model\Contact;
use App\Model\Newsletter;
class TestMultipleDbController extends Controller
{
function list_tabledata()
{
$getfirst_db_data = Category::all();
$getsecond_db_data = Contact::all();
$getthird_db_data = Newsletter::all();
dd($getfirst_db_data,$getsecond_db_data,$getthird_db_data);
}
}
After following these steps successfully, you are able to fetch these tables’ data from different databases.
Now, you can Concat or merge the different collections of model data from multiple databases easily.
In this article, we learned “Multiple Database Connection in Laravel”, I hope this article will help you with your Laravel application Project.
Also Read: Get the day difference between the two dates in Laravel.
Gaurav Pandey
Hi, My name is Gaurav Pandey. I'm a Laravel developer, owner of 8Bityard. I live in Uttarakhand - India and I love to write tutorials and tips that can help other developers. I am a big fan of PHP, Javascript, JQuery, Laravel, WordPress. connect@8bityard.com
