Миграции и декларативное управление схемой

Когда вы разрабатываете какой-то проект в одиночку, обычно в процессе разработки не возникает вопросов о том, как управлять схемой базы данных. Если вам нужно добавить новый столбец в таблицу, вы можете просто сделать это в своей локальной базе данных без каких либо последствий. Возможно, вы даже не думали об этом, потому что ваша ORM автоматически синхронизирует схему базы данных (хотя рекомендуется отключать эту синхронизацию в production, чтобы избежать безвозвратного удаления данных [подробнее ниже про переименование]).

После того, как вы сделали первый релиз, каждое изменение должно быть применено не только к вашей локальной базе данных, но и к базам в других средах (staging, production и др). Если схема базы данных может быть изменена по крайней мере 2 людьми, то даже во время разработки вы должны организовать ее синхронизацию между членами вашей команды.

Чтобы внести изменения в базу данных, вы можете использовать:

Миграции (императивное управление схемой).
Декларативное управление схемой.

Миграции

Самый популярный способ – создавать новый файл каждый раз, когда вы хотите сделать серию изменений в базе данных. Чаще всего это добавление новых столбцов, изменение enum полей, изменение значений по умолчанию и добавление индексов. Некоторый скрипт применяет эти миграции один за другим в том порядке, в котором они были созданы. Локально вы должны запустить этот скрипт вручную, но в production (и других средах) это должно быть сделано с использованием CI/CD автоматически.

Список файлов миграции может выглядеть следующим образом:

0001_initial.sql
0002_add_full_name_to_users.sql
0003_create_sales_table.sql

Первый определяет начальную схему базы данных, остальные последовательно вносят изменения. Например, начальная миграция создает таблицу users:

-- 0001_initial.sql
CREATE TABLE users (
  id serial PRIMARY KEY,
  first_name text NOT NULL,
  last_name text NOT NULL
);

Вторая миграция добавляет новую колонку full_name:

-- 0002_add_full_name_to_users.sql
ALTER TABLE users ADD COLUMN full_name text NOT NULL
  GENERATED ALWAYS AS (first_name || ' ' || last_name) STORED;

Порядковый номер или временная метка

В качестве префикса в имени файла может использоваться как порядковый номер (0001, 0002 и т.д.), так и временная метка (20230302102511 [<гггг><ММ><дд><ЧЧ><мм><сс>]). У каждого есть свои преимущества и недостатки.

При использовании временной метки 2 разработчика могут создать новый файл миграции одновременно. У каждого файла миграции будет уникальный префикс, если они не были созданы в одну и ту же секунду, что крайне маловероятно. Напротив, при использовании порядкового номера, новый файл миграции будет иметь один и тот же префикс (N+1).

Однако, в некоторых случаях при использовании временной метки может возникнуть проблема. Например, давайте представим, что Алексей создал миграцию, которая переименовывает колонку old_name в new_name, а Настя создала миграцию на пару секунд позже, в которой меняется тип колонки old_name. Алексей зальет свои изменения в удаленный репозиторий и CI/CD переименует колонку old_name в new_name в production. После этого Настя получит изменения Алексея из удаленного репозитория, сольет со своей веткой без конфликтов (файл миграции Алексея ведь с другим названием, т.к. используется временная метка) и после этого без проблем зальет все изменения в удаленный репозиторий. Ошибка произойдет только в тот момент, когда CI/CD попытается накатить миграцию Насти к базе данных в production (колонки old_name не существует уже).

Если в данной ситуации использовать порядковый номер в имени файлов миграций, то когда Настя получит изменения Алексея из удаленного репозитория, то увидит конфликт (файл с таким названием уже существует). В этом случае она увидит, что колонка уже была переименована, создаст новый файл миграции со следующим порядковым номером и скорректирует свой SQL-запрос.

В данном примере при использовании временной метки Настя обнаружила проблему на этапе накатывания миграции к базе данных в production. Но что будет, если ошибки не произойдет? Скажем, Алексей трансформировал данные определенной колонки и Настя сделала то же самое. Получается колонка будет преобразована дважды и никто не увидит никакого конфликта или ошибки.

Чтобы избежать подобных проблем, нужно использовать либо порядковый номер, либо тщательно делать code review.

Запускайте миграции внутри транзакций

Если во время выполнения миграции один из SQL-запросов выбросит ошибку, то все выполненные до него запросы должны откатиться назад. Этого можно добиться, если выполнять миграции внутри транзакций.

Таким образом, либо миграция будет выполнена полностью, либо она не будет выполнена совсем.

Миграции, откатывающие изменения

Обычно инструменты миграции позволяют определить 2 сценария для каждого изменения в базе данных: up и down. Первый применяет изменения, второй откатывает их назад.

