In this article, we will learn how to add multiple database connections in Laravel 9 with example. Sometimes, we need to use multiple database connections in a single Laravel 9 project. So let’s see how to connect multiple databases in Laravel.
Set Database Variables In .env File
First, we need to set the two different database credentials into the .env file. We already have the first database connection variables in .env file. Let’s copy them and paste them for the second database connection variables as below:
.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
Set Database Configuration
As we have created .env variables for the second database. We also need to add those variables to the config/database.php file. Let’s open it and add the following code to it.
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'),
]) : [],
],
.......
After adding the above values you need to run the php artisan config:clear command in your terminal.
Get Data From The Multiple Database In Laravel
The quickest way to test & get the records is by adding test routes in the web.php file. Let’s add the two routes for the first database connection and second database connection as below:
routes/web.php
<?php
use Illuminate\Support\Facades\Route;
use DB;
/* Getting Records of Mysql First Database Connections */
Route::get('/get-first-db-records', function () {
$products = DB::table("products")->get();
dd($products);
});
/* Getting Records of Mysql Second Database Connections */
Route::get('/get-second-db-records', function () {
$products = DB::connection('mysql_second')->table("products")->get();
dd($products);
});
Laravel Migration With Multiple Database Connections
You might have a question in your mind that how we can generate migrations for multiple database connections right? The answer is to use the Schema::connection(‘connection_name’) method to generate a migration for your other database.
Default database:
<?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();
});
}
.....
Laravel Model With Multiple Database Connections
If your migration will be interacting with a database connection other than your application’s default database connection, you should set the $connection property of your migration:
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'
];
}
Laravel Controller With Multiple Database Connections
To access the other database connection in the Laravel controller, you can use the on('connection_name') or setConnection('connection_name') method.
Default:
<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use App\Models\Product;
class ProductController extends BaseController
{
public function getRecord()
{
$products = Product::get();
return $products;
}
}
Second database:
<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use App\Models\Product;
class ProductController extends BaseController
{
public function getRecord()
{
// Using setConnection() method
$product = new Product();
$product->setConnection('mysql_second');
$product = $product->find(1);
$product->name = "First Product";
$product->save();
// Using on() method
$firstProduct = Product::on('mysql_second')->find(1);
dd($firstProduct);
}
}
Additionally, read our guide:
- Laravel 9 Image Upload Tutorial With Example
- Laravel: Switch Case Statement In Controller Example
- Laravel: Change Column Type In Migration
- Laravel: Change Column Name In Migration
- How To Use Where Date Between In Laravel
- How To Add Laravel Next Prev Pagination
- Laravel Remove Column From Table In Migration
- Laravel: Get Month Name From Date
- Laravel: Increase Quantity If Product Already Exists In Cart
- How To Update Pivot Table In Laravel
- How To Install Vue In Laravel 8 Step By Step
- How To Handle Failed Jobs In Laravel
- Best Ways To Define Global Variable In Laravel
- How To Get Latest Records In Laravel
- Laravel Twilio Send SMS Tutorial With Example
- How To Pass Laravel URL Parameter
- Set Default Value Of Timestamp In Laravel Migration
- Laravel 9 File Upload Tutorial With Example
- How To Schedule Tasks In Laravel With Example
- Laravel Collection Push() And Put() With Example
That’s it from our end. We hope this article helped you to learn the Laravel 9 multiple database connection tutorial with the example.
Please let us know in the comments if everything worked as expected, your issues, or any questions. If you think this article saved your time & money, please do comment, share, like & subscribe. Thank you for reading this post 🙂 Keep Smiling! Happy Coding!
