Dialects

Cloud warehouses

Snowflake, BigQuery, Redshift and Databricks (Delta Lake) are supported for DDL generation, DDL import, comparison and migration, and for live import through their own command-line clients (snowsql, bq, psql, dbsqlcli). None of them supports indexes; BigQuery and Databricks have no unique constraints; Snowflake, BigQuery and Redshift have no check constraints. Such objects are skipped with a note printed by bluep generate ddl. Table options are carried in option lines: Snowflake transient, cluster_by, data_retention_time_in_days; BigQuery partition_by, cluster_by, require_partition_filter; Redshift diststyle, distkey, sortkey, sortkey_style; Databricks using, partition_by, cluster_by, location and tblproperties.<key>. A dialect is the pluggable unit that knows one database family: how identifiers are quoted, which features exist, how logical types map to physical types and back, how CREATE and ALTER statements are spelled, and (for some dialects) how the catalog of a live database is read through the database's own command-line client. Dialects are registered in bluep_core::dialect::registry() and selected by their id.

id	display name	depth	live import	DDL import
`postgresql`	PostgreSQL	in depth	`psql`	yes
`sqlite`	SQLite	in depth	`sqlite3`	yes
`mysql`	MySQL / MariaDB	in depth	`mysql`	yes
`sqlserver`	SQL Server	forward DDL and migrations	no	no
`oracle`	Oracle	forward DDL and migrations	no	no

How a dialect is chosen

A target in the model names its dialect (target pg : postgresql { ... }). Commands that work on a target use that dialect. bluep generate ddl --dialect <id> derives a target from the logical layer for a dialect the model has no target for: one table per entity, one column per attribute through the dialect's type mapping, primary and unique keys from the entity keys, one foreign key per relationship named fk_<child>_<parent>, and one index ix_<child>_<relationship> on the foreign key columns.

Identifiers

Every dialect quotes an identifier when it is a reserved word of that dialect, when it does not match the dialect's plain identifier form, or when the dialect has additional rules (SQLite ignores the schema main; MySQL uses backticks; SQL Server uses square brackets). Quoting never changes the identifier's bytes.

Procedural and security objects

Six features describe the objects added in Phase 19: triggers, routines (functions and procedures), materialized_views, partitioning, roles and grants. PostgreSQL supports all six in depth (dollar-quoted bodies, EXECUTE FUNCTION triggers, PARTITION OF child tables, ALTER ROLE). MySQL writes routines and triggers inside DELIMITER $$ blocks and partitions inline; it has no materialized views. SQL Server separates routine, trigger and indexed-view batches with GO and emits a materialized view as a view WITH SCHEMABINDING plus a unique clustered index; it has no declarative partitioning here. Oracle uses CREATE OR REPLACE routines and triggers terminated by a / line and BUILD IMMEDIATE|DEFERRED materialized views. SQLite and the cloud warehouse dialects support none of them: bluep generate ddl skips such objects with a note (function 'f' skipped: not supported by SQLite) and bluep migrate lists them as -- skipped unsupported: ....

Live import: SQL Server and Oracle

bluep import db --dialect sqlserver runs sqlcmd; --dialect oracle runs sqlplus (or SQLcl sql). Both clients return one text column per row whose fields are joined with the unit separator (U+001F); definitions, defaults, check expressions and comments are hex-encoded in the query and decoded by bluep, so multi-line text survives. Texts longer than 15000 characters are truncated and reported as notes. Oracle needs --user and --password (or BLUEP_DB_PASSWORD); --database is the connect string host:port/service; without --schema the connecting user's schema is imported. SQL Server default constraints are assumed to be named DF_<table>_<column>; databases with system-named defaults need the DROP CONSTRAINT adjusted by hand before a migration that changes a default. Identifiers containing U+001F or a newline are not supported by the live import.

The tables below are generated from the dialect implementations by the test dialects_doc_is_current and are rewritten with BLUEP_UPDATE_GOLDEN=1 cargo test -p bluep-core --test docs_current.

Type mappings

Each table lists the physical type a dialect generates for a logical type (physical) and the logical type it recovers when reading that physical type back (reverse; - when there is no logical equivalent).

PostgreSQL (`postgresql`)

