SQLite — практическая работа и API

ОБЯЗАТЕЛЬНОДЛЯ НОВИЧКОВ

Разработчику Аналитику Инженеру

Другие СУБД в этом цикле: PostgreSQL · MySQL · Microsoft SQL Server · SQL — о разделе.

SQLite — встраиваемая реляционная СУБД. Она работает как библиотека в процессе приложения и хранит базу в одном файле (.sqlite, .db, .sqlite3).

Подходит для:

• мобильных и десктопных приложений;
• локальных инструментов и CLI-утилит;
• тестовых стендов и прототипов;
• edge/embedded-сценариев с одним файлом БД.

---

Как работает SQLite

• Нет отдельного сервера и демона.
• Подключение идёт к файлу БД.
• Транзакции соответствуют ACID.
• Параллельность построена вокруг файловых блокировок, режим WAL повышает конкурентность чтения и записи.
• Типизация динамическая через type affinity.

PRAGMA journal_mode = WAL;
PRAGMA foreign_keys = ON;
PRAGMA synchronous = NORMAL;

---

Подключение и запуск

CLI

sqlite3 app.db

Полезные мета-команды SQLite CLI:

.tables
.schema
.indexes users
.mode column
.headers on
.import users.csv users
.output dump.sql
.dump

URI-подключение

file:app.db
file:app.db?mode=ro
file:memdb1?mode=memory&cache=shared

---

Типы данных и синтаксис

SQLite использует storage classes:

• NULL
• INTEGER
• REAL
• TEXT
• BLOB

Type affinity колонок:

• INTEGER
• TEXT
• BLOB
• REAL
• NUMERIC

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    age INTEGER,
    balance NUMERIC,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP
);

---

Возможности SQLite

• Транзакции (BEGIN, COMMIT, ROLLBACK, SAVEPOINT)
• Индексы, составные индексы, частичные индексы
• CHECK, UNIQUE, FOREIGN KEY (при PRAGMA foreign_keys = ON)
• VIEW, TRIGGER
• CTE и рекурсивные CTE
• Оконные функции (современные версии SQLite)
• UPSERT через ON CONFLICT
• JSON-функции (с расширением JSON1, обычно включено)
• FTS-поиск (FTS5)

---

Доступные и недоступные команды

Категория	Команда / конструкция	Статус в SQLite	Комментарий
DML	SELECT, INSERT, UPDATE, DELETE	Доступно	Базовый SQL поддерживается
DDL	CREATE TABLE, CREATE INDEX, ALTER TABLE, DROP	Доступно частично	ALTER TABLE ограничен
Транзакции	BEGIN, COMMIT, ROLLBACK, SAVEPOINT	Доступно	Полная транзакционная модель
Конфликты	INSERT ... ON CONFLICT DO ...	Доступно	Полноценный UPSERT
Чтение схемы	PRAGMA table_info, sqlite_master	Доступно	Системные метаданные
Сессии/пользователи	CREATE USER, GRANT, REVOKE	Недоступно	Нет встроенной серверной модели ролей
Серверные функции	LISTEN/NOTIFY, фоновые worker-процессы	Недоступно	Нет серверного процесса
Хранимые процедуры	CREATE PROCEDURE	Недоступно	Логика через SQL + приложение
Партиционирование	декларативное партиционирование	Недоступно	Реализуют на уровне приложения
Материализованные представления	CREATE MATERIALIZED VIEW	Недоступно	Можно имитировать таблицей и триггерами
Права на схемы	ACL/roles как в PostgreSQL	Недоступно	Контроль доступа на уровне файла ОС

---

Практика SQL в SQLite

Подключение ограничений

PRAGMA foreign_keys = ON;

CRUD

CREATE TABLE tasks (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  done INTEGER NOT NULL DEFAULT 0
);

INSERT INTO tasks(title) VALUES ('Пройти SQLite');
SELECT id, title, done FROM tasks;
UPDATE tasks SET done = 1 WHERE id = 1;
DELETE FROM tasks WHERE id = 1;

