Part 7. BackendLesson 19# go# backend# postgresql# sql

Working with databases

pgx and the connection pool, CRUD, transactions, SQL injections, migrations

Цели главы

  • Выбирать инструмент для работы с БД и объяснять выбор на собеседовании.
  • Подключаться к PostgreSQL через пул соединений и хранить строку подключения вне кода.
  • Писать CRUD-репозиторий на pgx и переводить ошибки БД в доменные.
  • Использовать context, транзакции и параметризованные запросы.
  • Вести схему базы миграциями.

Зачем база данных

Хранить данные в памяти или в файлах на диске нельзя: перезапустил сервис — всё пропало. Для этого есть базы данных — отдельные приложения, которые предоставляют контракт: как отправлять и получать данные. Разбираем связку «Go-сервис + PostgreSQL»; с MySQL принципы те же.

Инструменты

С сетью net/http хватает для продакшена (18 — Backend — HTTP-сервер), а с базами иначе: стандартный пакет database/sql очень низкоуровневый, поэтому вокруг него всегда строят библиотеки.

Инструмент Что это Плюсы Минусы
pgx драйвер и клиент PostgreSQL максимальная производительность, полный контроль больше ручного кода
sqlx расширение database/sql писать SQL удобнее SQL всё равно пишется вручную
sqlc генератор Go-кода из SQL-запросов типобезопасность, скорость запросы нужно писать заранее
squirrel query builder запрос собирается кодом, без склейки строк ещё одна зависимость
GORM, ent ORM быстрая разработка, меньше SQL медленнее, сложнее оптимизировать

К ORM в индустрии отношение неоднозначное: сырой SQL понятно, как читать и оптимизировать, а ORM работает медленнее и прячет запросы. В одних командах пишут на ORM, в других — только на SQL. sqlc похож на API-first из прошлой главы: сначала пишешь SQL, потом из него генерируется Go-код.

Подключение и строка подключения

База — чувствительная штука: в ней есть пользователи с правами на чтение, изменение и удаление в определённых схемах. Чтобы подключиться, нужны адрес, логин, пароль и имя базы — из них собирается строка подключения (connection string, DSN):

text
postgres://user:password@localhost:5432/goflix

Строка подключения — секрет. Её не пишут в код, а передают через переменную окружения, например DATABASE_URL, или по частям: DB_USER, DB_PASSWORD, DB_SSLMODE.

Пул соединений

Запросов к базе много — пусть не миллиард RPS, но десятки и сотни в секунду. Если соединение одно, все запросы встанут в очередь. Открывать новое на каждый запрос тоже нельзя: соединения дорогие, их число ограничено, и установка занимает время. Поэтому есть пул соединений: ограниченный набор соединений, которые переиспользуются между запросами. В pgx им управляет pgxpool: выдаёт свободное соединение и забирает его обратно после использования.

100 запросовпул: 10 соединенийPostgreSQL
go
package main

