Comments
Миграции надо писать только на SQL, никаких генераторов. Применять инструменты типа yoyo-migrations, чтобы просто следили за порядком выполнения миграций.

Попробуйте сделать такую миграцию на alembic:
INSERT INTO table2 SELECT * FROM table1 WHERE condition;

Ой, оказывается alembic это про миграцию схемы, а не про миграцию данных.

Вот задали вопрос по миграции. И того решение задачи 36 loc, против 10 (создание таблиц на чистом sql) и 2 (insert, update для данных в задаче) и 2 (drop-ы для downgrade).

А как дела обстоят с хранимыми процедурами/функциями и триггерами? Да никак. Короче в любой сложной ситуации будет op.execute('...') c чистым SQL внутри. И получаеться что кроме SQL еще надо знать особенности работы этой утилиты для миграции.
Миграции надо писать только на SQL, никаких генераторов. Применять инструменты типа yoyo-migrations, чтобы просто следили за порядком выполнения миграций.

Звучит достаточно категорично, а ведь вы не привели ни одного аргумента.

Во-первых, миграции на чистом SQL нужно писать, на мой взгляд это уже минус. Нужно думать о зависимостях, порядке выполнения запросов, едином именовании constraint-ов. Alembic автоматизирует много рутинных задач — пробежаться глазами по готовой миграции сильно быстрее, чем писать код с нуля.

Во-вторых, миграции бывают большими и сложными. Читать декомпозированный код на Python сильно проще чем смесь из Python и SQL запросов, которые декомпозируются только конкатенацией строк. Мне приходилось поддерживать написанные на yoyo миграции, я бы не назвал их поддержку приятным опытом.

В-третьих, инструментов БД для обработки данных бывает недостаточно: необходимо получить данные из базы, обработать с помощью Python и записать обратно. Alembic даже предлагает для этого два режима выполнения миграций — online (миграция выполняется с живым подключением к серверу) и offline (из миграции можно сгенерировать SQL запросы для последующего выполнения).

Воспользоваться готовыми хорошо протестированными инструментами на Python в online-миграциях выглядит гораздо более разумным решением, чем переносить логику в хранимку, которую нужно будет отдельно поддерживать, писать на нее отдельно тесты (вы же ответственный разработчик и пишете тесты на ваш код?).

В-четвертых, существуют проекты, рассчитанные на разные инсталляции (например, для on-premises инсталляций — SQLite, для разворачивания в облаке — PostgreSQL). У SQLite есть свои особенности с ALTER TABLE, например. Alembic предлагает решение для подобных задач.

Мне приходилось работать и с такими проектами, где из-за 1 опечатки в миграции продукты клиентов могут перестать работать и им потребуется выпускать и доставлять апдейты, что сопряжено с огромными репутационными и денежными издержками. Alembic, в свою очередь позволил писать достаточно краткий, лаконичный, поддерживаемый код и не подвел ни разу.
Подводя итог: Alembic и SQLALchemy предланают много надежных, проверенных временем решений для широкого круга задач, тогда как yoyo решает только одну задачу с цепочкой миграций. Выполнять ли рутинную работу самому или поручить ее программам — это уже личный выбор каждого. Я бы не назвал yoyo-миграции современным решением.

Попробуйте сделать такую миграцию на alembic:
INSERT INTO table2 SELECT * FROM table1 WHERE condition;

В SQLAlchemy есть метод Insert.from_select
Пример
from sqlalchemy import (
    Column, Integer, MetaData, String, Table, select,
)


metadata = MetaData()

table1 = Table(
    'table1',
    metadata,
    Column('id', Integer, primary_key=True),
    Column('name', String)
)

table2 = Table(
    'table2',
    metadata,
    Column('id', Integer, primary_key=True),
    Column('name', String)
)

select_stmt = select([table1.c.id, table1.c.name]).where(table1.c.id > 5)
insert_stmt = table2.insert().from_select(['id', 'name'], select_stmt)

print(insert_stmt)

Сгенерирует желаемый вами SQL:

INSERT INTO table2 (id, name) SELECT table1.id, table1.name 
FROM table1 
WHERE table1.id > :id_1

Ой, оказывается alembic это про миграцию схемы, а не про миграцию данных.

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

А как дела обстоят с хранимыми процедурами/функциями и триггерами? Да никак.

Мы не используем хранимые процедуры, в нашей команде это запрещено.

Короче в любой сложной ситуации будет op.execute('...') c чистым SQL внутри.

