This page View Markdown Open in ChatGPT Open in Claude

Multi-tenant¶


One instance	Serves every tenant with the same APIs, schemas, hooks and permissions.
One database per tenant	Of the type of the instance ; the connection string lives in a row of a tenants table.
Name the tenant	`crm::acme` in the path, or the header `x-am-tenant-username: acme`.
Tokens	A person of a tenant gets a token which works for that tenant only.

A multi-tenant instance serves many customers (tenants) with one set of APIs. Every tenant has its own database, of the same type as the instance.
The APIs, schemas, hooks, settings and permissions of the instance are defined once and serve every tenant. Customers share the servers and the APIs, not the databases.
The instance keeps its own connection string. That database is the structure database: a request without a tenant works on it.
Supported for MongoDB, MySQL, MariaDB, SQL Server, PostgreSQL and Oracle instances.

Example: a CRM with two customers¶

A CRM is sold to two companies, Acme and Globex. Each one gets its own PostgreSQL database: crm_acme and crm_globex.
One instance named crm serves both. Its own connection string points to crm, the structure database, which holds the tables every tenant has: customers, orders, users.
The tenants live in a table tenants of another instance, catalog, one row per customer.

1. Keep your tenants in a table¶

Use a table of any instance, with one row per tenant: a username column and a connection string column.
The table needs an active schema in API Maker.
To keep connection strings encrypted, add conversions.encryption to that column in its schema, and save the rows through the APIs of the table. API Maker decrypts it with the default secret when it connects.
For Oracle tenants, add columns for the username and the password of each tenant, and if a tenant connects with a privilege, a column for it: SYSDBA, SYSOPER, SYSASM, SYSBACKUP, SYSDG, SYSKM or SYSRAC. A tenant without a privilege connects as a plain user of its database.

[
    { "username": "acme",   "connection_string": "postgresql://crm:secret@db-1:5432/crm_acme",   "active": true },
    { "username": "globex", "connection_string": "postgresql://crm:secret@db-2:5432/crm_globex", "active": true }
]

Tenants can use different database servers: here Acme is on db-1 and Globex on db-2.

2. Point the default secret to that table¶

import * as T from 'types';

let Secret: T.ISecretType | any = {
    // ... common and your other keys
    multiTenant: {
        crm: <T.IMultiTenantSecretObj>{
            instanceName: 'catalog',                 // instance of the tenants table
            databaseName: 'public',
            collectionName: 'tenants',
            usernameColumn: 'username',
            connectionStringColumn: 'connection_string',
            find: { active: true },                  // optional, added to the tenant lookup

            // Oracle only
            // oracleDBUsernameColumn: 'db_username',
            // oracleDBPasswordColumn: 'db_password',
            // oracleDBPrivilegeColumn: 'db_privilege',   // optional : SYSDBA, SYSOPER... empty for a plain user
        },
    },
};
module.exports = Secret;

instanceName, databaseName, collectionName, usernameColumn and connectionStringColumn are required. A request of the instance fails with an error naming the missing keys otherwise.
A tenant must match its username and the find. With find: { active: true }, setting active to false suspends a tenant. One table can also keep the tenants of several instances apart, for example with find: { db_kind: 'pg' }.
Each multi-tenant instance chooses its own entry, so two instances can use two tenants tables.
A request never names the entry: the instance knows it. The tenant is all a request names.

3. Mark the instance¶

Open the instance and check Is Multi Tenant Structure Instance.
In Connection String Multi Tenant, choose the entry of the secret, for example multiTenant.crm.

4. Name the tenant in every request¶

In the path, after the instance name, with two colons.

1
2
3

GET /api/gen/admin/crm::acme/crm/customers      # the customers of Acme, from crm_acme
GET /api/gen/admin/crm::globex/crm/customers    # the customers of Globex, from crm_globex
GET /api/gen/admin/crm/crm/customers            # no tenant: the structure database

Or with the x-am-tenant-username header.

1 2	`GET /api/gen/admin/crm/crm/customers x-am-tenant-username: acme`

The rules:
- The instance looks the tenant up in its own tenants table, the entry chosen as its Connection String Multi Tenant. One header therefore serves every multi-tenant instance, even instances with different tenants tables.
- An empty header names no tenant: the structure database.
- When the path and the header both name a tenant, the tenant of the path is used.
- A tenant the table does not have, or which the find leaves out: 400, Unable to find tenant with username 'acme'.
- crm:: or ::acme: 400, Please provide valid multi tenant instance name.
- A tenant named for an instance which is not multi-tenant changes nothing: that instance has one database for every tenant.

Custom APIs¶

Call a custom API with the tenant header. It reads the tenant in g.req.params, and the APIs it calls with g.sys.db and g.sys.system work on the database of that tenant: the header is passed on to them. Each instance they call finds the tenant in its own tenants table.

1 2	`GET /api/custom-api/admin/customers-report x-am-tenant-username: acme`

The tenant of the request, and its data
import * as T from 'types';

async function main(g: T.IAMGlobal) {
    const tenant = g.req.params.tenantUsername; // 'acme', undefined without the header

    // The database of acme : no need to name the tenant.
    const customers = await g.sys.db.getAll({ instance: 'crm', database: 'crm', collection: 'customers' });
    const orders = await g.sys.db.gen.queryGen({
        instance: 'crm', database: 'crm', collection: 'orders',
        find: { status: 'paid' },
    });

    return { tenant, customers, orders };
}
module.exports = main;

