SQL dialects

SQL dialects A dialect is the one place that knows about a specific engine. It owns identifier quoting, value escaping, how DDL is written, which clauses are legal, and how pagination and locking are spelled — so the same model code and the same FindOptions produce correct SQL for whichever engine you point at. Each page below documents what that engine supports, where it diverges from the others, and the SQL prorm actually emits for it. How to use these pages You do not need to read any of them to use the ORM. Come here when you want to know whether an engine supports something, why a query looks different on it, or what to expect when moving between two of them. Three differences account for most surprises: Pagination. LIMIT ... OFFSET on PostgreSQL and MySQL, OFFSET ... FETCH NEXT on MSSQL, Oracle and Snowflake. The dialect handles it, but the generated SQL will not look the same. Constraint enforcement. The analytical engines accept a primary key declaration and then do not enforce it. Uniqueness is your problem there. Transactions. Not every engine has them, and among those that do, isolation defaults differ. 27 dialect pages, grouped by what the engine is for. Relational (OLTP) The engines most applications start on. Full transactional SQL, and the dialects with the deepest feature coverage. Dialect | What it is | --- | --- | PostgreSQL | PostgreSQL is the ORM's most fully-featured dialect. | MySQL | The MySQL dialect targets MySQL 5.7 and 8.0+ servers (and compatible forks). | MariaDB | The MariaDB dialect provides first-class support for MariaDB servers. | SQLite | SQLite is an embedded, serverless, zero-configuration SQL database engine that stores an entire database (schema,… | Oracle | Oracle Database is an enterprise-grade relational database. prorm talks to it through the oracledb driver. | Microsoft SQL Server | The mssql dialect targets Microsoft SQL Server (and Azure SQL) through the mssql Node.js package, which uses the… | IBM Db2 | IBM Db2 (specifically Db2 for LUW — Linux/Unix/Windows) is IBM's flagship relational database. | Firebird | Firebird is a lightweight, open-source relational database derived from InterBase. | Turso / libSQL | Turso is a hosted database service built on libSQL, an open-source fork of SQLite. | Warehouse / OLAP / columnar Column-oriented engines built for aggregate reads over large tables. Expect no transactions, weak or absent constraint enforcement, and mutations that are asynchronous and expensive — model your data as append-only. Dialect | What it is | --- | --- | ClickHouse | ClickHouse is a columnar OLAP database built for real-time analytics over very large datasets — aggregations,… | DuckDB | DuckDB is an embedded, in-process OLAP (analytical) database. | Amazon Redshift | Amazon Redshift is a columnar, massively-parallel cloud data warehouse. | Snowflake | Snowflake is a cloud-native, columnar, analytical data warehouse. | Vertica | Reference documentation for the vertica dialect. Everything below is drawn from the dialect source… | Greenplum | Reference documentation for the greenplum SQL dialect. | Exasol | Exasol is an in-memory, column-oriented, massively-parallel (MPP) analytic database. | SAP HANA | The hana dialect targets SAP HANA — both on-premise HANA 2.0 and the continuously-delivered HANA Cloud editions. | Databricks | Reference for the databricks SQL dialect (src/dialects/databricks/index.ts). | Trino | Reference for the trino SQL dialect. | Distributed SQL / NewSQL Horizontally scalable engines that keep a SQL interface. They mostly speak PostgreSQL or MySQL wire protocols, but differ in transaction retries, schema-change behaviour and key distribution. Dialect | What it is | --- | --- | CockroachDB | CockroachDB is a distributed, horizontally-scalable SQL database that speaks the PostgreSQL wire protocol. | YugabyteDB | YugabyteDB is a distributed SQL database whose YSQL API is forked from the PostgreSQL source. | TiDB | TiDB is a distributed, MySQL-wire-protocol-compatible NewSQL database: it speaks the MySQL client/server protocol… | SingleStore | Reference documentation for the singlestore dialect. | Google Cloud Spanner | Google Cloud Spanner is a fully-managed, horizontally-scalable, strongly-consistent distributed SQL database. | Specialised Engines aimed at one workload — time series, or search-shaped analytics. Dialect | What it is | --- | --- | TimescaleDB | Reference for the timescaledb SQL dialect. | QuestDB | Reference for the questdb SQL dialect. | CrateDB | CrateDB is a distributed SQL database built for search and analytics over large volumes of structured and… | Related reading Database types — the full taxonomy, including the non-SQL stores Data stores — the NoSQL adapters Running databases in Docker — get one running locally