Laravel 5 - how to connect multiple databases at the same time?

3

I have already researched in several places, and they all lead to this link . But I could not work using the methods given in the link.

The problem: I have 3 banks configured in config / database.php: "main", "bank-1" and "financial", and I need to use all 3 in the same controller. The system only accesses one of them, the other 2 only serve for validations at the time of login, such as financial verification and others.

I get 3 values per POST: "company", "login" and "password". I create a $empresa variable, which is first used in the banco-1 database. Validations made, the same variable needs to be checked in the financeiro database. After a positive return, then yes I access the system bank and check the login and the password.

class LoginController extends Controller
{
  public function entrar(Request $request){
    $usuario = new \App\usuario();

$login = $request->usuario;
$senha = $request->senha;
$empresa = $request->empresa;
$cliente = DB::connection('banco-1')
->table('clientes')
->select('endereco','banco','nomecompleto','empresa','codigo','cnpj')
->where('empresa', '=',$empresa)->get();

Where $cliente is not a model, it's just a variable. Laravel returns the error saying that the "clients" table does not exist in the "main" database, that is, DB::connection('banco-1') did not connect in bank-1 but in the main, and still the query ran.

The following is the config / database.php file (the sensitive data removed, of course):

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

'connections' => [

    'principal' => [
        'driver' => 'mysql',
        'host' => env('DB_HOST', '-'),
        'port' => env('DB_PORT', '3306'),
        'database' => env('DB_DATABASE', '-'),
        'username' => env('DB_USERNAME', '-'),
        'password' => env('DB_PASSWORD', '-'),
        'charset' => 'utf8mb4',
        'collation' => 'utf8mb4_unicode_ci',
        'prefix' => '',
        'strict' => true,
        'engine' => null,
    ],

    'banco-1' => [
        'driver' => 'mysql',
        'host' => env('DB_HOST', '-'),
        'port' => env('DB_PORT', '3306'),
        'database' => env('DB_DATABASE', '-'),
        'username' => env('DB_USERNAME', '-'),
        'password' => env('DB_PASSWORD', '-'),
        'charset' => 'utf8mb4',
        'collation' => 'utf8mb4_unicode_ci',
        'prefix' => '',
        'strict' => true,
        'engine' => null,
    ],

    'financeiro' => [
        'driver' => 'mysql',
        'host' => env('DB_HOST', '-'),
        'port' => env('DB_PORT', '3306'),
        'database' => env('DB_DATABASE', '-'),
        'username' => env('DB_USERNAME', '-'),
        'password' => env('DB_PASSWORD', '-'),
        'charset' => 'utf8mb4',
        'collation' => 'utf8mb4_unicode_ci',
        'prefix' => '',
        'strict' => true,
        'engine' => null,
    ],
]

Am I forgetting anything?

    
asked by anonymous 17.03.2017 / 21:24

1 answer

2

You are using wrong so the setting is the same on the 3 connections, where the helper ( function ) env is searching in the same key as bank, user, and password settings.

Fixes:

File config/database.php

Added in banco-1 with the settings of .env the number 1 in front of the configuration, eg:

'database' => env('DB_DATABASE1', '-'),
'username' => env('DB_USERNAME1', '-'),
'password' => env('DB_PASSWORD1', '-'),

Also in financeiro with the settings of .env the number 2 in front of the configuration, eg:

'database' => env('DB_DATABASE2', '-'),
'username' => env('DB_USERNAME2', '-'),
'password' => env('DB_PASSWORD2', '-'),

Full file:

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

'connections' => [

    'principal' => [
        'driver' => 'mysql',
        'host' => env('DB_HOST', '-'),
        'port' => env('DB_PORT', '3306'),
        'database' => env('DB_DATABASE', '-'),
        'username' => env('DB_USERNAME', '-'),
        'password' => env('DB_PASSWORD', '-'),
        'charset' => 'utf8mb4',
        'collation' => 'utf8mb4_unicode_ci',
        'prefix' => '',
        'strict' => true,
        'engine' => null,
    ],

    'banco-1' => [
        'driver' => 'mysql',
        'host' => env('DB_HOST', '-'),
        'port' => env('DB_PORT', '3306'),
        'database' => env('DB_DATABASE1', '-'),
        'username' => env('DB_USERNAME1', '-'),
        'password' => env('DB_PASSWORD1', '-'),
        'charset' => 'utf8mb4',
        'collation' => 'utf8mb4_unicode_ci',
        'prefix' => '',
        'strict' => true,
        'engine' => null,
    ],

    'financeiro' => [
        'driver' => 'mysql',
        'host' => env('DB_HOST', '-'),
        'port' => env('DB_PORT', '3306'),
        'database' => env('DB_DATABASE2', '-'),
        'username' => env('DB_USERNAME2', '-'),
        'password' => env('DB_PASSWORD2', '-'),
        'charset' => 'utf8mb4',
        'collation' => 'utf8mb4_unicode_ci',
        'prefix' => '',
        'strict' => true,
        'engine' => null,
    ],
]

where - is trait ), used if the value of the first setting is not found.

In the .env file that is at the root of your project, add these settings as an example below:

APP_ENV=local
APP_KEY=base64:xwuxK/b2WGIUpNb73qJgbF8H3T94YqH6aDBldhECSiw=
APP_DEBUG=true
APP_LOG_LEVEL=debug
APP_URL=http://localhost

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=homestead
DB_USERNAME=root
DB_PASSWORD=senha

DB_DATABASE1=nome_do_banco
DB_USERNAME1=nome_do_usuario
DB_PASSWORD1=senha_do_banco

DB_DATABASE2=nome_do_banco
DB_USERNAME2=nome_do_usuario
DB_PASSWORD2=senha_do_banco

BROADCAST_DRIVER=log
CACHE_DRIVER=file
SESSION_DRIVER=file
QUEUE_DRIVER=sync

REDIS_HOST=127.0.0.1
REDIS_PASSWORD=null
REDIS_PORT=6379

MAIL_DRIVER=smtp
MAIL_HOST=mailtrap.io
MAIL_PORT=2525
MAIL_USERNAME=null
MAIL_PASSWORD=null
MAIL_ENCRYPTION=null

PUSHER_APP_ID=
PUSHER_APP_KEY=
PUSHER_APP_SECRET=

Of course, you will fill in the values with the respective connection settings of your bank, exactly where bank_name , user_name and bank_password .

Direct way:

Change values without helper .env :

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

'connections' => [

    'principal' => [
        'driver' => 'mysql',
        'host' => env('DB_HOST', '-'),
        'port' => env('DB_PORT', '3306'),
        'database' => env('DB_DATABASE', '-'),
        'username' => env('DB_USERNAME', '-'),
        'password' => env('DB_PASSWORD', '-'),
        'charset' => 'utf8mb4',
        'collation' => 'utf8mb4_unicode_ci',
        'prefix' => '',
        'strict' => true,
        'engine' => null,
    ],

    'banco-1' => [
        'driver' => 'mysql',
        'host' => env('DB_HOST', '-'),
        'port' => env('DB_PORT', '3306'),
        'database' => 'nome_do_banco',
        'username' => 'nome_do_usuario',
        'password' => 'senha_do_banco',
        'charset' => 'utf8mb4',
        'collation' => 'utf8mb4_unicode_ci',
        'prefix' => '',
        'strict' => true,
        'engine' => null,
    ],

    'financeiro' => [
        'driver' => 'mysql',
        'host' => env('DB_HOST', '-'),
        'port' => env('DB_PORT', '3306'),
        'database' => 'nome_do_banco',
        'username' => 'nome_do_usuario',
        'password' => 'senha_do_banco',
        'charset' => 'utf8mb4',
        'collation' => 'utf8mb4_unicode_ci',
        'prefix' => '',
        'strict' => true,
        'engine' => null,
    ],
]

Note that the setting is now direct in config/database.php without using the env function. Just change the values where 'bank_name', 'user_name' and 'bank_password' are in the two settings you have created.

Remembering that I only stipulated 3 settings, nothing prevents it from being done in both ways.

I prefer the first one, it gives a bit of work, but I believe it is in a proper place and created by laravel to load settings of this type.

References:

18.03.2017 / 04:42