A while ago, I answered on this SO question about using 2 database connections.In answering that, I did some investigation which I'll write up here.
Here is how to run more than one database connection.
This article is about using databases with different data, not necessary for load balancing (or connection pooling) between databases.
Define Connections
Inside of your datbase configuration file - likely app/config/database.php - you can define more than one database connection of any type. In fact, you can define as many connections as you'd like. For instance, if your application has to pull data from 2 MySQL databases, you can define them both separately:
<?php
return array(
'default' => 'mysql',
'connections' => array(
# Our primary database connection
'mysql' => array(
'driver' => 'mysql',
'host' => 'host1',
'database' => 'database1',
'username' => 'user1',
'password' => 'pass1'
'charset' => 'utf8',
'collation' => 'utf8_unicode_ci',
'prefix' => '',
),
# Our secondary database connection
'mysql2' => array(
'driver' => 'mysql',
'host' => 'host2',
'database' => 'database2',
'username' => 'user2',
'password' => 'pass2'
'charset' => 'utf8',
'collation' => 'utf8_unicode_ci',
'prefix' => '',
),
),
);
We have our default connection still set to mysql. This means that, unless we specify otherwise, the application will use thet mysql connection.
Specify Connection
Now that we have a 2nd database connection setup - how do we use it in code?
It turns out there's a few ways!
Schema
Within the Schema Builder, you can use the Schema facade with any connection. To specify which connection to use, simply run the connection() method:
Schema::connection('mysql2')->create('some_table', function($table)
{
$table->increments('id'):
});
Query
Similar to Schema Builder, you can define a connection on the Query Builder:
$users = DB::connection('mysql2')->select(...);
Eloquent
You can also define which connection to use in your Eloquent models as well!
One way is to set the $connection variable in your model:
<?php
class SomeModel extends Eloquent {
protected $connection = 'mysql2';
}
You can also define the connection at runtime via the setConnection method.
<?php
class SomeController extends BaseController {
public function someMethod()
{
$someModel = new SomeModel;
$someModel->setConnection('mysql2');
$something = $someModel->find(1);
return $something;
}
}
Be careful about attempting to build relationships with tables across databases! It is possible to do, but it can come with some caveats and depends on what database and/or database settings you have.
来自 http://fideloper.com/laravel-multiple-database-connections
之前，我回答了关于使用2个数据库连接的这个问题。
在回答时，我做了一些调查，我会在这里写。
以下是如何运行多个数据库连接。
本文是关于使用具有不同数据的数据库，而不是数据库之间的负载平衡（或连接池）所必需的。
定义连接
在datbase配置文件的内部 - 可能app/config/database.php- 您可以定义任何类型的多个数据库连接。事实上，您可以根据需要定义多个连接。例如，如果您的应用程序必须从2个MySQL数据库中提取数据，则可以单独定义它们：
<?php
return array(
'default' => 'mysql',
'connections' => array(
# Our primary database connection
'mysql' => array(
'driver' => 'mysql',
'host' => 'host1',
'database' => 'database1',
'username' => 'user1',
'password' => 'pass1'
'charset' => 'utf8',
'collation' => 'utf8_unicode_ci',
'prefix' => '',
),
# Our secondary database connection
'mysql2' => array(
'driver' => 'mysql',
'host' => 'host2',
'database' => 'database2',
'username' => 'user2',
'password' => 'pass2'
'charset' => 'utf8',
'collation' => 'utf8_unicode_ci',
'prefix' => '',
),
),
);
我们的默认连接仍然设置为mysql。这意味着除非另有说明，否则应用程序将使用该mysql连接。
指定连接
现在我们有第二个数据库连接设置 - 我们如何在代码中使用它？
原来有几种方法！
架构
在Schema Builder中，您可以使用架构外观与任何连接。要指定要使用的连接，只需运行connection()方法：
Schema::connection('mysql2')->create('some_table', function($table)
{
$table->increments('id'):
});
询问
与Schema Builder类似，您可以在查询生成器上定义一个连接：
$users = DB::connection('mysql2')->select(...);
雄辩
您还可以定义在您的雄辩模型中使用的连接！
一种方法是在你的模型中设置$ connection变量：
<?php
class SomeModel extends Eloquent {
protected $connection = 'mysql2';
}
您还可以通过setConnection方法在运行时定义连接。
<?php
class SomeController extends BaseController {
public function someMethod()
{
$someModel = new SomeModel;
$someModel->setConnection('mysql2');
$something = $someModel->find(1);
return $something;
}
}
小心尝试与数据库之间的表建立关系！这是可以做的，但它可以附带一些注意事项，并取决于您拥有的数据库和/或数据库设置。
来自 http://fideloper.com/laravel-multiple-database-connections
This is a great simple tip. I'd love to see some examples of building relationships across databases. In my mind, I can't begin to fathom how hard that would be.
At that point you're really just running into the CAP theorem (you can have two of consistency, availability, or partition tolerance).
To do what you're talking about, you'd start the transaction on each involved database (while having the appropriate isolation level set), run the involved queries across the DB's, then either commit or roll back the entire set together (on the application side). This can get pretty expensive though - especially if you have long-running queries/transactions. This approach falls towards C, but away from availability and partition tolerance.
The more common method is to split varying concerns into services or microservices (microservice here meaning that you're still in the same application runtime but each microservice is the source of truth for its domain of knowledge, having distinct database connections/etc). You drop consistency but keep atomicity/isolation/durability.
I don't think you can"do ACID" across separate databases, so you'd have to do this with extreme care, and preferably rarely :P (Skipping over details, you can't get good transaction support when connected to 2 different databases and passing data back and forth).
In other words, don't build relationships across databases if you can help it. Preferably use a database for one thing, and another for another thing, but don't mix the two if you can help it.
:) Same here. I can't imagine how hard would be to debug an error if one appears in Cross DB connections :D
Personally I thing that is why people use curl connections to communicate across servers/databases instead of direct DB connection :)
protected $connection = 'mysql2';
Not working! It keeps connecting to the default database.
Any thoughts?
Not sure, things could have changed in the 11 months since this was written. Try using the on() method:
User::on('some_connection')->where(/* ... */)->get()
Did you remove the local data from the env file?
Hi. I saw you address a different, but related, question on SO - it was something like 'how would you switch between an indefinite number of databases' which, by definition, cannot be predefined. And you questioned why someone would want to do such a thing. Yet, I find myself asking the same question because I want to implement a multi-tenant app and I don't want to rely on my app code to separate data by tenant_id - for all talk of good testing practice I am just not certain it is robust enough. Instead, I'd rather get the database to do the filtering before it gets to my app using a MySQL login (like this http://goo.gl/odwChA), or by switching from MySQL to Postgres and using multiple schema, or by sticking with MySQL and using prefixes (this last one is not so ideal). Now, none of these ideas require multiple databases, but they do require different attributes for the one database - either user login / password or schema or prefix change, so I believe the principle is largely the same. And while something like Config::set is available, I am not certain it persists long enough to be of use in this case. Do you have any thoughts on this topic?
What I am doing at the moment is having a LDAP-like server, which holds the users and their authorisation stuff. So an logged in user object will have it's database cluster host and dbname for the connection string. Then a logged in user will have it's context set like here http://culttt.com/2014/03/3....
Interesting! I'd be worried about "scaling" with mysql views (I guess...depends on your app) but that's something I haven't come across as a strategy. Neat.
Yeah ... except I can't make it work because I can't change the database user name in my Laravel app, just prior to the user logging in, and maintain that changed login for the session. Hence ... my ... well, confusion. I have posted my question on Laracasts as well, so don't surprised if you see it there. Do views slow down the return of results? Or is it s different type of scaling issue you refer to?
I don't have enough DBA knowledge to answer that unfortunately :/
Managing connections dynamically Laravel is a bit hard, since it mostly expects them to be set ahead of time.
One thing you can do is use Capsule to create a connection with a specific mysql username/password, however that might conflict with the main connection if set (so maybe that's a bad idea).
Otherwise, if you want to update the array of database connection information on the fly, you need to **replace** (instead of simply update) the $app['config']['database.connections'] array:
$connections = $app['config']['database.connections'];
$connections['new-connection] = array( 'driver' => 'mysql', 'username' => 'my_user' ... );
$app['config']['database.connections'] = $connections;
Hi. It's been a long time between drinks, but all roads lead back to this article!
My preferred option to implement multi-tenancy now is to use Postgres schema. So, as per your article - which is still super relevant - I have set up two connections (one for tenant and one for public, or common, tables) and I use before filters to dynamically set the pgsql connection schema to whatever the subdomain of the current url is. And that seems to be going just fine.
Then there is the quote at the end of your article 'Be careful about attempting to build relationships with tables across databases! It is possible to do, but it can come with some caveats ...'
Now, I am not using databases. Rather, I am using schema. Still, this quote turns out to be quite profound. And, for me, problematic.
I have a role_user table that I use to map users to one or many roles. Users and roles - as you'd expect - sit in the tenant schema. But roles are common amongst tenants so the roles table sits in the public schema. Surprisingly, Laravel expects the pivot table (in this case, role_user) to sit in the same schema as the role table and not the user table. FYI, I have made my default connection the 'tenant' connection (and this is where I reckon the pivot table connection *should* default to). I tried to fudge it by creating a RoleUser model and setting that connection to my tenant connection. Nope ... that didn't work either (not surprising - this was a Hail Mary).
The reason, then, that I've posted this off to you is to ask how my recent experience aligns with what you have seen and to see if I have any options (well, aside from moving the role model to the tenant schema).
Thanks for reading.
hi . I like your reply about the multi-database connection because I am building multi-tanat application too . and I want to know why did you preferred to use postgresql instead of mysql to build multi-tenant application ?
and why you selected multi-database instead of table prefixes while its very easy to implement ?
I think I understand. Thanks for that. I'll give it a crack and let you know how it pans out. Thanks again.
Hi. I had a fiddle with this and looked at what I am seeing others doing - say, Rails guys ... they apply the equivalent of Config::set() to a filter, setting the tenant-specific database details before the page loads and setting it back after it loads - and I have played with this and it kind of works. I just can't work out to to pass a variable - in this case the user login and password - to the filter.
Hmph. If you have any ideas, let me know!
I'm almost tempted to cheat and use MySQL prefixes to emulate separate schema and use something like ...
Route::getCurrentRoute()->getParameter('subdomain'))
... to generate the prefix for me based on the subdomain. I have been testing out a similar, but more robust, idea using PGSQL and multiple schema ... but I am seeing bugs that I didn't see using MySQL, like not encapsulating strings in quotes leading to errors (e.g. on hashed passwords). Weird.
Hi, I realize this comment is from 2 years ago, but I'm curious what route you ended up going with? I have a similar situation, trying to figure out which route to take.
I've done a couple of multi-tenant apps now and I've gone with the single database, single schema, tenant_id in every table approach. Writing the code is pretty painful - Rails seems support multi-tenant better than Laravel (at least when I looked at it back then) - and testing becomes even more important because you don't want one tenant's data leaking into another tenant's view. But things just work. And maintenance, like migrations, is a lot easier without separate schemas or databases. I won't be able to describe the nuances in any useful way, though - I moved to Django. I miss the Laravel community and I miss Jeff's videos, but I don't miss the '... that should be in a command ... that should be a service ... that should use an interface ...' :)
Yeah, the more I think about it, going the "multi" route does seem like a maintenance nightmare. I'm still getting adjusted to Laravel, but perhaps a lower level method (whether custom or existing) to add a "tenant_id" value automatically to every applicable query would save the day. Thank you for you response!
then how about to call data (eg email) from ldap server to display into my system? For example I have a db employee which stores name data, username and password. Then I also have ldap server that stores data name, username, password, position. Then when i login, i will be authenticated in db employee and ldap server to check my username exists or not. Then, after I successfully login, then I will be able to see the data name, email, and position taken from ldap server which is displayed into my system web.
Great article, simple to read and to understand, you've helped me with some project i'm having right now, thanks!
So simple and so userfull. Thanks for the explanation.
Schema::connection('mysql2')->create()
exception: database [mysql2] not configured
something's missing in your tutorial I think
Could be, it's 3 years old now.
However that exception sounds like your config/database.php file needs a `mysql2` connection configured. Usually I copy/paste the "mysql" connection and rename it to "mysql2" or something like that:
https://github.com/laravel/...
Excuse me, I had to config:clear, because it wasn't a fresh installation and the configs have been sort of cached and even cache:clear didn't work, I needed to do a config:clear to clear all past configurations.
Thanks man.
Already did that. I'm using laravel 5.2 maybe it doesn't take same connection method or settings.
i have done the necessary configuration but it looks like it is still connecting to the default database
so how to do i write select username and password in this case DB::connection('mysql2')->select(...);
Please help me out, to set route in order to establish database connection in laravel php.
does it affect the .env file?
What if I'm using more than one databases for the same application and I want to migrate only the second database connection?
do we need in Model: protected $table = 'table_name'; ???
I believe if that's not present then the model guesses the table name based on the lowercase version of the model class name.
But my example was definitely shortened - you'll need/want to add in a bunch of other things to a real model.
How can i define eloquent relationship between three tables of same database... i have done CRUD with two
tables by eloquent.. now how the third table can b handled ?..
i have three tables right now
=> brands Table
id | name
=> years Table looks like with foreign key
id | yearName | brand_id
=> modals table looks like with foreign key
id | modelName | brand_id | year_id
Just want to say thank you for telling so many ways to connect to different databases.
Thank you so much!!! Your tips worked here!
Great tip..Thank you so much.
how to connect a search using laravel??
does this still apply with laravel 5?
Great. And thanks for sharing. Just getting hands dirty :) .
Great!!!
I need suggestion.
I have multiple apps and its database.
Can i use common database for user table?
thanks a lot, your tutorial save my life.... really save my life ...
I am a new Indonesian laravel learner...
Thanks! This help me a lot.
Great tutorial! I just want to say one thing that I have learned is that Validation doesn't respond well to multiple database connections. If you try to Validate a field that would require a database connection (ie. unique) and that database is on the secondary connection, the validation is going to fail.
来自 http://fideloper.com/laravel-multiple-database-connections
