Базы данных: Часть 4 — SQLite

SQLite — встраиваемая реляционная база данных. В отличие от MySQL и PostgreSQL, у неё нет отдельного серверного процесса: это библиотека на языке C, которая подключается к приложению, а вся база хранится в одном файле на диске. Приложение читает и пишет этот файл напрямую, без сети и без посредника.

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

Что умеет SQLite

Несмотря на компактность, SQLite поддерживает большую часть стандартного SQL:

• транзакции со свойствами ACID — изменения либо сохраняются полностью, либо не сохраняются вовсе, даже при сбое питания;
• индексы, представления, триггеры, внешние ключи;
• оконные функции, общие табличные выражения (WITH), в том числе рекурсивные;
• работу с JSON через встроенные функции;
• полнотекстовый поиск через расширение FTS5.

Максимальный размер базы — около 281 ТБ, на практике ограничение задаёт файловая система.

Где используется SQLite

Мобильные и настольные приложения. SQLite встроена в Android и iOS и служит стандартным способом хранить данные приложения на устройстве. Firefox и Chrome хранят в ней историю, закладки и настройки, Adobe Lightroom Classic — каталог фотографий. Сервер устанавливать не нужно, данные доступны без интернета.

Сайты и небольшие веб-сервисы. Разработчики SQLite считают её подходящей для сайтов с посещаемостью до 100 тысяч запросов в сутки, а при аккуратной настройке — и в несколько раз больше. Сайт sqlite.org сам работает на SQLite и обслуживает 400–500 тысяч HTTP-запросов в сутки на одной виртуальной машине. Условие одно: на сайте преобладает чтение, а запись идёт умеренно.

Встраиваемые системы и IoT. Библиотека занимает несколько сотен килобайт и почти не требует памяти, поэтому её используют в роутерах, бортовой электронике, промышленных контроллерах.

Формат файлов. Файл SQLite удобно использовать как формат документа или архива: его можно передать, скопировать и при этом делать по нему SQL-запросы.

Разработка и тесты. Django по умолчанию создаёт проект с базой SQLite. Для прототипа и автоматических тестов не нужно поднимать отдельный сервер.

Как SQLite работает с записью

Главное ограничение SQLite — в каждый момент времени записывать в базу может только одно соединение. Остальные ждут, пока запись завершится. От того, в каком режиме работает журнал, зависит, мешает ли запись чтению.

Режим по умолчанию (rollback journal). Во время записи база блокируется, и читатели ждут окончания транзакции. Для настольного приложения с одним пользователем это незаметно, для сайта — может стать узким местом.

Режим WAL (Write-Ahead Logging). Изменения сначала записываются в отдельный файл журнала, а в основной файл базы переносятся позже, при контрольной точке. Читатели при этом не блокируют писателя, а писатель не блокирует читателей. Включается одной командой, и режим сохраняется в файле базы:

PRAGMA journal_mode=WAL;

Для сервера, который обращается к SQLite из нескольких потоков или процессов, обычно добавляют ещё две настройки на каждое соединение:

PRAGMA busy_timeout = 5000;   -- ждать до 5 секунд, если база занята, вместо немедленной ошибки
PRAGMA synchronous = NORMAL;  -- в режиме WAL база не повреждается при сбое, но последние транзакции могут потеряться

У режима WAL есть ограничения, о которых стоит знать заранее:

• писатель по-прежнему один — WAL ускоряет чтение, но не делает запись параллельной;
• все процессы, работающие с базой, должны находиться на одном компьютере: WAL использует общую память, поэтому не работает на сетевых файловых системах (NFS, SMB);
• рядом с базой появляются служебные файлы -wal и -shm, их нельзя удалять и нельзя копировать базу, не учитывая их;
• транзакции объёмом больше 100 МБ в режиме WAL работают медленно.

Обслуживание базы

Для работы с базой вручную есть консольная утилита sqlite3:

sqlite3 /var/lib/app/app.db

.tables                 -- список таблиц
.schema orders          -- структура таблицы
.mode box               -- удобный табличный вывод результатов
EXPLAIN QUERY PLAN SELECT * FROM orders WHERE customer_id = 42;  -- использует ли запрос индекс

Несколько операций стоит выполнять периодически:

• PRAGMA optimize; — обновляет статистику, по которой SQLite выбирает индексы. Документация рекомендует вызывать её перед закрытием долгоживущего соединения или раз в несколько часов;
• VACUUM; — пересобирает файл и освобождает место после массового удаления данных. На время выполнения база блокируется, а на диске нужно свободное место размером с базу;
• PRAGMA integrity_check; — проверяет целостность файла.

Резервное копирование

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

Команда .backup в утилите sqlite3. Использует встроенный механизм резервного копирования и делает согласованную копию работающей базы:

sqlite3 /var/lib/app/app.db ".backup '/backup/app-$(date +%F).db'"

VACUUM INTO. Создаёт новую, сжатую копию базы, не изменяя исходную (доступно с версии 3.27.0):

VACUUM INTO '/backup/app-2026-09-21.db';

Litestream. Отдельная программа с открытым исходным кодом, которая непрерывно передаёт изменения из журнала WAL в объектное хранилище S3 или в локальный каталог. Приложение менять не нужно: Litestream работает как отдельный процесс. Это позволяет восстановить базу на момент за несколько секунд до сбоя, а не только на момент последней ночной копии.

Как бы ни делалась копия, её нужно регулярно проверять восстановлением: sqlite3 копия.db "PRAGMA integrity_check;" должна вернуть ok.

Достоинства

• Нет сервера. Не нужно устанавливать, настраивать, обновлять и защищать отдельную службу; нет сетевых задержек.
• Простое развёртывание. База — это один файл, который переносится вместе с приложением.
• Надёжность. Транзакции ACID и тщательное тестирование: SQLite — одна из самых проверенных программ в мире.
• Работа без сети. Приложение полностью функционально офлайн.
• Скорость на чтение. Для небольших и средних объёмов данных запросы к локальному файлу выполняются быстрее, чем к серверу по сети.
• Нет лицензионных ограничений. Общественное достояние.

Недостатки

• Один писатель. При частой параллельной записи SQLite уступает клиент-серверным СУБД даже в режиме WAL.
• Нет сетевого доступа. Если несколько серверов приложений должны работать с одной базой, SQLite не подходит: держать её файл на сетевом диске нельзя.
• Нет пользователей и прав доступа. Доступ к данным определяется правами на файл в операционной системе.
• Нестрогая типизация. По умолчанию столбец может хранить значение любого типа. С версии 3.37.0 для таблицы можно включить строгий режим (CREATE TABLE ... STRICT), в котором типы проверяются.
• Нет горизонтального масштабирования. Все данные находятся на одном диске одного компьютера.

Когда выбирать SQLite, а когда нет

SQLite подходит, если:

• данные используются на том же устройстве или сервере, где работает приложение;
• запросов на чтение намного больше, чем на запись;
• объём данных измеряется гигабайтами, а не терабайтами;
• важна простота: один процесс, один файл, минимум администрирования.

Клиент-серверная СУБД (PostgreSQL, MySQL) нужна, если:

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

Частая ошибка — начать проект на SQLite, запустить второй экземпляр приложения на другом сервере и положить файл базы на общий сетевой диск. Такая схема приводит к блокировкам и повреждению данных; в этот момент пора переходить на серверную СУБД.

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