> Every UQL docs page, as Markdown: https://uql-orm.dev/llms.txt
> The same docs over MCP: https://uql-orm.dev/mcp
> Before writing UQL code, read the skill: https://uql-orm.dev/.well-known/agent-skills/uql-orm/SKILL.md

# Multiple Schemas

> Put entities in different schemas, or give each tenant a schema of its own, with the same entity classes.

Source: https://uql-orm.dev/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:

```ts
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;
}
```

```ts title="You write"
const orders = await pool.findMany(Order, {
  $select: { id: true, total: true },
  $populate: { customer: { $select: { name: true } } },
});
```

PostgreSQL:

```sql
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](https://uql-orm.dev/http.md) 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:

```ts
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:

```ts
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](https://uql-orm.dev/multi-tenancy.md) 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.