SQLite в бизнес-приложениях на C# — режим WAL, блокировки записи, защита от повреждений и выбор EF Core

В предыдущей статье «Как выбрать место хранения данных Windows-приложения» я писал, что для растущих бизнес-данных и истории первый кандидат — SQLite. Вывод, который стоит взять оттуда в одну строку: «каталог — %LOCALAPPDATA%, если данные свои у каждого пользователя, и %PROGRAMDATA%, если общие для всех; формат — JSON для небольших настроек и SQLite для растущих бизнес-данных и истории; пароли и API-ключи отдельно защищают DPAPI». Здесь речь только о том, что делать после выбора SQLite, поэтому предыдущую статью читать не обязательно: этой одной строки как предпосылки достаточно.

С самим выбором всё ясно, но на этапе встраивания появляются другие сомнения. На консультациях часто слышим: «ищу SQLite в NuGet — пакетов несколько, непонятно какой ставить»; «в целом работает, но иногда выскакивает database is locked. Перебиваемся повторными попытками — так правильно?»; «для резервной копии достаточно просто скопировать файл БД?»

Все эти вопросы неизбежно встают, если SQLite в бизнес-приложении эксплуатируют несколько лет. Если закрыть их ещё на первоначальном проектировании, потом не придётся мучиться. В этой статье, опираясь на Microsoft.Data.Sqlite, разберу пункты, которые на ревью проектирования проверяю каждый раз: выбор библиотеки, строка подключения и пул соединений, устройство режима WAL, как работать с SQLITE_BUSY, ловушки сопоставления типов, защита от повреждений и резервное копирование, а также когда брать EF Core, а когда голый ADO.NET.

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

Одной строкой: библиотека — Microsoft.Data.Sqlite, режим WAL включают с первого релиза, путь записи сводят к одному. Ниже — разбор по пунктам.

• Для новой разработки базовый выбор библиотеки — Microsoft.Data.Sqlite (или построенный на нём SQLite-провайдер EF Core). Это другая библиотека, несовместимая с System.Data.SQLite ни по строке подключения, ни по деталям поведения, поэтому всегда смотрите, для какой из двух написан найденный в сети пример.¹
• Включайте режим WAL начиная с самого первого релиза. Параллелизм чтения и записи растёт, и бо́льшая часть случаев database is locked исчезает. Настройка сохраняется в самом файле БД, но на сетевом общем ресурсе не работает.²
• database is locked (SQLITE_BUSY) — это не повод «добавить обработку ошибок», а сигнал пересмотреть проектирование. Microsoft.Data.Sqlite автоматически повторяет попытку вплоть до тайм-аута (по умолчанию 30 секунд)³, но настоящее решение — свести путь записи к одному.
• При большом количестве мелких INSERT достаточно объединить их в явную транзакцию, и работа ускоряется на два-три порядка. Если кажется, что «SQLite тормозит», в первую очередь подозревайте размер коммита.
• У SQLite фактически всего четыре типа — INTEGER / REAL / TEXT / BLOB, а DateTime, Guid и decimal хранятся как TEXT. Особенно decimal: сравнение и сортировка подчиняются строковым правилам, поэтому денежные суммы безопаснее хранить как целые числа в минимальной единице валюты.⁴
• Для резервного копирования простое копирование файла во время работы запрещено. Используйте VACUUM INTO или Backup API (SqliteConnection.BackupDatabase). Вручную удалять файлы -wal / -shm тоже строго запрещено.⁵
• Обычный SQLite шифрование не поддерживает. Параметр Password в строке подключения действует только если подключена нативная библиотека семейства SQLCipher⁶; для небольшого объёма конфиденциальных данных сначала рассмотрите защиту через DPAPI (Data Protection API — криптографический API, который Windows даёт как функцию ОС, и ключ приложению держать не нужно), а не шифрование всей БД. Подробности — в статье «Хранение секретов в Windows-приложениях — избегаем настроек в открытом виде с помощью DPAPI».

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

2. Выбор библиотеки — похожие имена, разное содержимое

Пакетов, через которые из .NET ходят в SQLite, несколько, и первое, на чём спотыкаются, — похожие названия. Свести их можно так.

Microsoft.Data.Sqlite сопровождает команда EF Core, и он же служит основой SQLite-провайдера EF Core.¹ Нативный двоичный файл (само ядро SQLite) входит в NuGet-пакет, поэтому отдельно распространять его на клиентские ПК не нужно, а различия x86/x64/ARM64 берёт на себя NuGet.

Имеет смысл помнить, что материалы, написанные для System.Data.SQLite, напрямую не переносятся. Библиотека живёт давно, поэтому в интернете масса примеров именно под System.Data.SQLite, и вы наступите на такие несовместимости.

• Строки подключения несовместимы. Ключевые слова вроде Version=3, UseUTF16Encoding или меняющего формат даты DateTimeFormat в Microsoft.Data.Sqlite не существуют, и их указание вызывает исключение. Поддерживаемый список короткий: Data Source / Mode / Cache / Password / Foreign Keys / Default Timeout / Pooling и ещё несколько.⁶
• Обработка типов отличается. Например, Guid по умолчанию хранится как BLOB в System.Data.SQLite и как TEXT в Microsoft.Data.Sqlite. В переходный период, когда один и тот же файл БД читают и пишут обе библиотеки, эта разница проявляется как несогласованность данных.

Microsoft.Data.Sqlite намеренно тонкий: своих преобразований типов и удобных обёрток нет, зато описания из официальной документации SQLite применимы напрямую. Если существующее приложение стабильно работает на System.Data.SQLite, форсировать миграцию не обязательно. Но новый код стоит писать на Microsoft.Data.Sqlite, а если миграция всё же нужна — сначала выписать перечисленные несовместимости как пункты плана.

3. Соединения и строка подключения — пул удерживает файл

Базовая форма строки подключения — просто путь к файлу. На практике важно следить за Mode и Pooling.⁶

using Microsoft.Data.Sqlite;

var builder = new SqliteConnectionStringBuilder
{
    DataSource = dbPath,
    Mode = SqliteOpenMode.ReadWriteCreate  // Значение по умолчанию: создать, если файла нет
};
using var conn = new SqliteConnection(builder.ConnectionString);
conn.Open();

• Mode: по умолчанию ReadWriteCreate (создать, если отсутствует). Если раздаёте справочную БД только для чтения, ReadOnly не даст записать из-за ошибки в коде или неверного действия пользователя.
• Cache: обычно оставляйте значение по умолчанию. В документации прямо сказано, что Cache=Shared не рекомендуется вместе с режимом WAL, поэтому при подходе этой статьи, ориентированном на WAL, этот параметр не нужен.⁶
• Password: если указать этот параметр, сразу после подключения уходит PRAGMA key, но стандартная нативная библиотека шифрование не поддерживает, поэтому ничего не происходит.⁶ Если нужно шифровать всю БД, придётся заменить бандл на семейство SQLCipher (например, SQLitePCLRaw.bundle_e_sqlcipher), а ключ шифрования всё равно хранить через DPAPI. DPAPI (Data Protection API) — криптографический API, который Windows даёт как функцию ОС; из .NET к нему ходят через System.Security.Cryptography.ProtectedData. Значение, зашифрованное с DataProtectionScope.CurrentUser, привязано к учётным данным входа этого пользователя и в общем случае не расшифровывается другим пользователем или на другом ПК. Достаточно понимать это как инструмент, которым ОС берёт на себя бесконечный вопрос «а куда тогда класть сам ключ шифрования» (подробности — по ссылке в главе 1).
• Default Timeout: тайм-аут команды (по умолчанию 30 секунд). Это верхняя граница времени повторов из главы 5.

Ещё один момент — про асинхронные API. У самого SQLite нет асинхронного ввода-вывода, поэтому async-методы вроде ExecuteNonQueryAsync внутри выполняются синхронно.⁷ Утверждение «сделал async — UI не зависнет» здесь не работает, поэтому тяжёлые запросы нужно явно уводить на рабочий поток, например через Task.Run. Критерии выбора async/await — в «Практической таблице решений для C# async/await».

3.1 Ловушка пула — файл остаётся открытым даже после Close

Начиная с версии 6.0 в Microsoft.Data.Sqlite пул соединений включён по умолчанию.⁶ Плюс в том, что файл не нужно заново открывать на каждый Open. Минус в том, что даже после Close / Dispose нативное соединение остаётся в пуле и продолжает удерживать дескриптор файла БД. Из-за этого следующие операции падают с ошибкой «файл используется».

