Как писать и тестировать миграции БД с Alembic

Александр Васин (Яндекс) @ MoscowPython Meetup 69

"Python приложения могут работать с самыми различными БД (Postgres, SQLite, MariaDB и др.) и перед разработчиками приложений возникает задача реализовать возможность легко и безопасно изменять состояние БД: как структуры так и самих данных, от версии к версии приложения.
В докладе я поделюсь опытом использования хорошо зарекомендовавшего себя инструмента для управления миграциями - alembic.

Расскажу, почему стоит остановить свой выбор именно на нем, как с его помощью подготовить миграции, как их запускать (автоматически или вручную), зачем тестировать, какие проблемы могут выявить тесты и как эти тесты реализовать.

Мы рассмотрим проблемы необратимых изменений в миграциях, а также несколько лайфхаков alembic, которые сделают работу с миграциями легкой и приятной".

Видео: http://www.moscowpython.ru/meetup/69/talk-from-yandex/

Transcript

1. Разработчик программного обеспечения
   Отдел подготовки и анализа данных
   Как писать и тестировать
   миграции БД с alembic
   Александр Васин
   

   View Slide

2. Что такое миграции?
   

   View Slide

3. Что такое миграции?
   3
   App v1 App v2
   table 1’
   table 2
   table 3
   App v3
   table 1’
   table 2
   table 3
   table 1
   table 2
   rollout
   

   View Slide

4. Миграция 3
   Миграция 1
   Что такое миграции?
   4
   App v1 App v2
   table 1’
   table 2
   table 3
   App v3
   table 1’
   table 2
   table 3
   table 1
   table 2
   Миграция 2
   upgrade
   downgrade
   rollout
   

   View Slide

5. Код, который переводит БД из одного определенного
   состояния в другое (структура данных, данные)
   Код, который откатывает эти изменения обратно
   Процесс, при котором выполняется код, изменяющий
   состояние БД
   Что такое миграции?
   5
   

   View Slide

6. Атомарность - миграция (или группа миграций) должна быть
   применена либо полностью, либо никак
   Обратимость - миграции должны содержать код, который
   позволит вернуться к предыдущему состоянию
   Упорядоченность - должно быть понятно, в каком порядке
   нужно накатывать миграции, какую катить следующей
   Какими свойствами они должны обладать?
   6
   

   View Slide

7. Инструменты
   

   View Slide

8. Файлы с SQL запросами
   yoyo-migrations
   django migrations
   alembic
   Как реализовать миграции?
   8
   

   View Slide

9. CREATE TABLE table1 (
   id SERIAL PRIMARY KEY,
   name TEXT,
   ...
   )
   CREATE TABLE table2 (...)
   Файлы с SQL запросами
   9
   DROP TABLE table1;
   DROP TABLE table2;
   upgrade_v1.sql downgrade_v1.sql
   

   View Slide

10. Необходимо вручную хранить состояние БД (какие миграции
    были выполнены)
    Отсутствует защита от выполнения миграции, не
    рассчитанной на работу с текущим состоянием БД
    Запросы в миграциях необходимо писать вручную, учитывая
    связи таблиц
    В каждой миграции нужно не забыть использовать транзакции
    Файлы с SQL запросами: недостатки
    10
    

    View Slide

11. yoyo-migrations
    11
    Поддерживает SQLite, Postgres,
    MySQL (MariaDB)
    Использует сырые SQL запросы
    

    View Slide

12. UPGRADE = """
    CREATE TABLE table1 (
    id SERIAL PRIMARY KEY,
    name TEXT,
    ...
    );
    CREATE TABLE table2 (...);
    """
    DOWNGRADE = """
    DROP TABLE table1;
    DROP TABLE table2;
    """
    yoyo-migrations: как выглядит миграция
    12
    from yoyo import step
    steps = [
    step(UPGRADE, DOWNGRADE),
    ]
    

    View Slide

13. # Создать новую миграцию
    yoyo new ./migrations -m "Add column to foo"
    # Применить (upgrade)
    yoyo apply --database postgresql://user:hackme@localhost/db ./migrations
    # Откатить
    yoyo rollback --database mysql://user:hackme@localhost/db ./migrations
    yoyo-migrations: как использовать
    13
    

    View Slide

14. Автоматически создает специальную таблицу _yoyo_migration,
    в которой сохраняет состояние БД (примененные миграции)
    Перед запуском миграции проверяет, можно ли ее запустить в
    текущем состоянии БД
    Выполняет каждую миграцию в отдельной транзакции
    yoyo-migrations: достоинства
    14
    

    View Slide

