Web Prepration

Web Concept Tutorials

Multiple database

Laravel Multiple db Connections

by Leave a Comment

In this post, I am going to explain “How to connect multiple databases in Laravel 6”. In Laravel, we can use a multi-database connection in the application. Many times we need to use multiple databases in an application.

I will give you step by step example to implement more than one database in Laravel application using the .env file. I will show you how to add Laravel multiple DB connections also give an example of how to work with migration, model and database query for multiple database connections. So let’s follow the bellow step.

Step 1:- In the first step, you need to add a configuration variables on the .env file. let’s create as bellow:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=mydatabase1
DB_USERNAME=root
DB_PASSWORD=root
DB_CONNECTION_SECOND=mysql
DB_HOST_SECOND=127.0.0.1
DB_PORT_SECOND=3306
DB_DATABASE_SECOND=mydatabase2
DB_USERNAME_SECOND=root
DB_PASSWORD_SECOND=root
 

Step 2:- Now, we need to use that variable on config file so let’s open database.php file and add new connections key as like bellow:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
 
<?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', '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'),
            ]) : [],
        ],
.....
 

Step 3:- How you can use as multiple connection with migrations.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
<?php
.....
public function up()
{
    Schema::connection('mysql2')->create('blog', function (Blueprint $table) {
        $table->increments('id');
        $table->string('title');
        $table->string('body')->nullable();
        $table->timestamps();
    });
}
.....
 

Use with model:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
<?php
  
namespace App;
  
use Illuminate\Database\Eloquent\Model;
  
class Blog extends Model
{
    protected $connection = 'mysql2';
 
    protected $fillable = [
        'id','title','body','created_at','updated_at'
    ];
}
 

Use with Controller:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
<?php
  
class BlogController extends BaseController
{
    public function getRecord()
    {
        $blogModel = new Blog;
        $blogModel->setConnection('mysql2');
        $find = $blogModel->find(1);
        return $find;
    }
}