Easy multi-tenancy with Laravel

I hope it's not just easy because I missed something huge

The first version of Teleatherapy was single-tenant, meaning all the resources in the database belonged to one “organisation”, us. This was fine for an early version of the product to get us up and running. Thankfully now we’re rolling out with other clinics and so we need some sort of separation between our resources in the database and those of other clinics.

I could have solved this problem earlier but really there was no need. It would have delayed features which were useful for our customers, so that took precedence until now.

Approaches to multi-tenancy

From my reading up on this, it seems there are three high-level approaches you can take:

  1. Have a separate database (maybe even whole instance of the app) per customer This would give the best guarantee of separation of data but would be more costly than needed and a pain to manage.
  2. Have a single database instance but separate schema per customer My understanding of this is that you have a single database instance/server but have a separate database/schema per customer (i.e. create database customer_1;) I’ve never done anything like this in practice and the advice I got was it would be time-consuming to run migrations for each database. Pro-tip: if in doubt ask someone smarter than you 🤓
  3. Have a single database for all customers and add a tenancy key to tables For someone who isn’t a database administrator this seems like the most approachable way. Add a table e.g. accounts and every other table which belongs to an account gets an account_id column. Then you can write your queries to only allow reading & writing resources belonging to the same account as a given user (e.g. $users->where('account_id', auth()->user()->account_id)). Some databases e.g. PostgreSQL can give you extra protection with row-level security.

Single-database multi-tenancy

I went with the third approach. It’s simplest from an infrastructure and DevOps perspective but it means all of my queries need to be aware of tenancy and stop users from accessing and modifying data from other tenants.

I’ve run into this problem before and you can end up having to manually write ->where('account_id', $user->account_id) almost everywhere you read and write from the database.

With Teleatherapy I wanted to try and solve it in once place. This protects future me from the current me who writes sloppy code and forgets to check for tenancy in every single place. I’m laying out a pit of success for me to fall in again and again.

Just show me the code already

Okay, fine.

In my case I’ve chosen to use Organisation as my “unit” of tenancy. You could use Account or Company or Team, whatever works. I also use uuid as my primary keys (is this a bad idea?) so if you’re using integer keys then make sure to replace method appropriately e.g.

- $table->uuid('id')->primary();
+ $table->id('id')->primary();

- $table->foreignUuid('user_id')->constrained()->cascadeOnDelete();
+ $table->foreignId('user_id')->constrained()->cascadeOnDelete();

Adding logic to the models

In order to avoid scattering where clauses around my codebase I decided to use Laravel’s global scopes. In order to have one global scope I can share with all my models I decided to add this via a trait:

<?php

namespace App\Concerns;

use App\Exceptions\MissingOrganisationException;
use App\Models\Organisation;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Eloquent\Model;

trait BelongsToOrganisation
{
    /**
     * Boot the trait.
     */
    protected static function bootBelongsToOrganisation()
    {
        static::addGlobalScope('organisation', function (Builder $query) {
            if ($organisationId = auth()->user()?->organisation_id) {
                $query->where('organisation_id', $organisationId);
            }
        });

        static::creating(function (Model $model) {
            if (empty($model->organisation_id)) {
                $organisationId = auth()->user()?->organisation_id;

                if (is_null($organisationId)) {
                    // Feel free to just use a standard `\Exception` here.
                    throw new MissingOrganisationException($model);
                }

                $model->organisation_id = $organisationId;
            }
        });
    }

    public function organisation()
    {
        return $this->belongsTo(Organisation::class);
    }
}
  • By adding a static function called boot{TraitName}, Laravel will call this when booting the Model which extends it.
  • In this method we define the global scope. If there is an authenticated user, we add a where clause to the query to the authenticated user’s id.
    • Note: when running as a background job there won’t be an authenticated user so I still have to handle that case!
    • I’m using an “anonymous scope” because it’s so short, if you wanted you could define a scope class as details in the docs.
    • If you ever want to disable this scope for a particular query you can do $query->withoutGlobalScope('organisation'). Swap in the name given to your closure scope or your scope class as needed.
  • In the static::creating closure I check if the organisation_id property is empty and try to set it to the organisation_id or the currently authenticated user (if any). If not I throw a clear exception explaining that it’s missing. Otherwise I would get a SQL error from the column being missing from the insert query.
    • This means when creating a model which uses this trait I can usually leave out organisation_id and it will set it for me. Nice and handy.
  • Be careful not to do an orWhere() query without nesting it as it will negate all our hard work here and can introduce bugs and allow querying unintentional data.

