Версионирование схемы БД бизнес-приложения — практика миграций, чтобы у клиентов не оказалось «у каждого своя база»

«В базе, которую ставили компании A, этот столбец есть, а в базе компании B его нет. И уже никто не вспомнит, в какой версии его добавили» — если вам передают сопровождение бизнес-приложения, которое ставят отдельно у каждого клиента, с большой вероятностью вы упрётесь именно в это.

В инструкции по обновлению написано: «выполните этот SQL на базе». Был ли он реально выполнен, знает только тот, кто работал на месте. Со временем среди клиентов накапливается смесь: где-то шаг забыли, где-то он упал с ошибкой на середине и так и остался, где-то при обновлении перескочили через версию и потеряли промежуточный ALTER TABLE. Спустя годы трудозатраты бесконечно уходят на расследование «ошибки, которая бывает только у этого одного клиента».

В этом блоге мы уже разбирали минимальную форму версионирования схемы в статье «Как выбрать место хранения данных Windows-приложения» и проектирование эксплуатации SQLite в статье «Использование SQLite в бизнес-приложениях на C#». Эта статья — продолжение: как версионировать изменения схемы БД и безопасно применять их к множеству баз, разбросанных по клиентам. Основной материал — SQLite, но то же проектирование обобщается и на SQL Server (Express).

1. Сначала вывод

• Изменения схемы поставляйте не как SQL-инструкцию, а как код (пронумерованные миграции), упакованный прямо в приложение, и применяйте автоматически при запуске. Любой процесс, в котором человек должен выполнить инструкцию, ломается в тот момент, когда БД оказывается разбросана по клиентам.
• Сама БД должна хранить текущую версию схемы. В SQLite PRAGMA user_version — как раз область, зарезервированная для этой цели.¹ В SQL Server историю применения ведут в отдельной таблице.
• Миграции идут только вперёд и только дописываются. SQL под уже выпущенным номером никогда не переписывается — исправление вносится под новым номером. Тогда даже обновление с пропуском версий, с v1.2 на v1.5, сводится к «просто применить по порядку то, что ещё не применено».
• Несовместимые изменения (удаление столбца, переименование) делают двухэтапным релизом expand-contract. Expand-contract — это двухэтапный релиз: сначала добавляют новую структуру, не трогая старую (expand), и только когда приложение уже перешло, старую структуру убирают (contract). Сначала выходит релиз только с добавлением, а релиз, который удаляет старую форму, — только после того, как обращения к ней исчезнут (раздел 5.1).
• От аварии, когда старая версия приложения открывает новую БД, защищаемся проверкой минимальной версии. При этом «текущий номер схемы» и «нижняя граница отсечения» — разные значения. Если совместить их в одном, старое приложение отсекут в тот момент, когда применится expand, и период сосуществования из пункта выше не сложится (раздел 5.2).
• Перед применением делаем автоматическое резервное копирование. В SQLite VACUUM INTO одной инструкцией создаёт согласованную копию², и восстановление при сбое сводится к замене файла.
• Одна миграция = одна транзакция, обновление номера версии — в той же транзакции. SQLite умеет откатывать DDL в рамках транзакции.³ В SQL Server есть исключительные DDL, поэтому такие операции выносятся в отдельную миграцию.⁴

На схеме сплошная линия обозначает отношение, которое выполняется всегда, а пунктирная — условное отношение (условия указаны в пояснении к каждому отношению на странице сведений). Полный список отношений (всего 22, с доказательствами и степенью уверенности) и определения основных понятий собраны на странице сведений карты знаний (на японском). Данные: JSON-LD / Turtle

2. Почему возникает проблема «у каждого клиента своя БД»

Если разложить причины, все они сводятся к «эксплуатации, рассчитанной на то, что это делает человек».

• Пропуск ручного ALTER. Нигде в самой БД не остаётся записи о том, был ли выполнен SQL из инструкции, и как только единственный способ проверки — «визуально посмотреть определение таблицы», пропуски гарантированно случаются.
• Оставленный без внимания сбой на середине. Если из пяти SQL-команд инструкции третья завершается ошибкой, исполнитель не может решить, продолжать или откатывать, и получается «приложение же работает, так и оставим». Эта БД теперь имеет уникальную схему, которая не совпадает ни с одной версией.
• Обновление с пропуском версий. У клиента, переходящего с v1.2 сразу на v1.5, нужно корректно пройти изменения схемы и v1.3, и v1.4 вместе, а при работе по инструкции сделать это трудно.
• Экстренный патч на месте. Возникает ситуация «этому одному клиенту столбец добавили заранее», а при последующем официальном обновлении это оборачивается ошибкой повторного применения.

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

3. Базовый паттерн: версия схемы + последовательные миграции вперёд

Каркас механизма состоит всего из трёх элементов.

1. Сама БД хранит номер версии схемы (отдельное от версии продукта целое число, предназначенное только для схемы).
2. Изменения схемы дописываются в код приложения как последовательность пронумерованных миграций.
3. При запуске (сразу после подключения к БД) приложение по порядку, внутри транзакций, применяет все миграции с номером больше текущей версии.

Поток при запуске выглядит так. Все защиты из глав 5 и 6 встраиваются куда-то в эту цепочку.

