laravel work with two connections with different prefixes?

I am writing a new project, which is build on a databse already has tables. So I have to add prefixes for the new tables to work with existings tables.

This is the database.php config:

    'connections' => [
        'mysql' => [
            'driver' => 'mysql',
            'url' => env('DB_URL'),
            'host' => env('DB_HOST', '127.0.0.1'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE', 'laravel'),
            'username' => env('DB_USERNAME', 'root'),
            'password' => env('DB_PASSWORD', ''),
            'unix_socket' => env('DB_SOCKET', ''),
            'charset' => env('DB_CHARSET', 'utf8mb4'),
            'collation' => env('DB_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'),
                PDO::ATTR_CASE => PDO::CASE_LOWER
            ]) : [],
        ],

        'mysql_with_prefix' => [
            'driver' => 'mysql',
            'url' => env('DB_URL'),
            'host' => env('DB_HOST', '127.0.0.1'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE', 'laravel'),
            'username' => env('DB_USERNAME', 'root'),
            'password' => env('DB_PASSWORD', ''),
            'unix_socket' => env('DB_SOCKET', ''),
            'charset' => env('DB_CHARSET', 'utf8mb4'),
            'collation' => env('DB_COLLATION', 'utf8mb4_unicode_ci'),
            'prefix' => 'x_campus_',
            'prefix_indexes' => false,
            'strict' => true,
            'engine' => null,
            'options' => extension_loaded('pdo_mysql') ? array_filter([
                PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
                PDO::ATTR_CASE => PDO::CASE_LOWER
            ]) : [],
        ],
    ]

As you can see, the two configs is exactly the same only except prefix.
When I deal with two models, one is with protected $connection = 'mysql' and one without, everything works fine. Transaction works fine, many-to-many queries works fine, except I open up debug bar, There is a Connection Established hint in query list.

select * from `x_campus_users`
Connection Established
select `test`.*, `user_test`.`test_id` as `pivot_test_id`, `user_test`.`user_id` as `pivot_user_id` from `test` inner join `user_test` on `test`.`id` = `user_test`.`user_id` where `user_test`.`test_id` in (1, 2)

What is this Connection Established mean? Does it mean it reconnect the database? Will this affect query performance?

LaryAI

1 year ago

Level 58

The "Connection Established" message in your debug bar indicates that a new database connection was initiated. This typically happens when you switch between different database connections within the same request. In your case, you have two connections defined in your database.php configuration: mysql and mysql_with_prefix. When you use models that are associated with different connections, Laravel will establish a new connection for each one as needed.

Here's a breakdown of what's happening:

Connection Switching: When you use a model that specifies a different connection (protected $connection = 'mysql_with_prefix'), Laravel will establish a new connection to the database using the specified configuration. This is necessary because each connection can have different settings, such as prefixes, host, database, etc.
Performance Impact: Establishing a new connection can have a slight performance impact, especially if it happens frequently within a single request. However, in most cases, this impact is minimal unless you are making a large number of connection switches.
Optimization Tips:
- Minimize Connection Switching: Try to minimize the number of times you switch connections within a single request. Group operations that use the same connection together if possible.
- Connection Pooling: Ensure that your database server is configured to handle multiple connections efficiently. Connection pooling can help mitigate the overhead of establishing new connections.
Debugging: The debug bar is useful for identifying when new connections are established, which can help you optimize your application's database interactions.

In summary, the "Connection Established" message is a normal part of using multiple database connections in Laravel. While it does indicate a new connection, the performance impact is usually minor unless excessive connection switching occurs.

martinbean

1 year ago

Level 80

@russellxu Sure, add prefixes to any new tables introduced, but I don‘t see why you’re then trying to access the tables via two entirely different connections?

If this is a legacy system (and database schema) you’re dealing with, just use Laravel’s schema:dump command to create a “dump” of the database schema as it is today, and then define any changes to that schema (including creating new tables) through individual migration files as normal.

Please or to participate in this conversation.