Skip to content

SQLite Шпаргалка

Самодостаточный бессерверный движок SQL-баз данных без конфигурации.

01

Начало работы

Основы CLI

SQLite хранит всю базу данных в одном файле. CLI sqlite3 открывает или создаёт файл .sqlite/.db по требованию. В отличие от MySQL/PostgreSQL, серверного процесса нет — приложения линкуют библиотеку SQLite напрямую. .dump экспортирует всю базу как текстовый SQL для резервной копии или миграции.

sqlite
# open/create a database file
sqlite3 mydb.sqlite

# at the sqlite> prompt
.help              # show help
.tables            # list tables
.schema users      # show CREATE statement for users
.databases         # list attached databases
.dump              # dump entire DB as SQL text
.quit              # exit

# execute SQL from a file
sqlite3 mydb.sqlite < script.sql

# execute a query from command line
sqlite3 mydb.sqlite "SELECT COUNT(*) FROM users;"

Dot-команды

Dot-команды интерпретирует CLI sqlite3, а не движок SQL — им никогда не нужна точка с запятой, и они недоступны через программные API. .mode управляет форматом вывода (попробуйте 'column' с .header on для читаемого интерактивного использования). .schema показывает операторы CREATE; .fullschema добавляет индексы, представления и триггеры. .help подскажет остальное.

sqlite
-- dot commands are CLI-only, NOT SQL (no semicolon needed)
.help                 -- list all dot commands
.tables               -- list all tables
.schema               -- schema for all tables
.schema users         -- schema for the users table
.fullschema           -- schema incl. indexes, triggers, views
.indices users        -- list indexes on users
.header on            -- show column headers
.mode column          -- column-aligned output
.mode list            -- default, pipe-separated
.mode csv             -- CSV output
.mode json            -- JSON output
.show                 -- show current settings
.width 10 20 30       -- set column widths
.timer on             -- show query execution time

Управление файлами базы

ATTACH DATABASE позволяет одному соединению запрашивать до 10 файлов базы как схемы (main, temp и подключённые). URI :memory: создаёт чисто in-memory базу, исчезающую при закрытии, — идеально для тестов и черновых вычислений. URI-имена файлов (file:...) включают режимы readonly, shared-cache и другие. Каждая подключённая база — отдельный файл на диске.

sqlite
-- attach another database file to the current connection
ATTACH DATABASE 'archive.sqlite' AS archive;
DETACH DATABASE archive;

-- list attached databases (main + attached)
.databases

-- the in-memory database (never written to disk)
sqlite3 :memory:

-- temporary database (deleted when connection closes)
sqlite3 ""          -- empty arg => temp DB

-- open read-only
sqlite3 "file:mydb.sqlite?mode=ro" --readonly

-- query the underlying page size / file format
PRAGMA page_size;
PRAGMA journal_mode;

Заголовки и форматирование вывода

Форматирование вывода влияет только на CLI и никогда на сами данные. Режим 'column' с .headers on самый читаемый для человека. 'list' (по умолчанию) лучше для shell-конвейеров. 'json' и 'csv' превращают SQLite в удобный конвертер. .output перенаправляет результаты в файл; не забудьте вернуться через .output stdout. .mode box/table рисуют ASCII-рамки.

sqlite
-- readable interactive mode
.headers on
.mode column
.width 15 20 10
SELECT id, name, email FROM users LIMIT 5;

-- pipe-separated (good for scripts)
.mode list
.separator "|"
SELECT id, name FROM users;

-- boxed table output (sqlite 3.36+)
.mode box
.mode table

-- JSON output for piping to other tools
.mode json
SELECT id, name FROM users;

-- write output to a file
.output users.txt
SELECT id, name FROM users;
.output stdout

Выполнение скриптов и SQL-файлов

.read выполняет файл с SQL внутри открытой сессии CLI; перенаправление через < запускает его до интерактивного приглашения. --bail останавливает пакет на первой ошибке (иначе SQLite продолжает, что может маскировать сбои). .parameter (3.44+) связывает именованные параметры из оболочки, избегая интерполяции строк. В разработке всегда проверяйте скрипты с .echo on.

sqlite
-- run a .sql file from the shell
sqlite3 mydb.sqlite ".read setup.sql"
-- or piped in
sqlite3 mydb.sqlite < setup.sql

-- inside the CLI
.read setup.sql

-- stop on first error (shell flag)
sqlite3 mydb.sqlite --bail < script.sql

-- echo commands as they run
.echo on
.read migrations.sql

-- show EXPLAIN before each statement (debug)
.explain on

-- parameterize from the shell (sqlite 3.44+)
sqlite3 mydb.sqlite \
  -cmd ".parameter set @min 18" \
  "SELECT name FROM users WHERE age >= @min"
02

Таблицы и типы данных

Создание таблиц

INTEGER PRIMARY KEY — алиас rowid: он автоинкрементируется и является самым быстрым ключом. AUTOINCREMENT меняет алгоритм так, что удалённые ID никогда не переиспользуются (чуть медленнее; обычно не нужно). DEFAULT (expr) допускает произвольные выражения вроде datetime('now'). CREATE TABLE AS SELECT создаёт таблицу с результатами запроса, но не копирует ограничения и индексы.

sqlite
CREATE TABLE users (
  id       INTEGER PRIMARY KEY AUTOINCREMENT,
  username TEXT    NOT NULL UNIQUE,
  email    TEXT    NOT NULL UNIQUE,
  age      INTEGER CHECK (age >= 0),
  role     TEXT    NOT NULL DEFAULT 'user',
  created  TEXT    NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS orders (
  id        INTEGER PRIMARY KEY,
  user_id   INTEGER NOT NULL,
  total     REAL    NOT NULL DEFAULT 0,
  FOREIGN KEY (user_id) REFERENCES users(id)
    ON DELETE CASCADE ON UPDATE CASCADE
);

-- quick table from a query
CREATE TABLE archive AS
  SELECT * FROM orders WHERE created < '2024-01-01';

Классы хранения и сродство типов

SQLite использует динамическую типизацию: любой столбец может хранить любой из 5 классов хранения. «Сродство типов» старается преобразовать значения, когда возможно (например, '42' в INTEGER-столбце становится числом 42), но никогда не выдаёт ошибку при несоответствии. Таблицы STRICT (3.37+) возвращают традиционную жёсткую проверку типов по столбцам. typeof() показывает фактический класс хранения. Гибкость мощна, но требует дисциплины — для новых схем, где важна целостность данных, используйте STRICT.

sqlite
-- SQLite has 5 storage classes (not strict types):
--   NULL, INTEGER, REAL, TEXT, BLOB
CREATE TYPE demo...  -- not supported

-- columns have 'type affinity', not enforced types
CREATE TABLE t (
  a INTEGER,   -- affinity INTEGER
  b TEXT,      -- affinity TEXT
  c REAL,      -- affinity REAL
  d BLOB,      -- affinity BLOB (no conversion)
  e            -- no affinity (accepts anything)
);

-- this WORKS despite 'INTEGER' column (dynamic typing)
INSERT INTO t (a) VALUES ('hello');
SELECT typeof(a) FROM t;   -- 'text'

-- STRICT tables (SQLite 3.37+) actually enforce types
CREATE TABLE strict_t (a INTEGER, b TEXT) STRICT;
INSERT INTO strict_t VALUES ('hi', 1);   -- error

Частые типы данных

В SQLite нет нативных типов DATE/TIME и BOOLEAN — храните отметки времени как ISO-8601 TEXT ('YYYY-MM-DD HH:MM:SS') для сортируемости и обрабатывайте их функциями даты и времени. Булевы — это целые 0/1. Ограничения длины VARCHAR(n) игнорируются (только для совместимости). Сродство NUMERIC сначала пробует INTEGER, затем REAL, сохраняя точные десятичные, когда возможно. BLOB хранит сырые байты как есть.

sqlite
-- integers
CREATE TABLE numerics (
  small  INTEGER,        -- 1/2/4/8 bytes depending on value
  big    INTEGER,        -- up to 64-bit
  -- no separate BIGINT/SMALLINT, all map to INTEGER affinity
  flag   BOOLEAN         -- stored as 0 (false) or 1 (true)
);

-- text & blobs
CREATE TABLE blobs (
  name   TEXT,           -- variable-length UTF-8, no length limit
  data   BLOB,           -- raw bytes, no conversion
  note   VARCHAR(255)    -- length ignored, affinity TEXT
);

-- real numbers
CREATE TABLE floats (
  price  REAL,           -- 8-byte IEEE float
  precise NUMERIC(10,2)  -- affinity NUMERIC, decimal preserved if exact
);

-- dates: SQLite has no native DATE type — store as TEXT (ISO-8601)
CREATE TABLE events (
  ts TEXT  -- 'YYYY-MM-DD HH:MM:SS' recommended
);

Изменение таблиц

ALTER TABLE в SQLite намеренно ограничен для бессерверного движка. ADD COLUMN работает за O(1) (без перезаписи таблицы). RENAME COLUMN (3.25+) и DROP COLUMN (3.35+) — современные удобства. Для смены типов или добавления ограничений используйте паттерн пересборки таблицы из 12 шагов (создать новую, скопировать, удалить, переименовать, пересоздать индексы). На время пересборки внешние ключи нужно временно отключить — см. PRAGMA legacy_alter_table.

sqlite
-- add a column (fast, appends to end)
ALTER TABLE users ADD COLUMN bio TEXT;

-- rename a column (SQLite 3.25+)
ALTER TABLE users RENAME COLUMN name TO username;

-- rename a table
ALTER TABLE users RENAME TO accounts;

-- drop a column (SQLite 3.35+)
ALTER TABLE users DROP COLUMN deprecated_field;

-- SQLite CANNOT: change a column type, add constraints to
-- an existing column, or reorder columns. Workaround:
-- 1) create new table with desired schema
-- 2) INSERT INTO new SELECT * FROM old
-- 3) DROP old; ALTER TABLE new RENAME TO old
-- 4) recreate indexes/triggers

Ограничения

Ограничения обеспечивают целостность данных на уровне движка. PRIMARY KEY подразумевает NOT NULL и UNIQUE. CHECK может ссылаться на любой столбец строки. Клаузы ON CONFLICT (OR IGNORE / OR REPLACE / OR ABORT / OR FAIL / OR ROLLBACK) управляют разрешением конфликтов на уровне оператора — OR REPLACE удаляет конфликтующую строку, а затем вставляет, что сбрасывает её rowid. Для семантики UPSERT предпочитайте ON CONFLICT ... DO UPDATE (см. раздел CRUD).

sqlite
CREATE TABLE products (
  id         INTEGER PRIMARY KEY,
  sku        TEXT    NOT NULL UNIQUE,
  name       TEXT    NOT NULL,
  price      REAL    NOT NULL CHECK (price >= 0),
  stock      INTEGER NOT NULL DEFAULT 0 CHECK (stock >= 0),
  category   TEXT    CHECK (category IN ('a','b','c')),

  -- composite unique constraint
  UNIQUE (name, category),

  -- table-level check
  CHECK (price * stock < 1000000)
);

-- name a constraint for clearer error messages
CREATE TABLE t (
  x INTEGER CONSTRAINT x_positive CHECK (x > 0)
);

-- conflict handling
INSERT OR IGNORE INTO products (sku, name, price) VALUES (...);
INSERT OR REPLACE INTO products (...) VALUES (...);
UPDATE OR ABORT products SET stock = stock - 1 WHERE id = 1;
03

Операции CRUD

INSERT

Многострочный INSERT кардинально быстрее вставки по одной в цикле — объединяйте их в батчи. INSERT ... SELECT копирует данные между таблицами. last_insert_rowid() возвращает rowid последнего INSERT в текущем соединении (не транзакции). В приложениях предпочитайте параметризованные вставки (см. раздел интеграции), чтобы избежать SQL-инъекций и проблем с кавычками. Используйте DEFAULT, чтобы явно взять значение по умолчанию для столбца.

sqlite
-- single row
INSERT INTO users (username, email, age)
VALUES ('alice', '[email protected]', 30);

-- multiple rows in one statement (fast)
INSERT INTO users (username, email) VALUES
  ('bob', '[email protected]'),
  ('carol', '[email protected]'),
  ('dave', '[email protected]');

-- insert from a query
INSERT INTO archive_users
SELECT * FROM users WHERE active = 0;

-- explicit NULL / default
INSERT INTO users (username, email, age)
VALUES ('eve', '[email protected]', DEFAULT);

-- return the new rowid (handy in apps)
INSERT INTO users (username) VALUES ('frank');
SELECT last_insert_rowid();   -- returns the new id

UPSERT (ON CONFLICT)

ON CONFLICT (UPSERT) — идиома SQLite для «вставить или обновить, если существует», гораздо безопаснее INSERT OR REPLACE, который удаляет существующую строку (срабатывают DELETE-триггеры, rowid сбрасывается). 'excluded' ссылается на строку, предлагаемую для вставки. DO NOTHING молча пропускает. Целью конфликта может быть любое ограничение UNIQUE или PRIMARY KEY. Это правильный паттерн для счётчиков, обновления last-seen и идемпотентного импорта.

sqlite
-- insert, but update on conflict
INSERT INTO users (id, username, email)
VALUES (1, 'alice', '[email protected]')
ON CONFLICT(id) DO UPDATE SET
  email  = excluded.email,
  username = excluded.username;

-- ignore on conflict
INSERT INTO users (id, username)
VALUES (1, 'alice')
ON CONFLICT(id) DO NOTHING;

-- conflict on a specific unique constraint
INSERT INTO users (username, email)
VALUES ('alice', '[email protected]')
ON CONFLICT(email) DO UPDATE SET
  username = excluded.username;

UPDATE

Всегда указывайте WHERE, если только вы действительно не хотите обновить все строки — в SQLite нет режима пробного прогона. UPDATE ... FROM (3.33+) и UPDATE ... RETURNING (3.35+) приближают SQLite к эргономике PostgreSQL. RETURNING бесценен для приложений, которым нужно состояние после обновления без отдельного SELECT. CASE позволяет делать массовые обновления одним оператором с логикой по строкам, что намного быстрее обновления по одной.

sqlite
-- basic update (ALWAYS use WHERE in production!)
UPDATE users SET age = 31 WHERE id = 1;

-- multiple columns
UPDATE users
SET age = age + 1, role = 'admin'
WHERE username = 'alice';

-- conditional update with CASE
UPDATE products
SET price = CASE
  WHEN category = 'a' THEN price * 0.9
  WHEN category = 'b' THEN price * 0.95
  ELSE price
END;

-- update from another table (subquery)
UPDATE orders
SET total = (SELECT SUM(qty * price)
             FROM items WHERE order_id = orders.id);

-- UPDATE ... RETURNING (SQLite 3.35+)
UPDATE users SET role = 'admin'
WHERE id IN (1,2,3)
RETURNING id, username, role;

DELETE

