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.