Support for Data Change Notification Callbacks (sqlite3_update_hook)

[vlasky]

Hi,

It would be really great if we could have node-sqlite3 support Data Change Notification Callbacks, which are implemented in the SQLite C interface by the sqlite3_update_hook() function.

This would provide the ability to register a callback function in NodeJS that will be called whenever a row is updated, inserted or deleted in a SQLite rowid table.

My intention is for this exposed functionality to be used to implement a reactive SQLite database driver for Meteor, to be used in real-time applications that use Meteor's publication & subscription functionality. That is, a change to a row in the SQLite database will generate an event that will make the server publish the modified data to a connected client.

At the moment, such drivers have been written for MongoDB, MySQL and PostgreSQL. SQLite would be a relatively easy and perfect addition once the Data Change Notification Callback is exposed in NodeJS.

[kroggen]

Would not be better implemented via virtual tables?

Check also #702

[malhal]

Unfortunately sqlite3_update_hook() doesn't contain the data so it won't work. There might be other tricks to simulate an oplog for sqlite however, with varying levels of performance.

[vlasky]

@malhal it is not obligatory for the hook to contain the data - just getting the table name and row id is sufficient to narrow things down. Once the event fires, the code can then retrieve the row and/or query the table and then decide what to do.

I still very much would like to see Node.js support for sqlite3_update_hook() implemented.

[malhal]

Then it would be querying for every insert, you would need to batch all the ids into one select which will introduce a potential consistency problem. Furthermore you would still need to do a diff manually between the query result and the previous cached row, unlike oplog which already only includes the fields that have changed in an update.

This being said the hook methods should still be added to this node api if they are missing.

[thezealousfool]

+1

[daniellockyer]

Closing as a duplicate of #419