HN Reader

If you're going to use SQLite as an application file format, you should:

1. Enable the secure_delete pragma <https://antonz.org/sqlite-secure-delete/> so that when your user deletes something, the data is actually erased. Otherwise, when a user shares one of your application's files with someone else, the recipient could recover information that the sender thought they had deleted.

2. Enable the options described at <https://www.sqlite.org/security.html#untrusted_sqlite_databa...> under "Untrusted SQLite Database Files" to make it safer to open files from untrusted sources. No one wants to get pwned when they open an email attachment.

3. Be aware that when it comes to handling security vulnerabilities, the SQLite developers consider this use case to be niche ("few real-world applications" open SQLite database files from untrusted sources, they say) and they seem to get annoyed that people run fuzzers against SQLite, even though application file formats should definitely be fuzzed. https://www.sqlite.org/cves.html

They fail to mention any of this on their marketing pages about how you should use SQLite as an application file format.

wouldn’t an XML database be easier?

> it is still bothersome that changing a single character in a 50 megabyte presentation causes one to burn through 50 megabytes of the finite write life on the SSD.

I used to worry a lot about this but it has never once actually come up for me. 50 megabytes is a pretty extreme example, but even so if you edit this document fewer than several million times it won't matter.

Serializing the object graph all over again can be way faster than mapping into a tabular model. There are JSON serializers that can push multiple gigabytes per second per core. It might even be the case that, once you factor in the SSD controller quirks, the tabular updates could cause more blocks to be written than just dumping a big fat json stream all at once.

SQLite can't be reliably used in networked file systems because it heavily relies on locking to be correctly implemented. I recently had to add a check for such file systems in my application [1] because I noticed a related corruption firsthand. Simpler file formats do not demand such requirements. SQLite is certainly good, but not for this use.

[1] https://github.com/lifthrasiir/angel/commit/50a15e703ef2c1af...

One thing I would call out, if you use SQLite as an application format:

BLOB type is limited to 2GiB in size (int32). Depending on your use cases, that might seem high, or not.

People would argue that if you store that much of binary data in a SQLite database, it is not really appropriate. But, application format usually has this requirement to bundle large binary data in one nice file, rather than many files that you need to copy together to make it work.

Dr. Hipp occasionally gets on a soapbox and extolls the virtue of sqlite databases for use as an application file format. He also preaches about the superiority of Fossil over Git. His arguments generally make sense. I tolerate his sermons because he is one of the truly great software developers of our time, and a personal hero of mine.

Previous discussions, 2 and 5 years ago: https://hn.algolia.com/?query=https%3A%2F%2Fwww.sqlite.org%2...

Didn’t Apple actually move to SQLite for their Pages/Numbers format? I remember reading years ago that it was rocky (the transition), but was maybe eventually smoothed out?

The fundamental problem in my mind is the mixing of binary and text content. An optimal solution would separate them, allowing systems like Git do the versioning. But separating the tightly coupled parts into own files would also be annoying sharing/management wise.

Base64:ing the images into strings, like one could do with html, would probably not be ideal for compression. As a matter of fact, text-files as such would not be ideal compression-wise.

So I suppose if binary-format cant be avoided, SQLite would be as good as any other compression format. But without built-in collaboration protocol support, like CRDT, with history truncation (and diverged histories can always fall back to diff) I dont think it'd be good enough to justify the migration.

If I remember correctly Mendix project file format is simply a sqlite db. I thought the designer was lazy but it turns out it's a reasonable decision.

Recently, DuckDB team raise similar question on DataLake catalog format. Why not just use SQL database for that ? It's simpler and more efficient as well.

Interesting read! I find the idea to use SQL queries to get only the relevant data quite convincing. I do wonder how this would work in practice though. Any changes the user makes would have to be inserted with SQL to allow for the new data to be included in SQL queries, but users also expect to be able to make changes and then not save them (or save them into a different file).

Should one make a massive transaction that is only committed when saving? It is possible to commit such a transaction to a different file when using Save As?

