Laravel App With Multiple Database Connections Defined Only Hits First Database

Question

I am trying to set up a Laravel (9.51) app that is a bundle of sites. All sites' user data is in one database, and each site has a database for its content. Using this article:

How to Use Multiple Database Connections in Laravel 8

And consulting the Laravel docs for Eloquent ORM, and this StackOverflow question:

How to use multiple databases in Laravel

I set up the two connections, set the $connection property in the model, and tried to save, and the app tries to save to the wrong database.

My code looks like this:

.env

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=dbaseone
DB_USERNAME=userone
DB_PASSWORD='my first password'

DB_CONNECTION_2=mysql
DB_HOST_2=127.0.0.1
DB_PORT_2=3306
DB_DATABASE_2=numbertwodbase
DB_USERNAME_2=seconduser
DB_PASSWORD_2='a different password'

config/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', ''),
            '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_2', '127.0.0.1'),
          'port' => env('DB_PORT_2', '3306'),
          'database' => env('DB_DATABASE_2', 'forge'),
          'username' => env('DB_USERNAME_2', 'forge'),
          'password' => env('DB_PASSWORD_2', ''),
          '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'),
          ]) : [],
      ],

App/Models/User.php


class User extends Authenticatable implements MustVerifyEmail
{
  use HasApiTokens, HasFactory, Notifiable, Billable;

  protected $connection = 'mysql2';

And in a service called in my controller, I have this:


use App\Models\User;

. . .

  public function newUser(array $props) {
    $user = new User();
    $user->name = $props['name'];
    $user['email'] = $props['email'];
    $user->password = Hash::make($props['password']);
    if($user->save()) {
      //listener sends verification email
      event(new RegisteredUser($user, $props['return']));
      //logs in for later use
      Auth::login($user);

      return true;
    } else {
      return false;
    }
  }

The result is this error message:

Illuminate\Database\QueryException
PHP 8.2.0
9.51.0
SQLSTATE[42S02]: Base table or view not found: 1146 Table 'dbaseone.users' doesn't exist

Table 'numbertwodbase.users' is the table the app should be trying to save to, and that table does exist.

Thank you

EDIT: To answer StewieSWS question below, here is the relevant part of the config/auth.php file:

\
'providers' => [
    'users' => [
        'driver' => 'eloquent',
        'model' => App\Models\User::class,
    ],

    // 'users' => [
    //     'driver' => 'database',
    //     'table' => 'users',
    // ],
],

It could be related to wrong config for Auth class. Try to play with auth.php. Could you share it here? — StewieSWS, Feb 17 '23 at 13:16
Thanks Stewie. I think the section you are thinking of is what I put in the edit above. Looks right to me. — Fred Polli, Feb 17 '23 at 14:29

Nelson Ekpenyong · Accepted Answer · 2023-02-17T20:32:19.530

1

I see, in that case, the error message "Base table or view not found: 1146 Table 'dbaseone.users' doesn't exist" indicates that the default database connection is being used instead of the specified connection.

One thing you could try is to specify the connection when creating the model, like this:

$user = (new User)->setConnection('mysql2')->create($data);

Because you have the configuration for the the second connection in your config file,you can use the DB::connection('mysql2') method to execute queries against the second database connection.

For example:

$user = DB::connection('mysql2')->table('my_table')->get();

edited Feb 17 '23 at 20:32

answered Feb 17 '23 at 16:24

Nelson Ekpenyong

48
8

Think of it like a company with several DBAs. Each DBA has its own products but all the customers are actually customers of the company. Each web site has a database of its own products, but all of the user data is kept in one database. So I want each site to have two connections, one to its product data and the other to the central user data. – Fred Polli Feb 17 '23 at 16:29
Iv had the same issue in the past and have done it this way as well. Never got it to work without specifying the connection like this – Yeak Feb 17 '23 at 22:42

Laravel App With Multiple Database Connections Defined Only Hits First Database

1 Answers1