Alembic предлагает разработчикам целый ряд механизмов (тот же batch для SQLite), а также query builder Алхимии, на котором можно хоть рекурсивные запросы описать.
Во-первых, миграции на чистом SQL нужно писать, на мой взгляд это уже минус. Нужно думать о зависимостях, порядке выполнения запросов, едином именовании constraint-ов. Alembic автоматизирует много рутинных задач — пробежаться глазами по готовой миграции сильно быстрее, чем писать код с нуля.

А на мой взгляд миграции на чистом SQL это нормально и в большинстве это плюс. SQL можно генерит кучей инструментов от консольных, до графических. Можно с существующей базы взять дамп и положить как init миграцию. С constraint-ами вообще ни разу не проблема, один раз придумал форма именования и пользуйся, обычно это что-то типа "_".join((«field1», «field2»,… ,«fieldn», «check»)), где field1, fieldn — это поля что есть в constraint-е. Но у вас в случае чего есть возможность назвать нормально, вместо start_date_finish_date_check — может быть start_finish_date_check или start_date_lt_finish_date_check. Да надо думать про порядок запросов, и это плюс что никакая туловища не поменяет этот порядок.

Во-вторых, миграции бывают большими и сложными. Читать декомпозированный код на Python сильно проще чем смесь из Python и SQL запросов, которые декомпозируются только конкатенацией строк. Мне приходилось поддерживать написанные на yoyo миграции, я бы не назвал их поддержку приятным опытом.

Да бывают, но SQL читается проще, чем Python. И я нигде не говорил что надо мешать код на Python и SQL, наоборот я сказал используя Alembic, в любой сложной ситуации (нету поддержки нужной функциональности) вам и прийдется использоваться op.execute('...') c чистым SQL, что и порождает смесь. С любым инструментом можно устроить лапшу кода, и в этом yoyo ничем не отличается от Alembic.

В-третьих, инструментов БД для обработки данных бывает недостаточно: необходимо получить данные из базы, обработать с помощью Python и записать обратно. Alembic даже предлагает для этого два режима выполнения миграций — online (миграция выполняется с живым подключением к серверу) и offline (из миграции можно сгенерировать SQL запросы для последующего выполнения).

Да бывает, но это настольно редкий случай. Специально посмотрел на проекте чуть больше чем 600 миграций(и все на чистом SQL) и только 1-на, в которой надо было вызывать методы для каждой записи в таблице и писать в новое(созданное) поле. И то это можно было решить по другому, просто заполнять поле если оно пустое при обращении и записи, и потом через месяц просто сделать SET NOT NULL для этого нового поля.

Воспользоваться готовыми хорошо протестированными инструментами на Python в online-миграциях выглядит гораздо более разумным решением, чем переносить логику в хранимку, которую нужно будет отдельно поддерживать, писать на нее отдельно тесты (вы же ответственный разработчик и пишете тесты на ваш код?).

Я нигде не предлагал хранимками устраивать миграции, не приписывайте мне этот бред. Я утверждал что как только дело касается создания хранимок, вьюх данный инструмент сразу переходит на чистый SQL в виде op.execute('CREATE FUNCTION...') или же в виде
to_upper = PGFunction(
schema='public',
signature='to_upper(some_text text)'
definition="""
RETURNS text as
$$
SELECT upper(some_text)
$$ language SQL;
"""
)

Этот шедевр взят отсюда

В-четвертых, существуют проекты, рассчитанные на разные инсталляции (например, для on-premises инсталляций — SQLite, для разворачивания в облаке — PostgreSQL). У SQLite есть свои особенности с ALTER TABLE, например. Alembic предлагает решение для подобных задач.

Вот это единственный плюс с которым я соглашусь.

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

Страшилка для джунов. Если миграция в транзакции, то от опечатки просто упадет транза.
Причем для того чтобы такое получилось это надо, прям в консоле продовой базы без транзы, копипастой вставить НЕПРОТЕСТИРОВАННУЮ миграцию. Одним словом шедевральный аргумент.

Подводя итог: Alembic и SQLALchemy предланают много надежных, проверенных временем решений для широкого круга задач, тогда как yoyo решает только одну задачу с цепочкой миграций. Выполнять ли рутинную работу самому или поручить ее программам — это уже личный выбор каждого. Я бы не назвал yoyo-миграции современным решением.

А я утверждаю что чистого SQL и любого инструмента который умеет выполнять цепочку миграций для 99% случаев достаточно. Даже больше скажу, что этот инструмент не должен уметь downgrade.

