Mastering Multi‑Tenant SaaS: Shared Database with Independent Schemas in PHP

Overview

Multi‑tenant SaaS platforms need a mechanism that selects the correct database connection at runtime so that each tenant’s data remains isolated while sharing a single physical database server.

Tenant Isolation Strategies

Separate databases : each tenant has its own database instance.

Shared database, separate schemas : one database contains a distinct schema (or logical database) per tenant.

Shared database, shared schema : all tenants use the same tables and are distinguished only by a tenant‑ID column.

Shared Database with Independent Schemas

This approach balances resource utilization and logical isolation. In Oracle a Schema is tied to a user; in MySQL the terms Schema and Database are interchangeable, and CREATE DATABASE and CREATE SCHEMA are equivalent.

Pros

High resource utilization : a single DB instance serves all tenants, reducing hardware and maintenance costs.

Simplified management : only one instance to back up, upgrade and monitor.

Logical isolation : each tenant’s objects live in its own schema, preventing accidental cross‑tenant data access.

Cons

Weaker isolation than separate databases : complex cross‑schema queries can expose data if not carefully controlled.

Potential performance bottlenecks : as the tenant count grows, all share the same CPU, memory and I/O resources.

Implementation Plan

Domain Resolution and Routing

Configure an A record for each tenant domain (e.g., newtrain.tinywan.com, hz_newtrain.tinywan.com, bj_newtrain.tinywan.com) to point to the SaaS server.

In the web application, read the HTTP Host header (or a custom x-site-domain header) to identify the tenant.

Data Source Configuration

Define a connection entry for each tenant (URL, username, password) in a configuration file or a configuration centre.

Load the appropriate entry at runtime based on the resolved tenant identifier.

Dynamic Connection Switching Middleware (PHP)

<?php
/**
 * Domain routing middleware – selects the tenant database.
 */
declare(strict_types=1);

namespace app\middleware;

use app\common\model\SaasModel;
use Webman\Http\Request;
use Webman\Http\Response;
use Webman\MiddlewareInterface;

class ConnectionMiddleware implements MiddlewareInterface
{
    public function process(Request $request, callable $handler): Response
    {
        $domain = $request->header()['x-site-domain'] ?? 'https://newtrain.tinywan.com';
        $platform = SaasModel::where('domain', $domain)
            ->field('id,domain,website')
            ->findOrEmpty();
        if (!$platform->isEmpty()) {
            $request->website = $platform['website']; // e.g. "train", "hangzhou", "beijing"
        }
        return $handler($request);
    }
}
?>

The middleware stores the selected connection name in $request->website for later use.

ThinkORM Configuration (config/thinkorm.php)

<?php
return [
    'default' => 'train',
    'connections' => [
        'train' => [
            'type' => 'mysql',
            'hostname' => '127.0.0.1',
            'database' => 'newtrain.tinywan.com',
            'username' => 'root',
            'password' => '123456',
        ],
        'hangzhou' => [
            'type' => 'mysql',
            'hostname' => '127.0.0.1',
            'database' => 'hangzhou.tinywan.com',
            'username' => 'root',
            'password' => '123456',
        ],
        'beijing' => [
            'type' => 'mysql',
            'hostname' => '127.0.0.1',
            'database' => 'beijing.tinywan.com',
            'username' => 'root',
            'password' => '123456',
        ],
    ],
];
?>

Base Model for Automatic Connection Selection

<?php
namespace app\common\model;

use think\Model;

class BaseModel extends Model
{
    /** @var string */
    protected $connection;

    public function __construct(array $data = [])
    {
        $this->connection = \request()->website ?? 'train';
        parent::__construct($data);
    }
}
?>

Example Models

<?php
// CityModel.php – uses the default "train" connection
namespace app\common\model;

use think\Model;

class CityModel extends Model
{
    protected $connection = 'train';
    protected $table = 'common_city';
}
?>

<?php
// MeetingModel.php – inherits BaseModel to pick the tenant connection
namespace app\common\model;

class MeetingModel extends BaseModel
{
    protected $table = 'resty_meeting';
}
?>

Controller Usage

<?php
public function meetingList(\support\Request $request, int $organizationId): \support\Response
{
    $list = \app\common\model\MeetingModel::where([
        'organization_id' => $organizationId,
        'create_user_id' => $this->userId,
    ])->select();
    return json($list->toArray());
}
?>

<?php
public function datasetList(\support\Request $request): \support\Response
{
    $res = \think\facade\Db::connect(\request()->website)
        ->table('resty_meeting')
        ->field('id,name')
        ->select();
    return json($res->toArray());
}
?>

Note: Db::connect() must be the first call in the query chain; it only affects that specific query.

Dynamic Connection Helper

/**
 * Create or switch to a connection that does not exist yet.
 *
 * @param string $name        Connection identifier
 * @param array  $connection   Connection parameters (type, hostname, database, username, password)
 * @return \think\db\ConnectionInterface
 */
function dynamic_connect_db(string $name, array $connection): \think\db\ConnectionInterface
{
    try {
        $connect = \think\facade\Db::connect($name);
    } catch (\Throwable $e) {
        $config = \think\facade\Db::getConfig();
        $config['connections'][$name] = $connection;
        \think\facade\Db::setConfig($config);
        $connect = \think\facade\Db::connect($name);
    }
    return $connect;
}

Example of Connecting to a New Tenant

<?php
public function dynamicConnectDb(\support\Request $request): \support\Response
{
    $connection = [
        'type'     => 'mysql',
        'hostname' => '127.0.0.1',
        'database' => 'zhejiang.tinywan.com',
        'username' => 'root',
        'password' => '123456',
    ];
    $connect = dynamic_connect_db('zhejiang', $connection);
    $result = $connect->table('resty_meeting')->where('id', 1)->find();
    return json($result->toArray());
}
?>

In production, dynamic connections are typically used for on‑the‑fly data synchronization, which may involve transaction handling, batch processing, and asynchronous jobs to maintain performance and consistency.

Key Takeaways

Using a shared database with independent schemas provides a cost‑effective isolation model for SaaS tenants.

Runtime tenant identification can be achieved via HTTP headers and a lightweight middleware that injects the appropriate connection name into the request.

ThinkORM’s connection property and the Db::connect() method enable per‑request database routing without restarting the application.

The dynamic_connect_db helper allows on‑demand creation of connections for tenants that are not pre‑configured.