15. Запросы в миграциях необходимо писать вручную, учитывая
    связи таблиц
    Много рутинной работы, автоматизировать невозможно
    Использовать бизнес-логику, написанную на python для
    обработки данных в миграциях невозможно
    yoyo-migrations: недостатки
    15
    

    View Slide

16. Framework agnostic, построен поверх
    SQLAlchemy
    Умеет автоматически генерировать код
    миграций на основе описанных в коде
    моделей и анализа БД
    Позволяет писать очень сложную бизнес-
    логику в миграциях без ограничений
    Умеет работать с ограниченными
    возможностями ALTER в SQLite
    Alembic
    16
    

    View Slide

17. SQLAlchemy
    

    View Slide

18. Недостатки
    〉Нельзя переиспользовать,
    расширять
    〉Большие запросы сложно читать,
    писать во вложенных конструкциях
    из-за отступов
    〉Нет инструментов для
    динамического построения запросов
    〉При миграциях нужно погрепать все
    сырые запросы в проекте
    Сырые SQL запросы
    18
    Достоинства
    Не требуется дополнительных
    библиотек
    Нет накладных расходов на
    обработку
    

    View Slide

19. Недостатки
    〉За удобство приходится платить
    накладными расходами - временем на
    генерацию запросов и памятью
    Query builder
    19
    Достоинства
    〉Python-синтаксис, легко
    разделять на части и читать
    〉Запросы можно
    декомпозировать,
    переиспользовать и расширять
    〉Построение динамических
    запросов
    〉При изменениях достаточно
    статического анализа кода
    

    View Slide

20. Отлично работает как с синхронными драйверами (psycopg2),
    так и с асинхронными (aiopg, asyncpgsa)
    Поддерживает огромное кол-во драйверов, баз данных, очень
    богатые возможности, при этом очень гибкая и дает честный
    единый интерфейс
    Отличная документация, большое сообщество, активно
    разрабатывается
    Есть очень много инструментов для разработки
    (sqlalchemy_utils, alembic, etc.)
    Не диктует фреймворков и библиотек
    Почему же SQLAlchemy?
    20
    

    View Slide

21. Из чего состоит SQLAlchemy?
    21
    Engine
    table 2
    table 1
    MetaData Dialect
    

    View Slide

22. Контейнер, содержит информацию о схеме БД: всех таблицах,
    индексах, типах данных и т.п.
    Умеет получать информацию о том, какие сущности сейчас есть
    в базе (reflect)
    Позволяет указать шаблоны именования индексов и constraint-
    ов по умолчанию
    SQLAlchemy: MetaData
    22
    

    View Slide

23. convention = {
    'all_column_names': lambda constraint, table: '_'.join([
    column.name for column in constraint.columns.values()
    ]),
    'ix': 'ix__%(table_name)s__%(all_column_names)s',
    'uq': 'uq__%(table_name)s__%(all_column_names)s',
    'ck': 'ck__%(table_name)s__%(constraint_name)s',
    'fk': (
    'fk__%(table_name)s__%(all_column_names)s__'
    '%(referred_table_name)s'
    ),
    'pk': 'pk__%(table_name)s'
    }
    metadata = sqlalchemy.MetaData(naming_convention=convention)
    MetaData naming conventions
    23
    

    View Slide

24. Описывает структуру определенной таблицы (типы столбцов, индексы
    и др).
    Необходима, чтобы SQLAlchemy могла генерировать запросы
    SQLAlchemy: Table
    24
    users_table = Table(
    'users',
    metadata,
    Column('user_id', Integer, primary_key=True),
    Column('email', String(256), nullable=False, unique=True)
    )
    

    View Slide

25. Скрывает за собой пул подключений и диалект, которые работают
    непосредственно с модулем DBAPI (драйвером) и БД
    SQLAlchemy: Engine
    25
    from sqlalchemy import create_engine
    engine = create_engine(
    'postgresql://user:hackme@0.0.0.0/db', echo=True
    )
    with engine.connect() as conn:
    # {sqlalchemy.engine.result.RowProxy} (1,)
    data = conn.execute('SELECT 1').fetchone()
    

    View Slide

