One SQL adapter layer, thirty database engines, 6,471 lines

DataCopilot’s adapter layer is 6,471 lines across 30 registered database engines — 25 hand-written plus 5 generated from the PostgreSQL family — and it is the largest single file in the backend. That size is not translation work. Generating valid SQL for a different dialect is a small part of it. The bulk is the seven places where the SQL standard is silent, or where every vendor ignored it in a different direction, and where the same query text produces different rows, different types, or no cancellation at all depending on what it is pointed at.

This is what those seven places are, engine by engine.

What the lines actually go on

The distribution is not what people guess. Query construction — the part that looks like the job — is a minority of the code.

The contract each adapter implements is small. Keeping it small is what makes thirty of them maintainable:

# adapters/base.py — the contract. Python 3.11+, typing.Protocol.
from typing import Protocol, Any, Sequence
from datetime import timedelta


class Adapter(Protocol):
    name: str
    supports_read_only_transaction: bool
    supports_server_side_timeout: bool
    supports_wide_integers: bool
    default_null_ordering_asc: str          # "first" | "last"

    def quote_identifier(self, ident: str) -> str: ...
    def apply_limit(self, sql: str, n: int, *, has_order_by: bool) -> str: ...
    def interval_ago(self, delta: timedelta) -> str: ...
    def date_trunc(self, unit: str, expr: str) -> str: ...
    def order_by_nulls(self, expr: str, *, asc: bool, nulls: str) -> str: ...
    def begin_read_only(self, cur: Any, timeout_s: int) -> None: ...
    def normalise_row(self, row: Sequence[Any]) -> list[Any]: ...

    # Probes the conformance suite runs against a live engine.
    def wide_integer_probe(self) -> str: ...
    def sleep_probe(self, seconds: int) -> str: ...

Nine methods and four capability flags. Every engine-specific decision below lives behind one of them.

Identifier quoting and case folding

Four quote characters and three case-folding rules exist across common engines, and mixing them up produces the most confusing class of error, because the query is valid and refers to a table that does not exist.

The rule that follows: always quote, and always quote using the identifier exactly as introspection returned it. Never normalise case in the adapter. Oracle’s upper-case folding and MySQL’s filesystem-dependent behaviour cannot both be satisfied by any normalisation you choose.

Row limiting

Five syntaxes, and one of them changes the query’s semantics rather than adding to it.

Two of those rows are silent-wrong-answer generators rather than errors. Oracle’s ROWNUM is assigned before sorting, so SELECT ... WHERE ROWNUM <= 10 ORDER BY amount DESC returns ten arbitrary rows sorted, not the top ten — it runs, it returns data, and it is wrong. And Elasticsearch’s 10,000-hit window means a naive from/size pager stops producing results partway through a large set; the documented alternative is search_after, which needs a tiebreaker field or it will skip and repeat documents.

Date arithmetic

There is no common form for “seven days ago”. This is the section that makes people believe an LLM can write portable SQL until they try it.

Underneath the syntax there is a semantic divergence that costs more. Oracle’s DATE type carries a time component, so TRUNC is not a no-op on a column a user thinks of as a date. PostgreSQL distinguishes timestamp from timestamptz and only the second participates in time-zone conversion. MySQL’s TIMESTAMP converts on read according to the session time zone while DATETIME does not — so two columns in one table can disagree about what “yesterday” means. An adapter layer that normalises only the syntax has done the easy half.

NULL ordering

Default NULL placement in ORDER BY splits the engines two-and-two, and two of them cannot be told otherwise. This is the difference that makes “the same query” return a different top-10 on a different engine, with no error anywhere.

PostgreSQL and Oracle treat NULL as larger than any value — PostgreSQL’s sorting documentation states that NULLS FIRST is the default for DESC and NULLS LAST otherwise — while MySQL and SQL Server treat it as smaller. On the two engines without the syntax, the adapter emits a sort key instead:

-- MySQL / SQL Server: force NULLS LAST on an ascending sort.
ORDER BY CASE WHEN amount IS NULL THEN 1 ELSE 0 END, amount ASC

