Three databases
10 / 11 · dialects in the full document · PDF
The same view loads and saves on PostgreSQL, MySQL 8 and SQLite through SQLx 0.9; each enabled database gets its own decoder and encoder, and a load runs on the database of the connection it is given. Where the databases differ, Mabat uses each one's own syntax; the shapes, plans, aliases, checks and errors are the same on all three.
| PostgreSQL | MySQL 8 | SQLite | |
|---|---|---|---|
| Enabled by | the postgres feature, the default MPA-DB-1 | the mysql feature | the sqlite feature |
| Keys of a batched query | one array parameter: = ANY($1) | IN (?, …), padded to a power of two so statements are reused, at most 1,000 keys a child statement MPA-DB-4 | as MySQL |
| Keys in an override | = ANY(:keys), or $1 MPA-OVR-4 | IN (:keys), expanded to the placeholders | as MySQL |
| Key types | i16, i32, i64, String, Uuid MPA-VIEW-2 | also unsigned integers | also columns without a declared type, read by value |
| Case-insensitive match | ILIKE MPA-LOAD-5 | LOWER(c) LIKE LOWER(?) | LOWER(c) LIKE LOWER(?) |
| Enum tag columns | any type, such as a PostgreSQL enum, read as text MPA-SUM-1 | any type, such as ENUM(…), read as text | any type, read as text |
| Concurrent loads | Pooled::snapshot or Pooled::read_committed MPA-LOAD-11 | Pooled::read_committed; a snapshot does not compile | Pooled::read_committed; a snapshot does not compile |
| Save: a new row | INSERT … ON CONFLICT (key) DO UPDATE MPA-WRITE-3 | INSERT … AS new ON DUPLICATE KEY UPDATE | INSERT … ON CONFLICT (key) DO UPDATE |
| Versioned insert, if absent | INSERT … ON CONFLICT (key) DO NOTHING MPA-WRITE-9 | INSERT … SELECT … WHERE NOT EXISTS, since ON DUPLICATE KEY counts a found row as affected | INSERT … ON CONFLICT (key) DO NOTHING |
| Types only some support | #[view(databases = "postgres, mysql")] limits a view to the listed databases, for field types not every enabled database can decode, such as a PostgreSQL array, or rust_decimal::Decimal on SQLite MPA-DB-2. A registry is built for one database; using it on another fails with Error::WrongBackend MPA-DB-3. | ||
One trait, three implementations. mabat-sqlx runs everything through a Backend trait implemented for each database: how rows are fetched and decoded, how keys are read and bound, how statements and their arguments are executed, how column types are described for the checks, and how a snapshot begins. The derive generates a decoder and an encoder for each enabled database, so a view whose field type one database cannot handle fails to compile there, not at run time — unless it opts out of that database.
The same everywhere. The planner and the SQL renderer in mabat-core take a dialect, not a connection: the plan of a view, its query names, its aliases and the order of its queries do not depend on the database. The checks of overrides prepare and describe queries on whichever database the registry is built for, and the tests run on all three databases, including end-to-end suites against the Chinook sample database on each and Pagila on PostgreSQL.