Модуль 10. Работа с PostgreSQL: основы

Цели модуля

После модуля студент сможет:

• Понимать основы PostgreSQL и реляционных БД
• Выбирать между database/sql и pgx
• Настраивать connection pool
• Выполнять SQL-запросы в Go (SELECT, INSERT, UPDATE, DELETE)
• Использовать prepared statements
• Предотвращать SQL injection
• Реализовывать repository pattern для PostgreSQL

🔥 Инцидент: база жива, но соединения кончились

Вечером сервис статистики начал отвечать pq: sorry, too many clients already. База при этом чувствовала себя прекрасно: CPU 15 %, очередь запросов пустая. Просто все 100 соединений из max_connections были заняты — и сервис не мог получить ни одного нового. Подняли max_connections до 300 — стало легче ровно на два дня, потом то же самое.

Настоящая причина нашлась в трёх хендлерах, где забыли defer rows.Close():

go
rows, err := db.Query(ctx, "SELECT id, total FROM orders WHERE user_id = $1", userID)
if err != nil {
    return nil, err
}
// нет defer rows.Close() — соединение не вернётся в пул
for rows.Next() { /* ... */ }

database/sql возвращает соединение в пул только после закрытия строк; пропущенный Close — это медленная утечка: перезапуск пода «лечит» проблему, пока сервис снова не утечёт. И классика того же дня: один из разработчиков открывал базу через sql.Open прямо в хендлере — новый пул на каждый запрос.

В этом модуле — работа с PostgreSQL по-взрослому: выбор между database/sql и pgx, настройка пула соединений, prepared statements, защита от SQL-инъекций и repository pattern.

10.1 PostgreSQL basics

Что такое PostgreSQL

PostgreSQL - объектно-реляционная СУБД с открытым исходным кодом. Особенности:

• ACID-транзакции
• Строгая типизация
• Поддержка JSON/JSONB
• Полнотекстовый поиск
• Расширяемость (extensions)

Основные концепции

Типы данных PostgreSQL

Создание таблицы

