Схема PostgreSQL и история миграций

Оглавление серии

Каталог отделён от HTTP через зависимость. Теперь изменим источник данных: кабинеты будут читаться из PostgreSQL, а будущие брони получат устойчивые идентификаторы и правила целостности. В этом уроке ещё не добавляем POST создания. Самостоятельный результат — понять схему, перенести чтение каталога в SQLAlchemy и объяснить, как известное состояние базы связано с файлами миграций.

Миграция — именованное изменение схемы, которое должно выполняться в определённом порядке. В учебном проекте используем небольшой SQL runner вместо подключения дополнительной системы миграций. Он подходит ровно показанным простым SQL-файлам; его разделение по точке с запятой не предназначено для функций SQL с внутренними разделителями. Это явное ограничение инструмента, а не причина вручную изменять уже применённый файл в чужой базе.

Сущности и ограничения

actors хранит двух вымышленных владельцев, resources — два кабинета. bookings ссылается на оба справочника. Внешний ключ означает, что сохранённая бронь не может указывать на отсутствующего владельца или кабинет. Python-модель и предварительный поиск не заменяют это правило: другой процесс может записывать в ту же базу. В этой ступени schema является источником структурных ограничений, а ORM описывает удобное отображение нужных таблиц.

id брони создаёт identity-столбец. Клиент его не выбирает. Последовательность выдаёт новые значения, и пропуски в номерах допустимы, например после отменённой транзакции. Идентификатор нужен для ссылки, а не для подсчёта успешных броней. Это пригодится в пагинации: порядок по id удобен, но его не следует объявлять точным временем завершения всех параллельных операций. Для такой задачи понадобился бы отдельный договор событий.

Поля starts_at и ends_at имеют тип timestamptz. CHECK positive_interval требует, чтобы конец следовал за началом. Второй CHECK допускает только confirmed и cancelled. Эти правила действуют для любой записи, проходящей через таблицу, даже если клиент обошёл наш HTTP API. Однако запрет пересечений здесь ещё отсутствует: его добавим отдельной миграцией после разбора конкуренции. Пока не называем схему готовой защитой общего расписания.

Полные файлы шага

Показанные файлы полностью совпадают с выбранным состоянием архива. Остальные согласованные файлы находятся в examples/fastapi-booking-lab/lesson-06/.

migrations/001_schema.sql

CREATE TABLE actors (
    id integer PRIMARY KEY,
    display_name text NOT NULL
);
CREATE TABLE resources (
    id integer PRIMARY KEY,
    name varchar(100) NOT NULL
);
CREATE TABLE bookings (
    id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    resource_id integer NOT NULL REFERENCES resources(id),
    actor_id integer NOT NULL REFERENCES actors(id),
    starts_at timestamptz NOT NULL,
    ends_at timestamptz NOT NULL,
    state varchar(20) NOT NULL DEFAULT 'confirmed',
    CONSTRAINT positive_interval CHECK (ends_at > starts_at),
    CONSTRAINT booking_state CHECK (state IN ('confirmed', 'cancelled'))
);
INSERT INTO actors VALUES (1, 'Первый читатель'), (2, 'Второй читатель');
INSERT INTO resources VALUES (1, 'Кабинет Лён'), (2, 'Кабинет Клён');

db.py

from collections.abc import Iterator

from sqlalchemy import create_engine
from sqlalchemy.orm import Session, sessionmaker

from settings import get_settings

engine = create_engine(
    get_settings().database_url,
    pool_pre_ping=True,
    isolation_level="READ COMMITTED",
)
SessionFactory = sessionmaker(bind=engine, expire_on_commit=False)


def get_session() -> Iterator[Session]:
    with SessionFactory() as session:
        yield session

migrate.py

from hashlib import sha256
from pathlib import Path

from sqlalchemy import text

from db import engine


def migrate() -> None:
    paths = sorted((Path(__file__).parent / "migrations").glob("*.sql"))
    with engine.begin() as connection:
        connection.execute(text("SELECT pg_advisory_xact_lock(42500)"))
        connection.execute(text(
            "CREATE TABLE IF NOT EXISTS schema_migrations "
            "(name text PRIMARY KEY, checksum char(64) NOT NULL)"
        ))
        applied = dict(connection.execute(text(
            "SELECT name, checksum FROM schema_migrations"
        )).all())
        for path in paths:
            sql = path.read_text(encoding="utf-8")
            checksum = sha256(sql.encode()).hexdigest()
            if path.name in applied:
                if applied[path.name] != checksum:
                    raise RuntimeError(f"Changed migration: {path.name}")
                continue
            for statement in sql.split(";"):
                if statement.strip():
                    connection.exec_driver_sql(statement)
            connection.execute(text(
                "INSERT INTO schema_migrations (name, checksum) "
                "VALUES (:name, :checksum)"
            ), {"name": path.name, "checksum": checksum})


