Indexes

Indexes are defined directly on your entities, either per field or at the class level for composite and specialized indexes.

Simple Indexes

For basic single-column indexes, use the index option within the @Field decorator.

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

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

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

Composite Indexes

When you need an index that spans multiple columns (e.g., for queries filtering by both lastName and firstName), use the @Index decorator at the class level. A composite index answers a filter on both columns directly; two single-column indexes leave the planner to combine two separate scans.

Example: Audit Log

Consider an audit log where you frequently search for entries by entityType and entityId, ordered by createdAt. A composite index on these three columns lets that lookup hit the index directly instead of scanning the table.

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

@Index(['entityType', 'entityId', 'createdAt'], { name: 'idx_audit_lookup' })
@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'
}

Tip

Which one should I use?

• Use @Field({ index: true }) for simple, single-column indexes. It’s more concise.
• Use @Index() for composite indexes, or when you need advanced features like type, where clauses, or custom naming that doesn’t fit in the field definition.

Customizing Indexes

The @Index decorator accepts several options to fine-tune the index behavior:

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

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.

Unique Composite Index

Ideal for enforcing uniqueness across a combination of fields, such as “one email per tenant” in a multi-tenant application.

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

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: '"deletedAt" IS NULL' })
@Entity()
export class User {
  @Id({ type: Number })
  id?: number;

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

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

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) are defined with the same @Index decorator, using the vector-specific options above.

distance is required, and only for vector types

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’s inline index 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[];
}

Generated SQL (CockroachDB)

CREATE VECTOR INDEX IF NOT EXISTS "idx_article_embedding" 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

UQL handles indexes automatically during migrations:

1. Entity to Database: Whenever you add or remove an index decorator, UQL detects the change during generate:entities or autoSync.
2. Database to Entity: When you use generate:from-db, UQL discovers existing indexes and adds the corresponding @Field({ index: true }) or @Index() decorators to your generated code.

---

Read more about entity definition or Migrations.