That expression is correct and it is also a performance decision — it can prevent an index from satisfying the sort. Which is the general shape of this whole layer: the portable form exists, and it costs something the non-portable form did not.

Type coercion at the driver boundary

This is where the production bugs are. The query is right, the rows are right, and the value that reaches the user is wrong.

Integers wider than 2⁵³. ClickHouse UInt64, Snowflake NUMBER(38,0) and Oracle NUMBER can all hold values that JavaScript cannot represent exactly. Serialise them to JSON as numbers and the browser silently rounds them. An identifier ending in ...992 becomes ...990, no error is raised anywhere, and the user reports that a record cannot be found. The fix is to carry wide integers as strings from the adapter outward, decided per column type at introspection time.

Decimals. NUMERIC and DECIMAL arrive as Python Decimal, which the standard JSON encoder refuses. Converting to float to make the error go away silently loses precision on monetary values. Serialise as a string and let the presentation layer format it.

Naive versus aware datetimes. PostgreSQL timestamptz yields an aware datetime; timestamp yields a naive one. Oracle DATE yields a naive datetime carrying a time component. SQL Server’s legacy datetime rounds to increments of roughly 3.33 milliseconds. Mixing these in one result set produces comparisons that are wrong by hours.

Values with no Python equivalent. MySQL permits '0000-00-00' dates, which are not representable as datetime.date. ClickHouse returns booleans as UInt8. Every adapter needs a normalise_row that is explicit about these rather than a driver default that varies by version.

Timeouts, cancellation and read-only enforcement

The capability flags in the adapter contract exist because two engines in common use cannot do what the other twenty-eight can, and both gaps are safety-relevant.

SQL Server is the row to internalise. Microsoft’s own documentation states it plainly: query timeouts are a client-side concept, and the remote query timeout setting applies only to queries the engine itself issues outbound. On SQL Server the adapter’s timeout is the driver’s CommandTimeout and a client-issued cancellation — which means a network partition between the application and the database leaves the query running on the server with nothing to stop it.

BigQuery is the other outlier, and in the opposite direction. A runaway query there is not a duration problem, it is an invoice. maximum_bytes_billed is the control that matters, and an adapter that only implements timeouts has protected the wrong resource.

The general principle across all of them: the transaction flag is a convenience, the grant is the control. DataCopilot runs every query in a read-only, time-bounded transaction where the engine supports one, but the property that actually holds is that the connection’s credentials cannot write. Three of the engines above have no read-only transaction at all, and on those the grant is the only thing standing between a generated query and a modified table.

The three engines that are not SQL

Three of the thirty do not speak SQL, and the honest adapter design admits that rather than emulating it.

MongoDB has an aggregation pipeline. $lookup is not a join in the relational sense, there is no schema to introspect — collections are sampled instead — and the same field can hold different types in different documents. The adapter surfaces a sampled schema and is explicit that it is sampled.

Cassandra speaks CQL, which looks enough like SQL to be dangerous. There are no joins. A WHERE clause that does not include the partition key either fails or requires ALLOW FILTERING, which turns a query into a full-cluster scan. Aggregations across partitions are not supported the way an analyst expects. An adapter that accepts arbitrary generated SQL for Cassandra is an adapter that will eventually take a production cluster down.

Elasticsearch carries the subtlest trap in the whole layer. Its cardinality aggregation — the natural mapping for COUNT(DISTINCT x) — is approximate, implemented over a HyperLogLog++ sketch whose error grows with the number of distinct values above the precision_threshold. It returns an integer. It looks exact. It is not, and no error, warning or type distinction tells the caller. Terms aggregations similarly report a documented count error bound that most clients discard. An adapter that maps COUNT(DISTINCT ...) onto it without propagating the approximation is manufacturing false precision, and the correct behaviour is to label the result as approximate all the way to the user interface.

What is generated and what is hand-written

Of the 30 registered adapters, 25 are declared and 5 are generated from the PostgreSQL family. Generation is safe only under one condition: the derived engine’s behaviour must be a subtraction from the base — same wire protocol, same quoting, same folding, same limit syntax, with features missing rather than changed.

