Egon ORM

Appearance

Multi-tenancy with database schemas ​

Give each tenant its own database schema (a Postgres schema, a MySQL database) with the same tables, and choose the schema per request. The schema classes stay the same for every tenant.

Setup ​

The code of this recipe depends on these declarations: the imports, the schema classes, the repo, and the migration that creates the tables. Run the recipe in an empty database of its own: each recipe creates its own tables, and some have the same names (posts) with other columns.

ts
import { postgres } from "@egon-orm/postgres";
import { cast, createRepo, from, type Repo, Schema, t } from "egon-orm";
import { createMigrator, defineMigration } from "egon-orm/migration";

class Project extends Schema("projects", {
  id: t.integer().primaryKey().dbGenerated(),
  name: t.string(),
}) {
  static changeset(project: Project | typeof Project, params: unknown) {
    return cast(project, params, ["name"]).validateRequired(["name"]).uniqueConstraint("name");
  }
}

const adapter = postgres({ connectionString: process.env.DATABASE_URL });

Creating a tenant's tables ​

The same migration runs once per tenant, with the schema in its operations:

ts
// The tables of one tenant, created in that tenant's database schema.
const tenantTables = (schema: string) =>
  defineMigration({
    up(m) {
      m.createSchema(schema, { ifNotExists: true });
      m.createTable(
        "projects",
        (table) => {
          table.id({ type: "integer" });
          table.text("name").notNull().unique();
        },
        { schema },
      );
    },
    down(m) {
      m.dropTable("projects", { schema });
      m.dropSchema(schema);
    },
  });

// `tenant` is one of your own identifiers (a slug), never raw user input.
export async function provision(tenant: string) {
  const schema = `tenant_${tenant}`;
  await createMigrator({
    adapter,
    table: `${schema}_migrations`, // a history per tenant, beside the tables it creates
    migrations: [{ version: 20261020090000, name: "tenant_tables", migration: tenantTables(schema) }],
  }).up();
}

Each tenant gets its own tracking table, so a new tenant starts with every migration pending, and a migration added later is applied to each tenant by running provision again for each.

Choosing the schema per call ​

Every read and write takes a schema option:

ts
const repo = createRepo({ adapter });

export function projectsOf(tenant: string) {
  return repo.all(
    from(Project, "p").orderBy(({ p }) => p.name),
    { schema: `tenant_${tenant}` },
  );
}

export function addProject(tenant: string, params: unknown) {
  return repo.insert(Project.changeset(Project, params), { schema: `tenant_${tenant}` });
}

Or a repo per tenant ​

A repo created with schema uses it by default. Repos are cheap: several can share one adapter, and so one connection pool.

ts
// One repo per tenant, sharing the adapter (and its pool): every call defaults to the schema.
const tenantRepos = new Map<string, Repo>();

export function repoFor(tenant: string): Repo {
  let tenantRepo = tenantRepos.get(tenant);
  if (tenantRepo === undefined) {
    tenantRepo = createRepo({ adapter, schema: `tenant_${tenant}` });
    tenantRepos.set(tenant, tenantRepo);
  }
  return tenantRepo;
}

The examples below start from these rows, after creating the tables:

ts
await provision("acme");
await provision("globex");
ts
await repoFor("acme").insertOrThrow(Project.changeset(Project, { name: "Anvil" }));
const acme = await repoFor("acme").all(from(Project, "p").orderBy(({ p }) => p.name));

Where the schema comes from, strongest first: a query's own .schema(…), a schema class that declares one, the call's schema option, the repo's default. A write of a loaded row goes back to the schema the row was read from. See Parameters and options.

Good to know

  • Names are identifiers. A schema name is written into the SQL as a quoted identifier, never as a parameter. Build it from your own tenant ids (a slug you validated), not from raw input.
  • Constraint names are per table, so uniqueConstraint("name") matches the violation in every tenant's schema.
  • SQLite has no schemas; the schema option names an attached database there.
  • Shared tables (users, plans) can live in public: give their schema class Schema("plans", fields, { schema: "public" }) so that no tenant default moves them.

See also ​