ИИтог

SQLite можно использовать как документную базу данных через JSON и генерируемые столбцы

инфраструктураРедакция ИИтогHacker News🔥32Рейтинг ИИтог32Влияние45Новизна12Релевантность35Неожиданность15Участники25Достоверность80

Автор персонального блога dgl.cx (имя в тексте не указано) описывает приём, который есть в SQLite уже больше пяти лет, но остаётся малоизвестным: генерируемые столбцы (generated columns) позволяют вставить в таблицу целый JSON-документ и одновременно, без отдельного шага, выделить из него нужные поля в собственные столбцы, их можно индексировать и по ним можно фильтровать. Фактически SQLite при этом ведёт себя как документная база данных, хотя остаётся тем же однофайловым встроенным движком без отдельного сервера. По словам автора, так уже давно умеет работать PostgreSQL, а нечто похожее по духу даёт Elastic, но получить это во встроенной СУБД, по мнению автора, очень удобно для несложных задач.

Возможность добавлена в SQLite 3.31.0, вышедшей 22 января 2020 года. В демонстрации автор использует чуть более новую сборку, 3.31.1 от 27 января 2020 года: это последующий патч-релиз, а не версия, в которой генерируемые столбцы впервые появились. Синтаксис такой: в CREATE TABLE рядом с обычным текстовым столбцом body объявляется столбец вида d INT GENERATED ALWAYS AS (json_extract(body, '$.d')) VIRTUAL. После этого INSERT INTO t VALUES(json('{"d":"42"}')) кладёт JSON в body, а число 42 автоматически появляется в столбце d, и по нему можно писать обычный SELECT * FROM t WHERE d = 42.

У SQLite нет отдельного типа для JSON, поэтому обычно в текстовый столбец пройдёт что угодно, если явно не проверять формат функцией json() при вставке, а это легко забыть сделать. Генерируемый столбец с json_extract() решает это автоматически: в демонстрации автор создаёт таблицу x со столбцом id TEXT GENERATED ALWAYS AS (json_extract(body, '$.id')) VIRTUAL NOT NULL, вставка пустой строки сразу возвращает ошибку malformed JSON, а вставка валидного, но пустого JSON '{}', ошибку NOT NULL constraint failed: x.id, потому что нужное поле id в нём отсутствует.

У генерируемых столбцов два режима. VIRTUAL, значение вычисляется на лету при каждом обращении и не хранится физически; такие столбцы можно свободно добавлять в уже существующую таблицу через ALTER TABLE. STORED, значение один раз считается и кэшируется на диске, но, в отличие от VIRTUAL, такие столбцы нельзя добавить в таблицу задним числом через ALTER TABLE. При этом даже виртуальный столбец можно проиндексировать обычным CREATE INDEX, и, как показывает EXPLAIN QUERY PLAN в статье, SQLite при поиске реально использует этот индекс, а не сканирует таблицу целиком.

Отсюда практический сценарий, который автор отдельно выделяет для приёма вебхуков: можно завести таблицу из одного JSON-столбца и складывать туда вообще всё, что присылает внешний сервис, ничего не проектируя заранее, а затем, по мере того как становится понятно, какие поля реально нужны, добавлять под них новые генерируемые столбцы и индексы, например, командой ALTER TABLE x ADD COLUMN text TEXT GENERATED ALWAYS AS (json_extract(body, '$.text')) VIRTUAL, а затем CREATE INDEX xtext ON x(text), не пересоздавая таблицу целиком. Отдельно автор отмечает, что на момент написания статьи получить достаточно новую версию SQLite было непросто: на macOS её давал Homebrew, а на других системах предлагалось использовать нестабильные сборки вроде nixpkgs-unstable.

Сам материал, не новость: судя по всему, он написан около 2020 года, и это следует не из текста статьи (дата публикации в нём не названа), а из пометки «(2020)» на публикации Hacker News, так HN обычно помечает старые, но всё ещё актуальные материалы. Обсуждение сейчас, это подъём старой статьи, а не сообщение о чём-то новом: никаких изменений в поведении SQLite с тех пор в тексте не упоминается.

Ключевые факты

  • Генерируемые столбцы появились в SQLite 3.31.0, вышедшей 22 января 2020 года, и позволяют извлекать поля из JSON прямо при вставке строки; в демонстрации автор использует чуть более новую сборку 3.31.1 от 27 января 2020 года.
  • Столбец d INT GENERATED ALWAYS AS (json_extract(body, '$.d')) VIRTUAL после INSERT с JSON {"d":"42"} автоматически получает значение 42, и по нему можно фильтровать обычным SELECT ... WHERE d = 42.
  • Раз столбец объявлен через json_extract(), вставка невалидного JSON падает с ошибкой malformed JSON уже на вставке, без этого SQLite, у которого нет своего типа JSON, пропустил бы в текстовый столбец что угодно.
  • Виртуальный (VIRTUAL) столбец всё равно можно проиндексировать через CREATE INDEX, и EXPLAIN QUERY PLAN подтверждает, что поиск идёт через индекс; хранимый (STORED) столбец физически кэширует значение, но, в отличие от VIRTUAL, его нельзя добавить в существующую таблицу через ALTER TABLE.
  • Автор советует начинать с таблицы из одного JSON-столбца и добавлять нужные генерируемые столбцы с индексами позже через ALTER TABLE по мере надобности, в статье такой приём назван особенно удобным для приёма вебхуков.

