Indexes

Use @Field({ index }) to index one column and the class-level @Index for anything more. Migrations create and update them.

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.
where	Makes it a partial index.
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:

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

@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, it can keep emails unique among live rows only:

@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: 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 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:

@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.

Vector indexes

A vector index serves semantic search. 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.

@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 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 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.