Skip to main content

ADR-010: Dual Database Driver

ADR-010: Dual Database Driver Abstraction

Status: SUPERSEDED by ADR-014: PostgreSQL-Only Architecture (2026-03-03) Date: 2026-02-21 Deciders: John (AI Director) Category: Database

Context

Per ADR-006, Drop uses SQLite for development and PostgreSQL for production. This creates a challenge: the application code must work correctly against both database engines, which have different SQL dialects, parameter binding, and transaction semantics.

The naive approach -- maintaining two separate codebases or using an ORM -- has drawbacks:

Approach Type Safety SQL Control Performance Complexity
Raw SQL per driver Low (string SQL) Full Optimal High (2x code)
ORM (Prisma/Drizzle) High Limited Good (with overhead) Medium
Thin abstraction layer Medium Full Optimal Low

An ORM would add a dependency, a build step (Prisma generate), and limit SQL flexibility for complex compliance queries. A thin abstraction layer provides the best balance: same SQL syntax where possible, automatic translation where not.

Decision

Implement a thin database abstraction layer (db.ts) that exposes a unified API and transparently converts SQL between SQLite and PostgreSQL dialects.

Driver detection at startup:

const USE_PG = !!process.env.DATABASE_URL;

graph TB
    subgraph app["Application Code"]
        routes["API Routes"]
        routes -->|"query(), getOne(),<br/>run(), transaction()"| dal["Database Access Layer<br/>(db.ts)"]
    end

    subgraph dal_internals["Abstraction Layer Internals"]
        dal --> detect{"DATABASE_URL<br/>set?"}
        detect -->|"Yes"| pg_driver["PostgreSQL Driver<br/>(pg pool)"]
        detect -->|"No"| sqlite_driver["SQLite Driver<br/>(better-sqlite3)"]

        dal --> convert["SQL Converter"]
        convert -->|"? → $1,$2..."| pg_driver
        convert -->|"datetime('now') →<br/>CURRENT_TIMESTAMP"| pg_driver
    end

    subgraph databases["Databases"]
        pg_driver --> pg["PostgreSQL<br/>(production)"]
        sqlite_driver --> sqlite["SQLite<br/>(development)"]
    end

Unified API

Function Signature Purpose
query<T> (sql, params?) -> Promise<T[]> SELECT, returns array of rows
getOne<T> (sql, params?) -> Promise<T | null> SELECT, returns first row or null
run (sql, params?) -> Promise<{changes}> INSERT/UPDATE/DELETE
runIgnore (sql, params?) -> Promise<{changes}> INSERT OR IGNORE / ON CONFLICT DO NOTHING
runUpsert (sql, conflictCol, updateCols, params?) -> Promise<{changes}> INSERT OR REPLACE / ON CONFLICT DO UPDATE
transaction<T> (fn) -> Promise<T> Atomic transaction wrapper
initDb () -> Promise<void> Schema creation + seed data
getDriver () -> "pg" | "sqlite" Current driver type

SQL Translation Rules (db.ts:50-59)

SQLite Syntax PostgreSQL Equivalent Handled By
? placeholders $1, $2, $3, ... Automatic in query()/run()
INSERT OR IGNORE INTO INSERT INTO ... ON CONFLICT DO NOTHING runIgnore()
INSERT OR REPLACE INTO INSERT INTO ... ON CONFLICT (col) DO UPDATE SET runUpsert()
datetime('now') CURRENT_TIMESTAMP Automatic in SQL string
INTEGER AUTOINCREMENT SERIAL Schema initialization
TEXT dates TIMESTAMPTZ Schema initialization

Consequences

Positive

  • Application code is database-agnostic -- same queries work against both engines
  • Zero-config local development (SQLite), production-grade in deployment (PostgreSQL)
  • No ORM overhead or code generation step
  • Full SQL control for complex compliance queries (joins across audit tables)
  • Transparent parameter binding conversion
  • Transaction semantics unified across both drivers

Negative

  • SQL must be compatible with both dialects (no PostgreSQL-specific features like arrays, JSON operators, CTEs with RETURNING)
  • Subtle behavioral differences may cause bugs (e.g., SQLite type affinity vs PostgreSQL strict typing)
  • runUpsert() API is slightly awkward compared to native SQL
  • Cannot use advanced PostgreSQL features (partial indexes, LISTEN/NOTIFY, materialized views) through the abstraction

Risks

  • Silent data differences: SQLite may accept data that PostgreSQL rejects (e.g., inserting text into INTEGER column). Mitigation: CI tests against both databases.
  • Transaction isolation: SQLite uses serialized transactions (one writer), PostgreSQL uses MVCC. Code that works under SQLite serialization may have race conditions under PostgreSQL MVCC. Mitigation: explicit row locking (FOR UPDATE) in critical paths like balance deduction.

References

No comments to display

Back to top