Multiple DBs: archtechx / tenancy

Before switching to the setup of multiple databases, let's talk about WHY you would want to do that.

I got this question from a few people on Twitter:

A multi-database setup means one database per customer/company/organization. Yes, it means managing 100s of databases if you have 100s of companies using your application.

Sounds overwhelming, doesn't it? But not necessarily.

The most common use case for this is the BUSINESS needs by those companies to store data separately from other companies using your application. Usually for privacy and legal reasons.

For example, if you are Microsoft, you would probably want to minimize the risks that some of your internal data would become visible to Apple or Google?

Also, there may be technical architectural reasons to separate the database, such as potential individual scaling or managing resources for each tenant based on their own requirements.

However, it also comes with additional complexity and more work to set up and manage multiple databases.

Let me try to summarize the pros and cons of this multi-database approach.

Advantages of multiple databases:

1. More security and privacy, sometimes required legally. More extensive protection from data leaks.
2. Ability to export data and make backups separately.
3. Potential performance gains: queries work faster because the database wouldn't need to touch other databases.
4. Ability to separate virtual/physical servers and scale individual tenant resources when needed.
5. If something wrong happens with data, it's easier to roll back or recover the data for one database without touching others.

Disadvantages:

1. Complexity to set up and maintain, especially the DB migrations. Connection names are used everywhere, still with a risk of accidentally connecting to the wrong database.
2. Harder to add new features and deploy changes because you need to execute migrations to multiple databases.
3. Harder/impossible to query data from multiple tenants if grouped reports are needed.

In short, you need a multi-database setup only if it's actually the requirement either from the clients or from yourself as an architectural decision for scalability.

In most cases, for smaller projects, one database is totally fine.

That said, if you still want to dive into that more complex setup, let's get familiar with the approach and the tools.

Installation and Configuration

Let's first create the multi-database and multi-tenancy setup with the package stancl/tenancy. Some of the code from earlier for this package from a single database, like installation, will be repeated, and then we'll move deeper.

The initial setup is the same Laravel Breeze with two CRUDs: Projects and Tasks.

So, let's install the package.

composer require stancl/tenancy
php artisan tenancy:install

bootstrap/providers.php:

return [
    App\Providers\AppServiceProvider::class,
    App\Providers\TenancyServiceProvider::class, 
];

IMPORTANT NOTICE: Version 3 doesn't support the database session driver. The easiest change would be to file as a session driver.

And now, here is the first difference between single and multiple databases. The DB_CONNECTION value should be the main database called central in this package. This database consists of tenants, users, and all the global things.

In my local setup, DB_CONNECTION is set to mysql, and DB_DATABASE is set to tenancy, where global things will be saved.

.env:

// ...
 
DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=tenancy
DB_USERNAME=root
DB_PASSWORD=
 
// ...

The config/tenancy.php has a central database connection setting.

config/tenancy.php:

return [
    // ...
 
    'database' => [
        'central_connection' => env('DB_CONNECTION', 'central'),
 
        // ...
        ],
    ],
 
    // ...
];

Next, let's set aside the use of UUIDs.

config/tenancy.php:

return [
    'tenant_model' => Tenant::class,
    'id_generator' => Stancl\Tenancy\UUIDGenerator::class, 
    'id_generator' => null, 
 
    // ...
];

database/migrations/xxxx_create_tenants_table.php:

Schema::create('tenants', function (Blueprint $table) {
    $table->string('id')->primary(); 
    $table->id(); 
 
    // your custom columns may go here
 
    $table->timestamps();
    $table->json('data')->nullable();
});

database/migrations/xxxx_create_domains_table.php:

Schema::create('domains', function (Blueprint $table) {
    $table->increments('id');
    $table->string('domain', 255)->unique();
    $table->string('tenant_id'); 
    $table->foreignId('tenant_id')->constrained()->cascadeOnUpdate()->cascadeOnDelete(); 
 
    $table->timestamps();
    $table->foreign('tenant_id')->references('id')->on('tenants')->onUpdate('cascade')->onDelete('cascade'); 
});

We need to define our own Tenancy Model.

php artisan make:model Tenant

app/Models/Tenant.php:

namespace App\Models;
 
use Stancl\Tenancy\Database\Models\Tenant as BaseTenant;
use Stancl\Tenancy\Contracts\TenantWithDatabase;
use Stancl\Tenancy\Database\Concerns\HasDatabase;
use Stancl\Tenancy\Database\Concerns\HasDomains;
 
class Tenant extends BaseTenant implements TenantWithDatabase
{
    use HasDatabase, HasDomains;
}

config/tenancy.php:

use Stancl\Tenancy\Database\Models\Domain;
use Stancl\Tenancy\Database\Models\Tenant; 
 