Database migrations

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration {
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::table('users', function (Blueprint $table) {
            $table->foreignUuid('organisation_id')
                ->after('id')
                ->constrained()
                ->cascadeOnDelete();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::table('users', function (Blueprint $table) {
            $table->dropForeign(['organisation_id']);
            $table->dropColumn('organisation_id');
        });
    }
};

Pretty straightforward, we just create the column, add a foreign key constraint and set it to cascade on delete. This means if we delete an organisation, we will delete all related data. Take that, GDPR.

To add a sprinkle of ✨ Laravel magic ✨ you can add a macro to the Blueprint class as we’re going to be using this everywhere:

<?php

namespace App\Providers;

use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Schema\ForeignIdColumnDefinition;
use Illuminate\Support\ServiceProvider;

// You can put this in `AppServiceProvider` or whichever service provider
// you want. I have a `MacroServiceProvider` with all of my macros in it.

class AppSericeProvider extends ServiceProvider
{
    public function boot()
    {
        Blueprint::macro('organisation', function () {
            return tap(
                $this->foreignUuid('organisation_id'),
                fn (ForeignIdColumnDefinition $column) =>
                    $column
                        ->constrained()
                        ->cascadeOnDelete()
            );
        });

        Blueprint::macro('dropOrganisation', function () {
            $this->dropForeign(['organisation_id']);
            $this->dropColumn('organisation_id');

            return $this;
        });
    }
}
  • We can’t simply return the whole thing as after we chain →constrained() it changes from an instance of the Column definition to the Foreign Key definition so we wouldn’t be able to chain ->after('id') after calling our ->organisation() macro.
  • I have never used tap() before but it’s nice here. That’s equivalent to doing:
$column = $this->foreignUuid('organisation_id');

$column->constrained()->cascadeOnDelete();

return $column;

I see why Taylor likes tap() now!

Now in your migrations you can do:

<?php

// in `up()`
Schema::table('users', function (Blueprint $table) {
    $table->organisation()->after('id');
});

// in `down()`
Schema::table('users', function (Blueprint $table) {
    $table->dropOrganisation();
});

Isn’t that nice.

Tests

Here are some basic tests which cover the behaviour:

<?php

use App\Exceptions\MissingOrganisationException;
use App\Models\User;
use Illuminate\Foundation\Testing\RefreshDatabase;
use function Pest\Laravel\actingAs;
use function Pest\Laravel\be;
use function PHPUnit\Framework\assertEquals;

uses(RefreshDatabase::class);

test('1. `organisation_id` is automatically set when there is an authenticated user', function () {
    /** @var User */
    $user = User::factory()
        ->forOrganisation()
        ->create();

    // Become the user so there will be an authenticated user.
    be($user);

    $post = $user->posts()->create([
        'title' => 'Easy multi-tenancy with Laravel'
        // We don't have to add `organisation_id` here,
        // our `BelongsToOrganisation` trait will do it for us
    ]);

    assertEquals($user->organisation_id, $post->organisation_id);
});

test('2. an exception is thrown when no `organisation_id` is provided', function () {
    /** @var User */
    $user = User::factory()
        ->forOrganisation()
        ->create();

    // We haven't come a user so there is no authenticated user.

    $user->post()->create([
        'title' => 'They always told me to fail fast',
    ]);
})->throws(
    MissingOrganisationException::class,
    'Missing organisation_id when creating model App\Models\ExerciseSession.'
);

test('3. organisation scope is automatically applied when there is an authenticated user', function () {
    /** @var User */
    $requestingUser = User::factory()
        ->forOrganisation()
        ->create();

    $userInSameOrganisation = User::factory()
        ->for($requestingUser->organisation)
        ->create();

    /** @var User */
    $userInDifferentOrganisation = User::factory()
        ->forOrganisation()
        ->create();

    assertEquals($requestingUser->organisation_id, $userInSameOrganisation->organisation_id);

    /**
     * Assert they're filtered from a paginated list.
     */
    actingAs($requestingUser)
        ->get('users', ['Accept' => 'application/json'])
        ->assertStatus(200)
        ->assertJsonFragment(['id' => $requestingUser->id])
        ->assertJsonFragment(['id' => $userInSameOrganisation->id])
        ->assertJsonMissing(['id' => $userInDifferentOrganisation->id]);

    /**
     * Assert you get a 404 when trying to access a resource from another organisation.
     */
    actingAs($requestingUser)
        ->get("users/{$userInDifferentOrganisation->id}", ['Accept' => 'application/json'])
        ->assertStatus(404);
});
  1. When there is an authenticated user, our trait will set the organisation_id to the same as the user
  2. When there is no user and it’s not provided it will throw an easy to understand exception
  3. When querying a list of users, users from a different organisation will be filtered out, and when trying to get a particular user from a different organisation you will get a 404.
    • You could make the case that a 403 would also work here but I’m happy with the 404.

That’s all folks

The core of this is a 41-line trait that provides me a decent bit of safety by default and helps protect me from my own worst enemy - me writing code in either the present or future.

I hope this is useful and I’m sure I haven’t thought of everything so I would love to hear any suggestions or comments!

Okay, I lied

Everything above is pretty generic but there are two extra things I did specific to my use case:

  1. Adding an organisation_id to models in an existing app
  2. Adding an organisation_id to Laravel\Sanctum\PersonalAccessToken

Adding an organisation_id to models in an existing app

Because I was adding multi-tenancy to an app already in production, I needed to add an organisation_id to all my existing models. I wanted to have a foreign key constraint on organisation_id but then the migration would fail as organisation_id would be null on all of the tables being updated. I figured I could either:

  1. Add the column but make it nullable, then create an organisation & set organisation_ids for everything, and then add yet another migration to add the constraint now that all my data was correct.
  2. Grab the first organisation in the database (or create one) and temporarily use that as the default value.

I went with the second option as it meant a quicker deploy. I don’t have any organisations in the database so I created a “Default Organisation” which I renamed after I deployed.

<?php

use App\Models\Organisation;
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration {
    const TABLES = [
        'posts',
        'users',
    ];

    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        /** @var Organisation */
        $organisation = Organisation::first() ?? Organisation::create([
            'name' => 'Default Organisation',
        ]);

        foreach (self::TABLES as $tableName) {
            Schema::table($tableName, function (Blueprint $table) use ($organisation) {
                $table->organisation()
                    ->after('id')
                    ->default($organisation->id); // Temporary default value
            });

            /**
             * Remove the default value after creating the table.
             */
            Schema::table($tableName, function (Blueprint $table) {
                $table->uuid('organisation_id')->default(null)->change();
            });
        }
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        foreach (self::TABLES as $tableName) {
            Schema::table($tableName, function (Blueprint $table) {
                $table->dropOrganisation();
            });
        }
    }
};