flowchart TD
    S["Приложение запускается, подключается к БД"] --> R["Читаем PRAGMA user_version и минимальный совместимый номер"]
    R --> Q1{"Минимальный совместимый номер<br/>больше максимума, известного приложению?"}
    Q1 -->|"больше"| STOP["Прерываем запуск (раздел 5.2)"]
    Q1 -->|"нет"| Q2{"Текущий номер<br/>больше максимума, известного приложению?"}
    Q2 -->|"больше"| FUT["Совместимая будущая схема.<br/>Ничего не применяем, обычный запуск (раздел 5.2)"]
    Q2 -->|"нет"| Q3{"Есть неприменённые миграции?"}
    Q3 -->|"нет"| OK["Обычный запуск"]
    Q3 -->|"есть"| BK["Снимаем резервную копию перед применением<br/>(VACUUM INTO, раздел 5.3)"]
    BK --> LOOP["Неприменённые номера применяем по возрастанию, по одной (раздел 6.1)<br/>BEGIN TRANSACTION → изменение схемы и преобразование данных →<br/>PRAGMA user_version = этот номер → COMMIT"]
    LOOP -.->|"если сбой на середине"| FAIL["Откатывается только эта одна,<br/>остановка на предыдущем номере"]
    LOOP --> DONE["Дошли до последней — обычный запуск"]

Рис. 1: Поток применения при запуске. Все защиты из глав 5 и 6 встраиваются в эту цепочку.

Для SQLite местом хранения номера версии может служить PRAGMA user_version. Это целое число в заголовке базы данных (смещение 60); официальная документация прямо говорит: «приложение может свободно использовать его, сама SQLite это значение не использует».¹ Отдельную таблицу создавать не нужно: один файл БД сам заявляет свою версию.

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

using Microsoft.Data.Sqlite;

public static class SchemaMigrator
{
    // Список только для дописывания. SQL под уже выпущенным номером переписывать нельзя категорически
    private static readonly (int Version, string Sql)[] Migrations =
    {
        (1, """
            CREATE TABLE customer (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
            -- Таблица с нижней границей отсечения. Создаём её в миграции №1
            -- и сразу кладём начальное значение.
            -- Если вынести в отдельный скрипт, его никто не выполнит:
            -- GetMinCompatibleVersion всегда вернёт 0, отсечение не сработает,
            -- а при первом contract UPDATE schema_meta упадёт с «таблицы нет» (раздел 5.2)
            CREATE TABLE schema_meta (key TEXT PRIMARY KEY, value INTEGER NOT NULL);
            INSERT INTO schema_meta (key, value) VALUES ('min_compatible_version', 0);
            """),
        (2, "ALTER TABLE customer ADD COLUMN phone TEXT"),
        (3, """
            CREATE TABLE invoice (
                id          INTEGER PRIMARY KEY,
                customer_id INTEGER NOT NULL REFERENCES customer(id),
                issued_at   TEXT    NOT NULL,  -- храним в UTC, в фиксированном формате
                amount      INTEGER NOT NULL   -- суммы — целые числа в минимальной единице валюты
            )
            """),
    };

    public static void Migrate(SqliteConnection conn)
    {
        // Ошибка при дописывании (дубликат или неверный порядок номеров) превращается
        // в тихое повторное применение или пропуск, поэтому обнаруживаем это до применения чего бы то ни было
        for (int i = 1; i < Migrations.Length; i++)
            if (Migrations[i].Version <= Migrations[i - 1].Version)
                throw new InvalidOperationException(
                    "Номера версий миграций должны строго возрастать и быть уникальными.");

        int current = GetUserVersion(conn);
        int latest = Migrations[^1].Version;
        int minCompatible = GetMinCompatibleVersion(conn);

        // Отсечение смотрим не по «номеру схемы», а по «минимальному совместимому номеру».
        // Если здесь проверять current > latest, то в момент, когда новое приложение
        // применит expand, user_version вырастет, и старое приложение сразу
        // не сможет открыть БД — конструкция из 5.1 («в период сосуществования
        // пишут и старое, и новое») просто не заработает. Минимальный совместимый
        // номер поднимаем только в миграции contract
        if (minCompatible > latest)
            throw new InvalidOperationException(
                $"Эта база данных требует приложение, которое понимает схему v{minCompatible} " +
                $"и новее (это приложение знает схемы до v{latest}). " +
                "Обновите приложение.");

        if (current > latest)
            // Схема из будущего, которой это приложение не знает, но она объявлена совместимой.
            // Применять нечего (всё уже <= current), поэтому идём в обычный запуск,
            // не трогая незнакомые столбцы (подробнее — раздел 5.2)
            return;

        foreach (var (version, sql) in Migrations)
        {
            if (version <= current) continue;

            using var tx = conn.BeginTransaction();
            using var cmd = conn.CreateCommand();
            cmd.Transaction = tx;
            cmd.CommandText = sql;
            cmd.ExecuteNonQuery();

            // Обновление версии фиксируем в той же транзакции.
            // Это устраняет состояние «изменение внесено, но номер остался старым»
            cmd.CommandText = $"PRAGMA user_version = {version}";
            cmd.ExecuteNonQuery();
            tx.Commit();
        }
    }

