Laravel, a renowned PHP framework, stands out for its simplicity and robust features that streamline web development.
Among its many capabilities, Laravel excels in seamlessly connecting to multiple databases, a functionality that proves invaluable for applications requiring access to diverse data sources.
However Laravel’s official documentation lacks more details on how to work with it.
In this comprehensive guide, we’ll delve into the intricacies of configuring and utilizing multiple database connections in Laravel, complemented by practical examples/use-cases to facilitate a deeper understanding.
I wanted to give you few use-cases where having multiple database connections in your Laravel application makes sense. But if you want to skip to the technical aspects click here.
Why use Multiple Databases?
In Laravel or any other web development framework, the use of multiple databases can be beneficial for several reasons:
Data Separation
By utilizing multiple databases, you can separate different types of data or functionalities into their own database instances. For example, you might have one database for user authentication and another for storing application data. This helps in organizing your data logically and makes it easier to manage and scale your application.
Performance Optimization
Distributing your data across multiple databases can improve performance by reducing the load on any single database instance. You can distribute read-heavy operations across replicas or scale-out databases horizontally to handle increased traffic.
Data Isolation and Security
Certain types of data may have different security requirements. By segregating sensitive data into its own database with stricter access controls, you can reduce the risk of unauthorized access or data breaches.
Scalability
As your application grows, you may need to scale different parts of your system independently. Multiple databases provide flexibility in scaling specific components of your application infrastructure according to demand.
Regulatory Compliance
Certain industries or regions may have regulations regarding data storage and access. By segregating data based on compliance requirements, you can ensure that your application meets regulatory standards without affecting the rest of your data.
Third-party Integration
Sometimes, you may need to integrate with third-party systems or legacy databases that have different schemas or requirements. Using multiple databases allows you to interface with these systems more effectively without compromising the integrity of your primary application database.
Let’s jump into implementation
Configure Database Connections in config/database.php
The configuration of database connections in Laravel resides within the config/database.php file. To establish multiple database connections, follow these steps:
<?php
return array(
'default' => 'mysql',
'connections' => array(
# Primary database
'mysql' => [
'driver' => 'mysql',
'url' => env('DB_URL'),
'host' => env('DB_HOST', '127.0.0.1'),
'port' => env('DB_PORT', '3306'),
'database' => env('DB_DATABASE', 'laravel'),
'username' => env('DB_USERNAME', 'root'),
'password' => env('DB_PASSWORD', ''),
'unix_socket' => env('DB_SOCKET', ''),
'charset' => env('DB_CHARSET', 'utf8mb4'),
'collation' => env('DB_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'),
]) : [],
],
# Secondary database
'mysql2' => [
'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'),
]) : [],
],
],
),
);Set Environment Variables
To safeguard sensitive database credentials, utilize Laravel’s environment file .env to store them securely. Configure environment variables for each database connection:
DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=your_database
DB_USERNAME=your_username
DB_PASSWORD=your_password
SECOND_DB_CONNECTION=mysql2
SECOND_DB_HOST=127.0.0.1
SECOND_DB_PORT=3306
SECOND_DB_DATABASE=second_database
SECOND_DB_USERNAME=second_username
SECOND_DB_PASSWORD=second_password
Create Models
Generate Eloquent models for each database connection using Laravel’s artisan command:
php artisan make:model User -m
php artisan make:model Product -mSpecify the connection in the generated model files:
class User extends Eloquent {
protected $connection = 'mysql';
}class Product extends Eloquent {
protected $connection = 'mysql2';
}We will see how to query the data from this models in next steps.
Create & Run Migrations
Laravel’s Schema Builder allows for seamless creation of tables in multiple databases. To specify which connection to use, simply utilize the connection() method:
use Illuminate\Support\Facades\Schema;
Schema::connection('mysql1')->create('users', function($table) {
$table->increments('id');
// Add other table columns as needed
});
Schema::connection('mysql2')->create('products', function($table) {
$table->increments('id');
// Add other table columns as needed
});Execute migrations for each connection individually:
php artisan migrate
php artisan migrate --database=mysql2This ensures the creation of necessary tables in their respective databases.
Query Data from Multiple Databases
Leverage Eloquent to query data from multiple databases effortlessly:
Using Database Query
When working with multiple databases in Laravel, you can execute raw SQL queries to fetch data. Follow these steps to query data using raw database queries:
use Illuminate\Support\Facades\DB;
// Specify the connection and execute the query
$users = DB::connection('mysql')->select('SELECT * FROM users');Using Eloquent
Alternatively, you can leverage Laravel’s Eloquent ORM to query data from multiple databases. Follow these steps to utilize Eloquent for fetching data:
use App\Models\User;
use App\Models\Product;
// Retrieve users from the 'mysql' database
$users = User::all();
// Retrieve products from the 'second_db' database
$products = Product::all();
If you haven’t defined connection in your model, you can also define the connection at runtime via the setConnection method.
class ProductController extends BaseController {
public function get()
{
$product = new Product
->setConnection('mysql2')
->find(1);
return $product;
}
}You can also do this using on() method statically:
class ProductController extends BaseController {
public function get()
{
$product = Product::on('mysql2')
->find(1);
return $product;
}
}Dynamic Connections for Multi-Tenant Application
In a multi-tenant application scenario, where each tenant has its own database, dynamic database connections become indispensable.
Laravel’s flexibility allows for dynamic switching of database connections based on the current tenant. Here’s how you can achieve it:
use Illuminate\Support\Facades\DB;
// Dynamically set the database connection based on the current tenant
DB::connection('tenant_xyz')->table('products')->get();But you can’t manually set connection every single time, instead you can set a default connection in the app service provider like this
// app/Providers/AppServiceProvider.php
use Illuminate\Database\DatabaseManager;
use Illuminate\Contracts\Foundation\Application;
$this->app->resolving('db', function(DatabaseManager $dbManager, Application $app) {
$dbManager->setDefaultConnection('some-connection');
});credits: https://fideloper.com/laravel-multiple-database-connections
Conclusion
Mastering multiple database connections in Laravel empowers developers to build versatile and scalable applications.
By adhering to this step-by-step guide and grasping the practical examples provided, you can efficiently leverage multiple databases within your Laravel projects.
Whether you’re architecting a multi-tenant application or integrating diverse data sources, Laravel equips you with the tools for success. Happy coding!