rodydavis 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.
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.
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.
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.
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:
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:
DELETE FROM documents
WHERE ttl IS NOT NULL
AND ttl + updated < unixepoch();