В SQLite нет TRUNCATE — DELETE FROM table удаляет все строки, но срабатывают построчные триггеры, и счётчик AUTOINCREMENT (хранится в sqlite_sequence) не сбрасывается. DELETE ... RETURNING (3.35+) логирует удалённое. Для аудиторских данных предпочитайте мягкое удаление (столбец deleted_at) жёсткому. Освобождённое удалением место переиспользуется будущими вставками; VACUUM возвращает его ОС.

sqlite
-- basic delete
DELETE FROM users WHERE id = 1;

-- delete with a subquery condition
DELETE FROM orders
WHERE user_id IN (SELECT id FROM users WHERE active = 0);

-- DELETE ... RETURNING
DELETE FROM sessions
WHERE expires < datetime('now')
RETURNING id, user_id;

-- delete ALL rows (fast, but fires triggers)
DELETE FROM logs;

-- TRUNCATE equivalent: delete all + reset rowid
DELETE FROM logs;
DELETE FROM sqlite_sequence WHERE name = 'logs';

-- soft delete pattern
UPDATE users SET deleted_at = datetime('now') WHERE id = 1;

Основы SELECT

SELECT — рабочая лошадка. LIMIT count OFFSET skip реализует пагинацию (keyset-пагинация быстрее при больших смещениях — см. раздел запросов). Алиасы (AS, необязательно) улучшают читаемость и обязательны для вычисляемых столбцов в представлениях. DISTINCT схлопывает одинаковые строки; для более тонкого контроля рассмотрите GROUP BY. CASE добавляет логику if/else в результаты и работает в любой клаузе.

sqlite
-- basic select
SELECT id, username, email FROM users;

-- limit and offset
SELECT id, username FROM users LIMIT 10 OFFSET 20;
SELECT id, username FROM users LIMIT 20, 10;  -- offset, count

-- aliasing columns and tables
SELECT u.id AS user_id, u.username AS name
FROM users AS u;

-- expressions
SELECT
  username,
  age,
  age * 365 AS days_alive,
  UPPER(username) AS upper_name
FROM users;

-- distinct rows
SELECT DISTINCT category FROM products;

-- conditional output
SELECT name,
  CASE WHEN age >= 18 THEN 'adult' ELSE 'minor' END AS status
FROM users;
04

Запросы и фильтрация

WHERE и операторы

NULL особенный в SQL — это «неизвестно», а не значение. = NULL всегда возвращает NULL (трактуется как false), поэтому используйте IS NULL / IS NOT NULL. NULL в сочетании с AND/OR подчиняется трёхзначной логике. BETWEEN включает границы. IN — сокращение для набора равенств через OR; избегайте IN (NULL, ...), так как он ведёт себя странно. Щедро используйте скобки, чтобы составные условия были однозначными.

sqlite
-- comparison operators
SELECT * FROM products WHERE price < 50;
SELECT * FROM products WHERE price BETWEEN 10 AND 100;
SELECT * FROM users WHERE age IN (18, 21, 25);
SELECT * FROM users WHERE age NOT IN (18, 21);

-- NULL handling (NEVER use = NULL)
SELECT * FROM users WHERE email IS NULL;
SELECT * FROM users WHERE email IS NOT NULL;

-- logical operators
SELECT * FROM users
WHERE age >= 18 AND age <= 65 AND role = 'admin';

SELECT * FROM users
WHERE role = 'admin' OR role = 'editor';

-- range with multiple conditions
SELECT * FROM orders
WHERE (status = 'shipped' AND total > 100)
   OR (status = 'pending' AND total > 500);

ORDER BY и LIMIT

ORDER BY без уникального «разрывателя ничьих» даёт недетерминированный порядок между запусками — всегда добавляйте уникальный столбец (например, id) последним ключом сортировки. Пагинация через OFFSET работает за O(n), потому что сканирует и отбрасывает строки; keyset-пагинация (WHERE id > last_seen_id) работает за O(limit) и подходит для больших таблиц. RANDOM() требует полной сортировки — используйте только на небольших результатах или выборках.

sqlite
-- ascending (default) / descending
SELECT * FROM users ORDER BY username;
SELECT * FROM users ORDER BY created DESC;
SELECT * FROM users ORDER BY age DESC, username ASC;

-- sort with NULLs first or last (SQLite 3.30+)
SELECT * FROM tasks ORDER BY due_date ASC NULLS FIRST;
SELECT * FROM tasks ORDER BY due_date DESC NULLS LAST;

-- pagination: keyset is faster than OFFSET
-- slow (offset scans & discards rows):
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 10000;
-- fast (keyset):
SELECT * FROM users WHERE id > 10000 ORDER BY id LIMIT 10;

-- random sample
SELECT * FROM users ORDER BY RANDOM() LIMIT 5;

Сопоставление по шаблону

LIKE по умолчанию регистронезависим для ASCII (частая неожиданность — для регистрозависимости используйте COLLATE BINARY). GLOB регистрозависим и поддерживает классы символов вроде [A-Z]. REGEXP не встроен — нужно зарегистрировать функцию или загрузить расширение. Для полнотекстового поиска в масштабе используйте FTS5 (см. раздел производительности) вместо LIKE '%term%', который не может использовать индексы.

sqlite
-- LIKE: case-insensitive by default for ASCII,
--   wildcards % (any chars) and _ (single char)
SELECT * FROM users WHERE username LIKE 'al%';
SELECT * FROM users WHERE email LIKE '%@gmail.com';
SELECT * FROM users WHERE username LIKE '_lice';

-- case-sensitive LIKE
SELECT * FROM users WHERE username LIKE 'AL%' COLLATE BINARY;

-- GLOB: Unix shell-style, case-SENSITIVE
--   wildcards * and ?, plus [abc] character classes
SELECT * FROM users WHERE username GLOB 'Al*';
SELECT * FROM users WHERE username GLOB '[A-D]*';

-- REGEXP (requires extension or custom function)
SELECT * FROM users WHERE username REGEXP '^a.*e$';

-- substring match
SELECT * FROM users WHERE INSTR(username, 'lic') > 0;

CASE и условная логика

CASE — это if/else в SQL, работает в SELECT, WHERE, ORDER BY, GROUP BY и HAVING. «Простая» форма сравнивает значение; «поисковая» форма вычисляет булевы условия по порядку. SUM(CASE WHEN ... THEN 1 ELSE 0 END) — классическая идиома для сводных таблиц и условных подсчётов. CASE обрывается на первом совпавшем WHEN, поэтому порядок важен. Если ELSE опущен, по умолчанию NULL.

sqlite
-- simple CASE (value match)
SELECT name,
  CASE role
    WHEN 'admin' THEN 'Administrator'
    WHEN 'editor' THEN 'Editor'
    ELSE 'Regular User'
  END AS role_label
FROM users;

-- searched CASE (conditions)
SELECT name,
  CASE
    WHEN age < 18 THEN 'minor'
    WHEN age < 65 THEN 'adult'
    ELSE 'senior'
  END AS age_group
FROM users;

-- CASE in aggregate (conditional count)
SELECT
  COUNT(*) AS total,
  SUM(CASE WHEN active = 1 THEN 1 ELSE 0 END) AS active_count,
  SUM(CASE WHEN active = 0 THEN 1 ELSE 0 END) AS inactive_count
FROM users;

-- CASE in ORDER BY (custom sort order)
SELECT * FROM products
ORDER BY CASE category
  WHEN 'featured' THEN 0
  WHEN 'new' THEN 1
  ELSE 2
END;

DISTINCT и операции над множествами

UNION ALL быстрее UNION, потому что UNION выполняет сортировку для дедупликации — предпочитайте UNION ALL, когда дубликаты невозможны или допустимы. Все операции над множествами требуют совместимого числа и типов столбцов. SQLite применяет INTERSECT/EXCEPT с более высоким приоритетом, чем UNION/UNION ALL; используйте скобки для порядка. ORDER BY в составном запросе применяется ко всему результату, а не к последнему SELECT.

sqlite
-- distinct rows
SELECT DISTINCT category FROM products;
SELECT DISTINCT city, country FROM users;

-- UNION (dedup) and UNION ALL (faster, keeps dups)
SELECT 'user' AS type, id FROM users WHERE active = 1
UNION ALL
SELECT 'admin', id FROM admins;

-- INTERSECT: rows in both
SELECT id FROM users
INTERSECT
SELECT user_id FROM orders;

-- EXCEPT: rows in first but not second
SELECT id FROM users
EXCEPT
SELECT user_id FROM orders;

-- combine with ORDER BY (applies to whole result)
SELECT name FROM users WHERE active = 1
UNION
SELECT name FROM archived_users
ORDER BY name;
05

JOIN

INNER JOIN

INNER JOIN возвращает только строки с совпадениями в обеих таблицах — несовпавшие строки с любой стороны отбрасываются. USING (col) — сокращение, когда в обеих таблицах есть одноимённый столбец, и он даёт один объединённый столбец. NATURAL JOIN неявно использует все одноимённые столбцы — удобно, но хрупко (изменение схемы может молча изменить поведение join). Для ясности предпочитайте явный ON.

sqlite
-- only matching rows from both tables
SELECT u.username, o.id AS order_id, o.total
FROM users AS u
INNER JOIN orders AS o ON o.user_id = u.id;

-- join with additional filter
SELECT u.username, o.total
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.total > 100 AND u.active = 1;

-- USING shorthand (when join columns share a name)
SELECT u.username, o.total
FROM users u
JOIN orders o USING (user_id);

-- natural join (joins on all same-named columns — risky)
SELECT * FROM users NATURAL JOIN profiles;

LEFT JOIN

LEFT JOIN сохраняет каждую строку левой таблицы; отсутствующие столбцы правой становятся NULL. Паттерн 'WHERE right.id IS NULL' (анти-join) находит строки без совпадений — часто яснее и быстрее, чем NOT IN с подзапросами, особенно когда есть NULL. При агрегации через LEFT JOIN COUNT(right.id) (а не COUNT(*)) считает только совпавшие строки: пользователь без заказов даст 0, а не 1.

sqlite
-- all users, with their orders (NULLs if no orders)
SELECT u.username, o.id AS order_id, o.total
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id;

-- find users with NO orders (anti-join pattern)
SELECT u.username
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;

-- multiple left joins
SELECT u.username,
  COUNT(o.id) AS order_count,
  SUM(o.total) AS total_spent
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.username;

CROSS JOIN и RIGHT/FULL

CROSS JOIN даёт декартово произведение — полезно для генерации комбинаций (размер × цвет), но опасно на больших таблицах (n×m строк). SQLite поддерживает RIGHT JOIN (3.39+), но он редко нужен; для ясности перепишите как LEFT JOIN. Нативного FULL OUTER JOIN нет; эмулируйте через LEFT JOIN ... UNION ... RIGHT JOIN, где union убирает дубли совпавших строк.

sqlite
-- CROSS JOIN: cartesian product (every combination)
SELECT s.size, c.color
FROM sizes s CROSS JOIN colors c;

-- explicit CROSS JOIN (same as comma join)
SELECT a.name, b.name FROM teams a, teams b;

-- RIGHT JOIN (preserves right table) — supported but unusual
SELECT u.username, o.total
FROM orders o
RIGHT JOIN users u ON u.id = o.user_id;

-- SQLite has no FULL OUTER JOIN; emulate with LEFT + UNION:
SELECT u.username, o.id AS order_id
FROM users u LEFT JOIN orders o ON o.user_id = u.id
UNION
SELECT u.username, o.id
FROM users u RIGHT JOIN orders o ON o.user_id = u.id;

Self join

Self join запрашивает одну и ту же таблицу дважды через алиасы, давая каждой копии роль (employee vs manager, user_a vs user_b). Приём 'a.id < b.id' не даёт строке совпасть с самой собой и породить зеркальные дубли. Для глубоких иерархий (произвольная глубина) правильный инструмент — рекурсивный CTE (см. раздел CTE): self join обрабатывает только фиксированные глубины.

sqlite
-- employees and their managers (same table)
CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT,
  manager_id INTEGER REFERENCES employees(id)
);

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- find pairs of users in the same city
SELECT a.username AS user_a, b.username AS user_b, a.city
FROM users a
JOIN users b ON a.city = b.city AND a.id < b.id;

-- recursive hierarchy (tree traversal) — see CTE section
-- multi-level category paths
SELECT c.name, p.name AS parent
FROM categories c
LEFT JOIN categories p ON c.parent_id = p.id;

Многотабличные join

Joins выстраиваются слева направо; каждый JOIN видит накопленный результат. При смешивании LEFT и INNER: INNER JOIN после LEFT JOIN может снова отфильтровать NULL и свести на нет смысл LEFT — следите за порядком. COUNT(DISTINCT col) избегает двойного подсчёта при связях один-ко-многим. Помните о кратности: соединение двух таблиц один-ко-многим может дать взрыв строк (m × n строк на родителя).

sqlite
-- chain joins across 3+ tables
SELECT u.username, o.id AS order_id, i.name AS item, i.qty
FROM users u
JOIN orders o ON o.user_id = u.id
JOIN order_items i ON i.order_id = o.id
WHERE u.active = 1
ORDER BY o.id, i.name;

-- mixing JOIN types
SELECT u.username, o.id AS order_id, p.name AS product
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
LEFT JOIN order_items i ON i.order_id = o.id
LEFT JOIN products p ON p.id = i.product_id;

-- join with aggregation
SELECT u.username,
  COUNT(DISTINCT o.id) AS orders,
  SUM(i.qty * i.price) AS lifetime_value
FROM users u
JOIN orders o ON o.user_id = u.id
JOIN order_items i ON i.order_id = o.id
GROUP BY u.id, u.username;
06

Агрегации и GROUP BY

Основы GROUP BY

GROUP BY схлопывает строки с одинаковыми значениями группирующих столбцов в одну строку на группу. Каждый неагрегированный столбец в SELECT должен быть в GROUP BY (SQLite снисходителен и берёт произвольное значение, но не полагайтесь на это). Группировка по выражению (например, strftime по дате) отлично подходит для отчётов по временным интервалам. ORDER BY применяется после GROUP BY — используйте его для сортировки групп.

sqlite
-- count users per role
SELECT role, COUNT(*) AS user_count
FROM users
GROUP BY role;

-- multiple aggregates per group
SELECT role,
  COUNT(*)            AS user_count,
  AVG(age)            AS avg_age,
  MIN(age)            AS min_age,
  MAX(age)            AS max_age,
  SUM(age)            AS total_age
FROM users
GROUP BY role;

-- group by multiple columns
SELECT role, active, COUNT(*) AS cnt
FROM users
GROUP BY role, active
ORDER BY role, active;

-- group by expression
SELECT strftime('%Y-%m', created) AS month,
  COUNT(*) AS signups
FROM users
GROUP BY month
ORDER BY month;

Агрегатные функции

COUNT(*) считает строки; COUNT(col) считает не-NULL значения; COUNT(DISTINCT col) считает уникальные не-NULL. AVG/SUM игнорируют NULL. GROUP_CONCAT соединяет значения в строку (разделитель по умолчанию — запятая). TOTAL() всегда возвращает REAL (0.0 для пустого входа), в отличие от SUM(), который для пустого входа даёт NULL. Клауза FILTER (3.30+) чище CASE для условной агрегации и может быть быстрее.

