Route SQL by tenant

Two customers share your service, and neither may ever see the other’s tasks. The safest line between them is a database each. With databaseFactory, every request’s tenant selects the database its queries and commands use, and no query can ask for another tenant’s data.

A database per tenant

import { drizzle, type NodePgDatabase } from 'drizzle-orm/node-postgres';
import { Pool } from 'pg';
import { DrizzleDialect } from '@cratis/arc.drizzle';
import { TaskRecord, tasks } from './Tasks.js';

const databases = new Map<string, NodePgDatabase>();
const allowedTenants = new Set(['acme', 'globex']);

builder.withDrizzle({
    dialect: DrizzleDialect.PostgreSQL,
    databaseFactory: tenant => {
        if (!allowedTenants.has(tenant)) throw new Error(`Unknown tenant ${tenant}`);
        let database = databases.get(tenant);
        if (!database) {
            database = drizzle(new Pool({ connectionString: `postgres://localhost/tasks_${tenant}` }));
            databases.set(tenant, database);
        }
        return database;
    },
    readModels: [{ type: TaskRecord, table: tasks }]
});

This excerpt assumes an Arc builder and a PostgreSQL tasks table declared with pgTable, in the shape Get started shows for SQLite. Arc calls databaseFactory once per execution scope, after it has resolved the tenant, with the tenant ID in lowercase. A missing tenant, or a factory that returns nothing, fails the request.

What you own

Arc selects; your factory decides. The application owns:

Creation and caching. Reuse one pool per tenant, as above. A factory that creates a pool for any tenant ID a request names lets a caller exhaust your connections.
The mapping. Arc does not check that the database you return belongs to the tenant, and does not add a tenant predicate to any query.
Credentials. Give each tenant’s pool credentials that reach only that tenant’s database.
Shutdown. Arc never closes a database the factory returns. Close your pools after await app.dispose().
Migrations. Migrate every tenant’s database before serving it; see Own the schema.

Choosing the tenant’s database does not prove the caller belongs to that tenant. Configure a membership check or derive the tenant from the principal; see Tenancy.

A schema per tenant

For one database with a schema per tenant, return a pool or connection that is already confined to that tenant’s schema, such as one with a fixed search path. Never change the search path of a shared pooled connection per request unless a transaction guarantees it is reset.

Single-tenant applications

The database option is the short path for one database. It serves only the default tenant, and a request for any other tenant fails closed with Drizzle database is only available for the default tenant. Resolve every request to that tenant:

const builder = ArcApplication.createBuilder({ tenancy: { resolve: () => 'default' } });

Set exactly one of database and databaseFactory; both, or neither, fails withDrizzle.

Verify the boundary

Test cross-tenant reads and writes with your real pool configuration and authorization: read as one tenant, write as another, and check that nothing crosses. The package’s SQLite tenant-isolation spec shows the shape of such a check. The MySQL 8.4 integration spec uses two databases on one MySQL server and verifies that databaseFactory routes reads to the current tenant; it does not test authorization or cross-tenant writes.

.NET EF pooled contexts do not infer a database per tenant either. This integration makes the choice explicit, like the MongoDB integration’s per-tenant resolvers, rather than reusing one pool across tenants.