Multi-tenancy

DrizzleD1TenantDatasource<TSchema> extends DrizzleD1Datasource and restricts queries to one tenant. It is abstract: extend it and declare a unique readonly name. Tables used with the scoped helpers need a string tenantId column.

src/db/schema.ts

import {relations} from 'drizzle-orm';
import {integer, sqliteTable, text} from 'drizzle-orm/sqlite-core';

export const customers = sqliteTable('customers', {
  id: text('id').primaryKey(),
  tenantId: text('tenant_id').notNull(),
  name: text('name').notNull(),
});

export const orders = sqliteTable('orders', {
  id: text('id').primaryKey(),
  tenantId: text('tenant_id').notNull(),
  customerId: text('customer_id'),
  status: text('status').notNull().default('open'),
  total: integer('total').notNull(),
  createdAt: integer('created_at', {mode: 'timestamp'}).notNull().$defaultFn(() => new Date()),
});

export const customerRelations = relations(customers, ({many}) => ({orders: many(orders)}));
export const orderRelations = relations(orders, ({one}) => ({
  customer: one(customers, {fields: [orders.customerId], references: [customers.id]}),
}));

src/db/order-datasource.ts

import {desc, eq} from 'drizzle-orm';
import type {Context} from 'hono';
import {DrizzleD1TenantDatasource} from '@ltv/cwb/datasources';
import * as schema from './schema';

const {orders} = schema;
type Order = typeof orders.$inferSelect;

export class OrderDatasource extends DrizzleD1TenantDatasource<typeof schema> {
  readonly name = 'orders';

  // tenantId: explicit tenant for background jobs; otherwise c.get('tenantId') or c.get('tenant').id
  constructor(c: Context, tenantId?: string) {
    super(c, {schema, tenantId});
  }

  async listOpen(page: number, limit: number): Promise<{rows: Order[]; total: number}> {
    const where = eq(orders.status, 'open');
    const [rows, total] = await Promise.all([
      this.scopedSelect(orders, {where, orderBy: desc(orders.createdAt), limit, offset: (page - 1) * limit}),
      this.scopedCount(orders, where),
    ]);
    return {rows, total};
  }
}

Tenant resolution

The tenant id is resolved from the first available source:

1. options.tenantId passed to the constructor;
2. c.get('tenantId');
3. c.get('tenant')?.id.

Your authentication middleware sets one of the context variables. Background and cron code has no request tenant, so pass options.tenantId there.

src/middleware/resolve-tenant.ts

import type {MiddlewareHandler} from 'hono';
import {ApiError} from '@ltv/cwb';

export const resolveTenant: MiddlewareHandler<{Variables: {tenantId: string}}> = async (c, next) => {
  const tenantId = await tenantFromVerifiedToken(c.req.header('authorization'));
  if (!tenantId) throw ApiError.unauthorized();
  c.set('tenantId', tenantId);
  await next();
};

Register it with createApp({middleware: [resolveTenant]}) or on the tenant module (defineModule({middleware: [resolveTenant], ...})) so it runs before the routes. See Module middleware.

• No tenant, no query. Reading tenantId, or calling where(), values(), a scoped* helper or withCache without a tenant, throws an ApiError with code TENANT_REQUIRED (403). Resolution is lazy: constructing the datasource never throws.
• Isolation. Tenant state belongs to each instance, never to the module, so concurrent requests can't leak tenants into each other.

Derive the tenant from authenticated data

The scoped helpers only enforce the tenant you give them. Resolve it from a verified token or session, never from an unauthenticated header or body field. (The basic Worker example reads X-Tenant-Id for demonstration only.)

Scoped helpers

Helper	Description
scopedSelect(table, {where?, orderBy?, limit?, offset?})	Resolves to the rows of SELECT * WHERE tenantId = current AND where [ORDER BY] [LIMIT] [OFFSET]. Returns rows, not a builder
scopedCount(table, where?)	Number of the current tenant's rows matching where
scopedInsert(table, row)	Insert with tenantId set to the current tenant, overriding any value in row
scopedInsertMany(table, rows)	Same, for several rows
scopedUpdate(table, set, ...conditions)	Update the current tenant's rows; tenantId in set is dropped
scopedUpsert(table, row, {target, set})	Insert, or update on conflict only if the conflicting row belongs to the current tenant
scopedDelete(table, ...conditions)	Delete the current tenant's rows matching the conditions
tenantId	Current tenant id (throws TENANT_REQUIRED without one)
where(table, ...conditions)	Tenant filter for custom queries; undefined conditions are ignored
values(row or rows)	Adds the current tenantId to insert values

Reads

const page = await ds.scopedSelect(orders, {
  where: and(eq(orders.status, 'open'), gte(orders.total, 100)),
  orderBy: [desc(orders.createdAt), orders.id],
  limit: 20,
  offset: 40,
});
const openCount = await ds.scopedCount(orders, eq(orders.status, 'open'));

• orderBy takes a column, an SQL expression or an array of them.
• limit and offset must be non-negative integers, and offset requires limit; otherwise a RangeError is thrown.
• scopedSelect(table) with no options returns all of the tenant's rows. The pre-0.2 form scopedSelect(table, condition) throws a TypeError.