logical	physical	reverse
smallint	smallint	smallint
integer	integer	integer
bigint	bigint	bigint
decimal(12, 2)	numeric(12, 2)	decimal(12, 2)
float	real	float
double	double precision	double
boolean	boolean	boolean
text	text	text
text(255)	varchar(255)	text(255)
char(2)	char(2)	char(2)
date	date	date
time	time	time
timestamp	timestamp	timestamp
timestamptz	timestamp with time zone	timestamptz
uuid	uuid	uuid
binary	bytea	binary
binary(16)	bytea	binary
json	jsonb	json
enum status	status	-

SQLite (`sqlite`)

logical	physical	reverse
smallint	INTEGER	integer
integer	INTEGER	integer
bigint	INTEGER	integer
decimal(12, 2)	NUMERIC(12, 2)	decimal(12, 2)
float	REAL	double
double	REAL	double
boolean	BOOLEAN	boolean
text	TEXT	text
text(255)	VARCHAR(255)	text(255)
char(2)	CHAR(2)	char(2)
date	DATE	date
time	TIME	time
timestamp	TIMESTAMP	timestamp
timestamptz	TIMESTAMPTZ	timestamptz
uuid	CHAR(36)	char(36)
binary	BLOB	binary
binary(16)	BLOB	binary
json	TEXT	text
enum status	TEXT	text

MySQL/MariaDB (`mysql`)

logical	physical	reverse
smallint	SMALLINT	smallint
integer	INT	integer
bigint	BIGINT	bigint
decimal(12, 2)	DECIMAL(12, 2)	decimal(12, 2)
float	FLOAT	float
double	DOUBLE	double
boolean	BOOLEAN	boolean
text	TEXT	text
text(255)	VARCHAR(255)	text(255)
char(2)	CHAR(2)	char(2)
date	DATE	date
time	TIME	time
timestamp	DATETIME(6)	timestamp
timestamptz	TIMESTAMP(6)	timestamptz
uuid	CHAR(36)	char(36)
binary	BLOB	binary
binary(16)	VARBINARY(16)	binary(16)
json	JSON	json
enum status	ENUM('active', 'inactive')	-

SQL Server (`sqlserver`)

logical	physical	reverse
smallint	SMALLINT	smallint
integer	INT	integer
bigint	BIGINT	bigint
decimal(12, 2)	DECIMAL(12, 2)	decimal(12, 2)
float	REAL	float
double	FLOAT	double
boolean	BIT	boolean
text	NVARCHAR(MAX)	text
text(255)	NVARCHAR(255)	text(255)
char(2)	NCHAR(2)	char(2)
date	DATE	date
time	TIME	time
timestamp	DATETIME2	timestamp
timestamptz	DATETIMEOFFSET	timestamptz
uuid	UNIQUEIDENTIFIER	uuid
binary	VARBINARY(MAX)	binary
binary(16)	VARBINARY(16)	binary(16)
json	NVARCHAR(MAX)	text
enum status	NVARCHAR(8)	text(8)

Oracle (`oracle`)

logical	physical	reverse
smallint	NUMBER(5, 0)	smallint
integer	NUMBER(10, 0)	integer
bigint	NUMBER(19, 0)	bigint
decimal(12, 2)	NUMBER(12, 2)	decimal(12, 2)
float	BINARY_FLOAT	float
double	BINARY_DOUBLE	double
boolean	NUMBER(1, 0)	boolean
text	CLOB	text
text(255)	VARCHAR2(255)	text(255)
char(2)	CHAR(2)	char(2)
date	DATE	timestamp
time	TIMESTAMP	timestamp
timestamp	TIMESTAMP	timestamp
timestamptz	TIMESTAMP WITH TIME ZONE	timestamptz
uuid	RAW(16)	uuid
binary	BLOB	binary
binary(16)	RAW(16)	uuid
json	CLOB	text
enum status	VARCHAR2(8)	text(8)

Snowflake (`snowflake`)

logical	physical	reverse
smallint	NUMBER(5,0)	smallint
integer	NUMBER(10,0)	integer
bigint	NUMBER(19,0)	bigint
decimal(12, 2)	NUMBER(12,2)	decimal(12, 2)
float	FLOAT	double
double	FLOAT	double
boolean	BOOLEAN	boolean
text	VARCHAR	text
text(255)	VARCHAR(255)	text(255)
char(2)	CHAR(2)	char(2)
date	DATE	date
time	TIME	time
timestamp	TIMESTAMP_NTZ	timestamp
timestamptz	TIMESTAMP_TZ	timestamptz
uuid	VARCHAR(36)	text(36)
binary	BINARY	binary
binary(16)	BINARY(16)	binary(16)
json	VARIANT	json
enum status	VARCHAR(8)	text(8)

