SQLite Forum

Updating in-process connections on write

I'm using the node-sqlite3 library in my program and I'm opening 2 connections to the same DB file in the same process, one read-only (RO) and the other for transacting (RW).

I'm doing this so that reads from the application don't see intermediate values during transactions, but maybe that's misguided. In any case it seems easier than wrapping everything in read transactions.

The problem I'm having is that sometimes, after completing a write transaction, the RO connection doesn't "see" the change. I would have expected connections from the same process to notify one another.

As a workaround, I'm setting pragma user_version in the transaction and then polling the RO connection (using a prepared query) until it sees the same user_version.

Is there a better way to do this?