Часть 7. BackendУрок 19# go# backend# postgresql# sql

Работа с базами данных

pgx и пул соединений, CRUD, транзакции, SQL-инъекции, миграции

Цели главы

  • Выбирать инструмент для работы с БД и объяснять выбор на собеседовании.
  • Подключаться к 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.
// связанные темы и шпаргалки