BigQuery (`bigquery`)

logical	physical	reverse
smallint	INT64	bigint
integer	INT64	bigint
bigint	INT64	bigint
decimal(12, 2)	NUMERIC(12,2)	decimal(12, 2)
float	FLOAT64	double
double	FLOAT64	double
boolean	BOOL	boolean
text	STRING	text
text(255)	STRING(255)	text(255)
char(2)	STRING(2)	text(2)
date	DATE	date
time	TIME	time
timestamp	DATETIME	timestamp
timestamptz	TIMESTAMP	timestamptz
uuid	STRING(36)	text(36)
binary	BYTES	binary
binary(16)	BYTES(16)	binary(16)
json	JSON	json
enum status	STRING	text

Redshift (`redshift`)

logical	physical	reverse
smallint	smallint	smallint
integer	integer	integer
bigint	bigint	bigint
decimal(12, 2)	numeric(12,2)	decimal(12, 2)
float	real	float
double	double precision	double
boolean	boolean	boolean
text	varchar(65535)	text
text(255)	varchar(255)	text(255)
char(2)	char(2)	char(2)
date	date	date
time	time	time
timestamp	timestamp	timestamp
timestamptz	timestamptz	timestamptz
uuid	varchar(36)	text(36)
binary	varbyte(1024000)	binary
binary(16)	varbyte(16)	binary(16)
json	super	json
enum status	varchar(8)	text(8)

Databricks (`databricks`)

logical	physical	reverse
smallint	SMALLINT	smallint
integer	INT	integer
bigint	BIGINT	bigint
decimal(12, 2)	DECIMAL(12,2)	decimal(12, 2)
float	FLOAT	float
double	DOUBLE	double
boolean	BOOLEAN	boolean
text	STRING	text
text(255)	STRING	text
char(2)	STRING	text
date	DATE	date
time	STRING	text
timestamp	TIMESTAMP_NTZ	timestamp
timestamptz	TIMESTAMP	timestamptz
uuid	STRING	text
binary	BINARY	binary
binary(16)	BINARY	binary
json	STRING	text
enum status	STRING	text

Db2 LUW (`db2`)

logical	physical	reverse
smallint	SMALLINT	smallint
integer	INTEGER	integer
bigint	BIGINT	bigint
decimal(12, 2)	NUMERIC(12, 2)	decimal(12, 2)
float	REAL	float
double	DOUBLE	double
boolean	BOOLEAN	boolean
text	CLOB(1M)	-
text(255)	VARCHAR(255)	text(255)
char(2)	CHAR(2)	char(2)
date	DATE	date
time	TIME	time
timestamp	TIMESTAMP	timestamp
timestamptz	TIMESTAMP WITH TIME ZONE	timestamptz
uuid	CHAR(36)	char(36)
binary	BLOB(1M)	binary(1000000)
binary(16)	BLOB	binary
json	CLOB(1M)	-
enum status	STATUS	-

Teradata (`teradata`)

logical	physical	reverse
smallint	smallint	smallint
integer	integer	integer
bigint	bigint	bigint
decimal(12, 2)	decimal(12, 2)	decimal(12, 2)
float	real	float
double	float	double
boolean	byteint	-
text	clob	-
text(255)	varchar(255)	text(255)
char(2)	char(2)	char(2)
date	date	date
time	time(0)	time
timestamp	timestamp(6)	timestamp
timestamptz	timestamp(6) with time zone	timestamptz
uuid	char(36)	char(36)
binary	blob	binary
binary(16)	blob	binary
json	json	json
enum status	status	-

MongoDB (`mongodb`)

logical	physical	reverse
smallint	smallint	smallint
integer	integer	integer
bigint	bigint	bigint
decimal(12, 2)	numeric(12, 2)	decimal(12, 2)
float	real	float
double	double precision	double
boolean	boolean	boolean
text	text	text
text(255)	varchar(255)	text(255)
char(2)	char(2)	char(2)
date	date	date
time	time	time
timestamp	timestamp	timestamp
timestamptz	timestamp with time zone	timestamptz
uuid	char(36)	char(36)
binary	blob	binary
binary(16)	blob	binary
json	text	text
enum status	status	-

