Skip to content

Dialect SQL Catalogs

from sqlspec.data_dictionary import DialectConfig, get_data_dictionary_loader, get_dialect_config

SQLSpec ships 15 dialect-specific SQL catalog packs under sqlspec/data_dictionary/dialects/ containing named SQLFileLoader queries (-- name: ...), version-gated feature profiles (DialectConfig), and logical-to-physical type mappings. See the Data Dictionary Guide and Data Dictionary Reference for programmatic usage.

Dialect Catalog Matrix

Dialect Default Schema DDL Resolution Domain .sql Files Named Queries Reference
PostgreSQL (postgres) public AST synthesis + pg_get_*def catalog functions 21 26 PostgreSQL Catalog
CockroachDB (cockroachdb) public Native crdb_internal.create_statement + AST fallback 15 18 CockroachDB Catalog
SQLite (sqlite) None Native sqlite_master.sql + PRAGMA synthesis 15 23 SQLite Catalog
DuckDB (duckdb) None Native duckdb_*().sql catalog functions + AST synthesis 17 24 DuckDB Catalog
MySQL (mysql) None Native SHOW CREATE + information_schema synthesis 18 27 MySQL Catalog
MariaDB (mariadb) None Native SHOW CREATE + MariaDB sequence & constraint reflection 17 27 MariaDB Catalog
Oracle Database (oracle) None Native DBMS_METADATA.GET_DDL + AST fallback 22 36 Oracle Database Catalog
SQL Server (mssql) dbo AST synthesis + OBJECT_DEFINITION() for views/routines/triggers 19 31 SQL Server Catalog
Teradata Vantage (teradata) None DBC.TableTextV object text and catalog-backed AST synthesis 9 17 Teradata Vantage Catalog
Sybase ASE (sybase) dbo AST synthesis + dbo.syscomments object text reassembly 9 16 Sybase ASE Catalog
SAP SQL Anywhere (sqlanywhere) DBA AST synthesis + SYS.SYSTAB / SYS.SYSPROCEDURE definitions 9 16 SAP SQL Anywhere Catalog
Snowflake (snowflake) None Native GET_DDL and SHOW KEYS metadata 11 24 Snowflake Catalog
IBM Db2 (db2) None AST synthesis + SYSCAT catalog text definitions 9 14 IBM Db2 Catalog
Google BigQuery (bigquery) None Native INFORMATION_SCHEMA.TABLES.ddl + AST fallback 14 30 Google BigQuery Catalog
Google Cloud Spanner (spanner) None AST synthesis with INTERLEAVE IN PARENT & ROW DELETION POLICY (GoogleSQL & PostgreSQL modes) 5 8 Google Cloud Spanner Catalog
Total (15 dialects) - - 210 337 -

Cross-Dialect Capability Matrix

Each dialect pack declares boolean feature_flags and version-gated feature_versions inside its DialectConfig:

Dialect Schemas Returning Upsert Window CTE JSON Arrays Vectors Transactions
PostgreSQL Yes >=8.2.0 >=9.5.0 >=8.4.0 >=8.4.0 >=9.2.0 Yes No Yes
CockroachDB Yes >=20.1.0 >=19.2.0 >=19.1.0 >=19.1.0 >=20.1.0 Yes >=24.2.0 Yes
SQLite No >=3.35.0 >=3.24.0 >=3.25.0 >=3.8.3 >=3.38.0 No No Yes
DuckDB Yes >=0.8.0 >=0.8.0 Yes Yes Yes Yes >=0.10.0 Yes
MySQL Yes No Yes >=8.0.2 >=8.0.1 >=5.7.8 No No Yes
MariaDB Yes Yes Yes >=10.2.0 >=10.2.1 >=10.2.7 No >=11.7.0 Yes
Oracle Database Yes Yes Yes Yes Yes >=12.1.0 No >=23.4.0 Yes
SQL Server Yes Yes Yes Yes Yes >=13.0.0 No >=17.0.0 Yes
Teradata Vantage Yes No >=15.10.0 >=15.10.0 >=15.10.0 >=15.10.0 No No Yes
Sybase ASE Yes No No No No No No No Yes
SAP SQL Anywhere Yes No Yes >=10.0.0 >=10.0.0 No No No Yes
Snowflake Yes No Yes Yes Yes Yes Yes Yes Yes
IBM Db2 Yes No No >=8.1.0 >=8.1.0 No No No Yes
Google BigQuery Yes No Yes Yes Yes Yes Yes Yes Yes
Google Cloud Spanner Yes Yes Yes Yes Yes Yes Yes Yes Yes

Logical-to-Physical Type Mappings

driver.data_dictionary.get_optimal_type(driver, type_category) resolves portable logical type categories using each dialect's DialectConfig.type_mappings and active engine version:

Dialect json uuid boolean timestamp text blob vector
PostgreSQL JSONB UUID BOOLEAN TIMESTAMP WITH TIME ZONE TEXT BYTEA -
CockroachDB JSONB UUID BOOL TIMESTAMPTZ STRING BYTES -
SQLite JSON TEXT INTEGER TIMESTAMP TEXT BLOB -
DuckDB JSON UUID BOOLEAN TIMESTAMP TEXT BLOB -
MySQL JSON VARCHAR(36) TINYINT(1) TIMESTAMP TEXT BLOB -
MariaDB JSON VARCHAR(36) TINYINT(1) TIMESTAMP TEXT BLOB -
Oracle Database JSON RAW(16) NUMBER(1) TIMESTAMP CLOB BLOB -
SQL Server NVARCHAR(MAX) UNIQUEIDENTIFIER BIT DATETIME2(6) NVARCHAR(MAX) VARBINARY(MAX) -
Teradata Vantage JSON VARCHAR(36) BYTEINT TIMESTAMP(6) WITH TIME ZONE CLOB BLOB -
Sybase ASE TEXT VARCHAR(36) BIT BIGDATETIME TEXT IMAGE -
SAP SQL Anywhere LONG VARCHAR UNIQUEIDENTIFIER BIT TIMESTAMP LONG VARCHAR LONG BINARY -
Snowflake VARIANT VARCHAR(36) BOOLEAN TIMESTAMP_TZ VARCHAR BINARY -
IBM Db2 CLOB VARCHAR(36) BOOLEAN TIMESTAMP CLOB BLOB -
Google BigQuery JSON STRING BOOL TIMESTAMP STRING BYTES -
Google Cloud Spanner JSON BYTES(16) BOOL TIMESTAMP STRING(MAX) BYTES(MAX) -