if __name__ == "__main__":
    migrate()

Ожидаемый результат

Это ожидаемый договор для самостоятельного исследования; пример не выполнялся.

Ожидаемая история: 001_schema.sql
resources: 1 «Кабинет Лён», 2 «Кабинет Клён»
bookings: пока пусто

Миграция и её контрольная сумма

migrate.py собирает SQL-файлы в лексикографическом порядке, поэтому цифровые префиксы важны. Он создаёт таблицу schema_migrations и хранит имя применённого файла вместе с SHA-256 содержимого. Повторный запуск пропускает неизменённый файл, а изменённая контрольная сумма вызывает отказ. Так редактор видит различие между новым шагом схемы и переписыванием истории. Корректировка уже известного состояния должна иметь новый файл с новым именем.

engine.begin открывает транзакцию для показанной последовательности. Успешное завершение блока фиксирует изменения; исключение отменяет их. Advisory lock с постоянным учебным ключом не позволяет двум нашим runners одновременно применять историю к одной базе. Он не блокирует все возможные административные инструменты: другие способы изменения схемы должны соблюдать согласованный процесс. Наш договор охватывает этот runner и собственную пустую учебную базу, не действующий сервер организации.

Миграция добавляет два кабинета и двух владельцев. Данные фиксированы, поэтому каталог после переноса должен сохранить прежние имена и номера. Повторное исполнение INSERT вручную уже не является корректным способом применения истории. Runner сначала проверяет schema_migrations. Это ещё одна причина переносить весь снимок вместе с файлами SQL: один models.py не создаёт начальные данные и не знает, какие изменения реально проходили в базе.

Сессия одного запроса

Engine управляет подключениями, а Session представляет единицу работы ORM. get_session открывает сессию внутри with и выдаёт её через yield. Когда запрос завершает использование зависимости, контекст закрывает сессию. Глобальная Session на все запросы не подходит: она смешала бы состояние операций и ошибки транзакций. Engine может жить дольше запроса, поскольку это другой объект с другой обязанностью. Закрытие пула процесса рассмотрим ближе к передаче проекта.

Чтение списка выполняет select(Resource).order_by(Resource.id). Явный порядок сохраняет предсказуемую выдачу: таблица не обязана возвращать строки в порядке вставки без ORDER BY. Поиск карточки использует db.get по первичному ключу. После замены источника внешний ResourceOut остаётся тем же. Клиенту не нужно знать, какие SQL-операции выполнялись; ему нужен устойчивый JSON-договор каталога.

В models.py объявлены только Resource и Booking. actors пока не используется ORM напрямую, поэтому actor_id отображён целым числом, а внешний ключ владельца задан в миграции. Мы не вызываем metadata.create_all: он не является используемой здесь историей схемы. При расхождении классов и SQL исправляется согласованный учебный снимок или добавляется новая миграция, а не создаётся параллельная история неизвестного происхождения.

Ни база, ни миграции при подготовке курса не запускались. Читатель применяет migrate.py только к собственной учебной PostgreSQL 18 после настройки доступа, а затем изучает каталог через API. Ожидаемое состояние — таблицы и запись 001_schema.sql с checksum, два ресурса и отсутствие броней. Следующий урок добавит атомарное создание записи, сохранив проверку DTO и внешний контракт. Установка пакетов и фактическая совместимость диапазонов требуют отдельного самостоятельного подтверждения.

История задаёт исходное состояние

Цифровое имя 001_schema.sql важно не как декоративный номер главы. Оно позволяет runner сравнить каталог файлов с историей базы и определить, какой переход отсутствует. Если перенести только новый SQL без прежних файлов, полная история проекта потеряет объяснимость. Поэтому последующие снимки сохраняют уже введённые миграции побайтно. Новый индекс или ограничение появится под новым именем, а не внутри тихо переписанной первой схемы.

Контрольная сумма относится к содержимому файла, включая редакционные изменения. Даже исправление пробела в уже применённой миграции даст другой SHA-256. Это намеренно строгая учебная политика: reader должен видеть, что история была изменена. Она не доказывает, что кто-то не выполнял произвольный SQL вручную, и не является аудитом всех административных действий. Наш runner охватывает согласованные переходы собственных учебных состояний. Для работы с чужими данными потребовался бы отдельный процесс сопровождения, а не слепой перенос этого небольшого инструмента.

Документация и исходники

Механизмы главы сверены с первичными источниками: документация 1, документация 2, документация 3. Полный архив «Окна» содержит все шестнадцать состояний. Пакеты, приложение и миграции не запускались.