# SQL databases in Go

> Connect to PostgreSQL with pgxpool or database/sql, run safe parameterised queries, scan rows and NULLs, and size your pool.

Backend with Go roadmap · Data · 17 min read · https://levelcode.org/roadmaps/go-backend/sql-with-go

The code that talks to your database is where many production incidents start: leaked connections, injected SQL, a NULL nobody expected. This lesson shows how Go talks to PostgreSQL through the standard `database/sql` package and the PostgreSQL-specific `pgx` driver. You'll open a pool, run queries, scan rows into structs, handle NULLs, and learn the pool behaviours that matter under load.

## Two ways to talk to PostgreSQL

Go gives you two layers to choose from.

**`database/sql`** is the standard library's generic interface. It defines `*sql.DB`, `Query`, `Scan` and friends, and leaves the wire protocol to a *driver* you import separately. The same code shape works for PostgreSQL, MySQL or SQLite.

**`pgx`** (`github.com/jackc/pgx/v5`) is the most widely used PostgreSQL driver for Go. It can act as a `database/sql` driver, but it also has its own native interface, with its own pool in the `pgxpool` package.

| | `database/sql` + pgx driver | pgx native (`pgxpool`) |
|---|---|---|
| Portability | Same API for any database | PostgreSQL only |
| PostgreSQL types | Through `Scanner`/`Valuer` adapters | Built in: arrays, `jsonb`, `uuid`, ranges |
| Extras | None | `CopyFrom`, batching, `LISTEN/NOTIFY` |
| Row helpers | Manual `Scan` | `pgx.CollectRows`, struct mapping |

If your service only targets PostgreSQL, use `pgxpool`. When a library insists on a `*sql.DB` (a migration tool, say), `stdlib.OpenDBFromPool(pool)` wraps the same pool. This lesson uses `pgxpool` and shows `database/sql` where it differs.

```bash
go get github.com/jackc/pgx/v5
```

## Opening a connection pool

