Hello there. The process to accomplish this can get quite involved. I don't have the time right now to write all the steps up in extreme detail, but I can at least give you some of my thoughts and some clues as to where to start looking. I am also going to make a few assumptions:

• The database user for your application has the appropriate permissions to create databases and assign privileges;
• That all new user accounts are going to be using the same database tables;
• That you will be using migrations to create the new database tables for the user accounts.

Some introduction

Okay. So here we go. There are a few things that you are going to need to figure out at first. The first thing is how are you going to be naming these databases that are created on the fly? Then, how will you associate these user accounts with these databases? The method I've used in the past is to create an accounts table, which mainly just holds the account name:

| id | account_name |
|----|--------------|
| 1  | the first account |

Then I would create a function that converts the ID from the accounts table into a predictable database name. A quick solution would be to just MD5 the ID, but you may run into issues with the possibility of the first character being numeric with some DBMS systems. Getting around this is just a simple str_replace for all numeric characters. The only thing is be consistent.

Let's just say that 1 is mapped to the database name cczadqdkmfeokkioaackazcoobffmkaz from our formatting function. We can create this database using Laravel's DB::raw().

NOTE You will have to take care of sanitizing and escaping all queries that you pass into DB::raw().

Creating a Database

Creating a database on the fly is actually fairly simple using Laravel. Just look up the syntax for your particular DBMS and pass the correct query through DB::raw() (assuming your applications database user has the correct permissions).

Creating a database might look like this:

/**
 * Creates a new database schema.

 * @param  string $schemaName The new schema name.
 * @return bool
 */
function createSchema($schemaName)
{
    // We will use the `statement` method from the connection class so that
    // we have access to parameter binding.
    return DB::getConnection()->statement('CREATE DATABASE :schema', array('schema' => $schemaName));
}

// Now we can create a MySQL Database
createSchema('cczadqdkmfeokkioaackazcoobffmkaz');

The above code example would then create a new database named cczadqdkmfeokkioaackazcoobffmkaz. Applying permissions would be done in a similar way, so I won't bother with showing that.

Creating the Database Tables

Again, I am assuming that the tables you want to create are already stored in migrations. This is easier to do if the tables the user will actually use are stored in their own separate folder (for example they might be stored in app/database/tenants). We can re-use Laravel's migrator to accomplish this for us.

There is a way to do this where we create our own migrator, migration repository, etc. But let's not get into that. We can simply do this:

Artisan::call('migrate', array('database' => $databaseConnection, 'path' => 'app/database/tenants'));

So there, we simply use Artisan to run the migrations on our new database connection. This function call would probably be within the account repository, where you actually create the user account. The only thing I havn't covered yet is how to get the $databaseConnection variable.

Creating the Connection

Since we are creating these databases on the fly, they are probably not configured in our applications app/config/database.php file (it would be a nightmare to maintain that list with dynamic databases). Because of this, we need to find a way to also configure the connection on the fly. There are a few different ways to do this, I'll just go with the easiest, and the steps are:

• Get the default connection information from app/config/database.php;
• Overwrite the values we want to change (our database name, mainly)

A simple function for this might look like this:

/**
 * Configures a tenant's database connection.

 * @param  string $tenantName The database name.
 * @return void
 */
function configureConnectionByName($tenantName)
{
    // Just get access to the config. 
    $config = App::make('config');

    // Will contain the array of connections that appear in our database config file.
    $connections = $config->get('database.connections');

    // This line pulls out the default connection by key (by default it's `mysql`)
    $defaultConnection = $connections[$config->get('database.default')];

    // Now we simply copy the default connection information to our new connection.
    $newConnection = $defaultConnection;
    // Override the database name.
    $newConnection['database'] = $tenantName;

    // This will add our new connection to the run-time configuration for the duration of the request.
    App::make('config')->set('database.connections.'.$tenantName, $newConnection);

}

Essentially what that does is create a new run-time database configuration for our database name (cczadqdkmfeokkioaackazcoobffmkaz). This will also let us re-use the permissions from whatever user is set inside app/config/database.php. This needs to be called before any query is made to the database.

