How can I connect to multiple databases in laravel for the same query?

Question

I want to execute a raw query which is related to all databases in laravel. So I don't want to point to a specific database. How can I do that in laravel?

Here is my code:

$sql = 'SELECT table_schema \"DB_Name\",
                Round(Sum(data_length + index_length) / 1024 / 1024, 2) \"DB_Size_in_MB\"
        FROM  information_schema.tables
        GROUP BY table_schema';
$dbs_size = DB::statement($sql);

As you see my query doesn't point to any specific database. So I guess I shouldn't use DB::. How can I do that?

Noted that my current code throws this poor error: (nothing more)

Whoops, looks like something went wrong.

your query is using `information_schema` database and the user by which you're connecting with the database might not have permission to access this database. — Haridarshan, Feb 11 '17 at 07:58
@Haridarshan Well how can I give the permission of `information_schema` database accessibility to my app? — stack, Feb 11 '17 at 07:59
Try this `GRANT SELECT ON information_schema.* to ''@'%' identified` by '';` not checked. Execute this with root user — Haridarshan, Feb 11 '17 at 08:02

score 3 · Accepted Answer · answered Feb 11 '17 at 08:06

Firstly you use double quotes too liberally, that's not really supposed to happen in MySQL queries.

Secondly, according to https://laravel.com/docs/5.4/database#running-queries , statement doesn't return anything.

You have a couple of options to use multiple databases:

1) Raw select

$sql = 'SELECT table_schema DB_Name,
            Round(Sum(data_length + index_length) / 1024 / 1024, 2) DB_Size_in_MB
    FROM  information_schema.tables
    GROUP BY table_schema';
$dbs_size = DB::select($sql);

2) Configure laravel to use that database by adding the entry in your config/database.php

//other database configs

"information_schema" => [
         'driver'    => 'mysql',
         'database'  => 'information_schema',
         'username'  => 'user',
         'password'  => 'pass',
         'charset'   => 'utf8',
         'collation' => 'utf8_unicode_ci',
         'prefix'    => ''
]

Then query via:

 $dbs_size = DB::connection("information_schema")->table("tables")->groupBy("table_schema")->select(DB::raw("...")); // rest of the query..

score 3 · Answer 2 · answered Feb 11 '17 at 10:55

.env File.

DB_HOST=192.168.1.211
DB_DATABASE=sonirocks_dgfbh
DB_USERNAME=sonirocks_dgfbh
DB_PASSWORD=sonirocks_dgfbh

DB_HOST2=192.168.1.211
DB_DATABASE2=sonirocks_dgfbh_log
DB_USERNAME2=sonirocks_dgfbh
DB_PASSWORD2=sonirocks_dgfbh

config/database.php

'connections' => [

    'sqlite' => [
        'driver'   => 'sqlite',
        'database' => storage_path().'/database.sqlite',
        'prefix'   => '',
    ],

    'mysql' => [
        'driver'    => 'mysql',
        'host'      => env('DB_HOST', 'localhost'),
        'database'  => env('DB_DATABASE', 'forge'),
        'username'  => env('DB_USERNAME', 'forge'),
        'password'  => env('DB_PASSWORD', ''),
        'charset'   => 'utf8',
        'collation' => 'utf8_unicode_ci',
        'prefix'    => '',
        'strict'    => false,
    ],

    'mysql2' => [
        'driver'    => 'mysql',
        'host'      => env('DB_HOST2', 'localhost'),
        'database'  => env('DB_DATABASE2', 'forge'),
        'username'  => env('DB_USERNAME2', 'forge'),
        'password'  => env('DB_PASSWORD2', ''),
        'charset'   => 'utf8',
        'collation' => 'utf8_unicode_ci',
        'prefix'    => '',
        'strict'    => false,
    ],

    'pgsql' => [
        'driver'   => 'pgsql',
        'host'     => env('DB_HOST', 'localhost'),
        'database' => env('DB_DATABASE', 'forge'),
        'username' => env('DB_USERNAME', 'forge'),
        'password' => env('DB_PASSWORD', ''),
        'charset'  => 'utf8',
        'prefix'   => '',
        'schema'   => 'public',
    ],

    'sqlsrv' => [
        'driver'   => 'sqlsrv',
        'host'     => env('DB_HOST', 'localhost'),
        'database' => env('DB_DATABASE', 'forge'),
        'username' => env('DB_USERNAME', 'forge'),
        'password' => env('DB_PASSWORD', ''),
        'prefix'   => '',
    ],

],

app/DbLog.php

<?php namespace App;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;

class DbLog extends Model{
    protected $connection = '';

    protected $table = 'users';

    public function set_connection($val){
        $this->connection=$val;
    }
    public function select_all_db2_users(){
        $sql = "
            SELECT *
            FROM $this->table
            LIMIT 0,1;
        ";
        $results=DB::connection($this->connection)
            ->select(DB::raw($sql));
        return $results;
    }

    public function select_all_db_users(){
        $sql = "
            SELECT *
            FROM $this->table
            LIMIT 0,1;
        ";
        $results=DB::connection($this->connection)
            ->select(DB::raw($sql));
        return $results;
    }

    public function close_connection(){
        DB::disconnect('mysql2');
    }
}
?>

app/Http/Controllers/AdminController.php

<?php
namespace App\Http\Controllers;
use App\DbLog;
class AdminController extends Controller {
    function db1(){
        $DbLog=new DbLog();
        $DbLog->set_connection('mysql');
        $select_all_db2_users=$DbLog->select_all_db2_users();
        echo "<pre>";
        print_r ($select_all_db2_users);
        echo "</pre>";
    }

    function db2(){
        $DbLog=new DbLog();
        $DbLog->set_connection('mysql2');
        $select_all_db2_users=$DbLog->select_all_db2_users();
        echo "<pre>";
        print_r ($select_all_db2_users);
        echo "</pre>";
    }
}

How can I connect to multiple databases in laravel for the same query?

2 Answers2