Watch Video:

In the tutorial, we will learn how to add multiple databases to your Laravel app. Laravel has a default database connection which we configure in .env file. But we can only define 1 database configuration. To add multiple databases to the Laravel app we have to edit the config file and set up multiple databases.

Let’s see how the default database is configured in Laravel. Open .env file.

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=laravel
DB_USERNAME=root
DB_PASSWORD=

We need to provide the above credential. For the sqlite database, we need to set up DB_CONNECTION and DB_DATABASE (file path) only.

For the sake of simplicity, we will use sqlite.

1. Configure – 2 Sqlite Database in .env file.

DB_CONNECTION=sqlite

DB_DATABASE='database/database.sqlite'



DB2_CONNECTION=sqlite2

DB2_DATABASE='database/database2.sqlite'

config/database.php

'default' => env('DB_CONNECTION', 'mysql'),



    'connections' => [



        'sqlite' => [

            'driver' => 'sqlite',

            'url' => env('DATABASE_URL'),

            'database' =>  base_path().DIRECTORY_SEPARATOR.env('DB_DATABASE'),

            'prefix' => '',

            'foreign_key_constraints' => env('DB_FOREIGN_KEYS', true),

        ],



        'sqlite2' => [

            'driver' => 'sqlite',

            'url' => env('DATABASE2_URL'),

            'database' => base_path().DIRECTORY_SEPARATOR.env('DB2_DATABASE'),

            'prefix' => '',

            'foreign_key_constraints' => env('DB2_FOREIGN_KEYS', true),

        ],
]

2. Migrations

We store all users to the 1st database and all posts, categories to the 2nd database.

User table - 2014_10_12_000000_create_users_table.php

public function up()

    {

        Schema::connection('sqlite')->create('users', function (Blueprint $table) {

            $table->id();

            $table->string('name');

            $table->string('email')->unique();

            $table->timestamp('email_verified_at')->nullable();

            $table->string('password');

            $table->rememberToken();

            $table->timestamps();

        });

    }

Posts table - 2021_08_13_064810_create_posts_table.php

 public function up()

    {

        Schema::connection('sqlite2')->create('posts', function (Blueprint $table) {

            $table->id();

            $table->unsignedBigInteger('category_id')->nullable();

            $table->text('title');

            $table->text('body');

            $table->timestamps();

        });

    }

Category table - 2021_08_22_075217_create_categories_table.php

   public function up()

    {

      Schema::connection('sqlite2')->create('categories', function (Blueprint $table) {

            $table->id();

            $table->string('name');

            $table->text('about');

            $table->timestamps();

        });

    }

Migrate-

php artisan migrate

3. Seeding

By default in seeding when we create data with models it will store the to the default DB_CONNECTION. So we need the specify the connection for the model.

 $c1 = Category::connection('sqlite2')->create([

           'name' => 'Web Development',

           'about' => 'officia est expedita minus quidem. Modi aut enim sequi optio quas quas nostrum aut. Commodi voluptas ut eos. Dicta alias non sint dolorem magnam. '

       ]);

We can define a model by default which database connection it should use.

In Models/Category.php

    /**

     * The database connection that should be used by the model.

     *

     * @var string

     */

    protected $connection = 'sqlite2';

Now it will store the data in a specific database.

Now run some seeding.

php artisan db:seed

Note: Remember to store all related models in the same Database, otherwise foreign keys will not work.

Please Like and Share this post if you find it helpful.