-- 0002_add_full_name_to_users.up.sql
ALTER TABLE users ADD COLUMN full_name text NOT NULL
  GENERATED ALWAYS AS (first_name || ' ' || last_name) STORED;

-- 0002_add_full_name_to_users.down.sql
ALTER TABLE users DROP COLUMN full_name;

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

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

Таким образом, скорее всего, написание down миграций будет пустой тратой вашего времени. Лучше более тщательно протестировать up миграции.

Язык

Файл миграции может быть написан не только на чистом SQL, но и с использованием конструкторов запросов на любом языке. Это зависит от используемого инструмента миграции. Например, Knex имеет встроенный инструмент, который считывает файлы миграции, написанные на JavaSciprt/TypeScript, которые содержат 2 экспортируемые функции: up и down.

// 20230302102511_add_full_name_to_users.ts
import { Knex } from 'knex';

export const up = async (knex: Knex) =>
  knex.schema.alterTable('tasks', (table) => {
    table.text('full_name');
  });

export const down = async (knex: Knex) =>
  knex.schema.alterTable('tasks', (table => {
    table.dropColumn('full_name');
  }));

Но лучше писать миграции на SQL, потому что:

Не будет никаких непредсказуемых SQL-запросов, сгенерированные конструкторами запросов (напр, Knex).
SQL гораздо проще и быстрее читается, когда вы его знаете (и вы должны его знать, если работаете с базой данных).
Когда вы или член вашей команды будете разрабатывать новый проект на другом языке (напр, Python), вам следует либо добавить поддержку миграций на этом языке и подготовить/поддерживать для них отдельные скрипты CI/CD, либо писать миграции для этого проекта на другом языке (что, если разработчик, который пишет новый проект на Python, не знает TypeScript, который используется для написания миграций).

Миграции без простоя проекта

Некоторые миграции выполняются за несколько секунд, некоторые могут занимать много часов. Если процесс миграции выполняется перед запуском проекта, то оно не будет доступно до завершения миграции. Кроме того, если во время миграции возникнет ошибка, ваш проект не запустится. Таким образом, миграции должны выполняться отдельно.

Чтобы выполнить миграцию без простоя проекта, вам нужно:

Применить первую миграцию (aka regular migration), которая будет совместима с текущей версией кода. Например, добавить новую колонку full_name, но пока не удалять колонки first_name и last_name. Текущая версия кода будет продолжать использовать first_name и last_name.
Развернуть новую версию кода, которая использует колонку full_name вместо first_name и last_name.
Применить вторую миграцию (aka post-deployment migration), которая удалит колонки first_name и last_name.

Этот процесс может может немного усложниться, например, если вы хотите использовать таблицу с другой структурой. Для этого нужно:

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

Недостатки миграций

1. Огромное количество файлов.

Через некоторое время у вас накопится достаточно много файлов с миграциями (0001_create_users_table.sql, 0002_add_full_name_to_users.sql, ..., 5372_create_sales_table.sql). Это приведет к тому, что развертывание локальной базы данных будет занимать гораздо больше времени.

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

Данную проблему можно решить, время от времени схлопывая миграции в один файл, делая дамп текущей версии схемы (напр, используя pg_dump -s в PostgreSQL). Это можно выполнять всякий раз, когда количество миграций превышает определенное значение.

2. Нет полной схемы.

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

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

Данную проблему можно решить, делая дамп текущей схемы после каждого применения новых миграций. В итоге вы сможете:

Просматривать всю схему базы данных.
Смотреть изменения схемы с помощью git diff.
Быстро найти тех, кто внес то или иное изменение, и выяснить когда это было сделано и почему, используя git blame.

3. Изменения вручную -> неожиданные результаты.

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

Эту проблему можно решить, сохраняя хэш схемы после выполнения каждой миграции и проверяя его перед применением новой миграции. Если текущая схема не равна схеме, которая была на момент последней примененной миграции, то должна произойти ошибка.

Самые популярные инструменты для выполнения миграций: flyway, liquibase, sqitch, dbmate, nomad.

Декларативное управление схемой

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

Это может быть сделано с помощью diff tools, таких как skeema (MySQL, MariaDB), migra (PostgreSQL), atlas (PostgreSQL, MySQL, MariaDB, SQLite), sqldef (PostgreSQL, MySQL, SQLite, SQL Server) и др. Этот подход используется в Facebook более 10 лет и другими компаниями (напр, SendGrid).

Вы можете просто работать с файлом вашей схемы, добавляя новые столбцы, изменяя значения по умолчанию и т.д. Скрипт автоматически внесет изменения в базу данных, чтобы достичь этого состояния.

Такой подход устраняет все недостатки, о которых я упомянул ранее:

