db post: Add correction on SQLite assumption of POSIX filesystem

1 files changed, 41 insertions, 3 deletions

diff --git a/_posts/2020-08-31-the-database-i-wish-i-had.md b/_posts/2020-08-31-the-database-i-wish-i-had.md
index 00e7323..4ba1885 100644
--- a/_posts/2020-08-31-the-database-i-wish-i-had.md
+++ b/_posts/2020-08-31-the-database-i-wish-i-had.md
@@ -1,7 +1,7 @@
 ---
 title: The database I wish I had
 date: 2020-08-31
-updated_at: 2020-09-01
+updated_at: 2020-09-03
 layout: post
 lang: en
 ref: the-database-i-wish-i-had
@@ -56,11 +56,49 @@ the source of truth, and allow the application to work on top of it.
 [**SQLite**][sqlite] is a great example of that: it is a very powerful
 relational database that runs [almost anywhere][sqlite-whentouse]. What I miss
 from it that SQLite doesn't provide is the ability to run it on the browser:
-even though you could compile it to WebAssembly, it assumes a POSIX filesystem
-that would have to be emulated.
+even though you could compile it to WebAssembly, ~~it assumes a POSIX filesystem
+that would have to be emulated~~[^posix-sqlite].
 
 [sqlite]: https://sqlite.org/index.html
 [sqlite-whentouse]: https://sqlite.org/whentouse.html
+[^posix-sqlite]: It was [pointed out to me](https://news.ycombinator.com/item?id=24338881)
+    that SQLite doesn't assume the existence of a POSIX filesystem, as I wrongly
+    stated. Thanks for the correction.
+
+    This makes me consider it as a storage backend all by itself. I
+    initially considered having an SQLite storage backend as one implementation
+    of the POSIX filesystem storage API that I mentioned. My goal was to rely on
+    it so I could validate the correctness of the actual implementation, given
+    SQLite's robustness.
+
+    However it may even better to just use SQLite, and get an ACID backend
+    without recreating a big part of SQLite from scratch. In fact, both Datomic
+    and PouchDB didn't create an storage backend for themselves, they just
+    plugged on what already existed and already worked. I'm beginning to think
+    that it would be wiser to just do the same, and drop entirely the from
+    scratch implementation that I mentioned.
+
+    That's not to say that adding an IndexedDB compatibility layer to SQLite
+    would be enough to make it fit the other requirements I mention on this
+    page. SQLite still is an implementation of a relational, SQL, table-oriented
+    database. It is probably true that cherry-picking the relevant parts of
+    SQLite (like storage access, consistency, crash recovery, parser generator,
+    etc.) and leaving out the unwanted parts (SQL, tables, FTS, etc.)  would be
+    better than using of all it just to building on top of the whole SQLite
+    stack, but that's simply an optimization. Both could even coexist, if
+    desired.
+
+    SQLite would have to be treated similarly to how Datomic treats SQL
+    databases: instead of having a table for each entities, spread attributes
+    over the tables, etc., it treats SQL databases as a key-value storage so it
+    doesn't have to re-implement interacting with the disk.
+
+    The tables would contain blocks of binary data, so there isn't a difference
+    on how the SQLite storage backend behaves and how the IndexedDB storage
+    backend behaves, much like Datomic works the same regardless of the storage
+    backend, same for PouchDB.
+
+    I welcome corrections on what I said above, too.
 
 [**PouchDB**][pouchdb] is another great example: it's a full reimplementation of
 [CouchDB][couchdb] that targets JavaScript environments, mainly the browser and