sqlite
SELECT
  COUNT(*)             AS row_count,
  COUNT(email)         AS emails_non_null,
  COUNT(DISTINCT role) AS distinct_roles,
  AVG(age)             AS avg_age,
  SUM(total)           AS revenue,
  MIN(created)         AS first_signup,
  MAX(created)         AS last_signup,
  GROUP_CONCAT(name)   AS all_names,
  GROUP_CONCAT(name, ' | ') AS names_piped,
  TOTAL(price)         AS total_real  -- always returns REAL, 0.0 if empty
FROM orders;

-- aggregate with FILTER (SQLite 3.30+)
SELECT
  COUNT(*)                              AS all_orders,
  COUNT(*) FILTER (WHERE status='paid') AS paid,
  SUM(total) FILTER (WHERE status='paid') AS paid_revenue
FROM orders;

HAVING (фильтрация групп)

WHERE фильтрует входные строки до группировки; HAVING фильтрует группы после агрегации. Порядок важен: WHERE по агрегату недопустим. SQLite разрешает ссылки на алиасы в HAVING (и GROUP BY) ради читаемости, но это нестандартно. Паттерн 'group by X having count(*) > 1' — классический запрос поиска дубликатов.

sqlite
-- HAVING filters groups (after GROUP BY);
-- WHERE filters rows (before GROUP BY)
SELECT user_id, COUNT(*) AS order_count, SUM(total) AS spent
FROM orders
WHERE status = 'paid'         -- filter rows first
GROUP BY user_id
HAVING COUNT(*) >= 3 AND SUM(total) > 100   -- filter groups
ORDER BY spent DESC;

-- HAVING with aliases (SQLite allows this)
SELECT role, COUNT(*) AS n
FROM users
GROUP BY role
HAVING n > 1;

-- find duplicate usernames
SELECT username, COUNT(*) AS dupes
FROM users
GROUP BY username
HAVING COUNT(*) > 1;

GROUP_CONCAT

GROUP_CONCAT — функция строковой агрегации в SQLite (в других СУБД STRING_AGG или LISTAGG). Разделитель по умолчанию — запятая; свой задаётся вторым аргументом. ORDER BY внутри агрегата (3.44+) управляет порядком конкатенации. DISTINCT убирает дубликаты перед соединением. Результат ограничен 1 ГБ; для огромных групп лучше получить строки и агрегировать в приложении.

sqlite
-- concatenate values within a group
SELECT role, GROUP_CONCAT(username) AS members
FROM users
GROUP BY role;
-- members: 'alice,bob,carol'

-- custom separator
SELECT role, GROUP_CONCAT(username, ' | ') AS members
FROM users
GROUP BY role;

-- ordered concatenation (SQLite 3.44+)
SELECT role,
  GROUP_CONCAT(username ORDER BY username) AS sorted_members
FROM users
GROUP BY role;

-- distinct values only
SELECT role,
  GROUP_CONCAT(DISTINCT city) AS cities
FROM users
GROUP BY role;

Сводные таблицы (pivot)

В SQLite нет нативного PIVOT; эмулируйте через SUM(CASE WHEN ...) или более чистую клаузу FILTER. Каждый сводный столбец проверяет одно значение группирующего признака. Этот паттерн превращает длинные (нормализованные) данные в широкие (кросс-табличные) отчёты. Для полностью динамического pivot (неизвестные категории) SQL-строку нужно строить в коде приложения — SQL не создаёт столбцы в рантайме.

sqlite
-- manual pivot with SUM(CASE ...)
SELECT user_id,
  SUM(CASE WHEN strftime('%w', created) = '0' THEN 1 ELSE 0 END) AS sun,
  SUM(CASE WHEN strftime('%w', created) = '1' THEN 1 ELSE 0 END) AS mon,
  SUM(CASE WHEN strftime('%w', created) = '2' THEN 1 ELSE 0 END) AS tue,
  SUM(CASE WHEN strftime('%w', created) = '3' THEN 1 ELSE 0 END) AS wed,
  SUM(CASE WHEN strftime('%w', created) = '4' THEN 1 ELSE 0 END) AS thu,
  SUM(CASE WHEN strftime('%w', created) = '5' THEN 1 ELSE 0 END) AS fri,
  SUM(CASE WHEN strftime('%w', created) = '6' THEN 1 ELSE 0 END) AS sat
FROM orders
GROUP BY user_id;

-- pivot with FILTER (cleaner)
SELECT user_id,
  COUNT(*) FILTER (WHERE status='paid')    AS paid,
  COUNT(*) FILTER (WHERE status='pending') AS pending,
  COUNT(*) FILTER (WHERE status='refunded') AS refunded
FROM orders
GROUP BY user_id;
07

Подзапросы

Скалярные подзапросы

Скалярный подзапрос возвращает ровно одну строку и один столбец; он может стоять везде, где допустимо выражение значения. Коррелированные подзапросы ссылаются на внешний запрос и перевыполняются на каждую строку — мощно, но потенциально медленно на больших таблицах (рассмотрите JOIN или оконную функцию). SQLite хорошо оптимизирует простые коррелированные подзапросы в joins, но на горячих путях всегда проверяйте EXPLAIN QUERY PLAN.

sqlite
-- a subquery returning a single value
SELECT username, age
FROM users
WHERE age > (SELECT AVG(age) FROM users);

-- in SELECT list
SELECT username,
  age,
  age - (SELECT AVG(age) FROM users) AS age_diff
FROM users;

-- in HAVING
SELECT role, AVG(age) AS avg_age
FROM users
GROUP BY role
HAVING AVG(age) > (SELECT AVG(age) FROM users);

-- correlated scalar subquery (re-evaluated per row)
SELECT username,
  (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS order_count
FROM users u;

Подзапросы IN и NOT IN

Подзапросы IN интуитивны, но осторожно с NULL: если результат содержит NULL, NOT IN вычисляется в NULL (трактуется как false) для каждой строки, возвращая пустой результат. Для анти-join с NULL-столбцами предпочитайте NOT EXISTS — он NULL-безопасен и часто быстрее. Многоколоночный IN краток для составных ключей.

sqlite
-- users who have placed an order
SELECT username FROM users
WHERE id IN (SELECT user_id FROM orders);

-- users who have NOT placed an order
SELECT username FROM users
WHERE id NOT IN (SELECT user_id FROM orders);

-- DANGER: NOT IN with NULLs returns no rows!
-- if the subquery returns any NULL, NOT IN matches nothing.
-- Safe version using NOT EXISTS:
SELECT username FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id
);

-- multi-column IN
SELECT * FROM products
WHERE (category, price) IN (
  SELECT category, MIN(price) FROM products GROUP BY category
);

EXISTS и NOT EXISTS

EXISTS проверяет существование строк, не выбирая данные — он останавливается на первом совпадении, поэтому эффективен. NOT EXISTS — NULL-безопасный способ найти несовпавшие строки (в отличие от NOT IN). Для лучшей производительности убедитесь, что есть индекс по столбцу, по которому соединяется коррелированный подзапрос (orders.user_id в примере). SELECT 1 — соглашение; список столбцов для EXISTS не важен.

sqlite
-- EXISTS: true if subquery returns any row
SELECT username FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.user_id = u.id AND o.total > 100
);

-- NOT EXISTS: classic anti-join (NULL-safe)
SELECT username FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id
);

-- correlated EXISTS — usually efficient with an index on
-- the subquery's join column (orders.user_id here)
SELECT p.name FROM products p
WHERE EXISTS (
  SELECT 1 FROM order_items i
  WHERE i.product_id = p.id AND i.qty > 10
);

Производные таблицы (подзапрос в FROM)

Подзапрос в FROM (производная таблица) — мощный способ подготовить промежуточные результаты, предварительно агрегировать или отфильтровать перед join. Производные таблицы должны иметь алиас. SQLite в большинстве случаев материализует подзапрос (пишет во временную таблицу) — при повторном использовании CTE (клауза WITH) часто яснее и может упоминаться несколько раз. SQLite не разрешает коррелированные ссылки в подзапрос FROM.

sqlite
-- treat a query result as a table
SELECT t.role, t.cnt
FROM (
  SELECT role, COUNT(*) AS cnt
  FROM users
  GROUP BY role
) t
WHERE t.cnt > 1;

-- join a derived table
SELECT u.username, t.total_spent
FROM users u
JOIN (
  SELECT user_id, SUM(total) AS total_spent
  FROM orders
  GROUP BY user_id
) t ON t.user_id = u.id;

-- derived tables must be aliased
SELECT * FROM (SELECT 1 AS x) AS sub;

Коррелированные и некоррелированные

Некоррелированные подзапросы выполняются один раз и могут кэшироваться; коррелированные перевыполняются на каждую внешнюю строку (используйте индексы, чтобы они были быстрыми). В SQLite нет LATERAL/APPLY; чтобы «соединить» агрегат по строке, предварительно агрегируйте в производной таблице и сделайте LEFT JOIN. Когда коррелированный подзапрос работает плохо, обычное решение — переписать его как JOIN с GROUP BY или оконной функцией.

sqlite
-- UNCORRELATED: runs once, result cached
SELECT username FROM users
WHERE age > (SELECT AVG(age) FROM users);

-- CORRELATED: references outer row, re-runs per outer row
SELECT u.username,
  (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS orders
FROM users u;

-- lateral-style correlation in a JOIN subquery is NOT
-- supported directly; use a scalar subquery in SELECT or
-- rewrite as a join with GROUP BY:
SELECT u.username, COALESCE(t.n, 0) AS orders
FROM users u
LEFT JOIN (
  SELECT user_id, COUNT(*) AS n FROM orders GROUP BY user_id
) t ON t.user_id = u.id;
08

Индексы

Создание индексов

Индексы ускоряют чтение, но замедляют запись (каждый INSERT/UPDATE/DELETE обновляет все индексы). Порядок столбцов в индексе важен: составной индекс по (a, b) помогает WHERE a=? и WHERE a=? AND b=?, но НЕ WHERE b=? отдельно (правило левого префикса). Уникальные индексы обеспечивают ограничения. SQLite хранит индексы как B-деревья. Удаляйте неиспользуемые индексы — они стоят места и производительности записи.

sqlite
-- single-column index
CREATE INDEX idx_users_email ON users(email);

-- unique index (also enforces uniqueness)
CREATE UNIQUE INDEX idx_users_username ON users(username);

-- composite index (column order matters!)
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- IF NOT EXISTS for idempotent scripts
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);

-- drop an index
DROP INDEX IF EXISTS idx_users_email;

-- list indexes
.indices users
-- or
SELECT name, sql FROM sqlite_master
WHERE type = 'index' AND tbl_name = 'users';

Частичные индексы

Частичные индексы индексируют только подходящие строки, поэтому они меньше и быстрее в обслуживании — идеальны, когда запросы всегда фильтруют по стабильному предикату ('active = 1', 'deleted_at IS NULL'). WHERE запроса должен совпадать с WHERE индекса (или быть строже), чтобы оптимизатор его использовал. Уникальные частичные индексы обеспечивают уникальность только на подмножестве — идеально для ограничения «одна активная строка на родителя».

sqlite
-- index only active users (smaller, faster)
CREATE INDEX idx_active_users ON users(username)
WHERE active = 1;

-- index only unpaid orders
CREATE INDEX idx_unpaid_orders ON orders(user_id)
WHERE status = 'unpaid';

-- unique partial index: one active session per user
CREATE UNIQUE INDEX idx_one_active_session
ON sessions(user_id) WHERE active = 1;

-- query must match the WHERE for the index to be used
SELECT * FROM users WHERE active = 1 AND username = 'alice';

Индексы по выражениям

Индексы по выражениям индексируют результат функции или выражения, позволяя быстрый поиск по преобразованным значениям, — но запрос должен использовать точно то же выражение. Частые применения: поиск без учёта регистра (LOWER), группировка по дате (strftime), извлечение поля JSON (json_extract). Только детерминированные функции (без RANDOM() или NOW()). Индексы по выражениям мощны, но усложняют изменение схемы.

sqlite
-- index a function result (must match the query expression exactly)
CREATE INDEX idx_users_lower_email ON users(LOWER(email));

-- query that can use it (must use the same expression)
SELECT * FROM users WHERE LOWER(email) = '[email protected]';

-- index a date extraction for fast monthly queries
CREATE INDEX idx_orders_month ON orders(strftime('%Y-%m', created));
SELECT * FROM orders WHERE strftime('%Y-%m', created) = '2024-06';

-- index a JSON path
CREATE INDEX idx_events_type ON events(json_extract(data, '$.type'));
SELECT * FROM events WHERE json_extract(data, '$.type') = 'login';

-- indexed computed value (collation)
CREATE INDEX idx_users_name_ci ON users(username COLLATE NOCASE);

Покрывающие индексы и WITHOUT ROWID

Покрывающий индекс включает все столбцы, которые читает запрос, поэтому SQLite удовлетворяет запрос только из индекса (без обращения к таблице). Таблицы WITHOUT ROWID делают PRIMARY KEY кластерным индексом — строки хранятся отсортированными по ключу, идеально для нагрузок ключ-значение и диапазонных сканов по PK. Компромиссы: нет AUTOINCREMENT, нет алиаса rowid, чуть сложнее обновления. Замеряйте перед массовым внедрением.

sqlite
-- covering index: includes all columns the query needs,
-- so SQLite never reads the table
CREATE INDEX idx_orders_cover ON orders(user_id, status, total);
SELECT user_id, status, total FROM orders WHERE user_id = 1;
-- ^ fully served by the index

-- WITHOUT ROWID tables: store rows IN the index (clustered)
CREATE TABLE kv (
  key   TEXT PRIMARY KEY,
  value TEXT
) WITHOUT ROWID;

-- the PK becomes the table itself; lookups by key are fast
SELECT value FROM kv WHERE key = 'config:theme';

-- good for key/value lookups and read-heavy tables
-- trade-off: no rowid, some features unsupported

Инспекция индексов

EXPLAIN QUERY PLAN — важнейший инструмент: 'USING INDEX' значит индекс используется; 'SCAN' — полное сканирование таблицы (часто плохо). sqlite_stat1 (заполняется ANALYZE) хранит статистику кардинальности, по которой планировщик выбирает индексы. REINDEX пересоздаёт раздутый или повреждённый индекс без пересборки таблицы. Запускайте ANALYZE после больших загрузок, чтобы у планировщика были свежие статистики для хороших планов.

sqlite
-- see if an index is used
EXPLAIN QUERY PLAN
SELECT * FROM users WHERE email = '[email protected]';
-- look for 'SEARCH users USING INDEX idx_users_email'

-- list all indexes on a table with their definitions
SELECT name, sql FROM sqlite_master
WHERE type = 'index' AND tbl_name = 'users';

-- index stats (how often each index is used)
SELECT name, idx_scan, idx_read
FROM sqlite_stat1, sqlite_master
WHERE sqlite_stat1.tbl = 'users';

