Multiple DBs: spatie / multi-tenancy

Now let's try to perform a multi-database setup with another package spatie/multi-tenancy. I will remind you, it has fewer features. Its philosophy is to provide the bare bones for the multi-tenancy, but not more than that. Again, we start with a project and task CRUD.

Installation and Configuration

First, we start with a package install via composer and publish the configuration.

composer require spatie/laravel-multitenancy
php artisan vendor:publish --provider="Spatie\Multitenancy\MultitenancyServiceProvider" --tag="multitenancy-config"

The next step is choosing the multiple databases.

First, we must configure the database connections. This will slightly differ from the previous package in configuring database connections.

You will need two connections: landlord and tenant. If we look at the config/multitenancy.php, the database connection names are null for tenant and landlord.

config/multitenancy.php:

return [
    // ...
 
    'tenant_database_connection_name' => null,
    'landlord_database_connection_name' => null,
 
    // ...
];

We can create a custom connection for the landlord or use the available default. I will be using the mysql connection.

config/multitenancy.php:

return [
    // ...
 
    'tenant_database_connection_name' => null,
    'landlord_database_connection_name' => 'mysql', 
 
    // ...
];

For the tenant, we will create a new database connection called tenant. The database key must be set to null in this new connection.

config/database.php:

return [
 
    // ...
 
    'connections' => [
 
        // ...
 
        'mysql' => [
            'driver' => 'mysql',
            'url' => env('DB_URL'),
            'host' => env('DB_HOST', '127.0.0.1'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE', 'laravel'),
            'username' => env('DB_USERNAME', 'root'),
            'password' => env('DB_PASSWORD', ''),
            'unix_socket' => env('DB_SOCKET', ''),
            'charset' => env('DB_CHARSET', 'utf8mb4'),
            'collation' => env('DB_COLLATION', 'utf8mb4_unicode_ci'),
            'prefix' => '',
            'prefix_indexes' => true,
            'strict' => true,
            'engine' => null,
            'options' => extension_loaded('pdo_mysql') ? array_filter([
                PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
            ]) : [],
        ],
 
        'tenant' => [ 
            'driver' => 'mysql',
            'url' => env('DB_URL'),
            'host' => env('DB_HOST', '127.0.0.1'),
            'port' => env('DB_PORT', '3306'),
            'database' => null,
            'username' => env('DB_USERNAME', 'root'),
            'password' => env('DB_PASSWORD', ''),
        ], 
 
        // ...
 
    ],
 
    // ...
 
];

config/multitenancy.php:

return [
    // ...
 
    'tenant_database_connection_name' => 'tenant', 
    'landlord_database_connection_name' => 'mysql',
 
    // ...
];

The database for landlord is set, and the database for tenant is null because it is dynamic.

Next, migrating the landlord database. They call it the landlord or main database. We need to publish the migrations.

php artisan vendor:publish --provider="Spatie\Multitenancy\MultitenancyServiceProvider" --tag="multitenancy-migrations"

As you can see, they are in the database/migrations/landlord subfolder. The previous package provided the tenant subfolder. In this case, landlord is a subfolder. The documentation says all the new migrations should be in the database/migrations/landlord folder. Move all migrations except projects and tasks to the landlord folder.

Now, let's create the pivot table for tenant users.

php artisan make:migration "create tenant user table" --path=database/migrations/landlord

database/migrations/landlord/xxx_create_tenant_user_table.php:

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

Now, we can migrate the landlord migrations.

php artisan migrate --path=database/migrations/landlord

All the tables are migrated except the projects and tasks.

We will migrate the tenant database for the tenant tables while creating the tenant.

Ant lastly, we must add the DomainTenantFinder.

config/multitenancy.php:

return [
    'tenant_finder' => \Spatie\Multitenancy\TenantFinder\DomainTenantFinder::class,
 
    // ...
];

New Tenant Registration

First, quickly explain how we create the tenant on user registration.

app/Models/Tenant.php:

class Tenant extends \Spatie\Multitenancy\Models\Tenant
{
    protected $fillable = [
        'name',
        'domain',
        'database',
    ];
}

app/Models/User.php:

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

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

// ...
 
<!-- Subdomain -->
<div class="mt-4">
    <x-input-label for="subdomain" :value="__('Subdomain')" />
 
    <x-text-input id="subdomain" class="block mt-1 w-full" type="text" name="subdomain" :value="old('subdomain')" required />
</div>
 
// ...

app/Http/Controllers/Auth/RegisteredUserController.php:

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,
            'domain' => $request->subdomain . '.' .config('session.domain'),
            'database' => 'tenancy_' . $request->subdomain,
        ]);
        $user->tenants()->attach($tenant->id); 
 
        event(new Registered($user));
 
        Auth::login($user);
 
        return redirect(route('dashboard', absolute: false)); 
        return redirect('http://' . $tenant->domain . route('dashboard', absolute: false)); 
    }
}

