SQLite is a great embedded database, but is deliberately not designed for replacing a 'full' database server. It doesn't support highly concurrent usage, it can't be run as a network service, it has a comparatively weak type model.
All of these differences are actually assets for the embedded DB market. They could be fixed, but you would end up with a database that was a winner in neither space.
I think the killer problem with SQLite for businesses is that it essentially locks their data inside the application. With a full SQL server, the data is trivially exposed for use / integration with other systems.
From the description and benchmarks, it seems that the 'SQL API for BerkeleyDB', as a drop-in replacement for SQLite, should provide an easy fix for many SQLite-using projects that hit barriers dur to concurrency. See:
But, I haven't seen nearly as much discussion/declared-usage of this as I would have expected, given the benefits of 'SQLite API way beyond prototype/single-user scale'.
Does anyone have experience with it? Any theories why it isn't so widely used/known?
I would say when SQLite added the WAL mode they greatly improved the concurrency. In WAL mode SQLite has better concurrency characteristics than MyISAM which not too long ago was commonly used.
Thanks for the WAL pointer; it does sound like concurrency is better now. Benchmarking concurrency against MyISAM is a bit like benchmarking reliability against MongoDB though ;-)
Agreed, my point is that since people could (and still can) run mostly fine on MyISAM and MongoDB despite their lack of real concurrency, then SQLite in WAL mode should not have any problems for a small to medium website.
Note: Personally I would pick PostgreSQL any day, unless developing an embedded application.
All of these differences are actually assets for the embedded DB market. They could be fixed, but you would end up with a database that was a winner in neither space.
I think the killer problem with SQLite for businesses is that it essentially locks their data inside the application. With a full SQL server, the data is trivially exposed for use / integration with other systems.