SQL constants

[!NOTE]
These are SQL statements to send. For the TypeScript enums you pass as
option values, such as ConstraintType, ReferentialAction and
IsolationLevel, see Enums and constants.

import { MYSQL, SQLITE } from 'ts-prorm-orm';

await prorm.query(SQLITE.PRAGMA.journal_mode.WAL);
// PRAGMA journal_mode = WAL

await prorm.query(MYSQL.FOREIGN_KEY_CHECKS.disable);
// SET FOREIGN_KEY_CHECKS = 0

import { getConstantsForDialect } from 'ts-prorm-orm';

const c = getConstantsForDialect(prorm.getDialect());

[!CAUTION]
Any other name, including 'mssql' and 'oracle', falls back to the MySQL
set. getConstantsForDialect('mssql').FOREIGN_KEY_CHECKS.disable is
SET FOREIGN_KEY_CHECKS = 0, which SQL Server rejects. For foreign-key checks
and isolation levels, use the connection methods below, which resolve per
engine and throw where there is no equivalent.

What each dialect exposes#

The PostgreSQL key really is spelled CONSTRAINT_DEFERral, in mixed case, in
both the interface and the constant. It is written that way here so that a
copy-paste compiles.

[!IMPORTANT]
Three constants that targeted settings current servers no longer have are
removed in 4.9.2: MYSQL.CACHE (query_cache_type; MySQL 8.0 removed the
query cache), POSTGRES.AUTOCOMMIT (SET SESSION autocommit, gone since
PostgreSQL 7.4) and POSTGRES.CACHE (statement_cache_mode, which is not a
PostgreSQL setting). Reading one of them now throws an error that says what
to use instead, rather than handing the server a statement it rejects:

[ORM] MYSQL.CACHE is obsolete: MySQL 8.0 removed the query cache, so `SET SESSION query_cache_type` is an error. Use MARIADB.CACHE on MariaDB, which still has it.

The names are not enumerable, so spreading or iterating the tables is
unaffected. MariaDB still has the query cache: MARIADB.CACHE.on is
SET SESSION query_cache_type = ON.

Isolation levels#

import { IsolationLevelEnum } from 'ts-prorm-orm';

prorm.getSetIsolationLevelSQL('REPEATABLE_READ'); // just the SQL, for the connected engine

await prorm.transaction(async (t) => {
  await prorm.setIsolationLevel(IsolationLevelEnum.SERIALIZABLE, { transaction: t });
  // … the transaction's statements …
});

[!IMPORTANT]
On PostgreSQL, SET TRANSACTION only affects the transaction it runs in. Sent
through the pool, it would land on an idle connection and change nothing, so
prorm.setIsolationLevel() without a transaction throws instead:

TransactionError: postgres: SET TRANSACTION only affects the transaction it runs in.
Pass { transaction }, or start the transaction with { isolationLevel }.

On MySQL the statement has no SESSION keyword either, so it applies to the
next transaction on that connection only. The simplest portable form is
prorm.transaction(fn, { isolationLevel: 'SERIALIZABLE' }); see
Transactions.

Foreign-key and unique checks#

await prorm.disableForeignKeyChecks();
// … load data in whatever order you like …
await prorm.enableForeignKeyChecks();

await prorm.disableUniqueKeyChecks(); // MySQL/MariaDB only; a no-op elsewhere
await prorm.enableUniqueKeyChecks();

UnsupportedForeignKeyChecksError: Session-level foreign key checks are not supported on mssql.
Use ALTER TABLE <table> NOCHECK CONSTRAINT ALL per table instead.

Loading data with foreign-key checks off#

Open a transaction#

import { getForeignKeyChecksSQL } from 'ts-prorm-orm';

const d = prorm.getDialect();
await prorm.transaction(async (t) => {
  await prorm.query(getForeignKeyChecksSQL(d, 'disable'), { transaction: t });
  await OrderLine.bulkCreate(lines, { transaction: t }); // children first
  await Order.bulkCreate(orders, { transaction: t });
  await prorm.query(getForeignKeyChecksSQL(d, 'enable'), { transaction: t });
});

Switch the checks back on before the transaction ends#

Verify the relationships#

[!WARNING]
SQLite ignores PRAGMA foreign_keys inside a transaction, so on SQLite the
pragma has to run before BEGIN. Use prorm.disableForeignKeyChecks()
outside the transaction there.

SQLite PRAGMAs#

import { SQLITE } from 'ts-prorm-orm';

SQLITE.PRAGMA.foreign_keys.on; // PRAGMA foreign_keys = ON
SQLITE.PRAGMA.journal_mode.WAL; // PRAGMA journal_mode = WAL
SQLITE.PRAGMA.synchronous.NORMAL; // PRAGMA synchronous = NORMAL
SQLITE.PRAGMA.trusted_schema.off; // PRAGMA trusted_schema = OFF

import { SQLITE } from 'ts-prorm-orm';

await prorm.query(SQLITE.PRAGMA.journal_mode.WAL); // persistent: stored in the database file
await prorm.query(SQLITE.PRAGMA.synchronous.NORMAL); // per connection
await prorm.query(SQLITE.PRAGMA.foreign_keys.on); // per connection, and off by default

MySQL sql_mode#

import { MYSQL } from 'ts-prorm-orm';

MYSQL.SQL_MODE.STRICT_TRANS_TABLES; // SET @@sql_mode = 'STRICT_TRANS_TABLES'
MYSQL.SQL_MODE.TRADITIONAL; // SET @@sql_mode = 'TRADITIONAL'
MYSQL.SQL_MODE.NO_ZERO_DATE; // SET @@sql_mode = 'NO_ZERO_DATE'

[!WARNING]
These constants assign sql_mode rather than appending to it, so a later
one replaces an earlier one. SET @@sql_mode = 'NO_ZERO_DATE' switches strict
mode off, and after it a too-long string is truncated again.
ALL is not a mode, so the server rejects it. MYSQL.SQL_MODE.NO_AUTO_CREATE_USER
is removed in 4.9.2 (MySQL 8.0 dropped the mode) and throws when read;
MARIADB.SQL_MODE.NO_AUTO_CREATE_USER keeps it for MariaDB. To combine modes, send your own statement:
SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO'.

PostgreSQL search path#

import { POSTGRES } from 'ts-prorm-orm';

await prorm.query(POSTGRES.SEARCH_PATH.set('reporting')); // SET search_path TO reporting
await prorm.query(POSTGRES.SEARCH_PATH.set('reporting, public')); // SET search_path TO reporting, public
await prorm.query(POSTGRES.SEARCH_PATH.reset); // SET search_path TO DEFAULT

Constraint deferral#

import { POSTGRES } from 'ts-prorm-orm';

await prorm.transaction(async (t) => {
  await prorm.query(POSTGRES.CONSTRAINT_DEFERral.DEFERRED, { transaction: t }); // SET CONSTRAINTS ALL DEFERRED
  // insert rows that reference each other, in any order
});

Session settings are per connection#

Related reading#