# 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`: ```bash 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: ```plaintext 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: ```text 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 ```bash 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)` ```go // 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](https://github.com/pressly/goose), with the SQL files **embedded into the binary**: **Create** `internal/storage/migrations/00001_create_users.sql` ```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` ```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)` ```go // 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)` ```go 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` ```diff --- 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` ```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. ```bash 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` ```diff --- 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** ```go 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** ```go 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` ```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** ```plaintext bin/ ``` **Replace the contents of** `makefile` **with** ```makefile .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 ```bash export DATABASE_URL='postgres://app:change-me@localhost:5432/app?sslmode=disable' go run ./cmd/rest ``` ```bash $ 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`): ```plaintext 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 1. Write migration `00003` that adds a nullable `email` column to `users`. Run the app and look at `goose_db_version`. 2. 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. 3. 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.