SQLite as an Application File Format • Hacker News Board

Re: SQLite as an Application File Format

#71

Post by fauigerzigerk » Fri, Nov 28, 2025, 6:39 PM UTC

robrenaud wrote: post #49

lateforwork wrote: post #42

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 a…

I had some json data that I wanted an annotation interface for. So I asked codex to put it into sqlite and make a little annotation webserver. It worked quickly/easily and without hassle. Sqlite supports queries over json-like objects. Maybe a very simple document oriented db would have been better? My biggest gripe is that the sqlite cli is brutally minimal (makes sense given design), but I probably should have been…

What do you mean by "json-like objects"?

My issue with SQLite's JSON implementation is that it cannot index arrays. SQLite indexes can only contain a single value per row (except for fulltext indexes but that's not what I want most of the time). SQLite has nothing like GIN indexes in Postgres.

Re: SQLite as an Application File Format

#72

Post by dahart » Fri, Nov 28, 2025, 6:56 PM UTC

amiga386 wrote: post #56

dahart wrote: post #45

Earlier quoted context omitted.

ZIP isn’t an application format, it’s a container, no? You store files with any format in a .zip, and that’s what applications do - they read files with other formats out of the .zip. What are your goals; what else would you pick, and why? What are the questionable choices you refer to?

I suspect he means the choices of putting the central directory headers at the end of the file, as well as having local file headers as you read through the file, which allows for ambiguity. Alternatively, he could mean that, for the purposes of archiving , ZIP is very far behind the state of the art (no solid compression, old algorithms, small windows, file size limits without the ZIP64 extensions, and so on, most o…

Thanks, makes sense. Are the headers even an issue when using ZIP as a container? Are there superior alternatives in practice?

I’ve reached for ZIP for application containers because it’s really easy, not because of design choices that affect me. Typically the compression is a convenient byproduct but not a requirement, and file size limits could be an issue, perhaps, but isn’t something I’ve ever hit when using ZIP for application data. File size limits is something I’ve hit when trying to archive lots of files.

Using ZIP for build pipelines that produce a large number of small files is handy since it’s often faster than direct file I/O, even on SSDs. In the past was much faster than spinning media, especially DVDs. These days in Python you can unzip to RAM and treat it like a small file system - and for that file size limits aren’t an issue in practice.

Re: SQLite as an Application File Format

#73

Post by euroderf » Fri, Nov 28, 2025, 7:32 PM UTC

forgotpwd16 wrote: post #63

euroderf wrote: post #50

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.

There's FUSE-using Sqlitefs & WebDAV-using Wddbfs.

FUSE on Mac seems to be a kernel/permissions mess.

Re: SQLite as an Application File Format

#74

Post by SoKamil » Fri, Nov 28, 2025, 7:56 PM UTC

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.

Re: SQLite as an Application File Format

#75

Post by ianberdin » Fri, Nov 28, 2025, 8:59 PM UTC

Sold. Absolutely.

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

Re: SQLite as an Application File Format

#76

Post by dchest » Fri, Nov 28, 2025, 9:20 PM UTC

setr wrote: post #51

seanalltogether wrote: post #23

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.

Messages uses it too on Mac; was using it to do some convoluted text search on my history

Not as an application file format discussed in the link, though. Lots of software use it as a database (as intended) it's also a base for Apple's Core Data.

Re: SQLite as an Application File Format

#77

Post by [deleted] » Fri, Nov 28, 2025, 9:34 PM UTC

lateforwork wrote: post #42

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 a…

[deleted]

Re: SQLite as an Application File Format

#78

Post by out_of_protocol » Fri, Nov 28, 2025, 10:09 PM UTC

incanus77 wrote: post #43

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 interfac…

Also, tested .zip vs .tar vs sqlite vs file system. Of this bunch, sqlite was the most compact format with minimal overhead.

Re: SQLite as an Application File Format

#79

Post by giancarlostoro » Fri, Nov 28, 2025, 10:16 PM UTC

actionfromafar wrote: post #61

giancarlostoro wrote: post #25

Earlier quoted context omitted.

Forget elf, imagine having a SQLite file that stores elf, exe and DMG binaries. I would not mind working on something like this.

Not that at all, but interesting in its own right - https://pypi.org/project/sqlelf/ explore ELF via SQL.

Yeah I'm thinking of like "appimage" but you can use it to run on any platform.

Re: SQLite as an Application File Format

#80

Post by lateforwork » Fri, Nov 28, 2025, 11:55 PM UTC

packetlost wrote: post #58

lateforwork wrote: post #42

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 a…

Maybe not as obvious for those without formal education in """database normalization""" but it's pretty trivial to convert from a tree structure to a flat table structure using foreign key relations. Recursive queries aren't even that difficult in SQLite, so self-referential data can be represented cleanly too, if not a bit more difficult to write. IME most applications "tree structures" aren't self-referential and a…

What problem are you trying to solve with this approach? Unless your document is huge and you need the ability to read or update portions of it, it is better to just read and write JSON.