Multiple database connections in Laravel
This article explains how to set up and configure multiple database connections in Laravel, including how to modify the .env file accordingly.
I will provide a step-by-step guide on using multiple database connections in Laravel with the .env file. We will add configuration variables to the .env file and use them in the database configuration file. By following this guide, you will also learn how to handle migrations, models, and database queries for multiple database connections.
Certainly! When dealing with various projects or complex data requirements, utilizing multiple database connections, such as MySQL, MongoDB, etc., becomes essential. Below are the steps to follow:
Step 1- Identify Database Requirements:
Determine which databases (e.g., MySQL, MongoDB) your project needs to connect to based on its specific requirements.
Step 2- Configure .env File:
Open your Laravel project’s .env file and define configuration variables for each database connection. For example:
DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=database
DB_USERNAME=root
DB_PASSWORD=
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=Step 3- Modify Database Configuration:
Update the config/database.php file to include configuration for each database connection. Define separate arrays for each connection, referencing the corresponding .env variables. For example:
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'),
]) : [],
],Note: You can configure databases other than MySQL, such as MongoDB, by following a similar approach.
<?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'),
]) : [],
],
'mongodb' => [
'driver' => 'mongodb',
'host' => env('DB_HOST_SECOND'),
'port' => env('DB_PORT_SECOND'),
'database' => env('DB_DATABASE_SECOND'),
'password' => env('DB_PASSWORD', ''),
// Other MongoDB-specific configurations...
],Step 4 – Run Migrations:
If you have migrations specific to each database connection, you can run them separately using the --database option. For example
php artisan migrate --database=mysql
php artisan migrate --database=mysql_second
--- OR ----
php artisan migrate --database=mongodbyou can create separate migrations for multiple database connections:
Default –
<?php
public function up(): void
{
Schema::create('user', function (Blueprint $table) {
$table->increments('id');
$table->string('name');
$table->string('email');
$table->timestamps();
});
}
Second database –
<?php
public function up(): void
{
Schema::connection('mysql_second')->create('user', function (Blueprint $table) {
$table->increments('id');
$table->string('title');
$table->string('email');
$table->timestamps();
});
}Step 5 – Multiple Database Connections with Model:
Default –
<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
class User extends Model
{
use HasFactory;
protected $fillable = [
'name', 'email'
];
}Second Database –
<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
class User extends Model
{
use HasFactory;
protected $connection = 'mysql_second';
protected $fillable = [
'name', 'email'
];
}Step 6- Multiple Database Connections in Controller:
Default
<?php
use App\Models\User;
class UserController extends BaseController
{
/**
* Write code on Method
*
* @return response()
*/
public function getRecord()
{
$users = User::get();
return $users;
}
}Second Database –
<?php
use App\Models\User;
class UserController extends BaseController
{
/**
* Write code on Method
*
* @return response()
*/
public function getRecord()
{
$user = new User;
$user->setConnection('mysql_second');
$something = $user->find(1);
return $something;
}
}Hope this articles will help you. Thanks for reading
