Schema-diff generator: transaction wrapper and ADD/DROP COLUMN paths are still single-dialect

[cevheri]

Summary

#269 made the modified-column path of the migration generator dialect-aware. The rest of the same
generator still emits one shape for everyone, so the same defect class survives one path over: the
transaction wrapper, ADD COLUMN, DROP COLUMN, and the newer type ids that #269 classified as unable
to express DDL at all.

Found while implementing #269 (recorded there as deliberately out of scope, per the loop's one-task
rule).

1. The transaction wrapper is only correct for PostgreSQL and MySQL

src/lib/schema-diff/migration-generator.ts:290-293 and :324

if (dialect !== "sqlite") {
  sections.push("BEGIN;");
  ...
sections.push("COMMIT;");

Every dialect except SQLite gets a bare BEGIN; / COMMIT; pair:

• MSSQL — BEGIN; alone opens a statement block that expects a matching END; the transaction
  spelling is BEGIN TRANSACTION. The script cannot parse.
• Oracle — BEGIN opens a PL/SQL block and also needs END;. Oracle additionally auto-commits
  DDL, so the wrapper is meaningless even when spelled right.
• ClickHouse, Druid, Couchbase, MongoDB, Redis, embedded libredb — no transactional DDL to wrap
  at all. Druid has no DDL, and the last three are not SQL.

So the wrapper is valid for exactly two of the eleven type ids, and actively invalid for two more.

2. Added and removed columns still emit one shape for everyone

:130-135 — added columns:

lines.push(`ALTER TABLE ${id} ADD COLUMN ${generateColumnDef(col, dialect)};`);

No dialect branch at all. ADD COLUMN is not valid T-SQL (ALTER TABLE t ADD c INT) and not valid in
Oracle (ALTER TABLE t ADD (c NUMBER)), and the type ids that #269 just classified as unable to modify
a column get a bare ALTER TABLE ... ADD COLUMN here rather than the honest comment that path now
emits.

:137-146 — removed columns branch for SQLite only, with the same consequence for everyone else.

3. Index and FK paths are partially aware

:238-247 does branch (mysql vs the rest), but the fallback is DROP INDEX IF EXISTS <name>;, which
Oracle does not accept (IF EXISTS is not Oracle syntax) and MSSQL requires an ON <table> for. Worth
auditing in the same pass rather than filed separately.

Suggested shape

Whatever #269 established for the modified-column path, applied to the rest of the generator: a branch
where a dialect genuinely differs, and the honest "this dialect cannot express it" comment where it
cannot. The dialect table in tests/unit/schema-diff/migration-generator.test.ts is now
Record<DatabaseType, ...>, so a new provider fails typecheck until it is classified — extend that
mechanism to whichever paths this work touches instead of adding prose guards.

Engine-syntax claims above come from each dialect's documented grammar, not from a live probe against
the containers in docker-compose. Worth confirming against a live MSSQL and Oracle before settling on
the emitted forms, the way #264 and #265 did.

[cevheri]

Status: known limitation, not scheduled — and staying open for that reason.

The generator can emit migration SQL that is unrunnable outside PostgreSQL, and a user meets that as broken SQL rather than as a missing feature. Closing it would hide the limitation instead of deferring it, so it keeps its issue while two neighbours from the same sweep were resolved differently: #288 was rejected in the contract where it was lying, and #279 moved to docs/BACKLOG.md because the capability gate already makes the software honest about it.

What changed instead, in #311: the module header of src/lib/schema-diff/migration-generator.ts now names the one path that is dialect-aware (the modified-column path, #269) and the four that are not — the transaction wrapper, ADD COLUMN, DROP COLUMN, and the index/FK fallbacks. That gap was invisible from a call site, since generateMigrationSQL(diff, dialect) takes a dialect either way.

This is a good pickup for an outside contributor. It is well specified, self-contained in one 318-line module, and the test file already carries a Record<DatabaseType, …> dialect table, so a new provider fails typecheck until it is classified — extend that mechanism rather than adding prose guards.

One caution before anyone starts: the engine-syntax claims in the issue body come from each dialect's documented grammar, not from a live probe. Check the emitted forms against a live MSSQL and Oracle before settling them — on this repo's last three providers the live run overruled the documented reading more than once.