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.
Hillel Coren 