People often ask about a way to share a SQLite database across multiple apps. Essentially they want to know how to use SQLite as a server and the most common questions relate to putting a SQLite database file on a shared network drive and accessing the database file from apps running on multiple computers. This is a really bad idea, as even the SQLite folks will tell you. I’ll quote the relevant section from their “When to use SQLite” page:
Situations Where A Client/Server RDBMS May Work Better
Client/Server Applications
If there are many client programs sending SQL to the same database over a network, then use a client/server database engine instead of SQLite. SQLite will work over a network filesystem, but because of the latency associated with most network filesystems, performance will not be great. Also, file locking logic is buggy in many network filesystem implementations (on both Unix and Windows). If file locking does not work correctly, two or more clients might try to modify the same part of the same database at the same time, resulting in corruption. Because this problem results from bugs in the underlying filesystem implementation, there is nothing SQLite can do to prevent it.
A good rule of thumb is to avoid using SQLite in situations where the same database will be accessed directly (without an intervening application server) and simultaneously from many computers over a network.
So what are your options when you want to share your database?
If you want to stick with SQLite then you’ll need to put something in front of it that can handle requests from multiple client apps. The most obvious solution is to create a web service by using a web app with WebApplication.HandleURL (or HandleSpecialURL). The web app can accept requests from multiple client apps (or any type — desktop, web, mobile, etc.), fetch the data requested from the SQLite database and then send it back as JSON. This works because the web app is the only app that is connected to the SQLite database.
The Eddie’s Electronics Web Service sample shows you how you might set this up: Examples/Communication/Web Services/EddiesWebService
Another option for setting up your own database web service is to use the Aloe open-source project, which gives you a robust framework for building web services.
To learn more about database web services, check out the Making Database Web Services video.
By the way, this is also why a regular web app can safely use SQLite with multiple users — the web app manages the multiple users but is the only app that is connected to the SQLite database.
If you want to stick with SQLite but would rather not create a web service, another option to consider is to use a product that puts a database server around SQLite. CubeSQL and Valentina have products available that can do this.
Your final option is to switch to an actual database server. PostgreSQL and MySQL are popular alternatives.
Top comments (2)
I'm using SQLite for a web application. I store primarily configutation and log data in there. It's working really well. If there are problems, they always originate from the (a bit lackluster) Java SQL driver, or from the SQLite browser UI which has some quirks as well. The only thing I wish SQLite had was full support for ALTER TABLE commands. I find myself creating new tables and copying data a lot, because many ALTER TABLE statements are not supported.
Yep, is an embeded database. Back the the days they were the only type of databases.
SqlLite is good for low devices or small storage data, it is heavily used on mobile apps for example, but I would not use it for a web service.
The embeded DBs are indeed used in intensive data, large scale services, as a local buffer, see RocksDB (a Facebook fork of LevelDB).