Hacker News Reader

Comment by dijit | Hacker News Reader

it's important to note that the author of SQLite wasn't "competing" with RDMS engines -- it's competing with flat file access.

To that goal, I think it's wildly successful, instead of writing files I almost always reach for SQLite first.

When systems share a database, I, like you, reach straight for postgresql. Most people reach for mysql, which I do not prefer.

View on HN

Firefox uses SQLite for localStorage. When Firefox is force quit, the database becomes corrupted. https://bugzilla.mozilla.org/show_bug.cgi?id=1341070

This bug has occurred to me personally multiple times.

Does it really have advantages over a flat file?

Sqlite is certified for use in planes. This requires a high level of tests.

I think the odds are likely that Firefox is doing something unsuspected.

https://www.sqlite.org/testing.html

- Four independently developed test harnesses

- 100% branch test coverage in an as-deployed configuration

- Millions and millions of test cases

- Out-of-memory tests

- I/O error tests

- Crash and power loss tests

- Fuzz tests

- Boundary value tests

- Disabled optimization tests

- Regression tests

- Malformed database tests

- Extensive use of assert() and run-time checks

- Valgrind analysis

- Undefined behavior checks

- Checklists

"Valgrind is perhaps the most amazing and useful developer tool in the world."

It seems that about once a month I read something that makes me like SQLite even more. I guess that they like Valgrind as much as I do is the reason this month.

Valgrind is fine if you can live with the false positives, limited stack validation and execution slowdown. In a development environment, AddressSanitizer is a far better choice.

I am trolling on the person you to whom you replied...

<<This bug has occurred to me personally multiple times.>>

Hello, I am AirBus engineer (not really, only pretend). Does my plane crash? No, it does not!

Dear Random Internet Person who complains that open source software is broken. Are you joking? We are talking about SQLite!!??

SQLite has whole pages dedicated to "why you should NOT use SQLite". How many open source projects are /so/ good they can do this? Incredible!

The test coverage feels like NASA.

I use Firefox almost exclusively and have never encountered this.

Whatever capabilities SQLLite has to recover corrupted or inconsistent storage, it certainly exceeds what you get with flat files (none, unless you implement it yourself).

Per that bug report, you are likely to encounter it at https://codesandbox.io/s/sentry-js-sdk-unhandled-promise-fuq....

Which suggests an application error to me. I'd give decent odds to the real problem being some bounds check or pointer writing to the wrong place. To prove it, I'd instrument SQLite to log every single query that it was asked to do, then try to run those queries outside of the application. If the queries work fine, then the copy of SQLite in the application is being corrupted somehow.

In which case it really isn't fair to blame SQLite.

What's getting corrupted is Firefox's own SQLite DB.

I'm perfectly aware of that.

The question is how it is getting corrupted. Is it that SQLite has a bug? Or the copy of SQLite in Firefox is getting corrupted first?

Given the complexity and relative levels of testing of those two products, my bet is that the problem is Firefox.

This is quite surprising. SQLite is also used in embedded where you don't have comparatively civil "forced quits" but also straight power offs. SQLite seems to do everything it can to handle this gracefully.

if you're corrupting sqlite files you're for sure going to corrupt other formats in a force quit situation.

Why would that not happen to any other form of flat file storage? When you force quit a process, unless there's some nuance I'm unaware of, you're not only bypassing any graceful cleanup but there could be an FS operation in progress. Although I'm certain there are mitigation techniques, I would never expect a process to guarantee no file corruption when it's forcefully quit.

Interestingly enough, I don't think I've ever had this issue. I've force quit Firefox many times in the past (not often, but I'm sure I've done it countless times in the 10+ years I've been using it) and I still have localStorage data intact. There's old crap in StackEdit.io, which uses localStorage, that I haven't touched in years. (no, I have neither that service or Firefox itself set up to sync with anything)

EDIT: Maybe it's OS specific? I've been on macOS, and maybe there's something about other file systems where corruption is more likely to happen with a force-quit? Just throwing darts here.

