/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 26 Jun 20262 min readv0.3.3View 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