И ответ на вопрос с миграцией INSERT INTO table2 SELECT * FROM table1 WHERE condition;
19 строк, вместо 1-й — браво, это победа :)

А можно например посложнее задачку:
upgrade -> ALTER TYPE test_type ADD VALUE 'test';
downgrade -> нужно удалить test из test_type перечисления.

Я бы не возмущался, если бы в начале было бы написано, что нам надо инструмент который умеет много баз, но все не на 100%, так как мы не используем эти все возможности.

Да и я против Python в миграциях вообще.
Была же история с майлру, когда у них кластер сломался из-за того что в миграции конфигурации кластера поехало форматирование.
А на мой взгляд миграции на чистом SQL это нормально и в большинстве это плюс.

Я не говорю что это не нормально. Я лишь перечислил преимущества и недостатки разных подходов. Кто-то файлики с SQL запросами вручную по очереди применяет и кому-то это нравится. Каждый сам решает, что решает его задачу лучше.

Страшилка для джунов. Если миграция в транзакции, то от опечатки просто упадет транза.
Причем для того чтобы такое получилось это надо, прям в консоле продовой базы без транзы, копипастой вставить НЕПРОТЕСТИРОВАННУЮ миграцию. Одним словом шедевральный аргумент.

Речь шла как раз об on-premise инсталляциях. Доступа к продовой базе нет — она может находиться на другом конце света в бункере у клиента, без доступа к интернетам. «Откат транзы» означат, что инсталлятор не сможет обновить продукт у клиента. Это достаточно больно. Единственное, что может как-то застраховать от незапланированного выпуска нового инсталлятора — хорошо протестированные миграции. Alembic позволил решить эту задачу.

Была же история с майлру, когда у них кластер сломался из-за того что в миграции конфигурации кластера поехало форматирование.

Не нам их судить за то, что они не используют минимальное тестирование типа stairway-тестирования.

Кстати, а вы вообще пишете тесты на миграции? Как сделать тесты на миграции когда у вас миграции — это чистый SQL с upgrade, без downgrade (я уже молчу про rollback релизов, только катим вперед, сжигая мосты, ни шагу назад)? Как написать тесты на миграцию с данными?

А можно например посложнее задачку:
upgrade -> ALTER TYPE test_type ADD VALUE 'test';

Вообще-то в рамках транзакции такой запрос работать не будет — только через пересоздание типа данных (что документация, кстати и рекомендует):

...ALTER TYPE… ADD VALUE (the form that adds a new value to an enum type) cannot be executed inside a transaction block...

А гонять миграции без транзакций… ну такое себе.
Я не говорю что это не нормально. Я лишь перечислил преимущества и недостатки разных подходов. Кто-то файлики с SQL запросами вручную по очереди применяет и кому-то это нравится. Каждый сам решает, что решает его задачу лучше.

А я не согласен с вашими преимуществами и недостатками, у меня другой субъективный опыт в этих вопросах. Да каждый решает, но многие могут и прислушаться к «большому Яндексу» и вообще из требований к джунам выкинуть знание SQL.

Речь шла как раз об on-premise инсталляциях. Доступа к продовой базе нет — она может находиться на другом конце света в бункере у клиента, без доступа к интернетам. «Откат транзы» означат, что инсталлятор не сможет обновить продукт у клиента. Это достаточно больно. Единственное, что может как-то застраховать от незапланированного выпуска нового инсталлятора — хорошо протестированные миграции. Alembic позволил решить эту задачу.


Ну тоже такой себе аргумент. Есть куча моментов где может отвалиться инсталлятор и так, без всяких миграций. И «откат трансы» это как раз нормальное поведение в этом случае. Может у того клиента в базу ручками сходили и что-то поменяли. Для таких клиентов, правильным решением будет запросить дамп базы(через свою фирменную утилиту), без данных (только структуру), раскатить у себя, заполнить нагенеренными данными, и уже запускать инсталлятор для этой базы. И только потом это все отдать клиенту с инструкцией по обновлению.

Не нам их судить за то, что они не используют минимальное тестирование типа stairway-тестирования.

Кстати, а вы вообще пишете тесты на миграции? Как сделать тесты на миграции когда у вас миграции — это чистый SQL с upgrade, без downgrade (я уже молчу про rollback релизов, только катим вперед, сжигая мосты, ни шагу назад)? Как написать тесты на миграцию с данными?


Никто и не судит, это специфика Python, что логика зависит от форматирования. И что это форматирование может просто поменяться.

