Indexes & constraints

Indexes & constraints Indexes and constraints can be declared on the model, so sync() creates them with the table, or applied imperatively at runtime through the connection and the dialect. This guide covers both, the SQL each produces, and the places where a dialect refuses or rewrites what you asked for. Declaring on the model sync() emits: Constraints are folded into the CREATE TABLE; indexes follow as separate statements. An unnamed index is named idx<table><first field>. Index options Option | Effect | --- | --- | fields | Column list. Composite indexes list several. | name | Index name; defaults to idx<table><field>. | unique | CREATE UNIQUE INDEX. | expression | Index an expression instead of columns: ((LOWER(email))). | using | Index method — GIN, GIST, BTREE, HASH (PostgreSQL/MySQL). | include | Covering-index payload columns (PostgreSQL 11+, MySQL). | type | Dialect index type keyword. | parser | MySQL/MariaDB full-text parser. | tablespace, storageParameters | PostgreSQL storage placement and knobs. | nullsNotDistinct | NULLS NOT DISTINCT on a unique index (PostgreSQL 15+). | ifNotExists | Suppress the "already exists" error. | where | Partial index predicate — see the caveat below. | Partial indexes work from the model declaration: Because CREATE INDEX is executed with no parameter list, the predicate's values are compiled as escaped literals rather than bound placeholders — through the dialect's own escape()/escapeId(), so a value containing a quote is doubled ('O''Brien') and cannot break out of the literal. Supported predicate forms: { col: value }, { col: [...] } (→ IN), Op.eq/ne/gt/gte/lt/lte/in/notIn/between/notBetween/like/ notLike/is/not/isNull/isNotNull/col, and nested Op.and/Op.or/ Op.not. Anything else throws, naming the operator. A where given as a plain string passes through verbatim, which is the escape hatch for a predicate the compiler does not model. Dialect | Partial index | --- | --- | SQLite, Turso, PostgreSQL, CockroachDB, Greenplum, TimescaleDB, YugabyteDB | supported | SQL Server | supported (a filtered index) | MariaDB, Oracle, Db2, SAP HANA, ClickHouse, MySQL, DuckDB, Vertica, Firebird, Exasol | throws UnsupportedSchemaObjectError | Spanner | warns and ignores the predicate | Snowflake, Redshift, Trino, CrateDB | no indexes at all | The throwing dialects previously either appended a WHERE clause the engine has never accepted (MariaDB, Oracle) or silently dropped it and handed back an unfiltered index (Db2, SAP HANA, ClickHouse) — the second being the more dangerous, since the index looked declared and covered every row. They now explain the problem instead: createPartialIndex(table, name, fields, predicate) still takes the predicate as a literal string, if you would rather write it yourself. Constraint options Option | Effect | --- | --- | type | 'PRIMARY KEY', 'UNIQUE', 'FOREIGN KEY', 'CHECK'. | name / constraintName | Constraint name. | fields | Columns the constraint covers. | check | Predicate for a CHECK, as a literal SQL string. | references | { model, field, onDelete, onUpdate, match, deferrable } for a FOREIGN KEY. | deferrable | INITIALLY DEFERRED / INITIALLY IMMEDIATE / NOT DEFERRABLE (PostgreSQL). | Composite uniqueness Three declaration forms all work and all normalise to the same table constraint: De-duplication is by constraint name and by order-independent column set, so declaring the same key two ways yields one constraint, not two. A key naming an unknown column throws with the list of known columns rather than vanishing. UniqueKeyOptions.unique: true produces a unique index instead, which is what that flag documents. Column names resolve through field / underscored, falling back to the attribute name when the rename is not among the columns CREATE TABLE actually emitted — otherwise the constraint would name a column that does not exist. sync({ alter: true }) used to destroy these. SQLite implements changeColumn by rebuilding the table, and the rebuild dropped the UNIQUE clause — so one alter silently un-enforced a key the model still declared. Declared keys are now re-applied as unique indexes after an alter (idempotent across re-syncs). Keys the model no longer declares are not dropped, and the re-apply fails loudly if existing rows already violate a newly declared key. Uniqueness on a single column The attribute-level shorthand is the simplest form and is emitted inline: A violation surfaces as a UniqueConstraintError; pass unique: { name: 'uqemail', msg: 'That email is taken' } to control the message. See Error handling. Foreign keys A foreign key is declared on the column that holds it, with the referential actions nested inside references: onDelete / onUpdate written as siblings of references are ignored — they must be inside it. Associations also contribute foreign keys during sync(); see Associations. The foreign-key column's type is derived from the column it references, so a UUID primary key produces a UUID child column rather than an INTEGER one. Foreign-key enforcement can be toggled around a bulk load: Dialect family | SQL emitted | --- | --- | SQLite, Turso | PRAGMA foreignkeys = OFF / ON | MySQL, MariaDB, TiDB | SET FOREIGNKEYCHECKS = 0 / 1 | PostgreSQL, TimescaleDB, Greenplum, YugabyteDB, CockroachDB | SET sessionreplicationrole = 'replica' / 'origin' | everything else | throws UnsupportedForeignKeyChecksError | The PostgreSQL form needs superuser, or GRANT SET ON PARAMETER sessionreplicationrole on PG 15+. That is deliberately not swallowed — the server's permission denied to set parameter surfaces so you know the checks were never actually disabled. Dialects with no session-level switch name their alternative instead of emitting something invalid: SQL Server → NOCHECK CONSTRAINT ALL, Oracle → DISABLE CONSTRAINT, Db2 → SET INTEGRITY; Redshift, Snowflake, ClickHouse, SingleStore and Databricks say they never enforce foreign keys at all. getDisableForeignKeyChecksSQL() / getEnableForeignKeyChecksSQL() return the statement without running it. MySQL/MariaDB additionally have disableUniqueKeyChecks() / enableUniqueKeyChecks() (SET UNIQUECHECKS = 0/1); those stay deliberate no-ops elsewhere, since SET UNIQUECHECKS is a MySQL bulk-load optimisation with no equivalent — throwing would break the bulk-load pattern everywhere else for no benefit. Changing indexes and constraints at runtime On the connection: On SQLite, removeColumn and changeColumn fall back to the standard rename-copy-drop dance, because SQLite cannot drop or alter a column in place: The same operations are available on the QueryInterface used inside migrations. Dialect index helpers Beyond addIndex, each dialect exposes the index kinds it actually supports. Reach them through prorm.getDialectInstance(). Method | Purpose | --- | --- | createIndex / dropIndex | Plain index. | createPartialIndex(table, name, fields, predicate) | Index with a literal WHERE predicate. | createExpressionIndex(table, name, expression) | CREATE INDEX … (LOWER(body)). | createFulltextIndex(table, name, fields) | Dialect-native full-text index. | createSpatialIndex(...) | GiST / SPATIAL / RTree index. | createGINIndex, createGISTIndex, createVectorIndex | PostgreSQL-specific. | addConstraint / removeConstraint | Constraint on an existing table. | showIndexes(table) / showConstraints(table) | Introspection. | SQLite refuses what it genuinely cannot do, with an explanation rather than broken SQL: Full-text indexes buildCreateFullTextIndexSQL(options, dialect, quoteId, quoteTable) resolves one declaration into whatever the engine actually needs: Dialect | SQL | --- | --- | PostgreSQL | CREATE INDEX "ftdocs" ON "docs" USING GIN (totsvector('english', coalesce("title",'') \|\| ' ' \|\| coalesce("body",''))) | MySQL / MariaDB | CREATE FULLTEXT INDEX "ftdocs" ON "docs" ("title", "body") | SQLite | CREATE VIRTUAL TABLE IF NOT EXISTS "ftdocs" USING fts5("title", "body", content="docs") | SQL Server | throws UnsupportedSchemaObjectError — needs a full-text catalog and unique key index first | Oracle | throws UnsupportedSchemaObjectError | language selects the PostgreSQL text-search configuration (default 'english'). The thrown error is deliberate: it tells you the operation is impossible on that engine instead of emitting SQL the driver will reject. Primary keys Single-column primary keys are declared on the attribute. Composite keys are declared on the model: Model.getPrimaryKeyAttribute() reports the resolved key, and it is what findByPk and findInBatches iterate on. Related reading Defining models Associations — foreign keys from relationships Migrations — versioned index/constraint changes Schema objects — views, triggers, sequences, partitions Query optimization — checking an index is used