Better-sqlite3: Using sqlite3_update_hook

Created on 29 Aug 2017 · 9Comments · Source: JoshuaWise/better-sqlite3

It would be nice to be able to subscribe to events using the sqlite3_update_hook function.

How about adding a setUpdateHook() method to the Database instance ?

I know that this function only fires for updates within the same database connection, but it would still be of use to me (probably).

Source

ceymard

Most helpful comment

You can do the same thing (and with more control) by combining triggers and custom functions:

db.prepare('CREATE TABLE data (a, b, c)').run();
db.prepare('INSERT INTO data VALUES (12, 34, 56)').run();

// This is your actual update hook
db.register({ varargs: true }, function logger(...values) {
  console.log(`row updated to (${values.join(', ')})`);
});

db.prepare('CREATE TRIGGER updateHook AFTER UPDATE ON data BEGIN SELECT logger(NEW.a, NEW.b, NEW.c); END').run();

db.prepare('UPDATE data SET a = 78').run();
// => row updated to (78, 34, 56)

JoshuaWise on 29 Aug 2017

👍2 ❤1

All 9 comments

You can do the same thing (and with more control) by combining triggers and custom functions:

db.prepare('CREATE TABLE data (a, b, c)').run();
db.prepare('INSERT INTO data VALUES (12, 34, 56)').run();

// This is your actual update hook
db.register({ varargs: true }, function logger(...values) {
  console.log(`row updated to (${values.join(', ')})`);
});

db.prepare('CREATE TRIGGER updateHook AFTER UPDATE ON data BEGIN SELECT logger(NEW.a, NEW.b, NEW.c); END').run();

db.prepare('UPDATE data SET a = 78').run();
// => row updated to (78, 34, 56)

JoshuaWise on 29 Aug 2017

👍2 ❤1

Didn't think about it. There is no need for this, then.

ceymard on 29 Aug 2017

😃

JoshuaWise on 29 Aug 2017

Nice! Will this trigger also fire if another connection updates the table? I think this use case warrants mentioning in the readme, btw…

wmertens on 6 Sep 2017

@wmertens this will only work if the logger() function is registered with the connection that tries to update the table. Otherwise an error will be thrown by SQLite

JoshuaWise on 11 Sep 2017

Aww. I'll just continue polling the DB then 😢. I could probably improve that by using filesystem monitoring and watching the DB file for changes…

wmertens on 11 Sep 2017

Yeah unfortunately that's the only way to read changes made by other connections. Even sqlite3_update_hook() only receives changes made by the same connection.

JoshuaWise on 11 Sep 2017

@JoshuaWise No register function?