In the up() method we:

  • We get the first organisation or create one.
  • We loop through each table and add the column (using the organisation macro we created earlier) and set the default to the organisation id
  • After successfully creating the column we remove the default value

In the down() method we

  • Loop through each table and drop the column using the dropOrganisation macro we created earlier

Adding an organisation_id to Laravel\Sanctum\PersonalAccessToken

I want my personal access tokens to be scoped to an organisation also. In order to do so I extended Sanctum’s class:

<?php

namespace App\Models;

use App\Concerns\BelongsToOrganisation;
use Laravel\Sanctum\PersonalAccessToken as SanctumPersonalAccessToken;

class PersonalAccessToken extends SanctumPersonalAccessToken
{
    use BelongsToOrganisation;
}

And I then I told Sanctum to use this new model in my AppServiceProvider:

<?php

namespace App\Providers;

use App\Models\PersonalAccessToken;
use Illuminate\Support\ServiceProvider;
use Laravel\Sanctum\Sanctum;

class AppServiceProvider extends ServiceProvider
{
    public function boot()
    {
        Sanctum::usePersonalAccessTokenModel(PersonalAccessToken::class);
    }
}

And that's that (for real this time)

Thanks for taking the time to read this. I'm happy with this approach but would love to hear any concerns with it - always up for a constructive chat!

Thanks for reading,

dave-calnan-signature.png