• функция «сбросить данные», которая удаляет файл БД и создаёт его заново
• замена файла БД при восстановлении из резервной копии
• перемещение файла БД при удалении приложения или эвакуации данных

Решение — очищать пул непосредственно перед операцией над файлом.

// Уничтожаем пул, связанный с этой строкой подключения, и освобождаем дескриптор файла
SqliteConnection.ClearPool(new SqliteConnection(connectionString));
File.Delete(dbPath);

В режиме WAL (следующая глава) могут оставаться файлы -wal / -shm, их тоже нужно убрать. Если требуется освободить всё при завершении приложения, используйте SqliteConnection.ClearAllPools(); а для одноразовых утилит, которым пул вообще не нужен, можно указать Pooling=False в строке подключения. «Вызвал Close, а удалить не могу» — частый вопрос после перехода на SQLite, поэтому с самого начала встраивайте ClearPool в любую служебную обработку, которая трогает файл БД.

4. Как устроен режим WAL — пользоваться, понимая, что происходит

В предыдущей статье было сказано лишь «включите режим WAL», поэтому здесь разберём и само устройство. При стандартном журнале отката (rollback journal) чтение блокируется на время записи — это главная причина database is locked. В режиме WAL (Write-Ahead Logging) изменения пишутся не в основной файл БД, а в отдельный журнал только для дозаписи, поэтому чтение не блокирует запись, а запись не блокирует чтение.² Типичная для бизнес-приложений схема — UI-поток показывает историю, пока в фоне пишутся измеренные значения — работает как есть.

При включении режима WAL рядом с основным файлом БД (содержимым, зафиксированным контрольными точками) появляются два дополнительных файла.

Связь трёх файлов с чтением и записью выглядит так.

flowchart TB
    W["Соединение записи<br/>одновременно только одно"]
    R["Соединения чтения<br/>можно открыть сколько угодно"]
    WAL["app.db-wal (журнал дозаписи)<br/>закоммиченные, но ещё не применённые к основному файлу изменения<br/>если удалить вручную, пропадут самые свежие данные"]
    SHM["app.db-shm (wal-index)<br/>разделяемая память. Согласует между процессами,<br/>до какой позиции WAL можно читать"]
    DB["app.db (основной файл)<br/>содержимое, зафиксированное контрольными точками"]
    CP["Контрольная точка (checkpoint)<br/>по умолчанию автоматически при 1000 страницах WAL (около 4 МБ)<br/>если долго держится чтение, не продвигается, WAL разрастается"]

    W -->|"дописывает изменения"| WAL
    R -->|"читает зафиксированную часть"| DB
    R -->|"неприменённые коммиты читает отсюда"| WAL
    SHM -.->|"указывает диапазон чтения"| R
    WAL --> CP
    CP -->|"переносит в основной файл и очищает WAL"| DB

Чтение смотрит и в основной файл, и в WAL, поэтому во время записи чтение не останавливается. В этом и состоит эффект WAL. Из той же схемы сразу видно ограничение: «копия одного основного файла не содержит самых свежих данных» и «-shm — разделяемая память, поэтому на сетевом общем ресурсе схема не работает».

Свойств, которые нужно держать в уме, четыре.

• Контрольная точка (checkpoint): процесс переноса содержимого -wal в основной файл; по умолчанию выполняется автоматически, когда WAL достигает 1000 страниц (около 4 МБ).² Если долго держится длинная транзакция чтения, контрольная точка не может продвинуться и -wal разрастается. Поэтому избегайте конструкции «держать соединение для чтения постоянно открытым и передавать его между компонентами» — открывайте и закрывайте по мере необходимости (благодаря пулу повторное открытие быстрое).
• Настройка сохраняется в БД: PRAGMA journal_mode=WAL достаточно выполнить один раз — она записывается в сам файл БД, и после этого режим WAL сохраняется при любом соединении.² Выполнять её при каждом подключении не нужно.
• Запись по-прежнему возможна только одна одновременно: WAL повышает параллелизм чтения и записи, но записи между собой остаются взаимоисключающими. Если ошибочно решить, что «раз включили WAL, можно свободно писать из нескольких потоков», вы столкнётесь с SQLITE_BUSY из главы 5.
• На сетевом общем ресурсе не работает: wal-index рассчитан на разделяемую память и между процессами на разных машинах не действует.² Впрочем, как уже говорилось в предыдущей статье, класть SQLite на сетевой общий ресурс вообще не стоит.