Opening a PostgreSQL connection costs a TCP handshake, often TLS, and authentication. A **pool** keeps connections open and lends one to each query. Create one pool at startup, share it everywhere (it's safe for concurrent use), and close it on shutdown.

```go
func openPool(ctx context.Context, dsn string) (*pgxpool.Pool, error) {
	cfg, err := pgxpool.ParseConfig(dsn)
	if err != nil {
		return nil, fmt.Errorf("parse dsn: %w", err)
	}
	cfg.MaxConns = 10
	cfg.MinConns = 2
	cfg.MaxConnLifetime = time.Hour
	cfg.MaxConnIdleTime = 5 * time.Minute

	pool, err := pgxpool.NewWithConfig(ctx, cfg)
	if err != nil {
		return nil, fmt.Errorf("create pool: %w", err)
	}

	// The pool connects lazily, so check the database is really there.
	pingCtx, cancel := context.WithTimeout(ctx, 5*time.Second)
	defer cancel()
	if err := pool.Ping(pingCtx); err != nil {
		pool.Close()
		return nil, fmt.Errorf("ping database: %w", err)
	}
	return pool, nil
}
```

The DSN is a URL: `postgres://app:secret@localhost:5432/app?sslmode=disable`. Read it from an environment variable, never from source code.

> [!IMPORTANT]
> Neither `pgxpool.NewWithConfig` nor `sql.Open` guarantees a working connection. A wrong password or a stopped database only shows up on the first query. Always `Ping` at startup so a misconfigured service fails immediately instead of on its first request.

The `database/sql` version looks similar. The blank import registers pgx under the driver name `"pgx"`:

```go
import (
	"database/sql"

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

func openDB(dsn string) (*sql.DB, error) {
	db, err := sql.Open("pgx", dsn)
	if err != nil {
		return nil, err
	}
	db.SetMaxOpenConns(10) // default is 0: unlimited
	db.SetMaxIdleConns(10) // default is 2
	db.SetConnMaxLifetime(time.Hour)
	db.SetConnMaxIdleTime(5 * time.Minute)
	return db, nil
}
```

`database/sql` defaults to unlimited connections, which can overwhelm PostgreSQL in a traffic spike; `pgxpool` defaults to the greater of 4 and the CPU count. Set the limit explicitly either way.

## Running queries

Every example below uses this table:

```sql
CREATE TABLE users (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email      text NOT NULL UNIQUE,
    name       text NOT NULL,
    bio        text,
    created_at timestamptz NOT NULL DEFAULT now()
);
```

There are three query methods, chosen by what you expect back.

**`QueryRow`** for exactly one row. `RETURNING` lets an `INSERT` hand back generated columns in the same round trip:

```go
func (s *UserStore) Create(ctx context.Context, email, name string) (User, error) {
	u := User{Email: email, Name: name}
	err := s.pool.QueryRow(ctx,
		`INSERT INTO users (email, name) VALUES ($1, $2) RETURNING id, created_at`,
		email, name,
	).Scan(&u.ID, &u.CreatedAt)
	if err != nil {
		return User{}, fmt.Errorf("insert user: %w", err)
	}
	return u, nil
}
```

When no row matches, `Scan` returns `pgx.ErrNoRows` (`sql.ErrNoRows` in `database/sql`; pgx's error also matches `sql.ErrNoRows` with `errors.Is`). Translate it into your own error so HTTP handlers never import the driver:

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

func (s *UserStore) Get(ctx context.Context, id int64) (User, error) {
	var u User
	err := s.pool.QueryRow(ctx,
		`SELECT id, email, name, bio, created_at FROM users WHERE id = $1`, id,
	).Scan(&u.ID, &u.Email, &u.Name, &u.Bio, &u.CreatedAt)
	if errors.Is(err, pgx.ErrNoRows) {
		return User{}, ErrNotFound
	}
	if err != nil {
		return User{}, fmt.Errorf("get user %d: %w", id, err)
	}
	return u, nil
}
```

**`Query`** for zero or more rows. The loop has a fixed shape: `defer rows.Close()`, iterate with `Next`, then check `rows.Err()`, which reports anything that went wrong mid-stream, such as a dropped connection:

```go
func (s *UserStore) ListSince(ctx context.Context, since time.Time) ([]User, error) {
	rows, err := s.pool.Query(ctx,
		`SELECT id, email, name, bio, created_at FROM users
		 WHERE created_at >= $1 ORDER BY id`, since)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var users []User
	for rows.Next() {
		var u User
		if err := rows.Scan(&u.ID, &u.Email, &u.Name, &u.Bio, &u.CreatedAt); err != nil {
			return nil, err
		}
		users = append(users, u)
	}
	return users, rows.Err()
}
```

**`Exec`** when you don't need rows back. The command tag tells you how many rows were affected:

```go
tag, err := s.pool.Exec(ctx, `DELETE FROM users WHERE id = $1`, id)
if err != nil {
	return err
}
if tag.RowsAffected() == 0 {
	return ErrNotFound
}
```

### Placeholders, never string formatting

`$1`, `$2` are **placeholders**. The SQL text and the values travel to PostgreSQL separately, so a value can never be interpreted as SQL. Compare:

```go
// DANGEROUS: name = "x'; DROP TABLE users; --" rewrites your query.
q := fmt.Sprintf("SELECT id FROM users WHERE name = '%s'", name)

// Safe: name is always just a value.
row := s.pool.QueryRow(ctx, "SELECT id FROM users WHERE name = $1", name)
```

Placeholders work only for values, not for table or column names. If those must vary (a sort column, for example), pick them from a hard-coded allowlist.

## Less boilerplate with CollectRows

Writing `Scan(&u.ID, &u.Email, ...)` in every function is tedious and easy to get out of order. pgx can map columns to struct fields by name using `db` tags:

```go
type User struct {
	ID        int64     `db:"id"`
	Email     string    `db:"email"`
	Name      string    `db:"name"`
	Bio       *string   `db:"bio"`
	CreatedAt time.Time `db:"created_at"`
}

func (s *UserStore) List(ctx context.Context, limit int) ([]User, error) {
	rows, _ := s.pool.Query(ctx,
		`SELECT id, email, name, bio, created_at FROM users ORDER BY id LIMIT $1`, limit)
	return pgx.CollectRows(rows, pgx.RowToStructByName[User])
}
```

Ignoring the error from `Query` here is a documented pgx idiom: the returned rows carry the error, and `CollectRows` reports it and closes the rows. For a single row, `pgx.CollectOneRow` returns `pgx.ErrNoRows` when the result is empty.

`RowToStructByName` requires every selected column to match a struct field, which catches typos and forgotten columns. Use `RowToStructByNameLax` if the struct deliberately has extra fields. Avoid `SELECT *`: adding a column to the table would break the mapping.

## NULLs and type mapping

SQL `NULL` has no equivalent in a Go `string` or `int64`. Scanning one into a plain value fails:

```text
can't scan into dest[3] (col: bio): cannot scan NULL into *string
```

You have three options for nullable columns:

1. **A pointer** (`*string`, `*time.Time`): `nil` means NULL. Simple, and it encodes as JSON `null`. This is what `User.Bio` uses.
2. **A null wrapper**: `pgtype.Text` (pgx) or `sql.NullString` (`database/sql`), with a `Valid` flag.
3. **`COALESCE(bio, '')` in SQL**, when NULL and empty genuinely mean the same thing to your program.

Common PostgreSQL to Go mappings with pgx:

| PostgreSQL | Go |
|---|---|
| `bigint`, `integer` | `int64`, `int32` |
| `text`, `varchar` | `string` |
| `boolean` | `bool` |
| `timestamptz` | `time.Time` |
| `uuid` | `string` or `pgtype.UUID` |
| `text[]`, `bigint[]` | `[]string`, `[]int64` |
| `jsonb` | a struct, map or `[]byte` (JSON-decoded for you) |
| `numeric` | `pgtype.Numeric` or a decimal library; avoid `float64` for money |

Slices make "fetch these IDs" queries easy and avoid building `IN (...)` lists by hand:

```go
rows, _ := s.pool.Query(ctx,
	`SELECT id, email, name, bio, created_at FROM users WHERE id = ANY($1)`, ids)
users, err := pgx.CollectRows(rows, pgx.RowToStructByName[User])
```

## Context, timeouts and pool pitfalls

Every pgx method takes a `context.Context`. Pass the request's context: if the client disconnects, the query is cancelled and the connection returns to the pool. Add a deadline for queries that must not run long:

```go
ctx, cancel := context.WithTimeout(r.Context(), 2*time.Second)
defer cancel()
u, err := store.Get(ctx, id)
```

The pool has a fixed number of connections, and most database outages in Go services are really **pool exhaustion**: every connection is busy, and new requests wait in `Acquire` until they time out. The usual causes:

- **Rows that are never closed.** A connection stays checked out until its rows are closed. Always `defer rows.Close()` right after checking the error, or use `CollectRows`.
- **Slow work while holding rows.** Calling an external API inside a `rows.Next()` loop holds the connection for the whole call. Collect the rows first, then do the slow work.
- **N+1 queries.** Fetching 50 posts and then running one query per post for its author is 51 round trips. Use a join, or one `= ANY($1)` query for all the authors.
- **Pools sized past the server.** PostgreSQL's `max_connections` defaults to 100. Ten replicas with `MaxConns = 20` each need 200. Size pools so the total across every instance stays below the server's limit.

`pool.Stat()` exposes counters such as `AcquiredConns()`, `IdleConns()` and `EmptyAcquireCount()` (how many acquires had to wait). Exporting these as metrics lets you see exhaustion coming.

> [!TIP]
> Keep SQL in a small store type (`UserStore`, `OrderStore`) that holds the pool and returns your own domain types and errors. Handlers then depend on methods like `Get(ctx, id)`, not on pgx, which makes them easy to test with a fake store.

## Practice

Start PostgreSQL locally (for example `docker run --rm -e POSTGRES_PASSWORD=dev -p 5432:5432 postgres:17`) and create a `products` table with `id bigint` identity, `name text NOT NULL`, `price_cents bigint NOT NULL` and `tags text[] NOT NULL DEFAULT '{}'`.

Write a `ProductStore` with:

1. `Create(ctx, name, priceCents, tags)` returning the new product with its ID.
2. `Get(ctx, id)` returning `ErrNotFound` when the product doesn't exist.
3. `ListByTag(ctx, tag)` returning every product with that tag, using `CollectRows`.

In `main`, read `DATABASE_URL`, open a pool with `openPool`, create two products and print each method's result.

**Solution**

```go
package main

import (
	"context"
	"errors"
	"fmt"
	"log"
	"os"
	"time"

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

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

type Product struct {
	ID         int64    `db:"id"`
	Name       string   `db:"name"`
	PriceCents int64    `db:"price_cents"`
	Tags       []string `db:"tags"`
}

type ProductStore struct{ pool *pgxpool.Pool }

func (s *ProductStore) Create(ctx context.Context, name string, price int64, tags []string) (Product, error) {
	rows, _ := s.pool.Query(ctx,
		`INSERT INTO products (name, price_cents, tags) VALUES ($1, $2, $3)
		 RETURNING id, name, price_cents, tags`, name, price, tags)
	return pgx.CollectOneRow(rows, pgx.RowToStructByName[Product])
}

func (s *ProductStore) Get(ctx context.Context, id int64) (Product, error) {
	rows, _ := s.pool.Query(ctx,
		`SELECT id, name, price_cents, tags FROM products WHERE id = $1`, id)
	p, err := pgx.CollectOneRow(rows, pgx.RowToStructByName[Product])
	if errors.Is(err, pgx.ErrNoRows) {
		return Product{}, ErrNotFound
	}
	return p, err
}

func (s *ProductStore) ListByTag(ctx context.Context, tag string) ([]Product, error) {
	rows, _ := s.pool.Query(ctx,
		`SELECT id, name, price_cents, tags FROM products
		 WHERE $1 = ANY(tags) ORDER BY id`, tag)
	return pgx.CollectRows(rows, pgx.RowToStructByName[Product])
}

func main() {
	ctx, cancel := context.WithTimeout(context.Background(), 10*time.Second)
	defer cancel()

	pool, err := openPool(ctx, os.Getenv("DATABASE_URL")) // from the lesson
	if err != nil {
		log.Fatal(err)
	}
	defer pool.Close()
	store := &ProductStore{pool: pool}

	kb, err := store.Create(ctx, "Keyboard", 4999, []string{"hardware", "office"})
	if err != nil {
		log.Fatal(err)
	}
	if _, err := store.Create(ctx, "Desk lamp", 2500, []string{"office"}); err != nil {
		log.Fatal(err)
	}

	got, err := store.Get(ctx, kb.ID)
	fmt.Printf("get: %+v %v\n", got, err)
	_, err = store.Get(ctx, -1)
	fmt.Println("missing:", errors.Is(err, ErrNotFound))
	office, err := store.ListByTag(ctx, "office")
	fmt.Printf("office: %d products %v\n", len(office), err)
}
```

```text
get: {ID:1 Name:Keyboard PriceCents:4999 Tags:[hardware office]} <nil>
missing: true
office: 2 products <nil>
```

## Key takeaways

- Use `pgxpool` for PostgreSQL-only services; use `database/sql` (with pgx's `stdlib` driver) when a tool needs a `*sql.DB`.
- Create one pool at startup, share it, set its size explicitly, and `Ping` so bad configuration fails fast.
- Always pass values as `$n` placeholders; only allowlisted identifiers may be formatted into SQL.
- Close rows (or use `CollectRows`), check `rows.Err()`, and map `ErrNoRows` to your own `ErrNotFound`.
- Scan nullable columns into pointers or null wrappers; plain values fail on NULL.
- Pass the request context to every query, and watch for pool exhaustion caused by leaked rows, slow loops and N+1 queries.

## Quiz

1. Your service starts with a wrong database password. When does pgxpool.NewWithConfig (or sql.Open) report the problem?

   A. Immediately, because it opens all MaxConns connections
   B. Only when the first connection is actually made, such as on Ping or the first query
   C. Never; the pool silently retries forever
   D. At compile time, if the DSN is a constant

   **Answer: B.** Both constructors only validate configuration and connect lazily. A Ping at startup forces a real connection so misconfiguration fails fast instead of on the first user request.

2. The bio column is nullable. What happens when pgx scans a NULL bio into a Go string field?

   A. The field is set to the empty string
   B. The field is set to the string "NULL"
   C. The program panics
   D. Scan returns an error

   **Answer: D.** pgx returns an error such as 'cannot scan NULL into *string'. Use *string, pgtype.Text / sql.NullString, or COALESCE in SQL for nullable columns.

3. Why is QueryRow(ctx, "SELECT id FROM users WHERE name = $1", name) safe from SQL injection when the fmt.Sprintf version is not?

   A. The query text and the parameter values are sent separately, so a value is never parsed as SQL
   B. pgx escapes single quotes inside the Go string before sending it
   C. PostgreSQL rejects any value that contains a semicolon
   D. Placeholders only allow alphanumeric values

   **Answer: A.** With placeholders, the SQL is parsed with $1 as a slot and the value is bound afterwards. It can never change the query's structure, whatever characters it contains.

4. Under load, requests start hanging until they time out, and pool.Stat().AcquiredConns() equals MaxConns. Which code is the most likely cause?

   A. Passing r.Context() to every query
   B. Calling pgx.CollectRows on every Query result
   C. A function that returns early from a rows.Next() loop without ever closing the rows
   D. Using $1 placeholders instead of string formatting

   **Answer: C.** A connection stays checked out until its rows are closed. Rows that are never closed leak connections until the pool is exhausted and every new request waits in Acquire. defer rows.Close() prevents this.