Chain builder

Appearance

Dialect Differences ​

The dialect is a type parameter — QueryBuilder<Postgres>, QueryBuilder<MySql>, QueryBuilder<Sqlite> — so every difference below is decided at compile time, with no runtime dialect switch to misconfigure. The same chain compiles to the right SQL for each backend; where a feature does not exist on a backend, the builder either fails loud with a BuildError, lowers the feature to an equivalent form, or (in two documented cases) silently drops it. This page is the single comparison table, with a short note per row pointing at the detailed page.

The comparison table ​

PostgreSQLMySQLSQLite
Placeholder$1, $2, …??
Identifier quote"col"`col`"col"
Upsert styleON CONFLICT (…) DO NOTHING / DO UPDATE SET …INSERT IGNORE / ON DUPLICATE KEY UPDATE …ON CONFLICT (…) DO NOTHING / DO UPDATE SET …
RETURNING✓silently dropped✓
DISTINCT ON✓BuildErrorBuildError
Row locking (FOR UPDATE / FOR SHARE)✓✓silent no-op
ILIKEnative ILIKELOWER(col) LIKE LOWER(?)LOWER(col) LIKE LOWER(?)

Placeholders ​

Postgres uses numbered placeholders ($1, $2, …, numbered in bind order and kept contiguous across CTEs, subqueries and UNION arms); MySQL and SQLite use positional ?. You never write a placeholder yourself for bound values — but raw fragments (select_raw, having_raw, …) must use the target dialect's syntax, see the placeholder contract in SELECT. Bind ordering details: Binds & Values.

Identifier quoting ​

Every identifier the builder emits is quoted and escaped: double quotes on Postgres and SQLite, backticks on MySQL. Quote characters embedded in a name are doubled (" → "", ` → ``), so identifiers can never break out of their quoting. See the Security Model.

Upsert style ​

Postgres and SQLite share the ON CONFLICT grammar; MySQL has its own. on_conflict_do_nothing(targets) renders ON CONFLICT (…) DO NOTHING on Postgres/SQLite and INSERT IGNORE on MySQL (which ignores the conflict targets — MySQL decides conflicts by whatever unique key collides). on_conflict_merge(targets) renders ON CONFLICT (…) DO UPDATE SET … vs ON DUPLICATE KEY UPDATE …. Full semantics and caveats: Upsert & RETURNING.

RETURNING ​

Supported on Postgres and SQLite. MySQL has no RETURNING clause, and the builder silently drops it there rather than emitting invalid SQL — a returning(["id"]) chain still compiles and executes on MySQL, it just returns no rows. If you need the inserted id on MySQL, use execute's QueryResult (last_insert_id). Details and the reasoning: Upsert & RETURNING.

DISTINCT ON ​

A Postgres-only feature. Compiling a distinct_on(...) builder for MySQL or SQLite is BuildError::DistinctOnRequiresPostgres from try_to_sql() (or a panic with DISTINCT ON requires PostgreSQL from to_sql()) — there is no silent fallback, because plain DISTINCT has different semantics. See SELECT and Error Handling.

Row locking ​

for_update() / for_share() (plus skip_locked() / no_wait()) render on Postgres and MySQL. SQLite has no row-level locks — it locks the whole database per transaction — so the entire lock clause is a silent no-op there. The structural guards (LockRequiresSelect, and LockWithUnion on locking dialects) still apply; the full matrix, including why SQLite's lock-with-UNION is a no-op rather than an error, is in Row Locking.

ILIKE ​

where_ilike is portable case-insensitive matching: Postgres compiles it to its native col ILIKE $n; MySQL and SQLite lower it to LOWER(col) LIKE LOWER(?). Semantics are equivalent for ASCII; for collation- and Unicode-edge details (and the LIKE-wildcard caveat), see WHERE and Case-insensitive Search.