Базы данных/Средний ~ 70 мин

SQL и реляционная модель

Реляционная база данных хранит данные в виде отношений. В повседневной разработке мы почти всегда говорим "таблица", "строка" и "колонка", но полезно помнить исходную идею:

Реляционный термин	Практический термин	Смысл
Relation	Таблица	Набор строк одинаковой структуры
Tuple	Строка	Один факт или один экземпляр сущности
Attribute	Колонка	Именованное свойство строки
Domain	Тип и допустимые значения	Например, `integer`, `text`, `timestamp with time zone`
Candidate key	Возможный ключ	Набор колонок, уникально определяющий строку
Primary key	Первичный ключ	Выбранный основной идентификатор строки
Foreign key	Внешний ключ	Ссылка на ключ в другой таблице

Главная сила SQL не в том, что он "похож на английский". SQL декларативный: вы описываете, какой результат нужен, а оптимизатор решает, как его получить. Для backend-разработчика это меняет мышление: хороший SQL часто короче и быстрее цикла в Go, потому что база умеет фильтровать, соединять, группировать и сортировать рядом с данными.

Go handler
  |
  |  SQL: "дай оплаченные заказы пользователя с суммой"
  v
PostgreSQL optimizer -> plan -> indexes/scans/joins/aggregates

В этом уроке примеры ориентированы на PostgreSQL, но базовые идеи SQL и реляционной модели применимы к большинству реляционных СУБД.

Таблица как набор фактов

Таблица должна описывать один тип фактов. Например, users хранит пользователей, orders хранит заказы, order_items хранит строки заказа.

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

CREATE TABLE orders (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id     bigint NOT NULL REFERENCES users(id),
    status      text NOT NULL,
    total_cents integer NOT NULL CHECK (total_cents >= 0),
    created_at  timestamptz NOT NULL DEFAULT now()
);

Здесь уже есть несколько важных решений:

users.id и orders.id - surrogate keys, технические идентификаторы;
users.email - уникальный бизнес-атрибут, но не primary key;
orders.user_id - внешний ключ на владельца заказа;
status пока text, но ограничение допустимых статусов стоит добавить;
total_cents хранится целым числом, чтобы не ловить ошибки округления денег в float;
created_at хранится как timestamptz, чтобы момент времени был однозначным.

В Go-коде похожая модель обычно превращается в repository-методы:

type Order struct {
    ID         int64
    UserID     int64
    Status     string
    TotalCents int
    CreatedAt  time.Time
}

Но база данных не просто "сериализует struct". Она проверяет инварианты, поддерживает связи, выполняет конкурентный доступ и строит планы запросов.

Схема, ключи и связи

Ключи

Ключ - это не обязательно одна колонка id. Ключ - это минимальный набор атрибутов, который уникально определяет строку.

Вид ключа	Пример	Для чего нужен
Primary key	`users(id)`	Главный идентификатор строки
Unique key	`users(email)`	Бизнес-уникальность
Composite key	`(order_id, product_id)`	Уникальность пары или группы колонок
Foreign key	`orders(user_id) -> users(id)`	Ссылочная целостность
Natural key	`country_code`, `email`, `tax_id`	Идентификатор из предметной области
Surrogate key	`bigint identity`, `uuid`	Технический идентификатор

Composite key часто нужен в таблицах связей:

CREATE TABLE course_enrollments (
    course_id bigint NOT NULL REFERENCES courses(id),
    user_id   bigint NOT NULL REFERENCES users(id),
    joined_at timestamptz NOT NULL DEFAULT now(),

    PRIMARY KEY (course_id, user_id)
);

Так база сама запретит дважды записать одного пользователя на один курс. Если вместо этого добавить отдельный id, но забыть UNIQUE (course_id, user_id), вы получите дубли и будете чинить их уже в production.

Constraints: правила рядом с данными

Ограничения нужны, чтобы база не принимала невозможные состояния.

