I discovered something absurd recently and I’m very excited to tell you about it.
The end result is absurd-sql, and it’s a persistent backend for SQLite on the web. That means it doesn’t have to load the whole db into memory, and writes persist. In this post I will explain the absurdities of the web’s storage APIs (mainly IndexedDB), show how SQLite provides a 10x perf improvement, explain all the cool tricks that make it work, and explain the locking/transactional semantics that make it robust.
If you are writing a web app today, you’ll probably choose IndexedDB to store data. It’s the only option for something database-like that works across all browsers.
As you attempt to build a local app, you will quickly find that it’s a poor database to build an entire app around. Sure, it might be fine for small bits of functionality. But if we really want webapps to be impressive (I do), we need a more powerful way to work with data.
IndexedDB is slow. What’s even worse, in my testing Chrome, which is by the far the major browser, has the slowest implementation. Simple operations against the database take ~10ms (and I’m being charitable!), while it’s common for me to be profiling SQLite at ~.01ms. This makes a massive difference for what kinds of apps you can write against it.
You’re on your own if you want to query data in IndexedDB. The only function it provides is count, and the rest of the APIs just return a range of items. You’ll have to construct your own query functionality by wiring up indexes and structuring your data in specific ways.
Heck, you can’t even add a new “object store”, which is sort of like a table, at any point in time. You can only do it when opening the database, and doing so forces all other tabs to kill their database connection!
Maybe IDB was supposed to be low-level and you’re supposed to reach for a library to help provide better support for these features. Every library I looked at was messy and made performance even worse (one of the most popular “fast” ones I looked at took ~45ms to just get one item!?).
I say abstract away the whole thing. I have something for you that you should reach for next time instead of all those other libraries. And it’s going to massively improve your life as a developer.
SQL is a great way to build apps. Especially small local web apps. Key/value stores may have their place in large distributed systems, but wow wouldn’t it be great if we could use SQLite on the web?
I’m excited to announce absurd-sql which makes this possible. absurd-sql is a filesystem backend for sql.js that allows SQLite to read/write from IndexedDB in small blocks, just like it would a disk. I ported my app to use and you can try it here.
This whole situation, and how well this project ended up working out, really is absurd. Why? In all browsers except Chrome, IndexedDB is implemented using SQLite. Anyway…
A huge thanks to phiresky writing the article Hosting SQLite databases on Github Pages which inspired me to do this. It’s a very clever technique and it gave me the idea to research this.
sql.js is already a great project for using SQLite on the web. It compiles SQLite to WebAssembly, and lets you read databases and run queries. The major problem is that you can’t persist any writes. It loads the entire db into memory, and only changes the in-memory data. Once you refresh the page, all your changes are lost.
While in-memory databases have their uses, it kneecaps SQLite into something far less useful. To build any kind of app with it, we need the ability to write and persist.
absurd-sql solves this, and it works by intercepting read/write requests from SQLite and fetching and persisting them into IndexedDB (or any other persistent backend). I wrote a whole filesystem layer that is aware of how SQLite reads and writes blocks, and it efficiently performs the operations correctly.
What this means is it never loads the database into memory because it only loads whatever SQLite asks for, and writes always persist.
We use sql.js because it already has a large community and is by far the most common way to use SQL on the web. Now, all you have to do is install absurd-sql and add some lines of code to hook it up. It looks like this:
import initSqlJs from '@jlongster/sql.js';import { SQLiteFS } from 'absurd-sql';import IndexedDBBackend from 'absurd-sql/dist/indexeddb-backend';SQL = await initSqlJs();sqlFS = new SQLiteFS(SQL.FS, new IndexedDBBackend());// This is temporary for nowSQL.register_for_idb(sqlFS);SQL.FS.mkdir('/sql');SQL.FS.mount(sqlFS, {}, '/sql');let db = new SQL.Database('/sql/db.sqlite', { filename: true });