This can usually be done using a filter, or just at the top of the routes.php file (if that's easier). Let's pretend we get the users account ID from Auth::user()->account_id and our magical formatting function is named getTenantName(), we could do something like this:

// In app/routes.php
configureConnectionByName(getTenantName(Auth::user()->account_id));

That way the database connection would be available to us when we need it. This leads us to another problem.

Eloquent Models, Query Builder and Dynamic Connections

When we create dynamic connections like this, we need to tell Eloquent or the Query Builder about our new connection. We simply cannot override the default connection, because I'm assuming there is data in that database that will need to be used on every request (think user names, profile settings, etc). So now we need to have a way of accessing data from two or more databases, while still taking advantage of Eloquent and the Query Builder.

Ideally, we would want to do something like this:

// This would typically use a central database. Lets call it DB1
$user = User::find(1);

// If we were building a shopping app, this should come from the dynamic DB2.
$products = Product:all();

Eloquent exposes a setConnection() method (the DatabaseManager exposes a connection() method) that will allow us to override the connection they use at run time. The easiest approach is to create a new base model to use for all models that should access the user's account database and tables:

use Illuminate\Database\Eloquent\Model;

class BaseTenantModel extends Model {

    public function __construct(array $attributes = array())
    {
        parent::__construct($attributes);

        // Set the database connection name.
        $this->setConnection(configureConnectionByName(getTenantName(Auth::user()->tenant_id)));
    }

}

Then we could create a Product model like this:

class Product extends BaseTenantModel {

    protected $table = 'products';

}

Now, the Product model would use whatever database was dynamically configured using configureConnectionByName().

NOTE You would probably want to wrap all the getTenantName and configureConnectionByName functions in a nice wrapper class.

Using the query builder utilizes the same technique (more or less):

DB::connection(configureConnectionByName(getTenantName(Auth::user()->tenant_id)))->table('products')->insert(['name' => 'New shoes']);

A quick note on validation

One of the problems you may run into if you want to do this is with validation. By default, the validator will use the default connection. So we need to override this too.

This can be done by doing this

Validator::getPresenceVerifier()->setConnection(configureConnectionByName(getTenantName(Auth::user()->tenant_id)));

before doing any validation. This is important to do if you are using rules like exists or unique.

Some final words (most important part here)

Doing this will take some time, and some planning. If you can think of any other way to accomplish your needs (such as setting an account_id in your tables, instead of creating new databases) I would definately recommend doing that instead.

There are a few things to consider with this approach. Creating the multiple database setup is more secure for your customers data, but it is really a maintenance nightmare waiting to explode down the road (especially with schema changes) if it is done wrong. Creating a shared database system where users are just restricited from certain records is easier to build, and more maintainable (but it is then up to the application layer to restrict access to the non-account data).

This technique is called multi-tenancy, and there are some good reads on it around the Internet:

http://msdn.microsoft.com/en-us/library/aa479086.aspx http://www.computerworld.com/article/2517005/data-center/multi-tenancy-in-the-cloud--why-it-matters.html https://developer.salesforce.com/page/Multi_Tenant_Architecture https://www.google.com/?q=php+multi+tenant#q=php+multi+tenant

You will also want to be comfortable with these classes within the core (assuming v4.2):

https://github.com/laravel/framework/blob/4.2/src/Illuminate/Database/Eloquent/Model.php https://github.com/laravel/framework/blob/4.2/src/Illuminate/Database/Connection.php https://github.com/laravel/framework/blob/4.2/src/Illuminate/Database/Query/Builder.php

There is a lot that can go wrong using this approach. So, again, if you can think of another way to do it, I'd recommend that instead. But it is possible. If anyone else has some comments on a nicer way to accomplish this, please let me know.

Quick comment - Are you sure that a new database PER USER is the right way to go? If this is well thought out, then fine, but it reminds me of some kids I was helping a few weeks back who used a new DB per user because they didn't understand relational database design and how to organize the data.

Not knowing your requirements, of course we can't say - nor is it for us to Judge, but it sounds like you might be able to accomplish what you want much easier by having a single users table, and adding a user ID field to your tables so that you can pull data related to those users easily.

basementjack said:

Quick comment - Are you sure that a new database PER USER is the right way to go? If this is well thought out, then fine, but it reminds me of some kids I was helping a few weeks back who used a new DB per user because they didn't understand relational database design and how to organize the data.

Not knowing your requirements, of course we can't say - nor is it for us to Judge, but it sounds like you might be able to accomplish what you want much easier by having a single users table, and adding a user ID field to your tables so that you can pull data related to those users easily.

Completely agree. These things can get muddy if done incorrectly.

Hey Johnathon, Thanks for your excellent mini-guide. I've got an idea for another approach and I'd like some feedback from the community. I've been testing this on my local environment and so far it seems to be working fine, but it's early and maybe I'm missing something.

My connection connects to the main database (MainDB). And I keep the User data here. I moved all the Tenant data to different databases (DB_1,DB_2,DB_n) where n represents the user.id .

Next, I have my Models all reference their table based on a dynamically created, long form name. So where you might normally have Orders set to orders, I have it set to DB_n.orders. I do this by having it set the table name in the Model Constructor like this:

class Order extends Eloquent{
	
	protected $table;

	public function __construct($attributes = [])
	{
        parent::__construct($attributes); // Important to add this, or you'll never be able to insert values

		$this->table = 'DB_' . Auth::id() . '.orders';
	}
	...
}

(obviously the model will need to be behind an Auth filter, and I do some checks to ensure a valid value, but this shows the general idea). So far I haven't had any problems. I know that one limitation to this approach is that all the databases need to be on the same server/connection. And if one's goal is to have really separate connections, then Johnathon's approach is probably the way to go.

For me, I'm using the database almost as a namespace. I don't want to go the route of having a user_id in every record of every table, and I don't want to have a ton of tables (orders_1,orders_2,orders_n) in one database which is why I want the separate database approach.

Besides secondary issues (backups, etc) do you see anything inherently flawed or problematic with this approach as it relates to Laravel?

##Update: Hm, just started working on the migrations (For testing, I had manually created, moved the tables). I didn't initially realize that the Artisan::call('migrate', []); function was looking for a Database connection not simply a name. With this in mind, I'll have to either implementmore of Johnathon's solution (ie. the dynamic connection builder) or see about modifying the migrator (doubtful)

So now my question changes slightly: Which would perform better - a model that was loaded via a different, dynamic connection? Or a model that was loaded via the long form of it's name (ie. db_1.orders).

Three years later... I'm posting this just in case someone might benefit.

I've approached this in a completely different way than the above posters. In my setup I configure all Eloquent models to use the "tenant" connection which I create on the fly using the above mentioned methods based on the user logging in. The only caveat with this method is that YOU CAN'T HAVE a 'tenant' config setup in database.php otherwise this will not break.

I have a 'system' connection to my central DB, and a 'tenant' connection to the currently connected tenant db.

I think this keeps the model classes much cleaner.

Hi every one, Hey @Flack Needless, Can you show us how to configure base on your approach. Because, i'm still confuse how to connecting user to tenant database.

thank you

Hi, i recently reached the same problem, i used the function above and some of my tricks, ind worked for me.

Create a class with the method above

<?php

namespace App\pacotes;


use Illuminate\Support\Facades\App;
class MultiConnector
{
    //

    public static function createDatabaseConnection($companyname){
        // Just get access to the config. 
        $config = App::make('config');
        // dump($config);

        // Will contain the array of connections that appear in our database config file.
        $connections = $config->get('database.connections');
        // dump($connections);
        // This line pulls out the default connection by key (by default it's `mysql`)
        $defaultConnection = $connections[$config->get('database.default')];
        // dump($defaultConnection);

        // Now we simply copy the default connection information to our new connection.
        $newConnection = $defaultConnection;
        // dump($newConnection);
        // Override the database name.
        $newConnection['database'] = $companyname;
        // dump($newConnection);
        // dd('enaouth');

        // This will add our new connection to the run-time configuration for the duration of the request.
        App::make('config')->set('database.connections.'.$companyname, $newConnection);
        // dd(App::make('config')->get('database.connections'));
    }
}

after that create

<?php

namespace App\pacotes;
use App\pacotes\MultiConnector;
use Illuminate\Support\Facades\Auth;
use Illuminate\Database\Eloquent\Model;

class BaseModel extends Model
{
    //

    
    public function __construct(){
        
        MultiConnector::createDatabaseConnection(Auth::user()->storage);
        $this->setConnection(Auth::user()->storage);
    }
}

and then your model should extend BaseModel

In my case

<?php

namespace App\models;

use App\pacotes\MultiConnector;
use App\pacotes\BaseModel;
use Illuminate\Support\Facades\Auth;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\SoftDeletes;


class Loja extends BaseModel
{
    use SoftDeletes;
    protected $dates        = ['deleted_at'];
    protected $table        = 'loja';
    protected $primaryKey   = "idloja";


    
}

@Nur Gunawan the simplest way you can do this is like this:

    public function setConfigWithConnection($connection)
    {
        Config::set('database.connections.tenant.driver', 'mysql');
        Config::set('database.connections.tenant.charset', 'utf8mb4');
        Config::set('database.connections.tenant.collation', 'utf8mb4_unicode_ci');
        Config::set('database.connections.tenant.timezone', '+00:00');
        Config::set('database.connections.tenant.host', $connection['host']);
        Config::set('database.connections.tenant.username', $connection['username']);
        Config::set('database.connections.tenant.password', $connection['password']);
        Config::set('database.connections.tenant.database', $connection['dbname']);
        Config::set('database.connections.tenant.port', $connection['port']);
        Config::set('database.connections.tenant.strict', false);
        Config::set('database.default', 'tenant');
    }

Just have your models use the tenant connection and you are all set.