Multi tenancy with query prefixes

This guide is about serving many tenants from one database while keeping each tenant’s data apart. The technique it describes — giving every tenant its own PostgreSQL schema (a namespace, not to be confused with a lode Schema(row)) — is the most thorough kind of isolation short of a database per tenant: the same tables (users, posts, …) exist once inside each tenant schema, and a request reads and writes only the schema belonging to the tenant it serves.

In PostgreSQL the active set of schemas is the connection’s search_path. With search_path set to tenant_42, an unqualified SELECT * FROM users resolves to tenant_42.users; flip the path to tenant_99 and the same SQL now reads a different tenant’s rows. The alternative to switching the path is to write the schema into the query itself as a qualified name: SELECT * FROM tenant_42.users.

Elixir’s Ecto wraps both of these in a single option, :prefix. You pass prefix: "tenant_42" to a repo call (Repo.all(query, prefix: "tenant_42")), stamp it onto a struct with Ecto.put_meta(user, prefix: "tenant_42"), or pin it to a schema module with @schema_prefix. Ecto then renders the prefix as the PostgreSQL schema qualifier on every table in the query. Migrations take the same option, so flush() and Repo.insert inside a migration target a chosen tenant schema.

lode supports this directly. query.prefix qualifies reads and changeset.put_prefix qualifies writes (with repo.insert_prefixed / update_prefixed as call-level overrides). When set, every table in the query — FROM, joins, and the target of an insert/update — renders as "<schema>"."<table>", exactly like Ecto’s :prefix:

// READS
let posts =
  query.from("posts", "p")
  |> query.prefix("tenant_42")
  |> query.where(expr.gt(post_visits(), 0))
repo.all(r, post_schema(), posts)
//  SELECT * FROM "tenant_42"."posts" AS p WHERE (p."visits" > $1)

// WRITES — prefix carried on the changeset (Ecto's struct-meta prefix)
let cs =
  changeset.cast(post, post_schema(), params, field.fields(["title"]))
  |> changeset.put_prefix("tenant_42")
repo.insert(r, post_schema(), cs)
//  INSERT INTO "tenant_42"."posts" (...) VALUES (...)

// or override at the call site
repo.insert_prefixed(r, post_schema(), cs, prefix: "tenant_42")

The active tenant is just a value you thread — query.prefix(q, tenant.schema) composes like any other builder step. query.prefix also applies to repo.update_all / repo.delete_all (which take a query).

The two connection-based strategies below are still worth knowing: a per-tenant Repo (Strategy 1, search_path) keeps queries free of qualifiers entirely and sets the tenant once per request, while qualified source strings (Strategy 2) suit one-off admin queries. Use query.prefix / put_prefix for the per-query/per-write knob, and search_path for the per-connection one.

Strategy 1: one Repo per tenant via `search_path`

The cleanest workaround leans on the single most important fact about a lode repo: a Repo is a plain value, not a module and not a global. You build one with repo.new(adapter), where the adapter wraps a pog connection (postgres.new(conn)). Nothing says you may only build one. Give a connection a tenant’s search_path, wrap it, and you have a Repo value whose every query is automatically scoped to that tenant — no prefix option required, because the connection already knows which schema to read.

The one library call you need is repo.query_raw, which runs arbitrary SQL on a repo’s connection. Set the path once, right after building the repo:

import lode/adapters/postgres
import lode/error.{type LodeError}
import lode/repo.{type Repo}
import gleam/erlang/process
import gleam/option.{Some}
import gleam/result
import pog

/// Build a Repo whose connection is pinned to one tenant's PostgreSQL schema.
/// Every query through the returned Repo resolves unqualified table names
/// against `schema_name` first.
pub fn repo_for_tenant(schema_name: String) -> Result(Repo, LodeError) {
  let assert Ok(started) =
    pog.default_config(process.new_name("tenant_pool"))
    |> pog.host("localhost")
    |> pog.database("app")
    |> pog.user("user")
    |> pog.password(Some("pass"))
    |> pog.rows_as_map(True)
    |> pog.start
  let r = repo.new(postgres.new(started.data))

  // Point the connection at the tenant schema, falling back to "public".
  use _ <- result.map(repo.query_raw(
    repo: r,
    sql: "SET search_path TO " <> quote_ident(schema_name) <> ", public",
    params: [],
  ))
  r
}