Or maybe for editing one would need to copy the file to a separate temporary location, constantly commit to that file, and when saving move the temporary file over the original file (this way we aren't losing the resilience against corruption SQLite offers).

Or is there a better way to do this? I don't like storing pending changes into the original file since it kinda goes against how users expect files to work (and could cause them to accidentally leak data).

What if instead of API's for data sets, we simply placed a sqlite file onto a web server as a static asset, so you could just periodically do a GET and have a local copy.

An interesting skim, but it would have been more meaningful if it had tackled text documents or spreadsheets to show what additional functionality would be enabled with those beyond "versioning".

Maybe it's just me, but I see the presentation functionality as one of the less used aspects of the OpenOffice family.

I've being trying out SQLite for a side project of mine, a virtual whiteboard, I haven't quite got my head around it, but it seems to be much less of a bother than interacting with file system APIs so far. The problem I haven't really solved is how sync and maybe collaboration is going to interact with it, so far I have:

1. Plaintext format (JSON or similar) or SQLite dump files versioned by git

2. Some sort of modern local first CRDT thing (Turso, libsql, Electric SQL)

3. Server/Client architecture that can also be run locally

Has anyone had any success in this department?

Anki's storage format is SQLite (or was a few years ago). That made it really lovely when I wanted to import the contents (including the view logs) of Anki deck I'd been using for a decade into a custom system I was designing. Just pop up the `sqlite3` REPL, poke around and see what it looks like, then write standard SQL queries to get the data out.

> The use of a ZIP archive to encapsulate XML files plus resources is an elegant approach to an application file format. It is clearly superior to a custom binary file format.

Can anyone expand on this? Why would it be better than a binary format?

I was watching a talk Andrew Kelley gave about a simple binary format he’s using in Zig: https://www.hytradboi.com/2025/05c72e39-c07e-41bc-ac40-85e83...

Having to map between SQLite and the application language seems like it’d add lots of complexity, but I don’t have any experience with custom file formats so would love some advice.

It seems like it would be relatively straightforward to make an sqlite based file format and just have users add a plugin if for some reason they couldn't upgrade their older version of LibreOffice etc. I agree with the other commenter who mentioned that the benefits for text and spreadsheet files needs more explanation. But it seems like a good enough idea to have a LibreOffice working group perform a more in depth study. If significant memory reduction is real and that would translate to fewer crashes, it would be a huge boost even if it had no other benefits, IMHO.

> SQLite database has a lot of capability, which this essay has only begun to touch upon. But hopefully this quick glimpse has convinced some readers that using an SQL database as an application file format is worth a second look.

It really is. One of the experiments we have been doing currently to make bug reporting from Androids easier (and to an extent, reduce user frustration and fatigue) is to store app logs (unstructured) in (an in-memory) SQLite table. It lends very well in to on-device LLMs (like Gemma 3n or Qwen2.5 0.5b), as users can Q&A to know just what the app is doing and why it won't work the way they want it to. On-device LLMs are limited (context length and/or embeddings) and too many writes (in batches of 1000 rows) to the in-memory SQLite table (surprisingly) eats up battery like no tomorrow, so this "chat to know what the app is doing" isn't rolled out to everyone, yet.

I would change this: "Do what works, not what your database professor said you ought to do."

To this: "Unless you work for Google or FaceBook, just do what works, not what your database professor said you ought to do."

Juggling all the fragments inside the database, garbage collecting all the unused ones, and maintaining consistency are all quite challenging in this use case.

I love SQLite.

As a document _exchange_/_interchange_ format, what I prefer for durability is a non-binary format (e.g. XML based).

For local use, I agree SQLite might be much faster than ZIP, and of course the ability to query based on SQL has its own flexibility merits.

Fwiw, autocad uses database format for its file data.

I'm a fan of both as a Linux user. Interesting thought experiment.

has anyone actually used the `content BLOB` pattern in a larger scale? Suppose if I have tens of thousands of small jpegs would it be better off in a .sqlite file?

(2014) https://web.archive.org/web/20141027044008/www.sqlite.org/af...

I remember I played with some software called "The Illumination Software Creator" [1], and I remember the saved project files were just SQLite databases.

I actually thought it was kind of cool, because I was able to play with it easily with some SQLite explorer tool (I forget which one) and I could easily look at how the save files actually worked.

I haven't really used SQLite for anything serious [2], but always found the idea of it kind of charming. Maybe I should dust it off and try it again.

[1] https://en.wikipedia.org/wiki/Illumination_Software_Creator by Bryan Lunduke before I realized how much of a pseudo-intellectual dimwit that he is.

[2] At least outside of the "included" database in a few web frameworks.