Feature support

feature	postgresql	sqlite	mysql	sqlserver	oracle	snowflake	bigquery	redshift	databricks	db2	teradata	mongodb
schemas	yes	no	yes	yes	yes	yes	yes	yes	yes	yes	no	no
enums	yes	no	no	no	no	no	no	no	no	no	no	no
sequences	yes	no	no	yes	yes	yes	no	no	no	yes	no	no
identity	yes	yes	yes	yes	yes	yes	no	yes	yes	yes	yes	no
comments	yes	no	yes	yes	yes	yes	yes	yes	yes	yes	yes	no
check_constraints	yes	yes	yes	yes	yes	no	no	no	yes	yes	yes	no
partial_indexes	yes	yes	no	yes	no	no	no	no	no	no	no	no
index_include	yes	no	no	yes	no	no	no	no	no	no	no	no
index_methods	yes	no	no	no	no	no	no	no	no	no	no	no
deferrable_foreign_keys	yes	yes	no	no	yes	no	no	no	no	no	no	no
alter_table_add_foreign_key	yes	no	yes	yes	yes	yes	yes	yes	yes	no	no	no
alter_column_type	yes	no	yes	yes	yes	yes	yes	yes	no	yes	no	no
drop_column	yes	yes	yes	yes	yes	yes	yes	yes	yes	yes	yes	no
rename_column	yes	yes	yes	yes	yes	yes	yes	yes	yes	yes	yes	no
rename_table	yes	yes	yes	yes	yes	yes	yes	yes	yes	yes	yes	no
alter_column_default	yes	no	yes	yes	yes	yes	no	yes	yes	yes	no	no
alter_column_nullability	yes	no	yes	yes	yes	yes	yes	yes	yes	yes	no	no
array_types	yes	no	no	no	no	no	no	no	no	no	no	no
collation	yes	yes	yes	yes	no	no	no	no	no	no	no	no
table_options	no	no	yes	no	no	yes	yes	yes	yes	yes	yes	no
unsigned	no	no	yes	no	no	no	no	no	no	no	no	no
expression_indexes	yes	yes	yes	no	yes	no	no	no	no	no	no	no
indexes	yes	yes	yes	yes	yes	no	no	no	no	yes	yes	no
unique_constraints	yes	yes	yes	yes	yes	yes	no	yes	no	yes	yes	no
referential_actions	yes	yes	yes	yes	yes	yes	no	no	no	yes	yes	no
identity_on_derived_keys	no	no	no	no	no	yes	no	yes	yes	yes	yes	no
enum_as_check_on_derive	no	no	no	no	no	no	no	no	yes	yes	yes	no
clustering	no	no	no	no	no	yes	yes	no	yes	no	no	no
distribution_keys	no	no	no	no	no	no	no	yes	no	no	no	no
table_partition_clause	no	no	no	no	no	no	yes	no	yes	no	no	no
not_enforced_constraints	no	no	no	no	no	no	yes	no	no	no	no	no
transient	no	no	no	no	no	yes	no	no	no	no	no	no
triggers	yes	no	yes	yes	yes	no	no	no	no	no	no	no
routines	yes	no	yes	yes	yes	no	no	no	no	no	no	no
materialized_views	yes	no	no	yes	yes	no	no	no	no	no	no	no
partitioning	yes	no	yes	no	yes	no	no	no	no	no	no	no
roles	yes	no	yes	yes	yes	no	no	no	no	no	no	no
grants	yes	no	yes	yes	yes	no	no	no	no	no	no	no
generated_columns	yes	no	no	no	no	no	no	no	no	yes	no	no
extended_identity	yes	no	no	no	no	no	no	no	no	yes	yes	no
index_kinds	yes	no	no	no	no	no	no	no	no	no	no	no
exclusion_constraints	yes	no	no	no	no	no	no	no	no	no	no	no
row_policies	yes	no	no	no	no	no	no	no	no	no	no	no
collations	yes	no	no	no	no	no	no	no	no	no	no	no
extensions	yes	no	no	no	no	no	no	no	no	no	no	no