schema_name reaches the database as a SQL identifier, not a bound parameter (SET search_path does not take placeholders), so it must be a value you control or one you have quoted and validated — never raw tenant input spliced in unchecked. A minimal guard:

import gleam/string

/// Double-quote an identifier and reject anything that isn't a plain
/// `[a-z0-9_]` schema name. Tenant identifiers should come from your own
/// allocation table, not from request data, but validate regardless.
fn quote_ident(name: String) -> String {
  let ok =
    string.to_graphemes(name)
    |> list_all(fn(c) {
      string.contains("abcdefghijklmnopqrstuvwxyz0123456789_", c)
    })
  case ok && name != "" {
    True -> "\"" <> name <> "\""
    False -> panic as { "unsafe schema identifier: " <> name }
  }
}

(Use gleam/list’s all for list_all; it is inlined here only to keep the example self-contained.)

With that in place, “which tenant am I serving” becomes simply “which Repo value do I pass”:

import lode/query
import person.{person_schema}

pub fn list_people_for(r: Repo) -> Result(List(person.Person), LodeError) {
  // No prefix anywhere — `r` is already tenant-scoped.
  repo.all(r, person_schema(), query.from(source: "people", alias: "p"))
}

This is the part of the design that pays off here. In Elixir, a process can have one implicitly-current repo, and serving the right tenant under a dynamic repo means juggling put_dynamic_repo/1 and the process dictionary so that an unqualified Repo.all lands on the right connection. lode dropped the process-dictionary “current repo” entirely (see DESIGN.md in the repository, §11: “Process-dictionary dynamic repo — dropped (pass repo explicitly)”; the rationale is in the §1 mapping table under BEAM processes). The active tenant is never ambient state — it is an argument. A web handler resolves the tenant from the request, calls repo_for_tenant, and threads the resulting Repo through its context. There is no global to forget to set and no risk of one request’s prefix leaking into another’s.

A few operational notes carried over from Ecto’s version:

Pool churn. Starting a fresh pog pool per request is wasteful. In production you would keep a pool per tenant (or a shared pool and issue SET search_path per checked-out connection / per transaction with SET LOCAL), exactly as Ecto recommends running the prefix at the connection level rather than re-opening connections.
SET search_path persists for the life of the connection, so on a shared pool prefer SET LOCAL search_path TO … inside a repo.transaction, which resets at commit and cannot bleed into the next checkout.
, public keeps shared extensions and lookup tables reachable. Drop it if you want a tenant schema to be the only thing in scope.

Strategy 2: schema-qualified table names

When you would rather not touch search_path — for instance, you have one ordinary repo and want a single query to reach into a specific tenant — write the schema qualifier directly into the source string. Both query.from and schema.new take the table name as a plain String, and PostgreSQL accepts a schema-qualified name there just as readily as a bare one:

let tenant_people =
  query.from(source: "tenant_42.people", alias: "p")

The renderer quotes the source as written, so "tenant_42.people" becomes the qualified reference in the SQL. The catch is that the qualifier is now baked into the value, so a schema you want to reuse across tenants has to be built per tenant with the qualifier interpolated:

import lode/schema.{type Schema}
import lode/types/primitive
import lode/value.{VInt, VString}
import gleam/dict
import gleam/result

pub fn people_schema_for(tenant: String) -> Schema(person.Person) {
  schema.new(
    source: tenant <> ".people",
    fields: [
      schema.primary_key("id", primitive.id()),
      schema.field("first_name", primitive.string()),
      schema.field("last_name", primitive.string()),
      schema.field("age", primitive.integer()),
    ],
    load: fn(row) {
      use id <- result.try(schema.require_int(row, "id"))
      use first_name <- result.try(schema.require_string(row, "first_name"))
      use last_name <- result.try(schema.require_string(row, "last_name"))
      use age <- result.try(schema.require_int(row, "age"))
      Ok(person.Person(id:, first_name:, last_name:, age:))
    },
    dump: fn(p: person.Person) {
      dict.from_list([
        #("id", VInt(p.id)),
        #("first_name", VString(p.first_name)),
        #("last_name", VString(p.last_name)),
        #("age", VInt(p.age)),
      ])
    },
  )
}

