Using SQLite as storage for web server static content

I did some experiments around this idea a few years ago, partly inspired by the "35% Faster Than The Filesystem" article: https://www.sqlite.org/fasterthanfs.html

Here are the notes I made at the time: https://simonwillison.net/2020/Jul/30/fun-binary-data-and-sq...

I built https://datasette.io/plugins/datasette-media as a plugin for serving static files from SQLite via Datasette and it works fine, but honestly I've not used it much since I built it.

A related concept is using SQLite to serve map tiles - this plugin https://datasette.io/plugins/datasette-tiles does that, using the MBTiles format which it turns out is a SQLite database full of PNGs.

If you want to experiment with SQLite to serve files you may find my "sqlite-utils insert-files" CLI tool useful for bootstrapping the database: https://sqlite-utils.datasette.io/en/stable/cli.html#inserti...

> Transactional Updates : This is the main benefit. Updating multiple files can be done in one transaction. Isolation ensures that there are no broken webapps during the update.

Your server on its own, whether it uses SQLite or the filesystem, cannot save you from having broken webapps during an update. Each page in the browser is a _tree_ of resources, which are fetched with separate HTTP requests and therefore not subject to your server-side transaction/atomic update system. You can change all resources transactionally on the server side but the browser can still observe a combination of old and new resources.

The common solution here is to ensure that all sub-resources of a page (javascript bundle(s), stylesheet(s), media, etc) are named (ie, in the URL) using a content hash or version. All resources (from the root HTML document down) need to refer to the specific content-hash or versioned name, so that if the browser loads version X of the root HTML document then it will load the _corresponding version_ of all sub-resources. Not only that, but if you're updating a page from version X to version Y, all the version-X sub-resources need to remain available after you start serving page version Y, until you're confident that no browser could reasonably be still loading version X of the page, otherwise you can break page version X while someone is still loading it.

This means you actually specifically don't want to put sub-resources for a page into one atomically-switched-out bundle along with the root HTML document, because if you do that you'll be removing the previous versions of sub-resources while they may still be referenced.

Also of course in some cases there might be some sub-resources (e.g., perhaps some media files) that you want to be versioned "separately" from the HTML document that contains them, so that you can update them without busting caches for all of the page/app structural elements like your javascript blobs and stylesheets and so on, and you _might_ need to take that into account in your page build system as well.

Back in 2011/2012 I worked for a small game development company and at my recommendation we moved all our assets under 100kb into an sqlite3 db, and then made a "pak file" and stored offsets to said files inside the sqlite3 db (and this was done because there was a post-mortem talk given by Richard Hipp where he said in hindsight he'd wish blobs had been treated more like an inode, where they were an offset further into the database and blobs were just appended to the file)

It was hella fast how quickly we could load our assets (this was for a mobile game so only a handful of assets were not in the db). It's been neat seeing people adopt it further.

One aspect someone might also not realize is you can store near infinite metadata alongside your content, so you can always just query the database to find "similar" files. We threw so much metadata into the DB and I think in the end the pak file was 200MB, and the database was something like 20MB. Again, this was a mobile game. I think the worst thing we had happen on the client side was a single double inner join that we couldn't figure out how to reduce due to some server side complexity (we weren't able to implement the server, which was frustrating as the people we worked with were very bad™ at software development so our builds would break out of nowhere when they would change the entire backend spec without alerting us )

We also used a separate sqlite3 database for game replays so you could (post match completion) replay an entire game and see what each opponent was doing during said game. This was super nice for automated testing as well.

We also came around to dumping files into SQLite instead of dealing with the filesystem (and git) for the lix change control system. This article touches on problems we encountered: https://opral.substack.com/i/150054233/breaking-git-compatib....

- File locking, concurrency, etc., are problems that SQLite solves

- Using SQLite allows drumroll querying files with SQL instead of platform-dependent fs APIs

- Using SQL queries is typesafe with Kysely https://kysely.dev/ (without the need for an ORM)

