Skip to content

Dialect SQL Catalogs

from sqlspec.data_dictionary import DialectConfig, get_data_dictionary_loader, get_dialect_config

SQLSpec ships 13 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 35 Oracle Database Catalog
SQL Server (mssql) dbo AST synthesis + OBJECT_DEFINITION() for views/routines/triggers 19 31 SQL Server 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
IBM Db2 (db2) None AST synthesis + SYSCAT catalog text definitions 9 16 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 (13 dialects) - - 190 297 -

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 No 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 No 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 No Yes
Oracle Database Yes No No No No No No No Yes
SQL Server Yes No No No No No 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
IBM Db2 Yes No No >=8.1.0 >=8.1.0 No No No Yes
Google BigQuery Yes No Yes Yes Yes Yes Yes No Yes
Google Cloud Spanner No No No No No Yes No No No

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) -
Sybase ASE TEXT VARCHAR(36) BIT BIGDATETIME TEXT IMAGE -
SAP SQL Anywhere LONG VARCHAR UNIQUEIDENTIFIER BIT TIMESTAMP LONG VARCHAR LONG 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) -