That holds for the PostgreSQL-compatible engines and does not hold anywhere else. Anything that changes a rule instead of removing a feature gets a hand-written adapter, and every generated adapter still carries its own override list, because “PostgreSQL-compatible” is a marketing claim before it is a technical one. The saving from generation is real but modest; the value is that five engines cannot drift apart on the shared 80%.

The conformance suite: one contract, thirty implementations

Thirty adapters stay honest only if one test suite runs against all of them. Not thirty test files — one parametrised suite that treats the contract as the specification and every adapter as an implementation under test.

# tests/test_adapter_conformance.py — pytest 8, run against live engines
# in CI containers where possible and against recorded fixtures otherwise.
import pytest
from datetime import timedelta
from app.connections.adapters import ADAPTERS      # the registry: 30 entries

ALL = pytest.mark.parametrize("adapter", ADAPTERS.values(), ids=lambda a: a.name)


@ALL
def test_quoting_round_trips_reserved_words(adapter, live):
    ident = "select"                    # a reserved word as a column name
    quoted = adapter.quote_identifier(ident)
    rows = live(adapter, f"SELECT 1 AS {quoted}")
    assert list(rows[0].keys())[0].lower() == ident


@ALL
def test_limit_is_applied_after_ordering(adapter, live):
    """Oracle's ROWNUM applies before ORDER BY. If apply_limit does not wrap,
    this returns arbitrary rows and the bug is invisible in every other engine."""
    sql = adapter.apply_limit(
        "SELECT n FROM conformance_numbers ORDER BY n DESC",
        n=3, has_order_by=True,
    )
    assert [r["n"] for r in live(adapter, sql)] == [100, 99, 98]


@ALL
def test_nulls_last_is_enforced_regardless_of_engine_default(adapter, live):
    expr = adapter.order_by_nulls("amount", asc=True, nulls="last")
    rows = live(adapter, f"SELECT amount FROM conformance_nulls ORDER BY {expr}")
    assert rows[-1]["amount"] is None


@ALL
def test_wide_integers_survive_as_strings(adapter, live):
    """9007199254740993 = 2**53 + 1. Any float path corrupts it."""
    if not adapter.supports_wide_integers:
        pytest.skip("engine has no 64-bit integer type")
    rows = live(adapter, adapter.wide_integer_probe())
    assert rows[0]["v"] == "9007199254740993"


@ALL
def test_write_is_refused(adapter, live):
    with pytest.raises(Exception):
        live(adapter, "CREATE TABLE conformance_should_not_exist (x int)")


@ALL
def test_timeout_capability_is_declared_truthfully(adapter, live):
    if not adapter.supports_server_side_timeout:
        pytest.skip("client-side cancellation only — see SQL Server")
    with pytest.raises(TimeoutError):
        live(adapter, adapter.sleep_probe(seconds=5), timeout_s=1)

The last test is the one worth copying. It does not assert that timeouts work — it asserts that the capability flag is true. An adapter that lies about what it can do is worse than one that cannot do it, because the layer above will make safety decisions on the flag. This is the same discipline that produced 17,137 backend test functions across the codebase: the tests encode the contract, so a new engine is added by making the existing suite pass rather than by writing new tests that agree with the new code.

Where this stops applying

This layer is built for read-only analytical queries generated at runtime, executed against engines the operator connects. Four boundaries.

It is not an ORM and does not try to be. An application that owns its schema should use that engine’s features directly; portability is only worth paying for when the engine is chosen by someone else.

It does not attempt write portability. Transaction isolation, upsert syntax, returning clauses and locking semantics diverge far more than the read path does, and a portable write layer across these thirty engines would be several times the size with much worse failure modes.

It does not make a poorly-modelled database queryable. The adapter guarantees the query is valid and safely bounded for the target engine. Whether it is the right query is a schema and documentation problem.

And the specifics above are current for the engine versions in use as of 27 August 2026. DATETRUNC arrived in SQL Server 2022; FETCH FIRST arrived in Oracle 12c; ClickHouse changes function names between releases. Every table in this article is a snapshot of a moving target, which is the real reason a layer like this needs a conformance suite rather than documentation.