Database abstraction with SQLite + Postgres parity #8

New issue

Closed

opened 2026-06-12 20:27:12 +00:00 by james · 0 comments

james commented

2026-06-12 20:27:12 +00:00

Owner

Every DB call goes through this layer. The same code must run unchanged against SQLite (self-hoster default) and Postgres (heavier deployments).

Scope

Pick an ORM/query builder with genuine multi-engine support. Drizzle and Kysely both qualify; Prisma's SQLite story is weaker. Justify the choice in the PR description.
Automated migration system (forward + reversible where the engine supports it). Migrations apply on service start.
Connection configured via env (e.g. DATABASE_URL or a DATABASE_ENGINE + per-engine vars). Pick one approach and stick to it.
Entity types (DB row shape) live in db/entities/. DTOs (API payload shape) live in lib/dto/. No leakage either direction.
A test harness that runs DB-touching tests against both engines in CI — Postgres via a service container, SQLite in-process.

Acceptance criteria

A trivial example entity + repository compiles and its tests pass against both engines.
A migration applied to an empty DB on both engines produces the same logical schema.
db/README.md covers: adding a migration, adding an entity, adding a DTO mapping.

Out of scope

Real domain entities — those land in feature tickets.

Part of epic #1. Depends on #7.

Every DB call goes through this layer. The same code must run unchanged against SQLite (self-hoster default) and Postgres (heavier deployments). ## Scope - Pick an ORM/query builder with genuine multi-engine support. Drizzle and Kysely both qualify; Prisma's SQLite story is weaker. **Justify the choice in the PR description.** - Automated migration system (forward + reversible where the engine supports it). Migrations apply on service start. - Connection configured via env (e.g. `DATABASE_URL` or a `DATABASE_ENGINE` + per-engine vars). Pick one approach and stick to it. - Entity types (DB row shape) live in `db/entities/`. DTOs (API payload shape) live in `lib/dto/`. **No leakage either direction.** - A test harness that runs DB-touching tests against both engines in CI — Postgres via a service container, SQLite in-process. ## Acceptance criteria - [ ] A trivial example entity + repository compiles and its tests pass against both engines. - [ ] A migration applied to an empty DB on both engines produces the same logical schema. - [ ] `db/README.md` covers: adding a migration, adding an entity, adding a DTO mapping. ## Out of scope - Real domain entities — those land in feature tickets. Part of epic #1. Depends on #7.

james referenced this issue

2026-06-12 20:27:17 +00:00

Containerize service + unauthenticated /api/health endpoint #9

james referenced this issue

2026-06-12 20:27:26 +00:00

Local user authentication #11

james referenced this issue

2026-06-12 20:27:41 +00:00

Forgejo Actions PR pipeline (build / test / lint / typecheck) #13

james referenced this issue

2026-06-12 20:28:14 +00:00

Profile feature #21