> 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

# Indexes

> Declare single-column, composite, partial, JSON, full-text and vector indexes on your entities.

Source: https://uql-orm.dev/entities/indexes

Use `@Field({ index })` to index one column and the class-level `@Index` for anything more. [Migrations](https://uql-orm.dev/migrations.md) create and update them.

```ts
import { Entity, Id, Field, Index } from 'uql-orm';

@Index((log) => [log.entityType, log.entityId, log.createdAt], {
  name: 'audit_lookup_idx',
})
@Entity()
export class AuditLog {
  @Id({ type: Number })
  id?: number;

  @Field({ type: String, index: true }) // named AuditLog__action_idx
  action?: string | null;

  @Field({ type: String })
  entityType?: string | null;

  @Field({ type: String })
  entityId?: string | null;

  @Field({ type: 'timestamptz' })
  createdAt?: Date | null;
}
```

`@Index` reads columns off the class, so a typo is a compile error and an editor rename updates every index.

Every foreign key column gets an index of its own, unless the primary key, a unique column or another index already leads with it. A partial, full-text, GIN, GiST, BRIN, vector or prefix (`length`) index does not count. `index: false` opts a column out.

## Index options

| Option | What it does |
| - | - |
| `name` | Index name. Defaults to `<table>__<columns>_idx`. |
| `unique` | Enforce uniqueness, e.g. one email per tenant: `@Index((u) => [u.email, u.tenantId], { unique: true })`. |
| `type` | Engine-specific index type, see [Index types](#index-types). |
| `where` | Makes it a [partial index](#partial-indexes). |
| `include` | Extra columns stored in the index (`INCLUDE`), `(user) => [user.name]`. Postgres and CockroachDB. |
| `distance` | Vector metric, such as `'cosine'` or `'l2'`. Required for `'hnsw'`, `'ivfflat'` and `'vector'`. |
| `m`, `efConstruction`, `lists` | Vector tuning: HNSW connections per node; build-time candidate list (`ef_construction`, CockroachDB’s `build_beam_size`, libSQL’s `insert_l`); IVFFlat lists. |
| `config` | `fulltext` stemming language, such as `'english'`. Default `'simple'`. PostgreSQL, CockroachDB, MongoDB. |

### Index types

| Engine | `type` values |
| - | - |
| PostgreSQL | `btree`, `hash`, `gin`, `gist`, `brin`, `hnsw`, `ivfflat`, `fulltext` |
| CockroachDB | `btree`, `gin`, `gist`, `vector`, `hnsw` (both its vector index), `fulltext` |
| MySQL | `btree`, `hash`, `fulltext` |
| MariaDB | `btree`, `hash`, `fulltext`, `vector` |
| MSSQL | `btree` |
| SQLite | any, each built as a plain index, so Postgres entities migrate unchanged |

A type or option the engine cannot express throws, naming the index, when the migration is generated. On MongoDB, `order` maps to `1`/`-1`, `fulltext` creates a `text` index, and SQL-only options are refused.

## Per-column options

Each entry is a member, a `sql` expression over members, or an object with more options:

```ts
import { Entity, Field, Id, Index, type Json, sql } from 'uql-orm';

@Index((note) => [note.tenantId, { column: note.createdAt, order: 'desc' }]) // keyset pagination
@Index((note) => [sql`lower(${note.email})`], { unique: true }) // case-insensitive unique
@Index((note) => [{ column: note.body, length: 64 }]) // MySQL TEXT needs a prefix
@Index((note) => [{ column: note.data, opsClass: 'jsonb_path_ops' }], {
  type: 'gin',
})
@Entity()
export class Note {
  @Id({ type: Number }) id?: number;
  @Field({ type: String }) tenantId?: string | null;
  @Field({ type: 'timestamptz' }) createdAt?: Date | null;
  @Field({ type: String }) email?: string | null;
  @Field({ type: 'text' }) body?: string | null;
  @Field({ type: 'jsonb' }) data?: Json<{ source?: string }> | null;
}
```

| Option | What it does | Engines |
| - | - | - |
| `column` | The member, or `sql` for an expression. | expressions: all but MariaDB and MSSQL |
| `order` | `'asc'` (default) or `'desc'`, the order stored in the index. | all |
| `length` | Index only the first *n* characters. Required for `TEXT` on MySQL. | MySQL, MariaDB |
| `nulls` | `'first'` or `'last'`. | Postgres |
| `opsClass` | Operator class, e.g. `jsonb_path_ops`. | Postgres |
| `weight` | How much a match in this column counts in [full-text](#full-text-indexes) ranking. | all with full-text search |
| `jsonPath` | Index one path inside a JSON column. | Postgres, CockroachDB, SQLite, MySQL |
| `jsonArray` | Index each element of a JSON array. | MySQL |

### JSON indexes

`jsonPath` indexes one path in a JSON column, and `jsonArray` each element of an array. Both match the expression your queries compile to, which a hand-written index often does not. `path` is checked against the column’s type, so a typo is a compile error; `type` is how the value is read:

```ts
@Index((account) => [
  { column: account.settings, jsonPath: { path: 'theme.color', type: String } },
])
@Index((account) => [
  { column: account.tags, jsonArray: { type: String, length: 64 } },
])
@Entity()
export class Account {
  @Id({ type: Number })
  id?: number;

  @Field({ type: 'jsonb' })
  settings?: Json<{ theme: { color: string } }> | null; // serves 'settings.theme.color': 'red'

  @Field({ type: 'jsonb' })
  tags?: Json<string[]> | null; // serves tags: { $all: ['admin'] }
}
```

On MySQL a string path needs a `length`, and a boolean path cannot be indexed. `jsonArray` serves `$all`, and an `$elemMatch` asking for one or several strings or numbers.

## Partial indexes

A partial index covers only rows matching `where`, so it stays small. With [soft delete](https://uql-orm.dev/entities/soft-delete.md), it can keep emails unique among live rows only:

```ts
@Index((user) => [user.email], { unique: true, where: { deletedAt: null } })
```

`where` is a `$where` predicate, checked against the entity, with values written in as literals. The entity’s own filters are not added. Prefer it to a callback returning [`sql`](https://uql-orm.dev/querying/raw-sql.md): the planner uses the index only when the query’s filter has the same shape, so ``sql`${user.archived} IS NOT TRUE` ``never serves `{ archived: { $ne: true } }`.

> **Engine support**
>
> MySQL and MariaDB have no partial indexes, so `where` throws there. SQL Server takes only comparisons, `$in`, `$isNull` and `$isNotNull` joined by `AND`. MongoDB takes equality, `$gt`, `$gte`, `$lt`, `$lte`, `$between`, `$in`, `$and` and `$or`, but no `null`, `$ne`, `$nin`, `sql` or `Date` value, so `{ deletedAt: null }` cannot be expressed.

## Full-text indexes

A `fulltext` index is what [`$text`](https://uql-orm.dev/querying/full-text.md) searches, on every engine that has full-text search. A column’s `weight` (1 to 99999, default 1) sets how much its matches count when `$sort: { $text }` ranks:

```ts
@Index((listing) => [{ column: listing.title, weight: 3 }, listing.description], {
  type: 'fulltext',
  config: 'spanish',
})
```

It builds a `GIN` index on PostgreSQL and CockroachDB, a `FULLTEXT` index on MySQL and MariaDB (plus one per column heavier than the lightest), and a weighted `text` index on MongoDB. See [column weights](https://uql-orm.dev/querying/full-text.md#column-weights).

## Vector indexes

A vector index serves [semantic search](https://uql-orm.dev/querying/semantic-search.md). `distance` is required on it, and a compile error on any other index. Match the metric your queries sort by: on MariaDB, a cosine query against a euclidean index scans the whole table.

```ts
@Index((article) => [article.embedding], { type: 'hnsw', distance: 'cosine', m: 16, efConstruction: 64 })
```

Changing `m` or `efConstruction` rebuilds it in the next migration. See [Semantic search](https://uql-orm.dev/querying/semantic-search.md#vector-indexes) for each engine’s vector indexes.

## Synchronization

`generate:entities` and `sync` create each index with every option the engine supports. An existing index is matched by name, then by columns, and rebuilt if it differs. An index you stop declaring is dropped (by a generated migration or `sync --unsafe`) only if uql named it or a declared index now takes its name. Indexes you named by hand are left alone.

`generate:from-db` goes the other way: a plain single-column index becomes `@Field({ index })`, anything else a full `@Index`.

[`drift:check`](https://uql-orm.dev/migrations.md#drift-detection) compares what each engine reports:

| Engine | Compared |
| - | - |
| Postgres | Columns and their order, uniqueness, access method, `INCLUDE` columns, operator class, a vector index’s distance |
| CockroachDB | The same, except nulls order and operator class |
| MariaDB, libSQL | Columns, uniqueness, a vector index’s distance |
| MySQL, MSSQL, SQLite | Columns and uniqueness |

Expressions are never compared, since the database reprints them in its own spelling. An unnamed partial index carries a hash of its `where` in its name (`Order__total_3fa9c1_idx`), so editing the predicate replaces the index. A partial index you name keeps its name, so editing its predicate is a migration you write.