SQLite as an Application File Format

I did this for MBTiles, for storing (at the time, raster) map tiles at Mapbox. I was working on the iPad wing of R&D early in the company and we were focusing on offline mapping for the iPad. Problem was, moving lots of tiny map tiles (generally 256px square PNGs) was tedious over USB and network. We had a thing called Maps on a Stick for moving things around by USB, but it just didn’t scale well to the iPad interface & file transfer needs.

Bundled the tiles into SQLite (I was inspired by seeing Dr. Hipp speak at a conference) and voila, things both easy to move and to checksum. Tiles were identified by X & Y offset at a given (Z)oom level, which made for super easy indexing in a relational DB like SQLite. On the iPad, it was then easy to give map bundles an application icon, associated datatype from file extension, metadata in a table, etc. At the time, I was fairly intimidated by the idea of creating a file format, but databases, I knew. And then making some CLI tools for working with the files in any language was trivial after that.

SQLite is abolutely amazing as an app format! I couldn't list how many tools are available to read SQLite data, or how easy and friendly they are. Even its CLI does wonders when you're dealing with data with it. SQLite has been around for 20+ years and is one of the most heavily tested softwares in the world.

SQLite is very simple, yet very reliable and powerful. Using SQLite as file format might be the best decision an engineer can take when it comes to future-proofing preservation of data.

I think I use SQLite like that (to some extent):

- https://github.com/rumca-js/Internet-Places-Database

For UI I use HTML, because it already provides components with bootrap, and everybody can use it without installation of any software.

All data comes from a single SQLite that is easy read, and returns data.

My database is really big, so it takes time to browse it, I wanted to provide more meaningful way to limit scope of searching

Actually used it for a desktop blogging app a few years ago. It was great! I could set up a blog skeleton, send the file to a family member. They could focus on writing content and hitting deploy.

https://blog.project-daily.com/pages/file-format_3705.html

Sold. Absolutely.

I always wonder when people can sell ideas or products so effectively.

Previously: https://news.ycombinator.com/item?id=23508923

This approach has really helped me out in my work. I do something very similar using DuckDB to slurp output files anytime I write a custom hierarchical model. The single sql queryable file simplified my storage and analytics pipeline. I imagine SQLite would be especially ideal where long term data preservation is critical.

I remember when I was a child I used to open WinRAR and try to open random files in games and programs to find some „hidden” assets.

Most application's file formats are structured as a tree, not as flat tables. If your application's data is flat tables or name-value pairs then SQLite is an obvious choice. But if it is tree structured then it is less obvious. You can still save your tree in JSON format as a blob in a SQLite table but in this case the benefits are fewer. But if in addition to the JSON you have images or other binary data then once again SQLite offers benefits, because each of those binary files can be additional rows in the SQLite table. This is far easier to handle than storing them in ZIP format.

We are developing using sqlite to transfer configurations from uat to production environment. Since the configurations are already saved in a postgres table in uat, moving some configs from uat to production an sqlite file is very easy. since it's a binary format, we are also saved from any inadvertent edits by people doing production deployment.

Also, another usecase is to export data from production to uat for testing some scenarios, it can be easily encoded in a sqlite file.

The Acorn macOS app uses SQLite in a similar way: https://flyingmeat.com/acorn/docs/technotes/ACTN002.html

Something to consider when using SQLite as a file format is compression (correct me if I'm wrong!). You might end up with a large file unless you consider this, and can't/won't just gz the entire db. Nothing is compressed by default.

There seems to be no single software solution "out there" for mounting an SQLite DB (or an SQLite archive) as a file system, with or without per-record relative paths.

Bit unrelated rant but I'm still not sure why ZIP has been adopted as an Application File Format rather than anything else. It is a remanent of a DOS era with questionable choices, why would you pick it over anything else?

I see no downside in using sqlite as an application file format.

I remember someone mentioning the Acorn image editor on Mac uses sql files to store image data. It probably makes backwards compatibility much easier to work with.

Searched for this topic:

> and is backwards compatible to its inception in 2004 and which promises to continue to be compatible in decades to come.

That is pretty amazing. You could do a lot worse.

i am taking it to new new extreme > https://github.com/blue-monads/potatoverse

Somehow my first thought from the title was using sqlite as a format for applications. So like a replacement for ELF. I think this idea is both fascinating and horrifying.