-- rebuild an index (after corruption or bloat)
REINDEX idx_users_email;
REINDEX;  -- rebuild all indexes

-- analyze tables to update query planner stats
ANALYZE;
09

Представления

Создание представлений

Представления — виртуальные таблицы, определённые сохранённым запросом: они не хранят данные (кроме временных), а перевыполняют нижележащий SELECT каждый раз. Используйте их, чтобы упростить сложные запросы, закрепить единые паттерны доступа или абстрагировать изменения схемы от приложений. В SQLite представления по умолчанию только для чтения (с ограничениями на обновляемые). DROP VIEW никогда не затрагивает нижележащие таблицы.

sqlite
-- a view is a saved SELECT, queried like a table
CREATE VIEW active_users AS
SELECT id, username, email
FROM users
WHERE active = 1;

SELECT * FROM active_users WHERE age > 18;

-- view with calculated columns
CREATE VIEW user_summary AS
SELECT u.id, u.username,
  COUNT(o.id) AS order_count,
  COALESCE(SUM(o.total), 0) AS total_spent
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.username;

-- IF NOT EXISTS for idempotent scripts
CREATE VIEW IF NOT EXISTS active_users AS
SELECT id, username FROM users WHERE active = 1;

-- drop a view
DROP VIEW IF EXISTS active_users;

Обновляемые представления

SQLite разрешает INSERT/UPDATE/DELETE для простых представлений (одна базовая таблица, без JOIN, агрегатов, DISTINCT, GROUP BY). Операция перенаправляется в нижележащую таблицу. SQLite НЕ поддерживает WITH CHECK OPTION, поэтому строка, вставленная через представление, может не соответствовать его WHERE и потом быть невидимой через него. Для сложных представлений, которым нужна запись, используйте триггеры INSTEAD OF.

sqlite
-- simple views (one base table, no aggregates/distinct/etc.)
-- are auto-updatable in SQLite
CREATE VIEW active_users AS
SELECT id, username, email, age FROM users WHERE active = 1;

-- INSERT through the view (active defaults/applies)
INSERT INTO active_users (id, username, email) VALUES (1, 'x', '[email protected]');

-- UPDATE through the view
UPDATE active_users SET age = 30 WHERE id = 1;

-- DELETE through the view
DELETE FROM active_users WHERE id = 1;

-- the WITH CHECK OPTION is NOT supported in SQLite, so
-- inserts/updates that don't match the view's WHERE still
-- land in the base table but vanish from the view.

Триггеры INSTEAD OF на представлениях

Триггеры INSTEAD OF перехватывают записи в представление и выполняют вашу логику, делая обновляемым любое представление. Это стандартный способ сделать записываемыми сложные (многотабличные, агрегирующие) представления. NEW и OLD ссылаются на входящую и существующую строки. Триггеры INSTEAD OF срабатывают только на представлениях, не на базовых таблицах. Используйте их для инкапсулированной бизнес-логики или чтобы скрыть нормализованную схему за денормализованным представлением.

sqlite
-- a view joining multiple tables is not auto-updatable,
-- but INSTEAD OF triggers let you define the write behavior
CREATE VIEW user_orders AS
SELECT u.id AS user_id, u.username, o.id AS order_id, o.total
FROM users u LEFT JOIN orders o ON o.user_id = u.id;

-- route INSERTs on the view to the orders table
CREATE TRIGGER trg_user_orders_insert
INSTEAD OF INSERT ON user_orders
FOR EACH ROW
BEGIN
  INSERT INTO orders (id, user_id, total)
  VALUES (NEW.order_id, NEW.user_id, NEW.total);
END;

-- now this works:
INSERT INTO user_orders (user_id, order_id, total)
VALUES (1, 100, 49.99);

Временные представления

Временные представления живут только в текущем соединении и автоматически удаляются при отключении — идеально для подготовки сложных ad-hoc запросов без засорения общей схемы. Они хранятся в отдельной схеме temp, поэтому другие соединения (даже к тому же файлу) их не видят. Временные представления могут ссылаться на временные таблицы, что полезно для ETL-пайплайнов в рамках сессии.

sqlite
-- TEMP view exists only for the current connection
CREATE TEMP VIEW recent_orders AS
SELECT * FROM orders WHERE created > datetime('now', '-7 days');

-- the view vanishes when the connection closes
-- (no other connection can see it)

-- also valid: CREATE TEMPORARY VIEW
CREATE TEMPORARY VIEW my_scratch AS
SELECT id, username FROM users WHERE role = 'admin';

-- use case: ad-hoc analysis without polluting the schema
SELECT * FROM recent_orders WHERE total > 100;

-- list views
SELECT name, sql FROM sqlite_master
WHERE type = 'view';
SELECT name, sql FROM temp.sqlite_master
WHERE type = 'view';  -- temp views

Материализованные представления (эмуляция)

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

sqlite
-- SQLite has no native materialized views; emulate with a
-- real table that you refresh periodically

-- 1) create the 'materialized' table
CREATE TABLE mv_user_summary AS
SELECT u.id, u.username,
  COUNT(o.id) AS order_count,
  COALESCE(SUM(o.total), 0) AS total_spent
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.username;

-- 2) add an index for fast lookups
CREATE INDEX idx_mv_user_summary_id ON mv_user_summary(id);

-- 3) refresh (full rebuild)
DELETE FROM mv_user_summary;
INSERT INTO mv_user_summary
SELECT u.id, u.username, COUNT(o.id), COALESCE(SUM(o.total), 0)
FROM users u LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.username;

-- 4) refresh atomically with a name swap
BEGIN;
CREATE TABLE mv_user_summary_new AS SELECT ...;
DROP TABLE mv_user_summary;
ALTER TABLE mv_user_summary_new RENAME TO mv_user_summary;
COMMIT;
10

Триггеры

Триггеры BEFORE / AFTER

Триггеры BEFORE выполняются до записи строки — используйте их для валидации (RAISE ABORT) или преобразования NEW-значений. Триггеры AFTER выполняются после фиксации строки — для аудита, каскадных эффектов или денормализации. FOR EACH ROW — единственный вариант (триггеров уровня оператора нет). NEW хранит входящую строку (INSERT/UPDATE); OLD — предыдущую (UPDATE/DELETE). Клауза WHEN условно запускает триггер.

sqlite
-- AFTER INSERT: audit log
CREATE TRIGGER trg_users_audit
AFTER INSERT ON users
FOR EACH ROW
BEGIN
  INSERT INTO users_audit (user_id, action, at)
  VALUES (NEW.id, 'insert', datetime('now'));
END;

-- BEFORE INSERT: validate / normalize data
CREATE TRIGGER trg_users_lower_email
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
  SELECT RAISE(ABORT, 'email cannot be empty')
  WHERE NEW.email = '';
END;

-- AFTER UPDATE: log changes
CREATE TRIGGER trg_users_update_log
AFTER UPDATE ON users
FOR EACH ROW
WHEN OLD.email IS NOT NEW.email
BEGIN
  INSERT INTO email_changes (user_id, old_email, new_email, at)
  VALUES (NEW.id, OLD.email, NEW.email, datetime('now'));
END;

Триггеры для каскадных эффектов

Триггеры могут автоматически поддерживать денормализованные счётчики, сводные таблицы и метки updated_at. Осторожно: возможны рекурсивные цепочки триггеров (триггер обновляет свою же таблицу), поэтому управляйте этим через PRAGMA recursive_triggers (по умолчанию OFF ради обратной совместимости). Порядок нескольких триггеров на одно событие — по порядку создания; приоритета BEFORE/AFTER между триггерами одного тайминга нет.

sqlite
-- maintain a denormalized counter
CREATE TRIGGER trg_orders_count_inc
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
  UPDATE users
  SET order_count = order_count + 1
  WHERE id = NEW.user_id;
END;

CREATE TRIGGER trg_orders_count_dec
AFTER DELETE ON orders
FOR EACH ROW
BEGIN
  UPDATE users
  SET order_count = order_count - 1
  WHERE id = OLD.user_id;
END;

-- keep an 'updated_at' column fresh
CREATE TRIGGER trg_users_touch
AFTER UPDATE ON users
FOR EACH ROW
WHEN NEW.updated_at IS OLD.updated_at
BEGIN
  UPDATE users SET updated_at = datetime('now') WHERE id = NEW.id;
END;

Триггеры INSTEAD OF (представления)

Триггеры INSTEAD OF выполняются вместо исходного INSERT/UPDATE/DELETE по представлению, позволяя направлять записи в одну или несколько нижележащих таблиц. Это канонический способ сделать сложные представления записываемыми. Они срабатывают на каждую строку представления. На представлениях можно определять только триггеры INSTEAD OF; BEFORE/AFTER требуют базовых таблиц. Полезно для API-подобных слоёв абстракции над нормализованной схемой.

sqlite
-- make a multi-table view writable
CREATE VIEW order_detail AS
SELECT o.id AS order_id, o.total, u.username, i.product_name
FROM orders o
JOIN users u ON u.id = o.user_id
JOIN order_items i ON i.order_id = o.id;

CREATE TRIGGER trg_order_detail_update
INSTEAD OF UPDATE ON order_detail
FOR EACH ROW
BEGIN
  UPDATE orders SET total = NEW.total WHERE id = NEW.order_id;
  UPDATE order_items SET product_name = NEW.product_name
  WHERE order_id = NEW.order_id;
END;

-- now UPDATE on the view routes to both tables
UPDATE order_detail SET total = 99.99, product_name = 'Widget'
WHERE order_id = 5;

RAISE и обработка ошибок

RAISE управляет тем, как распространяется сбой триггера: ABORT (самый частый) отменяет оператор и откатывает его изменения, ROLLBACK откатывает всю транзакцию, FAIL отменяет оператор, но сохраняет предыдущие успешные операторы транзакции, IGNORE пропускает только проблемную строку. RAISE(IGNORE) — аккуратный способ молча отбросить невалидные строки при массовой загрузке.

sqlite
-- RAISE aborts the statement with a custom error
CREATE TRIGGER trg_check_age
BEFORE INSERT ON users
FOR EACH ROW
WHEN NEW.age < 0
BEGIN
  SELECT RAISE(ABORT, 'age must be non-negative');
END;

-- ROLLBACK: undo the whole transaction, statement fails
CREATE TRIGGER trg_rollback_example
BEFORE UPDATE ON accounts
FOR EACH ROW
WHEN NEW.balance < 0
BEGIN
  SELECT RAISE(ROLLBACK, 'balance cannot go negative');
END;

-- FAIL: abort the current statement only (like ABORT)
-- IGNORE: skip this row, continue with the rest
CREATE TRIGGER trg_skip_invalid
BEFORE INSERT ON logs
FOR EACH ROW
WHEN NEW.level NOT IN ('info','warn','error')
BEGIN
  SELECT RAISE(IGNORE);
END;

Управление триггерами

Триггеры хранятся в sqlite_master (или temp.sqlite_master для временных). DROP TRIGGER удаляет их; ALTER TRIGGER нет — удаляйте и создавайте заново. Временные триггеры локальны для соединения и исчезают при отключении, полезны для отладки. Встроенного способа временно отключить триггер нет, кроме удаления; для условного выполнения используйте клаузу WHEN. Несколько триггеров на одно событие срабатывают в порядке создания в рамках своего тайминга.

sqlite
-- list all triggers
SELECT name, tbl_name, sql FROM sqlite_master
WHERE type = 'trigger';

-- list triggers on a specific table
SELECT name, sql FROM sqlite_master
WHERE type = 'trigger' AND tbl_name = 'users';

-- drop a trigger
DROP TRIGGER IF EXISTS trg_users_audit;

-- triggers in a TEMP schema (connection-local)
CREATE TEMP TRIGGER trg_temp_log
AFTER INSERT ON users
FOR EACH ROW
BEGIN
  SELECT 'inserted ' || NEW.id;
END;

-- trigger timing order: BEFORE triggers fire in
-- creation order, then AFTER triggers in creation order.
11

Настройки PRAGMA

Основы PRAGMA

PRAGMA — фирменный интерфейс конфигурации SQLite: настройки управляют журналированием, кэшем, внешними ключами, целостностью и планировщиком запросов. Настройки действуют в рамках сессии (на соединение), если в документации не сказано иначе; установка внутри транзакции может быть проигнорирована. Некоторые PRAGMA (journal_mode, page_size) работают только вне транзакций или до создания таблицы. PRAGMA compile_options показывает, что поддерживает ваша сборка.

sqlite
-- PRAGMA is SQLite's configuration knob (CLI and APIs)
-- read a setting
PRAGMA journal_mode;
PRAGMA cache_size;
PRAGMA foreign_keys;

-- set a setting (session scope unless noted)
PRAGMA foreign_keys = ON;
PRAGMA cache_size = -20000;   -- negative = KB, positive = pages
PRAGMA temp_store = MEMORY;

-- many PRAGMAs are no-ops below a certain SQLite version
-- check the compile-time options
PRAGMA compile_options;
-- e.g. ENABLE_FTS5, ENABLE_JSON1, MAX_ATTACHED, etc.

-- PRAGMAs are NOT part of SQL standard and are SQLite-only.

Внешние ключи

Проверка внешних ключей в SQLite по умолчанию ВЫКЛЮЧЕНА — известная ловушка для приходящих из других СУБД. Её нужно включать в каждом соединении через PRAGMA foreign_keys = ON; внутри транзакции это сделать нельзя. Настройка существует потому, что старые схемы (до поддержки FK) иначе ломались бы. Всегда включайте её в продакшене. defer_foreign_keys помогает при импорте данных в порядке, нарушающем зависимости.

sqlite
-- foreign keys are OFF by default in SQLite!
PRAGMA foreign_keys = ON;   -- enable per connection

-- check current state
PRAGMA foreign_keys;

-- inspect FK definitions on a table
PRAGMA foreign_key_list(orders);
-- returns: id, seq, table, from, to, on_update, on_delete, match

-- enable FKs in your app's connection setup, every time
-- (this is a frequent source of bugs when moving from MySQL/PG)

-- deferred FK checking (checked at COMMIT, not per statement)
PRAGMA defer_foreign_keys = ON;
-- or declare DEFERRABLE INITIALLY DEFERRED on the FK

Journal mode и WAL

WAL (Write-Ahead Logging) — рекомендуемый режим журнала для большинства приложений: читатели и писатели не блокируют друг друга, надёжность хорошая. Он создаёт рядом с основной БД файлы -wal и -shm. WAL сохраняется для файла базы (задаётся один раз). Используйте wal_checkpoint(TRUNCATE), чтобы влить содержимое WAL обратно в основной файл и вернуть место. Избегайте WAL на сетевых файловых системах — ему нужна разделяемая память.

sqlite
-- WAL: write-ahead logging (best for most apps)
PRAGMA journal_mode = WAL;
-- benefits: readers never block writers, writers never block
--           readers, crash recovery is fast
-- trade-off: creates -wal and -shm sidecar files

-- other modes: DELETE (default), TRUNCATE, PERSIST, MEMORY, OFF
PRAGMA journal_mode = DELETE;

