Are you looking to connect multiple databases to your Laravel application? Sometimes, you may come across a situation where you need to deal with more than one database. In this article, we study how to connect and use multiple databases in the Laravel application. Laravel provides support for multiple database connections in its core itself.

Usually, we need a single database for one website. But, it can be possible that you have to store some data about your application on another database. I worked on an E-commerce project where we wanted to send order details to a separate database. In your case, requirements can be different.

Having said that, let’s take a look at connecting and using multiple databases with Laravel.

Add Multiple Database Connections in Laravel

As I said, Laravel allows you to connect multiple databases. We can use a different database platform like SQLite, MySQL, or SQLSRV with Laravel. For this tutorial, I am going to use another MySQL instance of a separate database. In order words, we are going to deal with a separate MySQL database as additional storage.

Laravel stores a database connection in the config/database.php file. To add another connection of MySQL we have to modify this file.

Let’s add another connection namely ‘mysql2’. You can give any other name to this connection. Just make sure you will use the same name while interacting with the database.

config/database.php

<?php
 
use Illuminate\Support\Str;
 
return [
    ...
 
    '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_2', '127.0.0.1'),
            'port' => env('DB_PORT_2', '3306'),
            'database' => env('DB_DATABASE_2', 'forge'),
            'username' => env('DB_USERNAME_2', 'forge'),
            'password' => env('DB_PASSWORD_2', ''),
            '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'),
            ]) : [],
        ],
    ]

In the above code, the connection ‘mysql’ is the default one. I added a new connection ‘mysql2’ with different constants. Here, I passed the constants as DB_DATABASE_2, DB_USERNAME_2, DB_PASSWORD_2, etc. If your database is hosted on another server then you should change the keys of DB_HOST_2 and DB_PORT_2 also. I assume both databases are on the same server. So, I am not changing the values of these constants.

Laravel gets the values of these constants using the env() method. It means we should add other database details in a ‘.env’ file as below.

DB_HOST_2=127.0.0.1
DB_PORT_2=3306
DB_DATABASE_2=
DB_USERNAME_2=
DB_PASSWORD_2=

After performing the above steps we are done with connecting another database in Laravel. The next stuff is interacting with this database.

Run Migration on Another Database in Laravel

When it comes to Laravel, we should always create tables in the database using Migrations. Let’s say we want to create a ‘products’ table in our separate database. Run the below command which will create a migration file.

php artisan make:migration create_products_table

It will create a new file inside the ‘database/migrations’ directory. Open the file into the editor and pass the newly created connection as shown below.

<?php
 
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
 
class CreateProductsTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::connection('mysql2')->create('products', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->timestamps();
        });
    }
 
    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::connection('mysql2')->dropIfExists('products');
    }
}

I have passed the value ‘mysql2’ to the connection method of the Schema class. It means when you run the migrate command, this code executes the above code on the other database which connection’s string we have passed.

php artisan migrate

After running the above command, head over to your second database. It should now have a ‘products’ table.

Run Queries On Another Database

In Laravel, you can interact with the database either through a Query Builder or using the Eloquent ORM.

DB facade provides a method called connection which is used to run queries on another database. Let’s write a query on connection ‘mysql2’ using a Query Builder.

$products = DB::connection('mysql2')->select('SELECT * FROM products');
$products = DB::connection('mysql2')->select('SELECT * FROM products where id = ?', [1]);

Query Builder will automatically execute the above queries on the other database by taking a reference to the connection method.

When it comes to Eloquent, there are 2 ways of connecting separate databases. The first way is by using the $connection variable inside your Model class.

<?php
 
namespace App\Models;
 
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
 
class Product extends Model
{
    use HasFactory;
 
    /**
     * The connection name for the model.
     *
     * @var string
     */
    protected $connection = 'mysql2'; //pass the connection name here
}

Now the ‘Products’ model will deal with only the second database.

In the second option, you can build a connection on the fly whenever needed. In this way, you can interact with multiple databases from a single Model.

<?php
...
use App\Models\Product;
 
class ProductController extends Controller
{
    public function index()
    {
        $product = new Product;
        $product->setConnection('mysql2');
        $arr_product = $product->find(1);
        dd($arr_product);
    }
}

Here, I have set the connection runtime using setConnection() method.

That’s it! I hope you understand how to connect multiple databases in the Laravel application. Please share your thoughts and suggestions in the comment section below.

Related Articles

• Create Multi Language Website in PHP Using Laravel Framework
• A Guide to Upload and Compress Images in Laravel
• How to Install and Use CKEditor in Laravel

If you liked this article, then please subscribe to our YouTube Channel for video tutorials.