Level 29
Laravel does not append the database to the sql query. Multiple connections will be made.
Apr 28, 2016
7
Level 20
How to correctly use multiple databases on the same connection in Laravel
I am building a new application in Laravel, but the application requires the use of an existing MySQL database set, the data is spread over around 10 different databases all accessed from the same connection.
In the current non Laravel applications that is used in the databases are simply access via the same connection using queries such as:
SELECT a.col1, a.col2, b.col1, b.col2 FROM database1.tablea as a INNER JOIN database2.tableb as b on a.id = b.aid WHERE a.id = 1;
I am aware I can do this if I were to write raw SQL, but ideally I would want to use Eloquent and I am unsure as the best way to go about this in Laravel.
Reading the documentation I can see that I can set up multiple connections in the config file for the database and then in each Model set the connection using $connection such as below. Is this the best and correct way to work with multiple databases or is their a better way to do this if the databases can all be accessed from the same connection?
class Flight extends Model
{
/**
* The connection name for the model.
*
* @var string
*/
protected $connection = 'connection-name';
}
Level 20
I have been doing some testing and believe I have answered my initial question.
I have one database connection set up for my app and the DB_DATABASE is set to database1. In my App I have 10+ databases where data is stored.
Including the database name on the table name in the Model allows me to successfully use Eloquent with the multiple databases using the same database connection.
Below is an example of the model set up, with two Models for different tables in different database with a join.
class TableA extends Model
{
protected $table = 'database1.table_a';
protected $primaryKey = "aId";
public function info() {
return $this->hasMany('App\TableA', 'aId', 'aId');
}
}
class TableB extends Model
{
protected $table = 'database2.table_b';
protected $primaryKey = "bId";
}
Then in my controller I use the following which successfully gives me the first record from database2.table_b where the aId is equal to 123.
$info = TableA::find(123)->info()->first();
The above is return the results that I am looking for, my question is now is this the correct way to be doing this, or is there a better way?
For my migrations I have done the following and this is also allowing me to use one database connection that as the DB_DATABASE set to database1 and still add/edit/delete tables in the other databases by adding the database name to the table name.
public function up()
{
Schema::create('database3.table_c', function (Blueprint $table) {
$table->increments('id');
$table->string('name');
$table->timestamps();
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::drop('database3.table_c');
}
Again my question is now is this the correct way to be doing this, or is there a better way more correct way?
2 likes
Level 1
One thing I've noticed is you're naming your models as tables. From a semantic point of view this is incorrect - Models represent data objects once returned and should be named as such.
Glad you were able to figure it out though.
Level 5
A couple of questions on this:
- Were you able to later find a better way to do this? Not that I can see a reason why this is not a better way.
- How did laravel know to use the credentials of database2 when seeing it in the query? Can you help me understand what had to be done to get this working?
Level 20
@qwertynik This was pretty similar to the final approach I went with. I have been successfully using it across multiple applications now for several years. I did run in to a few issues with phpunit and a few other places, so had to make a few changes. My final approach was this:
In my config/database.php file I have connections set up for each of the databases, in addition to the default mysql connection which is used with phpunit.
'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' => 'InnoDB ROW_FORMAT=DYNAMIC',
'options' => extension_loaded('pdo_mysql') ? array_filter([
PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
]) : [],
],
'mysql_connection_a' => [
'driver' => 'mysql',
'url' => env('DATABASE_URL'),
'host' => env('DB_HOST', '127.0.0.1'),
'port' => env('DB_PORT', '3306'),
'database' => env('DB_DATABASE_A', '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' => 'InnoDB ROW_FORMAT=DYNAMIC',
'options' => extension_loaded('pdo_mysql') ? array_filter([
PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
]) : [],
],
'mysql_connection_b' => [
'driver' => 'mysql',
'url' => env('DATABASE_URL'),
'host' => env('DB_HOST', '127.0.0.1'),
'port' => env('DB_PORT', '3306'),
'database' => env('DB_DATABASE_B', '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' => 'InnoDB ROW_FORMAT=DYNAMIC',
'options' => extension_loaded('pdo_mysql') ? array_filter([
PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
]) : [],
],
'mysql_connection_c' => [
'driver' => 'mysql',
'url' => env('DATABASE_URL'),
'host' => env('DB_HOST', '127.0.0.1'),
'port' => env('DB_PORT', '3306'),
'database' => env('DB_DATABASE_C', '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' => 'InnoDB ROW_FORMAT=DYNAMIC',
'options' => extension_loaded('pdo_mysql') ? array_filter([
PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
]) : [],
],
I then create a trait for each of my connections to set the connection and include them in the relevant models. e.g. if the User model needed to use mysql_connection_a I would use ConnectionATrait in the model
use App\Traits\ConnectionATrait;
class User extends Authenticatable
{
use Notifiable, ConnectionATrait;
...
The trait would then look like this.
trait ConnectionATrait
{
public function __construct(array $attributes = [])
{
parent::__construct($attributes);
$this->connection = getConnectionName('mysql_connection_a');
$this->table = getDatabaseName('mysql_connection_a') . $this->getTable();
}
}
I then have a helpers.php that has a couple of helper functions that I needed when doing more complex queries that required joins etc, and deal with the phpunit issues. The helpers.php file looks like.
function getDatabaseName($database, $includePeriod = true)
{
if (empty(config('database.connections.' . $database . '.database'))) {
new Exception('no database connection for' . $database);
}
if ($includePeriod === false) {
return config('database.connections.' . $database . '.database');
}
return config('database.connections.' . $database . '.database') . '.';
}
function getConnectionName($database)
{
return app()->environment(['testing']) ? 'mysql' : $database;
}
In your migrations you need to specify the connection.
class CreateUserTable extends Migration
{
public function up()
{
Schema::connection('mysql_connection_a')->create('users', function (Blueprint $table) {
$table->increments('id');
....
});
}
If you need to add joins to Query builder queries you would do the follow
User::join(getDatabaseName('mysql_connection_b') . 'table_name AS alias', getDatabaseName('mysql_connection_a').'users.id'), '=', 'alias.user_id')
or
User::from(getDatabaseName('mysql_connection_a').'users AS u')
->join(getDatabaseName('mysql_connection_b') . 'table_name AS alias', 'u.id', '=', 'alias.user_id')
or
DB::table(getDatabaseName('mysql_connection_a') . 'users as u')
->join(getDatabaseName('mysql_connection_b') . 'table_name AS alias', 'u.id', '=', 'alias.user_id')
My .env file has the following:
DB_CONNECTION=mysql_connection_a
DB_DATABASE="database_name_a"
DB_DATABASE_A="database_name_a"
DB_DATABASE_B="database_name_b"
DB_DATABASE_C="database_name_c"
Then my phpunit.xml looks like this. I have to use mysql for testing, so need to create _testing versions of each database
<server name="DB_CONNECTION" value="mysql"/>
<server name="DB_DATABASE" value="database_name_a_testing"/>
<server name="DB_DATABASE_A" value="database_name_a_testing"/>
<server name="DB_DATABASE_B" value="database_name_b_testing"/>
<server name="DB_DATABASE_C" value="database_name_c_testing"/>
In the TestCase.php I had to include the following with each connection to ensure all were being cleared after tests.
protected $connectionsToReset = ['mysql_connection_a','mysql_connection_b', 'mysql_connection_c',];
I needed to override the RefreshDatabase trait, so have a file RefreshDatabase.php that contains the below:
namespace Tests\Traits;
use Illuminate\Foundation\Testing\RefreshDatabaseState;
use Illuminate\Foundation\Testing\RefreshDatabase as FrameworkRefreshDatabase;
trait RefreshDatabase
{
use FrameworkRefreshDatabase {
refreshTestDatabase as frameworkRefreshTestDatabase;
}
/**
* Refresh a conventional test database.
*
* This is a drop-in replacement for Laravel's `RefreshDatabase` testing trait. Do not use them together.
*
* We are having migrations targeting two different database connections. The default one "mysql" and another one
* named "addresses". The `migrate:fresh` command used in the original trait, only drops the tables the default
* connection. The `--database` option has no use here as it will cause all migrations run against that connection.
*
* To workaround the issue, this method calls drops the tables in additionally specified connections which should be
* stored in test class's `$connectionsToReset` property. Just as Laravel's `$connectionsToTransact` works.
*
* More information:
* https://github.com/laravel/framework/issues/21063
*
* @return void
*/
protected function refreshTestDatabase()
{
if (! RefreshDatabaseState::$migrated) {
$database = $this->app->make('db');
foreach ($this->connectionsToReset() as $connection) {
$database
->connection($connection)
->getSchemaBuilder()
->dropAllTables();
}
}
$this->frameworkRefreshTestDatabase();
}
/**
* The database connections that should be reset before running `migrate:fresh` command.
*
* @return array
*/
protected function connectionsToReset(): array
{
return $this->connectionsToReset ?? [];
}
}
Then in your test class files replace the standard RefreshDatabase trait with
use Tests\Traits\RefreshDatabase;
I pretty sure that covers all of the set up I had to do. There may be better ways to do some of this now in the more recent versions as I haven't really touched it for a few years. However, it is currently successfully working with that latest version of Laravel.
Let me know if you have any questions on anything, or if you run in to any issues. I am more than happy to help where I can.
7 likes
Level 1
@coxy121 Elegant! Looks like a promising approach for adapting to use with legacy Oracle databases where many of the table names in each schema are the same but the schema prefixes are all unique.
Level 5
@coxy121 Thanks for your detailed response. Your generosity is worth appreciation. After thoroughly reading your response. I will get back here if there are further questions
1 like
Please or to participate in this conversation.