Веха 3: переносим задачи в PostgreSQL

Это сделано намеренно. Написать четвёртую копию задачника значило бы завести ещё один источник того же знания, который однажды разойдётся с остальными. Вместо этого веха 3 - первый шаг внутрь финального проекта: ты видишь его слой работы с базой раньше, чем всё остальное.

Схема живёт в SQL, а не в Go

CREATE TABLE IF NOT EXISTS todos (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    title VARCHAR(200) NOT NULL,
    description TEXT,
    completed BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- А этот индекс нужен: PostgreSQL не создаёт индекс под внешний ключ сам.
-- Без него выборка задач пользователя идёт последовательным чтением таблицы,
-- и удаление пользователя проверяет каждую строку todos.
CREATE INDEX IF NOT EXISTS idx_todos_user_id ON todos(user_id);

Разберём три решения в этих двадцати строках.

REFERENCES users(id) ON DELETE CASCADE. Удалили пользователя - его задачи уходят вместе с ним. Без CASCADE база откажется удалять пользователя, у которого есть задачи, и придётся чистить руками в правильном порядке. Хуже - если внешнего ключа нет вовсе: тогда задачи остаются висеть с user_id, которого больше не существует, и обнаруживается это через полгода.

CREATE INDEX idx_todos_user_id. Под внешний ключ PostgreSQL индекс не создаёт автоматически - вопреки распространённому ожиданию. А запрос WHERE user_id = $1 мы делаем на каждый список задач. Без индекса это полный проход по таблице.

Никакого CREATE INDEX по users(email). Там стоит UNIQUE, а UNIQUE в PostgreSQL уже создаёт уникальный индекс. Второй индекс поверх дублировал бы его и удваивал работу на каждой вставке, ничего не ускоряя.

Как миграции применяются - через go:embed и чтение каталога:

// Package migrations хранит схему базы и отдаёт её как embed.FS.
//
// Зачем пакет ради двух SQL-файлов: чтобы у схемы был ровно один источник.
// Раньше те же CREATE TABLE лежали и здесь, и строками внутри Migrate() в Go.
// Две копии одного знания расходятся молча - меняешь одну, вторая остаётся
// правильной на вид и неправильной по факту. Теперь Go-код читает эти файлы,
// а не повторяет их.
//
// `go:embed` не умеет подниматься выше своего каталога, поэтому переменная
// объявлена рядом с самими .sql, а не в пакете database.
package migrations

import "embed"

// FS содержит все SQL-файлы каталога. Порядок применения задаётся именами:
// 001_, 002_ и так далее, поэтому сортировка по имени и есть порядок миграций.
//
//go:embed *.sql
var FS embed.FS

Каталог встраивается в бинарник на этапе сборки. Благодаря этому готовая программа не зависит от того, лежат ли рядом с ней .sql-файлы: миграции уезжают на сервер внутри неё.

Репозиторий вместо map

package repository

import (
	"context"
	"database/sql"
	"errors"
	"fmt"

	"todo-api/internal/models"
)

var ErrTodoNotFound = errors.New("todo not found")

type TodoRepository struct {
	db *sql.DB
}

func NewTodoRepository(db *sql.DB) *TodoRepository {
	return &TodoRepository{db: db}
}

func (r *TodoRepository) Create(ctx context.Context, todo *models.Todo) error {
	query := `
        INSERT INTO todos (user_id, title, description)
        VALUES ($1, $2, $3)
        RETURNING id, created_at, updated_at
    `

	err := r.db.QueryRowContext(
		ctx,
		query,
		todo.UserID,
		todo.Title,
		todo.Description,
	).Scan(&todo.ID, &todo.CreatedAt, &todo.UpdatedAt)

	if err != nil {
		return fmt.Errorf("create todo: %w", err)
	}

	return nil
}

// FindByID возвращает одну задачу конкретного пользователя.
//
// user_id в условии - не украшение: без него любой авторизованный смог бы
// читать чужие задачи, подставив чужой id. Проверка владельца делается
// в самом запросе, а не после выборки, потому что «забыть сравнить»
// в коде проще, чем забыть условие в SQL.
func (r *TodoRepository) FindByID(ctx context.Context, id, userID int) (*models.Todo, error) {
	todo := &models.Todo{}

	query := `
        SELECT id, user_id, title, description, completed, created_at, updated_at
        FROM todos
        WHERE id = $1 AND user_id = $2
    `

	err := r.db.QueryRowContext(ctx, query, id, userID).Scan(
		&todo.ID,
		&todo.UserID,
		&todo.Title,
		&todo.Description,
		&todo.Completed,
		&todo.CreatedAt,
		&todo.UpdatedAt,
	)

	if errors.Is(err, sql.ErrNoRows) {
		return nil, ErrTodoNotFound
	}
	if err != nil {
		return nil, fmt.Errorf("find todo by id: %w", err)
	}

	return todo, nil
}

func (r *TodoRepository) FindByUserID(ctx context.Context, userID int) ([]*models.Todo, error) {
	query := `
        SELECT id, user_id, title, description, completed, created_at, updated_at
        FROM todos
        WHERE user_id = $1
        ORDER BY created_at DESC
    `

	rows, err := r.db.QueryContext(ctx, query, userID)
	if err != nil {
		return nil, fmt.Errorf("find todos: %w", err)
	}
	defer rows.Close()

	// Инициализируем пустым срезом, а не nil: nil-срез сериализуется в `null`,
	// и клиенту приходится обрабатывать два вида «пусто». Пустой даёт `[]`.
	todos := []*models.Todo{}
	for rows.Next() {
		todo := &models.Todo{}
		err := rows.Scan(
			&todo.ID,
			&todo.UserID,
			&todo.Title,
			&todo.Description,
			&todo.Completed,
			&todo.CreatedAt,
			&todo.UpdatedAt,
		)
		if err != nil {
			return nil, fmt.Errorf("scan todo: %w", err)
		}
		todos = append(todos, todo)
	}

	// rows.Err() обязателен: цикл заканчивается и при ошибке чтения,
	// и без этой проверки половина строк молча превратится в успешный ответ.
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate todos: %w", err)
	}

	return todos, nil
}

