Hacker News Reader

Comment by OskarS | Hacker News Reader

The killer feature for me with SQLite is as an application file format: 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.

View on HN

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.

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.

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.

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.

I haven't tried it with git pluggable diff tools yet, but I wrote a tool that lets me convert a SQLite database into a bunch of plain text files so I can manage them in git: https://github.com/simonw/sqlite-diffable

Not git, but I find it's interesting how Fossil implements version control https://www.fossil-scm.org/home/doc/trunk/www/tech_overview....

We maintain a compromise with Alembic migration files in source control. This does mean people need to do Alembic things to make changes, but if they can do that little bit it's very good, and fully diffable of course.

We solved this problem by checking in full sql dumps of the content database into source control, in between major changes. Not that we ever had to check and see what went wrong with the content side - using an sqlite database pretty much ensured that things were in the right format before it hit the filesystem assets, and we had practially zero bugs from introducing this methodology to the project.

Thousands of text files laying on bare filesystem is rather slow solution, so additional step to convert between text files and sqlite is needed. Never have seen ready to use solutions, only heard of ad-hoc scripts

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

> 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.

Is SQLite the best solution for on-device logs?

unfortunately in the browser it needs to be downloaded so you’re stuck with indexeddb

> 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.?

> 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 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.

> ~800k extra added to initial page load?

If you're serving it uncompressed, which no production-grade site will (for a given definition of "no"/"none").

> K matter on the web still.

Not, i opine, for the types of apps which want to host client-side databases. These are client-side applications, not "web pages."

Even a bare-bones, database-less google.com is now 3.14mb uncompressed (1.32 compressed), and that's not counting the pieces which uBlock Origin keep from loading. It loads somewhere around 2MB (uncompressed) of JS.

Last i checked, gdrive downloaded some 14mb to get up and running.

> that's an extra half second added to initial page load. That's not trivial;

We'll have to agree to disagree on whether half a second extra initial-hit-only load time is trivial.

> If you're serving it uncompressed

That 847 KB is actually the size of the (compressed) zip file, but it does contain other files. The compressed WASM + the JS to load it is probably about 500K, depending on the specifics of compression & minification.

Makes me laugh. When I got my first computer in 1987 it took so long to load a small game from cassette that I could go downstairs, boil a kettle, make a cuppa, and bring it back to my room, and the game would be just about finished loading. Now people are moaning about an extra half a second lol

How things change.

No, the narrative here is that things used to be fast and idiot JS devs have made everything bloated and slow.

Computers are thousands to millions of times faster. So yes, that's a true assessment.

> millions of times faster

Unless you had a 3KHz processor (and no processor ever had that), that statement is just not true.

It's not about raw clock speed, or a Zen 4 would be as fast as a Pentium 4 :)

It's a matter of use case - for simple web page or even a lightweight client-side app, maybe too big. But for tool with heavy data processing, I could see it being quite useful.

It's too bad WebSQL never took off, as that was sqlite built in to the browser.

From memory the wasm binary of wa-sqlite is ~1mb, which is certainly not nothing but is an acceptable one-time download size for many web apps. I've seen websites with single image files larger than that. Not an advocating for more bloated web apps, but a sqlite download might not be a deal breaker.

Those journal/wal files create a number of potential issues for user visible application files. E.g. a user could copy a database file into a directory that already has an old journal file, corrupting the database [1].

Or users could simply get confused about what to do with those files and contact support a million times just to ask why these files exist, whether they need to be copied as well, why they are getting large, etc.

These issues can be worked around, but I wonder how many apps that use SQLite actually bother doing that. Clearly not all of them [2].

Avoiding file corruption is non-trivial regardless of file format, but giving users additional ways to corrupt their data is never a good thing.

So I think for small, user visible application files that don't need any database functionality, the onus is still on engineers to justify their choice of SQLite.

[1] https://www.sqlite.org/howtocorrupt.html

[2] https://forum.audacityteam.org/t/aup3-wal-file-remains-and-l...

>Or users could simply get confused about what to do with those files and contact support a million times just to ask why these files exist, whether they need to be copied as well, why they are getting large, etc.

This reminds me of a college roommate who was dissatisfied with his computer, likely due to malware from sketchy sites. He decided to delete large or suspicious files from window’s system32 folder. By suspicious I mean he didn’t like the file name.

He had to get a new computer later in the semester.

Decades ago I troubleshot a Solaris server that wouldn’t boot, and discovered the kernel file was missing. I suspect someone had tried to deal with a too-small root partition by removing large files.

You could simply turn off WAL for a simple application database, if you were really worried. You lose some performance and scale, but it should work fine.

Setting the journal_mode to OFF or MEMORY is a sure-fire way of corrupting the database. It's not fine.

https://sqlite.org/pragma.html#pragma_journal_mode