sql
CREATE TABLE repos (
    id UUID PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    owner_id UUID NOT NULL REFERENCES users(id),
    is_private BOOLEAN NOT NULL DEFAULT FALSE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_repos_owner_id ON repos(owner_id);

Индексы

Индексы ускоряют запросы по определённым колонкам:

sql
-- B-tree индекс (по умолчанию)
CREATE INDEX idx_repos_owner_id ON repos(owner_id);

-- Составной индекс
CREATE INDEX idx_repos_owner_created ON repos(owner_id, created_at);

-- Уникальный индекс
CREATE UNIQUE INDEX idx_repos_name_owner ON repos(name, owner_id);

Правила:

• Индексируйте колонки в WHERE, JOIN, ORDER BY
• Составные индексы: порядок колонок важен
• Слишком много индексов замедляет запись

10.2 database/sql vs pgx

database/sql

Стандартная библиотека Go. Абстракция над SQL-драйверами.

go
import "database/sql"
import _ "github.com/lib/pq"  // PostgreSQL драйвер

db, err := sql.Open("postgres", dsn)

Плюсы:

• Встроен в стандартную библиотеку
• Работает с разными БД (PostgreSQL, MySQL, SQLite)
• Стабильный API

Минусы:

• Медленнее pgx (дополнительный слой абстракции)
• Нет поддержки специфичных фич PostgreSQL
• Нет native types для PostgreSQL

pgx

Нативный драйвер PostgreSQL для Go.

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

pool, err := pgxpool.New(ctx, dsn)

Плюсы:

• Быстрее database/sql
• Поддержка специфичных типов PostgreSQL (UUID, JSONB, массивы)
• LISTEN/NOTIFY
• COPY protocol для bulk inserts
• Лучшая обработка ошибок

Минусы:

• Только PostgreSQL
• Внешняя зависимость

Когда что использовать

Для проектов с PostgreSQL рекомендуется pgx.

10.3 Подключение к PostgreSQL

DSN (Data Source Name)

Формат DSN для PostgreSQL:

text
postgres://username:password@host:port/database?sslmode=disable

Пример:

text
postgres://app:secret@localhost:5432/mydb?sslmode=disable

Подключение через pgxpool

go
import (
    "context"
    "fmt"
    "github.com/jackc/pgx/v5/pgxpool"
)

func NewDB(ctx context.Context, dsn string) (*pgxpool.Pool, error) {
    config, err := pgxpool.ParseConfig(dsn)
    if err != nil {
        return nil, fmt.Errorf("parse config: %w", err)
    }

    // Настройка пула
    config.MaxConns = 25
    config.MinConns = 5
    config.MaxConnLifetime = time.Hour
    config.MaxConnIdleTime = 30 * time.Minute

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

    // Проверка соединения
    if err := pool.Ping(ctx); err != nil {
        pool.Close()
        return nil, fmt.Errorf("ping database: %w", err)
    }

    return pool, nil
}

Подключение через database/sql

go
import (
    "database/sql"
    _ "github.com/lib/pq"
)

func NewDB(dsn string) (*sql.DB, error) {
    db, err := sql.Open("postgres", dsn)
    if err != nil {
        return nil, fmt.Errorf("open database: %w", err)
    }

    db.SetMaxOpenConns(25)
    db.SetMaxIdleConns(5)
    db.SetConnMaxLifetime(time.Hour)

    if err := db.Ping(); err != nil {
        db.Close()
        return nil, fmt.Errorf("ping database: %w", err)
    }

    return db, nil

}

Закрытие соединения

Всегда закрывайте пул при завершении приложения:

go
defer pool.Close()  // для pgxpool
defer db.Close()    // для database/sql

10.4 Connection pooling

Зачем нужен connection pool

Создание соединения с БД - дорогая операция:

• TCP handshake
• TLS handshake (если используется)
• Аутентификация
• Инициализация сессии

Connection pool переиспользует соединения, снижая latency.

Параметры пула

Правило для MaxConns

text
MaxConns = (cores * 2) + effective_spindle_count

Для SSD: effective_spindle_count = 1 Для web-сервиса с 4 cores: MaxConns = 4 * 2 + 1 = 9

Обычно 10-25 соединений достаточно для web-сервиса.

ВАЖНО: учитывайте количество инстансов сервиса. Если 3 инстанса по 25 соединений = 75 соединений к БД. Проверьте max_connections в PostgreSQL.

Мониторинг пула

go
// Статистика пула pgxpool
stats := pool.Stat()
fmt.Printf("acquired: %d, idle: %d, total: %d\n",
    stats.AcquiredConns(), stats.IdleConns(), stats.TotalConns())

10.5 SQL-запросы в Go

SELECT: одна строка

go
func (r *RepoRepository) GetByID(ctx context.Context, id string) (*models.Repo, error) {
    query := "SELECT id, name, owner_id, is_private, created_at, updated_at FROM repos WHERE id = $1"

    var repo models.Repo
    err := r.pool.QueryRow(ctx, query, id).Scan(
        &repo.ID,
        &repo.Name,
        &repo.OwnerID,
        &repo.IsPrivate,
        &repo.CreatedAt,
        &repo.UpdatedAt,
    )
    if err != nil {
        if errors.Is(err, pgx.ErrNoRows) {
            return nil, nil  // не найдено
        }
        return nil, fmt.Errorf("query repo: %w", err)
    }
    return &repo, nil
}

SELECT: несколько строк

go
func (r *RepoRepository) List(ctx context.Context, filter RepoFilter) ([]*models.Repo, error) {
    query := "SELECT id, name, owner_id, is_private, created_at, updated_at FROM repos WHERE owner_id = $1 LIMIT $2 OFFSET $3"

    rows, err := r.pool.Query(ctx, query, filter.OwnerID, filter.Limit, filter.Offset)
    if err != nil {
        return nil, fmt.Errorf("query repos: %w", err)
    }
    defer rows.Close()

    var repos []*models.Repo
    for rows.Next() {
        var repo models.Repo
        if err := rows.Scan(
            &repo.ID,
            &repo.Name,
            &repo.OwnerID,
            &repo.IsPrivate,
            &repo.CreatedAt,
            &repo.UpdatedAt,
        ); err != nil {
            return nil, fmt.Errorf("scan repo: %w", err)
        }
        repos = append(repos, &repo)
    }

    if err := rows.Err(); err != nil {
        return nil, fmt.Errorf("rows error: %w", err)
    }

    return repos, nil

}

INSERT

go
func (r *RepoRepository) Create(ctx context.Context, repo *models.Repo) error {
    query := "INSERT INTO repos (id, name, owner_id, is_private, created_at, updated_at) VALUES ($1, $2, $3, $4, $5, $6)"

    _, err := r.pool.Exec(ctx, query,
        repo.ID,
        repo.Name,
        repo.OwnerID,
        repo.IsPrivate,
        repo.CreatedAt,
        repo.UpdatedAt,
    )
    if err != nil {
        return fmt.Errorf("insert repo: %w", err)
    }
    return nil
}

INSERT с возвращаемым значением

go
func (r *RepoRepository) Create(ctx context.Context, repo *models.Repo) error {
    query := "INSERT INTO repos (id, name, owner_id, is_private, created_at, updated_at) VALUES ($1, $2, $3, $4, $5, $6) RETURNING created_at"

    err := r.pool.QueryRow(ctx, query,
        repo.ID,
        repo.Name,
        repo.OwnerID,
        repo.IsPrivate,
        repo.CreatedAt,
        repo.UpdatedAt,
    ).Scan(&repo.CreatedAt)
    if err != nil {
        return fmt.Errorf("insert repo: %w", err)
    }
    return nil

}

UPDATE

go
func (r *RepoRepository) Update(ctx context.Context, repo *models.Repo) error {
    query := "UPDATE repos SET name = $1, is_private = $2, updated_at = NOW() WHERE id = $3"

    result, err := r.pool.Exec(ctx, query, repo.Name, repo.IsPrivate, repo.ID)
    if err != nil {
        return fmt.Errorf("update repo: %w", err)
    }

    if result.RowsAffected() == 0 {
        return ErrRepoNotFound
    }
    return nil
}

DELETE

go
func (r *RepoRepository) Delete(ctx context.Context, id string) error {
    query := "DELETE FROM repos WHERE id = $1"

    result, err := r.pool.Exec(ctx, query, id)
    if err != nil {
        return fmt.Errorf("delete repo: %w", err)
    }

    if result.RowsAffected() == 0 {
        return ErrRepoNotFound
    }
    return nil

}

Параметризованные запросы

В pgx параметры обозначаются как $1, $2, $3 (позиционные).

go
// ПРАВИЛЬНО: параметризованный запрос
query := "SELECT * FROM repos WHERE id = $1 AND owner_id = $2"
rows, err := pool.Query(ctx, query, repoID, ownerID)

// ПЛОХО: конкатенация строк (SQL injection!)
query := "SELECT * FROM repos WHERE id = '" + repoID + "'"

10.6 Prepared statements

Что такое prepared statements

Prepared statement - предкомпилированный запрос на сервере. Полезен при многократном выполнении одного запроса с разными параметрами.

Автоматическая подготовка в pgx

pgx автоматически готовит statements при использовании pool. Явная подготовка не нужна в большинстве случаев.

Явная подготовка (database/sql)

go
stmt, err := db.Prepare("SELECT * FROM repos WHERE id = $1")
if err != nil {
    return err
}
defer stmt.Close()

// Многократное использование
for _, id := range ids {
    row := stmt.QueryRow(id)
    // ...
}

Когда использовать

• Многократное выполнение одного запроса в цикле
• Bulk операции
• Высоконагруженные эндпоинты

Для большинства случаев pgx с автоматической подготовкой достаточно.

10.7 SQL injection prevention

Что такое SQL injection

SQL injection - внедрение SQL-кода через пользовательский ввод.

Пример атаки:

go
// Уязвимый код
query := "SELECT * FROM users WHERE email = '" + email + "'"

// Атакующий вводит: ' OR '1'='1
// Результат: SELECT * FROM users WHERE email = '' OR '1'='1'
// Возвращает всех пользователей!

Защита: параметризованные запросы

go
// БЕЗОПАСНО: параметры экранируются драйвером
query := "SELECT * FROM users WHERE email = $1"
row := pool.QueryRow(ctx, query, email)

Параметры передаются отдельно от SQL-кода. Драйвер экранирует специальные символы. SQL injection невозможен.

Защита: валидация входных данных

go
// Валидация формата перед запросом
func ValidateEmail(email string) error {
    if len(email) > 255 {
        return errors.New("email too long")
    }
    if !emailRegex.MatchString(email) {
        return errors.New("invalid email format")
    }
    return nil
}

Защита: least privilege

Пользователь БД приложения должен иметь минимальные права:

sql
-- Только необходимые права
GRANT SELECT, INSERT, UPDATE, DELETE ON repos TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;

-- НЕ давать:
-- DROP, ALTER, CREATE
-- Доступ к pg_catalog
-- SUPERUSER

Защита: ORM и query builders

ORM (GORM, sqlx) и query builders (squirrel) автоматически экранируют параметры:

go
// squirrel query builder
query := squirrel.Select("*").
    From("repos").
    Where(squirrel.Eq{"id": repoID}).
    PlaceholderFormat(squirrel.Dollar)

sql, args, err := query.ToSql()
rows, err := pool.Query(ctx, sql, args...)

Чеклист защиты от SQL injection

1. Всегда использовать параметризованные запросы ($1, $2)
2. Никогда не конкатенировать пользовательский ввод в SQL
3. Валидировать входные данные
4. Использовать least privilege для пользователя БД
5. Использовать ORM или query builders для сложных запросов
6. Регулярно сканировать код на уязвимости (gosec)

10.8 Repository pattern

Зачем нужен repository pattern

Repository pattern абстрагирует доступ к данным:

• Бизнес-логика не знает о SQL
• Легко менять БД или добавлять кэш
• Удобно тестировать с моками

Интерфейс в internal/repository/

go
package repository

import (
    "context"
    "yourproject/internal/models"
)

type RepoRepository interface {
    Create(ctx context.Context, repo *models.Repo) error
    GetByID(ctx context.Context, id string) (*models.Repo, error)
    Update(ctx context.Context, repo *models.Repo) error
    Delete(ctx context.Context, id string) error
    List(ctx context.Context, filter RepoFilter) ([]*models.Repo, error)
}

Реализация в internal/adapters/postgres/

go
package postgres

import (
    "context"
    "fmt"
    "github.com/jackc/pgx/v5"
    "github.com/jackc/pgx/v5/pgxpool"
    "yourproject/internal/models"
    "yourproject/internal/repository"
)

type RepoRepository struct {
    pool *pgxpool.Pool
}

func NewRepoRepository(pool *pgxpool.Pool) *RepoRepository {
    return &RepoRepository{pool: pool}
}

func (r *RepoRepository) Create(ctx context.Context, repo *models.Repo) error {
    query := "INSERT INTO repos (id, name, owner_id, is_private, created_at, updated_at) VALUES ($1, $2, $3, $4, $5, $6)"

    _, err := r.pool.Exec(ctx, query,
        repo.ID, repo.Name, repo.OwnerID,
        repo.IsPrivate, repo.CreatedAt, repo.UpdatedAt,
    )
    if err != nil {
        return fmt.Errorf("insert repo: %w", err)
    }
    return nil
}

func (r *RepoRepository) GetByID(ctx context.Context, id string) (*models.Repo, error) {
    query := "SELECT id, name, owner_id, is_private, created_at, updated_at FROM repos WHERE id = $1"

    var repo models.Repo
    err := r.pool.QueryRow(ctx, query, id).Scan(
        &repo.ID, &repo.Name, &repo.OwnerID,
        &repo.IsPrivate, &repo.CreatedAt, &repo.UpdatedAt,
    )
    if err != nil {
        if errors.Is(err, pgx.ErrNoRows) {
            return nil, nil
        }
        return nil, fmt.Errorf("query repo: %w", err)
    }
    return &repo, nil
}

// Update, Delete, List реализуются аналогично

Использование в сервисе

go
type RepoService struct {
    repo repository.RepoRepository  // интерфейс, не конкретная реализация
}

func NewRepoService(repo repository.RepoRepository) *RepoService {
    return &RepoService{repo: repo}
}

func (s *RepoService) GetRepo(ctx context.Context, id string) (*models.Repo, error) {
    repo, err := s.repo.GetByID(ctx, id)
    if err != nil {
        return nil, fmt.Errorf("get repo: %w", err)
    }
    if repo == nil {
        return nil, ErrRepoNotFound
    }
    return repo, nil
}

DI: подстановка реализации

go
// internal/app/modules.go
func BuildRepoService(ctx context.Context, cfg *config.Config) (*service.RepoService, error) {
    pool, err := postgres.NewDB(ctx, cfg.PostgresDSN)
    if err != nil {
        return nil, fmt.Errorf("connect to postgres: %w", err)
    }

    repoRepo := postgres.NewRepoRepository(pool)
    return service.NewRepoService(repoRepo), nil
}

Для тестов подставляется mock:

go
func TestRepoService_GetRepo(t *testing.T) {
    mockRepo := new(MockRepoRepository)
    mockRepo.On("GetByID", mock.Anything, "repo-123").
        Return(&models.Repo{ID: "repo-123"}, nil)

    svc := NewRepoService(mockRepo)
    repo, err := svc.GetRepo(context.Background(), "repo-123")

    assert.NoError(t, err)
    assert.Equal(t, "repo-123", repo.ID)

}

Привязка к шаблону проекта

Где PostgreSQL в шаблоне

text
internal/adapters/postgres/
├── repo_repository.go       # Реализация RepoRepository
├── user_repository.go       # Реализация UserRepository
├── outbox_repository.go     # Реализация OutboxRepository
└── connection.go            # Подключение и настройка пула

migrations/
├── 001_init.sql
├── 002_repos.sql
├── 003_users.sql
└── 004_outbox.sql

Что изучаем на примере шаблона

1. Подключение через pgxpool с настройкой пула
2. Реализация repository pattern
3. Параметризованные запросы
4. Обработка pgx.ErrNoRows
5. Транзакции для атомарных операций

Задание: изучите internal/adapters/postgres/

• Как настраивается connection pool?
• Какие методы реализует RepoRepository?
• Как обрабатывается ErrNoRows?
• Есть ли транзакции?

💱 Компромиссы

• database/sql vs pgx: стандартная абстракция позволяет сменить СУБД и не тянет внешних зависимостей, но медленнее и не знает UUID, JSONB и LISTEN/NOTIFY; pgx быстрее и нативен для PostgreSQL, но привязывает проект к одной СУБД.
• Пул побольше vs max_connections: большой MaxConns сглаживает пики, но 3 инстанса по 25 соединений съедают бюджет БД и память на каждую сессию; маленький пул бережёт БД, но запросы встают в очередь под нагрузкой.
• Prepared statements vs ad-hoc запросы: явный db.Prepare экономит parse/plan при выполнении в цикле, но держит состояние на сервере и требует defer stmt.Close(); одноразовые запросы проще, но платят планированием на каждый вызов.
• Индексы vs скорость записи: индекс по колонкам из WHERE и ORDER BY ускоряет чтение, но каждый индекс замедляет INSERT и UPDATE и растёт вместе с данными; совсем без индексов запись дешёвая, но чтение падает в последовательное сканирование.
• Интерфейс репозитория vs прямой pool: интерфейс RepoRepository даёт моки в тестах и замену хранилища, но добавляет слой косвенности; прямой pgxpool.Pool в сервисе короче, но тестировать можно только против реальной БД.

Практическое задание

Задание 1: Подключение к PostgreSQL

Реализуйте подключение с настройкой пула:

go
func NewDB(ctx context.Context, dsn string) (*pgxpool.Pool, error) {
    config, err := pgxpool.ParseConfig(dsn)
    if err != nil {
        return nil, fmt.Errorf("parse config: %w", err)
    }

    config.MaxConns = 25
    config.MinConns = 5
    config.MaxConnLifetime = time.Hour
    config.MaxConnIdleTime = 30 * time.Minute

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

    if err := pool.Ping(ctx); err != nil {
        pool.Close()
        return nil, fmt.Errorf("ping database: %w", err)
    }

    return pool, nil
}

Задание 2: CRUD для Repo

Реализуйте полный CRUD:

go
func (r *RepoRepository) Create(ctx context.Context, repo *models.Repo) error
func (r *RepoRepository) GetByID(ctx context.Context, id string) (*models.Repo, error)
func (r *RepoRepository) Update(ctx context.Context, repo *models.Repo) error
func (r *RepoRepository) Delete(ctx context.Context, id string) error
func (r *RepoRepository) List(ctx context.Context, filter RepoFilter) ([]*models.Repo, error)

Задание 3: Обработка ошибок

Реализуйте обработку pgx.ErrNoRows:

go
if errors.Is(err, pgx.ErrNoRows) {
    return nil, nil  // не найдено, не ошибка
}
return nil, fmt.Errorf("query repo: %w", err)

Задание 4: Параметризованные запросы

Убедитесь что все запросы параметризованы:

go
// ПРАВИЛЬНО
query := "SELECT * FROM repos WHERE owner_id = $1"
rows, err := pool.Query(ctx, query, ownerID)

// ПЛОХО - SQL injection!
query := "SELECT * FROM repos WHERE owner_id = '" + ownerID + "'"

Задание 5: Тесты с testcontainers

Напишите интеграционные тесты с реальной БД:

go
func TestRepoRepository_Create(t *testing.T) {
    ctx := context.Background()

    // Запуск PostgreSQL в Docker
    pgContainer, err := postgres.Run(ctx,
        "postgres:16-alpine",
        postgres.WithDatabase("testdb"),
        postgres.WithUsername("test"),
        postgres.WithPassword("test"),
    )
    require.NoError(t, err)
    defer pgContainer.Terminate(ctx)

    // Получение DSN
    dsn, err := pgContainer.ConnectionString(ctx)
    require.NoError(t, err)

    // Подключение
    pool, err := pgxpool.New(ctx, dsn)
    require.NoError(t, err)
    defer pool.Close()

    // Применение миграций
    // ...

    // Тест
    repo := models.NewRepo("test", "user-1", false)
    repoRepo := postgres.NewRepoRepository(pool)

    err = repoRepo.Create(ctx, repo)
    assert.NoError(t, err)

    // Проверка
    got, err := repoRepo.GetByID(ctx, repo.ID)
    assert.NoError(t, err)
    assert.Equal(t, repo.Name, got.Name)
}

Задание 6: Миграции

Напишите миграцию для таблицы repos:

sql
-- +goose Up
CREATE TABLE repos (
    id UUID PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    owner_id UUID NOT NULL REFERENCES users(id),
    is_private BOOLEAN NOT NULL DEFAULT FALSE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_repos_owner_id ON repos(owner_id);

-- +goose Down
DROP TABLE repos;

Quiz "Проверь себя"

err := row.Scan(&id)

✅ Итоги модуля

Студент должен:

• Понимать основы PostgreSQL и реляционных БД
• Выбирать между database/sql и pgx
• Настраивать connection pool
• Выполнять CRUD-операции через pgx
• Использовать параметризованные запросы
• Предотвращать SQL injection
• Реализовывать repository pattern
• Писать интеграционные тесты с testcontainers
• Ответить на все вопросы Quiz

🔗 Мостик к Модулю 11

Следующий модуль: Модуль 11. Миграции и транзакции - goose/golang-migrate, версионирование схемы, транзакции в PostgreSQL, уровни изоляции, outbox pattern. Привязка к migrations/ шаблона.