SQLC vs Prisma — two philosophies of type-safe data access

You used the two dominant type-safe data-access approaches on opposite sides: SQLC (SQL-first, compiler-checked) in zoth, Prisma (schema-first, generated client) in traverse. Being able to compare them from your own code is a strong signal — most engineers know one.

SQLC (zoth)

Workflow: write SQL in internal/db/sqlc/queries.sql → sqlc generate reads schema migrations (001–005 in sqlc.yaml) → emits db.go, models.go, querier.go, queries.sql.go as plain Go. The application uses database/sql with the pgx stdlib driver.

What you get:

• The SQL you wrote is the SQL that runs — the generated code is a literal transcription. GetManagerChain is the recursive CTE from Day 38, verbatim, with typed row structs.
• Nullability encoded in types: emit_pointers_for_null_types: true → nullable columns are pointers; the compiler forces you to handle nil.
• Overrides map uuid → google/uuid.UUID — including nullable pointer variants.
• The optional-pagination idiom lives in SQL: LIMIT CASE WHEN $n > 0 THEN $n ELSE 2147483647 END — a trick worth knowing: one query for both paginated and unpaginated calls.

Costs: every new query is a regeneration step; dynamic query building (arbitrary filters) is awkward — zoth's audit filter (action, actor_id, from, to, limit) is served by fixed combinations, and dynamic filter composition would push you toward query builders like squirrel. Migration drift: sqlc.yaml lists only migrations 001–005, so outbox/vector tables (006/007) are invisible to SQLC — those are accessed via raw database/sql in the outbox store. (Know this; it's a real gap worth mentioning unprompted.)

Prisma (traverse)

Workflow: schema.prisma (742 lines, 30+ models) is the source of truth → prisma generate → typed client; prisma migrate dev/deploy creates SQL migrations from schema diffs.

What you get:

• Schema + client + migrations from one file — the 742-line schema is the data model documentation.
• Relation traversal in code: prisma.user.findUnique({ where: { id }, include: { revisions: true } }).
• Runtime error taxonomy: P2002 (unique violation), P2025 (not found), P2003 (FK), all mapped in mapPrismaError to safe HTTP errors — this is real production ergonomics.
• Prisma enforces the migration-hash contract: a byte-edited applied migration fails migrate deploy — traverse's CI even replays migrations via ci-setup-db.sh because db push can't express CHECK constraints and partial unique indexes the social-graph tests need.

Costs: the query layer is opaque (no hand-tuned SQL without $queryRaw); the client is a heavyweight runtime; complex queries (the raw FOR UPDATE SKIP LOCKED claim in the notification drain) drop to $queryRaw/$executeRaw anyway. Also niche footguns you hit personally: serializing a JS Date into a @db.Date column uses its UTC calendar date (the timezone lib normalizes to UTC midnight for exactly this reason), and the ring-goals bug — passing {solveGoal} where columns are ringSolveGoal type-checked cleanly because excess-property checks don't apply to variables, only to fresh literals — failed at runtime, not compile time. Type safety ends where reflection-generated clients begin.

The comparison you can recite

Your positioning line: "I chose SQLC where the SQL was the design — recursive CTEs, conditional updates, SKIP LOCKED claims — and Prisma where the schema evolved weekly with the product. Both decisions were about where the system's complexity lived: in the queries, or in the model."