The next-gen SQLite won't look like SQLite

next-gen-sqlite.md

SQLite was thought of as a toy for many years until we all realized it was actually nice to use a database as a library, even server-side. And yet...

1. It's a big dependency, despite what you think. In no universe is 150,000 lines of C code "lightweight".
2. Like with all SQL databases, you have an impedance mismatch: the way you store data in memory is completely different than the table structure a SQL database forces on you, leading to tons of glue code.
3. It's mutable, so it can't undo or time travel, and it can't read while a write is happening (even in WAL mode...reads can't happen during checkpointing!).

To solve these problems, I'm building a database called xitdb. It has ports for several languages:

• Zig: radarroark/xitdb
• Java: radarroark/xitdb-java
• Clojure: codeboost/xitdb-clj
• TypeScript: radarroark/xitdb-ts

Looking again at the above-mentioned problems:

1. At only about 3,000 lines of code with no dependencies, xitdb is actually lightweight. You could reimplement it in another language pretty easily.
2. It exposes rich data structures rather than a SQL interface, so the interface is very similar to how you would naturally organize data in memory.
3. It is immutable, so all past copies of the database are queryable and can be reverted back to.

Data structures, not SQL

In xitdb there is no built-in query language. Instead, it works just like in-memory data structures: you are given a HashMap, ArrayList, and so on. It reads/writes this data incrementally, though, so you can work with larger-than-memory databases.

For example, if I want to save the equivalent of this:

{"location": "New York",
 "people": [
   {"name": "Alice", "age": 25},
   {"name": "Bob", "age": 42}
 ]}

Here it is in Java:

var moment = new WriteHashMap(cursor);

moment.put("location", new Database.Bytes("New York"));

var people = new WriteArrayList(moment.putCursor("people"));

var alice = new WriteHashMap(people.appendCursor());
alice.put("name", new Database.Bytes("Alice"));
alice.put("age", new Database.Uint(25));

var bob = new WriteHashMap(people.appendCursor());
bob.put("name", new Database.Bytes("Bob"));
bob.put("age", new Database.Uint(42));

...and in Zig:

const moment = try DB.HashMap(.read_write).init(cursor);

try moment.put(hashInt("location"), .{ .bytes = "New York" });

const people = try DB.ArrayList(.read_write).init(try moment.putCursor(hashInt("people")));

const alice = try DB.HashMap(.read_write).init(try people.appendCursor());
try alice.put(hashInt("name"), .{ .bytes = "Alice" });
try alice.put(hashInt("age"), .{ .uint = 25 });

const bob = try DB.HashMap(.read_write).init(try people.appendCursor());
try bob.put(hashInt("name"), .{ .bytes = "Bob" });
try bob.put(hashInt("age"), .{ .uint = 42 });

...and in Clojure:

(swap! db (fn [moment]
            (assoc moment
                   :location "New York"
                   :people [{:name "Alice" :age 25}
                            {:name "Bob" :age 42}])))

You can put a query language on top of xitdb if you want. Here's an example with the Clojure version. The point isn't that query languages are bad, but that they shouldn't be built-in. Just give me data structures! Any higher-level data model should be added by a separate library.

Immutability

Similar to Datomic, xitdb is completely immutable. You can query the database from any point in the past, and revert to it if desired. Here it is in Java:

// the list of all transactions made in the database
var history = new WriteArrayList(db.rootCursor());

// save the current index so we can revert back to it
var historyIndex = history.count() - 1;

// ...do some new transactions here

// read the old copy of the db without the new transactions
var moment = new ReadHashMap(history.getCursor(historyIndex));

// revert back to the old copy of the db
history.append(history.getSlot(historyIndex));

Even if you don't care about time travel, one big side benefit is that you can safely read while new data is being written. This is just like in-memory immutable data: it's always safe to read from different threads. This is a big deal especially when using it server-side.

Conclusion

The next-gen SQLite will expose data structures rather than SQL, and will be immutable. Neither of these concepts are commonplace in databases today, especially in embedded databases, yet they solve problems that would be dramatically more complex to solve on existing mutable databases.