Indexes

Indexes are declared on the entity: @Field({ index }) for a single column, @Index at the class level for composite and specialized ones.

Simple Indexes

@Entity()
export class User {
  @Id({ type: Number })
  id?: number;

  @Field({ type: String, index: true }) // Adds an auto-named index: <table>__<column>_idx
  email?: string;

  @Field({ type: String, index: 'display_name_idx' }) // Adds a named index
  displayName?: string;
}

Composite Indexes

A composite index answers a filter on all its columns directly; two single-column indexes leave the planner to combine two separate scans. An audit log searched by entityType and entityId and ordered by createdAt wants one index over the three:

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

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

  @Field({ type: String })
  entityType?: string; // e.g., 'User', 'Post'

  @Field({ type: String })
  entityId?: string; // e.g., 'uuid-123'

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

  @Field({ type: String })
  action?: string; // e.g., 'create', 'update'
}

Customizing Indexes

Option	Type	Description
name	string	Custom index name.
unique	boolean	Whether the index should enforce uniqueness. Defaults to false.
type	string	Dialect-specific index type (e.g., 'btree', 'hash', 'gin', 'gist', 'fulltext', 'hnsw', 'ivfflat').
where	string	Partial index condition (SQL WHERE clause).
include	string[]	Non-key columns stored in the index, so a query reading only these is answered from the index alone (INCLUDE). Postgres and CockroachDB.
distance	string	Distance metric (e.g., 'cosine', 'l2'), mapped to the operator class. Required for 'hnsw', 'ivfflat' and 'vector' index types.
m	number	HNSW: max connections per node.
efConstruction	number	HNSW: construction search depth.
lists	number	IVFFlat: number of inverted lists.

Per-Column Options

Each entry of the column list is a name by default, raw`...` to index an expression, or an object when it needs more:

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

// Keyset pagination: the stored order is what lets `ORDER BY "createdAt" DESC` use the index.
@Index(['tenantId', { column: 'createdAt', order: 'desc' }])
// Case-insensitive uniqueness, without a duplicate lowercase column to keep in sync.
@Index([raw`lower("email")`], { unique: true })
// MySQL and MariaDB *require* a prefix length to index a TEXT column at all.
@Index([{ column: 'body', length: 64 }])
// A smaller, faster GIN index for JSONB containment.
@Index([{ column: 'data', opsClass: 'jsonb_path_ops' }], { type: 'gin' })
@Entity()
export class Note {
  @Id({ type: Number })
  id?: number;

  @Field({ type: String })
  tenantId?: string;

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

  @Field({ type: String })
  email?: string;

  @Field({ type: 'text' })
  body?: string;

  @Field({ type: 'jsonb' })
  data?: Json<{ source?: string }>;
}

Option	Description	Supported on
column	The column name, or raw`...` for an expression index.	expressions: all but MariaDB
order	'asc' (default) or 'desc', the order stored in the index.	all
length	Index only the first n characters. Required for TEXT/BLOB on MySQL.	MySQL, MariaDB
nulls	'first' or 'last', where NULLs sort.	Postgres
opsClass	Operator class, e.g. jsonb_path_ops.	Postgres
jsonPath	Index one path inside a JSON column: { path, type }.	Postgres, CockroachDB, SQLite
jsonArray	Index each element of a JSON array: { type, length?, path? }.	MySQL

An option the engine cannot express throws when the migration is generated, naming the index, rather than being dropped quietly - each one is a hard error at the server, so a silent drop would only move the failure. On MongoDB, order maps to 1/-1 and type: 'fulltext' creates the text index $text needs; the SQL-only options are refused.

JSON Indexes

A path inside a JSON column is indexed with jsonPath, the elements of a JSON array with jsonArray. Both compile to the expression the query itself compiles to, which is what an engine matches an expression index by - one written by hand that spells the path differently is an index the planner never uses.

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

// 'settings.theme.color': 'red'
@Index([
  { column: 'settings', jsonPath: { path: 'theme.color', type: String } },
])
// tags: { $all: ['admin'] }
@Index([{ column: 'tags', jsonArray: { type: String, length: 64 } }])
@Entity()
export class Account {
  @Id({ type: Number })
  id?: number;

  @Field({ type: 'jsonb' })
  settings?: Json<{ theme: { color: string } }>;

  @Field({ type: 'jsonb' })
  tags?: Json<string[]>;
}

