5 ms·

If binary, consider just using SQLite.

lifthrasiir 1y ago
Using SQLite as a container format is only beneficial when the file format itself is a composite, like word processor files which will include both the textual data and any attachments. SQLite is just a hinderance otherwise, like image file formats or archival/compressed file formats [1]. [1] SQLite's own sqlar format is a bad idea for this reason.
- frainfreeze 1y ago
  sqlar proved a great solution in the past for me. Where does it fall short in your experience?
  - lifthrasiir 1y ago
    Unless you are using the container file as a database too, sqlar is strictly inferior to ZIP in terms of pretty much everything [1]. I'm actually more interested in the context sqlar did prove useful for you. [1] https://news.ycombinator.com/item?id=28670418 https://news.ycombinator.com/item?id=28670418
    - frainfreeze 1y ago
      I remember seeing the comment you linked few years back, and back then comments were already locked so I couldn't reply, and this time I sadly don't have the time to get deeper into this, however - I recommend you to research more about sqlar/using sqlite db as _file format_ in general, or at minimum looking at SQLite Encryption Extension (SEE) (https://www.sqlite.org/see/doc/trunk/www/readme.wiki https://www.sqlite.org/see/doc/trunk/www/readme.wiki). You can get a lot out of the box with very little investment. IMHO sqlar is not competing with ZIP (can zip do metadata and transactions?)
      - lifthrasiir 1y ago
        > [...] at minimum looking at SQLite Encryption Extension (SEE) (https://www.sqlite.org/see/doc/trunk/www/readme.wiki https://www.sqlite.org/see/doc/trunk/www/readme.wiki). SEE is a proprietary extension, however generous its license is. So it is not very meaningful when sqlar is compared against ZIP. Not to say that I necessarily see encryption as a fundamental feature for compressed archive formats though---I'm advocating for age [1] integration instead. [1] https://github.com/FiloSottile/age https://github.com/FiloSottile/age > IMHO sqlar is not competing with ZIP (can zip do metadata and transactions?) In my understanding SQLite's support for sqlar and ZIP occurred at the same time, so I believe that sqlar was created to demonstrate an alternative to ZIP (and that the demonstration wasn't good enough). I'm aware that this is just a circumstantial evidence, so let me know if you have some concrete one. ZIP can of course do metadata in the form of per-file and archive comments. For more structured metadata, you can make use of extra fields if you really, really want, but at that point SQLite would indeed be a better choice. I however doubt it's a typical use case. ZIP can be partially updated in place but can't do any transaction. But it should be noted that SQLite handles transaction by additional files (`-journal` or `-wal` files). So both sqlar and ZIP would write to an additional file during the update process, though SQLite would write much less data compared to ZIP. Any remaining differences are invisible to end users, unless the in-place update is common enough in which case the use of SQLite is justified.
        frainfreeze 1y ago
        Point is that SEE exists, and so do free alternatives. > In my understanding SQLite's support for sqlar and ZIP occurred at the same time I believe so too. I agree with you on SQLAR being poor general-purpose archive or compression format compared to ZIP; what I'm arguing is that its very good file format for certain applications, offering structured, modifiable, and searchable file storage. We had great success using it as db/file format for PLM solution packed both as desktop and web app. Same database can then be used to power the web ui (single tenant SaaS deployments), and for desktop app (web export is simply a working file for desktop app). This file being just a simple sqlite db lets users play with data, do their own imports, migrations etc., while having all files & docs in one place.
- SyrupThinker 1y ago
  From my own experience SQLite works just fine as the container for an archive format. It ends up having some overhead compared to established ones, but the ability to query over the attributes of 10000s of files is pretty nice, and definitely faster than the worst case of tar. My archiver could even keep up with 7z in some cases (for size and access speed). Implementing it is also not particularly tricky, and SQLite even allows streaming the blobs. Making readers for such a format seems more accessible to me.
  - lifthrasiir 1y ago
    SQLite format itself is not very simple, because it is a database file format in its heart. By using SQLite you are unknowingly constraining your use case; for example you can indeed stream BLOBs, but you can't randomly access BLOBs because the SQLite format puts a large BLOB into pages in a linked list, at least when I checked last. And BLOBs are limited in size anyway (4GB AFAIK) so streaming itself might not be that useful. The use of SQLite also means that you have to bring SQLite into your code base, and SQLite is not very small if you are just using it as a container. > My archiver could even keep up with 7z in some cases (for size and access speed). 7z might feel slow because it enables solid compression by default, which trades decompression speed with compression ratio. I can't imagine 7z having a similar compression ratio with correct options though, was your input incompressible?
    - duskwuff 1y ago
      BLOBs support random access - the handles aren't stateful. https://www.sqlite.org/c3ref/blob_read.html https://www.sqlite.org/c3ref/blob_read.html You're right that their size is limited, though, and it's actually worse than you even thought (1 GB).
      - lifthrasiir 1y ago
        My statement wasn't precise enough, you are correct that random access API is provided. But it is ultimately connected to the `accessPayload` function in btree.c which comment mentions that: ** The content being read or written might appear on the main page ** or be scattered out on multiple overflow pages. In the other words, the API can read from multiple scattered pages unknowingly to the caller. That said I see this can be considered enough for being random accessible, as the underlying file system would use similarly structured indices behind the scene anyway... (But modern file systems do have consecutively allocated pages for performance.)
- sureglymop 1y ago
  I think it's fine as an image format. I've used the mbtiles format which is basically just a table filled with map tiles. Sqlite makes it super easy to deal with it, e.g. to dump individual blobs and save them as image files. It just may not always be the most performant option. For example, for map tiles there is alternatively the pmtiles binary format which is optimized for http range requests.
- InsideOutSanta 1y ago
  The Mac image editor Acorn uses SQLite as its file format. It's described here: https://shapeof.com/archives/2025/4/acorn_file_format.html https://shapeof.com/archives/2025/4/acorn_file_format.html The author notes that an advantage is that other programs can easily read the file format and extract information from it.
  - lifthrasiir 1y ago
    It is clearly a composite file format [1]: > Acorn’s native file format is used to losslessly store layer data, editable text, layer filters, an optional composite of the image, and various metadata. Its advantage over other common formats such as PNG or JPEG is that it preserves all this native information without flattening the layer data or vector graphics. As I've mentioned, this is a good use case for SQLite as a container. But ZIP would work equally well here. [1] https://flyingmeat.com/acorn/docs/technotes/ACTN002.html https://flyingmeat.com/acorn/docs/technotes/ACTN002.html
- aidenn0 1y ago
  Except image formats and archival formats are composites (data+metadata). We have Exif for images, and you might be surprised by how much metadata the USTar format has.
  - lifthrasiir 1y ago
    With that reasoning almost every format is a composite, which doesn't sound like a useful distinction. Such metadata should be fine as long as the metadata itself is isolated and can be updated without the parent format.
    - aidenn0 1y ago
      I agree that almost every format is a composite; you seem to not, which makes me think you mean something different than I by "composite." Your reply suggests that, if all the metadata is auxiliary it can be segregated from the data and doesn't count as a composite. However, that doesn't exclude archives (in many use-cases the file metadata is as important as the data itself; consider e.g. hardlinks in TAR files) Nor does it exclude certain vital metadata for images: resolution, color-space, and bit-depth come to mind.
      - lifthrasiir 1y ago
        My reasoning for Exif was that it is not only auxiliary but also post-hoc. Exif was defined independently from image formats and only got adopted later because those formats provided extension points (JPEG APP# markers, PNG chunks). You've got a good point that there are multiple types of metadata and some metadata might be crucial for interpreting data. I would say such "structural" metadata should be considered as a part of data. I'm not saying it is not a metadata; it is a metadata inside some data, so doesn't count for our purpose of defining a composite. I also don't think tar hardlinks are metadata for our purpose, because it technically consists of the linked path instead of the file contents and the information that the file is a hardlink, where the former is clearly a data and the latter is a metadata used to reconstruct the original file system so should be considered as a part of larger data (in this case, a logical notion of "file"). I believe these examples should be enough to derive my own definition of "composite". Please let me know otherwise.
paulddraper 1y ago
Did you read the article? That wouldn’t support partial parsing.
- HelloNurse 1y ago
  On the contrary, loading everything from a database is the limit case of "partial parsing" with queries that read only a few pages of a few tables and indices. From the point of view of the article, a SQLite file is similar to a chunked file format: the compact directory of what tables etc. it contains is more heavyweight than listing chunk names and lengths/offsets, but at least as fast, and loading only needed portions of the file is automatically managed.

Using SQLite as a container format is only beneficial when the file format itself is a composite, like word processor files which will include both the textual data and any attachments. SQLite is just a hinderance otherwise, like image file formats or archival/compressed file formats [1]. [1] SQLite's own sqlar format is a bad idea for this reason.

frainfreeze 1y ago
sqlar proved a great solution in the past for me. Where does it fall short in your experience?
- lifthrasiir 1y ago
  Unless you are using the container file as a database too, sqlar is strictly inferior to ZIP in terms of pretty much everything [1]. I'm actually more interested in the context sqlar did prove useful for you. [1] https://news.ycombinator.com/item?id=28670418 https://news.ycombinator.com/item?id=28670418
  - frainfreeze 1y ago
    I remember seeing the comment you linked few years back, and back then comments were already locked so I couldn't reply, and this time I sadly don't have the time to get deeper into this, however - I recommend you to research more about sqlar/using sqlite db as _file format_ in general, or at minimum looking at SQLite Encryption Extension (SEE) (https://www.sqlite.org/see/doc/trunk/www/readme.wiki https://www.sqlite.org/see/doc/trunk/www/readme.wiki). You can get a lot out of the box with very little investment. IMHO sqlar is not competing with ZIP (can zip do metadata and transactions?)
    - lifthrasiir 1y ago
      > [...] at minimum looking at SQLite Encryption Extension (SEE) (https://www.sqlite.org/see/doc/trunk/www/readme.wiki https://www.sqlite.org/see/doc/trunk/www/readme.wiki). SEE is a proprietary extension, however generous its license is. So it is not very meaningful when sqlar is compared against ZIP. Not to say that I necessarily see encryption as a fundamental feature for compressed archive formats though---I'm advocating for age [1] integration instead. [1] https://github.com/FiloSottile/age https://github.com/FiloSottile/age > IMHO sqlar is not competing with ZIP (can zip do metadata and transactions?) In my understanding SQLite's support for sqlar and ZIP occurred at the same time, so I believe that sqlar was created to demonstrate an alternative to ZIP (and that the demonstration wasn't good enough). I'm aware that this is just a circumstantial evidence, so let me know if you have some concrete one. ZIP can of course do metadata in the form of per-file and archive comments. For more structured metadata, you can make use of extra fields if you really, really want, but at that point SQLite would indeed be a better choice. I however doubt it's a typical use case. ZIP can be partially updated in place but can't do any transaction. But it should be noted that SQLite handles transaction by additional files (`-journal` or `-wal` files). So both sqlar and ZIP would write to an additional file during the update process, though SQLite would write much less data compared to ZIP. Any remaining differences are invisible to end users, unless the in-place update is common enough in which case the use of SQLite is justified.
      - frainfreeze 1y ago
        Point is that SEE exists, and so do free alternatives. > In my understanding SQLite's support for sqlar and ZIP occurred at the same time I believe so too. I agree with you on SQLAR being poor general-purpose archive or compression format compared to ZIP; what I'm arguing is that its very good file format for certain applications, offering structured, modifiable, and searchable file storage. We had great success using it as db/file format for PLM solution packed both as desktop and web app. Same database can then be used to power the web ui (single tenant SaaS deployments), and for desktop app (web export is simply a working file for desktop app). This file being just a simple sqlite db lets users play with data, do their own imports, migrations etc., while having all files & docs in one place.
SyrupThinker 1y ago
From my own experience SQLite works just fine as the container for an archive format. It ends up having some overhead compared to established ones, but the ability to query over the attributes of 10000s of files is pretty nice, and definitely faster than the worst case of tar. My archiver could even keep up with 7z in some cases (for size and access speed). Implementing it is also not particularly tricky, and SQLite even allows streaming the blobs. Making readers for such a format seems more accessible to me.
- lifthrasiir 1y ago
  SQLite format itself is not very simple, because it is a database file format in its heart. By using SQLite you are unknowingly constraining your use case; for example you can indeed stream BLOBs, but you can't randomly access BLOBs because the SQLite format puts a large BLOB into pages in a linked list, at least when I checked last. And BLOBs are limited in size anyway (4GB AFAIK) so streaming itself might not be that useful. The use of SQLite also means that you have to bring SQLite into your code base, and SQLite is not very small if you are just using it as a container. > My archiver could even keep up with 7z in some cases (for size and access speed). 7z might feel slow because it enables solid compression by default, which trades decompression speed with compression ratio. I can't imagine 7z having a similar compression ratio with correct options though, was your input incompressible?
  - duskwuff 1y ago
    BLOBs support random access - the handles aren't stateful. https://www.sqlite.org/c3ref/blob_read.html https://www.sqlite.org/c3ref/blob_read.html You're right that their size is limited, though, and it's actually worse than you even thought (1 GB).
    - lifthrasiir 1y ago
      My statement wasn't precise enough, you are correct that random access API is provided. But it is ultimately connected to the `accessPayload` function in btree.c which comment mentions that: ** The content being read or written might appear on the main page ** or be scattered out on multiple overflow pages. In the other words, the API can read from multiple scattered pages unknowingly to the caller. That said I see this can be considered enough for being random accessible, as the underlying file system would use similarly structured indices behind the scene anyway... (But modern file systems do have consecutively allocated pages for performance.)
sureglymop 1y ago
I think it's fine as an image format. I've used the mbtiles format which is basically just a table filled with map tiles. Sqlite makes it super easy to deal with it, e.g. to dump individual blobs and save them as image files. It just may not always be the most performant option. For example, for map tiles there is alternatively the pmtiles binary format which is optimized for http range requests.
InsideOutSanta 1y ago
The Mac image editor Acorn uses SQLite as its file format. It's described here: https://shapeof.com/archives/2025/4/acorn_file_format.html https://shapeof.com/archives/2025/4/acorn_file_format.html The author notes that an advantage is that other programs can easily read the file format and extract information from it.
- lifthrasiir 1y ago
  It is clearly a composite file format [1]: > Acorn’s native file format is used to losslessly store layer data, editable text, layer filters, an optional composite of the image, and various metadata. Its advantage over other common formats such as PNG or JPEG is that it preserves all this native information without flattening the layer data or vector graphics. As I've mentioned, this is a good use case for SQLite as a container. But ZIP would work equally well here. [1] https://flyingmeat.com/acorn/docs/technotes/ACTN002.html https://flyingmeat.com/acorn/docs/technotes/ACTN002.html
aidenn0 1y ago
Except image formats and archival formats are composites (data+metadata). We have Exif for images, and you might be surprised by how much metadata the USTar format has.
- lifthrasiir 1y ago
  With that reasoning almost every format is a composite, which doesn't sound like a useful distinction. Such metadata should be fine as long as the metadata itself is isolated and can be updated without the parent format.
  - aidenn0 1y ago
    I agree that almost every format is a composite; you seem to not, which makes me think you mean something different than I by "composite." Your reply suggests that, if all the metadata is auxiliary it can be segregated from the data and doesn't count as a composite. However, that doesn't exclude archives (in many use-cases the file metadata is as important as the data itself; consider e.g. hardlinks in TAR files) Nor does it exclude certain vital metadata for images: resolution, color-space, and bit-depth come to mind.
    - lifthrasiir 1y ago
      My reasoning for Exif was that it is not only auxiliary but also post-hoc. Exif was defined independently from image formats and only got adopted later because those formats provided extension points (JPEG APP# markers, PNG chunks). You've got a good point that there are multiple types of metadata and some metadata might be crucial for interpreting data. I would say such "structural" metadata should be considered as a part of data. I'm not saying it is not a metadata; it is a metadata inside some data, so doesn't count for our purpose of defining a composite. I also don't think tar hardlinks are metadata for our purpose, because it technically consists of the linked path instead of the file contents and the information that the file is a hardlink, where the former is clearly a data and the latter is a metadata used to reconstruct the original file system so should be considered as a part of larger data (in this case, a logical notion of "file"). I believe these examples should be enough to derive my own definition of "composite". Please let me know otherwise.

paulddraper 1y ago

Did you read the article? That wouldn’t support partial parsing.

HelloNurse 1y ago
On the contrary, loading everything from a database is the limit case of "partial parsing" with queries that read only a few pages of a few tables and indices. From the point of view of the article, a SQLite file is similar to a chunked file format: the compact directory of what tables etc. it contains is more heavyweight than listing chunk names and lengths/offsets, but at least as fast, and loading only needed portions of the file is automatically managed.