    private static int GetUserVersion(SqliteConnection conn)
    {
        using var cmd = conn.CreateCommand();
        cmd.CommandText = "PRAGMA user_version";
        return Convert.ToInt32(cmd.ExecuteScalar());
    }

    // «Нижняя граница приложения, которому ещё можно открыть эту БД».
    // Важно держать её отдельно от user_version: если совместить в одном значении,
    // expand сразу отсечёт старое приложение.
    // Поднимаем только в миграции contract (разделы 5.1 и 5.2)
    private static int GetMinCompatibleVersion(SqliteConnection conn)
    {
        using var exists = conn.CreateCommand();
        exists.CommandText =
            "SELECT 1 FROM sqlite_master WHERE type = 'table' AND name = 'schema_meta'";
        if (exists.ExecuteScalar() is null) return 0;   // Старая БД без таблицы. Нижней границы нет

        using var cmd = conn.CreateCommand();
        cmd.CommandText =
            "SELECT value FROM schema_meta WHERE key = 'min_compatible_version'";
        var value = cmd.ExecuteScalar();
        return value is null or DBNull ? 0 : Convert.ToInt32(value);
    }
}

schema_meta создаётся внутри миграции номер 1, как в коде выше. Если вынести это в отдельный скрипт, его никто не выполнит: GetMinCompatibleVersion всегда вернёт 0, отсечение не будет работать вообще, а при первом contract UPDATE schema_meta упадёт с «таблицы нет».

Если этот механизм добавляют в уже существующую БД, у баз, где миграция 1 уже применена, schema_meta нет. В миграции введения создайте её через CREATE TABLE IF NOT EXISTS и INSERT OR IGNORE (достаточно одного нового номера). GetMinCompatibleVersion сначала проверяет, есть ли таблица, как раз ради этого переходного периода.

Значение поднимаем только в миграции contract.

-- В миграции, которая удаляет старый столбец, поднимаем в той же транзакции
UPDATE schema_meta SET value = 7 WHERE key = 'min_compatible_version';
ALTER TABLE customer DROP COLUMN old_name;

Так проблемы из раздела 2 решаются структурно. Пропуски не случаются (проверка идёт при каждом запуске), сбой на середине откатывается (глава 6), пропуск версий тоже не проблема (если БД от v1.2 находится на схеме v2, приложение v1.5 просто применит 3, 4 и 5 по порядку). На вопрос «в каком состоянии БД этого клиента» можно ответить, один раз прочитав PRAGMA user_version.

Строго соблюдаются только два правила эксплуатации.

• Уже выпущенный номер не переписывается. Даже если в SQL версии v3 есть баг, исправление вносится в v4. Переписывание порождает новое расхождение: «применена старая v3» у одних и «применена новая v3» у других.
• Преобразование данных тоже включается в миграцию. Не только добавление столбца, но и перенос существующих данных (UPDATE) выполняется под тем же номером. Если с самого начала унифицировать формат и часовой пояс столбцов даты/времени на UTC и фиксированный формат — как описано в статье «Дата, время и часовые пояса в бизнес-приложениях», — последующие миграции упрощаются.

Есть ещё одна работа, нужная только когда этот механизм добавляют в уже существующую систему. У БД, которую вели ручными патчами, может быть состояние «user_version всё ещё 0, а реальная схема частично ушла вперёд» (именно это и есть экстренный патч из раздела 2). Поставив такую БД на цепочку миграций как есть, вы получите ошибку ALTER TABLE для уже применённого изменения — «столбец уже существует». В первом релизе, который внедряет этот механизм, нужно однократно, как базовый (baseline) шаг, обследовать реальную схему (в SQLite — проверить наличие столбцов через PRAGMA table_info), проставить соответствующий номер версии для БД с уже известными ручными патчами, и лишь после этого доверить всё дальнейшее последовательным миграциям вперёд. Пропустить этот шаг можно только если механизм был встроен с самого первого релиза.

В SQL Server нет аналога user_version, поэтому в отдельную таблицу (например, schema_version) построчно вставляют «номер версии, время применения, версию приложения на момент применения». Поскольку история сохраняется в виде строк, это устойчивее к последующему расследованию.

4. Инструмент или своя реализация — таблица решений

Достичь одного и того же можно тремя способами: EF Core Migrations, библиотека миграций (например, DbUp) и собственная реализация из предыдущего раздела. По одним названиям неясно, что именно делает каждый инструмент, поэтому сначала по одному предложению.

• EF Core (Entity Framework Core) — O/R-маппер для .NET от Microsoft (библиотека, которая сопоставляет объекты с таблицами и генерирует SQL). Встроенная функция EF Core Migrations обнаруживает изменения модели, написанной на C# (определения классов), и автоматически генерирует код изменения схемы.
• DbUp — открытая .NET-библиотека, заточенная под учёт применения SQL-скриптов. SQL пишете сами; библиотека берёт на себя запись «какой скрипт уже применён» и выполнение ещё не применённых.⁵

Рекомендации по ситуациям:

DbUp — это «библиотека .NET, помогающая развёртывать изменения в базе данных SQL Server»: она записывает выполненные скрипты в таблицу журнала и выполняет только ещё не выполненные. Также поддерживает SQLite, PostgreSQL, MySQL и другие СУБД.⁵ Как путь миграции «превратить SQL из инструкций в автоматическое применение с учётом выполнения» — это, пожалуй, самый короткий вариант.

