Thread by @nalgeon

SQLite — не игрушка! Один лайк — один факт, почему она отлично подойдет вам в повседневной работе. И неважно, разработчик вы, аналитик, тестировщик, админ или продакт-менеджер.

Для затравки несколько известных фактов. sqlite — самая распространенная СУБД в мире, встроена во все ОС. Она работает без сервера. Для разработчиков — встраивается прямо в приложение. Для всех остальных — удобная консоль (REPL) одним файлом (sqlite.exe на Windows).

Консоль — это киллер-фича sqlite: более мощный инструмент анализа данных, чем Excel, и сильно более простой, чем какой-нибудь pandas. Данные из CSV загружаются одной командой, таблица создается автоматически:

> .import --csv city.csv city
> select count(*) from city;
1117

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

select
century || '-й век' as dates,
count(*) as city_count
from history
group by century
order by century desc;

Куча форматов выгрузки данных: sql, csv, json, даже markdown и html. Все делается парой команд:

> .mode json
> .output city.json
> select * from city;

Нет ничего удобнее sqlite для анализа и преобразования JSON. Можно селектить данные напрямую из файла, как будто это обычная таблица. Или загрузить в таблицу и селектить оттуда — как вам удобнее.

Неважно, насколько развесистый JSON — можно выбрать атрибуты любой вложенности:

select
json_extract(value, '$.id') as id,
json_extract(value, '$.name') as name
from
json_tree(readfile('industry.sample.json'))
where
path like '$[%].industries'
;

Разумеется, поддерживаются CTE (конструкция WITH) и джойны, тут даже примеры приводить не буду. А если данные иерархичные (таблица ссылается сама на себя через столбец вроде parent_id) — поможет рекурсивный WITH. Иерархию любого уровня можно «размотать» одним запросом.

Множества? Нет проблем: UNION, INTERSECT, EXCEPT к вашим услугам.

select employer_id
from employer_area
where area_id = 1

except

select employer_id
from employer_area
where area_id = 2;

Хотите рассчитать один столбец на основании нескольких других? Пожалуйста — вычисляемые столбцы.

alter table vacancy
add column salary_net integer as (
case when salary_gross = true then
round(salary_from/1.13)
else
salary_from
end
);

Описательная статистика? Запросто: среднее, медиана, процентили, стандартное отклонение и вот это все. Правда, придется подключить библиотеку со стат. функциями, но это тоже одна команда (и один файл).

Лирическое отступление. SQLite традиционно бедна функциями по сравнению с каким-нибудь постгресом. Но их легко добавить, чем многие и занимаются — каждый кто во что горазд. Получается легкий бардак.

Поэтому я решил сделать нормальный набор библиотек с функциями, с разделением по предметной области и автоматической сборкой для всех ОС. Пока библиотек там немного, но скоро прибавится: github.com/nalgeon/sqlite…

А вот еще о статистике. Можно нарисовать распределение значений прямо в консоли. Смотрите, какая милота:

И еще очень, очень много всего! Если интересно, лайкайте исходный твит, и продолжим ближе к вечеру. А, и подписывайтесь на t.me/sqliter, конечно

SQLite спокойно работает с десятками миллионов записей (с сотнями, говорят, тоже — но я не проверял). Обычные INSERT дают на моем ноуте около 240 тысяч записей в секунду. А если подключить исходный CSV как виртуальную таблицу (такая специальная фича) — еще в 2 раза быстрее.

Поддерживаются частичные индексы и индексы по выражениям, как в «больших» СУБД. Можно даже строить индексы на виртуальных столбцах.

Так SQLite можно превратить хоть в документную БД: хранить сырой json и строить индексы по json_extract() на нужных столбцах.

Можно и как графовую базу использовать. Тут уже придется либо злые WITH RECURSIVE использовать (не буду пугать вас примерами), либо добавить щепотку программирования: github.com/dpapathanasiou…

Полнотекстовый поиск из коробки!

create virtual table books_fts
using fts5(title, author, publisher);

insert into books_fts
select * from books;

select * from books_fts
where books_fts match 'sqlite OR postgres*';

Среди разработчиков распространено мнение, что SQLite не подходит для веба, потому что поддерживает только одного клиента. Это миф. В режиме write-ahead log (стандартная фича современных СУБД) читателей может быть сколько угодно. Писатель — один, но часто больше и не надо.

SQLite отлично подходит для маленьких и средних сайтов и приложений. Например, сайт sqlite.org использует SQLite в качестве базы, не заморачиваясь с оптимизацией (~200 запросов на страницу). При этом у него 700К визитов в месяц, а работает быстрее 95% сайтов.

Хотите in-memory базу для промежуточных вычислений? Одна строчка кода на питоне:

db = sqlite3.connect(":memory:")

Можно даже обращаться к ней из нескольких соединений:

db = sqlite3.connect("file::memory:?cache=shared")

Есть навороченные оконные функции. UPSERT, UPDATE FROM и generate_series() как в постгресе. R-Tree индексы. Регекспы, fuzzy-поиск и гео. Но кажется, на сегодня хватит. Надеюсь, этот тред вдохновит кого-нибудь применить SQLite в своих задачах. Спасибо, что прочитали!