SQLite

Что такое SQLite

SQLite — встраиваемая реляционная СУБД. Её главное отличие от PostgreSQL и MySQL в том, что никакого сервера нет: SQLite — это библиотека, которую линкуют прямо в приложение, а вся база лежит в одном файле на диске.

Ни процесса, ни порта, ни пользователей, ни настройки. Приложение вызывает функцию — библиотека читает и пишет байты в файл. Отсюда парадокс: SQLite — самая распространённая СУБД в мире, потому что она есть в каждом Android- и iOS-устройстве, в браузерах, в Python из коробки, в бортовых системах самолётов.

Код SQLite находится в общественном достоянии (public domain) — использовать можно как угодно и без указания авторства.

Файл вместо сервера

sqlite3 app.db          # файла нет — он создастся

sqlite> CREATE TABLE notes (id INTEGER PRIMARY KEY, body TEXT, ts INTEGER);
sqlite> INSERT INTO notes (body, ts) VALUES ('привет', strftime('%s','now'));
sqlite> .tables
sqlite> .schema notes
sqlite> .quit

ls -lh app.db      # вся база — один файл
cp app.db backup.db   # копия базы — обычный cp

Из Python — без установки чего-либо, модуль в стандартной библиотеке:

import sqlite3

con = sqlite3.connect("app.db")
con.execute("INSERT INTO notes (body, ts) VALUES (?, ?)", ("текст", 1234567890))
con.commit()
print(con.execute("SELECT * FROM notes").fetchall())

Что SQLite умеет

Это не «урезанный SQL», как часто думают. SQLite поддерживает полноценные транзакции с ACID, индексы (B-tree, частичные, по выражениям), JOIN, оконные функции, CTE и рекурсивные запросы, внешние ключи, триггеры, представления, полнотекстовый поиск через FTS5, json -функции.

Одна ловушка: внешние ключи по умолчанию выключены ради обратной совместимости. Их надо включать в каждом соединении:

PRAGMA foreign_keys = ON;

Ограничения

• Одна запись за раз. Читателей может быть много, но пишущая транзакция — только одна на всю базу. Это главный потолок.
• Нет сетевого доступа. Работает только локальный файл. База на NFS-шаре — путь к повреждению данных: блокировки поверх сетевых ФС ненадёжны.
• Нет пользователей и прав. Кто может читать файл — тот читает всю базу. Разграничение — только правами файловой системы.
• Динамическая типизация. Колонка объявлена INTEGER, но принимает строку — SQLite хранит тип в значении, а не в колонке.
• Ограниченный ALTER TABLE. Переименовать таблицу и добавить колонку можно, а сложные изменения делаются через пересоздание таблицы.

WAL — обязательная настройка

По умолчанию SQLite использует журнал отката: пишущая транзакция блокирует читателей. Режим WAL это меняет — читатели работают параллельно с писателем:

PRAGMA journal_mode = WAL;      -- сохраняется в файле базы, задаётся один раз
PRAGMA synchronous = NORMAL;    -- разумный компромисс в паре с WAL
PRAGMA busy_timeout = 5000;     -- ждать 5 с вместо мгновенной ошибки "database is locked"

Три эти строки решают большинство жалоб на SQLite. SQLITE_BUSY («database is locked») — самая частая ошибка, и возникает она обычно потому, что busy_timeout не выставлен: параллельная запись не ждёт своей очереди, а сразу падает.

В режиме WAL рядом с базой появляются файлы -wal и -shm — копировать базу нужно вместе с ними либо штатной командой:

sqlite3 app.db ".backup backup.db"   # согласованная копия на живой базе
sqlite3 app.db .dump > app.sql       # логический дамп в SQL

Когда использовать

Подходит:

• Мобильные и десктопные приложения — по сути, безальтернативно.
• Локальное хранение: настройки, кеш, история, офлайн-данные.
• Тесты и прототипы — база поднимается в памяти (:memory:) за миллисекунды.
• Небольшие сайты и внутренние сервисы с преобладанием чтения: на одном сервере SQLite спокойно тянет заметную нагрузку и избавляет от эксплуатации отдельной СУБД.
• Формат обмена данными — файл базы как переносимый контейнер вместо CSV.

Не подходит:

• Много параллельных писателей — упрётесь в блокировку записи.
• Несколько серверов приложений за балансировщиком — общего файла у них нет.
• Данные, объём которых требует репликации и шардирования.
• Задачи, где нужны роли и разграничение доступа на уровне базы.

Практическое правило: начинать с SQLite не стыдно. Переезд на PostgreSQL — это в основном смена строки подключения и диалекта, и делать его стоит, когда упрётесь в конкретное ограничение, а не заранее.

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

• СУБД — встраиваемые против клиент-серверных
• SQL — язык, который SQLite поддерживает почти полностью
• PostgreSQL — куда переезжают, когда SQLite становится тесно
• Реляционные и NoSQL БД — модель данных SQLite
• Индексы в БД — работают и здесь
• Дамп базы данных — .dump и .backup