SQLite appeals to the hacker because it is simple on the surface, complex beneath the surface, easy-to-use, and does its job well. however, replacing a traditional MySQL or PostgreSQL database with it, takes a bit of work, because it's not a drop-in replacement. so you see this push and pull between people of different strata advocating for SQLite, disparaging it in favor of PostreSQL, and some who try to engineer stuff around SQLite to make it a better fit for traditionally PostgreSQL-type situations.
if you've never tried SQLite on your own and you're comfortable with C, I'd recommend giving it a try, it's pretty dang cool.
Who is using this and why is it such a hot topic on HN?