Миграция схемы и данных — это в каком-то виде конечный автомат, где просто идет переход из одного состояния в другое. Или перешло, или отвалилось. Вот и весь тест. Если при миграции данных где-то появилась ошибка из-за constraint то отвалиться вся транзакция. Что тут еще можно тестировать ума не приложу :)

Да и объясните мне как будет в вашем случае работать downgrade или rollback, если миграция данных не обратима (ну например массово обновили/удалили поле/данные или сделали агрегацию).

Вообще-то в рамках транзакции такой запрос работать не будет — только через пересоздание типа данных (что документация, кстати и рекомендует):

...ALTER TYPE… ADD VALUE (the form that adds a new value to an enum type) cannot be executed inside a transaction block…

А гонять миграции без транзакций… ну такое себе.

Я прекрасно понимаю что ALTER TYPE не сработает в транзакции. Но не понимаю почему вы решили что вся миграция будет без транзакции. Миграция может быть из двух файлов подряд, или же просто ALTER TYPE вынесен за блок транзакции в одном файле.
А почему не классические Liquibase или Flyway?
Я не работал с этими инструментами, но на первый взгляд они уступают по возможностям Alembic.

Alembic позволяет в миграциях написать любой Python/SQL код, декомпозировать запросы, использовать практически любые абстракции и любую обработку данных (обрабатывать данные на стороне базы или на стороне Python) и в большинстве случаев делает всю рутину за вас (генерирует миграции, следит за именованием шаблонов и др).

SQLAlchemy и Alembic — это Python-библиотеки, которые легко описать как зависимости и установить стандартными средствами. Вы можете использовать любой их API, расширять эти инструменты, написать свою программу для управления состоянием базы или написать разные тесты для ваших миграций. Их можно использовать в рамках 1 процесса и потока вашей программы.

Liquibase предлагает описывать миграции в виде XML (также поддерживаются YAML, JSON и SQL), технически возможности этого инструмента сильно ограничены и самый простой способ их расширить — писать код на SQL со всеми вытекающими. Поставляется в виде бинарника, который нужно устанавливать отдельно и запускать как отдельный процесс. Также смутило, что Liquibase добавляет две таблицы DATABASECHANGELOG и DATABASECHANGELOGLOCK, хотя большинство инструментов, с которыми я работал, вполне обходятся одной.

На мой взгляд, Liquibase по возможностям не сильно превосходит yoyo: она решает ту же задачу построения цепочки миграций, и хотя теоретически существующие change types могут поддерживать разные DSL инструкции для разных СУБД, их набор ограничен примитивными операциями, любая более-менее сложная обработка данных сведется к написанию больших объемов SQL (при этом yoyo устанавливается стандартными средствами и его API можно использовать в программах и тестах на Python, для его вызова из Python-программы не требуется создавать отдельный процесс).

Flyway также написан на Java и в ряде случаев платный (dry-run и undo платные?). Не предоставляет query builder-а, не умеет из коробки обрабатывать задачи типа ограниченного ALTER TABLE в SQLite.

Зачем, если есть бесплатные, мощнейшие инструменты с открытым исходным кодом и поддержкой не хуже платных инструментов (в мейлинг-листах отвечают достаточно шустро, можно бесплатно задать вопрос лично главному разработчику алхимии — Майку Байеру).
SQLAlchemy и Alembic — это Python-библиотеки, которые легко описать как зависимости и установить стандартными средствами

И как их использовать в приложении на java/go/etc?

При желании можно отдельно описать на python модели/таблицы и использовать отдельно. У нас есть сервисы на go, разработчики которых используют миграции Alembic.
Респект автору! Затащил уже в два проекта лестничные тесты миграций. Нашёл много ошибок. Больше всего, конечно, в downgrade, но кое-где пришлось и upgrade править.

> Также смутило, что Liquibase добавляет две таблицы DATABASECHANGELOG и DATABASECHANGELOGLOCK, хотя большинство инструментов, с которыми я работал, вполне обходятся одной.

Вопрос, а как в alembic решается вопрос блокировки параллельных миграций. То есть, если стартуется две миграции плюс/минус одновременно (например, стартуется одновременно несколько тест-контейнеров). В Liquibase таблица 'DATABASECHANGELOGLOCK' как раз эту проблему и решает. А как быть с alembic?
А как решаете вопрос с блокировками для долгих и тяжелых миграций в Alembic?
Only those users with full accounts are able to leave comments. Log in, please.