func (r *TodoRepository) Update(ctx context.Context, todo *models.Todo) error {
	query := `
        UPDATE todos
        SET title = $1, description = $2, completed = $3, updated_at = CURRENT_TIMESTAMP
        WHERE id = $4 AND user_id = $5
    `

	result, err := r.db.ExecContext(
		ctx,
		query,
		todo.Title,
		todo.Description,
		todo.Completed,
		todo.ID,
		todo.UserID,
	)
	if err != nil {
		return fmt.Errorf("update todo: %w", err)
	}

	rows, err := result.RowsAffected()
	if err != nil {
		return fmt.Errorf("rows affected: %w", err)
	}

	if rows == 0 {
		return ErrTodoNotFound
	}

	return nil
}

func (r *TodoRepository) Delete(ctx context.Context, id, userID int) error {
	query := `DELETE FROM todos WHERE id = $1 AND user_id = $2`

	result, err := r.db.ExecContext(ctx, query, id, userID)
	if err != nil {
		return fmt.Errorf("delete todo: %w", err)
	}

	rows, err := result.RowsAffected()
	if err != nil {
		return fmt.Errorf("rows affected: %w", err)
	}

	if rows == 0 {
		return ErrTodoNotFound
	}

	return nil
}

Сравни это с Store из вехи 2. Методы называются иначе, но роль та же: Create, FindByID, список, обновление, удаление. Именно поэтому обработчики менять почти не пришлось.

Три вещи, которых в вехе 2 быть не могло.

RETURNING вместо второго запроса

INSERT INTO todos (user_id, title, description)
VALUES ($1, $2, $3)
RETURNING id, created_at, updated_at

База сама проставляет id и временные метки. Наивный путь - вставить, потом отдельным SELECT прочитать, что получилось: два обращения вместо одного и гонка между ними. RETURNING отдаёт значения той же командой.

$1, а не склейка строк

r.db.QueryRowContext(ctx, query, todo.UserID, todo.Title, todo.Description)

Значения передаются отдельно от текста запроса. База получает шаблон и данные по раздельности, поэтому строка '; DROP TABLE todos; -- в названии задачи остаётся просто строкой. Это единственная надёжная защита от SQL-инъекции - не экранирование, а разделение кода и данных.

user_id в условии, а не после выборки

SELECT ... FROM todos WHERE id = $1 AND user_id = $2

Проверка владельца стоит в самом запросе. Можно было бы выбрать задачу по id и сравнить todo.UserID в коде - но забыть такую проверку в одной из десяти функций легко, а забыть условие в SQL сложнее: без него запрос просто не напишешь осмысленно.

Ошибка «любой авторизованный читает чужие данные, подставив чужой id» - одна из самых частых в самописных API и одна из самых дорогих.

Ошибки переводятся на язык предметной области

if errors.Is(err, sql.ErrNoRows) {
	return nil, ErrTodoNotFound
}

sql.ErrNoRows - деталь работы с базой. Наружу отдаётся ErrTodoNotFound, и обработчик, который мы написали в вехе 2, продолжает работать без правок: он и раньше сравнивал ошибку через errors.Is со своей доменной ошибкой.