After registering, we will be redirected to the correct subdomain.

But if we try to visit tasks or projects, we get an error.

So, it doesn't switch to the tenant database automatically. In the landlord database, we have the tenants table where we have a database name for a tenant.

We have to create this database manually and migrate all the migrations. But first, we must make the tenant current and enable the SwitchTenantDatabaseTask class in the config/multitenancy.php under the switch_tenant_tasks.

config/multitenancy.php:

return [
    // ...
 
    'switch_tenant_tasks' => [
        // \Spatie\Multitenancy\Tasks\PrefixCacheTask::class,
        \Spatie\Multitenancy\Tasks\SwitchTenantDatabaseTask::class, 
        // \Spatie\Multitenancy\Tasks\SwitchRouteCacheTask::class,
    ],
 
    // ...
];

app/Http/Controllers/Auth/RegisteredUserController.php:

class RegisteredUserController extends Controller
{
    // ...
 
    public function store(Request $request): RedirectResponse
    {
        // ...
 
        $tenant = Tenant::create([
            'name' => $request->name,
            'domain' => $request->subdomain . '.' .config('session.domain'),
            'database' => 'tenancy_' . $request->subdomain,
        ]);
        $tenant->makeCurrent(); 
        $user->tenants()->attach($tenant->id);
 
        event(new Registered($user));
 
        Auth::login($user);
 
        return redirect('http://' . $tenant->domain . route('dashboard', absolute: false));;
    }
}

The package recommends to create the database when a Tenant Model is being created.

app/Models/Tenant.php:

use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Artisan;
 
class Tenant extends \Spatie\Multitenancy\Models\Tenant
{
    protected $fillable = [
        'name',
        'domain',
        'database',
    ];
 
    protected static function booted(): void 
    {
        static::creating(function(Tenant $tenant) {
            $query = "CREATE DATABASE IF NOT EXISTS $tenant->database;";
            DB::statement($query);
        });
 
        static::created(function(Tenant $tenant) {
            Artisan::call('tenants:artisan "migrate --database=tenant"');
        });
    } 
}

In the Tenant Model, we use two Eloquent events. The first creates the database when a tenant is being created. But, we must migrate tenant tables after the tenant is created. Because then, the tenant database is switched.

Finally, we must prepare the models by adding UsesLandlordConnection or UsesTenantConnection traits.

app/Models/Tenant.php:

use Spatie\Multitenancy\Models\Concerns\UsesTenantConnection;
 
class Project extends Model
{
    use UsesTenantConnection; 
 
    protected $fillable = [
        'name',
    ];
}

app/Models/Task.php:

use Spatie\Multitenancy\Models\Concerns\UsesTenantConnection;
 
class Task extends Model
{
    use UsesTenantConnection; 
 
    protected $fillable = [
        'name',
        'project_id',
    ];
 
    public function project(): BelongsTo
    {
        return $this->belongsTo(Project::class);
    }
}

app/Models/User.php:

use Spatie\Multitenancy\Models\Concerns\UsesLandlordConnection;
 
class User extends Authenticatable
{
    use HasFactory, Notifiable;
    use UsesLandlordConnection; 
 
    // ...
}

After registering, we can now access the tasks and projects from a separate database.

Securing Tenants

The package provides two Middlewares:

1. NeedsTenant - to ensure a current tenant has been set.
2. EnsureValidTenantSession - to verify that the session is not being abused across multiple tenants.

You can add these two Middlewares separately or create a group and assign that group to the Routes.

bootstrap/app.php:

return Application::configure(basePath: dirname(__DIR__))
    ->withRouting(
        web: __DIR__.'/../routes/web.php',
        commands: __DIR__.'/../routes/console.php',
        health: '/up',
    )
    ->withMiddleware(function (Middleware $middleware) {
        $middleware->group('tenant', [ 
            \Spatie\Multitenancy\Http\Middleware\NeedsTenant::class,
            \Spatie\Multitenancy\Http\Middleware\EnsureValidTenantSession::class
        ]); 
    })
    ->withExceptions(function (Exceptions $exceptions) {
        //
    })->create();

routes/web.php:

Route::get('/', function () {
    return view('welcome');
});
 
Route::get('/dashboard', function () {
    return view('dashboard');
})->middleware(['auth', 'verified'])->name('dashboard');
 
Route::middleware(['auth', 'tenant'])->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';

We have performed and finished the multi-database setup with the Spatie package: registration, creating the tenant, separating the connections, and redirecting where it needs to be redirected.

You can find the source code for the multi database example using the spatie/laravel-multitenancy package on GitHub.