С эксплуатационной точки зрения важно помнить: файл -wal содержит уже закоммиченные, но ещё не применённые к основному файлу транзакции. И «достаточно скопировать только .db», и «-wal — временный файл, его можно удалить» — оба утверждения ошибочны: в лучшем случае теряются самые свежие коммиты, в худшем — БД повреждается.⁵ Это напрямую связано с темой главы 7: резервную копию нельзя делать копированием файла.

4.1 Проверяем, что настройка действительно действует

Переключение на WAL — это один вызов PRAGMA journal_mode=WAL, но чтобы не оказаться в ситуации «вроде выполнил, а не применилось», настройку всегда читают обратно и проверяют. PRAGMA без значения возвращает текущее, поэтому проверка занимает несколько строк.

using var cmd = conn.CreateCommand();
cmd.CommandText = "PRAGMA journal_mode";
var mode = (string)cmd.ExecuteScalar()!;      // если WAL действует, будет "wal"
if (!string.Equals(mode, "wal", StringComparison.OrdinalIgnoreCase))
    logger.LogWarning("journal_mode по-прежнему {Mode}", mode);

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

Важно различие в четвёртом столбце — «как действует». journal_mode записывается в файл БД, поэтому при каждом подключении его выдавать не нужно; остальное определяется каждый раз при открытии соединения. Типичная жалоба «на машине разработчика работало, в продуктиве нет» как раз от того, что из-за разницы в строке подключения или нативной библиотеке здесь меняется поведение.

Если нужно осмотреть сам файл БД, не запуская приложение, те же PRAGMA можно выполнить в официальной командной оболочке SQLite sqlite3. Как инструмент полевого разбора его стоит иметь под рукой: «эта БД действительно в WAL?» проверяется за минуту.

5. Блокировки и SQLITE_BUSY — сводим запись к одному пути

SQLITE_BUSY (в сообщении исключения — database is locked) возникает, когда другое соединение удерживает блокировку записи. Прежде всего стоит знать, что Microsoft.Data.Sqlite автоматически повторяет попытку при ошибках busy/locked вплоть до тайм-аута команды (по умолчанию 30 секунд).³ То есть собственная логика вида «поймать в catch, поспать и выполнить заново» на стороне приложения обычно не нужна. Если исключение всё же долетает после исчерпания тайм-аута, причина одна из следующих.

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

Ни одну из этих причин не устраняет «увеличение числа повторов». Решение — укорачивать транзакции и сводить путь записи к одному.

5.1 Очередь записи через System.Threading.Channels

Для данных, источником которых являются несколько потоков (измерения, журналы операций и т. п.), вместо прямой записи каждым потоком в БД направляйте их в очередь, которую обрабатывает выделенный цикл записи. В .NET для этого прямо подходит System.Threading.Channels.

using System.Globalization;
using System.Threading.Channels;
using Microsoft.Data.Sqlite;

public sealed record Measurement(string DeviceId, double Value, DateTime CreatedAtUtc);

public sealed class MeasurementWriter : IAsyncDisposable
{
    private readonly Channel<Measurement> _channel =
        Channel.CreateBounded<Measurement>(new BoundedChannelOptions(10_000)
        {
            FullMode = BoundedChannelFullMode.Wait  // При переполнении заставляем ждать производителя
        });
    private readonly string _connectionString;
    private readonly Task _loop;

    public MeasurementWriter(string connectionString)
    {
        _connectionString = connectionString;
        _loop = Task.Run(WriteLoop);
    }

    // Можно вызывать из любого потока. К БД не обращается. Если цикл записи
    // уже умер, отправитель сразу узнает об этом через ChannelClosedException
    public ValueTask EnqueueAsync(Measurement m) => _channel.Writer.WriteAsync(m);