import (
    "context"
    "fmt"
    "log"
    "os"

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

func main() {
    if err := run(context.Background()); err != nil {
        log.Fatal(err)
    }
    // только сборка: нужна запущенная PostgreSQL
}

func run(ctx context.Context) error {
    // DATABASE_URL задаётся в окружении, а не в коде
    pool, err := pgxpool.New(ctx, os.Getenv("DATABASE_URL"))
    if err != nil {
        return fmt.Errorf("создание пула: %w", err)
    }
    defer pool.Close()

    if err := pool.Ping(ctx); err != nil { // проверяем, что база доступна
        return fmt.Errorf("ping базы: %w", err)
    }
    log.Println("подключились к базе")
    return nil
}

Ping отправляет базе пустой запрос — как кнопка Test connection в DBeaver или DataGrip. Логика вынесена в run, а main только печатает ошибку: так defer pool.Close() гарантированно отработает (14 — defer, panic, recover → recover работает только в своей горутине — почему log.Fatal пропускает defer).

CRUD-репозиторий

CRUD — Create, Read, Update, Delete: основные операции с данными. Репозиторий выполняет SQL и превращает строки таблицы в Go-структуры.

go
package main

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

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

// Доменные ошибки: о них знает сервисный слой, о pgx — нет.
var (
    ErrNotFound      = errors.New("not found")
    ErrAlreadyExists = errors.New("already exists")
)

type User struct {
    ID        int64
    FullName  string
    Email     string
    CreatedAt time.Time
}

type PostgresUserRepo struct {
    pool *pgxpool.Pool
}

func NewPostgresUserRepo(pool *pgxpool.Pool) *PostgresUserRepo {
    return &PostgresUserRepo{pool: pool}
}

func (r *PostgresUserRepo) Create(ctx context.Context, name, email string) (User, error) {
    var u User
    err := r.pool.QueryRow(ctx,
        `INSERT INTO users (full_name, email) VALUES ($1, $2)
         RETURNING id, full_name, email, created_at`,
        name, email,
    ).Scan(&u.ID, &u.FullName, &u.Email, &u.CreatedAt)
    if err != nil {
        return User{}, fmt.Errorf("создание пользователя: %w", mapDBError(err))
    }
    return u, nil
}

func (r *PostgresUserRepo) GetByID(ctx context.Context, id int64) (User, error) {
    ctx, cancel := context.WithTimeout(ctx, 2*time.Second) // свой таймаут на запрос
    defer cancel()

    var u User
    err := r.pool.QueryRow(ctx,
        "SELECT id, full_name, email, created_at FROM users WHERE id = $1", id,
    ).Scan(&u.ID, &u.FullName, &u.Email, &u.CreatedAt)
    if err != nil {
        return User{}, fmt.Errorf("пользователь %d: %w", id, mapDBError(err))
    }
    return u, nil
}

func (r *PostgresUserRepo) List(ctx context.Context, limit, offset int) ([]User, error) {
    rows, err := r.pool.Query(ctx,
        "SELECT id, full_name, email, created_at FROM users ORDER BY id LIMIT $1 OFFSET $2",
        limit, offset,
    )
    if err != nil {
        return nil, fmt.Errorf("список пользователей: %w", err)
    }
    defer rows.Close() // вернуть соединение в пул

    users := []User{}
    for rows.Next() {
        var u User
        if err := rows.Scan(&u.ID, &u.FullName, &u.Email, &u.CreatedAt); err != nil {
            return nil, fmt.Errorf("чтение строки: %w", err)
        }
        users = append(users, u)
    }
    return users, rows.Err() // ошибка, случившаяся во время перебора
}

func (r *PostgresUserRepo) Delete(ctx context.Context, id int64) error {
    tag, err := r.pool.Exec(ctx, "DELETE FROM users WHERE id = $1", id)
    if err != nil {
        return fmt.Errorf("удаление пользователя %d: %w", id, err)
    }
    if tag.RowsAffected() == 0 { // ничего не удалили — такого пользователя нет
        return ErrNotFound
    }
    return nil
}

// mapDBError переводит ошибки PostgreSQL в доменные.
func mapDBError(err error) error {
    if errors.Is(err, pgx.ErrNoRows) {
        return ErrNotFound
    }
    var pgErr *pgconn.PgError
    if errors.As(err, &pgErr) {
        switch pgErr.Code {
        case "23505": // unique_violation
            return ErrAlreadyExists
        case "23503": // foreign_key_violation
            return ErrNotFound
        }
    }
    return err
}

func main() {
    if err := run(context.Background()); err != nil {
        log.Fatal(err)
    }
    // только сборка: нужна запущенная PostgreSQL с таблицей users
}

func run(ctx context.Context) error {
    pool, err := pgxpool.New(ctx, os.Getenv("DATABASE_URL"))
    if err != nil {
        return fmt.Errorf("создание пула: %w", err)
    }
    defer pool.Close()

    repo := NewPostgresUserRepo(pool)
    u, err := repo.Create(ctx, "Алексей", "alex@goflix.ru")
    if errors.Is(err, ErrAlreadyExists) {
        log.Println("пользователь с таким email уже есть")
        return nil
    }
    if err != nil {
        return err
    }
    log.Printf("создан пользователь %d", u.ID)
    return nil
}

Разберём каждую операцию:

  • Create — INSERT … RETURNING сразу возвращает созданную строку, и Scan раскладывает колонки по полям структуры. Репозиторий отдаёт наверх уже Go-структуру, и бизнес-логика работает с ней: проверяет, есть ли Premium, что-то меняет.
  • GetByID — если строки нет, pgx возвращает pgx.ErrNoRows. «Пользователь не найден» — не сбой, а нормальный сценарий: Telegram не падает, когда не находит контакт. Этот случай отдельно переводим в ErrNotFound, чтобы хендлер ответил 404.
  • List — Query возвращает строки, rows.Next() перебирает их, каждую Scan-ят в структуру и добавляют в слайс. defer rows.Close() обязателен: он возвращает соединение в пул. После цикла проверяют rows.Err() — ошибку, которая могла случиться во время перебора.
  • Delete — Exec для запросов без строк в ответе. RowsAffected() == 0 значит, что удалять было нечего, — это тоже ErrNotFound.

Маппинг ошибок

Если репозиторий будет отдавать наверх ошибки драйвера (pgx.ErrNoRows, коды PostgreSQL), о базе узнает сервисный слой. Переедем на другую базу — придётся менять и его. Поэтому ошибки БД переводят в доменные прямо в репозитории: бизнес-логика не знает, что такое «rows», но знает «пользователя нет».

  • pgx.ErrNoRows → ErrNotFound — проверка по значению через errors.Is.
  • *pgconn.PgError с кодом 23505 (нарушение уникальности) → ErrAlreadyExists, с кодом 23503 (нарушение внешнего ключа) → ErrNotFound — проверка по типу через errors.As.

Это упрощённый пример mapDBError из кода выше; приёмы — из 13 — Обработка ошибок.

Context во всём приложении

Все методы репозитория принимают context первым параметром. Это даёт две вещи:

  • Таймаут на операцию. Как в GetByID: context.WithTimeout ограничивает конкретный запрос.
  • Отмену по цепочке. Если хендлер передаёт в репозиторий r.Context(), то при обрыве клиента — как при остановке сервера в 18 — Backend — HTTP-сервер → Graceful shutdown и таймауты — тяжёлый запрос к PostgreSQL отменится сам. Дополнительно обрабатывать такие случаи не нужно.

context пронизывает всё приложение: от main через хендлеры и сервисы до репозиториев.

Транзакции

Иногда несколько операций должны выполниться вместе: либо все, либо ни одной. Нельзя перепрыгнуть пропасть на 99%. Классический пример — перевод денег: списать с одного счёта и зачислить на другой. Если списали, а зачислить не удалось, деньги просто исчезнут.

В PostgreSQL для этого есть BEGIN, COMMIT и ROLLBACK, в pgx — то же самое:

go
package main

import (
    "context"
    "fmt"
    "log"
    "os"

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

type AccountRepo struct {
    pool *pgxpool.Pool
}

// TransferWithTx переводит деньги между счетами одной транзакцией.
func (r *AccountRepo) TransferWithTx(ctx context.Context, fromID, toID, amountKopecks int64) error {
    tx, err := r.pool.Begin(ctx)
    if err != nil {
        return fmt.Errorf("начало транзакции: %w", err)
    }
    defer tx.Rollback(ctx) // откатит всё, если не дойдём до Commit; после Commit ничего не делает

    if _, err := tx.Exec(ctx,
        "UPDATE accounts SET balance = balance - $1 WHERE id = $2", amountKopecks, fromID); err != nil {
        return fmt.Errorf("списание: %w", err)
    }
    if _, err := tx.Exec(ctx,
        "UPDATE accounts SET balance = balance + $1 WHERE id = $2", amountKopecks, toID); err != nil {
        return fmt.Errorf("зачисление: %w", err)
    }
    return tx.Commit(ctx)
}

func main() {
    ctx := context.Background()
    pool, err := pgxpool.New(ctx, os.Getenv("DATABASE_URL"))
    if err != nil {
        log.Fatal(err)
    }
    defer pool.Close()

    repo := &AccountRepo{pool: pool}
    if err := repo.TransferWithTx(ctx, 1, 2, 50_000); err != nil { // 500 рублей
        log.Printf("перевод не выполнен: %v", err)
    }
    // только сборка: нужна запущенная PostgreSQL с таблицей accounts
}
  • defer tx.Rollback(ctx) ставят сразу после Begin: при любом return с ошибкой транзакция откатится, а после успешного Commit откат ничего не делает.
  • Методы, которые работают транзакционно, часто называют с суффиксом WithTx.
  • Когда сервис использует несколько репозиториев — заказ в одном, пользователь в другом, событие в Kafka, — транзакцию прокидывают через них или используют transaction manager. Это продвинутая тема: достаточно знать, что такое бывает.

Query builder: squirrel

Вместо сырых SQL-строк запрос можно собирать кодом — query builder. Часто для этого берут squirrel: запрос читается как SQL (Update, Set, Where), но собирается вызовами методов, и опечатка в одном месте не ломает строку целиком. PlaceholderFormat(sq.Dollar) включает плейсхолдеры $1, $2 в формате PostgreSQL.

go
package main

import (
    "fmt"

    sq "github.com/Masterminds/squirrel"
)

func main() {
    query, args, err := sq.Update("accounts").
        Set("balance", sq.Expr("balance - ?", 500)).
        Where(sq.Eq{"id": 42}).
        PlaceholderFormat(sq.Dollar).
        ToSql()
    if err != nil {
        fmt.Println("ошибка:", err)
        return
    }
    fmt.Println(query)
    fmt.Println(args)

    query, args, err = sq.Select("id", "full_name").From("users").
        Where(sq.Eq{"email": "alex@goflix.ru"}).
        OrderBy("id").Limit(10).
        PlaceholderFormat(sq.Dollar).
        ToSql()
    if err != nil {
        fmt.Println("ошибка:", err)
        return
    }
    fmt.Println(query)
    fmt.Println(args)
}
stdout // вывод программы
UPDATE accounts SET balance = balance - $1 WHERE id = $2
[500 42]
SELECT id, full_name FROM users WHERE email = $1 ORDER BY id LIMIT 10
[alex@goflix.ru]

Готовые query и args передают в tx.Exec(ctx, query, args...) или pool.Query.

SQL-инъекции и плейсхолдеры

Значения всегда передают отдельно от текста запроса — через плейсхолдеры $1, $2. База получает запрос и значения раздельно и гарантирует, что значение не станет частью SQL-выражения. Если же склеить запрос со строкой от пользователя, случится катастрофа — SQL-инъекция.

go
package main

import "fmt"

func main() {
    // name пришло от пользователя
    name := "x' OR '1'='1"
    unsafeQuery := "SELECT id FROM users WHERE full_name = '" + name + "'" // так нельзя
    fmt.Println(unsafeQuery)

    safeQuery := "SELECT id FROM users WHERE full_name = $1" // текст запроса не меняется
    fmt.Println(safeQuery, "| аргумент:", name)
}
stdout // вывод программы
SELECT id FROM users WHERE full_name = 'x' OR '1'='1'
SELECT id FROM users WHERE full_name = $1 | аргумент: x' OR '1'='1

В первом запросе условие OR '1'='1' истинно для всех строк — злоумышленник получит всю таблицу, а другой строкой может и удалить её. Во втором запросе текст неизменен, а значение — просто данные. Поэтому в сыром SQL примеров выше инъекций нет.

Миграции

Запросам нужны таблицы, колонки и схемы, а в новом сервисе их ещё нет. Можно подключиться через DBeaver и руками выполнить CREATE TABLE, потом ALTER TABLE ADD COLUMN… Но экземпляров базы бывает несколько, и ручные правки быстро расходятся. Код мы версионируем в Git — схему базы тоже нужно версионировать.

Миграции — версионированные изменения схемы: каждое изменение — отдельный файл, у файлов есть порядок. При старте сервиса — или на шаге деплоя — инструмент смотрит в служебную таблицу (например, schema_migrations), какие миграции уже применены, и накатывает новые по порядку.

text
migrations/
├── 001_create_users.sql      ✔ применена
├── 002_add_email.sql         ✔ применена
├── 003_create_orders.sql     ✔ применена
└── 004_add_index.sql         … ждёт применения

У каждой миграции две половины: up — изменить схему, down — вернуть как было (rollback). Создали таблицу — в down её удаляем; добавили колонку — удаляем колонку.

Самые популярные инструменты — golang-migrate и goose, отличаются они незначительно.

golang-migrate: up и down лежат в двух разных файлах — 000001_create_users.up.sql и 000001_create_users.down.sql.

sql
-- 000001_create_users.up.sql
CREATE TABLE users (
    id         BIGSERIAL PRIMARY KEY,
    full_name  VARCHAR(100) NOT NULL,
    email      VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- 000001_create_users.down.sql
DROP TABLE users;

goose: обе секции в одном файле — так миграцию удобно читать целиком. Нужны аннотации -- +goose Up и -- +goose Down; для сложных выражений есть ещё -- +goose StatementBegin и StatementEnd.

sql
-- 00002_add_phone.sql
-- +goose Up
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- +goose Down
ALTER TABLE users DROP COLUMN phone;

Миграции goose можно писать и на Go — это удобно, когда нужно не просто изменить схему, а, например, загрузить в базу кучу данных из файлов:

go
// migrations/00003_seed_roles.go
package migrations

import (
    "context"
    "database/sql"

    "github.com/pressly/goose/v3"
)

func init() {
    goose.AddMigrationContext(upSeedRoles, downSeedRoles)
}

func upSeedRoles(ctx context.Context, tx *sql.Tx) error {
    for _, role := range []string{"admin", "moderator", "user"} {
        if _, err := tx.ExecContext(ctx,
            "INSERT INTO roles (name) VALUES ($1) ON CONFLICT DO NOTHING", role); err != nil {
            return err
        }
    }
    return nil
}

func downSeedRoles(ctx context.Context, tx *sql.Tx) error {
    _, err := tx.ExecContext(ctx, "DELETE FROM roles WHERE name IN ('admin', 'moderator', 'user')")
    return err
}

Чаще всего миграции применяются автоматически при старте сервиса:

go
package main

import (
    "database/sql"
    "fmt"
    "log"
    "os"

    _ "github.com/jackc/pgx/v5/stdlib" // драйвер database/sql для pgx
    "github.com/pressly/goose/v3"
)

func migrate(dsn string) error {
    db, err := sql.Open("pgx", dsn)
    if err != nil {
        return fmt.Errorf("открытие БД: %w", err)
    }
    defer db.Close()

    if err := goose.SetDialect("postgres"); err != nil {
        return err
    }
    if err := goose.Up(db, "migrations"); err != nil { // применит новые миграции по порядку
        return fmt.Errorf("миграции: %w", err)
    }
    return nil
}

func main() {
    if err := migrate(os.Getenv("DATABASE_URL")); err != nil {
        log.Fatal(err)
    }
    log.Println("схема БД актуальна")
    // только сборка: нужна запущенная PostgreSQL и папка migrations
}

Есть и CLI-команды — накатить, откатить или посмотреть статус вручную. На практике ими пользуются редко: обычно всё делается автоматически при старте.

bash
goose -dir ./migrations postgres "$DATABASE_URL" status
goose -dir ./migrations postgres "$DATABASE_URL" up
migrate -path ./migrations -database "$DATABASE_URL" up

Что спрашивают на собеседовании

В отличие от синтаксиса Go — устройство мапы, бакеты, зачем context, как работают моки, — по базам и веб-сервисам «в разрезе Go» теоретических вопросов почти не задают. Задачи в основном практические: провести код-ревью сервиса или рассказать, как ты работал с базами. И отвечать нужно чем-то большим, чем «был Go и была БД».

Итоги

  • Пул соединений: зачем переиспользовать соединения и как не забить пул.
  • Параметризованные запросы и последствия SQL-инъекций.
  • Инструменты: pgx, sqlx, sqlc, squirrel, ORM.
  • Маппинг ошибок БД в доменные.
  • context в каждом запросе.
  • Транзакции: Begin, Commit, Rollback.
  • Миграции: версионирование схемы.

Материала двух последних глав хватает, чтобы написать своё backend-приложение. За кадром остался сервисный слой — это бизнес-логика, которую ты пишешь сам, — и инфраструктура вроде Docker.

Практика

Домашнее задание: CRUD-сервис

Напиши простой CRUD-сервис — пользователи или задачи: HTTP-сервер с парой эндпоинтов, PostgreSQL и миграции. После этого любой Go-бэкенд-проект будет казаться знакомым.

  • Поднять PostgreSQL в Docker; пароль — только в переменной окружения:
    bash
    export GOFLIX_DB_PASSWORD='придумай-пароль'
    docker run --name goflix-db -e POSTGRES_PASSWORD="$GOFLIX_DB_PASSWORD" -e POSTGRES_DB=goflix -p 5432:5432 -d postgres:17
    export DATABASE_URL="postgres://postgres:${GOFLIX_DB_PASSWORD}@localhost:5432/goflix?sslmode=disable"
  • go mod init goflix, затем go get github.com/jackc/pgx/v5 github.com/pressly/goose/v3. Свежие версии goose требуют Go 1.26+: если установлен Go постарше, команда go сама скачает нужный toolchain и поднимет версию в go.mod — или обнови Go заранее: brew upgrade go.
  • Миграция 00001_create_movies.sql: таблица movies (id BIGSERIAL, title, year, created_at); применение через goose.Up при старте.
  • Репозиторий на pgxpool: Create, GetByID, List(limit, offset), Delete, с маппингом ошибок в ErrNotFound и ErrAlreadyExists.
  • Хендлер зависит от интерфейса репозитория; доменные ошибки → 404 и 409, валидация → 400.
  • Middleware: recovery, request ID, логирование; graceful shutdown и таймауты сервера.
  • Тест хендлера через httptest с заглушкой репозитория; go test -race ./... проходит.
  • Проверка руками через curl: создать, получить, список, удалить, получить удалённый (404).

Проверь себя

вопросПочему с базами в Go используют библиотеки поверх database/sql?

Стандартный пакет низкоуровневый. pgx, sqlx, sqlc, squirrel и ORM упрощают работу с запросами, типами и пулом.

вопросЗачем нужен пул соединений и что будет, если забыть rows.Close()?

Соединения дорогие и ограничены, пул переиспользует их между запросами. Без rows.Close() соединение не вернётся в пул, пул забьётся, и новые запросы будут ждать.

вопросКак защититься от SQL-инъекций?

Передавать значения плейсхолдерами ($1, $2) или через query builder, никогда не склеивать SQL со строками от пользователя.

вопросЗачем переводить ошибки БД в доменные?

Чтобы сервисный слой не зависел от конкретной базы и драйвера: он работает с ErrNotFound, а не с pgx.ErrNoRows и кодами PostgreSQL.

вопросПочему «пользователь не найден» — не сбой?

Это ожидаемый сценарий: его обрабатывают отдельной веткой и отвечают 404, а не 500.

вопросКак устроена транзакция в pgx и зачем defer tx.Rollback(ctx)?

Begin → операции через tx → Commit. defer Rollback сразу после Begin откатывает всё при любом выходе с ошибкой, а после Commit ничего не делает.

вопросЗачем передавать context в методы репозитория?

Для таймаутов на запрос и отмены по цепочке: клиент отключился — запрос к базе отменился сам.

вопросЧто такое миграции и чем golang-migrate отличается от goose?

Версионированные изменения схемы с секциями up и down, которые применяются по порядку. В golang-migrate up и down лежат в разных файлах, в goose — в одном; goose умеет и миграции на Go.


Ключевые выводы

  • 🧰 Для PostgreSQL — pgx с pgxpool; рядом sqlx, sqlc, squirrel и ORM — знай плюсы и минусы каждого.
  • 🔑 Строка подключения — секрет: только из окружения, никогда в код и логи.
  • 🏊 Пул переиспользует соединения; defer rows.Close() возвращает соединение в пул.
  • 🛡️ Только плейсхолдеры или query builder — конкатенация SQL открывает инъекции.
  • 🗺️ Репозиторий переводит ошибки БД в доменные, хендлер — доменные в HTTP-статусы.
  • 🔁 Транзакция: Begin → defer Rollback → операции → Commit; деньги — в копейках.
  • 📜 Схема базы версионируется миграциями: golang-migrate или goose.

Chapter goals

  • Choose a tool for database work and justify the choice at an interview.
  • Connect to PostgreSQL through a connection pool and keep the connection string out of the code.
  • Write a CRUD repository on pgx and translate database errors into domain ones.
  • Use context, transactions, and parameterized queries.
  • Maintain the database schema with migrations.

Why a database

Keeping data in memory or in files on disk does not work: restart the service and everything is gone. That is what databases are for — separate applications that provide a contract: how to send and receive data. We cover the "Go service + PostgreSQL" pairing; with MySQL the principles are the same.

Tools

The standard net/http is production-ready for networking (18 — Backend — HTTP server), but databases are different: the standard database/sql package is very low-level, so libraries are always built around it.

Tool What it is Pros Cons
pgx a PostgreSQL driver and client maximum performance, full control more manual code
sqlx an extension of database/sql writing SQL is more convenient you still write SQL by hand
sqlc a Go code generator from SQL queries type safety, speed queries must be written up front
squirrel query builder queries are assembled in code, no string gluing one more dependency
GORM, ent ORM rapid development, less SQL slower, harder to optimize

The industry's attitude to ORMs is ambivalent: raw SQL is easy to read and optimize, while an ORM runs slower and hides the queries. Some teams write ORM, others SQL only. sqlc resembles API-first from the previous chapter: you write SQL first, then Go code is generated from it.

Connecting and the connection string

A database is a sensitive thing: it has users with rights to read, change, and delete within specific schemas. Connecting requires an address, a login, a password, and a database name — together they form the connection string (DSN):

text
postgres://user:password@localhost:5432/goflix

The connection string is a secret. It is not written in code; it is passed through an environment variable such as DATABASE_URL, or in parts: DB_USER, DB_PASSWORD, DB_SSLMODE.

Connection pool

There are many requests to the database — not a billion RPS, but tens and hundreds per second. With a single connection they all queue up. Opening a new one per request does not work either: connections are expensive, their number is limited, and setup takes time. Hence the connection pool: a bounded set of connections reused across requests. In pgx it is managed by pgxpool: it hands out a free connection and takes it back after use.

100 requestspool: 10 connectionsPostgreSQL
go
package main

import (
    "context"
    "fmt"
    "log"
    "os"

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

func main() {
    if err := run(context.Background()); err != nil {
        log.Fatal(err)
    }
    // compile only: needs a running PostgreSQL
}

func run(ctx context.Context) error {
    // DATABASE_URL comes from the environment, not the code
    pool, err := pgxpool.New(ctx, os.Getenv("DATABASE_URL"))
    if err != nil {
        return fmt.Errorf("create pool: %w", err)
    }
    defer pool.Close()

    if err := pool.Ping(ctx); err != nil { // check that the database is reachable
        return fmt.Errorf("ping database: %w", err)
    }
    log.Println("connected to the database")
    return nil
}

Ping sends the database an empty request — like the Test connection button in DBeaver or DataGrip. The logic lives in run while main only prints the error: this way defer pool.Close() is guaranteed to run (14 — defer, panic, recover → recover only works in its own goroutine — why log.Fatal skips defer).

A CRUD repository

CRUD — Create, Read, Update, Delete: the basic data operations. A repository runs SQL and turns table rows into Go structs.

go
package main

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

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

// Domain errors: the service layer knows about them, not about pgx.
var (
    ErrNotFound      = errors.New("not found")
    ErrAlreadyExists = errors.New("already exists")
)

type User struct {
    ID        int64
    FullName  string
    Email     string
    CreatedAt time.Time
}

type PostgresUserRepo struct {
    pool *pgxpool.Pool
}

func NewPostgresUserRepo(pool *pgxpool.Pool) *PostgresUserRepo {
    return &PostgresUserRepo{pool: pool}
}

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

func (r *PostgresUserRepo) GetByID(ctx context.Context, id int64) (User, error) {
    ctx, cancel := context.WithTimeout(ctx, 2*time.Second) // a per-query timeout
    defer cancel()

    var u User
    err := r.pool.QueryRow(ctx,
        "SELECT id, full_name, email, created_at FROM users WHERE id = $1", id,
    ).Scan(&u.ID, &u.FullName, &u.Email, &u.CreatedAt)
    if err != nil {
        return User{}, fmt.Errorf("user %d: %w", id, mapDBError(err))
    }
    return u, nil
}

func (r *PostgresUserRepo) List(ctx context.Context, limit, offset int) ([]User, error) {
    rows, err := r.pool.Query(ctx,
        "SELECT id, full_name, email, created_at FROM users ORDER BY id LIMIT $1 OFFSET $2",
        limit, offset,
    )
    if err != nil {
        return nil, fmt.Errorf("list users: %w", err)
    }
    defer rows.Close() // return the connection to the pool

    users := []User{}
    for rows.Next() {
        var u User
        if err := rows.Scan(&u.ID, &u.FullName, &u.Email, &u.CreatedAt); err != nil {
            return nil, fmt.Errorf("read row: %w", err)
        }
        users = append(users, u)
    }
    return users, rows.Err() // an error that happened during iteration
}

func (r *PostgresUserRepo) Delete(ctx context.Context, id int64) error {
    tag, err := r.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 { // nothing deleted — no such user
        return ErrNotFound
    }
    return nil
}

// mapDBError translates PostgreSQL errors into domain ones.
func mapDBError(err error) error {
    if errors.Is(err, pgx.ErrNoRows) {
        return ErrNotFound
    }
    var pgErr *pgconn.PgError
    if errors.As(err, &pgErr) {
        switch pgErr.Code {
        case "23505": // unique_violation
            return ErrAlreadyExists
        case "23503": // foreign_key_violation
            return ErrNotFound
        }
    }
    return err
}

func main() {
    if err := run(context.Background()); err != nil {
        log.Fatal(err)
    }
    // compile only: needs a running PostgreSQL with a users table
}

func run(ctx context.Context) error {
    pool, err := pgxpool.New(ctx, os.Getenv("DATABASE_URL"))
    if err != nil {
        return fmt.Errorf("create pool: %w", err)
    }
    defer pool.Close()

    repo := NewPostgresUserRepo(pool)
    u, err := repo.Create(ctx, "Alex", "alex@goflix.ru")
    if errors.Is(err, ErrAlreadyExists) {
        log.Println("a user with this email already exists")
        return nil
    }
    if err != nil {
        return err
    }
    log.Printf("created user %d", u.ID)
    return nil
}

Let's walk through each operation:

  • Create — INSERT … RETURNING returns the created row right away, and Scan maps the columns onto struct fields. The repository hands a Go struct upward, and business logic works with it: checks for Premium, mutates things.
  • GetByID — when there is no row, pgx returns pgx.ErrNoRows. "User not found" is not a failure but a normal scenario: Telegram does not crash when a contact is missing. This case is separately translated into ErrNotFound so the handler can answer 404.
  • List — Query returns rows, rows.Next() iterates them, each is Scan-ed into a struct and appended to a slice. defer rows.Close() is mandatory: it returns the connection to the pool. After the loop, check rows.Err() — an error that may have occurred during iteration.
  • Delete — Exec for queries that return no rows. RowsAffected() == 0 means there was nothing to delete — that is ErrNotFound too.

Error mapping

If the repository leaked driver errors upward (pgx.ErrNoRows, PostgreSQL codes), the service layer would learn about the database. Switch to another database and you would have to change it too. So database errors are translated into domain ones right inside the repository: business logic does not know what "rows" are, but it does know "user not found".

  • pgx.ErrNoRows → ErrNotFound — a value check via errors.Is.
  • *pgconn.PgError with code 23505 (unique violation) → ErrAlreadyExists, with code 23503 (foreign key violation) → ErrNotFound — a type check via errors.As.

This is a simplified version of mapDBError from the code above; the techniques come from 13 — Error handling.

Context throughout the application

Every repository method takes context as its first parameter. This gives two things:

  • A timeout per operation. As in GetByID: context.WithTimeout bounds one specific query.
  • Cancellation along the chain. If the handler passes r.Context() into the repository, then when the client disconnects — as during server shutdown in 18 — Backend — HTTP server → Graceful shutdown and timeouts — the heavy PostgreSQL query cancels itself. No extra handling needed.

context runs through the whole application: from main through handlers and services down to the repositories.

Transactions

Sometimes several operations must run together: all or nothing. You cannot jump 99% across a chasm. The classic example is a money transfer: withdraw from one account and deposit into another. If the withdrawal succeeded but the deposit failed, the money simply vanishes.

PostgreSQL has BEGIN, COMMIT, and ROLLBACK for this; in pgx it is the same:

go
package main

import (
    "context"
    "fmt"
    "log"
    "os"

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

type AccountRepo struct {
    pool *pgxpool.Pool
}

// TransferWithTx moves money between accounts in one transaction.
func (r *AccountRepo) TransferWithTx(ctx context.Context, fromID, toID, amountKopecks int64) error {
    tx, err := r.pool.Begin(ctx)
    if err != nil {
        return fmt.Errorf("begin transaction: %w", err)
    }
    defer tx.Rollback(ctx) // rolls everything back if we never reach Commit; a no-op after Commit

    if _, err := tx.Exec(ctx,
        "UPDATE accounts SET balance = balance - $1 WHERE id = $2", amountKopecks, fromID); err != nil {
        return fmt.Errorf("withdraw: %w", err)
    }
    if _, err := tx.Exec(ctx,
        "UPDATE accounts SET balance = balance + $1 WHERE id = $2", amountKopecks, toID); err != nil {
        return fmt.Errorf("deposit: %w", err)
    }
    return tx.Commit(ctx)
}

func main() {
    ctx := context.Background()
    pool, err := pgxpool.New(ctx, os.Getenv("DATABASE_URL"))
    if err != nil {
        log.Fatal(err)
    }
    defer pool.Close()

    repo := &AccountRepo{pool: pool}
    if err := repo.TransferWithTx(ctx, 1, 2, 50_000); err != nil { // 500 rubles
        log.Printf("transfer failed: %v", err)
    }
    // compile only: needs a running PostgreSQL with an accounts table
}
  • defer tx.Rollback(ctx) goes immediately after Begin: on any error return the transaction rolls back, and after a successful Commit the rollback is a no-op.
  • Methods that work transactionally often get the WithTx suffix.
  • When a service uses several repositories — the order in one, the user in another, an event in Kafka — the transaction is threaded through them, or a transaction manager is used. That is an advanced topic: it is enough to know it exists.

Query builder: squirrel

Instead of raw SQL strings, a query can be assembled in code — a query builder. A popular choice is squirrel: the query reads like SQL (Update, Set, Where) but is assembled with method calls, and a typo in one place does not break the whole string. PlaceholderFormat(sq.Dollar) enables $1, $2 placeholders in PostgreSQL format.

go
package main

import (
    "fmt"

    sq "github.com/Masterminds/squirrel"
)

func main() {
    query, args, err := sq.Update("accounts").
        Set("balance", sq.Expr("balance - ?", 500)).
        Where(sq.Eq{"id": 42}).
        PlaceholderFormat(sq.Dollar).
        ToSql()
    if err != nil {
        fmt.Println("error:", err)
        return
    }
    fmt.Println(query)
    fmt.Println(args)

    query, args, err = sq.Select("id", "full_name").From("users").
        Where(sq.Eq{"email": "alex@goflix.ru"}).
        OrderBy("id").Limit(10).
        PlaceholderFormat(sq.Dollar).
        ToSql()
    if err != nil {
        fmt.Println("error:", err)
        return
    }
    fmt.Println(query)
    fmt.Println(args)
}
stdout // program output
UPDATE accounts SET balance = balance - $1 WHERE id = $2
[500 42]
SELECT id, full_name FROM users WHERE email = $1 ORDER BY id LIMIT 10
[alex@goflix.ru]

The finished query and args go into tx.Exec(ctx, query, args...) or pool.Query.

SQL injections and placeholders

Values are always passed separately from the query text — through the $1, $2 placeholders. The database receives the query and the values apart and guarantees that a value can never become part of an SQL expression. Glue the query to a user-supplied string, though, and catastrophe follows — an SQL injection.

go
package main

import "fmt"

func main() {
    // name came from a user
    name := "x' OR '1'='1"
    unsafeQuery := "SELECT id FROM users WHERE full_name = '" + name + "'" // never do this
    fmt.Println(unsafeQuery)

    safeQuery := "SELECT id FROM users WHERE full_name = $1" // the query text never changes
    fmt.Println(safeQuery, "| argument:", name)
}
stdout // program output
SELECT id FROM users WHERE full_name = 'x' OR '1'='1'
SELECT id FROM users WHERE full_name = $1 | argument: x' OR '1'='1

In the first query the condition OR '1'='1' is true for every row — the attacker gets the whole table, and with another string could delete it. In the second query the text is untouched, and the value is just data. That is why the raw SQL examples above contain no injections.

Migrations

Queries need tables, columns, and schemas — and a new service has none yet. You could connect with DBeaver and run CREATE TABLE by hand, then ALTER TABLE ADD COLUMN… But there may be several database instances, and manual edits drift apart quickly. We version code in Git — the database schema needs versioning too.

Migrations are versioned schema changes: each change is a separate file, and the files have an order. At service startup — or as a deploy step — the tool checks a bookkeeping table (e.g. schema_migrations) for which migrations are already applied, then applies the new ones in order.

text
migrations/
├── 001_create_users.sql      ✔ applied
├── 002_add_email.sql         ✔ applied
├── 003_create_orders.sql     ✔ applied
└── 004_add_index.sql         … waiting to be applied

Every migration has two halves: up — change the schema, down — put it back (rollback). Created a table — the down drops it; added a column — the down removes the column.

The two most popular tools are golang-migrate and goose; they differ only slightly.

golang-migrate: up and down live in two separate files — 000001_create_users.up.sql and 000001_create_users.down.sql.

sql
-- 000001_create_users.up.sql
CREATE TABLE users (
    id         BIGSERIAL PRIMARY KEY,
    full_name  VARCHAR(100) NOT NULL,
    email      VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- 000001_create_users.down.sql
DROP TABLE users;

goose: both sections in one file — many people find it easier to read that way. It needs the -- +goose Up and -- +goose Down annotations; for complex statements there are also -- +goose StatementBegin and StatementEnd.

sql
-- 00002_add_phone.sql
-- +goose Up
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- +goose Down
ALTER TABLE users DROP COLUMN phone;

Goose migrations can also be written in Go — handy when you need more than a schema change, e.g. loading a pile of data into the database from files:

go
// migrations/00003_seed_roles.go
package migrations

import (
    "context"
    "database/sql"

    "github.com/pressly/goose/v3"
)

func init() {
    goose.AddMigrationContext(upSeedRoles, downSeedRoles)
}

func upSeedRoles(ctx context.Context, tx *sql.Tx) error {
    for _, role := range []string{"admin", "moderator", "user"} {
        if _, err := tx.ExecContext(ctx,
            "INSERT INTO roles (name) VALUES ($1) ON CONFLICT DO NOTHING", role); err != nil {
            return err
        }
    }
    return nil
}

func downSeedRoles(ctx context.Context, tx *sql.Tx) error {
    _, err := tx.ExecContext(ctx, "DELETE FROM roles WHERE name IN ('admin', 'moderator', 'user')")
    return err
}

Most often migrations are applied automatically at service startup:

go
package main

import (
    "database/sql"
    "fmt"
    "log"
    "os"

    _ "github.com/jackc/pgx/v5/stdlib" // database/sql driver for pgx
    "github.com/pressly/goose/v3"
)

func migrate(dsn string) error {
    db, err := sql.Open("pgx", dsn)
    if err != nil {
        return fmt.Errorf("open database: %w", err)
    }
    defer db.Close()

    if err := goose.SetDialect("postgres"); err != nil {
        return err
    }
    if err := goose.Up(db, "migrations"); err != nil { // applies new migrations in order
        return fmt.Errorf("migrations: %w", err)
    }
    return nil
}

func main() {
    if err := migrate(os.Getenv("DATABASE_URL")); err != nil {
        log.Fatal(err)
    }
    log.Println("database schema is up to date")
    // compile only: needs a running PostgreSQL and a migrations folder
}

There are CLI commands too — apply, roll back, or check the status by hand. They are used rarely: everything usually happens automatically at startup.

bash
goose -dir ./migrations postgres "$DATABASE_URL" status
goose -dir ./migrations postgres "$DATABASE_URL" up
migrate -path ./migrations -database "$DATABASE_URL" up

What they ask at interviews

Unlike Go syntax — how a map works, buckets, why context, how mocks work — databases and web services get almost no "Go-flavored" theory questions. The tasks are mostly practical: review a service's code or talk about how you worked with databases. And the answer must be more than "there was Go and there was a DB".

Summary

  • The connection pool: why connections are reused and how to keep the pool from clogging.
  • Parameterized queries and the consequences of SQL injections.
  • Tools: pgx, sqlx, sqlc, squirrel, ORM.
  • Mapping database errors to domain ones.
  • context in every query.
  • Transactions: Begin, Commit, Rollback.
  • Migrations: versioning the schema.

The material of the last two chapters is enough to write your own backend application. What stayed offscreen is the service layer — that is business logic you write yourself — and infrastructure like Docker.

Practice

Homework: a CRUD service

Write a simple CRUD service — users or tasks: an HTTP server with a couple of endpoints, PostgreSQL, and migrations. After that, any Go backend project will look familiar.

  • Run PostgreSQL in Docker; the password — in an environment variable only:
    bash
    export GOFLIX_DB_PASSWORD='make-up-a-password'
    docker run --name goflix-db -e POSTGRES_PASSWORD="$GOFLIX_DB_PASSWORD" -e POSTGRES_DB=goflix -p 5432:5432 -d postgres:17
    export DATABASE_URL="postgres://postgres:${GOFLIX_DB_PASSWORD}@localhost:5432/goflix?sslmode=disable"
  • go mod init goflix, then go get github.com/jackc/pgx/v5 github.com/pressly/goose/v3. Recent goose versions require Go 1.26+: with an older Go installed, the go command downloads the needed toolchain itself and bumps the version in go.mod — or upgrade Go beforehand: brew upgrade go.
  • Migration 00001_create_movies.sql: a movies table (id BIGSERIAL, title, year, created_at); applied via goose.Up at startup.
  • A repository on pgxpool: Create, GetByID, List(limit, offset), Delete, with error mapping into ErrNotFound and ErrAlreadyExists.
  • The handler depends on the repository interface; domain errors → 404 and 409, validation → 400.
  • Middleware: recovery, request ID, logging; graceful shutdown and server timeouts.
  • A handler test via httptest with a repository stub; go test -race ./... passes.
  • Manual checks with curl: create, get, list, delete, get the deleted one (404).

Check yourself

questionWhy do Go projects use libraries on top of database/sql?

The standard package is low-level. pgx, sqlx, sqlc, squirrel, and ORMs make queries, types, and the pool easier to work with.

questionWhy do you need a connection pool, and what happens if you forget rows.Close()?

Connections are expensive and limited; the pool reuses them across requests. Without rows.Close() the connection is not returned, the pool clogs up, and new requests wait.

questionHow do you protect against SQL injections?

Pass values through placeholders ($1, $2) or a query builder; never glue SQL together with user strings.

questionWhy translate database errors into domain ones?

So the service layer does not depend on a specific database and driver: it works with ErrNotFound, not with pgx.ErrNoRows and PostgreSQL codes.

questionWhy is "user not found" not a failure?

It is an expected scenario: handle it in its own branch and answer 404, not 500.

questionHow does a transaction work in pgx, and why defer tx.Rollback(ctx)?

Begin → operations through tx → Commit. defer Rollback right after Begin rolls everything back on any error exit and does nothing after Commit.

questionWhy pass context into repository methods?

For per-query timeouts and chain cancellation: the client disconnected — the database query cancels itself.

questionWhat are migrations, and how does golang-migrate differ from goose?

Versioned schema changes with up and down sections, applied in order. In golang-migrate up and down live in separate files, in goose in one; goose also supports migrations written in Go.


Key takeaways

  • 🧰 For PostgreSQL — pgx with pgxpool; alongside it sqlx, sqlc, squirrel, and ORMs — know the pros and cons of each.
  • 🔑 The connection string is a secret: from the environment only, never in code or logs.
  • 🏊 The pool reuses connections; defer rows.Close() returns a connection to the pool.
  • 🛡️ Placeholders or a query builder only — SQL concatenation opens the door to injections.
  • 🗺️ The repository translates database errors into domain ones; the handler — domain ones into HTTP statuses.
  • 🔁 A transaction: Begin → defer Rollback → operations → Commit; money — in kopecks.
  • 📜 The database schema is versioned with migrations: golang-migrate or goose.
// related topics and cheatsheets