-- WAL checkpoint: merge -wal back into the main file
PRAGMA wal_checkpoint;
PRAGMA wal_checkpoint(TRUNCATE);  -- also shrinks the -wal file

-- auto-checkpoint threshold (pages of WAL before auto-checkpoint)
PRAGMA wal_autocheckpoint = 1000;

-- WAL is persistent: setting it once keeps it for that DB file.

Synchronous и надёжность

synchronous управляет тем, как агрессивно SQLite вызывает fsync. FULL самый безопасный (fsync на каждый коммит); NORMAL — рекомендуемое значение при WAL (fsync на чекпоинте, а не на коммите, с пренебрежимо малым риском повреждения на практике); OFF рискует повреждением БД при потере питания — только для действительно одноразовых данных или пересоздаваемых кэшей. page_size нужно задавать до создания схемы; позже изменение требует VACUUM.

sqlite
-- how thoroughly SQLite flushes to disk on commit
PRAGMA synchronous;        -- read current value
PRAGMA synchronous = FULL;       -- safest, slowest (default for rollback journal)
PRAGMA synchronous = NORMAL;     -- safe with WAL, fast (recommended for WAL)
PRAGMA synchronous = OFF;        -- fastest; corruption risk on power loss
PRAGMA synchronous = EXTRA;      -- even more careful than FULL

-- trade-off: durability vs speed
-- FULL: fsync at every commit (survives OS crash)
-- NORMAL (WAL): fsync at checkpoint, not every commit
-- OFF: no fsync — fast but may corrupt on power loss

-- page size (set BEFORE creating tables, ideally)
PRAGMA page_size = 4096;
PRAGMA page_size;

Кэш, память и temp store

cache_size — кэш страниц в памяти: больше обычно лучше для read-heavy нагрузок; отрицательные значения в КБ, положительные в страницах. temp_store = MEMORY переносит промежуточные результаты (сортировки, временные таблицы) в RAM, ускоряя большие агрегации. mmap_size включает memory-mapped I/O для чтения, что может ускорить большие read-only базы. Настраивайте под бюджет памяти и паттерн доступа приложения.

sqlite
-- page cache (negative = KB, positive = pages)
PRAGMA cache_size = -65536;   -- 64 MB cache
PRAGMA cache_size = 20000;    -- 20000 pages

-- where temp tables and intermediate results live
PRAGMA temp_store = DEFAULT;  -- compile-time default
PRAGMA temp_store = FILE;     -- temp files
PRAGMA temp_store = MEMORY;   -- all temp data in RAM

-- mmap memory-mapped I/O for reads
PRAGMA mmap_size = 268435456;  -- 256 MB

-- soft heap limit for the SQLite library
PRAGMA soft_heap_limit = 100000000;  -- 100 MB

-- store prepared statements in memory between calls
PRAGMA cache_spill;

Целостность и информация о схеме

integrity_check проверяет всю структуру и содержимое базы (медленно, но тщательно); quick_check пропускает проверку содержимого и намного быстрее. Запускайте integrity_check при резервном копировании или после сбоев. table_info сообщает метаданные столбцов (table_xinfo — для скрытых столбцов вроде rowid FTS5). index_list и index_info показывают, какие индексы есть и по каким столбцам. database_list показывает подключённые базы.

sqlite
-- full integrity check (slow on large DBs)
PRAGMA integrity_check;
-- returns 'ok' or a list of problems

-- quick check (faster, less thorough)
PRAGMA quick_check;

-- list tables / schema
SELECT name, sql FROM sqlite_master WHERE type = 'table';

-- table info: columns, types, notnull, default, pk
PRAGMA table_info(users);
-- returns: cid, name, type, notnull, dflt_value, pk

-- extended table info (includes hidden columns from FTS/etc.)
PRAGMA table_xinfo(users);

-- index list with origin and unique flag
PRAGMA index_list(users);
PRAGMA index_info(idx_users_email);

-- database file header info
PRAGMA database_list;
12

Функции даты и времени

date / time / datetime

В SQLite нет нативного типа DATE; даты — это TEXT в ISO-8601 ('YYYY-MM-DD HH:MM:SS'). Функции date/time принимают много форматов ввода (ISO, слэши, названия месяцев, Unix-время через модификатор 'unixepoch'). 'now' вычисляется один раз на оператор, а не на строку — каждая строка в многострочном UPDATE получает одинаковую метку времени, что обычно и нужно. Храните в UTC, форматируйте для показа через localtime.

sqlite
-- current date/time (UTC by default)
SELECT date('now');              -- '2024-06-15'
SELECT time('now');              -- '14:30:00'
SELECT datetime('now');          -- '2024-06-15 14:30:00'
SELECT julianday('now');         -- 2460476.1042 (days since 4713 BC)

-- parse various formats
SELECT date('2024-06-15');
SELECT date('2024/06/15');
SELECT date('June 15, 2024');
SELECT datetime('2024-06-15 14:30:00.123');
SELECT date('2024-06-15','unixepoch');  -- from Unix timestamp (seconds)

-- Unix timestamp to datetime
SELECT datetime(1718450400, 'unixepoch');
SELECT datetime(1718450400, 'unixepoch', 'localtime');

Форматирование strftime

strftime — универсальный форматтер дат, коды формата повторяют strftime из C. Используйте его, чтобы извлечь части даты (год, месяц, день недели) для группировки или отрисовать свой формат. Для группировки по месяцу strftime('%Y-%m', created) даёт 'YYYY-MM', который естественно сортируется и группируется. В SQLite нет DAYNAME()/MONTHNAME(); комбинируйте strftime('%w', ...) с CASE, если нужны названия дней недели.

sqlite
-- strftime is the most flexible formatter
--   strftime(format, timestring, modifier, modifier, ...)
SELECT strftime('%Y-%m-%d', 'now');          -- '2024-06-15'
SELECT strftime('%Y-%m-%d %H:%M', 'now');    -- '2024-06-15 14:30'
SELECT strftime('%H:%M:%S', 'now');          -- '14:30:00'

-- common format codes
--   %Y year (4-digit)    %m month (01-12)   %d day (01-31)
--   %H hour (00-23)      %M minute (00-59)  %S second (00-59)
--   %j day of year       %w day of week (0=Sun)
--   %W week of year      %p AM/PM           %I hour (01-12)

-- get the day name
SELECT strftime('%w', '2024-06-15');   -- '6' (Saturday)
SELECT strftime('%Y-%m', 'now');       -- month bucket for grouping

Модификаторы (арифметика)

Модификаторы выстраиваются слева направо, сдвигая или выравнивая даты. Интервалы — '+N units' / '-N units' (единицы: days, hours, minutes, seconds, months, years). 'start of month/year/day' привязывает к границе периода — идеально для месячных отчётов. 'weekday N' возвращает дату следующего дня недели N (сегодня, если сегодня совпадает). Комбинация 'start of month', '+1 month', '-1 day' даёт последний день текущего месяца — классическая идиома.

sqlite
-- shift a date by an interval
SELECT date('now', '+1 day');           -- tomorrow
SELECT date('now', '-1 month');         -- one month ago
SELECT date('now', '+1 year', '+1 day');-- next year + 1 day
SELECT datetime('now', '+2 hours', '+30 minutes');

-- start of period
SELECT date('now', 'start of month');       -- first day of month
SELECT date('now', 'start of year');        -- first day of year
SELECT date('now', 'start of day');         -- midnight today

-- weekday: next occurrence of a given weekday (0=Sun, 1=Mon, ... 6=Sat)
SELECT date('now', 'weekday 1');            -- next Monday
SELECT date('now', 'weekday 0', '+7 days'); -- Sunday after this one

-- combine modifiers
SELECT date('now', 'start of month', '+1 month', '-1 day'); -- last day of month

Unix-время и юлианский день

Unix-метки — целые секунды с эпохи; unixepoch() (3.38+) сразу возвращает целое. Юлианский день — непрерывный дробный счёт дней: разность двух значений julianday даёт прошедшее время в днях (умножьте на 86400 для секунд). Для расчёта возраста деление на 365.25 приблизительно учитывает високосные годы; для юридической точности сравнивайте компоненты год/месяц/день явно.

sqlite
-- Unix timestamp (seconds since 1970-01-01 UTC)
SELECT strftime('%s', 'now');                  -- current Unix time (text)
SELECT unixepoch('now');                       -- integer Unix time (SQLite 3.38+)
SELECT datetime(1718450400, 'unixepoch');      -- back to datetime

-- Julian day (fractional days since 4713-01-01 BC)
SELECT julianday('now');                       -- 2460476.6042
SELECT julianday('2024-06-15') - julianday('2024-06-10');  -- 5.0 (days between)

-- compute elapsed time in seconds
SELECT (julianday('now') - julianday('2024-01-01')) * 86400.0 AS seconds_elapsed;

-- human-readable age from a birthdate
SELECT CAST((julianday('now') - julianday('1990-05-20')) / 365.25 AS INT) AS age;

Часовые пояса и localtime

В SQLite нет базы часовых поясов — 'now' это UTC, а модификаторы 'localtime'/'utc' полагаются на настройку часового пояса ОС. Рекомендуемый паттерн: всегда храните метки времени в UTC (datetime('now') даёт UTC) и переводите в местное время только при отображении модификатором 'localtime'. Это избегает неоднозначности между серверами в разных зонах и при переходах на летнее время. Никогда не храните местное время.

sqlite
-- 'now' is always UTC; convert to local time for display
SELECT datetime('now', 'localtime');          -- local wall-clock time
SELECT datetime('now');                       -- UTC

-- convert a stored UTC timestamp to local time
SELECT datetime(created, 'localtime') FROM users;

-- convert local time back to UTC for storage
SELECT datetime('2024-06-15 14:30:00', 'utc');

-- best practice: store UTC, format for display
CREATE TABLE events (ts TEXT DEFAULT (datetime('now')));  -- UTC
-- display:
SELECT datetime(ts, 'localtime') AS local_ts FROM events;

-- timezone is determined by the OS; SQLite has no TZ database.
13

Строковые функции

Длина и подстрока

length() считает символы для TEXT и байты для BLOB — для длины в байтах UTF-8 сначала приведите к BLOB. substr() индексируется с 1; отрицательное начало считается с конца. В SQLite нет нативных LPAD/RPAD — эмулируйте конкатенацией и substr. Следите за путаницей байтов и символов при обрезке многобайтового текста: substr работает с символами, поэтому безопасен, а побайтовая нарезка — нет.

sqlite
SELECT length('hello');              -- 5
SELECT length('日本語');             -- 3 (characters, not bytes)
SELECT length(x'00ff');             -- 2 (blob length in bytes)

SELECT substr('hello world', 7);    -- 'world' (1-indexed)
SELECT substr('hello world', 1, 5); -- 'hello' (length 5)
SELECT substr('hello', -3);         -- 'llo' (negative = from end)
SELECT substr('hello', -3, 2);      -- 'll'

-- byte length (vs character length)
SELECT length('日本語');            -- 3 (characters)
SELECT length(CAST('日本語' AS BLOB)); -- 9 (UTF-8 bytes)

-- left/right pad (workaround; no native LPAD/RPAD)
SELECT substr('0000' || '42', -4, 4);  -- '0042'

Регистр и обрезка

upper()/lower() по умолчанию влияют только на ASCII-буквы — не-ASCII символы не меняются, если не загружено ICU. trim() по умолчанию убирает ведущие и хвостовые пробелы или конкретные символы, заданные вторым аргументом. replace() делает буквальную замену подстроки (без regex и шаблонов). Нативного TRANSLATE() для замены набора символов нет — выстраивайте цепочку replace() или используйте свою функцию.

sqlite
SELECT upper('hello');       -- 'HELLO'
SELECT lower('HELLO');       -- 'hello'
SELECT upper('日本語');      -- '日本語' (no case to change)

SELECT trim('  hi  ');            -- 'hi'  (both sides)
SELECT ltrim('  hi  ');           -- 'hi   ' (left only)
SELECT rtrim('  hi  ');           -- '  hi' (right only)

-- trim specific characters
SELECT trim('xxhelloxx', 'x');    -- 'hello'
SELECT ltrim('aaabbb', 'a');      -- 'bbb'

-- replace characters
SELECT replace('a-b-c', '-', '/'); -- 'a/b/c'
SELECT replace('Hello World', 'o', '0'); -- 'Hell0 W0rld'

Конкатенация и printf

Оператор || — стандартный конкатенатор SQL, но он распространяет NULL (NULL || x = NULL) — оборачивайте в COALESCE, когда NULL возможны. printf()/format() приносит форматирование в стиле C и является самым чистым способом строить строки с выравниванием, форматированием чисел и разделителями тысяч. quote() возвращает строку, безопасно закавыченную и экранированную для вставки в SQL, — удобно при построении динамического SQL.

sqlite
-- || operator concatenates
SELECT 'Hello' || ' ' || 'World';      -- 'Hello World'
SELECT username || ' <' || email || '>' FROM users;

-- NULL propagates through || (NULL || 'x' IS NULL)
SELECT 'a' || NULL || 'b';             -- NULL
-- use COALESCE to handle NULLs:
SELECT COALESCE(username, '') || ' <' || COALESCE(email, '') || '>';

-- printf-style formatting (also called format())
SELECT printf('%s has %d orders', 'alice', 5);  -- 'alice has 5 orders'
SELECT format('%,d', 1234567);                   -- '1,234,567' (3.38+)
SELECT printf('%5.2f', 3.14159);                 -- ' 3.14'
SELECT printf('%04d', 42);                       -- '0042'

-- quote() escapes a string for SQL
SELECT quote("It's alive");   -- '''It''s alive'''

Разбиение и поиск

instr() находит первое вхождение подстроки (с 1; 0 если не найдено). В SQLite нет нативного SPLIT_PART — эмулируйте через substr + instr или рекурсивный CTE. Идиома «посчитать вхождения» вычитает длины до и после удаления разделителя. char() строит строку из кодовых точек; unicode() возвращает кодовую точку первого символа. Для полноценного разбиения загрузите JSON-массив через json_each.

sqlite
-- find position of substring (1-indexed; 0 if not found)
SELECT instr('hello world', 'world');   -- 7
SELECT instr('hello', 'xyz');           -- 0

-- emulate split by getting the Nth field
-- (no native SPLIT_PART; use a CTE or substring + instr)
SELECT
  substr('a,b,c,d', 1, instr('a,b,c,d', ',') - 1) AS first;  -- 'a'

-- count occurrences of a substring
SELECT (length('a,b,c,d') - length(replace('a,b,c,d', ',', ''))) / length(',') AS commas;
-- 3

-- reverse a string
SELECT reverse('hello');   -- 'olleh'

-- character from integer code
SELECT char(65, 66, 67);   -- 'ABC'
SELECT unicode('A');       -- 65

LIKE / GLOB / Collations

LIKE по умолчанию регистронезависим для ASCII (частая неожиданность); для регистрозависимости используйте COLLATE BINARY. GLOB всегда регистрозависим и поддерживает классы символов в стиле shell. NOCASE — встроенная коллация для сравнения без учёта регистра; применяйте её к индексу столбца, чтобы регистронезависимые запросы использовали индекс. Для не-ASCII свёртки регистра загрузите расширение ICU или храните копию в нижнем регистре с индексом.

