My favorite lens on SQLite is that it is actually two things:

1. A robust durability implementation 2. A library of high performance data structure and algorithms

The fact this it's SQL is nice, but those two attributes are what make it great.

For example, I'm implement an in-process event log that I want to be durable. I started simple, but soon saw some edge cases and instead of playing whackamole I just swapped to using sqlite as an ordered kv store that gives me ACID.

Another example: ingesting multiple inter related datasets. Instead of a dozen hash maps in memory, I load them up into sqlite (no persistence) and then slice and dice as I need to.

It's a super useful tool.