The big attraction of SQLite
Recently I came an obvious in retrospect realization about what is to me the big and game changing feature of SQLite. I'll put it this way:
SQLite gives you a powerful relational database that doesn't require you to run a server.
Running a separate database server is all sorts of hassle. You have to configure it, set up access to it, arrange for it to be started when the system boots up and then kept running, monitor it to make sure that it's still running, and so on and so forth. Often this will need either the involvement of your system administrator or even more hacking on scripts and configurations to run your own copy of the entire database server infrastructure. Then multiply this work for development and test database servers (because you're not developing or testing against the production database, are you), and so on and so forth.
(One of the many effects that this has is that it's simply not worth using a SQL relational database for small projects even when that might be the easiest approach if you had one handy. It's just too much bureaucracy.)
SQLite throws all of that hassle out of the window. There is no server and basically no configuration. You 'configure' SQLite by telling it the filename to store things in and access control is done by ordinary Unix permissions. Need a development or test database? Change the filename of the database in your configuration and you're done. SQLite can even run entirely in memory for the ultimate disposable quick database.
(And if you're developing as a different Unix user than the production application runs on, permissions will probably insure that you can't accidentally touch the production database even if you screw up. Not to mention the ease of setting up a copy of the production database; most of the time you can just copy the SQLite database file.)
At the macro level what this has done is make it practical to give even small low-hassle things access to a powerful relational database (and as a bonus it speaks SQL and you can do ad-hoc queries against it). If you think you need relational features there is now basically no reason not to use them. And it will probably be relatively easy to scale your program up to using a full sized server based SQL database if you turn out to need one of the big guns after all.
Or the real summary version: SQLite has made SQL databases into a lightweight thing.
(Well, of course. It's right there in the name. I'm slow so things take some time to sink in.)
I've been conditioned to think of SQL databases as very heavyweight things, but SQLite has changed this. With this in mind I think I'm going to be much more willing to assume and use a relational database in future projects, rather than try to glue together some sort of ad-hoc equivalent based on eg plain files. I'm not entirely convinced that I'll carry through on this because I like plain text files, but there's an inflection point where the filesystem makes an increasingly bad database engine (also).
PS: Yes of course if you need a fully featured SQL engine, SQLite is not really the thing for you. The same is true if you need something that stands up to high concurrency and high load and so on. I tend to think of SQLite as a relational database engine that happens to speak basic SQL for convenience.
Technological progress and efficiency
Sit down and ponder the fact that the fastest tools on your machine come from the 1970s and tool speed correlates negatively with time.
My reaction to this is 'isn't technological progress great?'
Tools in the 1970s had no choice but to be both screamingly efficient and generally relatively limited because they ran on machines that today we would consider extremely tiny. For one extreme example, the embedded CPU on your disk drives is almost certainly more powerful than the machines Unix originally ran on.
(Yes, your disk drives have onboard CPUs. Even and especially SSDs.)
Every step of improved computing since the 1970s has made feasible programs that are both more inefficient and more powerful than before, and so of course people have written them. Often these programs have been written in ways that are themselves more inefficient but faster in coding time. Garbage collection, widespread use of hash tables for everything, object oriented programming with multiple levels of indirection, dynamic types with runtime type dereference; any litany of modern programming techniques is also a litany of inefficiency. All of this inefficiency has been enabled by the relentless march of technological progress.
(Of course you can take this too far. You can take anything too far.)
All of this is great. We have not lost any possibilities in this technological progress; you can still write programs that are just as efficient as programs from the 1970s (although people generally don't). Instead we have gained possibilities. The march of technology has made it possible to do things that would have been crazy ten or even five years ago, and it will probably keep on doing so.
(I'm also a little bit dubious that the fastest tools really do date from the 1970s. I would not be surprised if the GNU grep family were faster than their V7 equivalents, for example.)