/DB

Multi-tenant tables

Two patterns for serving many tenants, a shared table keyed by tenant or a table per tenant, and when to reach for each.

updated 5 Jun 20262 min readv0.3.2View as Markdown

There are two common ways to isolate tenant data. Pick by your isolation and scale needs; the library supports both directly.

Shared table, tenant column

The simplest model: one table for everyone, with a tenant_id on every row and on every query. Best for many small tenants.

[Table("documents")]
public partial class Document
{
    [PrimaryKey, Default(DbDefaults.Guid.Random)]
    public Guid Id { get; set; }

    public Guid TenantId { get; set; }

    public required string Title { get; set; }
}

Every read and write carries the tenant filter:

public Task<List<Document>> ListAsync(Guid tenantId) =>
    db.ExecuteAsync(d => d.Documents.ToListAsync(x => x.TenantId == tenantId));
WARNING
Isolation is only as strong as your discipline: a query that forgets TenantId leaks across tenants. Funnel all access through a per-tenant repository, and back it up with PostgreSQL row-level security so the database enforces the boundary too.

Table per tenant, with dynamic tables

For strong isolation or very large tenants, give each tenant its own table (documents_tenant_42) and bind the name at runtime with a [TableType]. One declared shape serves every tenant, and results stay fully typed.

[TableType]
public partial class Document
{
    [PrimaryKey, Default(DbDefaults.Guid.Random)]
    public Guid Id { get; set; }
    public required string Title { get; set; }
}
static string TableFor(Guid tenantId) => $"documents_{tenantId:N}";

public Task<List<Document>> ListAsync(Guid tenantId) =>
    db.ExecuteAsync(d => d.DynamicTable<Document>(TableFor(tenantId))
        .Query(x => x.Title != "")
        .ToListAsync());

public Task ProvisionAsync(Guid tenantId) =>
    db.ExecuteAsync(d => d.DynamicTable<Document>(TableFor(tenantId)).InstantiateAsync());

InstantiateAsync creates a tenant's table on demand from the declared shape, and the table name is a SQL identifier you control, never user input. See Dynamic tables for the full runtime API.

Choosing

Shared table + tenant_id Table per tenant ([TableType])
Isolation logical, app-enforced physical, per table
Tenant count thousands of small tenants fewer, larger tenants
Provisioning none InstantiateAsync per tenant
Cross-tenant reports one query union across tables