I'd also like to draw attention to the parent project Firebird. For some reason (probably its ties to Borland/Delphi?) it never became widely popular, even though it combines SQLite's ease of use with a proper fully featured database engine.
For example, it supports proper ALTER TABLE which doesn't force you to recreate tables and copy data around when you go beyond the most trivial use cases.
This is pretty much the only discussion I've found on this site:
https://news.ycombinator.com/item?id=22155260