Connect Multiple Databases in Laravel 6,7,8 and 9
Step 1 – Create .env variables
The best and easiest way to create a secondary database connection is to store the credentials in a .env file and use it from there.
DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=primary_database
DB_USERNAME=root
DB_PASSWORD=root
DB_DATABASE_TWO=secondary_database
DB_USERNAME_TWO=root
DB_PASSWORD_TWO=rootStep 2 – Update the Database Configuration file
After creating the secondary database variables in .env, you need to configure the secondary connection in database.php as shown below,
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'),
]) : [],
],
'mysql2' => [
'driver' => 'mysql',
'url' => env('DATABASE_URL'),
'host' => env('DB_HOST', '127.0.0.1'),
'port' => env('DB_PORT', '3306'),
'database' => env('DB_DATABASE_TWO', 'forge'),
'username' => env('DB_USERNAME_TWO', 'forge'),
'password' => env('DB_PASSWORD_TWO', ''),
'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'),
]) : [],
],
// ....
],
// ....
];Step 3 – Secondary database connection on the fly
Database connection in Controller
<?php
class GenderController extends Controller
{
public function method1()
{
$gender = new Gender;
$gender->setConnection('mysql2');
return $gender->get();
}
public function method2()
{
return DB::connection('mysql2')->table("genders")->get();
}
}Database connection in Model
<?php
namespace App\Models;
class Gender extends Model
{
protected $connection = 'mysql2'; // This line will get records from mysql2 database data whenever we use this model
protected $fillable = [
...
];
}Database connection in Migration
<?php
use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;
class CreateGendersTable extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::connection('mysql2')->create('genders', function (Blueprint $table) {
$table->id();
$table->string('name');
$table->timestamps();
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::dropIfExists('genders');
}
}I hope this helped you, you can ask me in the comment section if you have any doubts.
Happy Coding!
