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?

When connecting to multiple db in laravel by defining the connection in the model as "protected $connection = 'mysql2'", it connects once, then afterwards i always get "Too Many Connections" error. Do I have to disconnect the db after use?

Working with L5 and then found path changes and .env changes. nice stuff here http://tutsnare.com/connect...

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!

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.

nice it is very useful for me to working on multiple databases and connect on Laravel

is it possible to define database according to user role .
like if user admin then connect to admin database,
if user then connecto user database

You can define different connections according to user roles. Each connection defines its different database so you can edit it there.

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.

Simpler method might be to change the config, see here = http://stackoverflow.com/qu...

Thanks!

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.

please help me

how to connect a search using laravel??

does this still apply with laravel 5?

Yep!

Great. And thanks for sharing. Just getting hands dirty :) .

Good. Thank you for your information.

Refer this link to make multiple database connection in PHP programming: http://www.gtechhub.com/dat...