Keyboard shortcuts

Press ← or → to navigate between chapters

Press S or / to search in the book

Press ? to show this help

Press Esc to hide this help

Dialect notes

Postgres, MySQL/MariaDB, and SQLite share the same schema DSL but differ in what they can express natively.

Type mapping

DSL typePostgresMySQL / MariaDBSQLiteNotes
StringTEXTVARCHAR(255)TEXT
IntINTEGERINTINTEGER
BigIntBIGINTBIGINTINTEGERSQLite only has one integer type.
FloatDOUBLE PRECISIONDOUBLEREAL
DecimalNUMERICDECIMAL(65,30)TEXTAvoid arithmetic on SQLite.
BooleanBOOLEANTINYINT(1)INTEGERMySQL and SQLite use 0/1.
DateTimeTIMESTAMPTZDATETIME(6)TEXTISO-8601 in SQLite.
DateDATEDATETEXT
TimeTIMETIMETEXT
UuidUUIDCHAR(36)TEXT
JsonJSONBJSONTEXTSQLite stores as text.
BytesBYTEABLOBBLOB

Migrations

ChangePostgresMySQL / MariaDBSQLite
Add / drop columnALTER TABLEALTER TABLEALTER TABLE (3.35+) or table rebuild.
Rename columnALTER TABLE ... RENAME COLUMNALTER TABLE ... CHANGE COLUMNTable rebuild.
Change typeALTER TABLE ... ALTER COLUMN ... TYPEALTER TABLE ... MODIFY COLUMNTable rebuild.
Add / drop FKALTER TABLE ... ADD/DROP CONSTRAINTALTER TABLE ... ADD/DROP FOREIGN KEYTable rebuild (cannot add FK after create).
Add enum variantALTER TYPE ... ADD VALUERebuild the column check constraint.Rebuild CHECK constraint on each using column.

MySQL has no DML RETURNING clause. The runtime executes the insert and then selects the inserted row by its primary key; auto-increment keys use LAST_INSERT_ID(). Upserts use ON DUPLICATE KEY UPDATE. MariaDB-specific RETURNING syntax is intentionally not emitted so the same schema remains valid on both MySQL and MariaDB.

Capabilities

#![allow(unused)]
fn main() {
let cap = SqliteDialect.capabilities();
assert!(!cap.native_enums);       // enums are emulated
assert!(!cap.native_uuid);        // stored as text
assert!(!cap.alter_column_type);  // type changes require rebuild
assert!(cap.drop_column);         // SQLite 3.35+
}

ruprizzle validate prints warnings when you use a construct that the active provider handles poorly, e.g. Decimal on SQLite.

Query builder

The runtime query builder compiles to SQL per dialect. If a construct cannot be expressed on the target database, to_sql() returns Error::Message(...) instead of emitting unsupported SQL:

ConstructPostgresMySQL / MariaDBSQLite
INNER JOINyesyesyes
LEFT JOINyesyesyes
RIGHT JOINyesyesno
FULL OUTER JOINyesnono
UNION / UNION ALLyesyesyes
INTERSECTyesnoyes
EXCEPTyesnoyes

MySQL does not support INTERSECT or EXCEPT; MariaDB does not support INTERSECT or EXCEPT either. SQLite supports both but does not support RIGHT JOIN or FULL OUTER JOIN.

v1.1–v1.5 features per dialect

The feature line shipped in 1.5.1 lands differently on each backend:

FeaturePostgresMySQL / MariaDBSQLite
Array columns + filter operatorsNative T[]JSON + JSON_CONTAINS / JSON_OVERLAPSTEXT (JSON)
Full-text searchNative tsvector / tsquery, @@fulltext + generated columnMATCH ... AGAINSTFTS5 virtual table
Soft deletes@softDelete field + transparent WHERE filtersamesame
PostGIS / geospatialgeo-types mapping, spatial indexesnono
Implicit many-to-manyjoin table emitted by migrate devsamesame
Read-replica routing / query cache / OTelruntime-level, dialect-agnosticsamesame
Serverless adapters——ruprizzle-turso (Hrana over HTTP), ruprizzle-d1 (Cloudflare REST)

Turso and D1 are SQLite-family dialects reached over HTTP: one request per statement, no interactive transactions — see docs/KnownLimitations.md for the full caveat list.

MySQL-specific notes

  • No RETURNING clause. Inserts and upserts rely on a primary-key follow-up query or LAST_INSERT_ID() for auto-increment keys.
  • No native ENUM types. Enumerations are enforced with CHECK constraints.
  • No FULL OUTER JOIN or RIGHT JOIN on older versions. Prefer LEFT JOIN or emulate with UNION where necessary.
  • String[] is stored as JSON. Array containment and overlap are implemented with JSON_CONTAINS and JSON_OVERLAPS.
  • Uuid is stored as CHAR(36); Decimal uses DECIMAL(65,30) (or a narrower DECIMAL(19,4) when declared with @db.Decimal(p,s)).
  • Upserts use ON DUPLICATE KEY UPDATE, keyed on the primary key and unique constraints.