How to use multiple databases in Laravel
Using .env
>= 5.0 (Tested on 5.5) (Works on 8)
In .env
DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=database1
DB_USERNAME=root
DB_PASSWORD=secret
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=secret
In config/database.php
'mysql' => [
'driver' => env('DB_CONNECTION'),
'host' => env('DB_HOST'),
'port' => env('DB_PORT'),
'database' => env('DB_DATABASE'),
'username' => env('DB_USERNAME'),
'password' => env('DB_PASSWORD'),
],
'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'),
],
Note: In
mysql2
if DB_username and DB_password is same, then you can useenv('DB_USERNAME')
which is metioned in.env
first few lines.
Without .env
<5.0
Define Connections
app/config/database.php
return array(
'default' => 'mysql',
'connections' => array(
# Primary/Default database connection
'mysql' => array(
'driver' => 'mysql',
'host' => '127.0.0.1',
'database' => 'database1',
'username' => 'root',
'password' => 'secret'
'charset' => 'utf8',
'collation' => 'utf8_unicode_ci',
'prefix' => '',
),
# Secondary database connection
'mysql2' => array(
'driver' => 'mysql',
'host' => '127.0.0.1',
'database' => 'database2',
'username' => 'root',
'password' => 'secret'
'charset' => 'utf8',
'collation' => 'utf8_unicode_ci',
'prefix' => '',
),
),
);
Schema
To specify which connection to use, simply run the connection()
method
Schema::connection('mysql2')->create('some_table', function($table)
{
$table->increments('id'):
});
Query Builder
$users = DB::connection('mysql2')->select(...);
Eloquent
Set the $connection
variable in your model
class SomeModel extends Eloquent {
protected $connection = 'mysql2';
}
You can also define the connection at runtime via the setConnection
method or the on
static method:
class SomeController extends BaseController {
public function someMethod()
{
$someModel = new SomeModel;
$someModel->setConnection('mysql2'); // non-static method
$something = $someModel->find(1);
$something = SomeModel::on('mysql2')->find(1); // static method
return $something;
}
}
Note Be careful about attempting to build relationships with tables across databases! It is possible to do, but it can come with some caveats and depends on what database and/or database settings you have.
From Laravel Docs
Using Multiple Database Connections
When using multiple connections, you may access each connection
via the connection method on the DB
facade. The name
passed to the connection
method should correspond to one of the connections listed in your config/database.php
configuration file:
$users = DB::connection('foo')->select(...);
You may also access the raw, underlying PDO instance using the getPdo method on a connection instance:
$pdo = DB::connection()->getPdo();
Useful Links
- Laravel 5 multiple database connection FROM
laracasts.com
- Connect multiple databases in laravel FROM
tutsnare.com
- Multiple DB Connections in Laravel FROM
fideloper.com
Having trouble with using multiple database in laravel 5.7?
In your database.php file try to change
'mysql_2' => [
'driver' => 'mysql',
'host' => env('DB_HOST', '127.0.0.1'),
'port' => env('DB_PORT', '3306'),
'database' => env('DB_DATABASE2', 'database_2'), // THIS IS THE ONE THATS CHANGED
'username' => env('DB_USERNAME', 'root'),
'password' => env('DB_PASSWORD', ''),
.......
],
.ENV FILE
DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=database_1
DB_USERNAME=root
DB_PASSWORD=
DB_CONNECTION=mysql_2
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE2=database_2 // We change 'DB_Database' to 'DB_Database2'
DB_USERNAME=root
DB_PASSWORD=
PHP Laravel eloquent with multiple databases, actually select something
$users1 = DB::connection('mysql_live')->table('users')->get()->toArray();
For the second query you can do the same. There is no reason to use eloquent model for that. But if you want to then:
Inside your User model you must define your connection
protected $connection = 'mysql'; //which is the default as well
And also you must define the fields that you want to be retrieved like:
protected $fillable = ['email','username'];
Really important is to hide sensitive fields like password etc etc. There you can use another array:
protected $hidden = ['password'];
Then you can use the eloquent like:
$users = User::get();
Connect multiple databases with laravel
add new database connection to config/database.php
'mysql' => [
'driver' => env('DB_CONNECTION'),
'host' => env('DB_HOST'),
'port' => env('DB_PORT'),
'database' => env('DB_DATABASE'),
'username' => env('DB_USERNAME'),
'password' => env('DB_PASSWORD'),
],
'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'),
],
and you can use the following:
DB::connection('mysql2')->select(...);
Connect multiple databases dynamically in laravel
One way of changing the connection at runtime is to set the values via the config:
config(['database.connections.mysql' => [
'driver' => 'mysql',
'host' => env('DB_HOST', 'localhost'),
'database' => env('DB_DATABASE', 'my_database'),
'username' => env('DB_USERNAME', 'my_user'),
'password' => env('DB_PASSWORD', 'my_password'),
'charset' => 'utf8',
'collation' => 'utf8_unicode_ci',
'prefix' => '',
'strict' => false,
]]);
This can be applied in a middleware to dynamically switch between tenant databases, for example.
You can also specify a connection via the DB facade:
DB::connection('mysql_2')->select(...);
Related Topics
Target Class Controller Does Not Exist - Laravel 8
How to Bind an Array of Strings With MySQLi Prepared Statement
MySQL Query to Get Column Names
Simplest Way to Profile a PHP Script
Recursive Function to Generate Multidimensional Array from Database Result
PHP $_Post Array Empty Upon Form Submission
Comparing String to Integer Gives Strange Results
How to Repair a Serialized String Which Has Been Corrupted by an Incorrect Byte Count Length
How to Get an Array of Specific "Key" in Multidimensional Array Without Looping
How to Force File Download With PHP
Can PHP Curl Retrieve Response Headers and Body in a Single Request
Warning: a Non-Numeric Value Encountered
How to Convert an Image to Base64 Encoding
Form Submit With Ajax Passing Form Data to PHP Without Page Refresh