type is how the value is read - a number compared as a number has to be indexed as one - and path is checked against that column’s own payload, however deep, so a typo is a compile error rather than an index no query ever matches:

// the path is checked against the entity, so the decorator needs one to check against
@Index([
  { column: 'settings', jsonPath: { path: 'theme.colour', type: String } },
])
@Entity()
class Account {
  @Id({ type: Number }) id?: number;
  @Field({ type: 'jsonb' }) settings?: Json<{ theme: { color: string } }>;
}
// error: '"theme.colour"' is not assignable to '"theme" | "theme.color"'. Did you mean '"theme.color"'?

Unique Composite Index

One email per tenant, rather than one email overall:

@Index(['email', 'tenantId'], { unique: true })
@Entity()
export class User {
  @Id({ type: Number })
  id?: number;

  @Field({ type: String })
  email?: string;

  @Field({ type: String })
  tenantId?: string;
}

Dialect-Specific Types

@Index(['metadata'], { type: 'gin' }) // PostgreSQL GIN index for JSONB
@Entity()
export class Log {
  @Id({ type: Number })
  id?: number;

  @Field({ type: 'jsonb' })
  metadata?: Json<{ level?: string }>;
}

SQLite’s CREATE INDEX grammar has no USING <method> clause, so SQLite, libSQL, Turso and D1 ignore type and emit a plain index. The same entity therefore migrates everywhere.

Partial Indexes (Postgres/SQLite)

Partial indexes cover only the rows matching a WHERE predicate, so they stay small and queries that match that predicate hit them directly. Useful for entities with Soft-Delete, where only active rows need indexing.

// Index only active (non-deleted) emails to ensure uniqueness
// while allowing multiple 'deleted' records with the same email.
@Index(['email'], { unique: true, where: raw`"deletedAt" IS NULL` })
@Entity()
export class User {
  @Id({ type: Number })
  id?: number;

  @Field({ type: String })
  email?: string;

  @Field({ type: Date, softDelete: true })
  deletedAt?: Date;
}

Prefer raw over a bare string, which still works: a plain template literal interpolates silently, while raw binds, and a bound value is refused since CREATE INDEX has no placeholder for one.

@Index(['name'], { where: `"stock" > ${minimum}` }) // interpolates into the DDL
@Index(['name'], { where: raw`"stock" > ${minimum}` }) // throws

MySQL and MariaDB

Neither has partial indexes, so where throws there when the migration is generated. Widening a partial unique index to the whole table would reject rows the entity means to allow.

Vector Indexes

Vector indexes, for semantic search, use the same decorator with the vector options above.

distance is required on a vector index and rejected on any other kind. Both directions are compile errors, because the DDL would otherwise be silently wrong - MariaDB’s DISTANCE= defaults to euclidean, so a cosine query would full-scan. Match the metric your queries sort by.

@Index(['embedding'], { type: 'hnsw' })                       // error: missing distance
@Index(['embedding'], { type: 'btree', distance: 'cosine' })  // error: distance on a non-vector index

@Index(['embedding'], {
  type: 'hnsw',
  distance: 'cosine',
  m: 16,
  efConstruction: 64,
})
@Entity()
export class Article {
  @Id({ type: Number })
  id?: number;

  @Field({ type: 'vector', dimensions: 1536 })
  embedding?: number[];
}

Migrations track these parameters: if you tune m or efConstruction in code, the diff includes the DROP/CREATE needed to rebuild the index.

On CockroachDB, the same @Index decorator (using type: 'vector', the same marker MariaDB uses) generates CockroachDB’s own native index instead:

@Index(['embedding'], { type: 'vector', distance: 'cosine' })
@Entity()
export class Article {
  @Id({ type: Number })
  id?: number;

  @Field({ type: 'vector', dimensions: 1536 })
  embedding?: number[];
}

CockroachDB

CREATE VECTOR INDEX IF NOT EXISTS "article_embedding_idx" ON "Article" ("embedding" vector_cosine_ops);

No access-method keyword (unlike pgvector’s USING ivfflat/USING hnsw), and only cosine, l2, and inner are supported - see Semantic Search for the full metric table.

Synchronization

Indexes travel both ways through migrations: adding or removing a decorator shows up in generate:entities and sync, and generate:from-db writes the indexes it finds back as @Field({ index }) or @Index().