Jichiduo.com
0%

MultiTanancy Using Multiple Database Howto

Posted on Edited on In ,

Want to creat a multi-tenancy application? You can use the Tenancy package to create a multi-tenancy application with multiple database and multiple domains support.

Here is a simple instruction for you to create a multi-tenancy application in windows system.

Requirement

  • PHP 8.0+
  • Laravel 8.0+
  • spatie/laravel-multitenancy 2.0+

Installation

Create a new laravel project and add neccessary packages then publish some config files

1
2
3
4
5
6
7
8
laravel new mulittenancy
cd mulittenancy
composer require laravel/breeze --dev
php artisan breeze:install
npm install && npm run dev
composer require "spatie/laravel-multitenancy:^2.0"
php artisan vendor:publish --provider="Spatie\Multitenancy\MultitenancyServiceProvider" --tag="multitenancy-config"
php artisan vendor:publish --provider="Spatie\Multitenancy\MultitenancyServiceProvider" --tag="multitenancy-migrations"

Create databases and run the migration

For simplicity we just manually create the 3 databases by HeidiSQL, you can implement your own code to create tenant database on the fly. Open your favorite mysql client app create 3 databases, names are tenant_1, tenant_2 and landlord .

After created the databases, modify the config files
config/database.php

1
2
// 'default' => env('DB_CONNECTION', 'mysql'),
'default' => 'tenant',

and add the following code under connections section below “sqlsrv”

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
'tenant' => [
    'driver' => 'mysql',
    'database' => null,
    'host' => '127.0.0.1',
    'username' => 'root',
    'password' => '',
    // And other options if needed ...
    'charset' => 'utf8mb4',
    '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'),
    ]) : [],

],

'landlord' => [
    'driver' => 'mysql',
    'database' => 'landlord',
    'host' => '127.0.0.1',
    'username' => 'root',
    'password' => '',
    // And other options if needed ...
    'charset' => 'utf8mb4',
    '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'),
    ]) : [],

],

Open config/multitenancy.php , modify as the followings, only line 21,36,58,63 changed from the original code

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
<?php

use Illuminate\Broadcasting\BroadcastEvent;
use Illuminate\Events\CallQueuedListener;
use Illuminate\Mail\SendQueuedMailable;
use Illuminate\Notifications\SendQueuedNotifications;
use Spatie\Multitenancy\Actions\ForgetCurrentTenantAction;
use Spatie\Multitenancy\Actions\MakeQueueTenantAwareAction;
use Spatie\Multitenancy\Actions\MakeTenantCurrentAction;
use Spatie\Multitenancy\Actions\MigrateTenantAction;
use Spatie\Multitenancy\Models\Tenant;

return [
    /*
     * This class is responsible for determining which tenant should be current
     * for the given request.
     *
     * This class should extend `Spatie\Multitenancy\TenantFinder\TenantFinder`
     *
     */
    'tenant_finder' => Spatie\Multitenancy\TenantFinder\DomainTenantFinder::class,

    /*
     * These fields are used by tenant:artisan command to match one or more tenant
     */
    'tenant_artisan_search_fields' => [
        'id',
    ],

    /*
     * These tasks will be performed when switching tenants.
     *
     * A valid task is any class that implements Spatie\Multitenancy\Tasks\SwitchTenantTask
     */
    'switch_tenant_tasks' => [
        Spatie\Multitenancy\Tasks\SwitchTenantDatabaseTask::class,
    ],

    /*
     * This class is the model used for storing configuration on tenants.
     *
     * It must be or extend `Spatie\Multitenancy\Models\Tenant::class`
     */
    'tenant_model' => Tenant::class,

    /*
     * If there is a current tenant when dispatching a job, the id of the current tenant
     * will be automatically set on the job. When the job is executed, the set
     * tenant on the job will be made current.
     */
    'queues_are_tenant_aware_by_default' => true,

    /*
     * The connection name to reach the tenant database.
     *
     * Set to `null` to use the default connection.
     */
    'tenant_database_connection_name' => 'tenant',

    /*
     * The connection name to reach the landlord database
     */
    'landlord_database_connection_name' => 'landlord',

    /*
     * This key will be used to bind the current tenant in the container.
     */
    'current_tenant_container_key' => 'currentTenant',

    /*
     * You can customize some of the behavior of this package by using our own custom action.
     * Your custom action should always extend the default one.
     */
    'actions' => [
        'make_tenant_current_action' => MakeTenantCurrentAction::class,
        'forget_current_tenant_action' => ForgetCurrentTenantAction::class,
        'make_queue_tenant_aware_action' => MakeQueueTenantAwareAction::class,
        'migrate_tenant' => MigrateTenantAction::class,
    ],

    /*
     * You can customize the way in which the package resolves the queuable to a job.
     *
     * For example, using the package laravel-actions (by Loris Leiva), you can
     * resolve JobDecorator to getAction() like so: JobDecorator::class => 'getAction'
     */
    'queueable_to_job' => [
        SendQueuedMailable::class => 'mailable',
        SendQueuedNotifications::class => 'notification',
        CallQueuedListener::class => 'class',
        BroadcastEvent::class => 'event',
    ],
];

