Lots of people saying that SQLite is super high quality and finding a bug is so rare. It’s not. I found one on a simple query utilising WHERE EXISTS [1]. Reporting it to a weird forum was also a horrible experience.
It’s high quality software, don’t get me wrong, but the infamous 100% test coverage doesn’t make it somehow immune to issues, or imply that the issues you do find are of a certain level of complexity. Nothing is back and white like that.
What exactly was a 'horrible experience' with the report you filed? Richard himself promptly replied in less than 24 hours that the issue was resolved and previously seen by Firefox too. I am at a genuine loss, what was horrible here??
It was a while ago, but if I recall I ended up having to sign up several times, the post failed to be created for some reason and something else. There was a captcha involved I think.
I don’t remember the specifics, but I do remember coming away from it with a feeling of “wow, that was an atrocious experience. I wonder what the drop off rate is”
Perhaps a while ago... For quite some time SQLite official forum has been running its own version of Forum software (part of Fossil). It allows Anonymous login, with captcha (again, Fossil handles), yeah, but this is nothing unexpected. So in general, the path to Forum presence is fairly unimpeded, well, it is moderated. Reasonably stated issues seem to get attention.
Though, some issues indeed need a push to be recognized as such, as it's a public forum, so other users may express their "other" opinions...
All in all it's Freedom of Reasonable speech in action.
I believe there's a different channel for reporting security-related issues. Again, it's through the Forum, but there's a private message feature for signed-in users.
Sounds about right, most forum software is buggy and fails, but those failures don't get sent to the maintainers. It just waits until someone whom knows the maintainers says "uhhh it's broke.".
in situations like this, I typically report bugs directly to members of the core team individually, with gory details, and explina that I tried the forum approach and it failed for me.
> Lots of people saying that SQLite is super high quality
I think the point most of those folks are making, is that SQLite is good enough where most developers think "Psh, I will use [HEAVIER DB SYSTEM THAT SLOWS OVERALL DEVELOPMENT TIME]" even if it is a better long term solution.
It's about bikeshedding, SQLite really is good enough for most projects and its a shame it still has such negative connotations.
I'll plead guilty to having this impression until fairly recently, but it's an incredible database. While we were all not taking it seriously, SQLite was quietly getting better and better, year after year. You do that for a few decades and you've got an incredible piece of software.
“Heavier” and “lightweight” are such abstract terms in software.
If “heavier” just means more LoC — sure, there’s more complexity in more LoC but also more problems solved. There’s a reason people tend to use the latest Linux/macos/Windows as opposed to the very lightweight Apple II OS from 1978.
Defaulting to, say, Postgres doesn’t seem so bad to me. It solves more problems than SQLite and “lightweight” is not really a concrete benefit for SQLite. It’s at least one level removed from speaking to a real problem.
I read them as "more/less complexity" not "more/less code". Postgres is heavier weight because it has more complexity; it has more features, it's networked, it has a more involved configuration process. They both have different advantages and can with in problem spaces the other can't.
This is quite unlike the Apple II, which is outmoded and requires a dedicated hobbyist to get working.
Postgres is an excellent default, but preferring lighter solutions does solve problems. It eliminates failure modes and cognitive load. As engineers we seek to eliminate the irrelevant to focus on the interesting. If you can use SQLite and avoid shipping a series of containers, and instead ship a single binary, you've eliminated things to think about.
Neither of them is a silver bullet and you'll be a better engineer if you can do both.
This is an enlightening answer. Part of my issue with “lightweight” is that is is vague. I see it used all the time. In this case, if people mean “way less configuration than Postgres,” and “does not need its own service and process(es) to manage,” that makes total sense. And it also gives a hint as to the tradeoffs.
Lightweight means I don't need to install some shit on a server somewhere and hope that its reachable at the time I need to access it. If I'm a process running then I loaded from a disk somewhere which means I can in-process sqlite to write to that disk.
Not really. Heavier and lightweight usually refer to the amount of features and requirements of a piece of software.
In that regard, it's easier to see which of PostgreSQL and SQLite is lighter. PostgreSQL requires a separate process running with its own config, plus the library to communicate with it, plus all the things Postgres does... On the other hand, SQLite is just a library that reads files in a certain format.
> It solves more problems than SQLite and “lightweight” is not really a concrete benefit for SQLite.
But it is a concrete benefit. Sometimes you'll have restricted environments because either by power or by permissions, you can't install Postgres or any other database server (e.g., mobile phones or embedded software). Or sometimes you just don't want the user to configure their postgres instance and your software for just a few tables (e.g., system utilities/small services that just need a simple database).
Here's one concept of heavy vs light: I have often wished I could just email a small Pg db to a colleague. In the same way I can just attach a single file to an email and transmit an entire SQLite DB.
I'm not dissing Pg. I really love Pg and I understand that it's built the way it is for good reasons. But it sure would be awesome to pg_dump and have a single tar that could be "run" with a single pg command without worrying about which version is required, what configuration is required, etc.
When that convenience is the most important requirement, SQLite wins. But that is hardly ever the biggest consideration in which RDBMS I choose.
> Here's one concept of heavy vs light: I have often wished I could just email a small Pg db to a colleague. In the same way I can just attach a single file to an email and transmit an entire SQLite DB.
The closest to this I've gotten is being able to run MySQL/MariaDB/PostgreSQL/other solutions in containers locally and sending archived data directories, with which they can be launched anywhere else locally, or on server.
1. run PostgreSQL in a container, e.g. https://hub.docker.com/_/postgres , use a bind mount for the data directory, /var/lib/postgresql/data
2. once you want to share, use tar/something else to archive the local bind mount directory
3. send the archive and the command to run the container through e-mail or whatever else you prefer
And on the receiving end:
1. receive the run command and attachment
2. unarchive the data directory into a folder
3. run the container with the provided command
It's not perfect, but it's one of the more portable methods I've found (though there are file system issues between Linux and Windows sometimes, like when running PHP apps).
It also causes problems. Now I have to worry about multiple processes instead of just one, and I need to upgrade it separately and test them both together.
I remember switching from postgres to MySQL and there was definitely a learning curve, so even without deployment costs (admittedly the same for mysql -- which were a real thing back then before RDS or Aurora or whatever hosted postgres)... It probably would have been better to use sqlite
> but the infamous 100% test coverage doesn’t make it somehow immune to issues
Infamous in what way? While I totally get that 100% coverage may be impractical for many projects, I’m also not seeing how less coverage would have improved things. And I highly doubt the SQLite team ever claimed they were immune to bugs!
It’s high quality software that is being pushed well past its intended use case. Maybe it will work out fine but rqlite is taking something designed as an on disk file format for one program and trying to use it as a network distributed concurrent database system. It would be surprising if they didn’t expose bugs in SQLite.
There are database systems that have been around for many years built from the ground up for this use case.
rqlite[1] author here. To be clear rqlite is using SQLite in a completely conventional manner. Nothing about the distributed nature of rqlite impacts on SQLite, since each rqlite node runs its own complete copy of SQLite.
This bug can affect anybody using an in-memory version of a SQLite database. That was the point of writing the C unit test.
> It’s high quality software that is being pushed well past its intended use case. Maybe it will work out fine but rqlite is taking something designed as an on disk file format for one program and trying to use it as a network distributed concurrent database system. It would be surprising if they didn’t expose bugs in SQLite.
Expensify is pushing millions of queries/sec by layering Bedrockdb over top of SQLite. You can go a long way and do amazing, unexpected things with a very solid foundation.
Not sure of SQLite's interpretation, but in general 100% test coverage "only" means all lines are executed in some piece of test. It doesn't necessarily indicate correctness of each line being checked.
Not to mention one can have multiple logic branches in a line. Or bugs relevant to only some subset of inputs (e.g. works fine for positive numbers but fails for negative is a classic example)
Yup. If you consider that a simple int can have billions of states, and that you can have 100% test coverage while testing just one of those, 100% really doesn't mean much. And with several variables, the total state space quickly becomes almost infinite.
The first couple of responses seemed a bit dismissive and impolite to me. Quickly remedied by a courteous and professional response by Richard Hipp.
I'm talking about things like, "You should not expect this to get a lot of attention on a Sunday. That's a slow day here.". I didn't see anything in the initial post that implied OP was expecting an immediate answer. And then the snarky, "This missing return makes me think you dislike or ignore warnings, which makes me want to eagle-eye your code more closely.".
I don't know, maybe I'm reading more into that than I should.
It’s high quality software, don’t get me wrong, but the infamous 100% test coverage doesn’t make it somehow immune to issues, or imply that the issues you do find are of a certain level of complexity. Nothing is back and white like that.
1. https://sqlite.org/forum/forumpost/452888d3b1?t=c&unf