Using multiple databases in Laravel for all queries

As we continue to grow our hosted Invoice Ninja platform we’ve been been looking into both vertical scaling [bigger servers] and horizontal scaling [more servers].

Initially we added more web servers using a load balancer. This was relatively easy to setup and as an added bonus provided higher availability. Over time however we’ve started to see our single database server experience increased memory usage, particularly after releasing our mobile apps.

Our plan was to simply divide our users across multiple databases. One database would function as the lookup server, the other databases would store the actual data.

Searching Google for ‘multiple databases in Laravel’ mainly returns examples of either specifying the connection for the particular query:

DB::connection(...)->select(...)

Or defining it in the model by setting:

protected $connection = ...;

However, if you’d like to use a different connection for all queries you can use:

config(['database.default' => ...]);

For our implementation we created a middleware class to find the database server with the provided credentials. The server is stored in the cache so the lookup is only required once every two hours, this is particularly helpful for the API where there’s no session.

To keep the lookup tables up to date we’re using Eloquent creating and deleted model events. Finally, to ensure our queued jobs use the right database we’re using Queue::before(...) to set it.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: