Support MySQL and SQLite engines

[iamralch]

Problem

sqlc-gen-sqlx only generates for PostgreSQL. sqlc itself reports the target backend in GenerateRequest.settings.engine, but the generator ignores it and hardcodes sqlx::Postgres, PgPool, PgQueryResult and the PostgreSQL type table throughout. Pointing the plugin at a engine: mysql or engine: sqlite config today produces code that does not compile.

sqlx supports all three backends with the same Executor/FromRow/query_as surface, so most of the generator is already engine-agnostic — the backend-specific parts are few and identifiable.

Result I'd like

sql[*].engine selects the sqlx driver, with postgresql behaviour unchanged, mysql and sqlite supported, and anything else rejected with a clear error.

What actually differs between the engines

I went through this carefully, because a couple of the differences are not obvious:

	PostgreSQL	MySQL	SQLite
Driver types	PgPool / PgQueryResult	MySqlPool / MySqlQueryResult	SqlitePool / SqliteQueryResult
Placeholders	$1 numbered	? ordinal	? ordinal
:execlastid	RETURNING column	u64 from last_insert_id()	i64 from last_insert_rowid()
Enums	#[sqlx(type_name)]	needs a hand-written Type impl (below)	plain derive
Composites	yes	no	no
Bind-param cap	65535	65535	32766

Two findings worth flagging on their own, since they are the parts most likely to be got wrong:

1. sqlc.slice() bind order on ? engines. sqlc lifts the slice out of the ordinal numbering. For WHERE id IN (sqlc.slice(ids)) AND country = ? it reports country as number 1 and ids as number 2 — even though the slice comes first in the query text. Ordering binds by Parameter.number therefore silently swaps the arguments. The order has to come from the query text itself. Relatedly, the /*SLICE:ids*/ marker follows the column's name (ids), not original_name (id); the PostgreSQL path never noticed because it falls back to replacing the bare $N.

2. MySQL enums cannot use #[derive(sqlx::Type)]. sqlx's derive reports MySqlTypeInfo::__enum(), whose PartialEq compares column flags, so a real ENUM column never matches and try_get fails at runtime with "Rust type Status (as SQL type ENUM) is not compatible with SQL type ENUM". Since the derive expands to Encode + Decode + Type, deriving only the first two and writing the Type impl (deferring compatibility to str) fixes it.

Reference implementation

I have this working end to end, split into two reviewable changes on my fork. Offered as prior art for whoever picks this up — happy for it to be rewritten, or to rework it against your review:

• MySQL + the Engine abstraction — feat(engine): add MySQL support iamralch/sqlc-gen-sqlx#1
• SQLite, stacked on the above — feat(engine): add SQLite support iamralch/sqlc-gen-sqlx#2

Notes on the shape of it:

• All 30 existing PostgreSQL snapshots are byte-identical. A request with no engine still defaults to PostgreSQL.
• Every match on Engine is exhaustive, so adding the third backend made the compiler list exactly the four decision points that needed a SQLite answer.
• The e2e harness reads engine: from each case's sqlc config and starts one container per engine in use (SQLite runs off a temp file). Six new cases cover CRUD, slice expansion including the interleaved-scalar ordering, ENUM roundtrips and a 250-row :copyfrom. There are also 18 new codegen tests and 12 snapshots.

Separate pre-existing bug found along the way

The dynamic-slice codegen emits sqlx::query_as(&sql), which sqlx 0.9 rejects — it now requires SqlSafeStr, and &String is not &'static str. This affects PostgreSQL identically and is unrelated to engine support; no existing example exercises a non-ANY slice, which is why it has gone unnoticed. The workspace declares sqlx 0.9 while the e2e fixtures pin 0.8, so the test suite does not catch it either.

The fix is sqlx::query_as(sqlx::AssertSqlSafe(sql)), which does not exist in 0.8 — so it is a "which sqlx version does generated code target" decision rather than a mechanical fix, and I left it alone. Happy to open a separate Discussion for it if you'd prefer it tracked on its own.

[iamralch]

Update: I went ahead and fixed the sqlx 0.9 incompatibility I mentioned at the bottom of the proposal, rather than leaving it as a separate question. It turned out to be a prerequisite rather than a footnote, so it is now the bottom of the stack — reviewable and mergeable entirely on its own, without any of the engine work.

Revised stack (each based on the one above it):

1. fix(codegen): make dynamic-slice queries compile on sqlx 0.9 iamralch/sqlc-gen-sqlx#3 — fix(codegen): make dynamic-slice queries compile on sqlx 0.9
2. feat(engine): add MySQL support iamralch/sqlc-gen-sqlx#1 — MySQL + the Engine abstraction
3. feat(engine): add SQLite support iamralch/sqlc-gen-sqlx#2 — SQLite

On the fix itself: run-time SQL now goes through sqlx::AssertSqlSafe, the escape hatch 0.9 added for this case. The strings are assembled by the generator from the query text plus a placeholder count — no user input reaches them — so the assertion is sound, and the AssertSqlSafe<String> impl takes the String by value, dropping the allocation the old &sql borrow implied. Nine call sites; only the two dynamic-slice snapshots change, since every other generated query passes a &'static str const that 0.9 still accepts directly.

The e2e fixtures move from sqlx 0.8 to 0.9 so the suite finally covers the same version the workspace declares. That is the gap that hid this: the fixtures were a version behind, and no example had a sqlc.slice() query missing the = ANY($1) fast path, so nothing ever compiled the affected output.