4.1 Нюансы использования EF Core Migrations в распространяемом приложении

При разработке миграции EF Core применяются через dotnet ef database update, но на компьютере клиента нет ни SDK, ни исходного кода. Реалистичный способ применения — context.Database.Migrate() при запуске приложения.

Здесь важно знать, что документация Microsoft прямо предостерегает от применения миграций при запуске как способа управления продакшен-базой данных. Причины таковы: (1) сбой или повреждение из-за одновременного применения несколькими экземплярами (до EF Core 9), (2) обращение других приложений к БД во время применения миграции может вызвать серьёзные проблемы, (3) приложению требуются повышенные права на изменение схемы, (4) практически нет механизма отката, (5) невозможно заранее проверить и исправить выполняемый SQL — и рекомендуется вместо этого генерировать SQL-скрипты и применять их в рамках процесса развёртывания.⁷

Однако эта рекомендация исходит из предпосылки серверной системы «одна БД, есть реальный процесс развёртывания». Для десктопного приложения с локальной БД на каждом компьютере клиента физическое перемещение по объектам со скриптами для их выполнения — это и есть та самая проблема из раздела 2, поэтому Migrate() при запуске становится фактическим стандартным решением. Оставшиеся опасения нужно закрыть отдельно.

• Параллелизм: начиная с EF Core 9, Migrate() автоматически получает блокировку, предотвращающую одновременное выполнение миграций несколькими процессами.⁷ В более ранних версиях сериализацию нужно делать самостоятельно, как описано в главе 6. Однако эта блокировка сериализует только сами запуски миграций — она не останавливает обычное чтение и запись старой версией приложения во время применения миграции. Для общей БД планируйте это в сочетании с проверкой минимальной версии (раздел 5.2) или окном обслуживания.
• Предварительная проверка SQL: обязательно просматривайте сгенерированную миграцию и репетируйте её на БД с данными, сопоставимыми с продакшеном, перед релизом (раздел 6.3).
• Не смешивать с EnsureCreated(): схема будет построена без истории миграций, и Migrate() впоследствии начнёт падать. Унифицируйте всё на Migrate() с самого начала.⁷

В провайдере SQLite миграция, изменяющая тип столбца или удаляющая его, выполняется как полная пересборка таблицы — создание новой таблицы, копирование данных, удаление старой таблицы, переименование, — и идемпотентные скрипты сгенерировать тоже нельзя.⁶ Стоит ли вообще использовать EF Core, разобрано в разделе 8 статьи «Использование SQLite в бизнес-приложениях на C#».

5. Как писать миграции, которые не ломаются

Принцип для каждой отдельной миграции: не ломать обратную совместимость в одном релизе.

5.1 Несовместимые изменения — через expand-contract (двухэтапный релиз)

Добавление столбца безопасно, а удаление, переименование или изменение типа ломает «всё, что рассчитывало на старую форму». Даже при локальной SQLite и связи приложения с БД один к одному обычно найдётся хотя бы одно из: (а) возможность отката приложения на старую версию при сбое, (б) другой инструмент, читающий БД напрямую (инструмент отчётов, экспортёр CSV, интеграция с Access), (в) конфигурация с SQL Server, к которой одновременно обращаются старые и новые клиенты. Поэтому несовместимые изменения делятся на два этапа — expand (расширить) → contract (сузить).

На временной шкале между ними нужен «период сосуществования», когда работают и старая, и новая форма.

flowchart TB
    A["Релиз A (expand: расширить)<br/>Добавляем новую структуру, старую оставляем как есть<br/>Приложение пишет в обе, чтение считает эталоном старую структуру"]
    B["Период сосуществования<br/>Старое приложение и старые инструменты продолжают работать (обе структуры живы)<br/>За это время всех клиентов переводим на новую версию"]
    C["Проверка минимальной версии (раздел 5.2)<br/>позволяет отсечь старые приложения"]
    D["Релиз B (contract: сузить)<br/>Последние значения старой структуры финально копируем / преобразуем в новую<br/>Чтение переключаем на новую структуру, старую удаляем"]
    A --> B --> C --> D

Рис. 2: Между expand и contract вставляют период сосуществования. Contract не выпускают, пока нельзя отсечь старые приложения — в этом суть.

Переименование столбца ветвится слишком сильно, чтобы уместиться в одну ячейку таблицы. По шагам это выглядит так.

1. expand: добавить новый столбец и скопировать значения из старого. В одной и той же миграции выполняете ALTER TABLE ... ADD COLUMN и UPDATE.
2. Период сосуществования: новое приложение пишет в оба столбца, чтение считает эталоном старый. Чтение оставляют на старом столбце, потому что на общей БД, где одновременно работают старое и новое приложения, старое пишет только в старый столбец. Если читать новый, обновления, которые внесло старое приложение, будут потеряны. Можно также синхронизировать старый столбец с новым триггером на стороне БД.
3. Отсечь старые приложения. Проверкой минимальной версии (раздел 5.2) добиваемся, чтобы старое приложение эту БД уже не открыло. До этого шага не отсекаем. После шага 1 user_version растёт, но минимальный совместимый номер не трогаем, поэтому старое приложение по-прежнему открывает БД и пишет в старый столбец. Если совместить эти два значения, период сосуществования из шага 2 исчезнет в момент применения шага 1.
4. contract: финально скопировать последние значения из старого столбца в новый, переключить чтение на новый и удалить старый. Порядок важен: если переключить чтение на новый столбец до отсечения, потеряются обновления, которые старое приложение записало только в старый столбец.

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