26. Используется для общения с различными имплементациями
    DBAPI, драйверами и базами данных. 

    Из коробки поддерживает:
    〉PostgreSQL
    〉MySQL
    〉SQLite
    〉Oracle
    〉Microsoft SQL Server
    SQLAlchemy: Dialect
    26
    

    View Slide

27. with engine.connect() as conn:
    # {Insert}:
    # INSERT INTO users (email) VALUES (:email)
    # RETURNING users.user_id, users.email
    query = users_table.insert().values(
    email='john.smith@ya.ru'
    ).returning(users_table)
    # {RowProxy} (4, 'john.smith@ya.ru')
    data = conn.execute(query).fetchone()
    SQLAlchemy: добавление данных
    27
    

    View Slide

28. with engine.connect() as conn:
    query = users_table.insert().values([
    {'email': 'vim.diesel@ya.ru'},
    {'email': 'curl.cobain@ya.ru'}
    ]).returning(users_table)
    # INSERT INTO users (email)
    # VALUES (%(email_m0)s), (%(email_m1)s)
    # RETURNING users.user_id, users.email
    print(query.compile(engine))
    # : [
    # (5, 'vim.diesel@ya.ru'), (6, 'curl.cobain@ya.ru')
    # ]
    data = conn.execute(query).fetchall()
    SQLAlchemy: массовое добавление данных
    28
    

    View Slide

29. with engine.connect() as conn:
    # {Select}:
    # SELECT users.user_id, users.email \nFROM users
    query = users_table.select()
    # : [
    # (1, 'vim.diesel@ya.ru'),
    # (2, 'curl.cobain@ya.ru'), ...]
    data = conn.execute(query).fetchall()
    SQLAlchemy: получение данных
    29
    

    View Slide

30. # {Update}
    # UPDATE users SET email=:email
    # WHERE users.user_id = :user_id_1
    # RETURNING users.user_id, users.email
    query = users_table.update().values(
    {'email': 'vim.diesel@ya.ru'}
    ).where(
    users_table.c.user_id == 1
    ).returning(users_table)
    SQLAlchemy: обновление данных
    30
    

    View Slide

31. def get_users(is_deleted=False) -> Select:
    return users_table.select().where(
    users_table.c.is_deleted == is_deleted
    )
    def get_new_users() -> Select:
    date = datetime.now() - timedelta(days=2)
    return get_users().where(
    users_table.c.created_at >= date
    )
    Расширение и декомпозиция запросов
    31
    

    View Slide

32. Миграции с alembic
    

    View Slide

33. $ alembic init alembic
    Как начать использовать alembic?
    33
    При инициализации будут созданы следующие файлы
    〉alembic/env.py
    〉alembic/script.py.mako
    〉alembic/versions/
    〉alembic.ini
    

    View Slide

34. alembic.ini - файл c конфигурацей
    34
    [alembic]
    # path to migration scripts
    script_location = alembic
    # template used to generate migration files
    file_template = %%(year)d-%%(month).2d-%%(day).2d_%%(slug)s
    # version location specification; this defaults
    # to alembic/versions.
    sqlalchemy.url = postgresql://user:hackme@localhost/db
    # Logging configuration
    # ...
    

    View Slide

35. """${message}
    Revision ID: ${up_revision}, revises: ${down_revision |
    comma,n}
    Create Date: ${create_date}
    """
    import sqlalchemy as sa
    from alembic import op
    ${imports if imports else ""}
    revision = ${repr(up_revision)}
    down_revision = ${repr(down_revision)}
    branch_labels = ${repr(branch_labels)}
    depends_on = ${repr(depends_on)}
    script.py.mako - шаблон для новых миграций
    35
    

    View Slide

36. def upgrade():
    ${upgrades if upgrades else "pass"}
    def downgrade():
    ${downgrades if downgrades else "pass"}
    script.py.mako - шаблон для новых миграций
    36
    

    View Slide

37. from logging.config import fileConfig
    from alembic import context
    from staff import schema
    config = context.config
    fileConfig(config.config_file_name)
    # Объект MetaData, к которому подключены модели/таблицы
    target_metadata = schema.metadata
    if context.is_offline_mode():
    run_migrations_offline()
    else:
    run_migrations_online()
    alembic/env.py - кастомизация
    37
    Необходимо подключить объект MetaData, который
    содержит всю информацию о таблицах (и др. сущностях)
    проекта
    

    View Slide

