News for hackers. Stuff that matters.
Re:Why SQLite succeeded as a database (2016) by jbob2000 on Wednesday February 19, 2020 @12:09PM (#22367526)
TL;DR because it's simple.
Re:Why SQLite succeeded as a database (2016) by gwd on Wednesday February 19, 2020 @01:23PM (#22368387)
I'd say there are a load of reasons, all of which combine together to make it unbeatable:

* Simple to embed into you application (no setting up a separate database, &c).

* Fantastically backwards compatible

* Public domain (so easy to comply with the license)

* High quality: Reasonably fast, reasonably efficient, reasonable feature support.

The lack of any of these would add significant drag. It's difficult to imagine any other project catching up and displacing it at this point.

Re:Why SQLite succeeded as a database (2016) by bob1029 on Wednesday February 19, 2020 @02:27PM (#22369076)
I personally cannot see any near term replacement for what SQLite offers. In my world, SQLite became the way in which I now manipulate a scope of structured data on a single logical disk. I mostly view it as a very reliable and friendly layer on top of basic file IO which also happens to be compatible with virtually every computer on earth. I can copy a SQLite database out of an embedded microcontroller environment and directly open it on my x86_64 workstation, edit some values, and then copy it back down without any fears of compatibility issues.
Re:Why SQLite succeeded as a database (2016) by umvi on Wednesday February 19, 2020 @12:39PM (#22367850)
Is it simple, though? I mean, reading about how they test it[1] leads me to believe it is not simple. Maybe it's simple compared to Oracle, and maybe using it is simple, but the actual implementation is definitely not simple.

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

Re:Why SQLite succeeded as a database (2016) by jbob2000 on Wednesday February 19, 2020 @12:40PM (#22367869)
I don't care about the implementation and I congratulate the developers on abstracting all of that complexity away from me. That's what good software does.
Re:Why SQLite succeeded as a database (2016) by umvi on Wednesday February 19, 2020 @12:46PM (#22367939)
I misinterpreted your comment. I thought you were saying SQLite was successful because SQlite itself (i.e. the implementation) was simple, not because it was simple to use.
Re:Why SQLite succeeded as a database (2016) by wpietri on Wednesday February 19, 2020 @01:24PM (#22368395)
For what it's worth, I use "simple" for talking about the complexity of the thing, and "easy" for the user experience. Quite frequently easy things have to be complex because the cognitive load gets shifted into the machinery.

E.g., compared with a Model T, modern cars are much easier but much more complex because the car itself understands things like adjusting the ignition timing and safely getting the motor turning, things that a Model T user had to understand if they wanted to avoid getting a broken arm before they got out of the driveway: https://www.fordmodelt.net/how-to-drive-ford-model-t.htm

Re:Why SQLite succeeded as a database (2016) by scottlamb on Wednesday February 19, 2020 @06:56PM (#22371457)
> I mean, reading about how they test it[1] leads me to believe it is not simple.

They are unusually thorough in their testing. I see that as a good thing, not a sign it isn't (relatively) simple. In fact, I don't think any other database engine is tested as thoroughly. I can't even imagine how much effort it'd take to do so for Oracle or even PostgreSQL, except that it'd be prohibitive.

Re:Why SQLite succeeded as a database (2016) by solarkraft on Wednesday February 19, 2020 @12:41PM (#22367876)
It's simple to use.
Re:Why SQLite succeeded as a database (2016) by umvi on Wednesday February 19, 2020 @12:47PM (#22367955)
Most of the time, yes, if SQLite supports the filesystem you are using. If the filesystem doesn't have a particular feature SQLite needs, it can be difficult to get working the way you want it to.
Re:Why SQLite succeeded as a database (2016) by danso on Wednesday February 19, 2020 @12:54PM (#22368056)
What scenarios/filesystems lead to these kinds of conflicts?
Re:Why SQLite succeeded as a database (2016) by umvi on Wednesday February 19, 2020 @01:22PM (#22368379)
Well the issue I ran into was that I wanted a SQLite db to have one writer and many readers. However, what I was seeing was that queries were failing because the DB was locked at the time of reading. The solution is to switch journaling mode to WAL[1].

However the particular platform I was developing for was an embedded system using the JFFS2 filesystem. JFFS2 doesn't support shared mmap, which is a feature SQLite needs for WAL.

So basically, it was big pain - I had to setup a tmpfs mount point where the WAL-enabled DB resided and then periodically sync it to flash.

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

Re:Why SQLite succeeded as a database (2016) by BubRoss on Wednesday February 19, 2020 @02:45PM (#22369265)
Why didn't you just do more granular writes and let the different processes lock the file? SQLite is impressively fast, it can handle a lot before you need a full database.
Re:Why SQLite succeeded as a database (2016) by umvi on Wednesday February 19, 2020 @03:48PM (#22369899)
I don't know how to do that.

Basically there is one process periodically dumping stats into a sqlitedb. There is an HTTP API that can retrieve said stats. How do I make it so the HTTP API doesn't occasionally fail when trying to read from the DB?

Re:Why SQLite succeeded as a database (2016) by BubRoss on Wednesday February 19, 2020 @07:30PM (#22371684)
Retry until it gets the lock and don't hold the lock longer than you need to to write to the db.

Really though if you are trying to read and write to a db concurrently and over the network, it sounds much more clear cut for something like postgres.

Re:Why SQLite succeeded as a database (2016) by umvi on Thursday February 20, 2020 @09:29PM (#22380478)
I don't really like the idea of a retry loop. Why doesn't sqlite have an API to just block until the DB is available?
Re:Why SQLite succeeded as a database (2016) by BubRoss on Friday February 21, 2020 @12:40AM (#22381162)
You asked the question and now you don't like the 'idea' of the answer. Use real numbers instead of feels, and again if you need real concurrency, use a real database.
Re:Why SQLite succeeded as a database (2016) by umvi on Saturday February 22, 2020 @10:02AM (#22391330)
"Retry" is not a solution; it's a workaround. Imagine if that's how opening files worked: "Oh yeah, if the file fails to open just retry a few times until it works."

There's a small chance even retrying 3 times you could fail all 3 times in a row. WAL journaling is a solution.

Re:Why SQLite succeeded as a database (2016) by franga2000 on Wednesday February 19, 2020 @01:19PM (#22368339)
Not exactly a filesystem, but it doesn't work on SMB shares, which is an enormous pain in the ass. And I'm not even talking about concurrent access - just one user. I get that SMB file locking is a mess, but making it a hard fail by default was a big mistake IMO as most develoers using the library don't make special cases to allow this.
Re:Why SQLite succeeded as a database (2016) by nomel on Wednesday February 19, 2020 @02:26PM (#22369070)
The documentation is very clear than SQLite over a network isn't a good use case, for reasonably sized data. Item 1 from "Checklist for choosing the right database engine" [1]

> 1. Is the data separated from the application by a network? → choose client/server

> Relational database engines act as bandwidth-reducing data filters. So it is best to keep the database engine and the data on the same physical device so that the high-bandwidth engine-to-disk link does not have to traverse the network, only the lower-bandwidth application-to-engine link.

> But SQLite is built into the application. So if the data is on a separate device from the application, it is required that the higher bandwidth engine-to-disk link be across the network. This works, but it is suboptimal. Hence, it is usually better to select a client/server database engine when the data is on a separate device from the application.

1. https://www.sqlite.org/whentouse.html

Re:Why SQLite succeeded as a database (2016) by thrower123 on Wednesday February 19, 2020 @02:00PM (#22368798)
I do wonder how much of the time that SQLite is used, that it wouldn't be even simpler to just dump things to an XML file.
Re:Why SQLite succeeded as a database (2016) by lurker458 on Wednesday February 19, 2020 @02:46PM (#22369271)
Here are some considerations why: https://danluu.com/file-consistency/
Re:Why SQLite succeeded as a database (2016) by xolox on Thursday February 20, 2020 @10:08AM (#22375549)
Thanks for sharing that link, it's on the most valuable things in this thread for me! (I loved SQLite already and one HN thread more or less isn't going to change that)