Luke Evers

On The Fly Database Connections With Laravel 5

Posted on 25 March 2015

Background

I've been rolling my own CMS for production sites for the past year in Laravel 4. Each site currently has their own CMS/admin panel backend that does basic CRUD stuff. There's currently about 14 live production websites with this CMS, and there's nothing wrong with the way we're currently doing it, but we can do better.

The idea is to be able to control all of the websites from one central location. Part of the issue is having to create new users (although it's very easy with Laravel to create new users) every time when a good portion of the time, the clients are the same people for multiple sites.

I thought about running an OAUTH server and having each individual CMS authenticate with the central OAUTH server, but that still doesn't solve all of the problems. If a user can login to one website and edit the meta descriptions of all 8 of the websites they have access to without having to login to 8 different websites, at the end of the day that'd make the clients happy.

Another issue that I care about is making sure the CMS is always up to date. If you don't update every single website when you make a change, some of the websites end up having the CMS with the most recent edits from last week, and others end up having the CMS that was updated back in August, 2014.

So I want to centralize the CMS. I started to take the most recent CMS that I started writing in Laravel 5 and give it a few updates to push it in the direction of being a centralized CMS.

Connecting To A Different Database

One of the first issues I came across was figuring out how to connect to these databases quickly ("on the fly"). The first article by Chris Fidao I found explained that (although it was an article from 2 years ago) you can use the Query Builder to specify a connection which will return an instance of Illuminate\Database\Connection:

$conn = DB::connection('connection-name');

And then we could use that connection to run queries:

// Get the users table
$users = $conn->table('users');

// Find the first user
$first = $users->first();

The only problem with this approach is that as of right now I have to currently edit my database configuration file every time I have a new website to add. Then I came across this issue on GitHub that asked how to create dynamic connections instead of specifying 'connection-name' when trying to connect to a new database.

Using Config::set you can create new temporary connections and then use DB::connection with the temporary name you just created.

On The Fly

I created a PHP class to make life much easier so I can call it whenever I need to do some on the fly database operations.

With this class I can easily connect to a new database on the same host:

// Using the same host/passwword/etc, just changing databases
$otf = new App\Database\OTF(['database' => 'kittens']);

// Get the users table
$users = $otf->getTable('users');

// Find the first user in the table
$first_user = $users->first();

Or we can connect to a different type of database entirely (assuming you weren't using pgsql as your default):

$otf = new App\Database\OTF([
    'driver'   => 'pgsql',
    'database' => 'puppies',
    'username' => 'jack',
    'password' => 'the-cute-dog',
]);

App\Database\OTF takes in an array that looks exactly like the arrays in your database configuration file that Laravel defaults with. You need to provide a database, but all other options are optional.

comments powered by Disqus