Schema per tenant

Schema-per-tenant gives each tenant its own Postgres schema inside a shared database. Tables aren't shared; tenant_acme.orders and tenant_globex.orders are different tables. You get stronger separation than row-level without the operational cost of a database per tenant.

Supported on Knex, Lucid, Prisma, TypeORM, Sequelize, and Drizzle for PostgreSQL.

How it's enforced

Knex, Lucid, TypeORM, Sequelize, and Drizzle set the connection's search_path to that tenant's schema - transaction-locally, so it reverts before pooled reuse:

select set_config('search_path', 'tenant_acme', true);

Every unqualified query in that scope resolves to the tenant's schema. The schema name is validated as a safe SQL identifier before it's ever used, and cross-placement access is rejected.

Prisma is different: runtime search_path does not route generated model queries. Its adapter leases a Prisma 7 client created with the PostgreSQL driver's explicit { schema } option. Both mechanisms use the same one-tenant-to-one-placement collision rule.

Adapter-enforced vs database-enforced

By default, isolation is adapter-enforced (the adapter controls routing). You can opt into database-enforced isolation by giving each tenant a dedicated Postgres role with USAGE on only its own schema - then the database itself blocks cross-schema access:

const tenancy = createKnexTenancy({
  manager,
  knex,
  strategy: "schemaPerTenant",
  schema: (tenant) => `tenant_${tenant.id}`,
  role: (tenant) => `tenant_${tenant.id}_role`, // optional: database-enforced
});

On Lucid (AdonisJS) it's the same options via createLucidTenancy - register your models with tenantModels, and your Lucid models are then scoped automatically:

const tenancy = createLucidTenancy({
  manager,
  database: db, // the Lucid Database service
  strategy: "schemaPerTenant",
  schema: (tenant) => `tenant_${tenant.id}`,
  tenantModels: [{ model: Post }],
  // role: (tenant) => `tenant_${tenant.id}_role`, // optional: database-enforced
});

What the database needs

The adapter's validate() refuses to run until these hold - set them up before serving. Run the DDL as a privileged/admin connection, then connect your app as the non-privileged app_runtime role:

-- 1. A non-privileged runtime role (no superuser, no RLS bypass):
create role app_runtime login nosuperuser nobypassrls;

-- 2. The central schema (default `public`) must EXIST and grant USAGE to the role:
grant usage on schema public to app_runtime;

-- 3. Per tenant: its own schema + tables, granted to the role:
create schema tenant_acme;
create table tenant_acme.posts (id text primary key, title text not null);
grant usage on schema tenant_acme to app_runtime;
grant select, insert, update, delete
  on all tables in schema tenant_acme to app_runtime;

Two rules validate() enforces, easy to miss:

• Tenant tables must not exist in the central schema. A public.posts shadows the tenant tables and fails validation (CENTRAL_SCHEMA_SHADOWS_TENANT_TABLE).
• The runtime role's default search_path must not resolve a tenant-table name - otherwise an unqualified query could hit the wrong place (DEFAULT_SEARCH_PATH_SHADOWS_TENANT_TABLE). Keep tenant tables only in their own schemas.

For database-enforced isolation, add a per-tenant role(tenant) whose role has USAGE on only its own schema - then the database itself rejects cross-schema access, not just the adapter.

Provisioning

Each tenant needs its schema created (and migrated) before use. Do that through the CLI's provisioning hooks - you supply how to create the schema and run your migrator; the CLI orchestrates per tenant.

npx tenancyjs-cli tenant provision acme
npx tenancyjs-cli tenant migrate acme