SQLite On The Web: Absurd-sql

Love it or hate it, the capabilities of your modern web browser continuously grow in strange and wild ways. The ability for web apps to work offline requires a persistent local storage solution and for many, IndexedDB is the only choice as it works across most browsers and provides a database-like interface. However, as [James Long] found, IndexedDB is painfully slow on chrome and limited in querying ability. He set out to bring a tool he was familiar with, SQLite, and bring it to the web browser as absurd-sql.

Why absurd? Partially because most browsers (not chrome) implement IndexedDB on top of SQLite. So for many browsers, it is just SQLite on top of IndexedDB on top of SQLite. Luckily for [James] there already was a project known as sql.js that uses emscripten to compile the C-based SQLite into WebAssembly. However, sql.js uses an in-memory storage backing and all data is lost when refreshing the page. [James] tweaked SQLite’s method of reading and writing blocks. Instead of being memory backed, he added a layer to read and write blocks from IndexedDB. This means that only sections of the database need to be read in, bringing in huge performance gains.

a graph showing absurd-sql beating IndexDB on every benchmarkThat brings us to the other reason why it’s absurd. On chrome (as well as Firefox), absurd-sql beats IndexedDB on almost every benchmark. A query like SELECT SUM(*) FROM kv led to stunning results.

So what’s the downside? Other than a somewhat large WebAssembly file that needs to get downloaded (409KB) and cached, there really isn’t. Of course, it’s not all roses when it comes to web development. Native SQLite runs 2-3 times faster than absurd-sql, which demonstrates how slow IndexedDB really is.

There are other storage standards on the horizon for web browsers, but locking becomes an issue. SQLite expects synchronous reads and writes because it’s just simple C. IndexedDB and other storage solutions are asynchronous as the event loop of Javascript lends itself well to that model. Absurd-sql gets around that by creating a SharedArrayBuffer that is shared with a worker process. The atomics API is used to communicate with the buffer. In particular, atomics.wait() allows the worker to block main thread execution until the read or write has finished. From the perspective of SQLite, the operations are synchronous. IndexedDB provides transactions so multiple connections can happen (for example multiple tabs open). Multiple readonly transactions can occur in parallel but only one readwrite transaction can be in flight.

Why not pull up your browser and start playing around with it? You’re already doomed to learn WebAssembly anyway.

12 thoughts on “SQLite On The Web: Absurd-sql

  1. Pretty much every web language is a dumpster fire. HTML is the only one I bother with anymore, and I don’t even bother with much of it. I stick to a handful of tags. I like the novel without illustrations look far more than the endless source of bugs and soviet/chinese hacking attempts that has become serving any interactive website these days.

    I’m happy to use websites that others have to maintain, but for me, it’s 644 all the way.

      1. That site seems to be badly broken and doesn’t really demonstrate anything. Pages under 100KB isn’t a difficult feat so long as you aren’t using something generated by an editor.

      2. I really liked the original idea behind HTML that you specify simple semantic information (headings, emphases, paragraph breaks, links, etc.), and the details of formatting was the job of the client side browser, so the user gets to control how everything looks. This resulted in much smaller pages, too.

  2. Huh. I’m finding out about this immediately after building a SQLite database, and immediately before building some kind of web interface for it. Timing!

    (I’ll probably still just whack something together in PHP, but…)

  3. I paused at the point when [James Long] says this: “Let’s hope a new storage API comes along because it’s worth mentioning that it’s not guaranteed that your IndexedDB data will live forever. Supposedly browsers may delete your IndexedDB database under certain conditions.” How is that possible? [James Long] says nothing more on the subject.

Leave a Reply

Please be kind and respectful to help make the comments section excellent. (Comment Policy)

This site uses Akismet to reduce spam. Learn how your comment data is processed.