# ADR-010: Dual Database Driver

# ADR-010: Dual Database Driver Abstraction

**Status:** SUPERSEDED by [ADR-014: PostgreSQL-Only Architecture](ADR-014-postgresql-only.md) (2026-03-03)
**Date:** 2026-02-21
**Deciders:** John (AI Director)
**Category:** Database

---

## Context

Per [ADR-006](ADR-006-sqlite-to-postgresql.md), 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;
```

```mermaid
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

- [ADR-006: SQLite to PostgreSQL](ADR-006-sqlite-to-postgresql.md) -- Database strategy decision
- [Database Schema](../../backend/DATABASE-SCHEMA.md) -- Table definitions for both dialects
- [Migration Strategy](../database/migration-strategy.md) -- Data migration plan
- [Database Design](../database/database-design.md) -- Database architecture