Skip to content
Tutorials Center
My learning

Topics

Lesson 119 of 147 ยท Databases

SQL in Go: Repositories, Migrations and sqlc

Organize SQL code in Go - the repository pattern, query builders versus raw SQL, sqlc and ORMs, schema migrations, testing with real databases, and avoiding N+1 queries and missing indexes.

Calling db.QueryRowContext from everywhere works for a demo. In a real application, a few hundred queries spread across HTTP handlers become hard to test, hard to change and easy to get wrong. This lesson is about the patterns that keep SQL code manageable as a Go program grows: putting queries behind a repository, deciding between raw SQL, query builders, sqlc and ORMs, evolving the schema with migrations, testing against a real database, and avoiding the two performance problems that cause most slow pages, the N+1 query and the missing index.

It assumes you know the basics of database/sql. As there is no database driver in the browser sandbox, every runnable example here works with fakes or pure Go logic, and the code that needs a real database is shown read-only. The ideas are the same either way.

The repository pattern

A repository is a type whose methods express what your application needs from storage, in the language of your domain: CreateUser, UserByEmail, ListOrdersFor. Handlers and services call those methods and never see SQL, rows or connections. This has three payoffs:

  • SQL lives in one place, so a schema change touches one file.
  • Business code can be tested with an in-memory fake instead of a database.
  • Domain-level errors (ErrNotFound, ErrEmailTaken) replace driver-specific ones such as sql.ErrNoRows or "unique constraint violated", so callers do not depend on the database you picked.

In idiomatic Go the interface belongs to the consumer, not the implementation (see Interface Design). The service declares the small interface it needs, and any type with those methods satisfies it, whether it uses PostgreSQL or a map.

One more trick matters in practice. Repository methods must work both inside and outside a transaction. Both *sql.DB and *sql.Tx have the same three query methods, so you can declare a small interface that either satisfies, and give the repository that interface instead of a concrete *sql.DB. That is exactly what sqlc generates, too.

Go A repository with an in-memory fake

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with bufio.Scanner or fmt.Scan), it gets the next line from this box.

The business function signup receives a UserRepository and runs unchanged against the fake. In a test you would also run the same repository test suite against the real sqlUserRepo and the fake, to prove they behave alike (a contract test). Two cautions:

  • Do not make the interface huge. A 40-method Repository interface is hard to fake. Prefer small interfaces per use case, and let one concrete type implement several of them.
  • Do not wrap everything. If a handler only runs one query, a function that takes a DBTX is fine. A repository is a tool for isolating storage, not a rule for every project.

Transactions across repositories

When a use case changes several tables, it needs one transaction that several repositories share. The pattern: a helper begins a transaction, hands the callback a repository built on the *sql.Tx, and commits or rolls back based on the returned error.

Go
type Store struct{ db *sql.DB }

func (s *Store) InTx(ctx context.Context, fn func(users UserRepository, orders OrderRepository) error) error {
	tx, err := s.db.BeginTx(ctx, nil)
	if err != nil {
		return err
	}
	defer tx.Rollback() // no-op after a successful Commit

	if err := fn(sqlUserRepo{tx}, sqlOrderRepo{tx}); err != nil {
		return err
	}
	return tx.Commit()
}

// usage
err := store.InTx(ctx, func(users UserRepository, orders OrderRepository) error {
	u, err := users.Create(ctx, "Ada", "[email protected]")
	if err != nil {
		return err
	}
	return orders.CreateFirstOrder(ctx, u.ID)
})

The callback cannot forget to use the transaction, because it only receives repositories already bound to it.

Raw SQL, builders, sqlc and ORMs

Go developers have more options than in most languages, and each trades control for convenience:

Approach What you write Strengths Weaknesses
database/sql SQL strings and Scan calls No dependencies, full control Repetitive; column and Scan mismatches show up at runtime
sqlx, pgx helpers SQL plus struct scanning (db.GetContext(ctx, &u, ...), pgx.CollectRows) Much less scanning code Still strings; errors at runtime
Query builders (squirrel, goqu) Go calls that assemble SQL Good for dynamic filters A second language to learn; complex SQL gets awkward
sqlc SQL files; generates typed Go Real SQL, compile-time checked, zero reflection A code-generation step; dynamic queries are harder
ORMs (GORM, ent, bun) Go structs and method calls Fast start, relations, migrations helpers Hidden queries (N+1), generated SQL you do not control, a big API

There is no universally right answer. Many teams settle on sqlc for the 90 percent of queries that are static, plus a small hand-written builder for dynamic search filters. ORMs shine in prototypes and CRUD-heavy back offices but need discipline: log every query in development, and know exactly what each call sends.

