KV interface?

[glommer]

As suggested by a user on twitter today: https://twitter.com/__pragma__/status/1577756654837575680

Personally I think we should look at all the use cases where SQLite is just used for K/V queries with an ORM and ask ourselves “how could we expose a programmatic K/V API that doesn’t incur the overhead of a SQL parser and query planner?"

This seems a great idea, and great direction. But it operates on the assumption that the SQL layer is itself, expensive. My intuition is that it is, but only if prepared statements are not used. For anything that is performance sensitive, statements are likely prepared.

@psarna do you have an intuition on the cost here?

[psarna]

As per SQLite docs, "All SQL must be converted into a prepared statement before it can be run", so the model already (rightfully) forces users to prepare their statements. libSQL's prepared statement is actually a compiled program that runs on its virtual machine, and I'd expect it to be fast enough even for key/value stores, but only a benchmark would verify that.

[kinthaiofficial]

A KV interface over libSQL is interesting — SQLite's key-value semantics are already quite close, just with a relational surface.

The most direct mapping:

-- KV table, with optional namespacing
CREATE TABLE kv (
    namespace TEXT NOT NULL DEFAULT 'default',
    key       TEXT NOT NULL,
    value     BLOB,
    expires_at INTEGER,  -- unix timestamp, NULL = never
    PRIMARY KEY (namespace, key)
);

-- Efficient range scans (KV killer feature)
SELECT key, value FROM kv
WHERE namespace = ? AND key BETWEEN ? AND ?
ORDER BY key;

-- TTL cleanup (run periodically)
DELETE FROM kv WHERE expires_at IS NOT NULL AND expires_at < unixepoch();

For agent workloads we use this pattern to store per-agent state blobs — each agent has its own namespace, and keys map to memory identifiers. libSQL's embedded replication means the KV store syncs to the primary without us managing replication.

The question is whether a KV interface (hiding SQL) adds value or just adds friction. If your callers are writing raw get/set, a thin abstraction makes sense. If they'd benefit from range scans or TTL queries, exposing the SQL surface is worth it.

What's the target use case? That'd help figure out whether the KV abstraction is the right layer.

[glommer]

@kinthaiofficial this is not a bad idea, but all of our new development has been going towards https://github.com/tursodatabase/turso now. We have found the rewrite (instead of the fork) to be a lot more fruitful to build the kind of new generation capabilities we wanted to build.