I see SQLite as a file-format with a SQL interface.
I love the description from SQLite's website on the subject:
> SQLite does not compete with client/server databases. SQLite competes with fopen().
SpiceWorks is the biggest example of a product that doesn't scale because they've elected to use SQLite (and only SQLite). There's no hard limit on the number of devices, but most people say not to exceed about 1,000 devices. We tested it out against 800 devices. We started it on Friday. On Monday, it was still running.
The only other issue I've seen with it is with the data typing. Type inference is much weaker than strict typing, and sometimes it doesn't quite store things the way you'd expect. Granted, the last time I saw problems with that was years ago, but I've seen it.
On the other hand it is somewhat rare to see aplication that really needs multiple writers and would not be better served by full fledged database server.
SQLite is solid and trusted by many but for some unknown reason some people refuse to see it as a solution for their needs. I have seen many databases that could easily been replaced by SQLite.