Query builders for dynamic filters

Dynamic search screens ("name contains X, age at least Y, role in these") are where hand-assembled SQL strings go wrong. The safe pattern has two rules: values always go through placeholders, and identifiers (columns to sort by) are only ever chosen from an allow-list. Builder libraries such as squirrel provide this, but the idea is small enough to write yourself:

Go A tiny query builder with placeholders and an allow-list

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with bufio.Scanner or fmt.Scan), it gets the next line from this box.

Look at the results. The single quote in o'brien does not appear in the SQL at all; it travels in args, where the driver sends it safely. The malicious SortBy never reaches the query text: it is not in the allow-list, so the builder returns an error. The IN list creates one placeholder per element, which is the portable approach (PostgreSQL also allows a single array parameter with = ANY($1)). Offset pagination (OFFSET n) gets slow for deep pages; for large tables prefer keyset pagination: WHERE id > $last_seen_id ORDER BY id LIMIT 50.

sqlc: type-safe code from real SQL

sqlc flips the usual direction. You write plain SQL in .sql files, together with your schema, and the tool generates Go functions and structs, checking the queries against the schema while it generates. A typo in a column name becomes a build error, and no reflection or string mapping happens at runtime. It is a command-line tool, not a library you import.

A minimal setup, shown read-only because it needs the tool and a driver:

YAML
# sqlc.yaml
version: "2"
sql:
  - engine: "postgresql"
    queries: "db/query.sql"
    schema: "db/migrations"        # sqlc reads the migration files as the schema
    gen:
      go:
        package: "db"
        out: "internal/db"
        sql_package: "pgx/v5"
TEXT
-- db/query.sql
-- name: GetUser :one
SELECT id, name, email FROM users WHERE id = $1;

-- name: ListUsers :many
SELECT id, name, email FROM users ORDER BY id LIMIT $1 OFFSET $2;

-- name: CreateUser :one
INSERT INTO users (name, email) VALUES ($1, $2) RETURNING id, name, email;

-- name: DeleteUser :exec
DELETE FROM users WHERE id = $1;

Running sqlc generate produces code like this (abridged):

Go
type User struct {
	ID    int64
	Name  string
	Email string
}

type CreateUserParams struct {
	Name  string
	Email string
}

type DBTX interface { /* Exec, Query, QueryRow with context */ }

func New(db DBTX) *Queries { return &Queries{db: db} }

func (q *Queries) WithTx(tx pgx.Tx) *Queries { return &Queries{db: tx} }

func (q *Queries) GetUser(ctx context.Context, id int64) (User, error)
func (q *Queries) ListUsers(ctx context.Context, arg ListUsersParams) ([]User, error)
func (q *Queries) CreateUser(ctx context.Context, arg CreateUserParams) (User, error)
func (q *Queries) DeleteUser(ctx context.Context, id int64) error

The annotations after -- name: decide the shape: :one returns a single row, :many a slice, :exec only an error. DBTX and WithTx give you the transaction behavior from the previous section for free. Because the output is ordinary Go, you commit the generated files to the repository (or generate them in CI) and review them like any other code. Wrap *Queries in your own repository when you want domain errors and a smaller interface.

ORMs

ORMs map Go structs to tables and offer methods such as db.First(&user, 1) (GORM) or fluent generated builders (ent, bun). They save time on simple CRUD and relations, but they have costs worth knowing before you adopt one:

  • Hidden queries. Loading a list and touching a relation in a loop produces the N+1 problem described below. Turn on SQL logging in development.
  • Zero values and updates. Struct-based updates have to decide whether 0 or "" means "set to zero" or "not given" (GORM, for instance, skips zero fields in Updates(struct)).
  • Leaky abstractions. Reports, window functions and bulk operations eventually need raw SQL anyway.

If you use one, keep it behind the repository interface so the rest of the program does not depend on it.

Schema migrations

The schema evolves with the code: a new column, an index, a renamed table. Migrations are numbered scripts that move the schema forward (and optionally back) in small, reviewable steps. A tool records which migrations already ran in a bookkeeping table, usually schema_migrations, and applies only the new ones, in order, on every environment from a laptop to production.

Common tools are goose, golang-migrate and atlas. Their files look like this (read-only):

TEXT
-- db/migrations/20260312_0001_create_users.sql   (goose format)
-- +goose Up
CREATE TABLE users (
    id    bigserial PRIMARY KEY,
    name  text NOT NULL,
    email text NOT NULL UNIQUE
);

-- +goose Down
DROP TABLE users;
TEXT
migrations/
  000001_create_users.up.sql        golang-migrate: one file per direction
  000001_create_users.down.sql
  000002_add_orders.up.sql
  000002_add_orders.down.sql