    private async Task WriteLoop()
    {
        try
        {
            await WriteLoopCore();
        }
        catch (Exception ex)
        {
            // Через канал сообщаем отправителям, что писатель умер (например, из-за
            // переполнения диска). Если этого не сделать, при заполнении очереди
            // EnqueueAsync будет ждать вечно, и никто не заметит сбой
            _channel.Writer.TryComplete(ex);
            throw;
        }
    }

    private async Task WriteLoopCore()
    {
        using var conn = new SqliteConnection(_connectionString);
        conn.Open();
        var buffer = new List<Measurement>(500);
        while (await _channel.Reader.WaitToReadAsync())
        {
            // Забираем накопившееся, до 500 элементов, и записываем одной транзакцией
            buffer.Clear();
            while (buffer.Count < 500 && _channel.Reader.TryRead(out var m))
                buffer.Add(m);

            using var tx = conn.BeginTransaction();
            using var cmd = conn.CreateCommand();
            cmd.Transaction = tx;
            cmd.CommandText =
                "INSERT INTO measurement (device_id, value, created_at) " +
                "VALUES ($device, $value, $at)";
            var pDevice = cmd.Parameters.Add("$device", SqliteType.Text);
            var pValue  = cmd.Parameters.Add("$value",  SqliteType.Real);
            var pAt     = cmd.Parameters.Add("$at",     SqliteType.Text);
            foreach (var m in buffer)
            {
                pDevice.Value = m.DeviceId;
                pValue.Value  = m.Value;
                // Явно указываем InvariantCulture, чтобы не зависеть от календаря и цифр культуры по умолчанию
                pAt.Value     = m.CreatedAtUtc.ToString(
                    "yyyy-MM-dd HH:mm:ss.fffffff", CultureInfo.InvariantCulture);
                cmd.ExecuteNonQuery();
            }
            tx.Commit();
        }
    }

    public async ValueTask DisposeAsync()
    {
        // Не бросаем исключение, даже если цикл записи уже закрыл канал из-за сбоя
        // (Complete() бросил бы при повторном закрытии, скрыв исходное исключение)
        _channel.Writer.TryComplete();
        await _loop;  // Дописываем оставшееся. Если цикл умер, исходное исключение всплывёт здесь
    }
}

При такой конструкции конфликты записи структурно исключаются, а накопившееся в очереди естественным образом собирается в пакет, так что ускорение из следующего раздела достаётся само. В бизнес-приложении важны и другие детали: дописывание очереди перед закрытием через DisposeAsync при завершении, явное решение о поведении при переполнении через FullMode, а также распространение сбоя цикла записи на отправителей через TryComplete(ex) (если единственный писатель тихо умирает, сбой проявляется как вечное ожидание при заполнении очереди). Та же идея «свести путь к одному» встречается и в проектировании взаимного исключения при обмене через файлы (см. «Интеграция через файлы: блокировки, атомарный claim и практические рекомендации»).

5.2 Объединение в транзакцию меняет порядок величины

SQLite при каждом коммите выполняет синхронную запись на диск (fsync), поэтому INSERT по одной записи с неявным коммитом упирается в потолок в несколько сотен–тысяч записей в секунду даже на SSD, а на HDD — в несколько десятков в секунду. Достаточно объединить те же INSERT в явную транзакцию по 1000 записей (обернуть BeginTransaction и в конце вызвать Commit), чтобы выйти на десятки–сотни тысяч записей в секунду. Изменение в три строки, которое даже неловко называть «тюнингом», меняет производительность на два-три порядка.

Многие жалобы вроде «импорт CSV занимает 20 минут» или «миграция данных при запуске никак не заканчивается» вызваны именно этим и решаются простым переходом на транзакции и повторным использованием параметров (форма кода из предыдущего раздела). С другой стороны, если сделать транзакцию слишком длинной, она заставит ждать другие записи, поэтому практичный компромисс — один коммит на «несколько сотен–тысяч записей или несколько сотен миллисекунд».

6. Ловушки сопоставления типов — что назначить всего четырём типам

SQLite реально умеет хранить всего четыре типа — INTEGER / REAL / TEXT / BLOB, и каждый тип .NET сопоставляется с одним из них. Основные соответствия в Microsoft.Data.Sqlite таковы.⁴

Особого внимания требуют три типа, попадающие в TEXT.