38. config = context.config
    target_metadata = models.Base.metadata
    def run_migrations_online():
    engine = engine_from_config(
    config.get_section(config.config_ini_section), ...
    )
    with engine.connect() as connection:
    context.configure(
    connection=connection,
    target_metadata=target_metadata
    )
    with context.begin_transaction():
    context.run_migrations()
    alembic/env.py: режим “онлайн”
    38
    

    View Slide

39. config = context.config
    target_metadata = models.Base.metadata
    def run_migrations_offline():
    url = config.get_main_option("sqlalchemy.url")
    context.configure(
    url=url,
    target_metadata=target_metadata,
    ...
    )
    with context.begin_transaction():
    context.run_migrations()
    alembic/env.py: режим “оффлайн”
    39
    

    View Slide

40. alembic revision --message="Initial" --autogenerate
    Как сгенерировать миграции?
    40
    Эта команда выполнит следующее:
    〉Получит схему существующей БД, последнюю примененную миграцию
    (если есть)
    〉Проанализирует разницу между описанными сущностями в проекте
    (таблицы, индексы, типы данных и т.п.) и в БД
    〉Сгенерирует новый файл (на основе script.py.mako) с DDL, который
    попытается привести БД в одинаковое состояние с объектом MetaData
    

    View Slide

41. """Initial
    Revision ID: 058939c545ff
    Revises:
    Create Date: 2019-10-30 15:18:22.209941
    """
    from alembic import op
    import sqlalchemy as sa
    # revision identifiers, used by Alembic.
    revision = '058939c545ff'
    down_revision = None
    branch_labels = None
    depends_on = None
    Миграция (часть 1)
    41
    

    View Slide

42. def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('users',
    sa.Column('user_id', sa.Integer(), nullable=False),
    sa.Column('email', sa.String(length=256), nullable=False),
    sa.PrimaryKeyConstraint('user_id', name=op.f('pk__offers')),
    sa.UniqueConstraint('email', name=op.f('uq__offers__email'))
    )
    # ### end Alembic commands ###
    def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_table('users')
    # ### end Alembic commands ###
    Миграция (часть 2)
    42
    

    View Slide

43. Для применения всех доступных миграций необходимо выполнить
    команду alembic upgrade head
    Применение миграций
    43
    $ alembic upgrade head
    INFO [alembic.runtime.migration] Context impl PostgresqlImpl.
    INFO [alembic.runtime.migration] Will assume transactional DDL.
    INFO [alembic.runtime.migration] Running upgrade -> 058939c545ff,
    Initial
    

    View Slide

44. Если приложение on-premises или доступ к нему ограничен - можно
    и нужно накатывать миграции автоматически, например при запуске
    приложения
    Если у вас важный сервис, к которому имеется доступ - накатывание
    миграций вручную дает больший контроль и позволяет оперативно
    реагировать на проблемы
    Когда и как применять миграции?
    44
    

    View Slide

45. Если вам больше не нужна большая таблица или столбец и вы хотите их
    удалить без возможности восстановить данные - не стоит их удалять сразу
    Убрать все обращения к ресурсам в коде как будто их не существует
    Столбцы / таблицы / типы данных стоит пометить специальным
    декоратором, который будет сообщать о доступе к ресурсам, которые
    должны быть удалены
    Создать задачу в бэклог на последующее удаление
    Как быть с необратимыми изменениями?
    45
    

    View Slide

46. Зачем и как тестировать
    миграции?
    

    View Slide

47. Применение миграции в production всегда сопряжено с
    риском
    Production БД обычно огромны, стары и полны сюрпризов
    БД используемые при разработке и тестировании, как
    правило, меньше и чище. Данные в них лучше понимаются
    или, если все остальное не удается, объем данных достаточно
    мал для обработки человеком
    Зачем тестировать миграции?
    47
    

    View Slide

48. Поврежденные данные, которые были написаны старыми версиями
    программного обеспечения и не очищены должным образом
    Подразумеваемые зависимости в данных, о которых больше никто не
    знает
    Люди непосредственно меняют базу данных без использования
    назначенных инструментов
    Ошибки в инструментах миграции схем
    Ошибки в предположениях о том, как следует переносить данные
    Из-за чего могут возникнуть проблемы?
    48
    

    View Slide

49. Миграция запускается, в коде нет глупых опечаток
    Написан корректный downgrade
    В downgrade() удаляются все созданные типы данных, которые
    SQLAlchemy создала автоматически в upgrade()
    Если миграция меняет какие-либо данные - что данные
    корректно изменяются в методе upgrade() и что эти изменения
    полностью откатываются в downgrade() без артефактов
    Что мы можем проверить?
    49
    

    View Slide

