Client / Server SQLite

SQLite is designed to operate as a library, linked into another application. This means that several independent processes may be accessing a database file at the same time. Concurrent access to a single database file is made possible by the use of "file locks", which only work reliable on local files. This is true of both Windows and Unix/Linux.

The above means that multiple machines cannot share a SQLite database. This can be overcome by embedding the SQLite core into a server process, and having remote clients access the server process, rather than the file. Using a stateless, RESTfull protocol makes access scalable (although of course the server process itself remains to have limited scalabilty - which is a separate problem to solve).

There are a few hurdles to overcome in such a design, amongst which:

• Lowering the cost of opening database connections. A RESTfull design cannot rely on session information and each request must stand on itself. The envisaged solution is to maintain a pool of open connections in the server. However, this has some issues as well (attached databases, user functions, etc.)
• Currently, high performance web servers seem to be built around non-blocking designs, either single threaded or with a limited number of concurrent threads. An example would be Lighttpd. The SQLite API is not necessarily non-blocking and would require some further scrutiny.
• Minimising round trips. A remote protocol should minimize request-response round trips in order to be fast (latency is some 5ms on a LAN and 50-200ms on the internet). A well designed protocol should handle common requests in a single round trip, or using pipelined, 'keep-alive' requests where a single trip is impossible.

The SQLiteDBMS, SQLiteServer and SQLite-on-sockets projects have done related work. None appear to use a stateless wire protocol.

Related work

SQLiteDBMS

SQLiteServer

SQLite-on-Sockets