Multiple Schemas

A schema is a namespace inside one database (a database, in MySQL terms). UQL takes one in two places: on the entity, pinning a table wherever it lives, and on the pool, defaulting every entity that names none. The entity wins when both are set, and with neither, tables stay unqualified and resolve through the connection’s search_path.

Qualified or not, entities join in a single statement. SQLite and MongoDB ignore schema entirely.

A fixed layout: schema on the entity

For a layout that doesn’t change per request, such as one schema per bounded context:

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

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

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

You write

import { pool } from './uql.config.js';
import { Order } from './shared/models/index.js';

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"

The schema goes in schema, not in name

name: 'sales.Order' is rejected when the entity is defined. name escapes as one identifier, so the query would build fine and then fail at the database.

A schema per tenant: schema on the pool

When the schema changes per request, give each tenant a pool. The entity classes stay untouched:

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

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

An entity with its own schema keeps it, so shared reference data can sit beside the per-tenant tables:

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

UQL writes the schema into each statement rather than setting search_path on the session, so it survives a pooler that hands out a different session per transaction.

Two entities, one table name

A Company in two schemas is two classes with different TypeScript names mapping the same table name:

@Entity({ schema: 'crm', name: 'Company' })
export class CrmCompany {
  @Id({ type: Number }) id?: number;
  @Field({ type: String }) label?: string;
}

@Entity({ schema: 'billing', name: 'Company' })
export class BillingCompany {
  @Id({ type: Number }) id?: number;
  @Field({ type: String }) vat?: string;
}

Entities are keyed by the class, so nothing collides. The exception is HTTP, where the class name becomes the route (/crm-company, /billing-company); two classes landing on the same route make the handler throw at startup instead of silently dropping one.

Over HTTP

Pick the pool from the verified request context, never from the URL or query string, or any authenticated caller reads any tenant by editing a URL:

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

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

See HTTP for mounting, and Multi-tenancy for the other strategy: one shared schema with a security filter on a tenant column.

Migrations

uql-migrate generate:entities emits CREATE SCHEMA IF NOT EXISTS for each schema before the tables that go in it; MySQL and MariaDB read that as a database. drift:check and sync read back the schemas your entities name, so a qualified table diffs like any other.