У SQLite есть свои особенности: ALTER TABLE поддерживает только переименование таблицы, переименование столбца, добавление столбца и удаление столбца, причём даже удаление столбца имеет множество ограничений — нельзя удалить столбец, входящий в PRIMARY KEY или UNIQUE-ограничение, или столбец, на который ссылается индекс, CHECK-ограничение, внешний ключ или представление. Любое другое изменение выполняется по процедуре, определённой официальной документацией: создать новую таблицу внутри транзакции, перенести данные через INSERT INTO new_X SELECT ... FROM X, затем удалить старую таблицу и переименовать.³ На большой таблице это превращается в полное копирование, поэтому нужно заранее закладывать время применения и свободное место на диске.

5.2 Защита от отката версии — проверка минимальной версии

При проектировании только с миграциями вперёд скрипты отката (down) не пишутся (у клиента им негде применяться, а непроверенный код только опасен). Вместо этого нужен механизм, останавливающий работу, если старая версия приложения открывает более новую БД.

Здесь важно разделить «номер схемы» и «нижнюю границу отсечения». В коде из раздела 3 это два разных значения:

• user_version — текущий номер схемы. Растёт при каждой применённой миграции
• min_compatible_version в schema_meta — нижняя граница приложения, которому ещё можно открыть эту БД. Поднимаем только в момент contract

Отсечение смотрим только по второму. Если отсекать по user_version, период сосуществования из раздела 5.1 не сложится. Как только новое приложение применит expand, user_version вырастет, и правило «отказать, если номер больше известного максимума» сразу не даст старому приложению открыть БД. Тогда конструкция «в период сосуществования пишут и старое, и новое» просто не работает.

Если разделить, движение такое:

Со стороны старого приложения это выглядит как «схема с неизвестным номером, но она объявлена совместимой». В этот момент к незнакомым столбцам не обращаются — это предпосылка. Поэтому на стороне expand ограничиваются добавлением столбцов и не меняют смысл существующих.

Если заранее договориться, что «в релизах, откуда возможен откат, несовместимых изменений не будет (только expand)», то чтение новой БД старым приложением само по себе безопасно, и проверку можно ослабить до «предупредить и запустить в режиме только для чтения». Выбор зависит от того, насколько бизнес может позволить себе простой.

5.3 Автоматическое резервное копирование перед применением

Миграция — это операция над «продакшен-данными на чужом компьютере». Автоматизируйте практику «сначала бэкап, потом выполнение». В SQLite для этого идеально подходит VACUUM INTO — одна инструкция создаёт согласованный снимок в отдельный файл даже с работающей БД.²

// conn      … открытый SqliteConnection (тот же, что передаёте в Migrate из раздела 3)
// latest    … последний номер в Migrations (тот же latest, что в разделе 3)
// backupDir … каталог для копий. Класть рядом с самой БД опасно:
//             при отказе диска потеряете оба, лучше другой диск или общая папка
var backupDir = Path.Combine(
    Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData),
    "MyApp", "db-backup");

// Резервную копию одного поколения делаем непосредственно перед применением, и только когда оно нужно
if (GetUserVersion(conn) < latest)
{
    Directory.CreateDirectory(backupDir);

    // Здесь реально убираем рабочие файлы, оставшиеся после прошлого сбоя.
    // Обычно они следующему запуску не мешают (в имени есть время), но
    // пока их не удаляют, при каждом сбое копится мусор размером с целую БД.
    // А если повторить в ту же секунду, имена столкнутся, и VACUUM INTO
    // остановится: он требует, чтобы файла назначения не было (или он был пуст).
    //
    // Удаляем только «достаточно старые». Этот блок рассчитан на работу
    // внутри мьютекса из 6.2, но другую сборку или другой инструмент
    // всё равно могут положить файлы в ту же папку; если снести рабочий
    // файл чужого выполнения, оно упадёт на FileNotFoundException
    // непосредственно перед File.Move
    DateTime staleBefore = DateTime.UtcNow - TimeSpan.FromHours(1);
    foreach (var stale in Directory.EnumerateFiles(backupDir, "*.db.tmp"))
    {
        try
        {
            if (File.GetLastWriteTimeUtc(stale) < staleBefore) { File.Delete(stale); }
        }
        catch (IOException) { }                  // Держит другой процесс. Отложим на следующий раз
        catch (UnauthorizedAccessException) { }
    }

    var backupPath = Path.Combine(backupDir,
        $"app_schema_v{GetUserVersion(conn)}_{DateTime.Now:yyyyMMdd_HHmmss}.db");
    // Создаём под временным именем и переименовываем только после успеха, чтобы незавершённый
    // файл после отключения питания или принудительного завершения процесса не выглядел «готовым бэкапом»
    var tempPath = backupPath + ".tmp";
    using var cmd = conn.CreateCommand();
    cmd.CommandText = "VACUUM INTO $path";
    cmd.Parameters.AddWithValue("$path", tempPath);
    cmd.ExecuteNonQuery();  // VACUUM выполняется вне транзакции
    File.Move(tempPath, backupPath);
}

