Keep SQL Out of Handlers With Small Store Interfaces

Handlers that build SQL strings are hard to test and harder to change. Handlers that call a giant Repository interface with twenty methods are not much better: every test fake has to implement all twenty. Go offers a lighter way. The consumer declares the small interface it needs, any type with those methods satisfies it, and a pair of implementations, one real and one fake, are kept in agreement by a single shared test. This article builds that on Go 1.25.6 with PostgreSQL 18.6, runs it, and shows where the pattern stops paying.

It is the lightweight alternative to the full repository pattern and fits in the database cornerstone.

The interface goes where it is used

Go's guidance is to define an interface in the package that consumes it, not the one that implements it. The reason is how interfaces work: a type satisfies one by having the methods, with no declaration of intent. So the producer need not know the interface exists.

package main

import (
	"context"
	"errors"
	"sync"
)

var ErrNotFound = errors.New("not found")

type Note struct {
	ID    int64
	Title string
}

// NoteStore is defined where it is used and holds only what the handler needs.
type NoteStore interface {
	CreateNote(ctx context.Context, title string) (Note, error)
	NoteByID(ctx context.Context, id int64) (Note, error)
}

// Fake is the in-memory implementation tests use.
type Fake struct {
	mu    sync.Mutex
	notes map[int64]Note
	next  int64
}

func NewFake() *Fake { return &Fake{notes: map[int64]Note{}} }

func (f *Fake) CreateNote(_ context.Context, title string) (Note, error) {
	f.mu.Lock()
	defer f.mu.Unlock()
	f.next++
	n := Note{ID: f.next, Title: title}
	f.notes[n.ID] = n
	return n, nil
}

func (f *Fake) NoteByID(_ context.Context, id int64) (Note, error) {
	f.mu.Lock()
	defer f.mu.Unlock()
	n, ok := f.notes[id]
	if !ok {
		return Note{}, ErrNotFound
	}
	return n, nil
}

NoteStore has two methods, the two the handler calls. It is not "the notes repository". If the handler grows a third need, such as listing, the interface gets a third method, and only then. The same file holds the fake, an in-memory map behind a mutex, which is what tests use. ErrNotFound is declared here too, because it is part of the contract between the store and its callers: the handler must be able to tell "no such note" from "the database is down" without parsing text.

The real implementation

package main

import (
	"context"
	"database/sql"
	"errors"
)

// PG is the production implementation.
type PG struct{ DB *sql.DB }

func (p PG) CreateNote(ctx context.Context, title string) (Note, error) {
	n := Note{Title: title}
	err := p.DB.QueryRowContext(ctx, "insert into notes607 (title) values ($1) returning id", title).Scan(&n.ID)
	return n, err
}

func (p PG) NoteByID(ctx context.Context, id int64) (Note, error) {
	var n Note
	err := p.DB.QueryRowContext(ctx, "select id, title from notes607 where id = $1", id).Scan(&n.ID, &n.Title)
	if errors.Is(err, sql.ErrNoRows) {
		return Note{}, ErrNotFound
	}
	return n, err
}

PG wraps a *sql.DB. It does not mention NoteStore. Its job is to translate: run the SQL, convert sql.ErrNoRows into ErrNotFound, and return plain Go values. Every database detail, including the placeholder syntax and the table name, sits in this one file. A column rename changes pg.go and nothing else. Mapping ErrNoRows here is what keeps database/sql out of the rest of the program; a handler that checked for sql.ErrNoRows itself would be coupled to the driver layer again.

The handler

package main

import (
	"encoding/json"
	"errors"
	"net/http"
	"strconv"
)

// Handler knows about HTTP and NoteStore, and nothing about SQL.
type Handler struct{ Notes NoteStore }

func (h Handler) Routes() http.Handler {
	mux := http.NewServeMux()
	mux.HandleFunc("POST /notes", func(w http.ResponseWriter, r *http.Request) {
		var in struct{ Title string }
		if err := json.NewDecoder(r.Body).Decode(&in); err != nil || in.Title == "" {
			http.Error(w, "title required", http.StatusBadRequest)
			return
		}
		n, err := h.Notes.CreateNote(r.Context(), in.Title)
		if err != nil {
			http.Error(w, "internal error", http.StatusInternalServerError)
			return
		}
		w.WriteHeader(http.StatusCreated)
		json.NewEncoder(w).Encode(n)
	})
	mux.HandleFunc("GET /notes/{id}", func(w http.ResponseWriter, r *http.Request) {
		id, err := strconv.ParseInt(r.PathValue("id"), 10, 64)
		if err != nil {
			http.Error(w, "bad id", http.StatusBadRequest)
			return
		}
		n, err := h.Notes.NoteByID(r.Context(), id)
		switch {
		case errors.Is(err, ErrNotFound):
			http.Error(w, "not found", http.StatusNotFound)
		case err != nil:
			http.Error(w, "internal error", http.StatusInternalServerError)
		default:
			json.NewEncoder(w).Encode(n)
		}
	})
	return mux
}

The handler imports net/http and encoding/json and nothing database related. It decodes a request, calls the store, and maps the outcome: created, found, 404 for ErrNotFound, 500 for anything else without leaking the message. It uses Go 1.22 pattern routing (POST /notes, GET /notes/{id}), so no router library is needed.

Two implementations, one contract

A fake is only useful while it behaves like the real thing. The usual failure is a fake that returns nil where PostgreSQL returns an error, so the handler passes its tests and fails in production. The cure is a contract test: one function that takes a NoteStore and checks the behaviour every implementation must have, run against all of them.

package main

import (
	"context"
	"database/sql"
	"net/http"
	"net/http/httptest"
	"strings"
	"testing"

	_ "github.com/jackc/pgx/v5/stdlib"
)