I believe for a proper implementation it would be done transactionally. So if the program crashed then that record wouldn’t be written but the DB wouldn’t be corrupted.

I force-quit Firefox regularly (easiest way to get my tabs to stay open), on both Linux and Windows.

I've never seen any error messages from Firefox suggesting that anything has been corrupted.

Are there other symptoms that I would expect to see, if this corruption is happening?

Some sites break due to not expecting the exception being thrown– deviantart.com won't work, for instance.

Even when systems don't share a database, if operational concerns - disaster recovery, failover migrations, metrics and monitoring, anything like that - matter to me, I'm reaching for PostgreSQL or MS SQL Server. I hear I also need to get over my 20 year old grudge against MySQL.

SQLite is really only my first choice for hobby projects and desktop apps that aren't running on the JVM.

> I hear I also need to get over my 20 year old grudge against MySQL.

I hear this too - but silently truncating my data (causing a great deal of data loss and grief for me) is likely going to continue to bias me against MySQL for the foreseeable future.

That was configurable for ages and default is on strict for a long time now ...

Compatibility with old decisions vs. doing the right thing™ is always a pain ...

> That was configurable for ages and default is on strict for a long time now

I'm not saying I'm being rational in holding my grudge. I got bit by that "bug" _bad_ early in my career and the scar tissue lingers.

Ye I believe its best to think of Sqlite not as a database per se but more as a file format, kinda as you would a word doc but a file format that doesn't suck and is absolutely amazing at storing information.

And retrieving it, too.

If it is mostly text, then I would say git is better suited.

git is ACID?

I still believe Sqlite is the appropriate store. Unless.. does Git handle power issues elegantly? What happens if you yank power in the middle of a commit? Sqlite is designed to handle that.

Let's see:

- A - yes, update HEAD/branch text file is the key atomic update

- C - not sure how to apply this in the context of git, because commited files' content is kinda free form by design, no schema, no consistency problems

- I - there should not be concurrent use of commands, so no. practically this is mostly important for pushing to remote repository, and there it's handled by rejecting the push that comes second, if you make two commits from the same base and push them at the same time

- D - yes

that's one of the reasons I love Fossil so much. Way better backend than Git.

one of my team members really pushed fossil, and we did like the integrated wiki/ticket system. not bad at all.

... but it was so slow compared to git! at everything. merging, pushing, pulling ...

you did not experience same?

Fossil has rebasing? I thought they felt strongly against rewriting history, which is the biggest reason I am not considering it.

oops. corrected. thanks.

my main beef with fossil was performance. but i should clarify that this was 5 years ago or so. so maybe it's ok now.

For context, before the rise of SQLite, BerkeleyDB was often used for local file databases.

I never programmed for berkelyDB but I don’t believe it was SQL, so I think that’s what made SQLite so successful. The combination of an SQL api backed by local files

BerkeleyDB was a key/value store. I'm not sure how much uptake it actually had vs other solutions. Prior to widespread availability of SQLite3, it was not unheard of to write your bespoke format if you needed something sophisticated.

My exposure to BDB was through sendmail, spam assassin, and subversion. All three a big chunk of my job in 2006.

BerkleyDB is NoSQL and is internally used in popular No-SQL solution like DynamoDB.

I actually saw a really interesting article (probably on here) a while back about using SQLite files as your applications' document storage model, and I really like it. Like if you wrote a word processor, you could include a table of edits that would let you recreate Google Doc's timeline feature, among other things. I thought that was a really neat idea.

Apple's Core Data persisted-object-graph engine (kind of like Hibernate, I guess?) uses SQLite as its default storage backend.

There is a lot of macOS/iOS software using Core Data who already have "SQLite as an application document storage model" without even realizing it.

https://www.sqlite.org/appfileformat.html

[1] https://actualbudget.com/blog/porting-local-app-web

[2] https://news.ycombinator.com/item?id=23508923

If competing with flat files, and assuming you don't need concurrency, I don't think it's better than just using one or more json or csv files. Those are much easier to manipulate and manage (same benefits in terms of admin and backup), and you don't need to use a database driver in your code.

Reply on news.ycombinator.com