Constraint	Пример	Что защищает
`NOT NULL`	`email text NOT NULL`	Поле обязательно
`UNIQUE`	`UNIQUE (email)`	Значение или комбинация значений уникальна
`PRIMARY KEY`	`PRIMARY KEY (id)`	`NOT NULL` + уникальность + идентификатор
`FOREIGN KEY`	`REFERENCES users(id)`	Ссылка указывает на существующую строку
`CHECK`	`CHECK (price_cents >= 0)`	Простое бизнес-условие
`DEFAULT`	`created_at DEFAULT now()`	Значение по умолчанию

Пример более строгой таблицы заказов:

CREATE TABLE orders (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id     bigint NOT NULL REFERENCES users(id),
    status      text NOT NULL CHECK (status IN ('draft', 'paid', 'cancelled')),
    total_cents integer NOT NULL CHECK (total_cents >= 0),
    created_at  timestamptz NOT NULL DEFAULT now(),
    paid_at     timestamptz,

    CHECK (
        (status = 'paid' AND paid_at IS NOT NULL)
        OR
        (status <> 'paid' AND paid_at IS NULL)
    )
);

Не всё нужно класть в constraints. Сложное доменное правило лучше жить в доменном слое. Но простые инварианты вроде "цена не отрицательная", "email уникален", "заказ ссылается на существующего пользователя" должны быть в базе. Иначе один забытый handler, cron job или SQL-скрипт сможет испортить данные.

Связи между таблицами

One-to-many

Один пользователь имеет много заказов:

CREATE TABLE orders (
    id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id bigint NOT NULL REFERENCES users(id)
);

Внешний ключ лежит на стороне "many".

Many-to-many

Пользователь может быть записан на много курсов, курс содержит много пользователей. Нужна таблица связей:

CREATE TABLE enrollments (
    user_id   bigint NOT NULL REFERENCES users(id),
    course_id bigint NOT NULL REFERENCES courses(id),
    role      text NOT NULL DEFAULT 'student',
    joined_at timestamptz NOT NULL DEFAULT now(),

    PRIMARY KEY (user_id, course_id)
);

One-to-one

Один пользователь имеет один профиль:

CREATE TABLE user_profiles (
    user_id bigint PRIMARY KEY REFERENCES users(id),
    bio     text,
    avatar_url text
);

PRIMARY KEY (user_id) одновременно делает связь уникальной и хранит ссылку на users.

SQL-запросы

DDL, DML, DCL, TCL

SQL-команды удобно группировать по назначению:

Группа	Что делает	Примеры
DDL	Меняет структуру базы	`CREATE TABLE`, `ALTER TABLE`, `DROP INDEX`
DML	Работает с данными	`SELECT`, `INSERT`, `UPDATE`, `DELETE`, `MERGE`
DCL	Управляет доступом	`GRANT`, `REVOKE`
TCL	Управляет транзакциями	`BEGIN`, `COMMIT`, `ROLLBACK`, `SAVEPOINT`

Для backend-разработчика важно понимать цену DDL. ALTER TABLE может брать сильные блокировки, перестраивать таблицу или долго валидировать constraint. Поэтому миграции проектируют отдельно от бизнес-кода: маленькими шагами, с учетом rollback, совместимости старой и новой версии приложения.

SELECT: порядок мышления

SQL-запрос читается сверху вниз, но логически обрабатывается иначе:

SELECT user_id, count(*) AS paid_orders
FROM orders
WHERE status = 'paid'
GROUP BY user_id
HAVING count(*) >= 3
ORDER BY paid_orders DESC
LIMIT 10;

Упрощенный логический порядок:

FROM - откуда берем строки.
JOIN - как соединяем источники.
WHERE - какие строки оставляем до группировки.
GROUP BY - как собираем группы.
HAVING - какие группы оставляем после агрегации.
SELECT - какие выражения выводим.
ORDER BY - как сортируем.
LIMIT/OFFSET - какую часть результата возвращаем.

Из этого следуют частые ошибки:

агрегаты нельзя писать в WHERE, для них нужен HAVING;
alias из SELECT обычно недоступен в WHERE, потому что WHERE логически раньше;
LIMIT без стабильного ORDER BY не гарантирует повторяемый порядок;
COUNT(*) считает строки, COUNT(column) считает только строки, где column IS NOT NULL.

JOIN

JOIN соединяет строки из нескольких источников.

SELECT
    o.id,
    o.status,
    u.email
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'paid';

Основные виды:

JOIN	Что возвращает
`INNER JOIN` или `JOIN`	Только строки, где есть совпадение с обеих сторон
`LEFT JOIN`	Все строки слева и совпадения справа, иначе `NULL`
`RIGHT JOIN`	Все строки справа и совпадения слева
`FULL JOIN`	Все строки с обеих сторон
`CROSS JOIN`	Декартово произведение

LEFT JOIN полезен для поиска отсутствующих связей:

SELECT u.id, u.email
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;

Этот запрос находит пользователей без заказов. Важно: условие на правую таблицу в WHERE может случайно превратить LEFT JOIN в INNER JOIN.

-- Ошибка: пользователи без заказов исчезнут, потому что o.status = 'paid'
-- для NULL-строки не true.
SELECT u.id, u.email
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.status = 'paid';

-- Правильнее, если нужны все пользователи и только paid-заказы справа.
SELECT u.id, u.email, o.id AS paid_order_id
FROM users u
LEFT JOIN orders o
    ON o.user_id = u.id AND o.status = 'paid';

GROUP BY и HAVING

GROUP BY превращает строки в группы. После группировки можно выбирать:

колонки из GROUP BY;
агрегатные выражения вроде count(*), sum(total_cents), max(created_at);
выражения от этих значений.

SELECT
    user_id,
    count(*) AS orders_count,
    sum(total_cents) AS revenue_cents
FROM orders
WHERE status = 'paid'
GROUP BY user_id
HAVING sum(total_cents) > 100000
ORDER BY revenue_cents DESC;

WHERE фильтрует строки до группировки. HAVING фильтрует группы после группировки.

Типичный вопрос на собеседовании:

Найти пользователей, у которых больше трех оплаченных заказов.

SELECT u.id, u.email, count(o.id) AS paid_orders
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.status = 'paid'
GROUP BY u.id, u.email
HAVING count(o.id) > 3;

CTE: WITH-запросы

Common Table Expression помогает дать имя подзапросу и сделать большой запрос читаемым.

WITH paid_orders AS (
    SELECT id, user_id, total_cents
    FROM orders
    WHERE status = 'paid'
),
user_revenue AS (
    SELECT user_id, sum(total_cents) AS revenue_cents
    FROM paid_orders
    GROUP BY user_id
)
SELECT u.email, ur.revenue_cents
FROM user_revenue ur
JOIN users u ON u.id = ur.user_id
WHERE ur.revenue_cents >= 100000
ORDER BY ur.revenue_cents DESC;

CTE - не "временная таблица" в общем смысле. В современных версиях PostgreSQL оптимизатор может встроить CTE в основной план, если это выгодно и семантически допустимо. Но CTE все равно стоит использовать прежде всего для ясности: этапы запроса получают имена, а бизнес-логика становится проверяемой.

Рекурсивные CTE решают задачи с деревьями:

WITH RECURSIVE category_tree AS (
    SELECT id, parent_id, name, 1 AS depth
    FROM categories
    WHERE id = $1

    UNION ALL

    SELECT c.id, c.parent_id, c.name, ct.depth + 1
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT *
FROM category_tree
ORDER BY depth, name;

Views

View - сохраненный запрос, к которому можно обращаться как к таблице.

CREATE VIEW paid_order_summary AS
SELECT
    o.id,
    o.user_id,
    u.email,
    o.total_cents,
    o.created_at
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'paid';

Теперь:

SELECT *
FROM paid_order_summary
WHERE user_id = $1;

View полезен, когда:

несколько сервисов или отчетов используют один и тот же SQL;
нужно скрыть сложность join'ов;
нужно дать read-only доступ к части данных;
нужна стабильная "read model" поверх нормализованных таблиц.

Обычный view не хранит результат. Materialized view хранит результат физически и требует обновления:

REFRESH MATERIALIZED VIEW course_stats;

Materialized view ускоряет тяжелые отчеты, но добавляет вопрос свежести данных.

Window functions

Агрегат с GROUP BY схлопывает несколько строк в одну. Window function считает значение по окну, но сохраняет строки.

SELECT
    id,
    user_id,
    total_cents,
    created_at,
    row_number() OVER (
        PARTITION BY user_id
        ORDER BY created_at DESC
    ) AS order_no_for_user
FROM orders;

Что происходит:

PARTITION BY user_id делит строки на окна по пользователю;
ORDER BY created_at DESC задает порядок внутри окна;
row_number() нумерует строки внутри каждого окна.

Найти последний заказ каждого пользователя:

WITH ranked AS (
    SELECT
        o.*,
        row_number() OVER (
            PARTITION BY user_id
            ORDER BY created_at DESC, id DESC
        ) AS rn
    FROM orders o
)
SELECT *
FROM ranked
WHERE rn = 1;

Полезные функции:

Функция	Для чего
`row_number()`	Уникальный номер строки внутри окна
`rank()`	Ранг с пропусками при равенстве
`dense_rank()`	Ранг без пропусков
`lag()`	Значение из предыдущей строки
`lead()`	Значение из следующей строки
`sum(...) OVER (...)`	Накопительная сумма или сумма по окну

Пример read skew в аналитике часто проще увидеть через lag:

SELECT
    user_id,
    created_at,
    total_cents,
    total_cents - lag(total_cents) OVER (
        PARTITION BY user_id ORDER BY created_at
    ) AS diff_from_previous
FROM orders
WHERE status = 'paid';

NULL и three-valued logic

NULL означает "значение отсутствует или неизвестно". Это не пустая строка, не ноль и не false.

SQL использует трехзначную логику:

Выражение	Результат
`NULL = NULL`	`UNKNOWN`
`NULL <> 1`	`UNKNOWN`
`1 = 1`	`TRUE`
`1 = 2`	`FALSE`
`email IS NULL`	`TRUE` или `FALSE`
`email IS NOT NULL`	`TRUE` или `FALSE`

WHERE оставляет только строки, где условие TRUE. FALSE и UNKNOWN отбрасываются.

-- Не найдет строки с NULL.
SELECT *
FROM users
WHERE deleted_at = NULL;

-- Правильно.
SELECT *
FROM users
WHERE deleted_at IS NULL;

Опасный пример:

SELECT *
FROM users
WHERE id NOT IN (SELECT banned_user_id FROM bans);

Если подзапрос вернет хотя бы один NULL, выражение NOT IN может дать неожиданный результат. Часто надежнее писать NOT EXISTS:

SELECT *
FROM users u
WHERE NOT EXISTS (
    SELECT 1
    FROM bans b
    WHERE b.banned_user_id = u.id
);

В Go NULL нужно моделировать явно: sql.NullString, sql.NullTime, pointer-типы или типы из драйвера. Не превращайте NULL в пустую строку без осознанного решения, иначе потеряете различие между "нет значения" и "значение пустое".

INSERT, UPDATE, DELETE, RETURNING

PostgreSQL умеет возвращать измененные строки через RETURNING, что очень удобно для Go backend.

INSERT INTO users (email, name)
VALUES ($1, $2)
RETURNING id, email, name, created_at;

Обновление с проверкой владельца:

UPDATE orders
SET status = 'cancelled'
WHERE id = $1
  AND user_id = $2
  AND status = 'draft'
RETURNING id, status;

Если RETURNING не вернул строку, возможны разные причины: заказа нет, он чужой или статус уже не draft. В API это лучше маппить аккуратно, а не всегда отвечать 500.

Upsert:

INSERT INTO user_settings (user_id, theme, updated_at)
VALUES ($1, $2, now())
ON CONFLICT (user_id)
DO UPDATE SET
    theme = EXCLUDED.theme,
    updated_at = now()
RETURNING *;

ON CONFLICT должен опираться на unique или primary key constraint.

SQL как контракт сервиса

SQL-запрос в backend - это часть контракта между приложением и базой. Он фиксирует не только "откуда взять данные", но и:

какие поля приложение действительно читает;
какие инварианты проверяются в WHERE;
какие ошибки базы считаются ожидаемыми;
какие строки пользователь вообще имеет право увидеть или изменить;
какие условия гонки должны приводить к повтору, конфликту или отказу.

Поэтому SELECT * опасен даже без SQL injection: он делает API зависимым от всей формы таблицы. Добавили колонку, изменили порядок scan'а, протащили лишние персональные данные в лог - и запрос внезапно стал частью проблемы. В production-коде лучше явно перечислять поля, которые нужны конкретному use case.

Пример контракта чтения:

SELECT id, status, total_cents, created_at
FROM orders
WHERE id = $1
  AND user_id = $2;

Здесь user_id = $2 - не косметика, а часть access control. Если запрос вернул ноль строк, API может намеренно ответить 404, не раскрывая, существует ли чужой заказ. Для админского сценария контракт будет другим: другой repository-метод, другая проверка прав и обычно другой audit trail.

Типичная маппинг-таблица для Go API:

Ситуация	Признак	Возможный ответ API
Строка не найдена	`sql.ErrNoRows`, `pgx.ErrNoRows`	`404`, иногда `403/404` без раскрытия существования
Нарушена уникальность	PostgreSQL `23505`	`409 Conflict` или ошибка валидации поля
Нарушен foreign key	PostgreSQL `23503`	`400 Bad Request` или `409 Conflict`, зависит от сценария
Нарушен `CHECK`	PostgreSQL `23514`	`400 Bad Request`, если это ошибка входных данных
Конфликт optimistic lock	`UPDATE ... WHERE id = $1 AND version = $2` вернул 0 строк	`409 Conflict`
Serialization failure	PostgreSQL `40001`	безопасный retry на уровне транзакции

Важно не ловить все ошибки базы одной веткой internal error. Некоторые ошибки - нормальная часть бизнес-потока: дубликат email, устаревшая версия записи, попытка изменить чужой ресурс, ссылка на несуществующий справочник.

Access-control nuance

Access control лучше не оставлять только на уровне handler. Для операций с пользовательскими или tenant-данными predicate доступа должен быть рядом с изменением данных:

UPDATE orders
SET status = 'cancelled'
WHERE id = $1
  AND user_id = $2
  AND status = 'draft'
RETURNING id, status;

Такой запрос атомарно проверяет владение и допустимое состояние. Паттерн "сначала SELECT, потом отдельно UPDATE" часто создает окно гонки и требует дополнительной транзакционной защиты.

Для внутренних сервисов с широкими правами это особенно важно. Service account может технически читать всё, но application query всё равно должен явно ограничивать tenant, project, user или другой ownership scope. В PostgreSQL дополнительно можно использовать отдельные роли, view для read-only доступа и Row-Level Security, но они не отменяют обязанность приложения формулировать корректный доменный контракт.

Практические правила для Go backend

Ситуация	Хорошее решение
Нужно создать запись и получить `id`	`INSERT ... RETURNING id`
Нужно запретить дубли	`UNIQUE`, а не только проверка `SELECT` перед `INSERT`
Нужно обновить только текущую версию	`WHERE id = $1 AND version = $2`
Нужно проверить владельца ресурса	Добавить `AND user_id = $currentUserID` в `UPDATE/DELETE/SELECT`
Нужно отличить пустое значение от отсутствующего	Явно использовать nullable-тип
Нужно собрать отчет	Сначала SQL-агрегация, потом Go-форматирование
Нужно передать список ids	Параметризованный массив или временная таблица, не string concatenation

Пример repository-метода:

const createUserQuery = `
INSERT INTO users (email, name)
VALUES ($1, $2)
RETURNING id, email, name, created_at
`

func (r *UserRepo) Create(ctx context.Context, email, name string) (User, error) {
    var u User
    err := r.db.QueryRowContext(ctx, createUserQuery, email, name).Scan(
        &u.ID,
        &u.Email,
        &u.Name,
        &u.CreatedAt,
    )
    if err != nil {
        return User{}, err
    }
    return u, nil
}

SQL-инъекции предотвращаются параметрами, а не ручным экранированием:

// Плохо: пользовательский ввод попадает в SQL-текст.
query := "SELECT id, email, name FROM users WHERE email = '" + email + "'"

// Хорошо: значение передается отдельно от SQL-текста.
row := db.QueryRowContext(ctx,
    "SELECT id, email, name FROM users WHERE email = $1",
    email,
)

Параметры защищают значения, но не имена колонок, направление сортировки и куски SQL-синтаксиса. Если пользователь выбирает сортировку, делайте whitelist:

sortColumn := map[string]string{
    "created_at": "created_at",
    "email":      "email",
}[input.Sort]
if sortColumn == "" {
    return nil, ErrInvalidSort
}

Типичные вопросы на собеседованиях

Чем `WHERE` отличается от `HAVING`?

WHERE фильтрует строки до группировки. HAVING фильтрует группы после GROUP BY.

SELECT user_id, count(*)
FROM orders
WHERE status = 'paid'
GROUP BY user_id
HAVING count(*) > 3;

запрос параметризован?
есть ли стабильный ORDER BY, если используется LIMIT?
не превращает ли WHERE ваш LEFT JOIN в INNER JOIN?
правильно ли обработан NULL?
не делает ли запрос N+1 round-trip'ов?
есть ли constraint, который защищает бизнес-уникальность?
проверен ли план через EXPLAIN для реального объема данных?
понятна ли ошибка пользователю, если сработает UNIQUE или FOREIGN KEY?
является ли список колонок явным контрактом, а не SELECT *?
проверяет ли запрос ownership/tenant scope там, где это нужно?
есть ли понятный retry/response path для ожидаемых ошибок PostgreSQL?

Что запомнить

Таблица хранит факты; constraint защищает правила этих фактов.
JOIN описывает связь наборов, а не "цикл по строкам".
WHERE работает до группировки, HAVING - после GROUP BY.
NULL - неизвестность, поэтому нужны IS NULL и аккуратная обработка в Go.
Параметры защищают значения от SQL injection; имена колонок и ORDER BY требуют whitelist.
ORDER BY обязателен, если результат с LIMIT должен быть стабильным.

Практика

Спроектируйте таблицы courses, lessons, users, enrollments с primary key, foreign key и уникальностью slug/email.
Напишите запрос списка курсов с количеством уроков через LEFT JOIN и GROUP BY.
Добавьте запрос "последний просмотренный урок пользователя" через window function.
Найдите в своих запросах места, где NULL может изменить результат.

Интерактивная практика

Quiz+10 XP

Почему COUNT(column) может вернуть меньше, чем COUNT(*)?

COUNT(column) считает только уникальные значения
COUNT(column) не считает строки, где column равен NULL
COUNT(column) работает только после GROUP BY
COUNT(*) всегда использует индекс, а COUNT(column) нет

Predict+15 XP

Что выведет этот код?

WITH lessons(course_id, published) AS (
    VALUES
        (1, true),
        (1, false),
        (2, false)
)
SELECT count(*) FILTER (WHERE published) AS published_count
FROM lessons
GROUP BY course_id
ORDER BY course_id;

Полезные источники

PostgreSQL Documentation: The SQL Language
PostgreSQL Documentation: Constraints
PostgreSQL Documentation: WITH Queries
PostgreSQL Documentation: Window Functions

SQLrelational modelJOINconstraintsCTEwindow functions