Слой базы не протекает в HTTP-слой - в этом и был смысл разделения.

Подключение и пул

package database

import (
	"context"
	"database/sql"
	"fmt"
	"io/fs"
	"sort"
	"time"

	_ "github.com/lib/pq"

	"todo-api/migrations"
)

type DB struct {
	*sql.DB
}

// New подключается к базе по строке из конфигурации.
//
// Принимаем готовый DSN, а не пять отдельных параметров: тогда невозможно
// случайно подключиться не туда, потому что подключаться просто некуда,
// кроме как по тому, что пришло из окружения. Логин и пароль в сигнатуре
// функции - это приглашение подставить их прямо в вызове и забыть.
//
// В строке подключения для локальной разработки будет `sslmode=disable`.
// Для прода это надо менять: см. чеклист в конце урока.
func New(ctx context.Context, dsn string) (*DB, error) {
	db, err := sql.Open("postgres", dsn)
	if err != nil {
		return nil, fmt.Errorf("open database: %w", err)
	}

	// sql.Open не подключается - он лишь готовит пул, поэтому первая реальная
	// проверка связи это Ping. И если он не прошёл, пул надо закрыть: за ним
	// стоят горутина обслуживания соединений и захваченные ресурсы, а вернув
	// одну ошибку, мы теряем на них последнюю ссылку.
	//
	// PingContext, а не Ping: без контекста вызов ждёт столько, сколько решит
	// драйвер, и запуск сервиса зависает на недоступной базе вместо того,
	// чтобы честно упасть.
	pingCtx, cancel := context.WithTimeout(ctx, 5*time.Second)
	defer cancel()

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

	// Настройка пула соединений
	db.SetMaxOpenConns(25)
	db.SetMaxIdleConns(5)
	db.SetConnMaxLifetime(5 * time.Minute)

	return &DB{db}, nil
}

// Migrate применяет SQL-файлы из каталога migrations по порядку имён.
//
// Раньше схема была продублирована здесь строками Go, и файлы рядом лежали
// для вида. Копия документации всегда отстаёт от кода - с копией схемы то же
// самое, только чинить дороже: расхождение вылезает на проде при первом
// развёртывании с нуля.
//
// Настоящей системы миграций тут нет намеренно: нет таблицы с историей,
// нет отката. Для учебного проекта достаточно `IF NOT EXISTS` в самих файлах,
// а на реальном проекте берут goose, migrate или Atlas.
func (db *DB) Migrate() error {
	files, err := fs.Glob(migrations.FS, "*.sql")
	if err != nil {
		return fmt.Errorf("list migrations: %w", err)
	}
	if len(files) == 0 {
		return fmt.Errorf("no migrations found: каталог migrations пуст")
	}

	// Имена начинаются с номера, поэтому сортировка по имени и есть порядок.
	sort.Strings(files)

	for _, name := range files {
		query, err := fs.ReadFile(migrations.FS, name)
		if err != nil {
			return fmt.Errorf("read %s: %w", name, err)
		}
		if _, err := db.Exec(string(query)); err != nil {
			return fmt.Errorf("apply %s: %w", name, err)
		}
	}

	return nil
}

sql.Open ничего не открывает

Вопреки названию, sql.Open только готовит пул и не проверяет подключение. Ошибка в пароле или адресе выяснится при первом запросе - то есть у пользователя, а не при старте. Поэтому сразу после Open идёт PingContext: сервис должен падать на старте, если базы нет.

Ограничения пула

db.SetMaxOpenConns(25)
db.SetMaxIdleConns(25)
db.SetConnMaxLifetime(5 * time.Minute)

По умолчанию число соединений не ограничено. Под нагрузкой Go откроет столько, сколько потребуется, и упрётся в max_connections PostgreSQL - после чего база начнёт отказывать всем клиентам, включая другие сервисы.

ConnMaxLifetime нужен по другой причине: соединения, живущие вечно, не переживают переключение базы на реплику и перезапуск - они остаются в пуле мёртвыми.

Проверяем

Поднимаем базу:

docker run --rm -d --name todo-pg \
  -e POSTGRES_PASSWORD=postgres \
  -e POSTGRES_DB=todo \
  -p 5432:5432 postgres:16-alpine

Запускаем сервис и проверяем, что данные переживают перезапуск:

curl -s -X POST localhost:8080/api/todos \
  -H "Authorization: Bearer $TOKEN" \
  -d '{"title":"Купить хлеб"}'

# перезапускаем процесс и снова читаем список
curl -s localhost:8080/api/todos -H "Authorization: Bearer $TOKEN"

Задача на месте - в вехе 2 её бы уже не было.

docker exec -it todo-pg psql -U postgres -d todo -c '\d todos'