Начало работы
Основы CLI
SQLite хранит всю базу данных в одном файле. CLI sqlite3 открывает или создаёт файл .sqlite/.db по требованию. В отличие от MySQL/PostgreSQL, серверного процесса нет — приложения линкуют библиотеку SQLite напрямую. .dump экспортирует всю базу как текстовый SQL для резервной копии или миграции.
# 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 подскажет остальное.
-- 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 и другие. Каждая подключённая база — отдельный файл на диске.
-- 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-рамки.
-- 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.
-- 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"Таблицы и типы данных
Создание таблиц
INTEGER PRIMARY KEY — алиас rowid: он автоинкрементируется и является самым быстрым ключом. AUTOINCREMENT меняет алгоритм так, что удалённые ID никогда не переиспользуются (чуть медленнее; обычно не нужно). DEFAULT (expr) допускает произвольные выражения вроде datetime('now'). CREATE TABLE AS SELECT создаёт таблицу с результатами запроса, но не копирует ограничения и индексы.
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 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 хранит сырые байты как есть.
-- 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.