Существует только один файл со всей схемой (или несколько – по одному для каждой таблицы для удобства). Таким образом, вы можете развернуть новую базу данных за миллисекунды (для разработки или тестов).
Т.к. вся схема хранится в одном файле, то вы можете посмотреть какие столбцы находятся в конкретной таблице или каково значение по умолчанию в определенном столбце. Используя git diff, вы можете посмотреть изменения, внесенные в схему, а с помощью git blame увидеть, кто внес эти изменения, когда и почему.
Вы можете вносить изменения в базу данных вручную. Например, если вы добавите новый столбец в файл со схемой, но этот столбец уже существует в базе данных в production, изменений не произойдет.

Переименование

В данный момент инструменты декларативного управления схемой не в состоянии понять, что было сделано: столбец был переименован или удален и создан новый. Если вы переименуете, то будет сгенерирован DROP и CREATE. Данные будут потеряны. Поэтому, если вы хотите что-то переименовать, используйте миграции.

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

Например, если вы хотите переименовать столбец с помощью миграций без простоя проекта, нужно:

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

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

Никаких изменений без вашего одобрения

Когда вы изменяете файл с вашей схемой, вы отправляете его в удаленный git-репозиторий. Скрипт CI/CD определяет различия между схемой базы данных и схемой в файле с помощью diff tool, а затем создает новый pull request с прикрепленными SQL-запросами, которые будут применены после вашего подтверждения. Если все в порядке, вы подтверждаете и CI/CD применяет эти изменения к базе данных. Любые DROP запросы должны требовать дополнительного подтверждения. Это позволит избежать потери данных.

Таким образом, все изменения в базе данных в продакшене будут находиться под вашим контролем.

Миграция данных

Используемые сокращения:

DDL (Data Definition Language) используется для управления схемой (CREATE, ALTER, DROP).
DML (Data Manipulation Language) используется для управления данными (SELECT, INSERT, DELETE, UPDATE).

Инструменты декларативного управления схемой генерируют только DDL. Если вы хотите выполнить DML запросы, используйте миграции. Убедитесь, что ваш CI/CD скрипт запускает все в правильном порядке: DDL -> DML -> развертывание кода.

Иногда необходимо сохранить порядок выполнения DDL и DML. Например, commit_1 создает новый столбец (DDL), commit_2 заполняет данные в этом столбце (DML), commit_3 удаляет этот столбец (DDL). Если инструкции DDL выполняются инструментом декларативного управления схемой, а DML выполняются миграциями, то DML-запросы выдадут ошибку, поскольку столбец не будет существовать. Чтобы сохранить порядок этих изменений, используйте миграции и для DDL, и для DML.

Рабочий процесс

Development environment

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

Запускайте синхронизацию вашей схемы перед запуском проекта и делайте это всякий раз, когда вы изменяете файл со схемой.

Production environment

Используйте декларативный подход для изменения схемы и миграции для переименования чего-либо и манипулирования данными.

Любые изменения схемы базы данных в production должны проходить ручную проверку. Весь процесс может выглядеть следующим образом.

Проверить, была ли изменена схема. Это нужно делать каждый раз, а не только при изменении файла schema.sql, поскольку база данных в production могла быть изменена вручную.

Если схема не была изменена, тогда CI/CD pipeline должен собрать проект, протестировать и развернуть его.

Если схема была изменена (на данный момент код должен поддерживать как текущую схему, так и новую), то:

Получить изменения схемы, используя инструмент декларативного управления схемой. Например, используя dsm, выполнить команду diff. В итоге вы получите список SQL запросов, выполнив которые, схема вашей базы данных в production станет такой же, какая она задана в локальном файле (напр, schema.sql или schema/*.sql).
Создать новую ветку (напр, change-schema-<timestamp>), добавить файл с SQL запросами, преобразующие схему (напр, pending.sql), сделать коммит, запушить в удаленный репозиторий и создать merge request. GitLab CI позволяет создать merge request автоматически. Для этого нужно указать дополнительные аргументы в push: merge_request.create, merge_request.target, merge_request.title. Подробнее тут.
При каждом изменении кода в процессе merge request, должен запускаться pipeline, который проверяет pending.sql и тестирует код на текущей схеме, который используется в production, и новой. Если все в порядке, то можно разрешить слияние.
При слиянии еще раз проверять pending.sql и тестировать код на текущей и новой схеме. Это необходимо сделать еще раз, т.к. может быть выполнено слияние, не смотря на то, что pipeline на предыдущем шаге выполнился с ошибкой.
Развернуть проект и выполнить все SQL запросы из pending.sql.
Удалить файл pending.sql, сделать коммит и пуш в удаленный репозиторий.
Удалить из кода поддержку старой схемы. Это запустит pipeline, который описан выше (если схема не была изменена).