return [
    'tenant_model' => Tenant::class, 
    'tenant_model' => \App\Models\Tenant::class, 
 
    // ...

Next, we transform the routes to have central and tenant routes.

bootstrap/app.php:

use Illuminate\Support\Facades\Route;
use Illuminate\Foundation\Application;
use Illuminate\Foundation\Configuration\Exceptions;
use Illuminate\Foundation\Configuration\Middleware;
 
return Application::configure(basePath: dirname(__DIR__))
    ->withRouting(
        using: function () { 
            $centralDomains = config('tenancy.central_domains');
 
            foreach ($centralDomains as $domain) {
                Route::middleware('web')
                    ->domain($domain)
                    ->group(base_path('routes/web.php'));
            }
 
            Route::middleware('web')->group(base_path('routes/tenant.php'));
        }, 
        web: __DIR__.'/../routes/web.php', 
        commands: __DIR__.'/../routes/console.php',
        health: '/up',
    )
    ->withMiddleware(function (Middleware $middleware) {
        //
    })
    ->withExceptions(function (Exceptions $exceptions) {
        //
    })->create();

.env:

// ...
 
SESSION_DRIVER=database
SESSION_LIFETIME=120
SESSION_ENCRYPT=false
SESSION_PATH=/
SESSION_DOMAIN=tenancy.test 
 
// ...

Finally, the routes themselves.

routes/web.php:

use App\Http\Controllers\ProfileController;
use Illuminate\Support\Facades\Route;
 
Route::get('/', function () {
    return view('welcome');
});
 
Route::get('/dashboard', function () { 
    return view('dashboard');
})->middleware(['auth', 'verified'])->name('dashboard');
 
Route::middleware('auth')->group(function () {
    Route::resource('tasks', \App\Http\Controllers\TaskController::class);
    Route::resource('projects', \App\Http\Controllers\ProjectController::class);
 
    Route::get('/profile', [ProfileController::class, 'edit'])->name('profile.edit');
    Route::patch('/profile', [ProfileController::class, 'update'])->name('profile.update');
    Route::delete('/profile', [ProfileController::class, 'destroy'])->name('profile.destroy');
}); 
 
require __DIR__.'/auth.php';

routes/tenant.php:

use App\Http\Controllers\ProfileController;
 
Route::middleware([
    'web',
    InitializeTenancyByDomain::class,
    PreventAccessFromCentralDomains::class,
])->group(function () {
    Route::get('/dashboard', function () { 
        return view('dashboard');
    })->middleware(['auth', 'verified'])->name('dashboard');
 
    Route::resource('tasks', \App\Http\Controllers\TaskController::class);
    Route::resource('projects', \App\Http\Controllers\ProjectController::class);
 
    Route::get('/profile', [ProfileController::class, 'edit'])->name('profile.edit');
    Route::patch('/profile', [ProfileController::class, 'update'])->name('profile.update');
    Route::delete('/profile', [ProfileController::class, 'destroy'])->name('profile.destroy'); 
    Route::get('/', function () { 
        return 'This is your multi-tenant application. The id of the current tenant is ' . tenant('id');
    });
});

We changed the logic of initializing tenancy: instead of by domain, now it will be by subdomain. This change needs to be done in the routes/tenant.php file.

routes/tenant.php:

use Stancl\Tenancy\Middleware\InitializeTenancyBySubdomain;
 
Route::middleware([
    'web',
    InitializeTenancyBySubdomain::class, 
    InitializeTenancyByDomain::class, 
    PreventAccessFromCentralDomains::class,
])->group(function () {
    //
});

Next, we need a migration for tenant_user pivot table.

php artisan make:migration "create tenant user table"

database/migrations/xxxx_create_tenant_user_table:

Schema::create('tenant_user', function (Blueprint $table) {
    $table->foreignId('tenant_id')->constrained();
    $table->foreignId('user_id')->constrained();
});

app/Models/User.php:

use Illuminate\Database\Eloquent\Relations\BelongsToMany;
 
class User extends Authenticatable
{
    // ...
 
    public function tenants(): BelongsToMany
    {
        return $this->belongsToMany(Tenant::class);
    }
}

New Tenant Registration

As with the single database, here we add the input for the subdomain on the registration page.

resources/views/auth/register.blade.php:

// ...
 
<!-- Subdomain -->
<div class="mt-2">
    <x-input-label for="subdomain" :value="__('Subdomain')" />
 
    <div class="flex items-baseline">
        <x-text-input id="subdomain" class="block mt-1 mr-2 w-full" type="text" name="subdomain" :value="old('subdomain')" required />
        .{{ config('tenancy.central_domains')[0] }}
    </div>
    <x-input-error :messages="$errors->get('subdomain')" class="mt-2" />
</div>
 
// ...

The backend part for registration in Laravel Breeze goes to app/Http/Controllers/Auth/RegisteredUserController.php.

When creating a tenant domain, the difference is that you only need the subdomain instead of a full subdomain + domain. After creating a user, we create a Tenant, then we create a domain for that tenant and attach the tenant to the User.

use App\Models\Tenant;
 
class RegisteredUserController extends Controller
{
    // ...
 
    public function store(Request $request): RedirectResponse
    {
        $request->validate([
            'name' => ['required', 'string', 'max:255'],
            'email' => ['required', 'string', 'lowercase', 'email', 'max:255', 'unique:'.User::class],
            'password' => ['required', 'confirmed', Rules\Password::defaults()],
        ]);
 
        $user = User::create([
            'name' => $request->name,
            'email' => $request->email,
            'password' => Hash::make($request->password),
        ]);
 
        $tenant = Tenant::create([ 
            'name' => $request->name,
        ]);
        $tenant->createDomain($request->subdomain);
        $user->tenants()->attach($tenant->id); 
 
        event(new Registered($user));
 
        Auth::login($user);
 
        return redirect(route('dashboard', absolute: false)); 
        return redirect('http://' . $request->subdomain . '.'. config('tenancy.central_domains')[0] . route('dashboard', absolute: false)); 
    }
}

Of course, we need to run the migration for our tenant user but wait a minute before doing that. Here's where we must split our migrations into landlord and tenant or central database and tenant databases.

Currently, we have all the migrations in one folder of database/migrations. But the package created the tenant subfolder, which is empty, and you need to move all the migrations of the Tenantable data to that folder. So, we have projects and task migrations that should be in the tenant folder.

If your SESSION_DRIVER is set to database, you must create the sessions table for every tenant.

When a tenant is created, in the TenancyServiceProvider, there are jobs that fire and try to create a new database and run the migrations from the database/migrations/tenant folder.

app/Providers/TenancyServiceProvider.php:

class TenancyServiceProvider extends ServiceProvider
{
    public static string $controllerNamespace = '';
 
    public function events()
    {
        return [
            Events\CreatingTenant::class => [],
            Events\TenantCreated::class => [
                JobPipeline::make([ 
                    Jobs\CreateDatabase::class,
                    Jobs\MigrateDatabase::class,
                    // Jobs\SeedDatabase::class,
                ])->send(function (Events\TenantCreated $event) {
                    return $event->tenant;
                })->shouldBeQueued(false),
            ],
            // ...
        ];
    }
 
    // ...
}

What would be the database name? It is configurable in the config/tenancy.php: the database value has a prefix and suffix. All the databases will be named prefix + tenant_id + suffix.

The users table is in the central database. If, for example, you try to update a user's profile, you will receive an error.

One way is to tell Laravel to use the central database connection. This can be done in two ways: setting a $connection property or using a CentralConnection Trait from the package on the Model.

Option 1:

app/Models/User.php:

class User extends Authenticatable
{
    use HasFactory, Notifiable;
 
    protected $connection = 'mysql'; 
 
    // ...
}

Option 2:

app/Models/User.php:

use Stancl\Tenancy\Database\Concerns\CentralConnection;
 
class User extends Authenticatable
{
    use HasFactory, Notifiable;
    use CentralConnection; 
 
    // ...
}

To correctly render Vite assets, uncomment the ViteBundler class in the configuration.

config/tenancy.php:

return [
    'features' => [
        // Stancl\Tenancy\Features\UserImpersonation::class,
        // Stancl\Tenancy\Features\TelescopeTags::class,
        // Stancl\Tenancy\Features\UniversalRoutes::class,
        // Stancl\Tenancy\Features\TenantConfig::class, // https://tenancyforlaravel.com/docs/v3/features/tenant-config
        // Stancl\Tenancy\Features\CrossDomainRedirect::class, // https://tenancyforlaravel.com/docs/v3/features/cross-domain-redirect
         Stancl\Tenancy\Features\ViteBundler::class, 
    ],
];

To prevent access to a different tenant, the subdomain package has a ScopeSessions scope.

routes/tenant.php:

use Stancl\Tenancy\Middleware\ScopeSessions;
 
Route::middleware([
    'web',
    InitializeTenancyBySubdomain::class,
    PreventAccessFromCentralDomains::class,
    ScopeSessions::class, 
])->group(function () {
    // ...
});

We have set up the stancl/tenancy package to work with multi-database. In the next section of the course, we will look at more features of this package.

You can find the source code for the single database example using the stancl/tenancy package on GitHub.