Amusingly, my static site generator CMS does precisely the reverse of what's done here. During development/updating of the website, all pages and posts are entries in an SQLite database that is manipulated through a web interface that present an editable version of the website. And then the website is dumped as static pages to the file system to be deployed directly and/or downloaded as a zip and uploaded anywhere else for deployment (including entirely static hosting services).

I do a lot of high performance scientific computing and find the most flexible and high performance way to access data- especially in parallel- is often a read only sqlite database on a ramdisk. It feels super hacky, but it is easier to setup and faster than anything else I’ve found.

From Appropriate Uses For SQLite - https://www.sqlite.org/whentouse.html :

> The amount of web traffic that SQLite can handle depends on how heavily the website uses its database. Generally speaking, any site that gets fewer than 100K hits/day should work fine with SQLite. The 100K hits/day figure is a conservative estimate, not a hard upper bound. SQLite has been demonstrated to work with 10 times that amount of traffic ... The SQLite website (https://www.sqlite.org/) uses SQLite itself, of course, and as of this writing (2015) it handles about 400K to 500K HTTP requests per day, about 15-20% of which are dynamic pages touching the database. Dynamic content uses about 200 SQL statements per webpage. This setup runs on a single VM that shares a physical server with 23 others and yet still keeps the load average below 0.1 most of the time. See also: https://news.ycombinator.com/item?id=33975635

> Why this approach is not more common?

Because file system excels at handling files. Need an atomic update? Checkout into a new directory and switch a symlink.

I've seen various versions of using a database as a filesystem. They have a nice side to them and then a nightmare side to them when the shit hits the fan.

> When updating an app, since there could be lots of files being updated, using a database would allow all changes to be done atomically in a transaction. This would prevent broken web pages from being served during a version change.

but....the SQLite file is locked against reads while writing in order to achieve serializable isolation. which sort of indicates you're better off doing your database work in an offline file, then just swapping the new file for the old one that's in production. which sort of indicates....just use a tar file, or a separate directory that you swap in to update the new content.

it is so much easier to serve static files statically rather than from some program that's trying to manage live SQLite connections and achieve some weird "update concurrency" magic when this problem is not at all that hard to solve. It's fine to manage your CMS in a sqlite database but when you serve the content live and it's static, use static files.

Genuinely, I'm curious to hear from OP if you explored using filesystems that can provide de-duplication, snapshots, versioning, and compression and compared that to the SQLite approach. It would be interesting to get your take on the comparative benefits of SQLite compared with an approach that uses a more advanced filesystem like ZFS or btrfs. Once you have a filesystem that can de-dup, atomic changes are more or less a matter of building up a directory with the new version first and then swapping one directory for another with a rename. Though I assume there may be a small race condition in between the two directory rename calls. I don't know if there's a standard call to tell the filesystem "mv current old && mv new current" in one shot.

I don't get argumentation, swapping "current" symlink to point to another version worked for years as atomic way to swap 2 site versions

using sqlite for static content might have other benefits, but i don't think that this is the main one

This gives me an idea.

Use git to manage versions of the static files.

Use [git worktree][1] to have multiple separate working trees in different directories.

Use [openat()][2] in your app whenever serving a request for a static file. On each request, open the directory, i.e. `/var/www/mysite.com`, and then use `openat()` with the directory's file descriptor when looking up any files under that directory.

`/var/www/mysite.com` is a symbolic link to some working tree.

When modifying the site, make modifications to the git repository. Then check out the modified working tree into a new directory.

Create a symbolic link to the new directory and `mv` (rename) it to `/var/www/mysite.com`.

Then some time later, `rm -r` the old working tree.

[1]: https://git-scm.com/docs/git-worktree

[2]: https://pubs.opengroup.org/onlinepubs/9799919799/functions/o...

I'm continually going through this thought process:

1. I should make blog engine using flat files. That way I can diff the plain text.

2. But flat files are hard to read/write structured data from, so I should use SQLite instead.

3. But SQLite is hard to diff. GOTO 1.

This is interesting as a toy and I'm sure the authors are having a lot of fun implementing their idea.

On the other hand, the state of art when it comes to performance is zero-copy from disk to NIC using io_uring and DMA, combined with NICs that support TLS & checksumming offloading and separated header and payload that can be filled independently by the OS and user-space.

I wonder if the authors of these projects ask themselves: what reasons are there to not do it like this? This thread has a few answers.

I read "There is no equivalent implementation using the file system to compare against, so a direct benchmark test is not done." But Btrfs and ZFS have file versioning built-in, not just snapshots, but also individual files - this can be used to make a similar setup, without using binary blobs, but just the filesystem references in sqlite or hard links - which might even perform faster.

So the entire argument here is that using sqlite to store files prevents visitors from seeing partial/mid-way changes when updating a website (a process that would take seconds)? Is that really a problem?

Otherwise the file system is far superior for every "benefit" they list. And far easier to work with other software and more robust over time (nothing to change/break).

Isn't there bottleneck at your egress - I thought sqlite was fast at reads but only handles one read at a time - so if someone is downloading a gigabyte binary out of your db every other connection just has to wait - am I wrong?

Something I want to try is using sqlite as my filesystem but just storing content hashes that would point to an S3-compatible object store, so you get the atomicity and rollbackability and all but you also get massive parallelization and multiregionality of an object store

Edit: I googled it again and find that multiple processes can read sqlite concurrently so shouldn't be a problem

https://stackoverflow.com/questions/4060772/sqlite-concurren...

While I like the idea of using sqlite, how to avoid downtime? Do you host the file on NFS or something?

If it is static content why have a database at all?

My SRE thought, sure, there might be some benefits for using SQLite but massive downside is you cannot easily examine what server is serving up.

So let's go over the claims about SQLite:

>Transactional Updates : This is the main benefit. Updating multiple files can be done in one transaction. Isolation ensures that there are no broken webapps during the update.

Yep, big one

>Deployment Rollbacks: Another of the transactional benefits is the ability to roll back deployment in case of errors. If multiple apps are being updated, all of them can be rolled back in one go. Rolling back a database transaction is much easier than cleaning up files on the file system.

I guess if you don't keep old artifact of what you are serving up. We zip up our entire static site as part of pipeline and dump into Azure Storage before deployment.

>File De-duplication Across Versions: Clace automatically versions all updates. This can lead to lots of duplicate files. The file data is stored in a table with the schema

INTERNAL SCREAMING Deduplication gives me nightmares from cache issues and so forth. Show me SQLite level of tests here to save a few kilobytes.

>De-duplication Across Apps : Each production app in Clace has an staging app. Apps can have multiple previews apps. This can lead to lots of duplication of files. Using the database helps avoid all the duplication. Even across apps, there are files which have the same contents. Files are de-duplicated across apps also.

INTERNAL SCREAMING Oh god, I hope this section of the code is extremely battle tested because de-duplication at this level gives me SRE nightmares.

>Easy Backups: Using SQLite means that backups are easy. The state of the whole system, metadata and files can be backed up easily using SQLite backup tools like Litestream.

tar directory is difficult?

>Content Hashing: For content caching on the browser, web servers return a ETag header. Using the database for files makes it easy to save the content SHA once during file upload without having to recompute it later.

Most web servers handle this for you.

>Compression: The file contents are saved Brotli compressed in the SQLite table. The database approach has the advantage that contents can be saved in multiple formats easily. GZip compressed data and uncompressed data can be added by just adding a column in the files table.

Cool, now you have to uncompress it every time you want to serve it? Is there massive shortage on disk space and I missed the memo?

I mean, I don't know what Clace does, seriously OP, reading your front page and I have no idea but disk space/speed is rarely a factor. Hell, most stuff probably gets loaded into RAM and is served from there at the end. Meanwhile, your content is being served out this proprietary file system and you are kind of locked in.