Этот блок запускайте внутри взаимного исключения из раздела 6.2. Резервное копирование — часть миграции, а не отдельный этап. Если не обернуть одной критической секцией цепочку «посмотреть версию → снять копию → применить», в обычный для бизнес-приложения момент, когда утром все запускают приложение одновременно, два процесса придут к одной и той же проверке. Один подчистит рабочий файл другого, и непосредственно перед File.Move будет FileNotFoundException — копия снята правильно, а запускается приложение не может, и такую аварию трудно объяснить. Удаление только «достаточно старых» файлов в коде выше — страховка на случай, если обернуть забыли. Это страховка, а не замена взаимному исключению.

Уборку .tmp пишите как часть этой процедуры, а не как «добавим потом». Если VACUUM INTO прервать отключением питания или принудительным завершением процесса, остаётся недописанный и повреждённый выходной файл.² Имя временное, поэтому его не примут за «готовую копию», но пока файлы не удаляют, при каждом сбое на ПК пользователя копится мусор размером с целую БД. Он тихо съедает место в каталоге бэкапов и при восстановлении добавляет ещё один вопрос: «какой из файлов настоящий».

К тому же VACUUM INTO требует, чтобы файла назначения не существовало (или он был пуст).² В имени выше есть время, поэтому обычно столкновения нет, но если пользователь сразу перезапустит приложение, упавшее на миграции (или это сделает служба мониторинга), попадание в ту же секунду совпадёт имена, и команда остановится. Самый труднообъяснимый вид сбоя: «бэкап не снялся, приложение не стартует».

Если включить версию схемы в имя файла, при восстановлении сразу видно, «до какого момента» откатываться. Подробности о резервном копировании, включая то, почему простое копирование файла работающей БД — источник повреждений, см. в разделе 7 статьи «Использование SQLite в бизнес-приложениях на C#». Для SQL Server эквивалент — выполнение BACKUP DATABASE перед применением; идея та же.

6. Эксплуатационные ловушки

6.1 Сбой на середине и транзакции — учитывайте различия СУБД

Код из раздела 3 оборачивает одну миграцию в одну транзакцию и включает обновление user_version в ту же транзакцию. Это работает потому, что SQLite умеет выполнять DDL (CREATE TABLE, ALTER TABLE и т. п.) внутри транзакции и откатывать его при сбое. Сама официальная процедура пересборки таблицы построена как «начать транзакцию, выполнить CREATE/INSERT/DROP/RENAME, зафиксировать».³ Даже если питание пропадёт на середине, БД при следующем запуске окажется в согласованном состоянии «непосредственно перед этой миграцией».

SQL Server тоже умеет выполнять множество DDL внутри транзакции, но есть исключения. Например, ALTER DATABASE нельзя использовать внутри явной транзакции, а CREATE FULLTEXT INDEX тоже нельзя разместить внутри пользовательской транзакции.⁴ EF Core тоже автоматически оборачивает каждую миграцию в транзакцию, когда это возможно, но прямо указывает, что «некоторые операции на некоторых базах данных нельзя выполнить внутри транзакции».⁸ Практическое правило сводится к одному: не смешивайте операцию, не входящую в транзакцию, с обычным изменением схемы в одной и той же миграции. При смене СУБД обязательно проверяйте, участвует ли DDL в транзакциях.

Классическая авария — «обновление версии в отдельной транзакции». Если само изменение прошло успешно, а процесс упал до обновления номера, при следующем запуске та же миграция выполнится повторно и упадёт с ошибкой «таблица уже существует», приводя к бесконечному сбою запуска. Если включить обновление номера в ту же транзакцию, это принципиально невозможно.

6.2 Одновременный запуск нескольких процессов — сериализация применения

Бизнес-приложение — это ПО, которое «утром все запускают одновременно». Несколько клиентов, обращающихся к общей БД (SQL Server), или многократный запуск на одном компьютере могут привести к одновременному выполнению миграций.

• Начиная с EF Core 9, Migrate() автоматически получает блокировку на уровне всей базы данных, предотвращая одновременное применение (в более ранних версиях этой защиты нет). Отметим, что блокировка в провайдере SQLite реализована через специальную таблицу блокировки, и в официальной документации отмечено, что таблица может остаться, если процесс, применявший миграцию, аварийно завершится.⁷ Если запуск застрял в ожидании этой блокировки, убедитесь, что ни один другой процесс реально не выполняет миграцию, и восстановитесь, удалив (DROP) оставшуюся таблицу блокировки (__EFMigrationsLock).
• В своей реализации для локальной БД проще всего сериализовать через именованный Mutex.

// using System.Threading;   (Mutex / AbandonedMutexException)
// conn … открытый SqliteConnection. Подключение завершаем до миграции,
//        Mutex берём, затем вызываем Migrate
using var conn = new SqliteConnection(connectionString);
conn.Open();