This does set a version floor. AssertSqlSafe does not exist in 0.8, so generated code cannot support both — 0.9 becomes the minimum, and the README says so up front. If you would rather keep 0.8 working, the alternative is emitting the call site conditionally behind a config option, which I did not want to assume. Happy to go that way instead.

With the fix in place both new examples now carry a real sqlc.slice() query, verified against a live MySQL 8.4 and an in-memory SQLite, so the dynamic path has example coverage as well as e2e coverage for the first time.

[jrandolf]

Thanks for this, and for splitting it into a clean stack. The write-up was right on two things that are easy to get wrong: slice bind order, and the MySQL ENUM / Type derive problem.

The sqlx 0.9 fix (your #3) is accepted. I reproduced the E0277 against sqlx 0.9.0 with what main generates today. I also checked every dynamic-slice command on 0.9 against PostgreSQL 17: :one, :many, :exec, :execrows, :execresult, :execlastid, and the three batch streams. It's up as #50 with your commit and authorship unchanged.

MySQL (#1) and SQLite (#2) aren't ready to merge yet. The Engine split is the right shape, the PostgreSQL snapshots are byte-identical, and your e2e suite passes for us on all three engines. But every MySQL test schema uses a signed BIGINT key, and every test query uses positional ? only. When I ran the generated code against MySQL 8.4 and SQLite with other common schemas, I hit these:

1. MySQL UNSIGNED columns fail at runtime. sqlc doesn't put unsigned in the type name. It sends type = "bigint" with Column.unsigned = true, and INT(11) UNSIGNED arrives as int with length = 11. So the "bigint unsigned" / "int unsigned" keys never match, and id BIGINT UNSIGNED AUTO_INCREMENT (or SERIAL) becomes i64:

   ColumnDecode { index: "\"id\"", source: "mismatched types; Rust type `i64` (as SQL type `BIGINT`) is not compatible with SQL type `BIGINT UNSIGNED`" }
   

   tests/codegen_mysql.rs builds its requests with "int unsigned" as the type name, which sqlc 1.31 never sends.

2. MySQL BIT(n) fails at runtime. The error is Rust type Vec<u8> (as SQL type BLOB) is not compatible with SQL type BIT. sqlx decodes BIT(1) as bool and wider BIT(n) as u64.

3. MySQL BOOLEAN / TINYINT(1) becomes i8. sqlc sends tinyint with length = 1, which is how sqlc-go picks bool. So Column.length does tell them apart.

4. Slice bind order is wrong when a sqlc.arg() comes before a positional ?. ordinal_bind_order fills scalar slots in Parameter.number order, but sqlc numbers positional ? before named args. Take this query:

   WHERE name = sqlc.arg(who) AND country = ? AND age IN (sqlc.slice(ages))

   country is bound to name = ? and who to country = ?, and the query silently returns no rows. For ? engines sqlc already lists params in text order (your own example comes back as ids chore: add crate publish metadata #2, then country chore(main): release 0.1.0 #1). The non-slice path already binds in that order. Using it instead of number fixes this.

5. SQLite named params around a slice return the wrong rows. sqlc rewrites SQLite named params to numbered placeholders. So this query:

   WHERE category = @category AND id IN (sqlc.slice(ids)) AND name <> @exclude

   arrives as category = ?1 AND id IN (/*SLICE:ids*/?) AND name <> ?3. The scanner treats ?1 and ?3 as bare ?. After expansion, SQLite gives the anonymous ?s indexes 2–4, so ?3 now points at the second slice element. sqlx binds ?NNN by number but bare ? in sequence. With ids = [1, 2, 3, 4] and exclude = "b", the query returns ids [1, 2] instead of [1, 3]. Reusing a named param (?1 … ?1) fails generation with the placeholder-count error. The rewrite needs to handle ?NNN as well, for example by turning every placeholder into an anonymous ? and binding once per occurrence.

6. MySQL SET columns become single-value enums. sqlc reports SET('a','b','c') as a catalog enum, so a row holding 'a,b' fails with invalid value "a,b" for enum UsersTags. The request doesn't say whether a column is a SET or an ENUM, so at minimum this needs documenting, with a column override to String as the workaround.

Smaller parity notes:

• SQLite JSON columns and any-typed expressions like max(x) fail generation with unknown SQLITE type. sqlc-go falls back to interface{} here. Erroring is fine, but the README should say so and point to overrides and CAST.
• The placeholder scanner treats \' as an escape. SQLite strings don't use backslash escapes, and neither does MySQL with NO_BACKSLASH_ESCAPES. A string literal ending in \ therefore hides every placeholder after it.

For the next round, the e2e cases need these shapes: BIGINT UNSIGNED AUTO_INCREMENT or SERIAL keys, BOOLEAN, BIT, a sqlc.arg() before a positional ? next to a slice, and SQLite @named params around a slice. Each of those broke here.

While testing I also found two naming bugs that are already on main and affect PostgreSQL too, so they aren't yours:

• A column like address2 becomes field address_2 without #[sqlx(rename)], so decoding fails with ColumnNotFound.
• sqlc.arg() params are named after the column instead of the argument. As a result, created_at > sqlc.arg(start_at) AND created_at < sqlc.arg(end_at) doesn't compile.

We'll fix those separately.

We'll take the MySQL and SQLite work from here, starting from your branches and fixing the points above. As with #50, the implementation PRs will link back to this Discussion and credit you. Thanks again for the groundwork.

[iamralch]

@jrandolf any chance to look at this proposal