将SQLite用作文档数据库(2020)

Hacker News Top 工具

摘要

本文介绍如何利用SQLite的JSON支持与生成列,将其作为文档数据库使用,从而能够从JSON列中提取并索引数据。

暂无内容
查看原文
查看缓存全文

缓存时间: 2026/08/29 15:37

# SQLite作为文档数据库 来源:https://dgl.cx/2020/06/sqlite-json-support SQLite早已支持JSON功能。 但近期它增加了一项杀手级特性:生成列(generated columns)(https://www.sqlite.org/gencol.html)。(该功能于2020年1月22日发布的3.31.0版本中引入。) 这使得我们可以直接将JSON数据插入SQLite,然后由数据库提取数据并建立索引——即可以把SQLite当作文档数据库使用。虽然PostgreSQL早已支持此功能,Elasticsearch等产品更是其典型代表,但能在一个嵌入式数据库中拥有该特性,对轻量级应用而言实属福音。 让我们开始实践: ``` $ 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()`函数进行压缩和验证,因为SQLite没有专门的JSON类型,它会接受任何输入。但这并非强制要求,你可以添加约束条件,但很可能忘记执行。而使用`GENERATED ALWAYS`配合`json_extract`,无效JSON在插入时就会触发`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数据——将收到的所有原始数据直接插入表,后续再按需提取有价值的内容。尽情探索吧。 2020年6月17日 发布于code专栏 (https://dgl.cx/tag/code)

相似文章

SQLite:万能数据库解决方案

Lobsters Hottest

本文倡导将SQLite视为一种多功能且稳定的数据库解决方案,强调其在不同技术栈中替代多种其他工具的能力。

SQLite 3.53.0

Simon Willison's Blog

SQLite 3.53.0 发布,带来重要的累积改进,包括 ALTER TABLE 约束修改、新的 JSON 函数(json_array_insert),以及通过新的查询结果格式化库实现的重大 CLI 模式增强。

基于 SQLite 的分布式高性能 Blob 存储

Lobsters Hottest

Zerodha 介绍了 Blobasaur——一个基于 SQLite 构建的分片式、分布式轻量级 blob 存储服务器,在商用硬件上可处理数 MB 大小的 JSON blob,达到每秒数千次操作、个位数毫秒的延迟,并在评估了 ScyllaDB、Postgres、YugabyteDB、RocksDB、Ceph 和 SeaweedFS 后,用它替代了 SSDB。

我们如何为SQLite构建零磁盘、S3分层存储引擎

Lobsters Hottest

Rivet工程师详细介绍了他们如何为SQLite构建零磁盘、S3分层的存储引擎,实现了隔离数据库,具备即时启动、低延迟写入、时间点恢复和无限存储能力,可支持数百万Actors。

BriskDB

Hacker News Top

BriskDB 是一款处于早期阶段的数据库工具,它将 SQLite 文件转化为一个支持并行写入、兼容 PostgreSQL 并嵌入 Rust/Python API 的分片系统,旨在扩展 SQLite 的同时保持其简洁性。