Option A — “SQL Layer” over FoundationDB

Pattern: relational engine above FDB. Tables → subspaces, rows → KV pairs, ACID inherited from FDB transactions. This is conceptually what Apple’s old (now-archived) fdb-sql-layer did.

What we ship (and don’t)

We ship the storage half of a SQL engine: a catalog, row encoding, table scans, primary-key lookup, and atomic inserts/updates/deletes — all in about 250 lines.

We do not ship a SQL parser. The Go API itself is the query language:

db.CreateTable(sqllayer.TableDef{Name:"users", PK:"id", Columns: ...})
db.Insert("users", sqllayer.Row{"id":1, "name":"Alice", "city":"Paris"})
db.SelectWhere("users", func(r sqllayer.Row) bool { return r["city"] == "Paris" })

A real SQL frontend would compile SELECT ... WHERE city='Paris' into exactly the same backend call.

Key layout

<ns> 0x00 <tableName>                     -> msgpack(TableDef)    [catalog]
<ns> 0x01 <tableName> 0x00 <encodedPK>    -> msgpack(Row)         [data]
<ns> 0x02 <tableName>                     -> uint64 (LE counter)  [rowid seq]

• Catalog lets a fresh Open() reconstruct the in-memory schema cache.
• Data rows live under a per-table subspace, so GetRange on <ns> 0x01 <tableName> 0x00 is a full table scan in key order.
• Seq is used only when the table has no declared PK (we mint a rowid).

PK values are encoded with the same sign-flipping big-endian trick used in option-c-record-layer: integer rows sort numerically, strings sort lexicographically. This is what makes table scans produce ordered output “for free”.

Transactionality

Insert does the seq read+write and the row write in the same fdb.Transact, so two concurrent inserts can never collide on the same rowid: FDB will retry one of them. DropTable clears catalog, all rows, and the seq counter atomically — no orphan rows can be observed after a drop.

Why this is not a real SQLite layer

A real SQLite vtab would let you write SELECT name FROM users WHERE city='Paris' in standard SQL and have SQLite’s planner call back into FDB. Building that requires:

• Implementing the sqlite3_module C ABI (via CGO with mattn/go-sqlite3, or using modernc.org/sqlite’s yet-undocumented vtab hooks).
• Mapping SQLite’s xBestIndex / xFilter / xColumn callbacks onto FDB range scans.
• Round-tripping SQLite’s rowids to our PK encoding.

That’s an order of magnitude more code (~1.5k lines) and adds little to your understanding of FDB itself; the interesting parts — how does the relational model fit on top of an ordered KV store? — are all here.

Running

cd option-a-sqlite
go mod tidy
go run ./demo -cluster ../fdb.cluster

Expected output:

SELECT * FROM users;
  map[city:Paris id:1 name:Alice]
  map[city:Tokyo id:2 name:Bob]
  map[city:Paris id:3 name:Carol]

SELECT * FROM users WHERE city='Paris';
  map[city:Paris id:1 name:Alice]
  map[city:Paris id:3 name:Carol]

After UPDATE users SET city='Tokyo' WHERE id=1;
  map[city:Tokyo id:1 name:Alice]
  map[city:Tokyo id:2 name:Bob]
  map[city:Paris id:3 name:Carol]

After DELETE FROM users WHERE id=3;
  map[city:Tokyo id:1 name:Alice]
  map[city:Tokyo id:2 name:Bob]