Multiple Schemas

A schema is a namespace inside one database (in MySQL and MariaDB, a database). Set one on an entity to pin its table, or on the pool as the default for entities that name none; the entity’s wins. With neither, tables stay unqualified and resolve through the connection’s default, such as Postgres’s search_path.

Schemas work on Postgres, CockroachDB, MySQL, MariaDB and SQL Server. The SQLite family and MongoDB ignore schema.

A fixed layout: schema on the entity

Entities in different schemas still join in one statement:

import { Entity, Field, Id, ManyToOne } from 'uql-orm';

@Entity({ schema: 'crm' })
export class Customer {
  @Id({ type: Number }) id?: number;
  @Field({ type: String }) name?: string | null;
}

@Entity({ schema: 'sales' })
export class Order {
  @Id({ type: Number }) id?: number;
  @Field({ type: Number }) total?: number | null;
  @Field({ references: () => Customer }) customerId?: number | null;
  @ManyToOne({
    entity: () => Customer,
    references: (order) => order.customerId,
  })
  customer?: Customer;
}

You write

const orders = await pool.findMany(Order, {
  $select: { id: true, total: true },
  $populate: { customer: { $select: { name: true } } },
});

PostgreSQL

SELECT "Order"."id", "Order"."total",
       "customer"."id" "customer.id", "customer"."name" "customer.name"
FROM "sales"."Order" "Order"
LEFT JOIN "crm"."Customer" "customer" ON "customer"."id" = "Order"."customerId"

Put the schema in schema, not in name

name: 'sales.Order' is refused when the entity is defined, because name is escaped as a single identifier.

To map a table name that exists in two schemas, declare two classes, such as CrmCompany with @Entity({ schema: 'crm', name: 'Company' }) and BillingCompany with @Entity({ schema: 'billing', name: 'Company' }). Over HTTP the class name is the route (/crm-company), and two classes landing on the same route make the handler throw at startup.

A schema per tenant: schema on the pool

Give each tenant a pool with its own schema. Entities that name a schema keep it, so shared reference data can sit beside per-tenant tables:

import { PgQuerierPool } from 'uql-orm/postgres';

const tenantA = new PgQuerierPool(
  { connectionString: process.env.DATABASE_URL },
  { schema: 'tenant_a' },
);

@Entity()
export class Invoice {} // wherever the pool points
@Entity({ schema: 'public' })
export class Country {} // always public

UQL writes the schema into each statement instead of setting search_path, so it works behind a pooler that hands out a different session per transaction.

Over HTTP, pick the pool from the verified caller, never from the URL, or any signed-in caller can read any tenant by editing it:

import { createFetchHandler } from 'uql-orm/http';

const handler = createFetchHandler({
  include: [Invoice],
  pool: (request) => poolFor(tenantOf(request)),
});

For tenants sharing one schema, use a security filter on a tenant column instead.

Migrations

uql-migrate generate:entities creates each schema before its tables (CREATE SCHEMA IF NOT EXISTS, a database on MySQL and MariaDB). drift:check and sync read back the schemas your entities name, so a qualified table diffs like any other.