SQLite Forum

Best way to observe database operations
> I have two process writing to a single SQLite file.

Are they wholly unrelated, or can they set up some sort of [IPC][1] channel?

If so, then my first choice here would be to create a [message queue][2] so that the DB modifying process can tell the DB reading process, "Hey, I just added record IDs 42 and 43!" or "Hey, record ID 44 just got nuked."

Several of the common message queue implementations are asynchronous and persistent, so both processes don't even have to be running for this to work. One side just stuffs its message into the queue, and the other pulls messages out in its own time until the queue is empty. This is not only convenient, it creates a decoupling point that can improve system scalability and stability as compared to a superficially similar system based on synchronous blocking IPC.

> ...using the OS to watch for file changes?

If you had to do it that way, then I'd build a message queue *in* SQLite. A common sort of scheme is to create a "work queue" table with timestamps that get updated on work item creation, work start time, and work completion. You index these columns so it's efficient to ask the DB questions useful to your application.

For example, you may wish to ask the work queue, "What work items have been added since the last time I checked, '2020-05-26 15:54:58'?" or "Give me a list of work queue items where neither the started-time nor the finished-time column are NULL."

If these suggestions are entirely inappropriate to your application, that is because you've posted an [XY Problem][3]. Instead of telling us how you expect the solution to be accomplished, tell us the actual problem and what you've tried in solving it.

[1]: https://en.wikipedia.org/wiki/Inter-process_communication
[2]: https://en.wikipedia.org/wiki/Message_queue
[3]: http://xyproblem.info/