UPSERT

INSERT INTO tasks(id, title, done)
VALUES (1, 'Обновлённая задача', 0)
ON CONFLICT(id) DO UPDATE SET
  title = excluded.title,
  done = excluded.done;

Транзакция

BEGIN;
INSERT INTO tasks(title) VALUES ('A');
INSERT INTO tasks(title) VALUES ('B');
COMMIT;

---

Браузеры и инструменты

• DB Browser for SQLite
• SQLiteStudio
• DBeaver
• DataGrip
• VS Code + SQLite extensions
• Beekeeper Studio

---

API по языкам — подключение, CRUD, библиотеки, классы и функции

Принцип чтения API-блоков

В каждом блоке указаны — библиотека, ключевые классы/типы, свойства и методы, затем рабочий пример подключения и CRUD.

---

C# — Microsoft.Data.Sqlite

Библиотека

• Microsoft.Data.Sqlite (NuGet)

Ключевые классы

• SqliteConnection
• SqliteCommand
• SqliteTransaction
• SqliteDataReader
• SqliteParameter
• SqliteConnectionStringBuilder

Свойства и методы

• SqliteConnection.ConnectionString, Open(), Close(), BeginTransaction()
• SqliteCommand.CommandText, Parameters.AddWithValue(), ExecuteNonQuery(), ExecuteScalar(), ExecuteReader()
• SqliteDataReader.Read(), индексатор reader["column"]

Код ITЗагрузка примера кода…

---

Python — sqlite3 (stdlib)

Библиотека

• стандартный модуль sqlite3

Ключевые классы

• sqlite3.Connection
• sqlite3.Cursor
• sqlite3.Row

Свойства и методы

• sqlite3.connect(path)
• Connection.execute(), cursor(), commit(), rollback(), close()
• Cursor.execute(), executemany(), fetchone(), fetchall()
• Connection.row_factory

Код ITЗагрузка примера кода…

Плейсхолдеры ? и SQLi

В примере выше значения передаются отдельно от текста запроса — СУБД не исполняет ввод как SQL. Конкатенация вроде f"… WHERE id = '{user_id}'" снова открывает типы SQL-инъекций (тавтология, UNION, слепые boolean- и time-based). То же правило для PostgreSQL, MySQL и JDBC — см. PostgreSQL — практическая работа и API, MySQL — практическая работа и API.

---

Java — JDBC + sqlite-jdbc

Библиотека

• org.xerial:sqlite-jdbc

Ключевые классы

• java.sql.Connection
• PreparedStatement
• Statement
• ResultSet
• DriverManager

Свойства и методы

• DriverManager.getConnection("jdbc:sqlite:app.db")
• Connection.prepareStatement(), setAutoCommit(false), commit(), rollback()
• PreparedStatement.setString(), executeUpdate(), executeQuery()
• ResultSet.next(), getInt(), getString()

Код ITЗагрузка примера кода…

---

C — нативный SQLite C API

Библиотека

• sqlite3 (официальная библиотека)

Ключевые типы

• sqlite3* (connection handle)
• sqlite3_stmt* (prepared statement)

Ключевые функции

• Подключение — sqlite3_open(), sqlite3_open_v2(), sqlite3_close_v2()
• Выполнение: sqlite3_exec()
• Подготовленные запросы — sqlite3_prepare_v2(), sqlite3_bind_*(), sqlite3_step(), sqlite3_finalize()
• Чтение данных: sqlite3_column_int(), sqlite3_column_text()
• Ошибки: sqlite3_errmsg(), sqlite3_errcode()
• Транзакции: обычные SQL-команды BEGIN/COMMIT/ROLLBACK

Код ITЗагрузка примера кода…

---

C++ — SQLiteCpp и нативный C API

Библиотеки

• SQLiteCpp (обёртка)
• нативный sqlite3 через extern "C"

Ключевые классы SQLiteCpp

• SQLite::Database
• SQLite::Statement
• SQLite::Transaction
• SQLite::Column
• SQLite::Exception