sqlite
-- LIKE: case-insensitive (ASCII), wildcards % and _
SELECT * FROM users WHERE username LIKE 'al%';
SELECT * FROM users WHERE username LIKE '_lice';

-- case-sensitive LIKE
SELECT * FROM users WHERE username LIKE 'Al%' COLLATE BINARY;

-- make LIKE case-insensitive for non-ASCII too (needs ICU)
-- or store a lowercased copy and query that
SELECT * FROM users WHERE LOWER(username) = LOWER('Alice');

-- GLOB: case-sensitive, Unix-style (* ? [abc] [a-z])
SELECT * FROM users WHERE username GLOB 'A*';
SELECT * FROM users WHERE username GLOB '[A-D]*';

-- built-in collations: BINARY (default), NOCASE, RTRIM
CREATE INDEX idx_users_name_ci ON users(username COLLATE NOCASE);
SELECT * FROM users WHERE username = 'ALICE' COLLATE NOCASE;

-- COLLATE in ORDER BY
SELECT * FROM users ORDER BY username COLLATE NOCASE;
14

Функции JSON

Извлечение значений

json_extract (и операторы -> / ->> с 3.38+) — рабочая лошадка для чтения JSON. $ — корень; $.key обращается к свойству объекта; $.arr[i] — к элементу массива (с 0). ->> возвращает скаляр (text/integer/real), -> возвращает JSON (поэтому '$.b' на массиве даёт массив как JSON). Часто запрашиваемые поля JSON храните обычными столбцами или добавьте индекс по выражению json_extract для производительности.

sqlite
-- json_extract: get a value by JSON path
SELECT json_extract('{"a": 1, "b": [10, 20, 30]}', '$.a');       -- 1
SELECT json_extract('{"a": 1, "b": [10, 20, 30]}', '$.b[1]');    -- 20
SELECT json_extract('{"a": 1, "b": [10, 20, 30]}', '$.b');       -- '[10,20,30]'

-- -> returns JSON; ->> returns scalar (text/integer/etc.)
SELECT '{"a": 1, "b": "x"}' -> '$.a';   -- 1 (as JSON)
SELECT '{"a": 1, "b": "x"}' ->> '$.a';  -- 1 (as scalar)
SELECT '{"a": 1, "b": "x"}' ->> '$.b';  -- 'x' (as text)

-- nested paths
SELECT json_extract('{"u": {"name": "alice"}}', '$.u.name');   -- 'alice'

-- array length
SELECT json_array_length('[1, 2, 3]');  -- 3
SELECT json_array_length('{"a": [1,2]}', '$.a');  -- 2

-- get object keys
SELECT json_each.name FROM json_each('{"a":1,"b":2}');  -- 'a', 'b'

Построение JSON

json_object и json_array строят JSON из SQL-значений (NULL становятся JSON null, а не опускаются). json_group_array и json_group_object (3.38+) — агрегатные функции, строящие JSON из строк запроса, бесценны для выдачи JSON API прямо из SQL. Вкладывайте конструкторы для сложных документов. Используйте json_quote, чтобы безопасно вставить строку как JSON-строку.

sqlite
-- build a JSON object
SELECT json_object('id', 1, 'name', 'alice', 'active', 1);
-- {"id":1,"name":"alice","active":1}

-- build a JSON array
SELECT json_array(1, 2, 3, 'four', NULL);
-- [1,2,3,"four",null]

-- nest them
SELECT json_object(
  'user', json_object('id', 1, 'name', 'alice'),
  'roles', json_array('admin', 'editor')
);

-- aggregate rows into a JSON array
SELECT json_group_array(username) FROM users WHERE active = 1;
-- ["alice","bob","carol"]

-- aggregate into a JSON object (key:value)
SELECT json_group_object(username, email) FROM users WHERE active = 1;
-- {"alice":"[email protected]","bob":"[email protected]"}

-- quote a string as JSON
SELECT json_quote('hello');   -- '"hello"'

Изменение JSON

json_set/insert/replace различаются поведением вставки и обновления; json_remove удаляет пути. Они возвращают НОВЫЕ значения JSON — JSON в SQLite неизменяем, поэтому для сохранения изменений сделайте UPDATE столбца результатом: UPDATE t SET data = json_set(data, '$.x', 1) WHERE id = 5. json_patch (RFC 7396) сливает объекты; null в патче удаляет ключи. Для больших документов горячие поля лучше хранить отдельными столбцами.

sqlite
-- json_set: insert or update a path
SELECT json_set('{"a": 1}', '$.b', 2);
-- {"a":1,"b":2}
SELECT json_set('{"a": 1}', '$.a', 99);
-- {"a":99}

-- json_insert: insert only if path does NOT exist
SELECT json_insert('{"a": 1}', '$.a', 99, '$.b', 2);
-- {"a":1,"b":2}  (a unchanged because it exists)

-- json_replace: update only if path EXISTS
SELECT json_replace('{"a": 1}', '$.a', 99, '$.b', 2);
-- {"a":99}  (b not added because it doesn't exist)

-- json_remove: delete a path
SELECT json_remove('{"a": 1, "b": 2}', '$.b');
-- {"a":1}

-- json_patch: merge per RFC 7396
SELECT json_patch('{"a": 1, "b": 2}', '{"b": 3, "c": 4}');
-- {"a":1,"b":3,"c":4}

Запросы к JSON-массивам

json_each и json_tree — виртуальные табличные функции, разворачивающие JSON в строки. json_each обрабатывает один массив или объект; json_tree рекурсивно обходит всю структуру, показывая пути fullkey. Это открывает множество-ориентированные операции над JSON-массивами — фильтрацию, join и агрегацию. Чтобы запрашивать массивы объектов в столбце, используйте join в стиле LATERAL: FROM users u, json_each(u.tags) e.

sqlite
-- json_each: virtual table, one row per array element
SELECT value FROM json_each('[10, 20, 30]');
-- 10, 20, 30

-- with index
SELECT key, value FROM json_each('["a","b","c"]');
-- 0,'a'  1,'b'  2,'c'

-- query an array stored in a column
SELECT u.username, e.value AS tag
FROM users u, json_each(u.tags) e
WHERE e.value LIKE 'admin%';

-- aggregate back to JSON
SELECT json_group_array(value)
FROM json_each('[10, 20, 30]')
WHERE value > 15;
-- [20,30]

-- json_tree: recursively walk nested structures
SELECT fullkey, value
FROM json_tree('{"a": {"b": [1, 2]}}');
-- '$','{"a":{"b":[1,2]}}'
-- '$.a','{"b":[1,2]}'
-- '$.a.b[0]',1
-- '$.a.b[1]',2

Индексирование и валидация JSON

Индексируйте выражения json_extract для быстрого поиска по полям JSON — запрос должен использовать идентичное выражение. json_valid() возвращает 1/0; используйте в CHECK-ограничении, чтобы в столбце был только JSON. json_type() сообщает тип JSON по пути ('object', 'array', 'integer', 'real', 'string', 'boolean', 'null'). При тяжёлых JSON-нагрузках денормализуйте горячие поля в обычные индексированные столбцы, оставив JSON для гибкого хвоста.

sqlite
-- expression index on a JSON field for fast lookups
CREATE INDEX idx_events_type
ON events(json_extract(data, '$.type'));
SELECT * FROM events WHERE json_extract(data, '$.type') = 'login';

-- unique index on a JSON field
CREATE UNIQUE INDEX idx_users_email_json
ON users(json_extract(profile, '$.email'));

-- validate JSON
SELECT json_valid('{"a": 1}');     -- 1
SELECT json_valid('{a: 1}');       -- 0 (unquoted keys)

-- JSON type of a path
SELECT json_type('{"a": 1, "b": [1,2]}', '$.a');  -- 'integer'
SELECT json_type('{"a": 1, "b": [1,2]}', '$.b');  -- 'array'

-- check constraint for valid JSON columns
CREATE TABLE events (
  id INTEGER PRIMARY KEY,
  data TEXT CHECK (json_valid(data))
);
15

Оконные функции

Основы OVER

Оконные функции (SQLite 3.25+) вычисляются по набору строк, связанных с текущей, не схлопывая их, в отличие от GROUP BY. OVER () означает «все строки»; OVER (ORDER BY ...) задаёт порядок для ранжирования. ROW_NUMBER уникален; RANK оставляет пропуски после ничьих; DENSE_RANK не оставляет. Оконные функции допустимы в SELECT и ORDER BY, но не в WHERE — фильтруйте внешним запросом или CTE.

sqlite
-- window functions: aggregate OVER a 'window' of rows
-- without collapsing rows (unlike GROUP BY)
SELECT username, age,
  AVG(age) OVER () AS overall_avg
FROM users;

-- ROW_NUMBER: unique sequential number per row
SELECT username,
  ROW_NUMBER() OVER (ORDER BY created) AS row_num
FROM users;

-- RANK / DENSE_RANK
SELECT username, age,
  RANK()       OVER (ORDER BY age DESC) AS rank,
  DENSE_RANK() OVER (ORDER BY age DESC) AS dense_rank
FROM users;

PARTITION BY

PARTITION BY делит строки на группы для оконной функции, как GROUP BY, но без схлопывания: каждая строка сохраняет идентичность, получая агрегат по группе. Паттерн «топ N на группу» (ROW_NUMBER + внешний WHERE rn <= N) — каноническое решение для «3 последних заказа на клиента», «5 последних постов на автора» и т. п. Обычно он намного быстрее коррелированных подзапросов.

sqlite
-- per-group ranking / aggregation
SELECT username, role, age,
  ROW_NUMBER() OVER (PARTITION BY role ORDER BY age DESC) AS rank_in_role,
  AVG(age)     OVER (PARTITION BY role)                   AS avg_role_age
FROM users;

-- top N per group (common pattern)
SELECT * FROM (
  SELECT user_id, total,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY total DESC) AS rn
  FROM orders
) WHERE rn <= 3;

-- percent of group total
SELECT username, role,
  age * 1.0 / SUM(age) OVER (PARTITION BY role) AS pct_of_role_total
FROM users;

Спецификации фрейма

Фрейм определяет, какие строки видит оконная функция относительно текущей. UNBOUNDED PRECEDING..CURRENT ROW даёт накопительный итог. N PRECEDING..N FOLLOWING даёт скользящее окно (скользящее среднее). Фрейм по умолчанию с ORDER BY использует RANGE, включающий равные строки (с тем же значением ORDER BY); ROWS строго считает строки. Путаница между фреймами и ORDER BY — частый источник неверной аналитики.

