Multiprocessing SQLite

Someone asked me the other day if multiple processes could connect to SQLite. I didn't know the answer; thankfully, it's addressed in the SQLite FAQ!

SQLite allows multiple processes to have the database file open at once, and for multiple processes to read the database at once. When any process wants to write, it must lock the entire database file for the duration of its update. But that normally only takes a few milliseconds. Other processes just wait on the writer to finish then continue about their business.

The context of the discussion was around replacing a client/server database running on the same host device as many other processes. For example, if you are running Postgres in a Docker container on the same device as your application, then you could potentially replace it with SQLite. Since SQLite is just a file, then you can just mount it in a volume shared by each service.

Running an application like this raises a few questions:

1. Which process is responsible for creating and updating schemas? How do you coordinate changes with multiple processes in different languages?

2. What happens in the case of busy waiting? How do you prevent one process from starving the connections of other processes?

3. What impact does file-level locking have on performance?

I'll leave the answers to these questions for another day. In the meantime, my takeaway is that application architecture can take many forms. Client/server databases are an easy choice for most usecases. However, I can see the appeal of using SQLite in an embedded environment, especially since it provides multiprocessing for free.