ADR 003 Database and ORM - steilerDev/cornerstone GitHub Wiki

ADR-003: Database & ORM

Status

Accepted

Context

Cornerstone uses SQLite as its database (a hard requirement for simplicity and self-hosted deployment). The decision here is about how to interact with SQLite from TypeScript:

  • Type-safe queries that catch schema mismatches at compile time
  • Schema migration support for incremental schema evolution
  • Lightweight overhead appropriate for a small application
  • Good developer experience with autocomplete and type inference

Alternatives Considered

Raw better-sqlite3

  • Zero abstraction overhead; direct synchronous SQLite access
  • No type safety on queries -- SQL strings with manual type assertions
  • No migration tooling; would need a separate migration runner
  • Best performance but worst developer experience
  • Higher risk of runtime SQL errors

Knex.js (query builder)

  • Mature query builder with migration support
  • Works well with better-sqlite3 via knex-better-sqlite3
  • Limited TypeScript type inference -- query results need manual typing
  • No schema-to-type generation
  • Good for complex raw SQL but less ergonomic for standard CRUD

Drizzle ORM

  • Type-safe schema definitions that generate TypeScript types
  • Lightweight "thin ORM" approach -- stays close to SQL semantics
  • First-class SQLite support via drizzle-orm/better-sqlite3
  • Migration tooling via drizzle-kit (generate, migrate, push)
  • Schema-driven: define tables in TypeScript, get typed queries automatically
  • Supports raw SQL escape hatches when needed
  • Active development with frequent releases

Prisma

  • Full-featured ORM with code generation
  • Heavy runtime (Prisma Engine binary) -- overkill for SQLite
  • Schema defined in .prisma files (not TypeScript)
  • Adds significant container size and startup time
  • Better suited for larger applications with PostgreSQL/MySQL

Decision

Use Drizzle ORM with better-sqlite3 as the underlying SQLite driver.

Drizzle-kit will be used for generating and running migrations. The schema is defined in TypeScript (server/src/db/schema.ts), and migrations are generated into server/src/db/migrations/.

Consequences

Positive

  • Full type safety from schema definition through query results
  • Schema changes are validated at compile time
  • drizzle-kit generate creates SQL migration files automatically from schema diffs
  • Lightweight runtime -- no binary engine or code generation step
  • Synchronous SQLite access via better-sqlite3 (no async overhead for DB calls)
  • SQL-like API means the team can reason about generated queries easily

Negative

  • Drizzle is newer than Knex/Prisma; some edge cases may be less documented
  • drizzle-kit CLI occasionally has rough edges with migration ordering
  • The team needs to learn Drizzle's API (though it maps closely to SQL)
  • better-sqlite3 requires native compilation (handled in Docker build)