sqlite
-- RUNNING TOTAL: cumulative sum over rows so far
SELECT username, total,
  SUM(total) OVER (ORDER BY created
                   ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running
FROM orders;

-- MOVING AVERAGE: 3-row window centered on current row
SELECT username, total,
  AVG(total) OVER (ORDER BY created
                   ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS moving_avg
FROM orders;

-- default frame for ORDER BY windows:
--   RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
-- default frame (no ORDER BY):
--   ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

-- ROWS vs RANGE: ROWS counts rows; RANGE includes peers.

LAG и LEAD

LAG и LEAD обращаются к другим строкам относительно текущей — необходимы для анализа временных рядов (дельты день-к-дню, поиск серий). Необязательный третий аргумент — значение по умолчанию для строк вне диапазона. FIRST_VALUE/LAST_VALUE возвращают значения с краёв фрейма; учтите, LAST_VALUE требует явного фрейма UNBOUNDED FOLLOWING, иначе вернёт значение текущей строки (классическая ловушка).

sqlite
-- compare current row to previous / next row
SELECT username, created,
  LAG(created, 1)  OVER (ORDER BY created) AS prev_created,
  LEAD(created, 1) OVER (ORDER BY created) AS next_created
FROM users;

-- difference from previous value (time-series analysis)
SELECT date, sales,
  sales - LAG(sales, 1) OVER (ORDER BY date) AS day_over_day
FROM daily_sales;

-- LAG with default value and offset
SELECT username, created,
  LAG(username, 3, 'N/A') OVER (ORDER BY created) AS three_back
FROM users;

-- FIRST_VALUE / LAST_VALUE / NTH_VALUE
SELECT username, age,
  FIRST_VALUE(age) OVER (ORDER BY age) AS youngest,
  LAST_VALUE(age)  OVER (ORDER BY age
                         ROWS BETWEEN UNBOUNDED PRECEDING
                         AND UNBOUNDED FOLLOWING) AS oldest
FROM users;

NTILE и перцентили

NTILE(n) делит упорядоченные строки на n примерно равных корзин: 4 для квартилей, 100 для перцентилей, 10 для децилей. CUME_DIST — доля строк со значением <= текущего; PERCENT_RANK — относительный ранг в виде доли 0..1. Это строительные блоки статистических и аналитических запросов. Как и все оконные функции, они не схлопывают строки — оборачивайте во внешний запрос для фильтрации.

sqlite
-- divide rows into N equal buckets (quartiles, percentiles)
SELECT username, age,
  NTILE(4) OVER (ORDER BY age) AS quartile
FROM users;

-- CUME_DIST: cumulative distribution (0..1)
SELECT username, age,
  CUME_DIST() OVER (ORDER BY age) AS cume_dist
FROM users;

-- PERCENT_RANK: rank as a fraction (0..1)
SELECT username, age,
  PERCENT_RANK() OVER (ORDER BY age) AS pct_rank
FROM users;

-- NTH_VALUE: value at a specific row in the frame
SELECT username, age,
  NTH_VALUE(age, 2) OVER (ORDER BY age) AS second_age
FROM users;
16

CTE (клауза WITH)

Базовый CTE

CTE (Common Table Expression), определённый через WITH, — именованный подзапрос с областью видимости: он улучшает читаемость и может упоминаться несколько раз в одном операторе. SQLite обычно материализует CTE (пишет во временную таблицу), поэтому производительность похожа на производную таблицу. Используйте CTE, чтобы разбить сложные запросы на этапы, предварительно агрегировать или избежать повторов подзапросов. Это стандарт SQL и переносимо.

sqlite
-- a CTE is a named temporary result set, scoped to one statement
WITH active_user_count AS (
  SELECT COUNT(*) AS n FROM users WHERE active = 1
)
SELECT username,
  (SELECT n FROM active_user_count) AS total_active
FROM users WHERE active = 1;

-- CTE in a join
WITH user_totals AS (
  SELECT user_id, SUM(total) AS spent
  FROM orders
  GROUP BY user_id
)
SELECT u.username, COALESCE(t.spent, 0) AS spent
FROM users u
LEFT JOIN user_totals t ON t.user_id = u.id
ORDER BY spent DESC;

-- CTEs make complex queries readable; they are syntactic sugar
-- (SQLite often materializes them, like a temp table).

Несколько CTE

Несколько CTE через запятую могут ссылаться на предыдущие CTE в том же WITH — идеально для пошаговых пайплайнов данных в одном операторе. Каждый CTE — логический шаг; чтение сверху вниз повторяет поток данных. Это куда поддерживаемее вложенных подзапросов. SQLite материализует каждый CTE; для очень больших промежуточных результатов рассмотрите временные таблицы между операторами вместо одной гигантской цепочки CTE.

sqlite
-- chain multiple CTEs, each able to reference earlier ones
WITH
active_users AS (
  SELECT id, username FROM users WHERE active = 1
),
user_orders AS (
  SELECT user_id, COUNT(*) AS n_orders, SUM(total) AS spent
  FROM orders
  GROUP BY user_id
),
labeled AS (
  SELECT au.username,
    COALESCE(uo.n_orders, 0) AS orders,
    COALESCE(uo.spent, 0)    AS spent
  FROM active_users au
  LEFT JOIN user_orders uo ON uo.user_id = au.id
)
SELECT * FROM labeled
WHERE orders > 0
ORDER BY spent DESC;

Рекурсивный CTE

Рекурсивные CTE имеют якорь (базовый случай) и рекурсивный член, соединённые UNION (с дедупликацией) или UNION ALL. Это стандартный способ SQL обходить деревья (оргструктуры, деревья категорий, ветки комментариев) и графы. SQLite ограничивает глубину рекурсии. Приём «generate series» бесценен, так как в SQLite нет GENERATE_SERIES (хотя расширение series существует).

sqlite
-- a recursive CTE references itself; perfect for hierarchies
WITH RECURSIVE subordinates AS (
  -- anchor: top-level manager
  SELECT id, name, manager_id, 0 AS depth
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  -- recursive: their direct reports, one level deeper
  SELECT e.id, e.name, e.manager_id, s.depth + 1
  FROM employees e
  JOIN subordinates s ON e.manager_id = s.id
)
SELECT depth, id, name FROM subordinates ORDER BY depth, name;

-- generate a series of numbers (no native GENERATE_SERIES)
WITH RECURSIVE cnt(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cnt WHERE n < 10
)
SELECT n FROM cnt;

Иерархические запросы

Рекурсивные CTE особенно хороши на самореферентных таблицах. Первый пример строит путь через слэш от корня к каждому узлу — удобно для хлебных крошек. Второй находит всех потомков узла (поддерево). Используйте UNION (а не UNION ALL), если возможны циклы, чтобы не зациклиться; иначе UNION ALL быстрее. На очень глубоких деревьях следите за производительностью — рекурсивные CTE могут быть медленными без индексов по столбцу соединения.

sqlite
-- categories with parent/child self-reference
CREATE TABLE categories (
  id INTEGER PRIMARY KEY,
  name TEXT,
  parent_id INTEGER REFERENCES categories(id)
);

-- full path from root to each node
WITH RECURSIVE cat_path(id, name, path) AS (
  SELECT id, name, name
  FROM categories WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.name, cp.path || ' > ' || c.name
  FROM categories c
  JOIN cat_path cp ON c.parent_id = cp.id
)
SELECT id, path FROM cat_path ORDER BY path;

-- find all descendants of a node
WITH RECURSIVE descendants(id) AS (
  SELECT id FROM categories WHERE id = 5   -- root of subtree
  UNION ALL
  SELECT c.id FROM categories c
  JOIN descendants d ON c.parent_id = d.id
)
SELECT * FROM categories WHERE id IN (SELECT id FROM descendants);

Оптимизация CTE

SQLite 3.35+ поддерживает подсказки MATERIALIZED / NOT MATERIALIZED для управления вычислением CTE. MATERIALIZED заставляет писать во временную таблицу (хорошо для дорогих CTE, используемых многократно); NOT MATERIALIZED встраивает CTE, чтобы оптимизатор мог протолкнуть предикаты (хорошо для дешёвых CTE). Планировщик обычно выбирает хорошо сам; используйте подсказки только когда EXPLAIN QUERY PLAN показывает плохой план. Рекурсивные CTE всегда материализуются.

sqlite
-- MATERIALIZED: force SQLite to materialize (temp table)
WITH cte AS MATERIALIZED (
  SELECT user_id, SUM(total) AS spent FROM orders GROUP BY user_id
)
SELECT u.username, cte.spent
FROM users u JOIN cte ON cte.user_id = u.id;

-- AS NOT MATERIALIZED: inline the CTE (may run multiple times)
WITH cte AS NOT MATERIALIZED (
  SELECT COUNT(*) FROM users WHERE active = 1
)
SELECT * FROM products WHERE id < (SELECT * FROM cte);

-- choose MATERIALIZED when the CTE is expensive and used
-- multiple times; NOT MATERIALIZED when it's cheap or used
-- once and could benefit from predicate pushdown.

-- recursive CTEs are always materialized.
17

Резервное копирование и восстановление

.dump (текстовая копия)

.dump экспортирует базу как обычные SQL-операторы INSERT — читаемо, диффаемо и переносимо между версиями и архитектурами SQLite. Это самый совместимый формат копии. Восстановление проигрывает SQL в свежую базу. --nosys пропускает sqlite_sequence и другие внутренние таблицы. Для больших баз .dump медленный и даёт огромные файлы; используйте .backup (бинарная копия) или Online Backup API.

sqlite
-- dump the entire database as SQL text (portable)
sqlite3 mydb.sqlite .dump > backup.sql

-- dump a single table
sqlite3 mydb.sqlite ".dump users" > users.sql

-- dump only data (no schema)
sqlite3 mydb.sqlite ".dump" | grep '^INSERT' > data.sql

-- restore from a dump
sqlite3 newdb.sqlite < backup.sql
-- or inside the CLI:
.read backup.sql

-- dump in a specific order to satisfy FK constraints
sqlite3 mydb.sqlite ".dump --nosys" > backup.sql

-- .dump works on attached databases too
ATTACH 'archive.sqlite' AS archive;
.dump archive

.backup (бинарная копия)

.backup делает безопасную онлайн-бинарную копию через SQLite Online Backup API — он работает даже при активных писателях, создавая транзакционно согласованный снимок. Это рекомендуемый способ копировать работающую базу. Результат — валидный файл SQLite, поэтому восстановление — просто копирование файла. В отличие от .dump, бинарный формат совместим по версиям и архитектурам, но не читаем человеком.

sqlite
-- binary copy to another file (online, safe with active writers)
sqlite3 mydb.sqlite ".backup 'backup.sqlite'"

-- inside the CLI
.backup backup.sqlite
.backup main backup.sqlite     -- source 'main', target file

-- backup to an attached database
ATTACH 'backup.sqlite' AS bk;
.backup main bk;
DETACH bk;

-- .backup uses the Online Backup API internally: it copies
-- page-by-page and handles concurrent writers safely, so it's
-- the right choice for live production backups.

-- restore = just copy the file back (or use it directly)
cp backup.sqlite mydb.sqlite

Online Backup API

Online Backup API (sqlite3_backup_*) — золотой стандарт резервного копирования: он копирует живую базу постранично, корректно обрабатывая параллельные записи, и даёт согласованный снимок. Python sqlite3.Connection.backup() и .backup() в better-sqlite3 для Node оборачивают его. Он работает в режиме WAL. Простое копирование файла может дать повреждённую копию, если идёт запись, — используйте копирование только на остановленной базе или со снимками уровня ФС.

sqlite
-- the sqlite3 CLI's .backup uses this C API; in Python:
import sqlite3
src = sqlite3.connect("mydb.sqlite")
dst = sqlite3.connect("backup.sqlite")
src.backup(dst)
dst.close()
src.close()

-- with progress callback
def progress(remaining, total):
    print(f"{total - remaining}/{total} pages copied")
src.backup(dst, pages=progress)

-- this is the safest backup method: it's online (doesn't block
-- writers), transactionally consistent, and works on WAL-mode
-- databases. Prefer it over raw file copy for production.

-- Node.js (better-sqlite3):
const backup = db.backup('backup.sqlite');
while (!backup.completed) backup.step(-1);
backup.delete();

Подключение баз

ATTACH DATABASE подключает до 10 файлов баз к одному соединению как именованные схемы (main, temp и подключённые). Можно запрашивать, соединять и копировать данные между ними через имена с указанием схемы (main.users, archive.orders). Это основа архивации (перенос старых строк в архивный файл), шардирования и слияний. DETACH закрывает схему, но оставляет файл целым. Транзакции между базами атомарны в режиме WAL.

sqlite
-- work with multiple DB files in one connection
ATTACH DATABASE 'archive.sqlite' AS archive;
ATTACH DATABASE 'lookup.sqlite' AS lookup;

-- query across databases
SELECT u.username, a.note
FROM main.users u
JOIN archive.old_users a ON u.id = a.id;

-- copy a table between databases
CREATE TABLE archive.users_copy AS
SELECT * FROM main.users;

-- move data: insert into one DB, delete from another
INSERT INTO archive.orders SELECT * FROM main.orders WHERE created < '2023-01-01';
DELETE FROM main.orders WHERE created < '2023-01-01';

-- list attached databases
.databases

-- detach (does NOT delete the file)
DETACH DATABASE archive;

-- limit: 10 attached databases (compile-time SQLITE_MAX_ATTACHED).

Восстановление и повреждения

integrity_check обнаруживает структурные повреждения; .recover (3.29+) — лучший инструмент спасения данных из повреждённого файла: он извлекает читаемые строки и пропускает плохие страницы, выдавая SQL, который можно загрузить в свежую базу. Если .recover не справился, вернитесь к .dump и смиритесь с частичной потерей. Большинство повреждений — от небезопасных файловых операций (копирование живой БД, сетевые ФС, потеря питания при synchronous=OFF). Всегда делайте копию перед попытками восстановления.

sqlite
-- check integrity
PRAGMA integrity_check;
PRAGMA quick_check;

-- recover from a corrupt database (best effort)
sqlite3 corrupt.sqlite ".recover" > recovered.sql
sqlite3 clean.sqlite < recovered.sql

-- .recover extracts everything readable, skipping corrupt pages

-- if .recover fails, try dumping what you can
sqlite3 corrupt.sqlite ".dump" > partial.sql 2>errors.txt
sqlite3 clean.sqlite < partial.sql

-- point-in-time from a backup file
cp nightly_backup.sqlite mydb.sqlite

-- after a crash, WAL is replayed automatically on next open;
-- if stuck, checkpoint manually:
sqlite3 mydb.sqlite "PRAGMA wal_checkpoint(TRUNCATE);"

-- prevent corruption: never overwrite a live DB file,
-- never share a DB on a network filesystem without WAL off.
18

Импорт и экспорт

Импорт CSV

.import читает CSV в таблицу; если таблицы нет, SQLite создаёт её со всеми столбцами TEXT (часто не то, что нужно, — создайте таблицу заранее с правильными типами). --skip N пропускает строки заголовка. .mode csv настраивает парсер на поля в кавычках с запятыми и переводами строк внутри. Для больших импортов оберните в BEGIN/COMMIT и временно поставьте PRAGMA synchronous=OFF ради ускорения в 10+ раз. Отключите индексы на время массовой загрузки и пересоздайте после.

sqlite
-- import a CSV file into a table
.mode csv
.import users.csv users
-- ^ creates 'users' table with text columns named from the header row

-- import into an EXISTING table (column order must match)
.mode csv
.import --skip 1 users.csv users   -- --skip 1 to skip header

-- recommended: create the table first with proper types
CREATE TABLE users (id INTEGER, name TEXT, email TEXT);
.mode csv
.import --skip 1 users.csv users

-- from the shell, one-shot
sqlite3 mydb.sqlite ".mode csv" ".import users.csv users"

-- handle quotes/escapes
.mode csv
.separator ,
.import data.csv mytable

Экспорт CSV

.mode csv с .headers on даёт стандартный CSV со строкой заголовка — направьте в файл через .output. Флаги оболочки -header -csv плюс перенаправление вывода делают это одной строкой без входа в CLI. Для TSV используйте .mode list с .separator "\t". Для больших экспортов это потоково и экономно по памяти. Чтобы экспортировать несколько таблиц, меняйте .output между запросами.

sqlite
-- export a query to CSV
.mode csv
.headers on
.output users.csv
SELECT id, username, email FROM users;
.output stdout

-- from the shell, one-shot
sqlite3 mydb.sqlite -header -csv \
  "SELECT id, username, email FROM users" > users.csv

-- export all rows of a table
.mode csv
.headers on
.output products.csv
SELECT * FROM products;
.output stdout

-- custom separator
.mode list
.separator "|"
.output users.txt
SELECT id, username FROM users;
.output stdout

Экспорт JSON

.mode json выдаёт по одному JSON-объекту на строку (без обёртки в массив). Для корректного JSON-массива используйте json_group_array(json_object(...)) — это даёт полный контроль над именами полей и вложенностью и работает с вложенными массивами через коррелированные подзапросы. Это мощный способ строить JSON API прямо из SQL без ORM. Для больших результатов лучше потоковый .mode json построчно, чем один гигантский массив в памяти.

sqlite
-- export a query as JSON (one JSON object per row)
.mode json
.output users.json
SELECT id, username, email FROM users;
.output stdout

-- build a single JSON array with json_group_array
SELECT json_group_array(json_object(
  'id', id, 'username', username, 'email', email
)) AS users_json
FROM users;
-- [{"id":1,"username":"alice","email":"[email protected]"},...]

-- nested JSON (orders with their items)
SELECT json_object(
  'order_id', o.id,
  'total', o.total,
  'items', (SELECT json_group_array(json_object('name', i.name, 'qty', i.qty))
            FROM order_items i WHERE i.order_id = o.id)
) AS order_json
FROM orders o;

Импорт JSON

В SQLite нет .import для JSON — загрузите файл как TEXT (кодом приложения или .read с INSERT'ами), затем разберите через json_each/json_extract. json_each(array) разворачивает массив в строки для множественной вставки. Для больших JSON-файлов делайте потоковый разбор в приложении (не загружайте файл целиком в память). Проверяйте json_valid() при загрузке и используйте CHECK-ограничение на JSON-столбцах для постоянной целостности.

sqlite
-- load a JSON file into a text column, then parse
CREATE TABLE raw (data TEXT);
-- (no built-in .import for JSON; load via CLI or app)

-- once loaded, parse with json_each (array) or json_extract (object)
SELECT json_extract(data, '$.name') AS name,
       json_extract(data, '$.age')  AS age
FROM raw;

-- expand a JSON array into rows
INSERT INTO users (username, email)
SELECT json_extract(value, '$.username'),
       json_extract(value, '$.email')
FROM raw, json_each(raw.data);

-- import via Python
import sqlite3, json
db = sqlite3.connect("mydb.sqlite")
with open("users.json") as f:
    for row in json.load(f):
        db.execute("INSERT INTO users(username,email) VALUES(?,?)",
                   (row["username"], row["email"]))
db.commit()

Excel и другие форматы

Для Excel экспортируйте CSV (Excel открывает его напрямую); .mode excel (3.36+) добавляет BOM, чтобы UTF-8 отображался корректно. .mode markdown даёт разметку таблицы для вставки в документацию. .mode insert генерирует INSERT-операторы — отлично для наполнения тестовых баз или миграции между схемами. .mode quote закавычивает каждое значение, полезно когда данные содержат запятые, переводы строк или кавычки, которые могут запутать другие инструменты. .mode table/box рисуют ASCII-таблицы для терминала.

sqlite
-- Excel: export CSV (Excel opens it) or use .mode excel
.mode csv
.headers on
.output report.csv
SELECT * FROM monthly_report;
.output stdout

-- .mode excel (3.36+): CSV with BOM, friendly to Excel
.mode excel
.output report.csv
SELECT * FROM monthly_report;
.output stdout

-- .mode markdown: GitHub-flavored markdown tables
.mode markdown
SELECT id, username, email FROM users LIMIT 5;

-- .mode insert: generate INSERT statements
.mode insert users
.output seed.sql
SELECT id, username, email FROM users;
.output stdout
-- produces: INSERT INTO "users" VALUES(1,'alice','[email protected]');

-- .mode quote: CSV with all values quoted
.mode quote
.output safe.csv
SELECT * FROM users;
.output stdout
19

Интеграция с приложениями

Python (sqlite3)

sqlite3 в Python входит в стандартную библиотеку. ВСЕГДА используйте плейсхолдеры ? (или :name) — никогда f-строки или % — чтобы предотвратить SQL-инъекции и корректно обрабатывать кавычки. executemany намного быстрее цикла execute для массовых вставок. Установите row_factory = sqlite3.Row для доступа по именам столбцов. Используйте контекстные менеджеры (with conn:) для автоматического commit/rollback. Для конкурентности — одно соединение на поток (sqlite3 по умолчанию запрещает совместное использование между потоками).

sqlite
import sqlite3

# connect (creates the file if missing)
conn = sqlite3.connect("mydb.sqlite")
conn.row_factory = sqlite3.Row  # dict-like rows
cur = conn.cursor()

# parameterized query (NEVER use string formatting!)
cur.execute(
    "INSERT INTO users (username, email) VALUES (?, ?)",
    ("alice", "[email protected]"),
)
conn.commit()

# query with parameters
cur.execute("SELECT id, username FROM users WHERE active = ?", (1,))
for row in cur:
    print(row["id"], row["username"])

# named parameters
cur.execute(
    "SELECT * FROM users WHERE age >= :min_age",
    {"min_age": 18},
)

# bulk insert (fast)
cur.executemany(
    "INSERT INTO users (username, email) VALUES (?, ?)",
    [("bob", "[email protected]"), ("carol", "[email protected]")],
)
conn.commit()
conn.close()

Node.js (better-sqlite3)

better-sqlite3 синхронный (без callback/Promise-обвязки) и самый быстрый драйвер SQLite для Node.js. Подготовленные операторы переиспользуются: готовьте один раз, запускайте много. .transaction(fn) оборачивает функцию в BEGIN/COMMIT (или ROLLBACK при исключении) и кардинально быстрее авто-коммита на каждый оператор. Используйте плейсхолдеры ? во избежание SQL-инъекций. Включите WAL для конкурентности. Для Promise-API оберните вызовы или используйте sqlite/sqlite3.

sqlite
const Database = require("better-sqlite3");
const db = new Database("mydb.sqlite");
db.pragma("journal_mode = WAL");

// prepared statement (synchronous, fast)
const insert = db.prepare(
  "INSERT INTO users (username, email) VALUES (?, ?)"
);
const info = insert.run("alice", "[email protected]");
console.log(info.lastInsertRowid);

// query multiple rows
const select = db.prepare("SELECT id, username FROM users WHERE active = ?");
const rows = select.all(1);  // array of objects

// query one row
const one = select.get(1);

// transaction (atomic, fast)
const insertMany = db.transaction((users) => {
  for (const u of users) insert.run(u.username, u.email);
});
insertMany([{username: "bob"}, {username: "carol"}]);

db.close();

URI соединения и режимы

URI-имена файлов (file:...) открывают режимы read-only, shared-cache и in-memory — передайте uri=True в Python или строку file: драйверам Node. :memory: по умолчанию своё для каждого соединения; file:name?mode=memory&cache=shared создаёт именованную in-memory БД, общую для соединений в одном процессе (отлично для тестов). Задайте busy_timeout, чтобы писатели, кратко ждущие блокировку, не падали сразу. Всегда ставьте PRAGMA foreign_keys = ON на каждое соединение.

sqlite
# open read-only
sqlite3.connect("file:mydb.sqlite?mode=ro", uri=True)

# open in-memory (vanishes on close)
sqlite3.connect(":memory:")
# or via URI:
sqlite3.connect("file::memory:", uri=True)

# shared in-memory database (visible across connections in same process)
sqlite3.connect("file:memdb1?mode=memory&cache=shared", uri=True)

# with a busy timeout (wait instead of erroring on locks)
conn = sqlite3.connect("mydb.sqlite", timeout=30)

# Node.js: same URI form
const db = new Database("file:mydb.sqlite?mode=ro", { readonly: true });

# Node.js in-memory
const mem = new Database(":memory:");

# Python: per-connection pragmas
conn.execute("PRAGMA journal_mode = WAL")
conn.execute("PRAGMA foreign_keys = ON")

Транзакции и конкурентность

Транзакции группируют операторы атомарно — всё или ничего. SQLite допускает одного писателя в момент времени (блокировка записи на уровне базы), но режим WAL позволяет параллельных читателей. В Python isolation_level по умолчанию оборачивает DML в неявные транзакции (нужен commit). Для явного контроля задайте isolation_level = None и выполняйте BEGIN/COMMIT сами. Используйте busy_timeout (PRAGMA busy_timeout или timeout соединения), чтобы писатель ждал, а не сразу получал SQLITE_BUSY.

sqlite
# Python: explicit transaction
conn = sqlite3.connect("mydb.sqlite")
conn.isolation_level = None  # autocommit off -> manual BEGIN
cur = conn.cursor()
cur.execute("BEGIN")
try:
    cur.execute("UPDATE accounts SET bal = bal - 100 WHERE id = 1")
    cur.execute("UPDATE accounts SET bal = bal + 100 WHERE id = 2")
    cur.execute("COMMIT")
except Exception:
    cur.execute("ROLLBACK")
    raise

// Node.js (better-sqlite3) transaction
const transfer = db.transaction((from, to, amt) => {
  db.prepare("UPDATE accounts SET bal = bal - ? WHERE id = ?").run(amt, from);
  db.prepare("UPDATE accounts SET bal = bal + ? WHERE id = ?").run(amt, to);
});
transfer(1, 2, 100);

-- SQLite allows ONE writer at a time; many readers in WAL mode.
-- Use a busy_timeout to wait for locks instead of failing fast.

Подготовленные операторы и безопасность

Параметризованные запросы (плейсхолдеры ?) — важнейшая практика безопасности: они предотвращают SQL-инъекции и корректно обрабатывают преобразование типов и экранирование. НИКОГДА не подставляйте пользовательский ввод в SQL-строки. Плейсхолдеры работают только для значений; идентификаторы (имена таблиц и столбцов) параметризовать нельзя — проверяйте их по белому списку. Для LIKE экранируйте % и _ во вводе, чтобы пользователи не могли внедрять шаблоны. Это касается любого языка и драйвера.

sqlite
-- SQL injection vulnerability (NEVER do this):
-- app: "SELECT * FROM users WHERE name = '" + user_input + "'"
-- user_input = "'; DROP TABLE users; --"
-- result:    SELECT * FROM users WHERE name = ''; DROP TABLE users; --'

-- SAFE: parameterized query (placeholders are NOT string substitution)
-- Python:
cur.execute("SELECT * FROM users WHERE name = ?", (user_input,))
-- Node.js:
stmt.get(user_input);

-- identifiers (table/column names) CANNOT be parameterized;
-- validate against a whitelist:
allowed_tables = {"users", "orders", "products"}
if table not in allowed_tables:
    raise ValueError("bad table")
cur.execute(f"SELECT * FROM {table} WHERE id = ?", (id_,))

-- LIKE with user input: escape wildcards
cur.execute(
    "SELECT * FROM users WHERE name LIKE ? ESCAPE '\'",
    (user_input.replace("\", "\\").replace("%", "\%").replace("_", "\_") + "%",)
)
20

Оптимизация производительности

EXPLAIN QUERY PLAN

EXPLAIN QUERY PLAN — инструмент настройки номер один. 'USING INDEX' = хорошо; 'SCAN' (полное сканирование таблицы) = обычно плохо на больших таблицах. Для joins проверяйте, что столбцы соединения проиндексированы и планировщик не сканирует большую таблицу. EXPLAIN (без QUERY PLAN) показывает байткод VDBE — нужен редко, но полезен для глубокой отладки. Экспериментальная команда EXPERT может подсказать индексы (3.36+), или используйте dot-команду CLI .expert.

sqlite
-- see how SQLite executes a query (essential for tuning)
EXPLAIN QUERY PLAN
SELECT * FROM users WHERE email = '[email protected]';

-- good: uses an index
--   SEARCH users USING INDEX idx_users_email (email=?)

-- bad: full table scan
--   SCAN users

-- join plan
EXPLAIN QUERY PLAN
SELECT u.username, o.total
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.active = 1;

-- automated index recommendation (3.36+, experimental)
SELECT * FROM users WHERE email = 'x';
-- then:
EXPLAIN QUERY PLAN SELECT * FROM sqlite_stat1;

-- lower-level: EXPLAIN (bytecode)
EXPLAIN SELECT * FROM users WHERE id = 1;

VACUUM и ANALYZE

VACUUM пересобирает файл, возвращая место после удалений и дефрагментируя страницы, — запускайте после больших удалений или периодически для write-heavy баз. VACUUM INTO (3.27+) пишет чистую копию, не блокируя источник надолго. ANALYZE обновляет статистику sqlite_stat1, по которой планировщик выбирает индексы, — запускайте после массовых загрузок или изменений схемы и данных. auto_vacuum=INCREMENTAL возвращает место постепенно без полного VACUUM.

sqlite
-- VACUUM: rebuild the database file, reclaiming free space
VACUUM;
-- after large deletes, this shrinks the file and defrags pages

-- VACUUM INTO: write a defragmented copy to a new file (3.27+)
VACUUM INTO 'clean.sqlite';

-- incremental VACUUM (only with auto_vacuum enabled)
PRAGMA auto_vacuum = INCREMENTAL;
PRAGMA incremental_vacuum(100);  -- free up to 100 pages

-- ANALYZE: update planner statistics for better query plans
ANALYZE;                          -- all tables
ANALYZE users;                    -- one table
ANALYZE users(idx_users_email);   -- one index

-- run ANALYZE after bulk loads or major data changes

Массовая загрузка

Для массовой загрузки: оберните в одну транзакцию (самое большое ускорение — авто-коммит на каждую строку катастрофически медленный), временно отключите synchronous и журналирование, удалите и пересоздайте индексы, рассмотрите увеличение PRAGMA cache_size. Dot-команда .import быстрее INSERT-операторов. Для действительно огромных загрузок рассмотрите .backup заранее собранной базы. Всегда восстанавливайте безопасные PRAGMA после загрузки продакшен-данных.

sqlite
-- fastest bulk load into a fresh table
PRAGMA synchronous = OFF;     -- risk: power-loss corruption (use only for one-off loads)
PRAGMA journal_mode = MEMORY;
PRAGMA temp_store = MEMORY;

BEGIN;
CREATE TABLE big (id INTEGER, data TEXT);
.import data.csv big
COMMIT;

-- then turn safety back on
PRAGMA synchronous = FULL;
PRAGMA journal_mode = WAL;

-- faster still: drop indexes, load, recreate
DROP INDEX idx_big_data;
-- ... load ...
CREATE INDEX idx_big_data ON big(data);

-- use a transaction (10-100x faster than autocommit per row)
BEGIN;
INSERT INTO big VALUES (1, 'a');
-- ... thousands of inserts ...
COMMIT;

FTS5 (полнотекстовый поиск)

FTS5 — полнотекстовый поисковый движок SQLite: он индексирует текст для быстрых MATCH-запросов, гораздо быстрее LIKE '%term%', который сканирует каждую строку. Поддерживает булевы операторы (AND/OR/NOT), префиксы (term*), фразы ("...") и ранжирование по релевантности. Паттерн external-content (content='posts') избегает дублирования данных. Держите FTS-индекс в синхронизации триггерами (INSERT/UPDATE/DELETE по базовой таблице). FTS5 идеален для поиска по мере ввода, автодополнения и поиска по документам.

sqlite
-- create a full-text search virtual table
CREATE VIRTUAL TABLE posts_fts USING fts5(
  title, body,
  content='posts', content_rowid='id'
);

-- populate it (keep in sync with triggers or rebuild)
INSERT INTO posts_fts(rowid, title, body)
  SELECT id, title, body FROM posts;

-- search with MATCH (much faster than LIKE '%term%')
SELECT p.* FROM posts p
JOIN posts_fts f ON f.rowid = p.id
WHERE posts_fts MATCH 'sqlite python';

-- ranking by relevance
SELECT p.title, rank
FROM posts p
JOIN posts_fts f ON f.rowid = p.id
WHERE posts_fts MATCH 'sqlite OR python'
ORDER BY rank;

-- prefix and phrase queries
SELECT * FROM posts_fts WHERE posts_fts MATCH 'sql*';        -- prefix
SELECT * FROM posts_fts WHERE posts_fts MATCH '"full text"'; -- exact phrase

Частые ловушки и тюнинг

Большинство проблем производительности SQLite сводятся к нескольким причинам: отсутствие индексов по столбцам WHERE/JOIN/ORDER BY, отсутствие транзакций (авто-коммит на строку — причина №1 медленных массовых вставок), молча выключенные внешние ключи и SELECT * на широких таблицах. Включайте WAL и внешние ключи в каждой настройке соединения. Разбивайте большие DELETE на батчи, чтобы избежать долгих блокировок записи и огромных временных файлов. Запускайте ANALYZE после крупных изменений данных, чтобы планировщик выбирал хорошие планы.

sqlite
-- 1. Enable foreign keys (OFF by default!)
PRAGMA foreign_keys = ON;

-- 2. Use WAL for concurrency
PRAGMA journal_mode = WAL;

-- 3. Index foreign keys AND join columns
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- 4. Avoid SELECT * (more I/O, breaks on schema change)
SELECT id, username FROM users;

-- 5. Use transactions for multi-statement writes
BEGIN;
-- ... several inserts/updates ...
COMMIT;

-- 6. Don't index every column (slows writes)
-- index selectively on WHERE/JOIN/ORDER BY columns.

-- 7. Use LIMIT on exploratory queries
SELECT * FROM big_table LIMIT 10;

-- 8. Batch UPDATEs/DELETEs to avoid long locks
DELETE FROM logs WHERE id IN (
  SELECT id FROM logs WHERE ts < '2023-01-01' LIMIT 10000
);

-- 9. Prefer EXISTS over IN for large subquery results
-- 10. Run ANALYZE after big data changes.

Was this helpful?