• DateTime: формат в стиле ISO 8601 в TEXT, если формат и часовой пояс унифицированы, даёт совпадение строковой сортировки с хронологической — на практике проблем нет. Но верно и обратное: всё ломается в тот момент, когда UTC и локальное время смешиваются. Единственное решение — заранее решить «хранить в UTC, преобразовывать в локальное только при отображении» и соблюдать это во всех местах кода; функции SQLite вроде datetime('now') тоже возвращают UTC. Ещё момент: при самостоятельном преобразовании в строку обязательно передавайте в ToString CultureInfo.InvariantCulture. Если оставить культуру по умолчанию, на машинах с негригорианской культурой (японское или буддийское летоисчисление и т. п.) представление года изменится только там, что сломает и сортировку, и чтение (см. пример кода в 5.1).
• Guid: хранится как строка, поэтому сопоставление — это сравнение строк. Если другой инструмент или библиотека запишет его в другом представлении (регистр букв, формат BLOB), сверка не удастся, поэтому если к БД обращаются несколько языков/инструментов, зафиксируйте представление явной спецификацией.
• decimal: самая большая ловушка. Он хранится как TEXT, потому что REAL приводит к потере точности⁴, но сравнение вроде WHERE amount > 1000 для столбца TEXT работает не так, как задумано, потому что в порядке типов SQLite TEXT всегда больше числового значения. Агрегатные функции вроде SUM тоже внутренне преобразуются в REAL, теряя ту самую точность, ради которой и был выбран decimal.

Практическая рекомендация по decimal проста: храните денежные суммы как целое число (INTEGER) в минимальной единице валюты. Для японской иены — как long в единицах йен, преобразуя только при отображении. С целым числом сравнение и агрегация точны и быстры, а ловушка типов исчезает. Если из-за существующей схемы вынуждены хранить decimal как есть, смиритесь с тем, что сравнение и агрегацию нужно выполнять не в SQL, а на стороне .NET после чтения данных.

Кроме того, имя типа, указанное в CREATE TABLE, — всего лишь подсказка для «сродства (affinity)», а собственное имя типа вроде STRING — источник аварий из-за неявного преобразования. Официальная рекомендация — использовать для имён типов столбцов только те же четыре — INTEGER / REAL / TEXT / BLOB.⁴

7. Эксплуатация — не ломать, а если сломалось — уметь восстановить

7.1 quick_check при запуске

Хотя SQLite защищён транзакциями, повреждение из-за сбоя диска или ошибочной файловой операции невозможно свести к нулю. Чтобы не продолжать работу с повреждённой БД и не расширять ущерб, добавьте проверку целостности при запуске. Полный integrity_check на большой БД занимает много времени, поэтому для повседневного использования достаточно облегчённой версии quick_check.

using var cmd = conn.CreateCommand();
cmd.CommandText = "PRAGMA quick_check";
var result = (string)cmd.ExecuteScalar()!;
if (result != "ok")
{
    // Не дописываем в повреждённую БД. Переходим в режим только для чтения и предлагаем восстановление
    logger.LogError("Обнаружено повреждение базы данных: {Detail}", result);
    EnterReadOnlyMode(result);
}

Политика не выполнять автоматическое восстановление или автоматический откат при обнаружении повреждения (а привлекать пользователя) описана в разделе 6.2 предыдущей статьи.

7.2 Резервное копирование — почему не подходит копирование файла

Простое копирование файла БД во время работы может захватить промежуточное состояние транзакции, и официальная документация SQLite прямо называет это причиной повреждения.⁵ В режиме WAL, если скопировать только основной файл, оставив без внимания -wal (содержащий ещё не применённые коммиты — глава 4), самые свежие данные пропадут.

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

• VACUUM INTO: одной SQL-инструкцией создаёт дефрагментированную копию минимального размера.⁸ Пример кода приведён в разделе 6.3 предыдущей статьи — обращайтесь туда.
• SqliteConnection.BackupDatabase: обёртка над Backup API SQLite, копирует между объектами соединений.

using var source = new SqliteConnection($"Data Source={dbPath}");
using var target = new SqliteConnection($"Data Source={backupPath}");
source.Open();
target.Open();
source.BackupDatabase(target);  // Даже во время работы получается согласованный снимок

