SQLite: введение во встраиваемую базу данных

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

Как устроена SQLite

Обычная СУБД вроде PostgreSQL работает как отдельная программа-сервер: приложение подключается к ней по сети, передает запросы и получает ответы. SQLite устроена иначе. Это библиотека на языке C, которая встраивается прямо в приложение и читает и пишет файл базы на диске сама. Нет ни сервера, ни порта, ни пользователей с паролями.

Вся база, то есть таблицы, индексы, представления и триггеры, лежит в одном файле с любым расширением, чаще .db, .sqlite или .sqlite3. Формат файла одинаковый на Windows, Linux, macOS и мобильных системах, поэтому базу можно просто скопировать на другой компьютер. Исходный код SQLite передан в общественное достояние, ее можно использовать в любых проектах без лицензионных отчислений.

Несмотря на компактность, SQLite поддерживает основную часть стандарта SQL: транзакции с гарантиями ACID, индексы, подзапросы, JOIN, представления, триггеры, оконные функции и общие табличные выражения (WITH).

Где применяется SQLite

По оценке разработчиков, SQLite - самая распространенная СУБД в мире: она есть в каждом смартфоне и почти в каждом браузере. Типичные сценарии использования:

• Мобильные приложения. Android и iOS включают SQLite в систему, в ней хранят контакты, сообщения, настройки.
• Настольные программы. Браузеры держат в SQLite историю, закладки и cookie, многие программы хранят в ней свои данные вместо самописных форматов.
• Сайты с умеренной нагрузкой. Блог или сервис, где запись идет нечасто, спокойно работает на SQLite.
• Прототипы и учебные проекты. Не нужно ставить и настраивать сервер, база создается одной строкой кода.
• Тесты. База в оперативной памяти (:memory:) создается за миллисекунды и исчезает после теста.
• Анализ данных и обмен файлами. Один файл .db удобнее набора CSV: в нем есть типы, связи и индексы.

Сравнение с серверными СУБД

SQLite не конкурирует с PostgreSQL и MySQL напрямую, у них разные задачи. Серверная СУБД рассчитана на много клиентов, которые одновременно пишут в базу по сети. SQLite заменяет не сервер баз данных, а функцию open(): это способ надежно хранить данные приложения.

Главное ограничение - запись. Пока одна транзакция пишет, остальные ждут, а при долгом ожидании получают ошибку database is locked. Режим журнала WAL (PRAGMA journal_mode = WAL) позволяет читать параллельно с записью, но писатель по-прежнему один. Если приложение принимает много одновременных записей от разных пользователей, лучше сразу выбрать серверную СУБД.

Особенности типов и ограничений

В SQLite пять классов хранения: NULL, INTEGER, REAL, TEXT и BLOB. Тип, указанный при создании столбца, задает лишь предпочтение. В столбец INTEGER можно записать строку "abc", и ошибки не будет. Отдельных типов BOOLEAN и DATETIME нет: логические значения хранят как 0 и 1, даты как текст в формате ISO или как число. Подробности в статье про типы данных SQLite.

Начиная с версии 3.37 можно объявить таблицу с ключевым словом STRICT, тогда SQLite проверяет типы так же жестко, как серверные СУБД.

CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    name  TEXT NOT NULL,
    age   INTEGER
) STRICT;

Еще одна особенность, о которой часто забывают: проверка внешних ключей по умолчанию выключена. Чтобы SQLite не давала вставить ссылку на несуществующую строку, после каждого подключения выполняют PRAGMA foreign_keys = ON. Команда ALTER TABLE тоже ограничена: можно переименовать таблицу или столбец, добавить и удалить столбец, а для остального таблицу пересоздают.

Первая база на Python

Для работы с SQLite в Python ничего устанавливать не нужно: модуль sqlite3 входит в стандартную библиотеку. Функция connect() открывает файл базы, а если его нет, создает новый.

import sqlite3

conn = sqlite3.connect("library.db")
conn.execute("""
    CREATE TABLE IF NOT EXISTS books (
        id     INTEGER PRIMARY KEY,
        title  TEXT NOT NULL,
        year   INTEGER
    )
""")
books = [("Мастер и Маргарита", 1967), ("Пикник на обочине", 1972)]
conn.executemany("INSERT INTO books (title, year) VALUES (?, ?)", books)
conn.commit()

for row in conn.execute("SELECT id, title, year FROM books ORDER BY year"):
    print(row)
conn.close()
# (1, 'Мастер и Маргарита', 1967)
# (2, 'Пикник на обочине', 1972)

Значения в запрос передаются через знаки ?, а не подстановкой в строку. Так модуль сам экранирует данные, и SQL-инъекция невозможна. Без вызова commit() вставленные строки не сохранятся в файле. Подробный разбор подключения, курсоров и транзакций есть в статье Python sqlite, а синтаксис выборки в материале про оператор SELECT.

Инструменты для просмотра базы

Файл SQLite удобно открывать не только из кода. Консольная утилита sqlite3 с официального сайта sqlite.org принимает SQL-запросы и служебные команды с точкой: .tables показывает список таблиц, .schema их структуру, .quit завершает работу.

Для работы мышкой есть графические программы. Самая популярная бесплатная - DB Browser for SQLite: в ней можно создать таблицы, просмотреть и отредактировать строки, выполнить запрос и экспортировать данные в CSV. Также SQLite поддерживают универсальные клиенты вроде DBeaver и встроенные инструменты баз данных в IDE.

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

Короткие ответы на вопросы, которые задают при знакомстве с SQLite.

Нужно ли устанавливать SQLite для работы из Python?

Нет. Модуль sqlite3 и сама библиотека SQLite поставляются вместе с Python. Версию движка можно узнать так: sqlite3.sqlite_version.

Какой максимальный размер базы SQLite?

Теоретический предел около 281 терабайта. На практике раньше упираются в ограничения файловой системы и в то, что большие объемы удобнее обслуживать серверной СУБД.

Подходит ли SQLite для сайта?

Для сайта, где в основном читают, а пишут редко, подходит хорошо. Для сервиса с частыми одновременными записями от многих пользователей лучше PostgreSQL или MySQL, потому что SQLite пропускает только одного пишущего за раз.

Как защитить базу SQLite паролем?

В стандартной сборке шифрования и паролей нет, доступ к данным определяется правами на файл. Для шифрования используют отдельные сборки, например SQLCipher.