Свойства и методы

• Database("app.db", SQLite::OPEN_READWRITE|SQLite::OPEN_CREATE)
• exec()
• Statement.bind(), executeStep(), exec()
• Column.getInt(), Column.getString()

Код ITЗагрузка примера кода…

---

Go — database/sql + драйвер SQLite

Библиотеки

• database/sql (стандарт)
• драйвер github.com/mattn/go-sqlite3 или modernc.org/sqlite

Ключевые типы

• *sql.DB
• *sql.Tx
• *sql.Stmt
• *sql.Rows
• sql.Result

Свойства и методы

• sql.Open("sqlite3", "app.db")
• DB.Exec(), DB.Query(), DB.QueryRow(), DB.Begin()
• Tx.Exec(), Tx.Commit(), Tx.Rollback()
• Rows.Next(), Rows.Scan()

Код ITЗагрузка примера кода…

---

PHP — PDO_SQLITE и SQLite3

Библиотеки

• PDO + DSN sqlite:app.db
• встроенный класс SQLite3

Ключевые классы PDO

• PDO
• PDOStatement

Ключевые классы SQLite3

• SQLite3
• SQLite3Stmt
• SQLite3Result

Свойства и методы

• new PDO('sqlite:app.db'), prepare(), execute(), fetchAll()
• new SQLite3('app.db'), prepare(), bindValue(), query(), querySingle()

<?php
$db = new PDO('sqlite:app.db');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$db->exec("CREATE TABLE IF NOT EXISTS users(id INTEGER PRIMARY KEY, name TEXT NOT NULL)");

$stmt = $db->prepare("INSERT INTO users(name) VALUES (:name)");
$stmt->execute([':name' => 'Anna']);

$rows = $db->query("SELECT id, name FROM users")->fetchAll(PDO::FETCH_ASSOC);
foreach ($rows as $row) {
    echo $row['id'] . " " . $row['name'] . PHP_EOL;
}

---

Kotlin — JDBC и Android SQLite API

Библиотеки и платформы

• JVM: JDBC (sqlite-jdbc)
• Android: SQLiteOpenHelper, SQLiteDatabase

Ключевые классы JVM

• java.sql.Connection
• PreparedStatement
• ResultSet

Ключевые классы Android

• SQLiteOpenHelper
• SQLiteDatabase
• ContentValues
• Cursor

Методы Android

• onCreate(), onUpgrade()
• writableDatabase, readableDatabase
• insert(), update(), delete(), query(), rawQuery()

Код ITЗагрузка примера кода…

---

Rust — rusqlite

Библиотека

• rusqlite

Ключевые типы

• Connection
• Transaction
• Statement
• Row
• Result<T>

Методы

• Connection::open()
• execute(), prepare(), query_map()
• transaction(), commit(), rollback()
• макрос params![]

Код ITЗагрузка примера кода…

---

JavaScript — Node.js (better-sqlite3 и sqlite3)

Библиотеки

• better-sqlite3 (синхронный, быстрый, простой API)
• sqlite3 (асинхронный callback API)

Ключевые классы/объекты better-sqlite3

• Database
• Statement
• методы prepare(), run(), get(), all(), transaction()

Ключевые объекты sqlite3

• Database
• методы run(), get(), all(), serialize(), close()

Код ITЗагрузка примера кода…

---

Минимальный шаблон безопасной работы

• Всегда используйте параметризованные запросы (?, $1, :name).
• Включайте foreign_keys.
• Явно управляйте транзакциями в пакетных операциях.
• Делайте резервную копию файла БД перед миграциями.
• Для многопоточного чтения/записи включайте WAL.

PRAGMA foreign_keys = ON;
PRAGMA journal_mode = WAL;
PRAGMA busy_timeout = 5000;

---

Когда SQLite подходит и когда лучше серверная СУБД

SQLite подходит:

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

Серверная СУБД лучше:

• много одновременных записей;
• централизованные роли и права;
• горизонтальное масштабирование;
• репликация и кластерные сценарии.

---