SQLite CLI: работа с консолью sqlite3

Что такое sqlite cli

sqlite3 — это официальная консоль sqlite3, которая поставляется вместе с SQLite. Запускаете бинарник, передаёте файл базы — и получаете приглашение, в котором можно выполнять SQL. Ни сервера, ни логина, ни конфигов: только исполняемый файл и файл базы.

Командная строка sqlite3 пригодится, когда нужно быстро заглянуть в базу, прогнать разовый запрос, выгрузить схему или импортировать данные. Это самый быстрый способ покопаться в базе, не написав ни строчки прикладного кода.

$ sqlite3 mydata.db
SQLite version 3.45.0
Enter ".help" for usage hints.
sqlite>

Если файла mydata.db ещё нет, SQLite создаст его сам, как только вы внесёте первое изменение. А если запустить sqlite3 вообще без имени файла, откроется временная база прямо в памяти — после выхода от неё ничего не останется.

Выполнение SQL в консоли sqlite3

Оказавшись внутри shell, пишите SQL и заканчивайте каждый запрос точкой с запятой. Именно её ждёт консоль: нажмёте Enter без точки с запятой — увидите приглашение продолжения ввода, и только.

CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  year INTEGER
);

INSERT INTO books (title, year) VALUES ('Dune', 1965);
INSERT INTO books (title, year) VALUES ('Neuromancer', 1984);

SELECT * FROM books;

Точка с запятой — это сигнал «всё, выполняй». Забудешь её — и оболочка просто зависнет с приглашением ...>, ожидая продолжения. Поставь ; на новой строке, чтобы завершить команду.

Dot-команды — это не SQL

Всё, что начинается с точки (.), — это dot-команда, то есть указание самой оболочке, а не движку SQL. Точку с запятой они не требуют и работают только внутри консоли sqlite3.

sqlite> .tables
books

sqlite> .schema books
CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  year INTEGER
);

sqlite> .help

Те, что вы будете использовать постоянно:

• .tables — показать все таблицы в текущей базе данных.
• .schema [table] — вывести CREATE TABLE для конкретной таблицы или сразу для всех.
• .databases — список подключённых файлов баз данных.
• .mode <format> — изменить формат вывода результатов запроса.
• .headers on — показывать имена столбцов над результатами.
• .read file.sql — выполнить SQL-файл.
• .exit или .quit — выйти из консоли.

Полный список покажет .help. Команд там десятки, но в реальной работе вам пригодится от силы десяток.

Делаем вывод читаемым

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

sqlite> .headers on
sqlite> .mode column
sqlite> SELECT * FROM books;
id  title        year
--  -----------  ----
1   Dune         1965
2   Neuromancer  1984

Другие полезные режимы:

• .mode box — рисует таблицу с рамкой из Unicode-символов. Красиво смотрится на скриншотах.
• .mode csv — значения через запятую, удобно прокидывать в другие утилиты.
• .mode json — вывод в JSON, по одному объекту на строку.
• .mode line — каждая колонка на своей строке, выручает, когда строки широкие.

Все настройки сбрасываются при каждом запуске консоли. Чтобы они сохранялись, сложите их в файл ~/.sqliterc — sqlite3 подхватит его при старте.

Импорт и экспорт CSV в SQLite

В sqlite cli есть встроенная поддержка CSV. Переключитесь в режим CSV и загрузите файл в таблицу командой .import:

sqlite> .mode csv
sqlite> .import authors.csv authors
sqlite> SELECT * FROM authors LIMIT 3;

Если таблицы ещё нет, .import создаст её, взяв имена столбцов из первой строки. Если таблица уже существует, первая строка считается данными — пока вы не добавите --skip 1.

Чтобы выгрузить данные обратно, перенаправьте вывод в файл:

sqlite> .headers on
sqlite> .mode csv
sqlite> .output books.csv
sqlite> SELECT * FROM books;
sqlite> .output stdout

.output filename перенаправляет вывод всех последующих запросов в указанный файл — и так до тех пор, пока вы не вернёте всё обратно командой .output stdout. Механизм один и тот же, что для экспорта в CSV, что в JSON, что для дампа схемы.

Как выполнить SQL-файл в SQLite

Если запросов больше пары штук, удобнее сохранить их в файл и запустить целиком. Прямо из консоли sqlite3:

sqlite> .read setup.sql

Или прямо из обычного терминала, даже не заходя в саму консоль:

$ sqlite3 mydata.db < setup.sql
$ sqlite3 mydata.db "SELECT count(*) FROM books;"

Второй вариант выполняет один запрос и сразу завершает работу — то, что нужно для shell-скриптов или быстрых проверок. Если нужен форматированный вывод, скомбинируйте его с настройками .mode через флаг -cmd:

$ sqlite3 -header -column mydata.db "SELECT * FROM books;"

Как изучить незнакомую базу данных

Когда вам кидают .db-файл, а что внутри — непонятно, действуйте по такому сценарию:

$ sqlite3 mystery.db
sqlite> .tables
sqlite> .schema
sqlite> .indexes
sqlite> SELECT count(*) FROM some_table;

.schema без аргументов выдаёт все CREATE TABLE, CREATE INDEX и CREATE TRIGGER, какие есть в файле. .indexes показывает индексы по каждой таблице. Пять минут в консоли sqlite3 расскажут о базе больше, чем любой README.

Если нужен полный текстовый снимок, который потом можно развернуть в другом месте, используйте .dump:

sqlite> .output backup.sql
sqlite> .dump
sqlite> .output stdout

Получится SQL-скрипт, который полностью воссоздаёт базу — и схему, и данные, — если его потом скормить обратно sqlite3.

Как выйти из sqlite3

Способа три, все равнозначны:

sqlite> .exit
sqlite> .quit

Либо нажмите Ctrl+D на macOS и Linux (Ctrl+Z, а затем Enter на Windows). Все изменения уже сохранены — SQLite фиксирует каждый запрос сразу после его выполнения, если только вы явно не открыли транзакцию.

Дальше: GUI-инструменты для SQLite

Консоль sqlite3 — это быстро и удобно для скриптов, но иногда хочется просто покликать по таблицам, отсортировать столбцы и поправить строки в визуальном интерфейсе. Для SQLite есть несколько достойных GUI-инструментов — о них поговорим на следующей странице.