SQL Dialects & Portability

SQL Portability Strategy

Portability is the ability to move or support a workload with known, tested costs—not the absence of vendor features. Keep invariants and a useful relational/query core portable, isolate consequential differences behind explicit adapters, and specialize deliberately when the operational or correctness gain pays for the boundary.

Choose a portability target, not an aspiration.
Portable does not mean lowest common denominator.
Separate syntax from semantics.
Make the database boundary observable.
Migrations are part of the portability contract.
Cross-dialect tests need semantic oracles.

Layered portability boundary

Portability decision framework
Decision	Portable core	Adapter/capability boundary	Evidence
Integrity	PK/FK/unique/check and explicit nullability	deferral, exclusion/predicate features	race tests on real engines
Queries	joins, predicates, aggregates, stable ordering	pagination, upsert, returning, JSON/search	result + concurrency contracts
Types	bounded semantic domain and conversion rules	native UUID/JSON/time-zone/binary types	boundary/overflow round trips
Transactions	short unit, rollback, retry protocol	isolation mapping, lock hints/modes	anomaly and deadlock tests
Evolution	logical target schema	online-DDL and migration steps	upgrade rehearsal and timing
Operations	RPO/RTO and telemetry requirements	backup, replication, failover tooling	restore/failover drills

Capability contract

DatabaseCapabilities {
  serverProduct, serverVersion, driverVersion
  generatedKeyStrategy
  paginationStrategy
  conflictWriteStrategy
  ddlAtomicityClass
  supportedIsolationContracts
}

At startup: detect and reject unsupported combinations.
In CI: run the same contract suite on every supported version.
In production: expose chosen capabilities as bounded telemetry.