Мини‑проект: схема для «Списка задач» + запросы
Мини‑проект: схема для «Списка задач» + все SQL операции
Собираем полноценный проект: БД для списка задач. Это реальный сценарий, который ты встретишь везде (Notion, GitHub, Jira и т.д.). Здесь сходится всё из трека: ограничения, индексы, агрегаты и планы запросов.
Архитектура
Таблицы для хранения пользователей и задач:
-- Пользователи
CREATE TABLE users (
id BIGSERIAL PRIMARY KEY,
email TEXT NOT NULL UNIQUE,
name TEXT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- Задачи
CREATE TABLE tasks (
id BIGSERIAL PRIMARY KEY,
user_id BIGINT NOT NULL,
title TEXT NOT NULL,
description TEXT,
status VARCHAR(20) NOT NULL DEFAULT 'new',
priority INT NOT NULL DEFAULT 3,
done BOOLEAN NOT NULL DEFAULT false,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
deleted_at TIMESTAMPTZ,
CONSTRAINT tasks_user_fk FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
CONSTRAINT tasks_status_check CHECK (status IN ('new', 'in_progress', 'done', 'archived')),
CONSTRAINT tasks_priority_check CHECK (priority >= 1 AND priority <= 5)
);
-- Индексы для быстрого поиска.
--
-- Первый - на столбец внешнего ключа. PostgreSQL его НЕ создаёт сам:
-- UNIQUE и PRIMARY KEY индекс дают, REFERENCES - нет. Без него каждый
-- DELETE пользователя сканирует всю tasks, чтобы проверить ON DELETE CASCADE.
CREATE INDEX idx_tasks_user_id ON tasks(user_id);
CREATE INDEX idx_tasks_status ON tasks(status);
CREATE INDEX idx_tasks_deleted_at ON tasks(deleted_at);
INSERT: добавление данных
Вставка пользователя
-- Простой INSERT
INSERT INTO users (email, name) VALUES ('alice@example.com', 'Alice');
-- INSERT RETURNING - сразу получаем созданный ID
INSERT INTO users (email, name) VALUES ('bob@example.com', 'Bob')
RETURNING id, email;
-- Результат: id=2, email='bob@example.com'
-- INSERT несколько строк за раз
INSERT INTO users (email, name) VALUES
('charlie@example.com', 'Charlie'),
('diana@example.com', 'Diana'),
('eve@example.com', 'Eve');
Вставка задач
-- Создаём задачу (статус и priority берут DEFAULT)
INSERT INTO tasks (user_id, title, description)
VALUES (1, 'Выучить SQL', 'Пройти все 12 уроков');
-- С явным статусом и приоритетом
INSERT INTO tasks (user_id, title, description, status, priority)
VALUES (1, 'Написать проект', 'Собрать настоящую БД', 'in_progress', 4);
-- Несколько задач
INSERT INTO tasks (user_id, title, status, priority) VALUES
(2, 'Купить молоко', 'new', 1),
(2, 'Написать отчёт', 'in_progress', 5),
(1, 'Встреча с командой', 'new', 3);
-- Вставка с RETURNING - полезно в приложении
INSERT INTO tasks (user_id, title, status, priority)
VALUES (1, 'Задача номер 42', 'new', 2)
RETURNING id, created_at;
SELECT: чтение данных
Базовый SELECT
-- Все задачи пользователя (с сортировкой)
SELECT id, title, status, priority, created_at
FROM tasks
WHERE user_id = 1
ORDER BY created_at DESC;
-- Активные задачи (не архивированные, не удалённые)
SELECT id, title, status, priority
FROM tasks
WHERE user_id = 1
AND status != 'archived'
AND deleted_at IS NULL
ORDER BY priority DESC, created_at ASC;
Группировка и агрегация
-- Сколько задач в каждом статусе
SELECT status, COUNT(*) as task_count
FROM tasks
WHERE user_id = 1 AND deleted_at IS NULL
GROUP BY status
ORDER BY task_count DESC;
-- Результат:
-- status | task_count
-- done | 12
-- in_progress | 5
-- new | 3
-- Количество задач у каждого пользователя (включая тех, у кого 0)
SELECT u.name, COUNT(t.id) as task_count
FROM users u
LEFT JOIN tasks t ON u.id = t.user_id AND t.deleted_at IS NULL
GROUP BY u.id, u.name
ORDER BY task_count DESC;
-- Задачи с высоким приоритетом (4-5)
SELECT u.name, t.title, t.priority
FROM tasks t
JOIN users u ON t.user_id = u.id
WHERE t.priority >= 4
AND t.done = false
AND t.deleted_at IS NULL
ORDER BY t.priority DESC, t.created_at ASC;
Группировку и агрегаты разбирали в уроке про GROUP BY.
Аналитические запросы
-- Недавно созданные задачи (за последние 7 дней)
SELECT id, title, user_id, created_at
FROM tasks
WHERE created_at > NOW() - INTERVAL '7 days'
AND deleted_at IS NULL
ORDER BY created_at DESC;
-- Зависшие задачи (старше 7 дней)
SELECT id, title, user_id, created_at,
(NOW() - created_at) as age
FROM tasks
WHERE status != 'done'
AND deleted_at IS NULL
AND created_at < NOW() - INTERVAL '7 days'
ORDER BY created_at ASC;
-- Задачи с максимальным приоритетом для каждого пользователя
SELECT DISTINCT ON (user_id)
user_id, title, priority, created_at
FROM tasks
WHERE deleted_at IS NULL AND done = false
ORDER BY user_id, priority DESC, created_at DESC;
UPDATE: изменение данных
Простой UPDATE
-- Отметить задачу как выполненную
UPDATE tasks
SET done = true, status = 'done', updated_at = NOW()
WHERE id = 10 AND user_id = 1; -- ВАЖНО: проверяем user_id для безопасности
-- Обновляем приоритет
UPDATE tasks
SET priority = 5, updated_at = NOW()
WHERE id = 15 AND user_id = 1;
UPDATE с условиями
-- Переместить старые новые задачи в архив
UPDATE tasks
SET status = 'archived', updated_at = NOW()
WHERE status = 'new'
AND created_at < NOW() - INTERVAL '30 days'
AND deleted_at IS NULL;
-- Повысить приоритет всем задачам в процессе
UPDATE tasks
SET priority = priority + 1, updated_at = NOW()
WHERE status = 'in_progress'
AND user_id = 1;
UPDATE с RETURNING
-- Обновляем и сразу видим результат
UPDATE tasks
SET done = true, status = 'done', updated_at = NOW()
WHERE id = 42 AND user_id = 1
RETURNING id, title, status, updated_at;
DELETE: удаление данных
Hard Delete (физическое удаление)
-- Сразу удаляем задачу (осторожно!)
DELETE FROM tasks
WHERE id = 10 AND user_id = 1;
-- Удаляем все архивированные задачи старше года
DELETE FROM tasks
WHERE status = 'archived'
AND created_at < NOW() - INTERVAL '1 year';
🚫 Hard DELETE рискован
При физическом удалении ты теряешь данные навсегда. Для аудита и восстановления используй soft delete.
Soft Delete (мягкое удаление)
Вместо реального DELETE используем временную метку:
-- Помечаем задачу как удалённую
UPDATE tasks
SET deleted_at = NOW()
WHERE id = 10 AND user_id = 1;
-- Сами по себе удалённые строки никуда не деваются: каждый запрос
-- должен явно исключать их условием deleted_at IS NULL
SELECT id, title FROM tasks
WHERE user_id = 1 AND deleted_at IS NULL;
-- Восстановление (если было случайно удалено)
UPDATE tasks
SET deleted_at = NULL
WHERE id = 10 AND user_id = 1;
-- Физическое удаление спустя 30 дней
DELETE FROM tasks
WHERE deleted_at < NOW() - INTERVAL '30 days';
Чтобы не дописывать deleted_at IS NULL в каждый запрос руками, условие прячут в представление (VIEW). Это сохранённый под именем SELECT: данных оно не хранит, а при каждом обращении выполняет свой запрос.
CREATE VIEW active_tasks AS
SELECT id, user_id, title, status, priority, created_at
FROM tasks
WHERE deleted_at IS NULL;
-- Дальше читаем из него, как из обычной таблицы
SELECT id, title FROM active_tasks WHERE user_id = 1;
Полный сценарий: управление задачами
В приложении id нового пользователя и задач берут из RETURNING. Чтобы сценарий можно было выполнить в psql целиком, не подставляя id руками, здесь пользователь находится по email, а задачи - по названию.
-- 1. Создаём пользователя
INSERT INTO users (email, name) VALUES ('john@example.com', 'John Doe')
RETURNING id; -- id выдаст база, в приложении сохраняем его в переменную
-- 2. Добавляем несколько задач
INSERT INTO tasks (user_id, title, description, status, priority)
SELECT u.id, t.title, t.description, t.status, t.priority
FROM users u,
(VALUES
('Срочно: Подготовить отчёт', 'до завтра', 'in_progress', 5),
('Обучение SQL', 'закончить курс', 'in_progress', 4),
('Купить продукты', '', 'new', 1)
) AS t(title, description, status, priority)
WHERE u.email = 'john@example.com'
RETURNING id, title;
-- 3. Смотрим активные задачи
SELECT t.id, t.title, t.priority, t.status
FROM tasks t
JOIN users u ON u.id = t.user_id
WHERE u.email = 'john@example.com' AND t.deleted_at IS NULL
ORDER BY t.priority DESC;
-- 4. Отмечаем первую задачу как выполненную
UPDATE tasks
SET done = true, status = 'done', updated_at = NOW()
WHERE title = 'Срочно: Подготовить отчёт'
AND user_id = (SELECT id FROM users WHERE email = 'john@example.com');
-- 5. Статистика по задачам
SELECT
COUNT(*) FILTER (WHERE status = 'done') as done_count,
COUNT(*) FILTER (WHERE status = 'in_progress') as in_progress_count,
COUNT(*) FILTER (WHERE status = 'new') as new_count
FROM tasks
WHERE user_id = (SELECT id FROM users WHERE email = 'john@example.com')
AND deleted_at IS NULL;
-- 6. Мягко удаляем ненужную задачу
UPDATE tasks SET deleted_at = NOW()
WHERE title = 'Купить продукты'
AND user_id = (SELECT id FROM users WHERE email = 'john@example.com');
Индексная стратегия
-- Индексы из схемы выше уже созданы - здесь только то, чего там не было.
--
-- Индекса на users(email) сознательно нет: колонка объявлена UNIQUE,
-- а UNIQUE в PostgreSQL реализован через уникальный индекс (users_email_key).
-- Второй индекс на ту же колонку хранил бы те же данные и удваивал работу
-- на каждой вставке, ничего не ускоряя.
-- Комбинированный частичный индекс для часто используемого запроса.
-- deleted_at в ключ не входит: в таких строках он всегда NULL
CREATE INDEX idx_tasks_user_status_active ON tasks(user_id, status)
WHERE deleted_at IS NULL;
-- Проверяем что используется
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title FROM tasks
WHERE user_id = 1 AND status = 'in_progress' AND deleted_at IS NULL;
Как читать вывод - в уроке про EXPLAIN.
Миграции: как менять схему со временем
-- v1: добавляем поле
ALTER TABLE tasks ADD COLUMN due_date DATE;
-- v2: добавляем индекс
CREATE INDEX idx_tasks_due_date ON tasks(due_date);
-- v3: добавляем поле-ссылку (без дефолта, по умолчанию NULL)
ALTER TABLE tasks ADD COLUMN assignee_id BIGINT REFERENCES users(id);
-- v4: переименовываем поле
ALTER TABLE tasks RENAME COLUMN done TO is_done;
-- v5: массив тегов. TEXT[] - массив строк прямо в одной колонке
ALTER TABLE tasks ADD COLUMN tags TEXT[] NOT NULL DEFAULT '{}';
UPDATE tasks SET tags = ARRAY['work', 'urgent'] WHERE id = 1;
SELECT id, title FROM tasks WHERE 'urgent' = ANY(tags); -- задачи с тегом urgent
-- Всегда тести миграции перед деплоем!
-- В проде делай по одной, осторожно
ℹ️ Правило 1: Проверяй user_id
Никогда не забывай добавить
AND user_id = $current_userв WHERE. Иначе пользователь видит/меняет чужие задачи!
ℹ️ Правило 2: Используй soft delete
deleted_at нужен для аудита. Физическое удаление уничтожает историю.
Полный чек-лист реализации
Когда ты используешь эту схему в приложении:
- Таблицы и индексы созданы
- Все SELECT запросы добавляют
AND deleted_at IS NULL - Все UPDATE/DELETE проверяют
AND user_id = current_user - Используется soft delete (UPDATE deleted_at) вместо DELETE
- Добавлен
updated_atи обновляется при каждом UPDATE - Есть миграции для изменений схемы (не меняй вручную на проде)
- Написаны unit-тесты для основных запросов
- Проверены индексы через EXPLAIN для основных операций
- На проде есть резервные копии (backup)
- Логирование всех изменений (audit log таблица, если нужно)
Практические задания
- Пересоздай всю схему в своей БД и вставь тестовые данные
- Напиши запрос: все невыполненные задачи пользователя, отсортированные по приоритету
- Напиши запрос: для каждого статуса - количество задач у каждого пользователя
- Реализуй soft delete: добавь DELETE запрос через UPDATE deleted_at
- Добавь индекс и проверь EXPLAIN - как изменилась стоимость?
- Напиши миграцию: добавить поле
tags(массив текста) для категоризации - Создай VIEW для часто используемых запросов (например, активные задачи)
- Подключи эту схему из приложения: database/sql в Go или PDO в PHP