AgentSkillsCN

how-to-store-sqlite-as-nosql-store

探索如何利用 SQLite 的 JSON 支持，在这一强大的嵌入式数据库中构建类 NoSQL 的文档存储，并内置基于 TTL 的过期机制。

SKILL.md
--- frontmatter
name: how-to-store-sqlite-as-nosql-store
description: Discover how to leverage SQLite's JSON support to build a NoSQL-like document store, complete with TTL-based expiration, within this powerful embedded database.
metadata:
  url: https://rodydavis.com/posts/sqlite/no-sql
  last_modified: Tue, 03 Feb 2026 20:04:37 GMT

How to store SQLite as NoSQL Store

SQLite is a very capable edge database that can store various shapes of data.

NoSQL databases are very popular due to the schema-less nature of storing of the data but it is totally possible to store these documents in SQLite.

SQLite actually has great JSON support and even supports JSONB.

Create the table 

To store JSON documents we need to create a table to store the values as strings.

code
CREATE TABLE documents (
  path TEXT NOT NULL PRIMARY KEY,
  data TEXT,
  ttl INTEGER,
  created INTEGER NOT NULL,
  updated INTEGER NOT NULL,
  UNIQUE(path)
);

path

data

ttl

created

updated

/posts/1

{"id":1}

NULL

0

0

/posts/2

{"id":2}

NULL

0

0

/users/1

{"id":1}

NULL

0

0

The basic idea is to store a JSON object and an unique path.

There is an optional TTL to automatically delete rows when they reach the stale date.

Save a document 

To save a document we can encode our JSON as a string or binary and save in in the table with a unique path.

code
INSERT OR REPLACE 
INTO documents (path, data, ttl, created, updated) 
VALUES (:path, :data, :ttl, :created, :updated)
RETURNING *;

You can also use JSON functions to save the Object to a valid JSON string.

code
INSERT OR REPLACE 
INTO documents (path, data, ttl, created, updated) 
VALUES ("/posts/1", json('{"id" 1}'), NULL, 0, 0)
RETURNING *;

path

data

ttl

created

updated

/posts/1

{"id":1}

NULL

0

0

Reading a document 

To read a document we just need the path. If a TTL is set we can calculate if the current date is greater than the offset and not return the document.

code
SELECT * FROM documents 
WHERE path = :path
AND (
	(ttl IS NOT NULL AND ttl + updated < unixepoch())
	OR
	ttl IS NULL
);

path

data

ttl

created

updated

/posts/1

{"id":1}

NULL

0

0

Get documents for a collection 

We can query all the docs for a given collection using some built-in functions and a path prefix:

code
SELECT *
FROM documents 
WHERE (
	path LIKE :prefix
	AND
	(LENGTH(path) - LENGTH(REPLACE(path, '/', ''))) = (LENGTH(:prefix) - LENGTH(REPLACE(:prefix, '/', '')))
)
AND (
	(ttl IS NOT NULL AND ttl + updated < unixepoch())
	OR
	ttl IS NULL
)
ORDER BY created;

It is expected to search for a :prefix with the /% at the end:

"/my/path/%" // search for /my/path

Deleting expired documents 

Using the TTL field we can delete all expired documents:

code
DELETE FROM documents
WHERE ttl IS NOT NULL
AND ttl + updated < unixepoch();

Demo