You can ship the files inside your binary with the standard embed package (//go:embed migrations/*.sql into an embed.FS), so a deployed program always carries the matching schema.

The tools differ in details but all implement the same small algorithm. Writing it once shows what they do, and what can go wrong. The runner below works against a fake database that records the statements it receives, and it does what real tools do: remember applied versions with a checksum, apply pending ones in order, each in its own transaction, and refuse to continue if an already-applied migration was edited.

Go A migration runner in forty lines

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with bufio.Scanner or fmt.Scan), it gets the next line from this box.

The runner sorts by version regardless of the order in the slice, skips what is recorded as applied, and verifies the checksum. That last check is the most important safety net: never edit a migration that has already run anywhere. Add a new one that makes the change instead. Because the second run sent no statements, running migrations on every deploy is cheap and safe.

Practical rules that apply with any tool:

  • Run migrations as an explicit deploy step (or take a database lock, such as a PostgreSQL advisory lock), not from every app instance at startup, or two instances may race.
  • Make changes backward compatible so old and new versions of the app can run side by side during a rolling deploy. For a rename or a type change, use expand and contract: add the new column, deploy code that writes both, backfill, switch reads, and finally drop the old column in a later migration.
  • Be careful with locks. ALTER TABLE on a large table can block writes. In PostgreSQL, use CREATE INDEX CONCURRENTLY, add NOT NULL constraints in steps, and set a lock_timeout.
  • Keep migrations small and one-directional in practice. Down scripts are rarely safe on a production database; a forward fix is usually the real rollback.

Testing with a real database

Tests for repositories should run against the same kind of database you run in production. The common alternatives each fail in a specific way:

  • Mocking database/sql (for example with go-sqlmock) only checks that your code sends the SQL string you expected. It cannot find an invalid query, a wrong column or a constraint violation.
  • SQLite in memory instead of PostgreSQL hides differences in types, RETURNING, locking and dialect functions. It can be fine if production is also SQLite.
  • A real PostgreSQL or MySQL in a container (started by docker compose or a library such as testcontainers-go) is slower to start but exercises everything. Start it once per package in TestMain, run migrations, and give each test its own isolation.

For isolation, two techniques are common: wrap each test in a transaction that you roll back at the end, or create a fresh database from a template for each test. A skeleton (read-only):

Go
func TestMain(m *testing.M) {
	ctx := context.Background()
	container, dsn := startPostgres(ctx) // e.g. testcontainers-go
	testDB = mustOpen(dsn)
	mustMigrate(testDB)

	code := m.Run()

	testDB.Close()
	container.Terminate(ctx)
	os.Exit(code)
}

func newTx(t *testing.T) *sql.Tx {
	t.Helper()
	tx, err := testDB.BeginTx(context.Background(), nil)
	if err != nil {
		t.Fatal(err)
	}
	t.Cleanup(func() { tx.Rollback() }) // every test leaves the database clean
	return tx
}

func TestCreateUser(t *testing.T) {
	repo := sqlUserRepo{newTx(t)}
	u, err := repo.Create(context.Background(), "Ada", "[email protected]")
	if err != nil {
		t.Fatal(err)
	}
	if _, err := repo.Create(context.Background(), "Ada 2", "[email protected]"); !errors.Is(err, ErrEmailTaken) {
		t.Fatalf("got %v, want ErrEmailTaken (user %d already exists)", err, u.ID)
	}
}

This is where taking a DBTX in the repository pays off: the test passes a *sql.Tx and nothing else changes. The structure of such tests (table-driven cases, helpers, cleanup) is covered in Testing with the testing Package and Table-Driven Tests and Testable Design. Use the in-memory fake for unit tests of services, and the real database for repository tests; the two layers together are fast and trustworthy.

The N+1 query problem

The most common database performance bug is also the easiest to write. You load a list with one query, then loop and run one more query per item, usually hidden inside a helper or an ORM field access. For 100 authors that is 101 round trips; each costs network latency plus parse and plan time, so a page that is fast with 5 rows turns slow with 500.

There are three standard fixes. Which fits depends on the shape of the result:

  1. Batch lookup. Fetch the parent rows, collect their IDs and run a single second query with WHERE author_id IN (...) (or = ANY($1)), then group the results in Go. Two queries total, no matter how many parents.
  2. A join. Fetch everything in one query and fold the flat rows into nested structures. One query, but rows repeat for each child and pagination gets trickier.
  3. Aggregate in SQL (COUNT, array_agg, json_agg) when you only need a summary.

The program below counts queries against a fake database, to make the difference visible without a real server:

