Part 6: Moving to PostgreSQL

A real database server, a connection pool you can reason about, versioned migrations, and tests that talk to the actual database. And the payoff for the layering we've been building: the handlers don't change.

Series: Build a production-grade REST API in Go · Part 6 of 17 Where we are: the two-resource SQLite API from Part 5.

Why leave SQLite?

SQLite is wonderful, and for many apps it's enough. But it is an embedded file: one process, one machine. The moment you want:

two copies of your API behind a load balancer (high availability, deploys without downtime),
replication, point-in-time backups, access control managed by the database,
richer types, concurrency (MVCC) and constraints,

you need a database server. We pick PostgreSQL: open source, battle-tested, and the default choice for most backends.

Start a Postgres

You need one running. Docker is the quickest way (in Part 8 we'll fold this into docker compose). The makefile at the end of this post wraps the same command as make db:

docker run -d --name pg -p 5432:5432 \
  -e POSTGRES_USER=app -e POSTGRES_PASSWORD=change-me -e POSTGRES_DB=app \
  postgres:17-alpine

The connection string we'll use throughout:

postgres://app:change-me@localhost:5432/app?sslmode=disable

(sslmode=disable is only acceptable on localhost. In Part 14 we turn on verified TLS.)

The big idea: only the storage layer changes

Look at what this migration touches:

 cmd/rest/main.go                                   |  24 ++-
 internal/config/config.go                          |  11 +-
 internal/handlers/handlers_test.go                 |   8 +-
 internal/storage/migrations/00001_create_users.sql |  11 +
 .../storage/migrations/00002_create_orders.sql     |  17 ++
 internal/storage/orders.go                         |  66 +++---
 internal/storage/storage.go                        | 238 ++++++---------------
 internal/storage/storage_test.go                   |  79 ++++---
 internal/storage/users.go                          | 107 +++++++++
 internal/testdb/testdb.go                          |  59 +++++
 10 files changed, 361 insertions(+), 259 deletions(-)

Every file under internal/handlers/ is unchanged except the test setup. The HTTP layer never knew it was talking to SQLite. That is what the layering from Parts 3 and 5 bought us: handlers depend on small interfaces (UserStore, OrderStore); we write new implementations of them and re-wire main. Remember this the next time someone says "interfaces are over-engineering".

The driver: pgx

go get github.com/jackc/pgx/v5

We use pgx directly (its pgxpool package) instead of going through database/sql. database/sql is a lowest-common-denominator interface; pgx speaks the PostgreSQL protocol natively, which means better performance, richer types, and, importantly, proper cancellation: when a request's context is cancelled, pgx cancels the query on the server.

Step 1: the connection pool

internal/storage/storage.go (excerpt)

// PoolConfig holds connection-pool tuning. MaxConns caps connections per instance, so replicas x MaxConns must stay below the server's limit.
type PoolConfig struct {
	URL             string
	MaxConns        int32
	MinConns        int32
	MaxConnLifetime time.Duration
	MaxConnIdleTime time.Duration
}

// NewPool creates a pgx connection pool. Unlike database/sql, pgxpool speaks
// Postgres natively (faster, richer types, proper context cancellation).
// Extra options let callers tweak the parsed config (tests use it to pin a
// schema).
func NewPool(ctx context.Context, c PoolConfig, opts ...func(*pgxpool.Config)) (*pgxpool.Pool, error) {
	cfg, err := pgxpool.ParseConfig(c.URL)
	if err != nil {
		// Do not wrap the URL into the message: it contains the password.
		return nil, errors.New("parse database url: invalid format")
	}
	cfg.MaxConns = c.MaxConns
	cfg.MinConns = c.MinConns
	cfg.MaxConnLifetime = c.MaxConnLifetime // recycle conns so DNS/failover changes are picked up
	cfg.MaxConnIdleTime = c.MaxConnIdleTime
	cfg.HealthCheckPeriod = 30 * time.Second

	for _, o := range opts {
		o(cfg)
	}

	pool, err := pgxpool.NewWithConfig(ctx, cfg)
	if err != nil {
		return nil, fmt.Errorf("create pool: %w", err)
	}
	// NewWithConfig connects lazily; Ping forces a real connection so a wrong
	// host/password fails at startup, not on the first request.
	if err := pool.Ping(ctx); err != nil {
		pool.Close()
		return nil, fmt.Errorf("ping database: %w", err)
	}
	return pool, nil
}

Every line here is a decision:

The error message never includes the URL, because the URL contains the password. Logs are read by many people and shipped to many systems; a leaked credential in a log is one of the most common real-world leaks.
MaxConns: the hard cap per application instance. This is the most important tuning knob, so let's be precise. Every PostgreSQL connection is a separate server process using memory (roughly 5-10 MB) and by default the server allows only about 100 in total. If each of 3 API replicas opens 50 connections, you're already over. Rule of thumb: replicas × MaxConns must stay comfortably below the server limit. More connections is not faster: the database only has so many CPU cores.
MinConns: connections kept warm so a quiet period doesn't make the next request pay for connection setup.
MaxConnLifetime: recycle connections periodically so that DNS changes and database failovers are picked up.
Ping at startup: NewWithConfig connects lazily. Pinging forces a real connection, so a wrong password or hostname stops the server in the first second, not on the first request.
Functional options (opts ...func(*pgxpool.Config)): lets callers tweak the config. Our tests use it to pin a schema (more below).

Step 2: migrations

With SQLite we ran CREATE TABLE IF NOT EXISTS on every start. That doesn't scale: how do you change a table that already holds data? You need versioned migrations: an ordered list of SQL files, each applied exactly once, with the database remembering which ones ran.

We use goose, with the SQL files embedded into the binary:

Create internal/storage/migrations/00001_create_users.sql

-- +goose Up
-- GENERATED ALWAYS AS IDENTITY is the modern (SQL-standard) replacement for
-- SERIAL: the database owns the id and rejects attempts to insert one by hand.
CREATE TABLE users (
    id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name       TEXT        NOT NULL CHECK (length(btrim(name)) > 0),
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- +goose Down
DROP TABLE users;

Create internal/storage/migrations/00002_create_orders.sql

-- +goose Up
-- ON DELETE CASCADE: deleting a user removes their orders. Use RESTRICT
-- instead if you would rather refuse to delete users who still have orders.
CREATE TABLE orders (
    id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id    BIGINT      NOT NULL REFERENCES users (id) ON DELETE CASCADE,
    item       TEXT        NOT NULL CHECK (length(btrim(item)) > 0),
    quantity   INTEGER     NOT NULL CHECK (quantity > 0),
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Postgres does NOT auto-index foreign-key columns (unlike primary keys).
-- Without this, "orders for user X" and cascade deletes scan the whole table.
CREATE INDEX idx_orders_user_id ON orders (user_id);

-- +goose Down
DROP TABLE orders;

internal/storage/storage.go (excerpt)

// The SQL files are compiled INTO the binary, so the container needs no extra
// files and the binary always ships with the schema it was built against.
//
//go:embed migrations/*.sql
var migrationsFS embed.FS

// Migrate applies all pending migrations. Safe to run concurrently from many
// replicas: goose takes a Postgres advisory lock so only one runs at a time.
func Migrate(ctx context.Context, pool *pgxpool.Pool) error {
	sub, err := fs.Sub(migrationsFS, "migrations")
	if err != nil {
		return err
	}
	locker, err := lock.NewPostgresSessionLocker()
	if err != nil {
		return err
	}
	// goose wants database/sql; stdlib adapts our pool. Closing this *sql.DB
	// does not close the pool.
	db := stdlib.OpenDBFromPool(pool)
	defer db.Close()

	p, err := goose.NewProvider(goose.DialectPostgres, db, sub, goose.WithSessionLocker(locker))
	if err != nil {
		return fmt.Errorf("create migration provider: %w", err)
	}
	if _, err := p.Up(ctx); err != nil {
		return fmt.Errorf("apply migrations: %w", err)
	}
	return nil
}

Details worth knowing:

-- +goose Up / -- +goose Down mark the apply and revert halves.
//go:embed compiles the SQL files into the binary, so the program always ships with exactly the schema it was built for, and the container needs no extra files.
goose records applied versions in a goose_db_version table. Run Migrate twice and the second run does nothing.
It takes a PostgreSQL advisory lock (WithSessionLocker), so if several copies of the app start at once, only one applies migrations.

Golden rules for migrations on a live system (we'll expand them in Part 8): never edit a migration that has run anywhere (add a new one); and make each change compatible with the previous version of the app, because during a rolling deploy old and new code run side by side.

Step 3: a better schema

Compare to the SQLite version; PostgreSQL gives us better tools:

In the SQL	Why
`BIGINT GENERATED ALWAYS AS IDENTITY`	the modern replacement for `SERIAL`: the database owns the id and rejects manually supplied ones
`TIMESTAMPTZ`	timestamps with time zone, stored as UTC. Never store local times
`CHECK (length(btrim(name)) > 0)`	the database refuses blank names
`REFERENCES users (id) ON DELETE CASCADE`	foreign key + cascade, enforced always (no pragma needed)
`CREATE INDEX idx_orders_user_id`	PostgreSQL does not auto-index foreign keys; without this, cascade deletes and per-user queries scan the whole table

Step 4: the stores, rewritten

The user store, in PostgreSQL:

internal/storage/users.go (excerpt)

const userCols = "id, name, created_at"

func scanUser(row pgx.Row) (*User, error) {
	u := &User{}
	if err := row.Scan(&u.ID, &u.Name, &u.CreatedAt); err != nil {
		return nil, err
	}
	return u, nil
}

// Create uses INSERT ... RETURNING: one round trip returns the row exactly as
// stored, including database-generated id and created_at.
func (s *UserStore) Create(ctx context.Context, name string) (*User, error) {
	u, err := scanUser(s.pool.QueryRow(ctx,
		"INSERT INTO users (name) VALUES ($1) RETURNING "+userCols, name))
	if err != nil {
		return nil, fmt.Errorf("insert user: %w", err)
	}
	return u, nil
}

func (s *UserStore) Update(ctx context.Context, id int, name string) (*User, error) {
	u, err := scanUser(s.pool.QueryRow(ctx,
		"UPDATE users SET name = $1 WHERE id = $2 RETURNING "+userCols, name, id))
	if errors.Is(err, pgx.ErrNoRows) {
		return nil, ErrNotFound
	}
	if err != nil {
		return nil, fmt.Errorf("update user %d: %w", id, err)
	}
	return u, nil
}

func (s *UserStore) Delete(ctx context.Context, id int) error {
	tag, err := s.pool.Exec(ctx, "DELETE FROM users WHERE id = $1", id)
	if err != nil {
		return fmt.Errorf("delete user %d: %w", id, err)
	}
	if tag.RowsAffected() == 0 {
		return ErrNotFound
	}
	return nil
}

Differences from the SQLite version, all small:

Placeholders are numbered: $1, $2 instead of ?. Same protection against SQL injection.
INSERT ... RETURNING returns the row exactly as stored, including database-generated id and created_at, in one round trip. No LastInsertId.
pgx.ErrNoRows replaces sql.ErrNoRows, and we still translate it into our own ErrNotFound. The handler never sees pgx types.
Scanning is shared by a tiny scanUser(row pgx.Row) helper, which works for both single rows and rows from a list.

Orders get the same treatment, including the atomic INSERT ... SELECT ... WHERE EXISTS from Part 5 (type-cast with $1::bigint so Postgres knows each parameter's type).

Step 5: wire it in `main`

Change cmd/rest/main.go

--- a/cmd/rest/main.go
+++ b/cmd/rest/main.go
@@ -8,6 +8,7 @@ import (
 	"os"
 	"os/signal"
 	"syscall"
+	"time"
 
 	"go-rest/internal/config"
 	"go-rest/internal/handlers"
@@ -39,18 +40,31 @@ func run() error {
 	ctx, stop := signal.NotifyContext(context.Background(), os.Interrupt, syscall.SIGTERM)
 	defer stop()
 
-	db, err := storage.InitDB(ctx, cfg.DBPath)
+	pool, err := storage.NewPool(ctx, storage.PoolConfig{
+		URL:             cfg.DatabaseURL,
+		MaxConns:        cfg.DBMaxConns,
+		MinConns:        2,
+		MaxConnLifetime: 30 * time.Minute,
+		MaxConnIdleTime: 5 * time.Minute,
+	})
 	if err != nil {
 		return err
 	}
-	defer db.Close()
+	defer pool.Close()
+
+	// Apply any pending schema migrations before accepting traffic. (Part 8
+	// moves this into its own one-shot job, which is better once you run
+	// more than one copy of the server.)
+	if err := storage.Migrate(ctx, pool); err != nil {
+		return err
+	}
 
 	limits := handlers.Limits{
 		MaxBodyBytes: cfg.MaxBodyBytes,
 		MaxNameLen:   cfg.MaxNameLen,
 	}
-	users := handlers.NewUserHandler(storage.NewUserStore(db), log, limits)
-	orders := handlers.NewOrderHandler(storage.NewOrderStore(db), log, limits)
+	users := handlers.NewUserHandler(storage.NewUserStore(pool), log, limits)
+	orders := handlers.NewOrderHandler(storage.NewOrderStore(pool), log, limits)
 	mux := http.NewServeMux()
 	routes.SetupRoutes(mux, users, orders)
 
@@ -73,7 +87,7 @@ func run() error {
 	// server failure or a shutdown signal.
 	serverErr := make(chan error, 1)
 	go func() {
-		log.Info("server starting", "addr", cfg.Addr, "db", cfg.DBPath)
+		log.Info("server starting", "addr", cfg.Addr)
 		serverErr <- srv.ListenAndServe()
 	}()

Migrate runs at startup here, which is simple and fine for one instance. In Part 8 we'll move it into its own one-shot job, which is the right answer once you run more than one copy.

Step 6: tests against the real thing

When we used SQLite, a test could use a temp file. For PostgreSQL we want the real server, because mocks can't catch the bugs that matter: wrong SQL, constraint behavior, cascade rules, locking. But tests must still be isolated and fast. The trick: one schema per test.

Create internal/testdb/testdb.go

// Package testdb gives tests an isolated, migrated Postgres schema.
//
// Tests need a real Postgres (mocks would not catch SQL mistakes). Set
// TEST_DATABASE_URL to run them; without it the tests are skipped, so a plain
// `go test ./...` still works on a laptop with no database. `make
// test-integration` starts one with Docker for you.
package testdb

import (
	"context"
	"fmt"
	"math/rand/v2"
	"os"
	"testing"
	"time"

	"github.com/jackc/pgx/v5/pgxpool"

	"go-rest/internal/storage"
)

// New returns a pool whose connections all use a brand-new schema, dropped
// when the test ends. A schema per test gives full isolation, even when tests
// run in parallel against the same database.
func New(t *testing.T) *pgxpool.Pool {
	t.Helper()
	url := os.Getenv("TEST_DATABASE_URL")
	if url == "" {
		t.Skip("TEST_DATABASE_URL not set; skipping database test")
	}
	ctx := context.Background()
	schema := fmt.Sprintf("test_%d_%d", time.Now().UnixNano(), rand.IntN(1_000_000))

	admin, err := storage.NewPool(ctx, storage.PoolConfig{URL: url, MaxConns: 1})
	if err != nil {
		t.Fatal(err)
	}
	if _, err := admin.Exec(ctx, "CREATE SCHEMA "+schema); err != nil {
		t.Fatal(err)
	}
	t.Cleanup(func() {
		_, _ = admin.Exec(context.Background(), "DROP SCHEMA "+schema+" CASCADE")
		admin.Close()
	})

	// search_path makes unqualified table names resolve inside our schema.
	pool, err := storage.NewPool(ctx,
		storage.PoolConfig{URL: url, MaxConns: 4},
		func(c *pgxpool.Config) { c.ConnConfig.RuntimeParams["search_path"] = schema })
	if err != nil {
		t.Fatal(err)
	}
	t.Cleanup(pool.Close)

	if err := storage.Migrate(ctx, pool); err != nil {
		t.Fatal(err)
	}
	return pool
}

CREATE SCHEMA test_123456... gives the test a private namespace; search_path makes unqualified table names resolve inside it; the real migrations run there; DROP SCHEMA ... CASCADE cleans up. Tests can even run in parallel against one server.

If TEST_DATABASE_URL isn't set, the tests skip rather than fail, so a plain go test ./... still works on a laptop with no database.

export TEST_DATABASE_URL='postgres://app:change-me@localhost:5432/app?sslmode=disable'
go test -race ./...
# ok  go-rest/internal/handlers  2.173s
# ok  go-rest/internal/storage   6.891s

Complete code for this part

Everything to type, file by file (the migrations, main.go and the test helper are given above). Then go mod tidy fetches the new dependencies (pgx and goose) for you.

Already given earlier in this post: internal/storage/migrations/00001_create_users.sql, internal/storage/migrations/00002_create_orders.sql, internal/testdb/testdb.go, cmd/rest/main.go.

Change internal/config/config.go

--- a/internal/config/config.go
+++ b/internal/config/config.go
@@ -11,8 +11,9 @@ import (
 )
 
 type Config struct {
-	Addr   string
-	DBPath string
+	Addr        string
+	DatabaseURL string // postgres://user:pass@host:5432/db?sslmode=...
+	DBMaxConns  int32
 
 	// http.Server timeouts: enforced by net/http, below the handler layer.
 	ReadHeaderTimeout time.Duration
@@ -33,7 +34,8 @@ type Config struct {
 func Load() (Config, error) {
 	c := Config{
 		Addr:              env("ADDR", ":7800"),
-		DBPath:            env("DB_PATH", "users.db"),
+		DatabaseURL:       os.Getenv("DATABASE_URL"),
+		DBMaxConns:        10,
 		ReadHeaderTimeout: 5 * time.Second,
 		ReadTimeout:       10 * time.Second,
 		WriteTimeout:      15 * time.Second,
@@ -74,6 +76,9 @@ func Load() (Config, error) {
 	}
 
 	// Fail fast on nonsense values instead of misbehaving at runtime.
+	if c.DatabaseURL == "" {
+		return c, fmt.Errorf("DATABASE_URL is required")
+	}
 	if c.MaxBodyBytes <= 0 || c.MaxNameLen <= 0 || c.RequestTimeout <= 0 {
 		return c, fmt.Errorf("limits and request timeout must be positive")
 	}

Replace the contents of internal/storage/orders.go with

package storage

import (
	"context"
	"errors"
	"fmt"
	"time"

	"github.com/jackc/pgx/v5"
	"github.com/jackc/pgx/v5/pgxpool"
)

// ErrUserNotFound is distinct from ErrNotFound: it means "the order's owner
// doesn't exist", which handlers report differently from "order not found".
var ErrUserNotFound = errors.New("referenced user not found")

type Order struct {
	ID        int       `json:"id"`
	UserID    int       `json:"user_id"`
	Item      string    `json:"item"`
	Quantity  int       `json:"quantity"`
	CreatedAt time.Time `json:"created_at"`
}

type OrderStore struct {
	pool *pgxpool.Pool
}

func NewOrderStore(pool *pgxpool.Pool) *OrderStore {
	return &OrderStore{pool: pool}
}

const orderCols = "id, user_id, item, quantity, created_at"

func scanOrder(row pgx.Row) (*Order, error) {
	o := &Order{}
	if err := row.Scan(&o.ID, &o.UserID, &o.Item, &o.Quantity, &o.CreatedAt); err != nil {
		return nil, err
	}
	return o, nil
}

// List returns all orders, or only one user's when userID > 0.
func (s *OrderStore) List(ctx context.Context, userID int) ([]*Order, error) {
	// One static query for both cases. Values are always bound parameters,
	// never concatenated into SQL (no injection). The ::bigint cast tells
	// Postgres the type of $1, which it uses in two different places.
	rows, err := s.pool.Query(ctx,
		"SELECT "+orderCols+" FROM orders WHERE $1::bigint = 0 OR user_id = $1 ORDER BY id", userID)
	if err != nil {
		return nil, fmt.Errorf("query orders: %w", err)
	}
	defer rows.Close()

	orders := make([]*Order, 0)
	for rows.Next() {
		o, err := scanOrder(rows)
		if err != nil {
			return nil, fmt.Errorf("scan order: %w", err)
		}
		orders = append(orders, o)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate orders: %w", err)
	}
	return orders, nil
}

func (s *OrderStore) Get(ctx context.Context, id int) (*Order, error) {
	o, err := scanOrder(s.pool.QueryRow(ctx, "SELECT "+orderCols+" FROM orders WHERE id = $1", id))
	if errors.Is(err, pgx.ErrNoRows) {
		return nil, ErrNotFound
	}
	if err != nil {
		return nil, fmt.Errorf("get order %d: %w", id, err)
	}
	return o, nil
}

// Create inserts only if the user exists, in ONE atomic statement. A separate
// "SELECT user, then INSERT" would race with a concurrent user delete. Zero
// rows inserted surfaces as pgx.ErrNoRows from RETURNING. The FOREIGN KEY
// remains as a backstop.
func (s *OrderStore) Create(ctx context.Context, userID int, item string, quantity int) (*Order, error) {
	o, err := scanOrder(s.pool.QueryRow(ctx, `
		INSERT INTO orders (user_id, item, quantity)
		SELECT $1::bigint, $2::text, $3::int
		WHERE EXISTS (SELECT 1 FROM users WHERE id = $1)
		RETURNING `+orderCols, userID, item, quantity))
	if errors.Is(err, pgx.ErrNoRows) {
		return nil, ErrUserNotFound
	}
	if err != nil {
		return nil, fmt.Errorf("insert order: %w", err)
	}
	return o, nil
}

func (s *OrderStore) Delete(ctx context.Context, id int) error {
	tag, err := s.pool.Exec(ctx, "DELETE FROM orders WHERE id = $1", id)
	if err != nil {
		return fmt.Errorf("delete order %d: %w", id, err)
	}
	if tag.RowsAffected() == 0 {
		return ErrNotFound
	}
	return nil
}

Replace the contents of internal/storage/storage.go with

package storage

import (
	"context"
	"embed"
	"errors"
	"fmt"
	"io/fs"
	"time"

	"github.com/jackc/pgx/v5/pgxpool"
	"github.com/jackc/pgx/v5/stdlib"
	"github.com/pressly/goose/v3"
	"github.com/pressly/goose/v3/lock"
)

// ErrNotFound is a *sentinel error*: a package-level value callers compare
// against with errors.Is. String comparison of err.Error() is brittle (reword
// the message or wrap the error and it silently stops matching); errors.Is
// walks the whole wrap chain.
var ErrNotFound = errors.New("not found")

// The SQL files are compiled INTO the binary, so the container needs no extra
// files and the binary always ships with the schema it was built against.
//
//go:embed migrations/*.sql
var migrationsFS embed.FS

// PoolConfig holds connection-pool tuning. MaxConns caps connections per instance, so replicas x MaxConns must stay below the server's limit.
type PoolConfig struct {
	URL             string
	MaxConns        int32
	MinConns        int32
	MaxConnLifetime time.Duration
	MaxConnIdleTime time.Duration
}

// NewPool creates a pgx connection pool. Unlike database/sql, pgxpool speaks
// Postgres natively (faster, richer types, proper context cancellation).
// Extra options let callers tweak the parsed config (tests use it to pin a
// schema).
func NewPool(ctx context.Context, c PoolConfig, opts ...func(*pgxpool.Config)) (*pgxpool.Pool, error) {
	cfg, err := pgxpool.ParseConfig(c.URL)
	if err != nil {
		// Do not wrap the URL into the message: it contains the password.
		return nil, errors.New("parse database url: invalid format")
	}
	cfg.MaxConns = c.MaxConns
	cfg.MinConns = c.MinConns
	cfg.MaxConnLifetime = c.MaxConnLifetime // recycle conns so DNS/failover changes are picked up
	cfg.MaxConnIdleTime = c.MaxConnIdleTime
	cfg.HealthCheckPeriod = 30 * time.Second

	for _, o := range opts {
		o(cfg)
	}

	pool, err := pgxpool.NewWithConfig(ctx, cfg)
	if err != nil {
		return nil, fmt.Errorf("create pool: %w", err)
	}
	// NewWithConfig connects lazily; Ping forces a real connection so a wrong
	// host/password fails at startup, not on the first request.
	if err := pool.Ping(ctx); err != nil {
		pool.Close()
		return nil, fmt.Errorf("ping database: %w", err)
	}
	return pool, nil
}

// Migrate applies all pending migrations. Safe to run concurrently from many
// replicas: goose takes a Postgres advisory lock so only one runs at a time.
func Migrate(ctx context.Context, pool *pgxpool.Pool) error {
	sub, err := fs.Sub(migrationsFS, "migrations")
	if err != nil {
		return err
	}
	locker, err := lock.NewPostgresSessionLocker()
	if err != nil {
		return err
	}
	// goose wants database/sql; stdlib adapts our pool. Closing this *sql.DB
	// does not close the pool.
	db := stdlib.OpenDBFromPool(pool)
	defer db.Close()

	p, err := goose.NewProvider(goose.DialectPostgres, db, sub, goose.WithSessionLocker(locker))
	if err != nil {
		return fmt.Errorf("create migration provider: %w", err)
	}
	if _, err := p.Up(ctx); err != nil {
		return fmt.Errorf("apply migrations: %w", err)
	}
	return nil
}

Create internal/storage/users.go

package storage

import (
	"context"
	"errors"
	"fmt"
	"time"

	"github.com/jackc/pgx/v5"
	"github.com/jackc/pgx/v5/pgxpool"
)

type User struct {
	ID        int       `json:"id"`
	Name      string    `json:"name"`
	CreatedAt time.Time `json:"created_at"`
}

type UserStore struct {
	pool *pgxpool.Pool
}

func NewUserStore(pool *pgxpool.Pool) *UserStore {
	return &UserStore{pool: pool}
}

const userCols = "id, name, created_at"

func scanUser(row pgx.Row) (*User, error) {
	u := &User{}
	if err := row.Scan(&u.ID, &u.Name, &u.CreatedAt); err != nil {
		return nil, err
	}
	return u, nil
}

// Every method takes a context and passes it to the pool. When the client
// disconnects or the request deadline passes, the in-flight query is
// cancelled server-side and the connection returns to the pool.

func (s *UserStore) GetAll(ctx context.Context) ([]*User, error) {
	rows, err := s.pool.Query(ctx, "SELECT "+userCols+" FROM users ORDER BY id")
	if err != nil {
		return nil, fmt.Errorf("query users: %w", err)
	}
	defer rows.Close()

	users := make([]*User, 0) // non-nil so JSON encodes [] rather than null
	for rows.Next() {
		u, err := scanUser(rows)
		if err != nil {
			return nil, fmt.Errorf("scan user: %w", err)
		}
		users = append(users, u)
	}
	// rows.Err reports errors that ended iteration early (e.g. cancelled
	// context). Skipping it can silently return a truncated list.
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate users: %w", err)
	}
	return users, nil
}

func (s *UserStore) Get(ctx context.Context, id int) (*User, error) {
	u, err := scanUser(s.pool.QueryRow(ctx, "SELECT "+userCols+" FROM users WHERE id = $1", id))
	if errors.Is(err, pgx.ErrNoRows) {
		return nil, ErrNotFound
	}
	if err != nil {
		return nil, fmt.Errorf("get user %d: %w", id, err)
	}
	return u, nil
}

// Create uses INSERT ... RETURNING: one round trip returns the row exactly as
// stored, including database-generated id and created_at.
func (s *UserStore) Create(ctx context.Context, name string) (*User, error) {
	u, err := scanUser(s.pool.QueryRow(ctx,
		"INSERT INTO users (name) VALUES ($1) RETURNING "+userCols, name))
	if err != nil {
		return nil, fmt.Errorf("insert user: %w", err)
	}
	return u, nil
}

func (s *UserStore) Update(ctx context.Context, id int, name string) (*User, error) {
	u, err := scanUser(s.pool.QueryRow(ctx,
		"UPDATE users SET name = $1 WHERE id = $2 RETURNING "+userCols, name, id))
	if errors.Is(err, pgx.ErrNoRows) {
		return nil, ErrNotFound
	}
	if err != nil {
		return nil, fmt.Errorf("update user %d: %w", id, err)
	}
	return u, nil
}

func (s *UserStore) Delete(ctx context.Context, id int) error {
	tag, err := s.pool.Exec(ctx, "DELETE FROM users WHERE id = $1", id)
	if err != nil {
		return fmt.Errorf("delete user %d: %w", id, err)
	}
	if tag.RowsAffected() == 0 {
		return ErrNotFound
	}
	return nil
}

Replace the contents of .gitignore with

bin/

Replace the contents of makefile with

.PHONY: run build test db clean

BINARY := bin/server

# Where the app and the tests find PostgreSQL. `make db` starts a matching one.
export DATABASE_URL ?= postgres://app:change-me@localhost:5432/app?sslmode=disable
TEST_DATABASE_URL ?= $(DATABASE_URL)

run:                      ## run the API (needs a database: make db)
	go run ./cmd/rest

build:
	mkdir -p bin
	CGO_ENABLED=0 go build -trimpath -ldflags="-s -w" -o $(BINARY) ./cmd/rest

test:                     ## vet + all tests (database tests run against $(TEST_DATABASE_URL))
	go vet ./...
	TEST_DATABASE_URL="$(TEST_DATABASE_URL)" go test -race ./...

db:                       ## start a throwaway PostgreSQL in Docker (its data disappears with the container)
	docker run -d --name pg -p 5432:5432 \
		-e POSTGRES_USER=app -e POSTGRES_PASSWORD=change-me -e POSTGRES_DB=app \
		postgres:17-alpine

clean:
	rm -rf bin

Try it

export DATABASE_URL='postgres://app:change-me@localhost:5432/app?sslmode=disable'
go run ./cmd/rest

$ curl -XPOST localhost:7800/users -d '{"name":"Ann"}'
{"id":1,"name":"Ann","created_at":"2026-10-18T12:25:07.315021+05:30"}

$ curl -XPOST localhost:7800/orders -d '{"user_id":1,"item":"book","quantity":2}'
{"id":1,"user_id":1,"item":"book","quantity":2,"created_at":"2026-10-18T12:25:07.333454+05:30"}

Peek into the database (docker exec -it pg psql -U app -d app):

app=# \dt
 public | goose_db_version | table | app
 public | orders           | table | app
 public | users            | table | app

app=# \d orders
 id         | bigint                   | not null | generated always as identity
 user_id    | bigint                   | not null |
 item       | text                     | not null |
 quantity   | integer                  | not null |
 created_at | timestamp with time zone | not null | now()
Indexes:
    "orders_pkey" PRIMARY KEY, btree (id)
    "idx_orders_user_id" btree (user_id)
Check constraints:
    "orders_item_check" CHECK (length(btrim(item)) > 0)
    "orders_quantity_check" CHECK (quantity > 0)
Foreign-key constraints:
    "orders_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE

Recap

A database server enables multiple app instances, backups, replication.
Size the pool deliberately: replicas × MaxConns below the server's limit.
Versioned, embedded migrations; never edit applied ones.
Let the database enforce integrity: identity, CHECK, foreign keys, TIMESTAMPTZ.
Because handlers depend on interfaces, swapping the database touched only storage and main.
Test against the real database, isolated by schema.

Exercises

Write migration 00003 that adds a nullable email column to users. Run the app and look at goose_db_version.
Set MaxConns to 1 and fire 20 concurrent requests (xargs -P20 curl ...). What happens to latency? You've just reproduced pool exhaustion; we'll measure it in Part 7.
Run EXPLAIN on SELECT * FROM orders WHERE user_id = 1 with and without the index (DROP INDEX temporarily). On an empty table the planner may ignore the index; insert 100,000 rows with generate_series and compare.

Part 6: Moving to PostgreSQL

Why leave SQLite?

Start a Postgres

The big idea: only the storage layer changes

The driver: pgx

Step 1: the connection pool

Step 2: migrations

Step 3: a better schema

Step 4: the stores, rewritten

Step 5: wire it in `main`

Step 6: tests against the real thing

Complete code for this part

Try it

Recap

Exercises

Comments

Build a production-grade REST API in Go

More from this blog

Part 17: Wrap-up: the checklist, what we skipped, and where to go next

Part 16: OpenAPI: a contract that can't drift, and a docs page you can trust

Part 15: Idempotency keys: making retries safe

Part 14: TLS: encrypting the wire, hot-reloading certificates, and verifying the database

Part 13: Protecting the login endpoint: layered rate limiting

Why leave SQLite?

Start a Postgres

The big idea: only the storage layer changes

The driver: pgx

Step 1: the connection pool

Step 2: migrations

Step 3: a better schema

Step 4: the stores, rewritten

Step 5: wire it in main

Step 6: tests against the real thing

Complete code for this part

Try it

Recap

Exercises

Comments

Build a production-grade REST API in Go

More from this blog

Part 17: Wrap-up: the checklist, what we skipped, and where to go next

Part 16: OpenAPI: a contract that can't drift, and a docs page you can trust

Part 15: Idempotency keys: making retries safe

Part 14: TLS: encrypting the wire, hot-reloading certificates, and verifying the database

Part 13: Protecting the login endpoint: layered rate limiting

Step 5: wire it in `main`