将 SQLite 用作文档数据库

SQLite 很早就支持 JSON 了。

但最近它增加了一个杀手级特性：生成列（3.31.0 版本，发布于 2020-01-22）。

这意味着你可以直接将 JSON 插入 SQLite，然后提取其中的数据并建立索引，即把 SQLite 当作文档数据库使用。PostgreSQL 一直都能做到这一点，Elastic 等产品也是这么做的，但将其嵌入到嵌入式数据库中对于轻量级应用来说非常方便。

开始吧：

$ sqlite3
SQLite version 3.31.1 2020-01-27 19:55:54
Connected to a transient in-memory database.
sqlite> CREATE TABLE t (
   body TEXT,
   d INT GENERATED ALWAYS AS (json_extract(body, '$.d')) VIRTUAL);
sqlite> insert into t values(json('{"d":"42"}'));
sqlite> select * from t WHERE d = 42;
{"d":"42"}|42

就是这么简单，d 列是从提供的 JSON 中提取出来的。

(顺便提一下，难点可能在于获取足够新的 SQLite 版本，目前 macOS 上的 Homebrew 已包含此版本，否则你可能需要使用像 nixpkgs-unstable 这样的不稳定源。)

这有一些不错的特性。通常建议在插入时通过 json() 函数对 JSON 进行压缩和验证，因为 SQLite 没有原生的 JSON 类型，它允许任何内容。不过并没有机制强制执行这一点，你可以添加约束，但很可能会忘记……而使用 GENERATED ALWAYS 配合 json_extract 意味着无效的 JSON 会在 INSERT 时报错 Error: malformed JSON。

这还可以更进一步：

sqlite> CREATE TABLE x (
  body TEXT,
  id TEXT GENERATED ALWAYS AS (json_extract(body, '$.id')) VIRTUAL NOT NULL);
sqlite> insert into x values('');
Error: malformed JSON
sqlite> insert into x values('{}');
Error: NOT NULL constraint failed: x.id

我们可以强制要求插入的 JSON 中包含特定字段，这里通过添加 NOT NULL 来实现，但也可以使用约束和其他 SQLite 特性！

你会发现我在这些示例中使用了 VIRTUAL 来定义生成列。还有一个选项是使用 STORED，这本质上是缓存了这些值，不过缺点是你无法通过 ALTER TABLE 添加这些列。

不过，你总是可以给列创建索引，即使是虚拟列：

CREATE INDEX xid on x(id);

然后检查其是否按预期工作：

EXPLAIN QUERY PLAN SELECT * FROM x WHERE id='foo';
QUERY PLAN
`--SEARCH TABLE x USING INDEX xid (id=?)

结合 ALTER TABLE，我们可以添加新列并建立索引：

ALTER TABLE x ADD COLUMN text TEXT
    GENERATED ALWAYS AS (json_extract(body, '$.text')) VIRTUAL;
INSERT INTO x VALUES(json('{"id":43, "text":"test"}'));
CREATE INDEX xtext ON x(text);

这样做的好处是，你可以从一个只包含单个 JSON 列的简单表开始，随着在 JSON 中发现有用数据，再逐步添加列和索引。例如，这对处理 Webhook 非常有效：直接将接收到的所有数据插入表中，稍后再提取有用的信息。玩得开心。

17^th June 2020 in code

原始来源： Hacker News

将 SQLite 用作文档数据库

评论 (0)