Start the mirgation , first migrate the landlord database

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

Insert two rows of tenant data to the landlord database tenants table

1
2
INSERT INTO `tenants` (`id`, `name`, `domain`, `database`, `created_at`, `updated_at`) VALUES (1, 'tenant-1', 'tenant1.test', 'tenant_1', NULL, NULL);
INSERT INTO `tenants` (`id`, `name`, `domain`, `database`, `created_at`, `updated_at`) VALUES (2, 'tenant-2', 'tenant2.test', 'tenant_2', NULL, NULL);

Second migrate the tenant database

1
php artisan tenants:artisan "migrate --database=tenant"

if everything goes well, you will see

Modify app\Http\Kernel.php

Add last two line (NeedsTenant::class,EnsureValidTenantSession::class) to the web middleware group, all your application routes are now tenant-aware

1
2
3
4
5
6
7
8
9
10
11
12
13
protected $middlewareGroups = [
    'web' => [
        \App\Http\Middleware\EncryptCookies::class,
        \Illuminate\Cookie\Middleware\AddQueuedCookiesToResponse::class,
        \Illuminate\Session\Middleware\StartSession::class,
        // \Illuminate\Session\Middleware\AuthenticateSession::class,
        \Illuminate\View\Middleware\ShareErrorsFromSession::class,
        \App\Http\Middleware\VerifyCsrfToken::class,
        \Illuminate\Routing\Middleware\SubstituteBindings::class,
        \Spatie\Multitenancy\Http\Middleware\NeedsTenant::class,
        \Spatie\Multitenancy\Http\Middleware\EnsureValidTenantSession::class,
    ],

Try login with different tenant

Modify default welcome page to show which tenant is current tenant, oepn resource/views/welcome.blade.php, find the Documentation section and add {{app("currentTenant")->name}}

1
2
3
4
<div class="ml-4 text-lg leading-7 font-semibold"><a href="https://laravel.com/docs"
        class="underline text-gray-900 dark:text-white">Documentation
        {{app("currentTenant")->name}}</a></div>

Add two dummy domain to hosts file.
Open C:\Windows\System32\drivers\etc\hosts file with administrator privilege, add the following line to the bottom

1
2
127.0.0.1 tenant1.test
127.0.0.1 tenant2.test

Now open your browser and try open two tab, one is tenant1.test, the other is tenant2.test, you will see the welcome page with different tenant name after Documentation.

Try to create 2 new user from different tenants, create Tom in tenant1.test, and Jerry in tenant2.test

Open HeidiSQL, we can see the user Tom and Jerry are created in tenant1 and tenant2 database respectively.

Sub Domain support

In this tutorial, we use two different dummy domain tenant1.test and tenant2.test, you can also change to sub-domain mode like tenant1.abc.test and tenant2.abc.test in the landlord database tenant table , no code need to be changed, the system will support sub domain automaticly.

Github

You can find the source of this example here

REF