Однако у BackupDatabase есть нюанс. Текущая реализация Microsoft.Data.Sqlite копирует максимально быстро, но блокирует запись из других соединений до завершения.⁹ Если делать резервную копию большой БД во время активных измерений или действий пользователя, записи в этот период проявятся как SQLITE_BUSY или зависание экрана. Безопасное разделение таково: для повседневного создания поколений резервных копий использовать VACUUM INTO, а BackupDatabase приберечь для взаимного копирования с in-memory БД или для дублирования в окно, когда запись остановлена, — например, во время обслуживания. Если для периодического запуска используете Планировщик заданий, не копируйте файл извне, а поручите выполнение указанного выше резервного копирования самому приложению (или небольшому инструменту, который корректно открывает SQLite). Проектирование самого механизма периодического запуска описано в «Планировщик заданий не запускается или завершается с 0x1 — диагностика и надёжная эксплуатация».

7.3 Расположение и миграция

Базовое расположение файла БД — %LOCALAPPDATA%\<название компании>\<название приложения>, а минимальная схема версионирования — миграция при запуске через PRAGMA user_version. Обе темы с примерами кода уже разобраны в предыдущей статье (глава 3 и раздел 6.1), поэтому здесь не повторяем. Добавим лишь одно: если перед выполнением миграции сделать одно поколение резервной копии из раздела 7.2, худший случай — «миграция не удалась, и приложение не запускается» — превращается в простую замену файла.

8. Когда стоит использовать EF Core — где ORM себя оправдывает

До сих пор мы писали код на голом Microsoft.Data.Sqlite, но есть и чёткие случаи, когда стоит использовать SQLite-провайдер EF Core. Критерий выбора — характер приложения.

Если выбираете EF Core, нужно знать ограничения, специфичные для SQLite-провайдера.¹⁰

• Пересборка таблицы из-за ограничений ALTER TABLE: SQLite не поддерживает напрямую изменение типа или удаление столбца, поэтому миграции, включающие AlterColumn или DropColumn, выполняются как пересборка: «создать новую таблицу → скопировать данные → удалить старую таблицу → переименовать». В окружениях с большим объёмом данных это влияет на время применения и использование диска, поэтому изменения схемы больших таблиц нужно планировать заранее.
• Нельзя создать идемпотентный скрипт: скрипты миграции с условиями if-then, как для SQL Server, сгенерировать нельзя. Реалистичный способ применения — dbContext.Database.Migrate() при запуске приложения.
• Операции с decimal / DateTimeOffset вычисляются на стороне клиента: особенности типов из главы 6 никуда не исчезают и в EF Core. Любое сравнение, кроме равенства, и сортировка вычисляются на стороне клиента, поэтому рекомендация хранить суммы как целые числа в минимальной единице действует и в EF Core (можно преобразовывать в long для хранения через value converter).
• WAL включён по умолчанию: БД, созданная EF Core, изначально работает в режиме WAL⁷, поэтому настройка из главы 4 не требуется. Однако понимание поведения по-прежнему необходимо.

Даже при выборе EF Core хорошо работает приём с использованием in-memory БД SQLite для модульных тестов слоя репозитория. Поскольку она работает на том же провайдере, что и в продакшене, зазор вида «на моках проходит, а на реальной БД падает» сужается. О том, на каком уровне писать тесты, см. «Где провести границу между юнит-тестами и интеграционными тестами».

9. Итог

SQLite — библиотека, о которой можно сказать: «просто встроить — 30 минут, а чтобы правильно эксплуатировать — нужно проектирование». При этом необходимое проектирование вполне определено, и если превратить содержание этой статьи в чек-лист, получится шесть пунктов.

• Библиотека — Microsoft.Data.Sqlite (или EF Core поверх него). Не смешивайте с информацией, рассчитанной на System.Data.SQLite
• Включайте режим WAL начиная с первого релиза и понимайте роль файлов -wal / -shm
• Сводите запись к одному пути (очередь записи на Channels), объединяйте мелкие INSERT в транзакции
• Унифицируйте DateTime по UTC, суммы храните как целые числа в минимальной единице валюты. Не сравнивайте и не агрегируйте decimal прямо как TEXT
• Выполняйте quick_check при запуске, резервное копирование — через VACUUM INTO или BackupDatabase. Не копируйте файл во время работы
• Не забывайте SqliteConnection.ClearPool в операциях, удаляющих или заменяющих файл БД