// Добавляем префикс Global\, чтобы сериализация действовала в масштабе всего компьютера
// даже при запуске из нескольких сессий входа через RDP или переключение пользователей
// (Local\ ограничивается только текущей сессией)
using var mutex = new Mutex(false, @"Global\MyApp.SchemaMigration");
try
{
    mutex.WaitOne();
}
catch (AbandonedMutexException)
{
    // Предыдущий владевший процесс аварийно завершился, не вызвав Release.
    // Несмотря на исключение, само владение уже получено, поэтому можно продолжать.
    // Возможность того, что предыдущее применение завершилось на середине, покрывается
    // последующей повторной проверкой версии и транзакциями для каждой миграции
}
try
{
    SchemaMigrator.Migrate(conn);
}
finally
{
    mutex.ReleaseMutex();
}

Тот, кто ждал, после получения блокировки снова проверяет версию (код из раздела 3 каждый раз заново проверяет version <= current перед применением), поэтому повторного применения не произойдёт. Отметим также, что именованный объект Global\ по умолчанию имеет ACL, унаследованный от создавшего его пользователя, поэтому открытие того же Mutex из сессии другой учётной записи Windows может вызвать UnauthorizedAccessException. Если предполагается использование из нескольких учётных записей, либо создавайте объект через MutexAcl из System.Threading.AccessControl, предоставив нужным пользователям права synchronize/modify, либо переходите к блокировке на стороне БД, описанной ниже. Поскольку Mutex не действует между машинами, для общей БД лучше опираться на сериализацию на уровне БД: «завершить применение на стороне сервера до раздачи обновления», «брать блокировку на стороне БД в момент начала применения (BEGIN IMMEDIATE для SQLite, application lock для SQL Server)».

6.3 Репетиция — тестируйте одномоментное применение от «самой старой БД»

Баги миграций почти никогда не находятся на машине разработчика, потому что БД на ней всегда находится на актуальной схеме, а данные чистые. Ломается то, что у клиента — старая, большая БД с неожиданными данными. Перед релизом нужно сделать как минимум три вещи.

• Хранить файл БД для каждой версии схемы как тестовую фикстуру и автоматизировать тест, применяющий миграции от каждой из них до последней за один проход. Паттерны с пропуском вроде «с v1 на v5» или «с v3 на v5» — это и есть реальность у клиентов. С SQLite достаточно поместить файл БД в репозиторий, так что такой тест писать сравнительно легко.
• Тестировать на данных, сопоставимых по объёму и характеру с продакшеном. Столбцы, полные NULL, неожиданные дубликаты, время пересборки на огромной таблице (раздел 5.1) не проявятся, если данные далеки от настоящих. По возможности репетируйте на анонимизированной БД клиента.
• Проверять сценарий сбоя. Убейте процесс в середине применения и убедитесь, что при следующем запуске происходит корректное восстановление (повторное применение начинается с версии, к которой произошёл откат).

6.4 Проверка на месте и откат

Когда механизм уже внедрён, выпишите отдельной процедурой «как понять, что всё прошло» и «что делать, если нет». Рано или поздно придётся диктовать шаги по телефону человеку на месте, поэтому держите их в виде команд.

Проверить результат применения. Если на машине есть официальная командная оболочка SQLite (sqlite3), текущую версию схемы читают одной строкой. Вернётся одно целое число.

sqlite3 "C:\ProgramData\MyApp\app.db" "PRAGMA user_version;"

На клиентский ПК часто нельзя положить sqlite3.exe, поэтому на экране сведений о версии приложения показывайте и версию продукта, и версию схемы — тогда состояние можно снять одним звонком. Достаточно вызвать GetUserVersion из раздела 3. Для SQL Server ту же роль играет SELECT MAX(version) FROM schema_version;.

Откатить после сбоя. Копию из раздела 5.3 возвращают так.

1. Полностью завершите приложение. Включая повторные запуски и другие терминалы, которые смотрят в ту же БД.
2. Отложите текущие файлы в сторону. Перенесите текущий файл БД — и при режиме WAL одноимённые файлы -wal / -shm — в другую папку. Не удаляйте, сохраните. Они понадобятся для разбора причин.
3. Скопируйте файл резервной копии под исходным именем. В разделе 5.3 в имя файла включена версия схемы, поэтому по имени видно, к какому моменту вы возвращаетесь.
4. Запустите приложение и убедитесь, что PRAGMA user_version равен нужному номеру. До тех пор, пока не раздадите исправленную сборку, продолжайте работу на старой версии приложения.

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

7. Итог

• «У каждого клиента своя БД» — это не вопрос внимательности исполнителя, а структурное следствие эксплуатации, при которой человек выполняет SQL-инструкции. Для десктопного бизнес-приложения с БД, разбросанной по клиентам, единственный вариант — дать приложению самому обновлять собственную БД.
• Каркас — это номер версии схемы, который хранит сама БД (PRAGMA user_version для SQLite¹) плюс применение пронумерованных миграций только вперёд при запуске. На C# для этого хватает нескольких десятков строк своей реализации.
• Способов три: EF Core Migrations, библиотека вроде DbUp и своя реализация. Выбор зависит от того, уже ли используется EF Core и сколько «голого» SQL уже накоплено как актив (таблица решений в главе 4). У вызова Migrate() при запуске в EF Core официально перечислены нюансы,⁷ поэтому используйте его вместе с защитой от параллелизма и репетициями.
• Несовместимые изменения выполняют двухэтапным релизом expand-contract, а аварию, при которой старое приложение открывает новую БД, останавливают проверкой минимальной версии. Ограничения ALTER TABLE в SQLite и процедура пересборки следуют официальной документации.³
• Принцип: одна миграция = одна транзакция, обновление версии — в той же транзакции. В SQL Server есть DDL, не входящий в транзакцию,⁴ поэтому такие операции выносятся отдельно. Только вместе с резервным копированием через VACUUM INTO перед применением² и репетицией одномоментного применения от самой старой версии миграция становится тем, что действительно «можно поставить клиенту».

