DEV Community

Abdullah Kaya
Abdullah Kaya

Posted on

42 database engines, and no `if (type === "postgres")` outside the drivers

LibreDB Studio is an open source SQL IDE that runs in the browser. It talks to 42 external engines today: PostgreSQL, MySQL, Oracle, ClickHouse, MongoDB, Redis, Kafka, Kubernetes and a long tail after that.

Every one of those engines has small facts of its own. How it writes a bind placeholder. How it quotes an identifier. Whether a backslash inside a string literal is data or an escape. Which column types lose precision when they pass through JavaScript.

For a long time those facts lived wherever someone first needed them. This post is about how we moved all of them next to the driver that owns them, and the test that keeps them there.

What it looked like

Here is one function from our shared SQL helpers, as it was in October. It picks the placeholder a generated statement uses for a bound value:

export function positionalPlaceholder(dialect: DatabaseType, position: number): string | null {
  switch (dialect) {
    case "postgres":
    case "couchbase":
      return `$${position}`;
    case "duckdb":
    case "mysql":
    case "sqlite":
    case "druid":
    case "db2":
      return "?";
    case "oracle":
      return `:${position}`;
    case "mssql":
      return `@p${position}`;
    default:
      return null;
  }
}
Enter fullscreen mode Exit fullscreen mode

Nothing is wrong with it on its own. The problem is that there were dozens like it, spread over hooks, UI components and shared helpers. Adding an engine meant finding every switch that should now have a new case. Nothing told you when you missed one. The default branch quietly answered for the engine you forgot.

Nothing is wrong with it on its own. The problem is that there were dozens like it, spread over hooks, UI components and shared helpers. Adding an engine meant finding every switch that should now have a new case. Nothing told you when you missed one. The default branch quietly answered for the engine you forgot.

Counting it first

Before moving anything we wrote the guard. It is a unit test that parses every source file outside the driver layer with the TypeScript compiler API and reports each place that branches on an engine id. It looks for five shapes:

  • a Record, Map or Set keyed by the engine type
  • an array literal holding an engine id
  • a comparison (===, !==, Object.is) against an engine id
  • a case label that is an engine id
  • an engine id in default position, like type ?? "postgres" or a parameter default

It follows imports and type aliases, so moving a constant into another file does not hide it.

The first run found 116 branches in 63 declarations, in hooks, components and shared library code. We did not fix them in that PR. We wrote every one into a DEBT list in the test, by file, declaration name and count. The test fails if a new branch appears anywhere, and it also fails if a listed entry no longer matches, so each later PR has to delete the entries it removed.

That turned a vague cleanup into a list that could only shrink.

Where the facts went

Each kind of fact became a contract with one row per engine, and each row sits in the driver's own folder. The placeholder now lives in a SQL-writing spec. This is the PostgreSQL row:

export const POSTGRES_SQL_WRITING: SqlWritingSpec = {
  // standard_conforming_strings has been on by default since PostgreSQL 9.1,
  // so a backslash in a plain literal is data.
  escapeLiteral: literalStandard,
  unescapeLiteral: unquoteStandard,
  quoteIdentifier: identifierDoubleQuoted,
  // pg takes $n.
  placeholder: placeholderDollar,
  temporalLiteral: null,
  // ...inline edit and export rules
};
Enter fullscreen mode Exit fullscreen mode

The shared code collects the rows into one table:

export const SQL_WRITING: Readonly<Record<DatabaseType, SqlWritingSpec>> = Object.freeze({
  postgres: POSTGRES_SQL_WRITING,
  mysql: MYSQL_SQL_WRITING,
  // ...one row per engine
});
Enter fullscreen mode Exit fullscreen mode

Two things changed with that. First, the table is typed over the full engine union, so a new engine without a row does not compile. There is no default left to answer for it. Second, the comment that explains a fact now sits beside the driver that the fact is about, which is where a reviewer of that driver will read it.

We ended up with five of these contracts: connection strings, execution UI, SQL grammar, migration DDL and SQL writing. The tables that collect them are listed in the guard as registries, with the reason each one is allowed to be keyed by engine.

How long it took

Less than you would think. The guard merged on 19 October, and four refactor PRs emptied the debt list the same evening, the last one about three hours after the guard. The list has stayed empty since. With an exact list, each PR had a clear finish line: move the facts for its entries into the rows, delete those entries, and let the test confirm nothing else moved.

The part that needed thought was the defaults. A default branch is an answer someone chose for engines they were not thinking about. With a row per engine, that answer has to be written down for each engine by name, and a reviewer can see it.

What we would tell someone doing the same

  • Write the scanner before the refactor, and make the debt list exact. "Count must match" is what kept every PR honest.
  • Make the list fail in both directions. A guard that only blocks new branches lets stale entries pile up.
  • Put the row next to the driver, not next to the consumer. The person changing a driver is the one who knows the fact.
  • Type the collecting table over the whole union. That replaces a runtime fallback with a compile error.

The guard is tests/unit/lib/no-engine-branches.test.ts if you want to read it, and the scanner is in the folder beside it.

LibreDB Studio is MIT licensed: https://github.com/libredb/libredb-studio

Top comments (0)