Lesson 11 · sqlc — typed SQL + the synthesis

sqlc in the hexagon

The payoff: one real function where hexagonal, CQRS, and sqlc all click together — a read that skips the port and writes that don't, both ending at the same generated SQL.

Your win: trace one usecase end to end and name every pattern as it appears — ports, the read/write asymmetry, the querier, cross-module writes — in a single flow.

The usecase holds both a querier and repo ports

Look at what eureka's assessment usecase depends on. It's the whole course in one struct:

internal/eureka/v2/modules/assessment/usecase/abstraction.go:89

type AssessmentUsecaseImpl struct {
    DB database.Ext
    SubmissionRemoverRepo         repository.SubmissionRemoverRepo                // write PORT (this module)
    LearningTimeWriterRepo        learning_time_repository.LearningTimeWriterRepo // write PORT (another module!)
    LearningSubmissionRemoverRepo book_repository.LearningSubmissionRemoverRepo   // write PORT (another module!)
    ...
    AssessmentQuerier assessment_db.Querier                                       // sqlc querier — for direct READS
}

Two kinds of dependency, side by side: repo ports for writes (Lesson 2), and the sqlc Querier for reads (Lesson 10). That single struct encodes the read/write asymmetry from Lesson 7 as types.¹

The flow: read direct, write through the port

internal/eureka/v2/modules/assessment/usecase/reset_student_study_plan.go:24

func (uc *AssessmentUsecaseImpl) ResetStudentStudyPlan(ctx, courseID string, studentIDs []string) error {
    // ── READ: straight from the querier, no port ──
    rows, _ := uc.AssessmentQuerier.ListRichSubmissionsByCourseAndStudents(ctx, uc.DB, params)   // :25

    // ── WRITE: every mutation through a repo PORT, all in one transaction ──
    database.ExecInTxV2(ctx, uc.DB, func(ctx, tx pgx.Tx) error {
        uc.SubmissionRemoverRepo.BulkRemoveSubmissions(ctx, tx, submissionIDs)               // :61
        uc.LearningTimeWriterRepo.BulkRemoveLearningTimesBySessionIDs(ctx, tx, sessionIDs)   // :73  (cross-module)
        uc.LearningSubmissionRemoverRepo.BulkRemove…(ctx, tx, ...)                            // :77  (cross-module)
        return nil
    })
}

The read calls the querier directly. The writes each go through a repository port, and the same tx threads through all of them (Lesson 6) so the whole reset is atomic. Notice two of the write ports belong to other modules — learning_time and book — injected as interfaces. eureka's rule: to write another module's table, inject its repo port; never reach for its querier.²

Follow one write all the way down

What happens behind SubmissionRemoverRepo.BulkRemoveSubmissions? The adapter that implements that port holds its own querier and delegates:

internal/eureka/v2/modules/assessment/repository/postgres/submission_postgres.go:23

type SubmissionRepo struct { querier assessment_db.Querier }   // the adapter
func NewSubmissionRepo() *SubmissionRepo { return &SubmissionRepo{querier: assessment_db.New()} }
// BulkRemoveSubmissions → s.querier.BulkRemoveSubmissions(ctx, ext, ...Params{...})   (~:1198)

the two paths, one destination ───────────────────────────────────────────────────────────────────── READ usecase ─▶ AssessmentQuerier ───────────────▶ generated SQL ─▶ DB (no port — reads guard nothing) WRITE usecase ─▶ SubmissionRemoverRepo ─▶ SubmissionRepo ─▶ querier ─▶ generated SQL ─▶ DB (PORT / interface) (adapter, in a tx) ───────────────────────────────────────────────────────────────────── both end at the same sqlc querier — but WRITE routes through the guard port first

This is the whole course in one picture Hexagonal — the usecase depends on ports, the postgres SubmissionRepo is the adapter. CQRS / read-write split — the read hits the querier directly; every write goes through a port, in a transaction. sqlc — both paths ultimately call generated, type-safe query methods. The read shortcut is safe precisely because writes are funnelled through the port that carries invariants, events, and observability (Lesson 7). Three patterns, one function.

Map it back to your services Strip out sqlc and this is exactly your code: a spike command handler calls a repo port, the adapter runs SQL, all in an ExecInTx. The only difference is that eureka's adapter delegates to a generated querier and its usecase may read straight from one. Same hexagon, same command/query split — sqlc just changes what sits inside the adapter and adds one sanctioned read shortcut.

DDD, CQRS & Clean Architecture, combined

Three Dots Labs assembling exactly this — ports, command/query handlers, and a real repository — into one coherent flow. Read it with ResetStudentStudyPlan in mind.

→ threedots.tech — combined
→ in-repo .../assessment/usecase/reset_student_study_plan.go, .../assessment/usecase/abstraction.go:89-140, .../assessment/repository/postgres/submission_postgres.go:23-29

Check yourself (from memory)

Q1. The assessment usecase holds two kinds of DB dependency. Which?

Repo ports for every write, the sqlc Querier for direct reads — the read/write asymmetry encoded as struct fields.

Q2. To write another module's table, the usecase uses…

Cross-module writes inject the other module's repo port (learning_time, book) — never its querier. Writes always pass a port.

Q3. Where do the read path and the write path end up?

Both hit the same sqlc querier and generated SQL — but the write routes through the repo port (the guard layer) on the way, the read doesn't.

Recall: assessment end-to-end — all three patterns in one flow.

the struct + the two paths + why, then reveal

The struct (AssessmentUsecaseImpl): holds write repo ports (incl. cross-module learning_time/book) AND the sqlc AssessmentQuerier for reads — the asymmetry as types. The flow (ResetStudentStudyPlan): READ via uc.AssessmentQuerier.List… directly (:25); WRITE via repo ports in ExecInTxV2 (:61-77), same tx → atomic. Down a write: usecase → port (interface) → adapter SubmissionRepo (holds its own querier, built by NewSubmissionRepo()) → querier → generated SQL → DB. Both paths end at the same querier; only the write passes the guard port. = the whole course: hexagonal (port/adapter) + CQRS (read/write split) + sqlc (typed queries), one function.

🎯 Interview one-liner "Walk me through a real request in your architecture." → "A usecase reads what it needs straight from the typed query interface, then does all its writes through repository ports inside one transaction — including writes to other modules' tables via their ports. Each port's adapter delegates to generated SQL. So reads are direct and cheap, writes are funnelled through the layer that guards invariants and emits events — hexagonal, CQRS, and sqlc in one flow."

Last lesson: the whole picture — the v1→v2 migration, the module-quality ladder, when sqlc is worth it, and the course recap with interview prompts. Ask me to re-walk this flow if any hop is fuzzy — it's the one to be able to draw from memory.

1. In-repo (verified): .../assessment/usecase/abstraction.go:89,117-121,140 (ports + AssessmentQuerier).

2. In-repo (verified): .../assessment/usecase/reset_student_study_plan.go:25 (read), :60-77 (cross-module writes via ports); .../assessment/repository/postgres/submission_postgres.go:23-29; internal/eureka/CLAUDE.md (cross-module write rule).