If the application crashes in the middle of a writing a transaction, yes. You can work around this if you care to, and I don't think it's anything specific to SQLite. What happens if your application crashes in the middle writing out a JSON file?

I had actually meant JOURNAL_MODE=DELETE, though. This would not technically use a WAL file, though there would be a rollback journal file that exists only for the duration of the transaction.

>What happens if your application crashes in the middle writing out a JSON file?

The usual way to deal with this is to write to a temporary file and then atomically replace the original file with that temporary file. You could do the same thing with an SQLite database. This is the workaround I was referring to earlier.

journal_mode=delete may not be the worst compromise, but I hate the idea of exposing users to potential database corruption, even in a relatively unlikely event.

> 1.4. Mispairing database files and hot journals

I’m surprised it would corrupt the database. The database could have included a unique identifier as well as in the journal and refuse opening the database if the identifiers don’t match.

Uniquely identifying a database and its journal/wal files would prevent just one of the five mispairing cases they mention. And it's the least likely case.

There's one big obvious one. Git and SyncThing.

If the user could benefit from either of those, atomically updated plain files are great. Otherwise, SQLite is perfect.

Of course there's always workarounds to use SQLite in memory and dump to SQL code and such.

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.

For numeric values only, any advantage of using sqlite over let's say parquet with Apache Arrow/duckdb?

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.

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).

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.

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.

They did just release DuckDB version 1.0, so it should be getting much more reliable and stable.

They said 1.0 means “we aren’t going to break the disk format backwards compatibility anymore” other than that, it’s just a number and doesn’t have any power to make the software more reliable. It really depends on their priorities how much they’ll actually stabilize my use-case. I’ll probably check back in a year and see if things improved.

SQLite encodes integers in a variable-length format, so it can be very space-efficient compared to always storing integers in 4 or 8 bytes.

Strangely enough I'm doing the exact same thing. My case seems a bit more complicated since we're dumping not only long arrays of floats, but also a bunch of miscellaneous data (datetimes, unique datapoints, ids) right before/after those numbers. So it's even more of a nightmare... Some of these files have version numbers in the 30's (oh yeah, and file versions are based on file size.)

And did I mention that the file handles are kept open for the whole program duration (might be multiple hours) ? That's fun, data loss is almost a daily occurrence. It's the main thing I'm refactoring right now.

Well, with an ORM you put everything in data structures, and write them.

Most data fits into hierarchical data structures that don't form a complicated memory graph with references.

Once you know how to write primitive types and arrays of them, you can write objects, and it means you can write anything (without having to worry about writing "before" or "after").

And yes, switching to SQLite absolutely addresses the "perpetual fopen” problem.

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

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).

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.

Can you explain what you mean about needing to be careful about it?

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".

Thanks!

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.

This. In my testing some years ago, trying to make a basic Roguelike game with the state stored entitely in SQLite, the in-memory db could support reading the visible portion of the map and positions of entities at 60 FPS with time to spare, on a modest machine, and with slight FFI penalty as the code was written in Common Lisp (SBCL). So while you may not want to do this for a first-person shooter, there are many types of games that could in fact live entirely in SQLite, save for some transient visual FX.

I'm pretty far removed from game dev but curious.. is the in mem sqlite DB the only representation of game state or is it just 'mirroring' the 'real' values in ram? like if there's a Score on the HUD, are you doing a SQL select to read the value on every frame?

Or is this just a way to serialize and deserialization the game state to automatically save the game so it could be reloaded if it closed/crashed without explicitly running a 'save game' function?

> if there's a Score on the HUD, are you doing a SQL select to read the value on every frame?

Yes. That was the point of my experiments, after I realized that good chunks of the data structures I set up for my game look suspiciously similar to indices and materialized views. So I figured, why waste time reinventing them poorly, when I could use a proper database for it, and do something silly in the process?

In a way, it's also coming back to the origin of the ECS pattern. The way it was originally designed, in MMO space, the entity was a primary key into component tables, and systems did a multiple JOIN query.

I followed this line of thinking last week due to curiosity w.r.t ECS & SQLite. I found that the bottleneck was not on the reads, but on the complexity of the writes (iterating each entity and subsequently you have num_systems X num_components writes). You can actually avoid this entirely if you write your systems in SQL.

Since I had already thrown out logic & reason for the sake of curiosity, I took it a step further and learned that the Bun JS runtime actually has SQLite baked in, which allows you to create a :memory: db that can be accessed _synchronously_ avoiding modification of most ECS implementations. (I'm not familiar with the larger SQLite ecosystem, but being a largely TS developer this was very foreign to me)

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.

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

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://pack.ac/

works faster AND more compact than .tar.zstd and .7z.zstd (patched 7z with zstd compression)

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.

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

Thanks, I did a quick google for something like this before writing my comment, but your link did not come up.

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

Reply on news.ycombinator.com