Go Counting queries: N+1 versus batch versus join

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with bufio.Scanner or fmt.Scan), it gets the next line from this box.

With 100 authors, the naive loop needs 101 queries, the batch needs two and the join needs one, and the gap grows with the data. Detect N+1 problems by logging queries per request in development (a wrapper around DBTX that counts calls is easy) and by putting a query-count assertion into integration tests for important endpoints.

Indexes and EXPLAIN

The other classic problem is a query that scans a whole table. An index is a sorted structure that lets the database find rows without reading everything, at the price of slower writes and extra disk space. Index the columns you filter, join and order by, in the order your queries use them: a composite index on (user_id, created_at) serves WHERE user_id = $1 ORDER BY created_at DESC. Foreign key columns are not indexed automatically in PostgreSQL.

To find out what the database actually does, ask it with EXPLAIN (or EXPLAIN ANALYZE, which runs the query). The output below is illustrative for a PostgreSQL table with a million orders, not something you can run here:

TEXT
EXPLAIN SELECT * FROM orders WHERE user_id = 42;

 Seq Scan on orders  (cost=0.00..21850.00 rows=52 width=44)
   Filter: (user_id = 42)

CREATE INDEX orders_user_id_idx ON orders (user_id);

 Index Scan using orders_user_id_idx on orders  (cost=0.42..8.44 rows=52 width=44)
   Index Cond: (user_id = 42)

Seq Scan means every row is read; Index Scan goes straight to the matching ones. Costs are in arbitrary planner units, so compare plans rather than reading the numbers as milliseconds. Also remember:

  • A function on the column (WHERE lower(email) = $1) prevents a plain index from being used; create an expression index instead.
  • Leading wildcards (LIKE '%abc') cannot use a normal B-tree index.
  • Fetch only the columns you need, and always LIMIT list queries.
  • Measure in production: slow-query logs (log_min_duration_statement in PostgreSQL) and pg_stat_statements show the queries that really cost time.

Putting it together

A layout that works well for medium-sized services:

TEXT
internal/
  db/           sqlc output (or hand-written queries), migrations embedded with //go:embed
  store/        repositories: wrap generated code, translate errors, expose small interfaces
  service/      business logic; depends on store interfaces
  httpapi/      handlers; depend on services (see the REST API lesson)

Keep SQL in one layer, translate errors at its boundary, pass context.Context everywhere, share one *sql.DB, and let migrations be the only thing that changes the schema. Caching, the subject of the next lesson, is the natural step once the queries themselves are sound.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Why should the repository take an interface such as DBTX (with QueryRowContext, ExecContext, QueryContext) instead of a *sql.DB?

    Both *sql.DB and *sql.Tx implement the three methods. Depending on the small interface lets the same code run outside or inside a transaction, which is also what sqlc generates.
  2. 2. A user supplies sort=name; DROP TABLE users. What is the right way to build the ORDER BY clause?

    Placeholders carry values, not identifiers, so ORDER BY $1 sorts by a constant. Choosing the column from a fixed map in Go is the safe approach.
  3. 3. What is the main advantage of sqlc over hand-written Scan code?

    sqlc reads your SQL and schema and generates plain Go functions and structs. It catches wrong columns or types at generation time and uses no reflection at runtime.
  4. 4. Why is editing an already applied migration file a bad idea?

    Applied versions are recorded and skipped. An edit never reaches those databases, so schemas drift apart. Add a new migration instead; tools use checksums to detect edits.
  5. 5. A page lists 200 articles and then runs one query per article to fetch its tags. How many queries does it run, and what is a fix?

    One query for the list plus one per row is the N+1 problem. A batch query (or a join) reduces it to two queries or one, regardless of the number of articles.

Summary

  • Put SQL behind repositories: small interfaces defined by the consumer, domain errors instead of driver errors, and an in-memory fake for unit tests. Depend on a DBTX interface so code works with *sql.DB and *sql.Tx.
  • Share one transaction across repositories with an InTx(ctx, fn) helper that hands the callback tx-bound repositories and uses defer tx.Rollback().
  • Values always go through placeholders; identifiers come from allow-lists. Builders suit dynamic filters, sqlc suits static queries with compile-time checking, and ORMs trade control for speed of development, so log their SQL.
  • Migrations are ordered, immutable scripts tracked in a table with checksums. Never edit an applied one, run them as a deploy step, and use expand-and-contract for breaking changes.
  • Test repositories against the same database engine as production (containers, TestMain, a rolled-back transaction per test); mocks of database/sql prove little.
  • Fix N+1 queries with batch lookups or joins, count queries in tests, add indexes for filter and join columns, and read EXPLAIN output instead of guessing.

Next: Caching and NoSQL from Go