Disclaimer: It's been a while since I tried to use SQLite for anything.
I've never really felt that it was actually easier to use than a client/server DBMS. I can imagine that it's good for some kinds of embedded applications, but for normal web applications I don't see any improvement in usability. Juggling files is not any easier than setting up a connection.
And there were always annoyances associated with the simplicity. All those "complex features" in a real DBMS turn out to be useful sooner than you think (and I don't mean "once in production" I mean while still in development). Even the simplest applications can usually benefit from good support for dates and times.
I think SQLite just drew the line in the wrong place for most applications (although it may serve a niche quite well). Oversimplification requires the developer to reinvent, which results in a much more complex system overall (or one that at least requires more work to develop and maintain). Key/value stores have similar problems for general-purpose application development.
1. With the PDO (or any ORM) setting up and "using" SQLite is as easy as any other database.
2. There are several really nice front-ends to SQlite, along with the phpmyadmin like app for SQlite. Like MySQL you'll never have to use the command line (for most operations).
3. SQLite is now included with just about every scripting language (except MS' langs - where it's just a matter of downloading the app and an ODBC driver).
4. "Because it requires no configuration and stores information in ordinary disk files, SQLite is a popular choice as the database to back small to medium-sized websites." - sqlite.com
Simple websites. Would I run this site on SQLite? Of course not. Any shopping site with a million users? No. But a personal blog? Yes. A company website used for marketing and information (and maybe a few Contact US pages)? Yes.
There are millions of sites where using MySQl is over-kill. But everyone uses it because, like PHP, it's always there.
5. SQlite uses SQL. Very few commands aren't available.. like you can't write to a View. But I think that's true in MySQl too. There's a small list on sqlite.com. It even has transactions.
Simply put, if you don't require writing to the db from multiple sources (and lets face it, 80% of the sites out there don't.); running your app or site on sqlite is fine.
"setting up and "using" SQLite is as easy as any other database"
That's exactly the problem. If it's easier, then maybe I'll use it. If it's just as easy, but does less, I have no reason to bother using it, for two reasons:
1. There is non-zero risk that I will need one of the other features.
2. Even if I don't, why would I want to spend time learning something if it isn't better (at something) than what I already know how to use?
I haven't seen any compelling reason to use SQLite outside of the mobile or embedded space.
It's not. I'm just pointing out that there are alternatives to MySQL. And for small sites/apps that don't require something like Postgres; that can be SQLite.
Frankly I miss the days when an easy to install and use database was available to the masses. Remember Paradox, dBase and Foxpro? Sure all those database where powerful (xBase, for example) and used to develop complex applications. But they were also used for simple applications.
I would love to see something equivalent to those online. Oh wait, there is; SQLite.
SQLite is not a real solution for anything needing to support more than one user at a time. It's fine for things like managing bookmarks within firefox, but not for a full relational database replacement. The write access is single threaded (at least last time I used it).
My experience with SQLite is that if you're using it as more than an object-store, you'll be disappointed by the limited subset of SQL it supports. And if you do just need an object store, Mongo is often a better fit and gives you flexible schema.
