works faster AND more compact than .tar.zstd and .7z.zstd (patched 7z with zstd compression)
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...
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.
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.
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.
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.
That’s one hell of an endorsement.
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.?
'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.
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.
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.
How things change.
Unless you had a 3KHz processor (and no processor ever had that), that statement is just not true.
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.
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.
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.
Really convenient for the user, zero cost for the developer (same API for in-memory/fs db and smooth transition between them).
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?
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.
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)
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.
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.
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.
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.
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 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).
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.
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.
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.