Если узнаёте в этом свою конфигурацию — спасаетесь от database is locked повторными попытками или делаете резервные копии простым копированием файла, — попробуйте один раз пройтись по пунктам этой статьи, пока ничего не сломалось. Исправление каждого из них само по себе — небольшое изменение.

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

• Как выбрать место хранения данных Windows-приложения — таблица решений для SQLite / JSON / реестра / Access
• Хранение секретов в Windows-приложениях — избегаем настроек в открытом виде с помощью DPAPI
• Планировщик заданий не запускается или завершается с 0x1 — диагностика и надёжная эксплуатация
• Практическая таблица решений для C# async/await — Task.Run и ConfigureAwait

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

Komura Software Co., Ltd. занимается ревью проектирования бизнес-приложений со встроенным SQLite (проектирование блокировок, резервного копирования и миграций), расследованием проблем в работающих приложениях — таких как database is locked, повреждение данных, снижение производительности — а также поддержкой миграции с существующих хранилищ данных вроде Access.

• Техническая консультация / ревью архитектуры
• Разработка Windows-приложений
• Использование и миграция существующих активов
• Контакты

Источники

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

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

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

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

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

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

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

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

Почему в SQLite появляется ошибка «database is locked»?

SQLITE_BUSY возникает, когда другое соединение удерживает блокировку записи. Microsoft.Data.Sqlite автоматически повторяет попытку вплоть до тайм-аута команды (по умолчанию 30 секунд), поэтому собственная логика повторов обычно не нужна. Если исключение всё же возникает, причина — либо транзакция длиннее 30 секунд, либо конфликт при повышении транзакции чтения до записи, либо борьба за блокировку между множеством мелких записей из разных потоков; увеличить число повторов эту проблему не решает. Настоящее решение — укорачивать транзакции и сводить путь записи к одному, например через очередь на System.Threading.Channels. Включение режима WAL тоже снимает большую часть блокировок между чтением и записью.

Какую библиотеку выбрать для работы с SQLite из C#?

Для новой разработки базовый выбор — Microsoft.Data.Sqlite, ADO.NET-провайдер, который сопровождает Microsoft (или построенный на нём SQLite-провайдер EF Core). Нативное ядро SQLite входит в NuGet-пакет, отдельной работы по распространению нет. Старожил System.Data.SQLite — совершенно другая библиотека: несовместимы и строка подключения, и обработка типов. Например, Guid по умолчанию хранится как BLOB в System.Data.SQLite и как TEXT в Microsoft.Data.Sqlite. Всегда смотрите, для какой из двух библиотек написан найденный в сети пример.

Можно ли делать резервную копию SQLite простым копированием файла?

Простое копирование файла во время работы приложения запрещено: можно захватить промежуточное состояние транзакции, и официальная документация SQLite прямо называет это причиной повреждения. В режиме WAL файл -wal содержит коммиты, ещё не применённые к основному файлу, поэтому копия одного основного файла теряет самые свежие данные. Правильные способы — VACUUM INTO (одной SQL-инструкцией получается согласованная копия минимального размера) или SqliteConnection.BackupDatabase. Однако BackupDatabase до завершения блокирует запись из других соединений, поэтому для повседневного создания поколений резервных копий безопаснее VACUUM INTO.

Почему массовая вставка INSERT в SQLite работает медленно?

Почти всегда причина — размер коммита. SQLite при каждом коммите выполняет синхронную запись на диск (fsync), поэтому INSERT по одной записи с неявным коммитом даже на SSD упирается в потолок в несколько сотен–тысяч записей в секунду. Те же INSERT, объединённые в явную транзакцию по 1000 записей (обернуть BeginTransaction и в конце вызвать Commit), выходят на десятки–сотни тысяч записей в секунду — ускорение на два-три порядка. Слишком длинная транзакция, наоборот, заставит ждать другие записи, поэтому практичный компромисс — один коммит на несколько сотен–тысяч записей или на несколько сотен миллисекунд.

Об авторе

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

Го Комура

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

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