Having trouble with using multiple database in laravel 5.7?

Question

I'm having a problem with Laravel 5.7, it don't recognize a second database connection. Database 2 has a 'countries' table that Database 1 doesn't. But laravel insist with connect the Database 1. This is the error and actual code, please help! Thanks in advance

ERROR

SQLSTATE[42S02]: Base table or view not found: 1146 Table 'database_1.countries' doesn't exist (SQL: select * from countries)

MODEL

class Country extends Model {

    use SoftDeletes;

    protected $connection = 'mysql_2';
    protected $table      = 'countries';
    protected $fillable = ['...'];

}

CONTROLLER / DEBUGGINB

class CountriesController extends Controller {

    public function index(){

       //  neither works, this

       $countries = DB::connection("mysql_2")->select('select * from countries');

       // or this

       $countries = Country::all();

       dd($countries); --> both give ERROR
}

DATABASE.PHP

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

    'connections' => [

        'mysql' => [
            'driver'        => 'mysql',
            'host'          => env('DB_HOST', '127.0.0.1'),
            'port'          => env('DB_PORT', '3306'),
            'database'      => env('DB_DATABASE', 'database_1'), //forge
            'username'      => env('DB_USERNAME', 'root'), // forge
            'password'      => env('DB_PASSWORD', ''),
             .......
        ],

        'mysql_2' => [
            'driver'        => 'mysql',
            'host'          => env('DB_HOST', '127.0.0.1'),
            'port'          => env('DB_PORT', '3306'),
            'database'      => env('DB_DATABASE', 'database_2'),
            'username'      => env('DB_USERNAME', 'root'),
            'password'      => env('DB_PASSWORD', ''),
            .......
       ],

.ENV file

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

DB_CONNECTION=mysql_2
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=database_2
DB_USERNAME=root
DB_PASSWORD=

and CACHE is updated:

php artisan config:cache

  Configuration cache cleared!
  Configuration cached successfully!

You can't have multiple values in the `.env` file for the same variable name. You need a `DB_CONNECTION_2`, `DB_HOST_2`, etc. to have different values. — ceejayoz, Oct 06 '19 at 14:37
thanks Matti, your comments its perfect in the same line that Steven, thanks a lot! — techienomics, Oct 06 '19 at 14:59
Possible duplicate of [How to use multiple databases in Laravel](https://stackoverflow.com/questions/31847054/how-to-use-multiple-databases-in-laravel) — Rashed Hasan, Oct 06 '19 at 15:52

score 2 · Accepted Answer · answered Oct 06 '19 at 14:33

2

In your database.php file try to change

'mysql_2' => [
            'driver'        => 'mysql',
            'host'          => env('DB_HOST', '127.0.0.1'),
            'port'          => env('DB_PORT', '3306'),
            'database'      => env('DB_DATABASE2', 'database_2'), // THIS IS THE ONE THATS CHANGED
            'username'      => env('DB_USERNAME', 'root'),
            'password'      => env('DB_PASSWORD', ''),
            .......
       ],

.ENV FILE

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

DB_CONNECTION=mysql_2
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE2=database_2 // We change 'DB_Database' to 'DB_Database2'
DB_USERNAME=root
DB_PASSWORD=

answered Oct 06 '19 at 14:33

Steven

518
2
4
12

thanks Steven, works greats! You know why this first code works but the second don't? // this work $countries = DB::connection('mysql_test_2')->select('select * from countries'); // this not $countries = Country::select('*')->get(); dd($countries); Thanks a lot! – techienomics Oct 06 '19 at 14:56
Glad to help! Don't forget to mark as best answer, :) – Steven Oct 06 '19 at 14:58
Did you import Country model? Tried your second example and it just works in my app. Also if you're going to get all the columns on that table, rather than typing `select(*)` you can just directly type `get()` that is if you're going for the eloquent method. Use `select()` if you're selecting specific columns. e.g (`$countries = Country::select('id','countryName')->get();`) – Steven Oct 07 '19 at 00:48

score 1 · Answer 2 · answered Oct 06 '19 at 14:57

Another process you can try -

.env

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

CUSTOM_DATABASE=database_2
CUSTOM_USERNAME=root
CUSTOM_PASSWORD=

database.php

'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', ''),
        ...................................
    ],
'custom' => [
        'driver' => 'mysql',
        'url' => env('DATABASE_URL'),
        'host' => env('DB_HOST', '127.0.0.1'),
        'port' => env('DB_PORT', '3306'),
        'database' => env('CUSTOM_DATABASE', 'forge'),
        'username' => env('CUSTOM_USERNAME', 'forge'),
        'password' => env('CUSTOM_PASSWORD', ''),
        ..........................................
    ],

YourModel.php

protected $connection = 'custom';
protected $fillable = [......];

If you use protected $connection in your model then you can use eloquent as usual. If so, then you can run your query like this -

$countries = Country::all();

And in your migration file -

public function up()
{
    Schema::connection('custom')->create('your-table-name', function (Blueprint $table){
        $table->bigIncrements('id');
        ...........................
        $table->timestamps();
    });
}

Having trouble with using multiple database in laravel 5.7?

2 Answers2