Writes

import {sql} from 'drizzle-orm';

await ds.scopedInsert(orders, {id: 'ord_1', total: 100});
const [created] = await ds.scopedInsert(orders, {id: 'ord_2', total: 50}).returning();
await ds.scopedInsert(orders, {id: 'ord_1', total: 100}).onConflictDoNothing();
await ds.scopedInsertMany(orders, [{id: 'ord_3', total: 10}, {id: 'ord_4', total: 20}]);

const updated = await ds.scopedUpdate(orders, {status: 'paid'}, eq(orders.id, 'ord_1')).returning();

const result = await ds.scopedUpsert(
  orders,
  {id: 'ord_5', total: 70},
  {target: orders.id, set: {total: sql`excluded.total`}}
);
if (result.meta.changes === 0) {
  // the id belongs to another tenant: nothing was inserted or updated
}

await ds.scopedDelete(orders, eq(orders.id, 'ord_4'), eq(orders.status, 'open'));

Write helpers return narrow queries, not Drizzle builders:

Type	You can
ScopedWriteQuery	await it for the D1 result (D1Result), or call .returning() for the affected rows
ScopedInsertQuery	The same, plus .onConflictDoNothing()

Nothing that would replace or bypass the tenant filter, such as .where(), .onConflictDoUpdate() or $dynamic(), can be chained. Narrow queries can't be passed to batch(); for batches, use ds.db with ds.where() and ds.values():

await ds.batch([
  ds.db.insert(orders).values(ds.values({id: 'ord_6', total: 30})),
  ds.db.update(orders).set({status: 'archived'}).where(ds.where(orders, eq(orders.status, 'paid'))),
]);

Custom queries, joins and raw SQL

ds.db is unscoped by design, for system or admin queries. When you use it for tenant data, add the filter yourself with ds.where().

import {and, eq} from 'drizzle-orm';
const {customers} = schema;

// Relational query API
await ds.db.query.orders.findMany({where: ds.where(orders, eq(orders.status, 'open'))});

// Joins: every tenant-scoped table needs its own filter
await ds.db
  .select()
  .from(orders)
  .innerJoin(customers, eq(orders.customerId, customers.id))
  .where(and(ds.where(orders), ds.where(customers)));

// Nested relational includes (needs relations in the schema): filter each included table
await ds.db.query.customers.findMany({
  where: ds.where(customers),
  with: {orders: {where: (t, {eq}) => eq(t.tenantId, ds.tenantId)}},
});

Unscoped writes affect every tenant

• ds.db.delete(orders) or ds.db.update(orders).set(x) without .where(ds.where(orders)) changes every tenant's rows. Use scopedDelete and scopedUpdate.
• ds.db.update(orders).set(body) lets a client move a row to another tenant through body.tenantId. scopedUpdate drops tenantId, and so does withoutTenantId(body).
• ds.db.insert(orders).values(...).onConflictDoUpdate(...) is unscoped on conflict. Use scopedUpsert.

Raw sql with OR

Conditions passed to where(), tenantWhere() and the scoped* helpers are each wrapped in parentheses, so sql`status = 'open' or status = 'draft'` can't escape the tenant filter. If you combine a tenant condition with raw SQL yourself (for example with Drizzle's and() directly on ds.db), parenthesize the raw fragment, or pass it through ds.where(table, fragment) instead.

Tenant-aware caching

withCache entries live under {name}:tenant:{encodeURIComponent(tenantId)}. A caller-supplied options.namespace is nested inside it, as ...:tenant:{id}:{namespace}. One tenant's cached data is never served to another, and clear() removes only the current tenant's entries.

src/db/order-datasource.ts (inside OrderDatasource)

summary(): Promise<{open: number}> {
  return this.withCache('summary', {}, async () => ({
    open: await this.scopedCount(orders, eq(orders.status, 'open')),
  }), {ttl: 60});
}
// tenant "acme": key orders:tenant:acme:summary:<sha256 of params>

Background jobs

Code without a request tenant, such as an internal job endpoint or work triggered by a queue, passes the tenant explicitly. With the OrderDatasource constructor above:

const ds = new OrderDatasource(c, message.body.tenantId); // explicit tenant wins over context variables
await ds.scopedUpdate(orders, {status: 'expired'}, eq(orders.status, 'open'));

The datasource still needs a Hono context (for c.env bindings and the logger); only the tenant comes from the explicit value.

Standalone helpers

These work with any Drizzle SQLite database, without a datasource:

Helper	Description
tenantWhere(table, tenantId, ...conditions)	Scoped where clause
withTenantId(values, tenantId)	Sets tenantId on one row or many rows, overriding existing values
withoutTenantId(values)	Removes tenantId from update values

Types: TenantScopedTable, TenantTable, TenantInsert<T>, TenantUpsertSet<T>, WithTenantId<V>, ScopedSelectOptions, ScopedOrderBy, ScopedWriteQuery, ScopedInsertQuery, DrizzleD1TenantDatasourceOptions.