Если процесс с ALTER по инструкциям вам знаком, попробуйте в следующем релизе добавить хотя бы «запись номера версии» и «применение при запуске». Как только этот фундамент появится, двухэтапные релизы и резервное копирование можно будет добавлять постепенно.

Похожие статьи

• Использование SQLite в бизнес-приложениях на C# — режим WAL, блокировки, защита от повреждений, разграничение с EF Core
• Как выбрать место хранения данных Windows-приложения — таблица решений SQLite / JSON / реестр / Access
• Не только appsettings.json — практика управления конфигурацией Windows-бизнес-приложений
• Дата, время и часовые пояса в бизнес-приложениях — от ловушек DateTime до принципа хранения в UTC и проектирования тестов

Смежные области консультаций

В Komura Software LLC мы занимаемся проектированием БД и внедрением инфраструктуры миграций для бизнес-приложений, устанавливаемых у каждого клиента отдельно, расследованием и нормализацией схем, разошедшихся из-за эксплуатации по инструкциям, а также проектированием распространения обновлений как для конфигураций с EF Core, так и с «голым» SQL.

• Разработка приложений для Windows
• Доработка и обслуживание существующего ПО для Windows
• Техническая консультация и ревью архитектуры
• Контакты

Справочные материалы

Похожие статьи

Недавние статьи с теми же тегами помогут подробнее изучить близкие темы.

Связанные темы

Эти страницы показывают тему статьи в более широком контексте услуг и решений.

Услуги по этой теме

Статья напрямую связана со следующими услугами.

Частые вопросы

Вопросы, которые часто возникают при консультациях по теме статьи.

Как управлять изменениями схемы БД в бизнес-приложении?

Не поручайте человеку выполнять SQL из инструкции. Упакуйте изменения схемы в пронумерованные миграции (код изменения схемы) прямо в приложение и применяйте их автоматически при запуске. Сама БД должна хранить текущую версию схемы (для SQLite — PRAGMA user_version, для SQL Server — отдельная таблица), а приложение применяет по порядку, в транзакциях, только ещё не применённые номера. Тогда даже обновление с пропуском версий, например с v1.2 сразу на v1.5, применит все промежуточные изменения схемы, и состояние «у каждого клиента своя форма БД» структурно перестаёт возникать.

Можно ли вызывать Migrate() из EF Core при запуске приложения?

Это условно реалистичный выбор. Документация Microsoft предупреждает об осторожности при применении миграций во время запуска в продакшене — из-за одновременного применения несколькими экземплярами, необходимости давать приложению права на изменение схемы, невозможности заранее проверить SQL и других причин — и для серверных приложений рекомендует генерировать SQL-скрипты и применять их в процессе развёртывания. Однако для десктопного бизнес-приложения с локальной БД на каждом клиентском компьютере объезжать объекты со скриптами нереально, поэтому вызов Migrate() при запуске становится фактическим стандартным решением. И в этом случае обязательно сочетайте его с защитой от одновременного запуска (автоматическая блокировка в EF Core 9+ или свой Mutex) и резервным копированием перед применением.

Что произойдёт с БД, если миграция прервётся на середине?

Если обернуть одну миграцию в одну транзакцию и включить обновление номера версии в ту же транзакцию, при сбое произойдёт откат к состоянию до начала этой миграции, и незавершённая схема не останется. SQLite позволяет выполнять DDL вроде CREATE TABLE и ALTER TABLE внутри транзакции, и сама официальная процедура пересборки таблицы написана в расчёте на транзакцию. SQL Server тоже позволяет выполнять многие DDL внутри транзакции, но есть исключения — например, ALTER DATABASE и операции, связанные с полнотекстовыми индексами, — поэтому такие операции стоит выносить в отдельную миграцию. А если есть автоматическое резервное копирование перед применением, даже в худшем случае можно восстановиться простой заменой файла.

Если схема БД уже разошлась по разным клиентам, как её нормализовать?

Сначала зафиксируйте одну эталонную схему и обследуйте БД каждого клиента, выявив расхождения с этим эталоном. Затем для БД без номера версии напишите начальную (baseline) миграцию, которая обнаруживает каждый реально встречающийся вариант и приводит его к эталонной форме, а по завершении этой миграции проставьте номер версии. В SQLite наличие столбца можно механически определить через sqlite_master или PRAGMA table_info, и расхождения можно закрыть защитным SQL вида «добавить столбец, если его ещё нет». Как только все последующие изменения пойдут через пронумерованные миграции, расхождения больше не повторятся.

Об авторе

Страница с профилем автора статьи.

Го Комура

Представитель KomuraSoft LLC

Специализируется на разработке программного обеспечения для Windows, техническом консалтинге и расследовании сбоев, особенно в проектах с унаследованными системами и трудно воспроизводимыми ошибками.