Почему это важно

Многие лёгкие приложения, мобильные, десктопные, встраиваемые, локальные инструменты, используют SQLite вместо полноценного сервера БД просто потому, что это один файл без отдельного процесса. До появления генерируемых столбцов хранить в такой базе что-то похожее на JSON-документы означало класть JSON в текстовый столбец и терять возможность фильтровать и индексировать по вложенным полям, либо разворачивать полноценный сервер вроде PostgreSQL или Elastic. Генерируемые столбцы закрывают этот разрыв: JSON вставляется как есть, а нужные поля отдельно выделяются в обычные столбцы с индексами и ограничениями целостности (например, NOT NULL). При этом сама возможность не новая, она в SQLite с версии 3.31.0, вышедшей в январе 2020 года, а статья, которая её описывает, сейчас просто заново всплыла на Hacker News.

Кому это важно

В первую очередь, разработчикам, которые используют SQLite как встроенную БД (десктопные и мобильные приложения, локальные утилиты, прототипы) и не хотят разворачивать отдельный сервер ради документо-ориентированного хранения. Отдельно автор выделяет сценарий с вебхуками: данные от внешнего сервиса часто приходят непредсказуемой JSON-структурой, и вместо того чтобы заранее проектировать под неё схему, можно сохранять JSON целиком в один столбец, а нужные поля выделять в индексируемые столбцы по мере того, как становится понятно, что из этих данных реально требуется.

Как это применить

Механика такая. В CREATE TABLE рядом с текстовым столбцом (например, body) объявляется столбец вида d INT GENERATED ALWAYS AS (json_extract(body, '$.d')) VIRTUAL, нужное поле из вставленного JSON автоматически попадает в него. К такому столбцу можно добавить NOT NULL: тогда вставка JSON без нужного поля упадёт с ошибкой ограничения, а вставка не-JSON, с ошибкой malformed JSON. Виртуальный (VIRTUAL) столбец можно проиндексировать обычным CREATE INDEX, и, как показывает EXPLAIN QUERY PLAN в статье, поиск реально идёт через этот индекс. Есть и второй режим, хранимый (STORED): значение считается один раз и кэшируется на диске, но такие столбцы, в отличие от VIRTUAL, нельзя добавить в уже существующую таблицу через ALTER TABLE, это стоит учитывать заранее. Новые поля и индексы можно добавлять по мере необходимости, например, командой ALTER TABLE x ADD COLUMN text TEXT GENERATED ALWAYS AS (json_extract(body, '$.text')) VIRTUAL, а затем CREATE INDEX xtext ON x(text), не пересоздавая таблицу целиком. Практическая деталь на момент публикации статьи: автор отмечает, что получить достаточно новую версию SQLite было непросто, на macOS её давал Homebrew, в других системах предлагалось использовать нестабильные сборки вроде nixpkgs-unstable.

Можно ли доверять

Материал, личный технический блог на домене dgl.cx, имя автора в тексте нигде не указано. Ник "lioeters", под которым ссылка выложена на Hacker News, это ник того, кто принёс ссылку на форум, а не автора статьи, и переносить его на авторство нельзя. При этом сама механика показана как воспроизводимая последовательность команд SQLite и их реального вывода, конкретные CREATE TABLE, INSERT, тексты ошибок (malformed JSON, NOT NULL constraint failed), вывод EXPLAIN QUERY PLAN, а не как голословное утверждение: команды можно самостоятельно повторить и получить тот же результат. Но сам материал, не новость: судя по всему, он написан около 2020 года, и это следует не из текста статьи (дата публикации в нём не названа), а из пометки «(2020)» на публикации Hacker News, так HN обычно помечает старые, но всё ещё актуальные материалы. Обсуждение сейчас, это подъём старой статьи, а не сообщение о чём-то новом; никаких изменений в поведении SQLite с тех пор в тексте не упоминается.

Риски и подводные камни

У SQLite нет отдельного типа для JSON: если не защитить столбец генерируемым правилом с NOT NULL или другим ограничением, невалидный или просто не тот JSON молча пройдёт в обычный текстовый столбец, и проблема всплывёт не при вставке, а позже, когда запрос к этому полю не сработает как ожидалось. Выбор между VIRTUAL и STORED нужно делать заранее: столбцы STORED, в отличие от VIRTUAL, нельзя добавить в уже существующую таблицу через ALTER TABLE, и если такая потребность возникнет позже, таблицу придётся пересоздавать. Автор не приводит ни одной цифры производительности, ни скорости выборки, ни размера индексов на JSON-полях, так что технику стоит проверить на своих объёмах данных, а не считать её быстрой по умолчанию только потому, что индекс используется. Также в тексте не сказано, отличается ли поведение в более новых версиях SQLite от версии 3.31.0, в которой генерируемые столбцы появились впервые, если используется современная сборка, детали синтаксиса стоит сверить с актуальной документацией SQLite.