SQL constants

SQL constants Some database behaviour is controlled by session statements rather than by anything in a query — foreign-key checks, isolation level, autocommit, SQLite PRAGMAs, MySQL sqlmode, PostgreSQL's searchpath. sql-constants.ts holds the exact statement each dialect needs, so you set the behaviour rather than remember the syntax. SQL is the whole table keyed by dialect (SQL.postgres, SQL.mysql, …), and getConstantsForDialect(name) picks the right set at runtime — falling back to MySQL's for an unknown dialect, which is worth knowing before you feed it a name it does not have. Covered dialects: mysql, mariadb, tidb, postgres, sqlite, turso, cockroachdb, timescaledb, greenplum, yugabytedb, redshift, db2, snowflake, clickhouse. The PostgreSQL-derived dialects reuse the PostgreSQL set (so timescaledb gets sessionreplicationrole, not MySQL's syntax), TiDB reuses MySQL's, and Turso reuses SQLite's. What each dialect exposes | MySQL / MariaDB | PostgreSQL | SQLite | --- | --- | --- | --- | ISOLATIONLEVEL | ✓ | ✓ | ✓ | FOREIGNKEYCHECKS | ✓ (SET FOREIGNKEYCHECKS) | ✓ (sessionreplicationrole) | via PRAGMA | UNIQUEKEYCHECKS | ✓ | — | — | AUTOCOMMIT | ✓ | ✓ | — | CACHE | ✓ | ✓ | — | SQLMODE | ✓ | — | — | CONSTRAINTDEFERral | — | ✓ | — | SEARCHPATH | — | ✓ | — | PRAGMA | — | — | ✓ | The PostgreSQL key really is spelled CONSTRAINTDEFERral — mixed case, in both the interface and the constant. Written that way here so a copy-paste compiles. Isolation levels Level | MySQL | PostgreSQL | --- | --- | --- | READUNCOMMITTED | SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED | SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED | READCOMMITTED | SET SESSION … READ COMMITTED | SET TRANSACTION … READ COMMITTED | REPEATABLEREAD | SET SESSION … REPEATABLE READ | SET TRANSACTION … REPEATABLE READ | SERIALIZABLE | SET SESSION … SERIALIZABLE | SET TRANSACTION … SERIALIZABLE | For a per-transaction level, pass isolationLevel to prorm.transaction() — see Transactions. Foreign-key and unique checks The connection methods resolve the right statement per dialect family, and throw UnsupportedForeignKeyChecksError — naming that engine's alternative — where there is no session-level switch. getForeignKeyChecksSQL(dialect) is the underlying resolver. See Indexes & constraints for the full table and the PostgreSQL privilege caveat. SQLite PRAGMAs journalmode accepts WAL, DELETE, TRUNCATE, PERSIST, MEMORY; synchronous accepts OFF, NORMAL, FULL, EXTRA. The usual production pairing is journalmode = WAL with synchronous = NORMAL: readers stop blocking the writer, and you keep durability against process crashes (though not against an OS crash — that is what FULL buys). More SQLite tuning: SQLite advanced. MySQL sqlmode STRICTTRANSTABLES (or TRADITIONAL) turns silent truncation and out-of-range coercion into errors. Setting it is the single most valuable session change on MySQL: without it, a too-long string is quietly cut and an invalid date becomes 0000-00-00. Note these constants assign sqlmode rather than appending, so a later one replaces an earlier one. PostgreSQL search path SEARCHPATH.set is a function, not a string. FindOptions.searchPath and the model-level schema option are the declarative alternatives. Constraint deferral Deferring inside a transaction lets you insert rows with circular references and have the constraints checked at commit. The constraints must have been declared DEFERRABLE — see Indexes & constraints. Session settings are per connection Every statement here affects the connection it runs on. With a pool, the next query may land on a different connection that never saw it. Set session state either inside a transaction (which holds one connection throughout) or in the pool's connection-init hook so every connection gets it. Related reading Transactions — isolation levels per transaction Connection pooling SQLite advanced Indexes & constraints