SQL dialects

Choosing a dialect#

Pick a dialect#

PostgreSQL family (33)#

MySQL family (17)#

SQL Server family (8)#

SQLite family (4)#

Standalone engines (15)#

How to use these pages#

Dialect families#

Dialects per family#

Capability matrix#

Same operation, eight dialects#

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

const User = db.define(
  'User',
  {
    id: { type: DataTypes.INTEGER(), primaryKey: true, autoIncrement: true },
    email: { type: DataTypes.STRING(255), allowNull: false },
    name: { type: DataTypes.STRING(100) },
    meta: { type: DataTypes.JSON() },
    joinedAt: { type: DataTypes.DATE(), allowNull: false, defaultValue: 'CURRENT_TIMESTAMP' },
  },
  { timestamps: false },
);

Paging#

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

await User.findAll({
  where: { name: { [Op.like]: 'A%' } },
  order: [['id', 'ASC']],
  limit: 20,
  offset: 40,
});

Upsert#

await User.upsert({ id: 1, email: 'ada@example.com', name: 'Ada Lovelace' });

RETURNING#

await User.create({ email: 'ada@example.com', name: 'Ada' }, { returning: ['id', 'joinedAt'] });

[!CAUTION]
Refused before anything is sent: RETURNING on INSERT is not supported for the bigquery dialect.

JSON query#

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

await User.findAll({ attributes: ['id', [jsonExtract('postgres', 'meta', 'plan'), 'plan']] });

Row lock#

await db.transaction(async (t) => {
  await User.findAll({ where: { id: 1 }, lock: { level: 'UPDATE', skipLocked: true }, transaction: t });
});

[!CAUTION]
Refused before anything is sent: startTransaction() is not supported for the bigquery dialect.

[!CAUTION]
Refused before anything is sent: ClickHouse does not support multi-statement transactions (no BEGIN/COMMIT/ROLLBACK spanning multiple statements).

Timestamp default#

[!WARNING]
Use the string 'CURRENT_TIMESTAMP' for a column default, as above. In
4.9.4, defaultValue: DataTypes.NOW only becomes a real default on
SQLite (and Turso, rqlite), SQL Server (and its RDS, Azure and Cloud SQL
editions) and Oracle (CURRENT_TIMESTAMP, GETDATE(), SYSTIMESTAMP). On PostgreSQL, MySQL, MariaDB, Db2, HANA, Snowflake,
BigQuery, ClickHouse, DuckDB and most of their managed editions, the
CREATE TABLE gets the quoted text DEFAULT '() => new NOW()'. On
Firebird, Greenplum, Vertica, SingleStore, Databricks and a few others,
createTable() throws a TypeError instead.

Limitations at a glance#

[!CAUTION] No transactions
ClickHouse, BigQuery,
Athena and Presto refuse transaction(),
and so do Druid and Kusto. startTransaction()
throws, so a lock or a savepoint inside one is never reached. BigQuery can
still run one atomic script with runTransactionScript([sql, …]).

[!WARNING] Transactions that are not atomic
Cloudflare D1, rqlite and the
Redshift Data API run each statement in a
transaction() as its own request. A failure part-way leaves the earlier
statements applied, and a rollback cannot undo them. Pass
requireAtomic: true to refuse them. On D1, use its batch API for an
all-or-nothing unit.

[!WARNING] No upsert
upsert() throws on Apache Doris and
StarRocks (upsert is a table property: a unique or
primary key model), Materialize and
RisingWave (no ON CONFLICT), and
Yellowbrick (neither ON CONFLICT nor MERGE). It
also throws on Athena, Druid and
Kusto. On ClickHouse and
QuestDB it is a plain INSERT, which adds a row instead
of updating one.

[!IMPORTANT] No DDL or DML
Apache Druid has no CREATE TABLE, INSERT … VALUES,
UPDATE or DELETE. Data arrives by ingestion (buildInsertIngestion()
and submitIngestion()). The T-SQL that
Azure Data Explorer (Kusto) accepts is read-only: CREATE,
INSERT, UPDATE, DELETE, views, triggers, sequences and procedures
all throw. Both are for querying data loaded some other way.

[!NOTE] Generate-only: the connection is unverified
Firebird, Trino, Vertica,
Exasol and Databricks generate their
engine's SQL, but none of them overrides connect(). They connect with
their parent's driver (pg, or mysql2 for Databricks), which does not
speak the engine's own wire protocol. Use them to build SQL, and run it
with the engine's own client until a native driver lands.

Managed and cloud variants#

Drivers#

Changes in 4.9#

[!IMPORTANT]
Tables created by an older release are not rewritten. They keep a quoted
'CURRENT_TIMESTAMP' default, a bare VARCHAR for ENUM, and on MySQL no
foreign keys. Migrate those columns and constraints yourself.

Relational (OLTP)#

Warehouse / OLAP / columnar#

Distributed SQL / NewSQL#

Specialised#

Serverless and HTTP#

Managed cloud services#

AWS#

Microsoft Azure#

Google Cloud#

Other providers#

Related reading#