50. На каждый тест лучше использовать отдельную БД, это позволяет
    полностью изолировать тесты друг от друга и в перспективе использовать
    параллельный запуск тестов.
    sqlalchemy_utils поможет в создании и удалении БД. Эта библиотека
    учитывает, что к БД может быть кто-то подключен и перед удалением
    отключит всех клиентов.
    Как подготовить базу?
    50
    

    View Slide

51. @pytest.fixture
    def temp_db() -> str:
    tmp_db_name = '.'.join([uuid.uuid4().hex, 'pytest'])
    tmp_db_url = str(URL(DB_URL).with_path(tmp_db_name))
    create_database(tmp_db_url)
    try:
    yield tmp_db_url
    finally:
    drop_database(tmp_db_url)
    Как подготовить базу?
    51
    

    View Slide

52. @pytest.fixture()
    def temp_db_engine(temp_db) -> Engine:
    engine = create_engine(temp_db, echo=True)
    try:
    yield engine
    finally:
    engine.dispose()
    Как подготовить Engine?
    52
    

    View Slide

53. Тест получает список всех миграций и итерируется по ним.
    Для каждой он вызывает upgrade(), downgrade() и еще раз
    upgrade().
    Простой, но эффективный метод проверить - полностью ли
    миграция откатила изменения. Можно добавить в проект 1 раз
    и забыть.
    Stairway тест
    53
    

    View Slide

54. Stairway тест
    54
    5
    4
    3
    2
    1 1 1 1
    2
    1
    2
    1
    2
    1
    3
    2
    1
    3
    2
    1
    3
    2
    1
    4
    3
    2
    1
    4
    3
    2
    1
    4
    3
    2
    1
    5
    4
    3
    2
    1
    5
    4
    3
    2
    1
    5
    4
    3
    2
    1
    

    View Slide

55. from tests.conftest import DB_URL
    from staff.testing import get_revisions, get_alembic_config
    REVISIONS = list(get_revisions(DB_URL))
    MODULE_PATH = os.path.dirname(os.path.dirname(__file__))
    @pytest.mark.parametrize('rev_index', reversed(range(len(REVISIONS))))
    def test_migrations_stairway(temp_db_engine: Engine, rev_index: int):
    revision = REVISIONS[rev_index]
    config = get_alembic_config(str(temp_db_engine.url))
    upgrade(config, revision.revision)
    downgrade(config, revision.down_revision or '-1')
    upgrade(config, revision.revision)
    Stairway тест
    55
    

    View Slide

56. Если в миграции не просто добавляется новый столбец или
    таблица, а каким-либо образом меняются существующие
    данные - то ошибка может иметь самые серьезные
    последствия
    Обычно такие миграции кажутся чем-то простым, но по опыту
    именно в них бывает очень много багов, которые очень
    сложно обнаружить
    Дорогой в разработке, но очень надежный
    Тестирование миграций с данными
    56
    

    View Slide

57. Применяются все миграции до тестируемой (не
    включительно)
    Создается набор данных, который будет изменен тестируемой
    миграцией
    Выполняется upgrade и проверяется, что все данные были
    корректно изменены
    Выполняется downgrade до предыдущей миграции,
    проверяется, что все изменения были корректно отменены
    Тестирование миграций с данными
    57
    

    View Slide

58. @pytest.mark.parametrize(...)
    def test_data_migrations(
    temp_db_engine: Engine, rev_base: str, rev_head: str,
    on_init: Callable, on_upgrade: Callable, on_downgrade: Callable
    ):
    """
    Check data is migrated correctly in "data" migrations.
    """
    config = get_alembic_config(str(temp_db_engine.url))
    upgrade(config, rev_base)
    on_init(engine=temp_db_engine)
    upgrade(config, rev_head)
    on_upgrade(engine=temp_db_engine)
    downgrade(config, rev_base)
    on_downgrade(engine=temp_db_engine)
    Тестирование миграций с данными
    58
    

    View Slide

59. На что обратить внимание:
    〉staff-db - обертка над alembic, которую
    можео зывывать из любой папки
    Где посмотреть пример
    59
    

    View Slide

60. alvassin@yandex-team.ru
    Васин Александр
    Разработчик программного
    обеспечения
    Спасибо за внимание
    

    View Slide