Genuine question, why does SQLite get so much hate? Is it because it's SQL, or it's embedded, or what? I've used it for a lot of projects where I have to move the server around a lot and it's super convenient
SQLite gets a lot of love when it's used for the right job.
It's not a replacement for a full-blown RDBMS. If you need a database for a multi-user application, you'll probably be better served by something like PostgreSQL. For single-user applications, however, it's quite fantastic.
Huh? Not sure what you mean but it's not intended as a "server database". It is however used a lot in production as an embedded DB in applications. E.g. I've seen SQLite in lots of Android apps.