Lesson 22 · PostgreSQL in Go

The database layer: Ext, entities, ExecInTx

The thin abstraction every repository in your services is built on.

Your win: read our internal/golibs/database layer — the interfaces, the entity pattern, and the transaction closure — recognising the Go-course ideas underneath it.

The interfaces: Ext and QueryExecer

Repos don't take a concrete pool — they take a small interface, so the same method works with the pool or a transaction (your Go course, Lesson 6):

internal/golibs/database/db.go:17-36 
type QueryExecer interface { Query(...); QueryRow(...); Exec(...); SendBatch(...) }
type Ext interface { QueryExecer; Begin(ctx) (pgx.Tx, error) }  // can start a tx

// repos: func (r *EmailRepo) UpsertEmail(ctx, db database.QueryExecer, e *model.Email) error

The entity pattern

A Go struct maps to a table via FieldMap() (columns ↔ field pointers) and TableName(); pgtype fields carry NULL via their status (Lesson 2). database.Select(...).ScanAll(&ents) reads rows using the slice's Add():

type Email struct { EmailID pgtype.Text; Content pgtype.JSONB; ... }
func (*Email) TableName() string { return "emails" }
func (e *Email) FieldMap() ([]string, []interface{}) { ... }
database.Select(ctx, db, query, args...).ScanAll(&emails)

Writes use raw SQL with $N params + helpers like database.GeneratePlaceholders and UpsertExcept (ON CONFLICT, Lesson 4). IDs are ULID text (idutil.ULIDNow()).

Transactions: the ExecInTx closure

To run several writes atomically (Lesson 4), wrap them in a closure — the same defer/closure pattern from your Go course, Lesson 13:

internal/golibs/database/db.go:41 
database.ExecInTx(ctx, db, func(ctx, tx pgx.Tx) error {
    repoA.Insert(ctx, tx, a)   // tx satisfies Ext → threads into each call
    repoB.Insert(ctx, tx, b)
    return nil                // nil → COMMIT;  error → ROLLBACK
})
Anchor — three courses converge here This layer is the Go course made concrete: small consumer-side interfaces (Ext/QueryExecer — Go L6) that make repos mockable; pgtype zero-value/NULL handling (Go L2); the ExecInTx closure + deferred commit/rollback (Go L13). And it's how the Postgres concepts you learned reach real SQL — UpsertExcept is Lesson 4's ON CONFLICT, ExecInTxWithRetry is Lesson 20's 40001.
Read this next

pgx (queries, transactions, batch) + the repo layer

How pgx Query/Exec/Begin/SendBatch work under our wrappers. Ground truth: repo-postgres-map.md.

→ pkg.go.dev — jackc/pgx/v4

Check yourself (from memory)

Q1. A repo method takes db database.Ext so it can accept…

A pgx.Tx satisfies Ext, so the same repo call runs inside or outside a transaction. (Small interface — Go L6.)

Q2. The entity's FieldMap() exists to…

It lines up column names with &field pointers, so ScanAll can read rows into the struct.

Q3. ExecInTx commits when the closure…

Return nil → COMMIT; return an error → ROLLBACK. The whole group is atomic (Lesson 4).
Sketch how our Go code runs a transactional multi-write against Postgres.
recall, then click to reveal
Repo methods take (ctx, db database.Ext|QueryExecer, ...) — small interfaces (Go L6) accepting the pool or a tx. Entities implement FieldMap()/TableName() with pgtype fields (NULL via .Set(nil)/status); Select(...).ScanAll(&ents) reads; writes use $N params + helpers (UpsertExcept = ON CONFLICT). For several writes in one tx: ExecInTx(ctx, db, func(ctx, tx){ repoA.Insert(ctx, tx,…); repoB.Insert(ctx, tx,…); return nil }) — COMMIT on nil else ROLLBACK (Go L13); tx satisfies Ext so it threads into each call.
Want to see how SendBatch (pipelining) or ScanAll works under the hood? Ask me.

1. Repo: repo-postgres-map.md; pgx/v4 docs.