I am convinced of SQLite. For many use cases where other databases like MySQL, MariaDB or PostgreSQL are often used, SQLite would suffice, given the right design and architecture.

Wesley Aptekar-Cassels describes in “Consider SQLite” how scalable SQLite is and why SQLite is not just a toy database.

I can confirm this as well: I use SQLite for my blog as well as for my link shortener and I have never encountered the problem that SQLite is too slow. Rather, SQLite simplifies a lot of things because it integrates directly into the application and doesn’t require a separate database process. Also, I might guess that it offers more performance for such use cases simply because communication within one process is faster than across multiple processes.

On the whole, I think using SQLite is a good tradeoff for a lot of projects, including webapps that expect to have a potentially large number of users. As long as you don’t expect to need tens of thousands of small writes per second, thousands of large writes, or long-lived write transactions, it’s highly likely that SQLite will support your usecase. It significantly reduces complexity and operational burden and eases testing, with the primary downside that it’s somewhat harder to get levels of availability and uptime that almost no one needs in the first place.