One limitation of SQLite is that it doesn’t support any kind of “notify me when some other process does X” operation. (If you Google it, you’ll find sqlite3_update_hook, but that only works for updates performed by the same process.) If you want to use SQLite as an event queue, you can have one process writing rows to a table and another process reading them, but you need some external signal to tell the second process “wake up, there’s new stuff in the queue”. Or you can have it poll on a timer, but that’s suboptimal in many different ways.
Which is topical, because watching for updates is a core feature of Redis streams (and Redis already had pub/sub channels before that). For that use case, SQLite is too little, even if your needs are otherwise quite basic.
Unfortunately, this difference in capabilities seems to be partly a result of limitations in the underlying OS APIs. SQLite uses POSIX advisory locks to lock ranges of the database file, but I don’t think there’s any similar API that provides an event or semaphore associated with a given file, instead of a lock. There are plenty of messaging APIs that aren’t associated with an arbitrary file – there are semaphores, message queues, and shared memory objects, in fact two sets of APIs for each of those (SysV and POSIX), plus signals, etc. But those all have their own namespaces, and if the two processes trying to synchronize with each other are in different containers, they might not share those namespaces. There are Unix sockets – those are a decent option, but they require one process to set itself up as the server, which is a bit weird in the SQLite model where all the processes are on an equal footing, and any may quit at any time. They also don’t work over NFS (whereas locking does, at least sometimes). You can try to mmap a regular file and then treat it as shared memory, but that’s not guaranteed to work in all cases, and again doesn’t work over NFS. I suppose you could try to abuse a lock as a semaphore, but that has its limitations…
But it’s not like many people use SQLite over NFS anyway. Whatever the approach, I’d love to see a “SQLite for notifications”. It would probably be a pretty simple library, but with the needed bells and whistles like bindings to higher level languages. If a library like this exists, I’d be very interested to hear, because a while back I searched for one in vain.
