For an internal project, I recently implemented a SQLite-like DB in pure OCaml for use within Mirage. Please find the repo here. Please note this is AI generated code - I designed and set up the test harness (Jepsen + SQLite parity) but didn’t write any of the code.
Beyond standard relational tables, there’s fledgling support for column oriented tables to use in OLAP type queries. While already fairly capable, I expect this will see more commits as I make progress on the larger project.
The larger goal is a full-fledged IoT based workflow framework I am building for use at my company, IoTReady.com. Our current framework is JS based and uses SQLite + Parquet as DBs. I wanted something similar but suitable for running within Mirage to meet some security and isolation density requirements. That framework is WIP and may not be released publicly.
Hope this is useful to someone else too. Feel free to file feature requests and issues.
It’s a regret that the AI isn’t able to keep up with and chat to some of the people behind Mirage. It would probably have known that our cooperative has been using mkernel to create unikernels for a few months now. This was announced here and there’s even a tutorial.
I did see mkernel and may yet move our application framework there eventually. I have Solo5 running on one of our instances for evaluation of the base. In response to the sarcasm in your response, I have been playing with Mirage for about 6 years now so I am more familiar with it and this, i.e. granary, is a nascent project.
Only related to SQLite in spirit - i.e. this is an in-process DB and writes to a file on disk. This tries to maintain compatibility with SQLite via the mined test corpus - but the on-disk format is completely rewritten and I chose to enforce types for columns (SQLite does not).
I chose to build in a few additional things I frequently end up reaching for - a more capable REPL and the built-in OLAP. Our use cases need replication so Granary handles Litestream style replication out of the box.
I have retitled to make the connection more obvious.
FWIW there have been previous experiments to use SQLite directly on Mirage. SQLite usually expects a filesystem, but it also has a “one file” VFS which you can use directly on a block device The SQLite OS Interface or "VFS"
Since then we now have mfat implementing FAT32, and it could be interesting to see a SQLite VFS using that filesystem.
I was familiar with the SQLite VFS but not with mfat. That combination could work and I will try and experiment. I am still moving the CI and wiki from our internal Forgejo to GitHub but Granary is about 3x slower than SQLite at the moment.
For our needs, I wanted something I could run on Xen like hosts rather than Unix like. To avoid any libc dependencies, I felt, rightly or wrongly, a pure Ocaml write would be cleaner. I have seen Robur’s notes on preferring Solo5/*nix hosts but am some distance from settling on that front.
Really nice to see this experiment in agentic programming! I’m curious about your test setup; can you share more about the Jepsen testing loop here? I’m wondering whether Jepsen could be used to guide an agent towards writing a crowbar (or other) property based fuzz testing code so that it could then be fuzzed independently.
What hardware targets are you looking at? Xen is pretty good on most arm64 backends these days, as long as you use a Linux dom0. I’ve not tried for a few years, but stub domains remain difficult to manually setup. This may have changed with the advent of agents though; I’d love to hear if that’s the case!
I run Jepsen as a nightly CI job alongside some benchmarking and cross-architecture builds to check for regressions. I am trying to move the CI to Github. Will ping here when done.
Jepsen is a bit tricky to move because I have been using some Podman + LazyFS hacks thus far to run within CI. Jepsen was definitely a lot of fun and quite a relief to get past. To guide the agents, I looked at a few more tools like dead_code_analyser and bisect_ppx but neither seems compatible with OCaml 5.4 yet? I have qcheck-core integrated in the tests as well.
Yeah, quite familiar with your work on Xen and I have been itching for an excuse to go down to hypservisor level for a while! The CI builds non-mirage amd64, arm64, mirage-linux and mirage-hvt. My needs are amd64 for centralised deployments and arm64 for field. But again, this is about 3-6 months out from the first field trials so it’s hard to speculate.
I got a bit put off by the Linux dom0 requirement as I would prefer not having it from a purity standpoint but didn’t get much further on the experiments. We have a few bare metal instances in the office we experiment with and I will see what’s possible.
The last coding I did in OCaml in anger was a few years ago. My day job involves a lot of code but not a lot of coding any longer.
I have wanted to move my org and our customers to Unikernels since about 2020 as I think we are only at the tip of the iceberg in terms of CVEs and zero days.
As a bootstrapped venture, agents are what enabled us to put resources behind this effort and build something tangible. We understand the problem domain and the shape of the solution so, with the right harnesses, agents are fantastic at building the tools. OCaml turned out to be a great fit too - the compile-fix loop is pretty fast.
As suspected, Jepsen doesn’t travel to Github CI well. But manual runs work well - documented here. I will try and figure out how to connect Github CI to our internal runners.
Thanks for sharing. I’ve not much experience with databases, but I’m curious about:
What was the motivation behind that? In my experience, keeping the on-disk format as existing tooling enables a lot of testing and also avoids a “lock-in” – since you can then easily use sqlite tooling to modify, inspect, recover the data.
(Of course, there are always valid reasons for using a custom on-disk format – as said, it’s curiosity.)
Primary motivation was design freedom. Granary started life as an effectuation experiment in pushing boundaries and understand what AI agents were capable of, while potentially unlocking some use cases for us. SQLite parity (via the tests) was a guiding principle to ensure the agents or I were not doing something very stupid.
If one squints hard enough, a few end results justify the choice:
Time-travel because writes use copy-on-write and there’s a flag that retains old pages (CoW comes with a performance penalty though).
MVCC isolation to allow multiple readers, and replication, without additional .wal or .shm files
Pages are self-describing allowing some nice table level monitoring in the REPL as well as new page IDs for specific use cases which then hide it from regular views: e.g. the columnar store and full text search.
You can trade-off durability vs performance a bit more easily by controlling a synchronous PRAGMA that allows you to batch commits.
That said, there’s a different experiment in trying to maintain .sqlite parity. You would need to rewrite lib/store, row.ml , the catalog and possibly a few other things. The SQL layer, REPL etc can stay as-is. Type affinity vs strict typing is another trade-off. Not something I need but if you have a few free credits or a generous usage limit, well worth the attempt.
I think that the VFS + mfat experiment mentioned by @reynir is another worth exploring. That would then retain most of SQLite code and only need a thin VFS shim.
But if your goal to get rid of the C code, why would you want the complexity of a full filesystem (even FAT) for just a couple of files? It would be easier to just use ocaml-qcow or a volume manager to just allocate extents for a few files.
Worth experimenting from a comparative and academic experiment perspective and as a general SQLite in OCaml, “what if”, exploration. If and when I do, I will add results to the comparative benchmarks.
My web framework depends deeply on some Granary features such as reactive views and is quite far along so I am unlikely to change path right now.
Thanks for sharing the paper, I have a copy in my notes from 2020 but haven’t read in a while and didn’t make the connection! But the idea that a simple web app shouldn’t load the world with it stayed with me.
I’ve been looking at having a pure OCaml version of sqlite running on top of solo5 as well (as I wanted to see how much of a performance hit that’ll incur) for a while and it’s actually quite practical/performant. For most operations, I managed to cover a large part of the API with really decent performance (I need better benchmarks before claiming this properly - if you are aware of any interesting/relevant SQL* benchmarks to use please share!).
I followed the route of full binary compatibility so testing with existing CLI tools could be possible. You can see some of those experiments there:
Warning: it’s still an early experiment (with some big holes) and will probably change a lot but I’m using it in a few projects already and I’m fixing bugs along the way. Naybe you could find some of it useful to make Granary faster/more portable? I’ve tried to keep a pure core in all of these and I’m using eio as a thin IO layer (when possible). Happy to discuss extensions if that could help you!
Thanks for sharing! Let me pour over these this weekend and come back to you. I have a bench.yml that might be useful and some slightly outdated numbers.
I am yet to invest heavily into benchmarking or optimisations as my first goal was ‘within 10x’ and I got there a while ago.
bisect_ppx seems stuck at 5.3 or earlier, so I’m mostly dropping it from my dependency cones now (I’m most grateful to the maintainers for the years I did use it, but in general I try to avoid ppx dependencies unless necessary these days).
On the dead code front, I’ve been trying out @samoht’s (unreleased but dependency free so I found it easy to install) gazagnaire.org/merlint at main · Tangled as a highly opinionated linter that drives agents very well. This has been great at only eliminating dead code (usually introduced by the aforementioned agent), but also for avoiding too much cyclomatic complexity and triggering a refactoring.