Another tenant, or the structure database, on purpose:

Name the tenant of one call
// In the instance name : the tenant of the name wins over the tenant of the request.
const customersOfGlobex = await g.sys.db.getAll({ instance: 'crm::globex', database: 'crm', collection: 'customers' });

// With the header of the call.
const ordersOfGlobex = await g.sys.db.count({
    instance: 'crm', database: 'crm', collection: 'orders',
    headers: { 'x-am-tenant-username': 'globex' },
});

// An empty header : the structure database.
const templates = await g.sys.db.getAll({
    instance: 'crm', database: 'crm', collection: 'templates',
    headers: { 'x-am-tenant-username': '' },
});

Queries and table meta run in the database of the tenant too:

SQL of the tenant of the request
const rows = await g.sys.system.executeQuery({
    instance: 'crm', database: 'crm',
    query: 'select status, count(*) as orders from orders group by status',
});
const columns = await g.sys.system.getTableMeta({ instance: 'crm', database: 'crm', table: 'orders' });

// Another tenant : name it in the instance.
const rowsOfGlobex = await g.sys.system.executeQuery({ instance: 'crm::globex', database: 'crm', query: 'select count(*) from orders' });

A custom API with enableCaching keeps its answers per tenant. Reset them for one tenant:

Reset the cache of a custom API for one tenant
await g.sys.cache.resetCacheCustomApis({ name: 'customers-report', tenantUsername: 'acme' }); // without tenantUsername : every tenant

A custom API whose authProviders name a token generator on a users table of the instance needs a token of the tenant of the request, see Users of a tenant.

Hooks¶

The pre and post hooks of a request of a tenant read it in g.req.params.tenantUsername.
The APIs they call work on the database of that tenant, whether the request named the tenant in the path or with the header.

Post hook : an audit row in the database of the tenant
import * as T from 'types';

async function main(g: T.IAMGlobal) {
    await g.sys.db.saveSingleOrMultiple({
        instance: 'crm', database: 'crm', collection: 'audit_logs',
        saveData: { action: 'customer saved', tenant: g.req.params.tenantUsername },
    });
}
module.exports = main;

What API Maker does¶

Reads the row of the tenant from the tenants table, decrypts its connection string if needed, and opens a connection pool for that tenant (for PostgreSQL, one per tenant and database). The next requests of the tenant reuse the pool.
A name the tenants table does not know opens no pool.
A new tenant needs no restart and no deployment: its first request reads its row. Onboarding a customer is creating its database and adding one row to the tenants table.
Logs record the tenant of each call.
The databases panel and the API testing page let you pick a tenant to work with its data.

Caching¶

The cached answers of a multi-tenant instance are kept per tenant: a tenant never gets the cached answer of another tenant, nor of the structure database.
A request naming its tenant in the path and one naming it with the header share their cached answers.
A write for one tenant drops the cached answers of that tenant. A write to the structure database drops those of every tenant.
The tables of an instance which is not multi-tenant are read by every tenant: a write made for any tenant drops the cached answers of all of them.
Custom APIs with caching keep their answers per tenant as well. A write to the database of a tenant drops the cached answers of the custom APIs reading that table, for that tenant.
To reset the cache of a table by hand:

Reset the cache of a table
await g.sys.cache.resetCacheDB({ instance: 'crm::acme', database: 'crm', collection: 'customers' }); // acme only
await g.sys.cache.resetCacheDB({ instance: 'crm', database: 'crm', collection: 'customers' });       // the structure database and every tenant

Users of a tenant¶

Users can live in a users table inside each tenant database, like crm.users above.
Add a DB token generator on that table of the multi-tenant instance, on the Token Generators page, for example crm_users_token.
A user gets a token with the token API and the tenant header:

POST /api/system-api/admin/token
x-am-tenant-username: acme

{ "name": "crm_users_token", "u": "[email protected]", "p": "PASSWORD" }

API Maker reads Alice from the users table of crm_acme, and the token it returns is made for acme:
- Requests of acme accept it, as long as Alice is in the users table of crm_acme.
- A request for globex is refused with 401: Tenant from x-am-user-authorization and x-am-tenant-username should be same. Even when globex has a user with the same username and password.
- A request without a tenant, on the structure database, is refused with 401.
- A refreshed token keeps the tenant, whatever tenant the refresh call names.
A token made without a tenant, or from a users table in another instance, is not tied to a tenant: it is checked against the users table of the tenant each request names. To keep such a caller to its own tenant, compare g.req.params.tenantUsername with the tenant of the caller in a pre hook.

After a tenant moves¶

When the connection string of a tenant changes, call the Multi tenant instance updated system API. Every server drops the pools of that tenant, for PostgreSQL the pools of all its databases. The cached answers of the instance and of the tenants table are cleared, so the next request of the tenant reads its row again and connects to the new database.
Until then, the servers keep using the pools they have.

await g.sys.system.multiTenantInstanceUpdated({ instanceName: 'crm', username: 'acme' });

It also accepts an array of { instanceName, username }, and answers each item: { success: true, data: true }, or success: false with the error: an unknown instance (404) or an instance which is not multi-tenant (400).

1
2
3

POST /api/system-api/admin/multi-tenant-instance-updated

[{ "instanceName": "crm", "username": "acme" }, { "instanceName": "billing", "username": "acme" }]

Good to know¶

A schema change must run on every tenant database: plan migrations for all of them.
Each API Maker process keeps one pool per active tenant. Size the connection limits of your database servers for it.