6 ms·

The killer feature for me with SQLite is as an application file format: https://sqlite.org/appfileformat.html https://sqlite.org/appfileformat.html It has SO M

by OskarS 2y ago

The killer feature for me with SQLite is as an application file format: https://sqlite.org/appfileformat.html https://sqlite.org/appfileformat.html It has SO MANY advantages over every other solution. It can do the "just a bundle of files" thing, but it can also do all this rich data as well. And unlike most "just a bundle of files" formats, it's incrementally updatable, it's so good. We're using this in production right now and couldn't be happier with it, wouldn't dream of using anything else at this point. One notable application that uses SQLite for this is Audacity: all the stuff you record is streamed into a SQLite database (the project file). This is a huge reason why Audacity is so good for recording long sessions, and why it's so resilient against crashes and failures.

therein 2y ago
I don't use Audacity often, maybe once or twice a month at most but stability and snappiness aren't really things I associate with Audacity. None of the crashes I ever encountered with Audacity has been due to SQLite of course. If anything this article made me want to use SQLite in more of my projects where I need to store local data.
- bux93 2y ago
  The resiliency against crashes offered by it's SQLite file format is much needed. Because it crashes a lot.
sureglymop 2y ago
I agree. And the best feature for me is "ATTACH DATABASE". Then one can just join together tables from multiple files etc.
romwell 2y ago
Once upon a time, I revamped a binary file format for a propriety industrial-scientific software. The software was writing vast arrays of numbers into a file, and reading them based on offsets. As the software (and its data) evolved, versioning became a nightmare. I ended up writing an ORM serializer which read from/wrote into an SQLite DB with a variation on an EAV schema. One of my favorite projects, result-wise. Reading and writing was clean and efficient; the format was portable, language-agnostic, compact. Versions of the new format were backwards- (and, within reason, forward-) compatible with each other (I forced specifying sane defaults for attributes if they're missing). I'm still thinking of reimplementing a project like that as FOSS one day. Serializing numeric data to something like JSON is a waste, and the structured binary formats I've looked at aren't nearly as thought out as SQLite.
- gchamonlive 2y ago
  For numeric values only, any advantage of using sqlite over let's say parquet with Apache Arrow/duckdb?
  - avereveard 2y ago
    SQLite let's you decide when and if vacuuming after partial row modifications. Does arrow allows that I don't know but it's most likely the best SQLite feature you can open a massive database and do one value change really really fast without having to write and compact the entire thing all the times.
    - andyferris 2y ago
      Arrow isn’t great at random insertion and deletion, as each column is a contiguous array. If it’s on disk those arrays are probably packed tightly together, so you can’t even append rows. (I have an idea to implement an LSM tree where each layer is an arrow file, which should allow for faster mutations while maintaining a lot of the benefits of arrow. But I haven’t got around to it).
  - CJefferson 2y ago
    Given SQLite is already distributed on basically every OS,is amazingly rock solid, I’d ask instead any advantage to using Apache Arrow/duckdb? I don’t have great experience with either.
    - jitl 2y ago
      DuckDB can have massive performance advantage over SQLite for things like aggregates on wide tables or or filters on unindexed columns, with advantage growing with the size of the table. We tried a few head to heads between the two and while we ultimately went with SQLite, we may go back and double write everything to DuckDB to get better performance for those kinds of queries on the same data set. Duckdb can also read / import SQLite natively so we could ingest lazily from sqlite disk -> duckdb. Biggest issue with duckdb is some memory leaks and crashes for the nodejs driver. Seems not production ready.
boffinAudio 2y ago
I used SQLite as the file format for a game system, and it was probably one of the most successful technical decisions I've made in 40 years. The fact that the artists and developers and even managers could use standard database tools to lay out changes to the game levels, characters and even physics behaviours, meant I didn't have to write a ton of tooling to allow them access to all of those things - they just opened up their favourite database GUI, added the data to the sqlite database, and shared the single file around. Nowadays I just can't see any other way to do this, other than by maintaining a well-ordered .zip file or filesystem structure, with strict validation being something that has to be programmed. I'd just prefer to sqlite all the things.
- jeremyjh 2y ago
  That sounds like a decent solution, but I'd rather have all that content in text files so there is meaningful version control with features like diffs and merges. Some engines will store the data this way but give you an editor to manage it, and there are dedicated level editors as well but every project is different and it could be the case none of that would have been helpful to you.
  - Karellen 2y ago
    AFAIK git has pluggable diff and merge engines. I wonder if it would be possible to create a diff/merge git plugin for sqlite files that does an intermediate conversion to .sql dump files, with some kind of serialisation normalisation so that rows are output in a stable order between runs.
    - TeMPOraL 2y ago
      It is. It's a bit of a hassle, because the git mechanism that allows it is considered sensitive, so all users of your repo will need to separately configure this if they want to use it. Bigger problem for me is that none of the code review systems I know of support this. At work, we've been wishing to have something like this in Gerrit, due to multiple SQLite DBs and other binary documents living in the repo, but as far as we can tell, we'd have to modify Gerrit sources directly to make it happen, which IT would frown at and nobody has time for anyway.
belter 2y ago
In 2024, not using SQLite as your application file format, should really force an engineering team to have a damn good explanation.... https://www.sqlite.org/famous.html https://www.sqlite.org/famous.html
- throwup238 2y ago
  > The United States Library of Congress recognizes SQLite as a recommended storage format for preservation of digital content. That’s one hell of an endorsement.
- threatofrain 2y ago
  Is SQLite the best solution for on-device logs?
- ecuaflo 2y ago
  unfortunately in the browser it needs to be downloaded so you’re stuck with indexeddb
  - sgbeal 2y ago
    > unfortunately in the browser it needs to be downloaded so you’re stuck with indexeddb That argument implies that we're stuck with the browser's single built-in home page because every other web page has to be downloaded. How is having to download sqlite3.wasm any different from having to download HTML, CSS, JS, images, etc.?
    - w0m 2y ago
      > How is having to download sqlite3.wasm any different from 'unnecessary' file size. K matter on the web still. I'm not normally a web guy, but from https://sqlite.org/download.html https://sqlite.org/download.html it looks like it's ~800k extra added to initial page load? Based on average mobile speed in US of 97.09Mbps; that's an extra half second added to initial page load. That's not trivial; maybe an extra 15% bounce rate on initial hits.
kabanka 2y ago
Also, the in-memory feature (‘:memory:’) allows you to start working “on the fly” and saving the work to disk later. Really convenient for the user, zero cost for the developer (same API for in-memory/fs db and smooth transition between them).
- TeMPOraL 2y ago
  If you're careful about it, you can store the canonical runtime state for e.g. a videogame in :memory: database; you then get saving/loading for free with SQLite backup API.
  - bryancoxwell 2y ago
    Can you explain what you mean about needing to be careful about it?
    - TeMPOraL 2y ago
      You need to store all the data that define the runtime state, so it can be dumped and recovered, without accidentally capturing information or references to things that live outside the DB and won't survive application restart. It's a more generic case of the "don't dump pointers to files, if you can't guarantee they'll be valid after reload".
      - bryancoxwell 2y ago
        Thanks!
    - v7n 2y ago
      In addition to paying attention to references, perhaps it makes sense to always keep e.g. player inventory state in there, but something that can change every frame, like player position data, should perhaps only be synced just prior to the snapshot if it would otherwise degrade performance too much. This is of course another layer of complexity (volatile vs non-volatile game state) to think about.
out_of_protocol 2y ago
Theres is "Pack" archive format that does exactly this for general-purpose archives (sqlite + zstd for compression). https://news.ycombinator.com/item?id=39793805 https://news.ycombinator.com/item?id=39793805 https://pack.ac/ https://pack.ac/ works faster AND more compact than .tar.zstd and .7z.zstd (patched 7z with zstd compression)
jhanschoo 2y ago
It doesn't list obvious cases where you shouldn't take this approach, so I have one off the top of my head that SQLite doesn't seem like it's a good fit, I'm interested in whether I'm wrong on this. The article discusses storing files as BLOBs. But it looks to me like this isn't a good fit for sufficiently memory-constrained environments, given a task involving processing a very large file in a sequential manner, like with media reencoding, or video playback. It seems like the traditional file access patterns are better suited for those tasks.
- jitl 2y ago
  You can incrementally read or write the value in a BLOB column of a row. You can read/write etc using a file descriptor like API: https://www.sqlite.org/c3ref/blob_open.html https://www.sqlite.org/c3ref/blob_open.html
  - jhanschoo 2y ago
    Thanks, I did a quick google for something like this before writing my comment, but your link did not come up.
- OskarS 2y ago
  It's very easy to design a schema that breaks up blobs into chunks. SQLite is excellent for memory constrained environments, with even mathematical proofs of maximum memory usage: https://www.sqlite.org/malloc.html https://www.sqlite.org/malloc.html

by OskarS 2y ago

therein 2y ago

I don't use Audacity often, maybe once or twice a month at most but stability and snappiness aren't really things I associate with Audacity. None of the crashes I ever encountered with Audacity has been due to SQLite of course. If anything this article made me want to use SQLite in more of my projects where I need to store local data.

bux93 2y ago
The resiliency against crashes offered by it's SQLite file format is much needed. Because it crashes a lot.

sureglymop 2y ago

I agree. And the best feature for me is "ATTACH DATABASE". Then one can just join together tables from multiple files etc.

romwell 2y ago

Once upon a time, I revamped a binary file format for a propriety industrial-scientific software. The software was writing vast arrays of numbers into a file, and reading them based on offsets. As the software (and its data) evolved, versioning became a nightmare. I ended up writing an ORM serializer which read from/wrote into an SQLite DB with a variation on an EAV schema. One of my favorite projects, result-wise. Reading and writing was clean and efficient; the format was portable, language-agnostic, compact. Versions of the new format were backwards- (and, within reason, forward-) compatible with each other (I forced specifying sane defaults for attributes if they're missing). I'm still thinking of reimplementing a project like that as FOSS one day. Serializing numeric data to something like JSON is a waste, and the structured binary formats I've looked at aren't nearly as thought out as SQLite.

gchamonlive 2y ago
For numeric values only, any advantage of using sqlite over let's say parquet with Apache Arrow/duckdb?
- avereveard 2y ago
  SQLite let's you decide when and if vacuuming after partial row modifications. Does arrow allows that I don't know but it's most likely the best SQLite feature you can open a massive database and do one value change really really fast without having to write and compact the entire thing all the times.
  - andyferris 2y ago
    Arrow isn’t great at random insertion and deletion, as each column is a contiguous array. If it’s on disk those arrays are probably packed tightly together, so you can’t even append rows. (I have an idea to implement an LSM tree where each layer is an arrow file, which should allow for faster mutations while maintaining a lot of the benefits of arrow. But I haven’t got around to it).
- CJefferson 2y ago
  Given SQLite is already distributed on basically every OS,is amazingly rock solid, I’d ask instead any advantage to using Apache Arrow/duckdb? I don’t have great experience with either.
  - jitl 2y ago
    DuckDB can have massive performance advantage over SQLite for things like aggregates on wide tables or or filters on unindexed columns, with advantage growing with the size of the table. We tried a few head to heads between the two and while we ultimately went with SQLite, we may go back and double write everything to DuckDB to get better performance for those kinds of queries on the same data set. Duckdb can also read / import SQLite natively so we could ingest lazily from sqlite disk -> duckdb. Biggest issue with duckdb is some memory leaks and crashes for the nodejs driver. Seems not production ready.

boffinAudio 2y ago

I used SQLite as the file format for a game system, and it was probably one of the most successful technical decisions I've made in 40 years. The fact that the artists and developers and even managers could use standard database tools to lay out changes to the game levels, characters and even physics behaviours, meant I didn't have to write a ton of tooling to allow them access to all of those things - they just opened up their favourite database GUI, added the data to the sqlite database, and shared the single file around. Nowadays I just can't see any other way to do this, other than by maintaining a well-ordered .zip file or filesystem structure, with strict validation being something that has to be programmed. I'd just prefer to sqlite all the things.

jeremyjh 2y ago
That sounds like a decent solution, but I'd rather have all that content in text files so there is meaningful version control with features like diffs and merges. Some engines will store the data this way but give you an editor to manage it, and there are dedicated level editors as well but every project is different and it could be the case none of that would have been helpful to you.
- Karellen 2y ago
  AFAIK git has pluggable diff and merge engines. I wonder if it would be possible to create a diff/merge git plugin for sqlite files that does an intermediate conversion to .sql dump files, with some kind of serialisation normalisation so that rows are output in a stable order between runs.
  - TeMPOraL 2y ago
    It is. It's a bit of a hassle, because the git mechanism that allows it is considered sensitive, so all users of your repo will need to separately configure this if they want to use it. Bigger problem for me is that none of the code review systems I know of support this. At work, we've been wishing to have something like this in Gerrit, due to multiple SQLite DBs and other binary documents living in the repo, but as far as we can tell, we'd have to modify Gerrit sources directly to make it happen, which IT would frown at and nobody has time for anyway.

belter 2y ago

In 2024, not using SQLite as your application file format, should really force an engineering team to have a damn good explanation.... https://www.sqlite.org/famous.html https://www.sqlite.org/famous.html

throwup238 2y ago
> The United States Library of Congress recognizes SQLite as a recommended storage format for preservation of digital content. That’s one hell of an endorsement.
threatofrain 2y ago
Is SQLite the best solution for on-device logs?
ecuaflo 2y ago
unfortunately in the browser it needs to be downloaded so you’re stuck with indexeddb
- sgbeal 2y ago
  > unfortunately in the browser it needs to be downloaded so you’re stuck with indexeddb That argument implies that we're stuck with the browser's single built-in home page because every other web page has to be downloaded. How is having to download sqlite3.wasm any different from having to download HTML, CSS, JS, images, etc.?
  - w0m 2y ago
    > How is having to download sqlite3.wasm any different from 'unnecessary' file size. K matter on the web still. I'm not normally a web guy, but from https://sqlite.org/download.html https://sqlite.org/download.html it looks like it's ~800k extra added to initial page load? Based on average mobile speed in US of 97.09Mbps; that's an extra half second added to initial page load. That's not trivial; maybe an extra 15% bounce rate on initial hits.

kabanka 2y ago

Also, the in-memory feature (‘:memory:’) allows you to start working “on the fly” and saving the work to disk later. Really convenient for the user, zero cost for the developer (same API for in-memory/fs db and smooth transition between them).

TeMPOraL 2y ago
If you're careful about it, you can store the canonical runtime state for e.g. a videogame in :memory: database; you then get saving/loading for free with SQLite backup API.
- bryancoxwell 2y ago
  Can you explain what you mean about needing to be careful about it?
  - TeMPOraL 2y ago
    You need to store all the data that define the runtime state, so it can be dumped and recovered, without accidentally capturing information or references to things that live outside the DB and won't survive application restart. It's a more generic case of the "don't dump pointers to files, if you can't guarantee they'll be valid after reload".
    - bryancoxwell 2y ago
      Thanks!
  - v7n 2y ago
    In addition to paying attention to references, perhaps it makes sense to always keep e.g. player inventory state in there, but something that can change every frame, like player position data, should perhaps only be synced just prior to the snapshot if it would otherwise degrade performance too much. This is of course another layer of complexity (volatile vs non-volatile game state) to think about.

out_of_protocol 2y ago

Theres is "Pack" archive format that does exactly this for general-purpose archives (sqlite + zstd for compression). https://news.ycombinator.com/item?id=39793805 https://news.ycombinator.com/item?id=39793805 https://pack.ac/ https://pack.ac/ works faster AND more compact than .tar.zstd and .7z.zstd (patched 7z with zstd compression)

jhanschoo 2y ago

It doesn't list obvious cases where you shouldn't take this approach, so I have one off the top of my head that SQLite doesn't seem like it's a good fit, I'm interested in whether I'm wrong on this. The article discusses storing files as BLOBs. But it looks to me like this isn't a good fit for sufficiently memory-constrained environments, given a task involving processing a very large file in a sequential manner, like with media reencoding, or video playback. It seems like the traditional file access patterns are better suited for those tasks.

jitl 2y ago
You can incrementally read or write the value in a BLOB column of a row. You can read/write etc using a file descriptor like API: https://www.sqlite.org/c3ref/blob_open.html https://www.sqlite.org/c3ref/blob_open.html
- jhanschoo 2y ago
  Thanks, I did a quick google for something like this before writing my comment, but your link did not come up.
OskarS 2y ago
It's very easy to design a schema that breaks up blobs into chunks. SQLite is excellent for memory constrained environments, with even mathematical proofs of maximum memory usage: https://www.sqlite.org/malloc.html https://www.sqlite.org/malloc.html