Лаборатории SQL, JSONB, Redis и TypeORM
Используем вымышленные публикации и авторов. PostgreSQL 17 — отдельная пустая
учебная база; Redis — отдельный экземпляр без данных CMS. Выполняйте SQL через
psql с ON_ERROR_STOP=1. Имя подключения берите из своей учебной среды,
пароль не вставляйте в команды и отчёт. Никакие таблицы Strapi не изменяются.
Эта глава содержит выполняемые инструкции; готовность текста не означает,
что внешние сервисы уже запущены и проверены на вашей машине.
Подготовить учебную среду
Начните с локального окружения, затем из корня
своего worktree выполните pnpm setup и pnpm infra:up. Здесь используется
существующий локальный PostgreSQL Compose. Команды не предназначены для VPS.
Не меняйте --username на роль CMS и не выбирайте базу strapi.
Один раз создайте отдельную роль и пустую базу (без автоматического удаления существующей базы при повторе):
docker compose exec -T postgres psql --username=atmanki --dbname=atmanki --set=ON_ERROR_STOP=1 <<'SQL'
CREATE ROLE lesson_student LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE NOREPLICATION;
CREATE DATABASE lesson_data_lab OWNER lesson_student;
SQL
docker compose exec postgres psql --username=lesson_student --dbname=lesson_data_lab --set=ON_ERROR_STOP=1
В открывшемся psql проверьте границу подключения:
SELECT current_database(), current_user;
SELECT count(*) FROM pg_tables WHERE schemaname = 'public';
Ожидаются lesson_data_lab, lesson_student и 0 таблиц. Если база/роль уже
существуют, сначала проверьте их владельца и свои прошлые файлы опыта;
не заменяйте ошибку созданием или удалением ресурсов CMS. SQL-блоки главы
можно сохранять в файл и передавать через stdin той же команде exec -T ... psql.
Встроенное локальное socket-подключение контейнера не требует вывода пароля.
Для сетевого клиента TypeORM понадобится отдельный порт, доступный только
на loopback. В интерактивном psql задайте пароль командой
\password lesson_student: пароль вводится без отображения, а не в shell-команде.
Если нужна TypeORM-часть, создайте var командой mkdir -p var и сохраните временный override в
var/lesson-postgres.compose.yaml своего worktree:
services:
postgres:
ports:
- "127.0.0.1:55432:5432"
Примените его только к своему локальному Compose (PostgreSQL будет пересоздан с сохранением тома; локальные подключения на это время прервутся):
docker compose -f compose.yaml -f var/lesson-postgres.compose.yaml up -d --wait postgres
В каталоге отдельного TypeORM-опыта создайте незакоммиченный .env.lesson:
LESSON_DATABASE_URL=postgresql://lesson_student:<URL-encoded-password>@127.0.0.1:55432/lesson_data_lab.
Замените шаблон на заданный пароль с URL-кодированием специальных символов.
Не вставляйте пароль в журнал, аргументы команды или workspace-файлы.
Перед опытом проверьте identity через этот сетевой endpoint; она должна совпасть
с lesson_data_lab / lesson_student. После опыта обычная команда
docker compose up -d --wait postgres убирает дополнительный порт; удалите
только временный override. Production-адреса в этой лаборатории не используются.
Для Redis-части поднимите отдельный временный контейнер. Он не входит в Compose,
не получает томов CMS и не публикует порт на хост. redis-cli уже есть в образе;
устанавливать его в workspace не нужно. Если такое имя занято, выберите своё
уникальное имя и замените его во всех командах, не останавливайте чужой контейнер.
docker run --name gheilt-lesson-redis --rm -d redis:7.4-alpine
docker exec gheilt-lesson-redis redis-cli PING
docker exec gheilt-lesson-redis redis-cli DBSIZE
docker inspect --format '{{.Image}}' gheilt-lesson-redis
docker exec -it gheilt-lesson-redis redis-cli
Ожидаются PONG и 0 ключей. Сохраните image ID и INFO server в журнале опыта (официальный запуск Redis):
тег ветки 7.4 получает исправления, поэтому версия должна быть явно записана.
Следующий Redis-блок вводится в этом интерактивном клиенте. Выход — QUIT;
после опыта docker stop gheilt-lesson-redis удаляет только этот --rm контейнер.
Не используйте FLUSHALL или общий кеш другого проекта.
В конце закройте все учебные подключения через \q. Из корня worktree удалите
только созданные здесь базу и роль; dropdb откажет при оставшихся соединениях:
docker compose exec -T postgres dropdb --username=atmanki --maintenance-db=atmanki lesson_data_lab
docker compose exec -T postgres dropuser --username=atmanki lesson_student
Тома Compose и база Strapi сохраняются. Это инструкции для воспроизводимого опыта; их исполнение на вашей машине и соответствие identity проверяет читатель.
SQL: связи, нормализация и JSONB
Исходник схемы
erDiagram
AUTHOR ||--o{ PUBLICATION : writes
AUTHOR {
int id PK
text name
}
PUBLICATION {
int id PK
int author_id FK
text title
jsonb attributes
}
Один автор имеет ноль или больше публикаций, у публикации ровно один автор. Кардинальность соответствует NOT NULL foreign key, а не только рисунку.
CREATE SCHEMA lesson_data;
CREATE TABLE lesson_data.author (
id integer PRIMARY KEY,
name text NOT NULL
);
CREATE TABLE lesson_data.publication (
id integer PRIMARY KEY,
author_id integer NOT NULL REFERENCES lesson_data.author(id),
title text NOT NULL CHECK (length(btrim(title)) > 0),
attributes jsonb NOT NULL DEFAULT '{}'::jsonb CHECK (jsonb_typeof(attributes) = 'object')
);
INSERT INTO lesson_data.author VALUES (1, 'Анна'), (2, 'Борис'), (3, 'Вера');
INSERT INTO lesson_data.publication VALUES
(1, 1, 'Первый урок', '{"level":"beginner","duration":60}'),
(2, 1, 'Второй урок', '{"level":"advanced","duration":90}'),
(3, 2, 'Открытая встреча', '{"level":"beginner"}');
SELECT a.id, a.name, count(p.id) AS publications
FROM lesson_data.author a LEFT JOIN lesson_data.publication p ON p.author_id = a.id
GROUP BY a.id, a.name ORDER BY a.id;
SELECT id, title FROM lesson_data.publication
WHERE attributes @> '{"level":"beginner"}'::jsonb ORDER BY id;
CREATE INDEX publication_author ON lesson_data.publication(author_id);
CREATE INDEX publication_attributes ON lesson_data.publication USING gin(attributes);
ANALYZE lesson_data.publication;
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM lesson_data.publication
WHERE attributes @> '{"level":"beginner"}'::jsonb;
Ожидаем авторов с количеством публикаций 2, 1 и 0; IDs записей уровня beginner — 1 и 3. count(*) после LEFT JOIN
дал бы 1 для автора без публикаций; считайте nullable поле связанной таблицы.
Имя автора хранится отдельно, чтобы переименование не обновляло каждую публикацию.
JSONB подходит для гибких attributes; связь и обязательные инварианты не прячем
в документ без причины. CHECK здесь проверяет только object, не схему duration.
На трёх строках Seq Scan нормален: индекс не обязан быть дешевле. Для опыта с планом создайте большой учебный набор, сохраните распределение значений, выполните ANALYZE и сравните число прочитанных строк/блоков. Не отключайте Seq Scan, чтобы «доказать» пользу индекса. EXPLAIN ANALYZE реально выполняет запрос, поэтому для UPDATE учитывайте эффект и транзакцию.
Приёмка: чужой author_id отвергается FK, массив attributes отвергается CHECK,
переименование автора отражается в JOIN без изменения публикаций. Проверьте
missing duration, JSON null и SQL NULL отдельно. Выберите колонку для значения,
по которому часто фильтруете и которое обязано проходить ограничения.
Redis: TTL и отказ кеша
В отдельном учебном Redis через redis-cli, с префиксом lesson::
SET lesson:publication:1 "old" EX 60
GET lesson:publication:1
TTL lesson:publication:1
DEL lesson:publication:1
GET lesson:publication:1
SET lesson:lock:publication:1 "owner-A" NX PX 5000
SET lesson:lock:publication:1 "owner-B" NX PX 5000
Сначала old, затем TTL не больше 60, после DEL nil; второй NX не захватит занятый lock. После истечения аренды B может получить lock, но A всё ещё способен выполнять старую работу. Для release проверяйте owner атомарно:
if redis.call('GET', KEYS[1]) == ARGV[1] then
return redis.call('DEL', KEYS[1])
end
return 0
Передайте скрипт через EVAL с одним ключом и owner value. Отдельный GET, затем DEL оставляет гонку; удалять чужую аренду нельзя. Для защиты записи одного owner value недостаточно: нужен fencing/version на стороне хранилища, если старый владелец опасен. Это учебный single-instance lock, не доказательство распределённой безопасности.
Для cache-aside сначала читайте кеш, на miss — источник и сохраняйте с TTL. Запись источника меняет данные и инвалидирует ключ; возможные гонки разобраны в лаборатории кеша. Отключите только свой учебный Redis и проверьте чтение из БД с ограниченным timeout: отказ кеша не должен превращать существующую публикацию в 404. Сравните задержку и нагрузку на источник. Если Redis хранит sessions/очередь/единственные данные, требования и политика отказа другие. TTL и persist-настройка — независимые свойства.
Не используйте FLUSHALL/FLUSHDB. Очистите только перечисленные lesson: ключи
после опыта. В Atmanki Redis не установлен и не нужен для этого чтения CMS.
TypeORM: Data Mapper и реальные запросы
Во временном каталоге вне workspace установите typeorm@0.3.28, pg@8.16.3
и reflect-metadata@0.2.2 с точными версиями. Это отдельный учебный проект.
Используем EntitySchema, чтобы не добавлять transpiler/decorators ради опыта.
Сохраните блок в orm.mjs; LESSON_DATABASE_URL указывает только на учебную БД.
Запуск из каталога опыта: node --env-file=.env.lesson orm.mjs. Таблицы создаются миграцией, не synchronize: true.
import "reflect-metadata";
import assert from "node:assert/strict";
import { DataSource, EntitySchema } from "typeorm";
const Author = new EntitySchema({
name: "Author",
schema: "lesson_orm",
tableName: "author",
columns: { id: { type: Number, primary: true }, name: { type: String } },
relations: {
publications: { type: "one-to-many", target: "Publication", inverseSide: "author" },
},
});
const Publication = new EntitySchema({
name: "Publication",
schema: "lesson_orm",
tableName: "publication",
columns: {
id: { type: Number, primary: true },
title: { type: String },
version: { type: Number, default: 1 },
},
relations: {
author: {
type: "many-to-one",
target: "Author",
joinColumn: { name: "author_id" },
nullable: false,
},
},
});
class Initial1700000000000 {
async up(queryRunner) {
await queryRunner.query("CREATE SCHEMA lesson_orm");
await queryRunner.query(
"CREATE TABLE lesson_orm.author (id integer PRIMARY KEY, name text NOT NULL)",
);
await queryRunner.query(
"CREATE TABLE lesson_orm.publication (id integer PRIMARY KEY, title text NOT NULL CHECK(length(btrim(title))>0), version integer NOT NULL DEFAULT 1 CHECK(version>0), author_id integer NOT NULL REFERENCES lesson_orm.author(id))",
);
}
async down(queryRunner) {
await queryRunner.query("DROP SCHEMA lesson_orm CASCADE");
}
}
if (!process.env.LESSON_DATABASE_URL) throw new Error("Set a separate learning database URL");
const db = new DataSource({
type: "postgres",
url: process.env.LESSON_DATABASE_URL,
entities: [Author, Publication],
migrations: [Initial1700000000000],
migrationsTableName: "lesson_orm_migrations",
synchronize: false,
logging: ["query", "error"],
});
await db.initialize();
try {
const [identity] = await db.query("SELECT current_database() AS database, current_user AS role");
assert.equal(identity.database, "lesson_data_lab");
assert.equal(identity.role, "lesson_student");
await db.runMigrations();
await db.transaction(async (manager) => {
await manager.getRepository("Author").save({ id: 1, name: "Анна" });
await manager.getRepository("Publication").save({ id: 1, title: "Урок", author: { id: 1 } });
});
const rows = await db.getRepository("Publication").find({ relations: { author: true } });
assert.equal(rows[0].author.name, "Анна");
const result = await db
.getRepository("Publication")
.createQueryBuilder()
.update()
.set({ title: "Новый урок", version: () => "version + 1" })
.where("id = :id AND version = :version", { id: 1, version: 1 })
.execute();
assert.equal(result.affected, 1);
const stale = await db
.getRepository("Publication")
.createQueryBuilder()
.update()
.set({ title: "Старая правка", version: () => "version + 1" })
.where("id = :id AND version = :version", { id: 1, version: 1 })
.execute();
assert.equal(stale.affected, 0);
} finally {
await db.destroy();
}
Первый запуск предполагает чистую схему; повтор на тех же данных изменит исходные условия version. Миграцию down запускайте только для этого отдельного опыта после проверки списка объектов; CASCADE не является командой очистки CMS. Логи SQL на вымышленных данных помогают увидеть запрос, но production query logging может раскрыть параметры.
Data Mapper использует repository/manager отдельно от entities. В Active Record
entity наследует BaseEntity и получает save/find методы. Это разные способы
расположить ответственность, не разные гарантии транзакций. Внутри transaction
используйте её manager, а не глобальный repository: иначе запрос может уйти
через другое соединение за пределами транзакции.
Пул ограничивает параллельные соединения; сумма пулов всех процессов должна
укладываться в бюджет БД. QueryBuilder не делает произвольную строку SQL безопасной:
значения передавайте параметрами, имена сортировки выбирайте из allowlist.
VersionColumn или save сами по себе не доказывают нужную проверку конфликта;
здесь ожидаемую version проверяет WHERE и число затронутых строк.
Опыт N+1: создайте 10 авторов и публикации. Сначала загрузите publications,
затем отдельным read автора для каждого элемента; посчитайте SQL. Сравните
один JOIN и batch IN на уникальных IDs. Меньше запросов не всегда быстрее:
избыточный JOIN больших коллекций умножает строки. Покажите SQL и план.
Приёмка: миграция на пустой БД, FK, одно условное обновление и один конфликт, rollback при ошибке внутри transaction, число запросов без/со связями и закрытие пула. Проверка синтаксиса JS не заменяет запуск против PostgreSQL 17.
Источники: JOIN, JSONB, Redis SET, EntitySchema, transactions.