Laravel Multiple Database Connections
In this article, we will discuss the “Laravel Multiple Database Connections”. In some conditions, we need to connect multiple databases to the Laravel application. Let’s see how we implement multiple database connections in Laravel 5.7.
Table of Contents
Prerequisites
We need a fresh Laravel setup where we implement our example.
Database Configuration without .env
In Laravel, database configuration file is located at “config/database.php”. Here we can define more than one database connection of any type. If your application use data from two different MySql database then you can define them easily.
<?php return => [ 'connections' => [ // Default database connection 'mysql' => [ 'driver' => 'mysql', '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' => '', 'strict' => true, 'engine' => null, ], // Custom database connection 'mysql2' => [ 'driver' => 'mysql', 'host' => 'localhost', 'port' => '3306', 'database' => 'lara_multiple', 'username' => 'root', 'password' => '', 'unix_socket' => '', 'charset' => 'utf8mb4', 'collation' => 'utf8mb4_unicode_ci', 'prefix' => '', 'strict' => true, 'engine' => null, ], ] ]
Database Configuration with .env
Update your “config/database.php” with following code snippet.
<?php return => [ 'connections' => [ // Custom database connection 'mysql2' => [ 'driver' => env('DB_CONNECTION_SECOND'), 'host' => env('DB_HOST_SECOND'), 'port' => env('DB_PORT_SECOND'), 'database' => env('DB_DATABASE_SECOND'), 'username' => env('DB_USERNAME_SECOND'), 'password' => env('DB_PASSWORD_SECOND'), ... ], ] ]
After updating the You need to add following code snippet in your “.env” file.
DB_CONNECTION_SECOND=mysql DB_HOST_SECOND=127.0.0.1 DB_PORT_SECOND=3306 DB_DATABASE_SECOND=multi_lara DB_USERNAME_SECOND=root DB_PASSWORD_SECOND=
Custom Connection in Migration
You can use the following example for how to use the custom connection in your migration.
<?php ... public function up() { Schema::connection('mysql2')->create('multipost', function (Blueprint $table) { $table->increments('id'); $table->string('post_title'); $table->string('post_content'); $table->timestamps(); }); } ...
After creating your migration time to execute our migrations for the specific database connection.
php artisan migrate --database=mysql2
Sometimes we face error when we execute the migrate command. Such as:
You can fix this kind of issues via using “config:cache” artisan command.
php artisan config:cache
Custom connection with Model
Set the “$connection” variable in your model.
<?php class SomeModel extends Eloquent { protected $connection = 'mysql2'; }
Custom connection with Controller
You can also define the connection in your controller using “setConnection” method.
<?php class SomeController extends BaseController { public function someMethod() { $someModel = new SomeModel; $someModel->setConnection('mysql2'); $something = $someModel->find(1); return $something; } }
Custom connection with Query Builder
You can also define a connection on the query builder.
DB::connection('mysql2')->select(...)->(...);
Conclusion
In this article, we will discuss Laravel Multiple Database Connections. Thanks for reading this article. Please feel free to add the comment or submit your feedback 🙂
You may like:
Laravel 5.7 New Feature Dump Server
LaRecipe: Create Beautiful Documentation with Markdown
Difference Between Agile and Scrum Methodology
If you like our content, please consider buying us a coffee.
Thank you for your support!
Buy Me a Coffee
Nice article but i seen this article some other links as well but it is not clear in the statement below what is … means DB::connection(‘mysql2’)->select(…)->(…);
Hi Praveen,
It means you can write your query using a DB instance with a specific connection if required.
Hi,
my query is can we use multiple env file at laravel project?
Thanks,
Swapnil Nath
Hello, thanks for the helpful tutorial!
Is it possible to select the database connection on a per-request basis?
We have a database per tenant to keep data from one tenant from other tenants (only having a few tenants).
A request comes in from tenant A so we want to use tenant A’s database to get all the data. If a request from tenant C comes in we want to use C’s connection. The application (models, etc) is the same. We are using Eloquent ORM.
Could you describe that scenario for me?
appreciating your response and thanks for all your effort!
Hi Merle, You can make your own custom logic to play with connections for multi-tenancy. For example, please check Laravel Multi-Tenancy.
Thanks for your quick response!
However it looks like Tenancy doesn’t work with Lumen which was used to build our whole API.
Do you know how one can set the default connection used by Eloquent ORM per request?
You have to make your own logic. Something like dynamically changes the connection as per tenant.
I will try to make an example of this in the upcoming weekend.
I just wanna ask if how can i use that to pull a data from database1 and save it to database 2?