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. Note: these are SQL statements to send. For the TypeScript enums you pass as option values — ConstraintType, ReferentialAction, IsolationLevel and the rest — see Enums and constants. 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