How do I connect to different databases at run time?

Question

I am making a multi-tenant multi-database app that has one central database and many sub-databases.

The app creates an instance of an organisation in the central database, and to each organisation it creates a sub-database with different tables.

To achieve this, I have made a class class Setup that

1. Creates the Organisation
2. Creates its Database
3. Configures the connection to that database and connects to it
4. Runs the proper migrations to it.

All wrapped up in a constructor, so upon caling Setup::create all of this runs properly.

Most of the database configuration and connection are inspiried from this tutorial.

To test whether my logic goes as wanted, I interacted with my application via :

1. Tinker
2. Web Browser.

To my suprise, the outcome is different in both cases, and never as wanted as far as connecting to another database is concerned.

Interaction with tinker :

After creating calling my Setup::create and having output telling me everything went okay, I try to check for myself what database am I on right now Using:

DB::connection()->getDatabaseName()

It outputs the sub-database name we have just created for the organisation and connected to, which is logical and going accordingly.

However, I attempt to connect to another database by creating a new configuration for it and then connecting to it with the DB methods I have provided, it does not work, I am still on the sub-database I was on.

---

Interacting with the browser :

This time, having my Setup::create wrapped up properly in my controller's code, I attempt to test everything again, I also made a line in my layout to output me the current database :

<?php echo DB::connection()->getDatabaseName() ?>

At first, while I am still on the central database, its name appears, however after calling Setup::create, it switches to the sub-database -Which is expected- but then, after one refresh, I am on the central database again -Which is totally Unexpected-

So, what happens here? and how do I get to connect to all of my different databases how I wish when I wish?

Extra:

Testing in tinker, I have went to the point where I have commented out the migration code, and left the creation of the database and also the connection to it. To my suprise, it does not connect to the database. so I started thinking that the migration code has something to do with connecting to the database, or maybe tinker has different behaviors I completely ingore.

Important:

• I have came across threads where solutions using QueryBuilders were mentioned
• Please, do not provide such answers because my aim is to switch databases entirely to the point where I can use eloquent model's events with no problem.
  • By that I mean, I want to be able to use Model::create after having connected to the database instead of DB::connection()->....

Technical details:

I am using Laravel 5 with mysql-server, on Ubuntu Machine.

Answer 1

I stumbled upon this question and it had my answer.

I made a class called DatabaseConnection:

class DatabaseConnection extends Model
{

        static $instances=array();

        protected $database;

        protected $connection;

        public function __construct($options = null)
        {
            // Set the database
            $database = $options['database'];
            $this->database = $database;

            // Figure out the driver and get the default configuration for the driver
            $driver  = isset($options['driver']) ? $options['driver'] : Config::get("database.default");
            $default = Config::get("database.connections.$driver");

            // Loop through our default array and update options if we have non-defaults
            foreach($default as $item => $value)
            {
                $default[$item] = isset($options[$item]) ? $options[$item] : $default[$item];
            }

            $capsule = new Capsule;
            $capsule->addConnection($default);
            $capsule->setEventDispatcher(new Dispatcher(new Container));
            $capsule->setAsGlobal();
            $capsule->bootEloquent();

            // Create the connection
            $this->connection = $capsule->getConnection();

            DatabaseConnection::$instances[] = $capsule;
            return $this->connection;
        }
}

So, whenever I am in a controller that manipulates tables of a sub-database, I simply go this way:

public function RandomActionInMyController()
{
      $db_connection = new DatabaseConnection(['database' => 'name_of_db']);
       $someModel = new Model/Model::find()..// Basically anything
        return myreturnstuff;
}

Extra Bonus:

The use of the static attribute $instances in my DatabaseConnection boils down to retrieving my latest database connection for ease uses.

For example, if I ever wanted to retrieve it, it would be wrapped in a function such as

function CurrentOrLatestDbConnection()
{
    if( !empty(DatabaseConnection::$instances) )
    {
        return end(DatabaseConnection::$instances)->getConnection()->getDatabaseName();
    }
}

Notes :

If you encounter errors such as Unknown class 'Container' or Capsule or anything of that kind, make sure you check the question link I have provided, and use use statements properly.

Concerning upcoming answers :

It seems to me that this database connection lives within the the brackets of the controller's action, so when I proceed to another action that specifies no connection, it returns to the central database automatically.

Which has got me thinking that there must be a way to set the database connection to the sub-database in a 'global' way to the whole function, such as a middleware or something.

I would love to see an answer, implementing such thing.

Update :

I came up with a neater way to do it.

I assume you are on the same ground as me, wanting to change databases conditionally in accordance with each controller... say, each of your controllers requires a different database, just for the sake of the argument.

What we will be using to solve this is `Middlewares.

First, to explain what we are about to do..

We are going to check for the name of the controller (and even action) and then set the proper database we wish to set.

1. Go to your command-line , type in:

   php artisan make:middleware SetDatabaseConnectionMiddleware

To create a middleware with ready boilerplate.

Or, if you like it the hard way, go to your app_name/app/Http/Middleware and create one manually.

2. Go to your helper methods file( if you already have one, if not, dude make one!)

    function getControllerAndActionName()
   {
   
   $action = app('request')->route()->getAction();
   
   $controller = class_basename($action['controller']);
   
   list($controller, $action) = explode('@', $controller);
   
   return ['action' => $action, 'controller' => $controller];
   }
   

This will return to you an array with both the action name and controller name, if you want to return restrictidly just the controller's name, feel free to remove 'action' => $action from the code.

3. Inside of your middleware, it will look this way :

    namespace App\Http\Middleware;

    use Closure;
    use DatabaseConnection;

    class SetProperDatabase
    {
    /**
    * Handle an incoming request.
    *
    * @param  \Illuminate\Http\Request  $request
    * @param  \Closure  $next
    * @return mixed
    */
    public function handle($request, Closure $next)
    {
         $database_name = '';
         $controllerAndActionName = getControllerAndActionName();
         $controller_name = $controllerAndActionName['controller'];
         $action_name = $controllerAndActionName['action'];
         if($controller_name == 'my_controller_nameController')
         {

         $database_name = 'your_proper_database_name';
         }
         else
         {
          $database_name = 'other_db';
         }

         $database_connection = new DatabaseConnection(['database' => $database_name']);

          return $next($request);
    }
    }

4.Now, that you have created properly your middleware, let us tell your app where to find it and under what name.

1. Go to your app_name/app/Http/Kernel.php

2. In your $routeMiddleware variable, add this line

   'set_proper_database' => \App\Http\Middleware\SetProperDatabase::class,

This way we know how to call it.

5. Finally, setting it up.

   1. Go to your Controller.php (the Abstract class from which all of your controller's inherit)

   public function __construct() { $this->middleware('set_proper_database'); }

And this should do it for you.

If you have any further questions, please feel free to comment.

// Resources :

1.Controller And Action Name

2.Middleware Documentation

3.Further Middleware Documentation Notes : I'd appreciate some edition concerning my styling and code indenting, since it seems I struggled to style my code properly in here but in vain, the indentions I used had no effeft.