// contract is run against every implementation, so the fake cannot drift from the real store.
func contract(t *testing.T, s NoteStore) {
	ctx := context.Background()
	made, err := s.CreateNote(ctx, "first")
	if err != nil {
		t.Fatal(err)
	}
	got, err := s.NoteByID(ctx, made.ID)
	if err != nil || got != made {
		t.Fatalf("round trip: got %+v, %v; want %+v", got, err, made)
	}
	if _, err := s.NoteByID(ctx, made.ID+1000); err != ErrNotFound {
		t.Fatalf("missing id: got %v, want ErrNotFound", err)
	}
}

func TestContractFake(t *testing.T) { contract(t, NewFake()) }

func TestContractPostgres(t *testing.T) {
	db, err := sql.Open("pgx", "postgres://postgres:[email protected]:55432/lab?sslmode=disable")
	if err != nil {
		t.Fatal(err)
	}
	defer db.Close()
	for _, q := range []string{"drop table if exists notes607", "create table notes607 (id bigserial primary key, title text not null)"} {
		if _, err := db.Exec(q); err != nil {
			t.Fatal(err)
		}
	}
	contract(t, PG{DB: db})
}

func TestHandlerWithFake(t *testing.T) {
	srv := httptest.NewServer(Handler{Notes: NewFake()}.Routes())
	defer srv.Close()

	resp, err := http.Post(srv.URL+"/notes", "application/json", strings.NewReader(`{"Title":"hello"}`))
	if err != nil {
		t.Fatal(err)
	}
	resp.Body.Close()
	if resp.StatusCode != http.StatusCreated {
		t.Fatalf("create: status %d", resp.StatusCode)
	}
	for path, want := range map[string]int{"/notes/1": 200, "/notes/99": 404, "/notes/abc": 400} {
		r, err := http.Get(srv.URL + path)
		if err != nil {
			t.Fatal(err)
		}
		r.Body.Close()
		if r.StatusCode != want {
			t.Errorf("GET %s: status %d, want %d", path, r.StatusCode, want)
		}
	}
}

=== RUN   TestContractFake
--- PASS: TestContractFake (0.00s)
=== RUN   TestContractPostgres
--- PASS: TestContractPostgres (0.03s)
=== RUN   TestHandlerWithFake
--- PASS: TestHandlerWithFake (0.00s)
PASS
ok  	example.test/lab	1.374s

contract creates a note, reads it back and requires equality, then asks for an id that does not exist and requires ErrNotFound. TestContractFake runs it on the fake and TestContractPostgres runs it on the real store, which drops and recreates its own table first. When both pass, the fake and the real store agree on the behaviours that handlers rely on. TestHandlerWithFake then starts the whole HTTP handler with the fake in httptest and checks four outcomes: 201 on create, 200 for an existing note, 404 for a missing one and 400 for a malformed id.

The output shows all three passing under the race detector. The PostgreSQL contract test took 0.03s and the handler test took no measurable time, because it never leaves memory. A suite with hundreds of handler tests costs almost nothing, and the one test that needs a database runs once per implementation, not once per handler.

Why this design holds up

• Tests stay fast without lying. The fake gives speed; the contract gives confidence. A test that mocks every call with canned values gives neither.
• The interface stays small. Each consumer declares what it uses. A background job that only reads notes declares a one method interface and still gets the same PG type.
• The dependency points inward. Handlers know the interface, main wires the concrete type, and the database package imports nothing from the handlers.
• Errors are part of the contract. ErrNotFound is checked with errors.Is, so wrapping it with context still works.

A second consumer, a smaller interface

The benefit shows when a second piece of code needs the same data. Suppose a nightly job exports note titles. It does not need CreateNote, so it declares its own interface in its own package:

type titleSource interface {
	NoteByID(ctx context.Context, id int64) (Note, error)
}

It accepts a PG in production and a three line fake in its test. The two interfaces overlap on NoteByID, and neither knows about the other. Compare that with a shared NoteRepository interface of eight methods: the job's test double would have to implement all eight, or embed the interface and panic on the six it never calls, which is a different bug waiting to happen. Small interfaces make fakes cheap to write, and cheap fakes get written.

There is one convention worth adopting. Name the interface for the role the consumer gives it, such as NoteStore in the handler or titleSource in the job, not for the thing implementing it. The word "Repository" in a name usually means the interface was written from the implementation's point of view.

Wiring it together

main is the only place that knows the concrete type. It opens the pool once, constructs PG{DB: db} and passes it to Handler{Notes: ...}. Nothing else imports the concrete type, so replacing it, for instance with a caching wrapper that also satisfies NoteStore, is a change to main alone. The decorator is a few lines: a struct holding another NoteStore and a cache, with NoteByID checking the cache first. Because the interface is tiny, the wrapper is tiny too. Test the wrapper with the same contract function and it inherits every behaviour the handler relies on, including the not found case.

Where it stops paying

A fake that must reproduce database behaviour grows quickly. Uniqueness constraints, ordering, pagination, transactions and concurrent updates are all things the real database does and a map does not. When the fake needs those, you are writing a second database. At that point test the logic against a real PostgreSQL, using the techniques in testing against PostgreSQL, and keep the fake for the handler tests that only care about status codes.

Transactions are the other limit. A NoteStore method that opens its own transaction cannot join an outer one. When an operation spans several stores, introduce a unit of work that hands out stores bound to one transaction, as described in unit of work and transaction boundaries.

Finally, do not generate interfaces for every struct by habit. An interface with one implementation and no test that needs a fake is code to maintain for no reader. Add one when a second implementation or a test needs it. The Go Code Review Comments say the same, in its section on interfaces, the httptest package is what makes the handler tests above possible, and the standard library's io.Reader and http.Handler show how small a useful interface can be.