The same validation caution applies: tenant is interpolated into a SQL identifier, so it must come from your tenant registry, not from untrusted input.

Compared to Ecto’s :prefix, this is more verbose — you carry the qualifier on every from/schema.new rather than passing it once per call — and it does not compose as freely, since a base query already names its source. For a handful of cross-tenant operations it is the most direct tool; for serving whole requests in one tenant, Strategy 1’s per-tenant Repo is cleaner. Most applications reach for search_path and keep this for the occasional admin query that must span or target a specific schema.

Migrations across tenant schemas

Each tenant schema holds its own copy of every table, so the same migration list must run once per tenant schema. lode’s migrator (see Getting Started) tracks applied versions in a schema_migrations table on whatever search_path its repo connection has — so the natural way to migrate tenant tenant_42 is to run the migrator against a repo built with repo_for_tenant("tenant_42"). The search_path makes both the schema_migrations bookkeeping table and the migration’s DDL land inside that tenant’s namespace, giving each tenant an independent migration history:

import lode/migrator

pub fn migrate_tenant(
  tenant: String,
  migrations: List(migration.Migration),
) -> Result(Nil, LodeError) {
  use r <- result.try(repo_for_tenant(tenant))
  let assert Ok(_) = migrator.run(r, migrations, ["migrate"])
  Ok(Nil)
}

To migrate every tenant, fold this over your list of tenant schema names (which you keep in a registry table on public). If you instead qualify names (Strategy 2), the migration DDL itself must name the schema — migration.execute(up: "CREATE TABLE tenant_42.people (...)", down: ...) — which loses the migrator’s per-schema version tracking, so the search_path approach is preferable for migrations.

Creating a tenant schema

Before a tenant’s tables can be migrated, the PostgreSQL schema must exist. There is no DDL builder for CREATE SCHEMA, so use the raw-SQL migration.execute escape hatch (or a one-off repo.query_raw when you provision a tenant):

import lode/migration
import lode/migration/ddl

pub fn create_tenant_schema(tenant: String) -> migration.Migration {
  migration.new(20_260_614_090_000)
  |> migration.execute(
    up: "CREATE SCHEMA IF NOT EXISTS " <> tenant,
    down: "DROP SCHEMA IF EXISTS " <> tenant <> " CASCADE",
  )
}

Provisioning a new tenant is therefore two steps: create its schema (the migration above, or a repo.query_raw(r, "CREATE SCHEMA …", []) against an admin repo), then run the table migrations against it with migrate_tenant. As in Ecto, the table-creating migrations are written once with unqualified names and gain their tenant namespace from the connection’s search_path.

Schema/namespace DDL. migration.create_schema(name) / migration.drop_schema(name) emit CREATE SCHEMA / DROP SCHEMA ... CASCADE (auto-reversible) for PostgreSQL schemas (namespaces); the table migrations still run against a tenant via the connection’s search_path as above. TASK:ddl-coverage

Choosing a strategy

Ecto’s :prefix is one knob that covers per-query, per-struct, and per-schema prefixing. lode replaces it with two explicit techniques, and the choice between them is really a choice about where the tenant lives:

In the connection (Strategy 1, search_path). Best default. The tenant is the Repo value, set once per request; queries and migrations stay free of qualifiers. This is the approach that fits the design — repos are first-class values, so “current tenant” is data you pass, not global state you mutate.
In the query (Strategy 2, qualified source strings). For one-off cross-tenant or admin queries where switching the path is more ceremony than the single query is worth.

For the direct per-query/per-write equivalent of Ecto’s :prefix, use query.prefix / changeset.put_prefix (top of this guide); the two strategies here are the per-connection complement. What they do give you is the same data isolation, with the active tenant made explicit rather than ambient.