SQL and Key-Value Over the Same Bytes: An Old Database Idea Worth Making Small
A sensor node writes a reading every few seconds. The firmware wants one thing from storage: put a record by key, get it back by key, as fast as the flash allows. The dashboard on the gateway wants something else, like average temperature by sensor by hour, joined to a table of sites. The usual answer is two databases with a translation job between them, a key-value store on the device and SQLite or Postgres upstream. Then comes a year of debugging the translation: a unit that changed, a row that arrived twice, a field the device encodes one way and the gateway reads another.
The better answer gives every record two personalities. The same bytes are reachable by key with no parser or planner in the path, and reachable by SQL when someone needs a relational question answered. No second copy, no export job, no translation layer. That’s a stronger idea than “SQLite plus a key-value API”, which gets you two databases that happen to share a file: the key-value calls store opaque values, so SQL sees one value column and has to decode it on every query.
Older Than It Looks
None of this is new, and it’s better to say so with some pride. HandlerSocket, a MySQL plugin from DeNA, gave applications key access to InnoDB data while skipping SQL parsing. Yoshinori Matsunobu’s 2010 post “Using MySQL as a NoSQL” described passing 750,000 queries per second on a commodity server, a loud argument that for simple lookups the SQL layer takes a big share of the cost. MySQL later shipped an InnoDB memcached plugin that offered direct key access to InnoDB tables; it was deprecated in MySQL 8.0 and has since been removed. FoundationDB’s Record Layer, described in a 2019 paper, puts structured records and indexes on top of an ordered key-value store. EmbedDB, a research system, targets microcontrollers with key-value and time-series storage plus relational queries.
If you’re on a Linux gateway, SQLite already covers most of this, and you can stop reading. A prepared statement parses and plans once, each lookup is then a B-tree seek, and on a laptop that’s fast enough that you’ll never see the difference. The idea earns its keep in two places: small CPUs, where binding and stepping a tiny program for every lookup is a real cost, and storage that has to be identical at both ends of a link. The opportunity now is making the combination small and coherent, from a microcontroller to a gateway.
One Encoding, One Write Path
Define the record encoding first, because both paths have to read it. A record is a schema version, a null bitmap and the columns in declared order, with fixed-width columns at fixed offsets so a key read of one column doesn’t decode the rest. Keys use an order-preserving encoding (big-endian integers with the sign bit flipped, for example), so the byte order of keys matches what ORDER BY would produce and a range scan by key prefix walks the same B-tree that SQL scans. The key path is then the planner’s output for one very common query, built once when the table is created instead of on every call.
Here are the two doors into one table, as a sketch of an API that doesn’t exist yet.
// Declared once; this is when the key path gets compiled.
db_exec(db, "CREATE TABLE reading (sensor INT, ts INT, temp_c REAL, PRIMARY KEY (sensor, ts))");
db_exec(db, "CREATE INDEX reading_temp ON reading (temp_c)");
// Key path: no parse, no plan. Updates the row and reading_temp in one transaction.
kv_put(db, "reading", KEY(7, 1791000000), ROW(21.5));
kv_get(db, "reading", KEY(7, 1791000000), &row);
// SQL path: same pages, same index, so it sees the put above.
db_query(db, "SELECT sensor, ts FROM reading WHERE temp_c > 21");
The comment on kv_put carries the whole design: it updates the row and the reading_temp index in one transaction, using the same write routine the SQL INSERT calls. A fast path with its own copy of the write logic is a second implementation of your database, with all the bugs that implies. Skipped index updates are the classic failure. The key path writes the row, the index keeps the old value, and a SQL query returns a reading that’s no longer true. Constraints, triggers and any change log you keep for sync fall into the same trap. One write routine, two front doors.
Where Fast Paths Go Wrong
Schema changes alter the encoding both paths read. A version tag in each record header lets old rows decode with defaults after an ADD COLUMN, so nothing needs rewriting the moment you alter a table, and the same DDL statement regenerates the compiled key-path routines. A device can’t carry a migration engine, so the gateway should own schema changes and ship devices a new table descriptor. Renaming a column is cheap. Changing a column’s type rewrites data, and the design should say that out loud.
Concurrency has to mean the same thing on both paths. If a key write becomes visible to other readers at a different moment than the same row written through SQL, you’ve built a database with two isolation levels, and the bugs will show up only under load. On a microcontroller with one main loop this is trivial. On a gateway it means both paths go through one transaction manager, and one writer at a time is a fine rule to start with.
There’s a shortcut for the SQL half: expose the record store to SQLite as a virtual table, so SQLite parses, plans and joins while your code supplies rows. It saves you from writing a SQL engine. The catch is transactions. SQLite’s commit and your store’s commit become two events that have to agree, and agreeing is the problem this whole design exists to avoid. Benchmark honestly, too. On a developer laptop the key path will barely beat a prepared SELECT, and that result is correct for the laptop. Measure on the target chip with real record sizes and the flash write cost included, because on a sensor node a flash write can cost more than everything the parser could waste.
A Small Version of It
AltSql is one current attempt at the small version, not the inventor of the idea. It describes itself as a native hybrid of key-value and SQL, from the sensor to the gateway. Devices use the key-value interface and keep working offline (AltSql Core compiles to about 15 KB of code for a microcontroller). The gateway database, AltSql DB, stores the very same records byte for byte and answers SQL over them. It’s alpha, with the hardware simulated so far, and open source under Apache 2.0; this spotlight post covers what happens when the link drops. Identical bytes at both ends is the differentiator worth judging it on, because if the device and the gateway ever disagree about the encoding, the idea is gone. Source distribution matters as much as the design: SQLite ships as a single C file, the amalgamation, and a hybrid that wants a place on small chips needs the same property, which is the argument in one binary, one file, no daemon.
Version 0.1 of the generic idea needs less than people expect: one record format, a handful of column types, a primary key plus one kind of secondary index, get, put, delete and a prefix range scan on the key side, and a SQL subset covering WHERE on the key and indexed columns, ORDER BY and simple aggregates. It refuses a second storage format, a network protocol and anything that lets the two paths differ. If you only need the key side with counters and expiry, an embedded state engine is the smaller idea. If you want those records on two devices to converge, database sync gets easier when both ends share one encoding, and harder everywhere else.
If the two paths ever disagree, delete one.