Using sqlite3_update_hook

[ceymard]

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).

[JoshuaWise]

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)

[ceymard]

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

[JoshuaWise]

😃

[wmertens]

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

[JoshuaWise]

@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

[wmertens]

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…

[JoshuaWise]

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.

[crossle]

@JoshuaWise No register function?

[crossle]

The mean function function

[tantaman]

@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

Using a temp trigger allows you to create a trigger that invokes functions only installed on the current connection. You still, however, have the problem of not seeing changes made by other connections with this method.

[xyc]

Is there any interest in re-opening this issue? sqlite3_update_hook would be useful in many local first app use cases where reactive updates is a common scenario.

For example, the implementation of useLiveQuery in expo-sqlite uses the update hook:

https://github.com/expo/expo/blob/75172f0339d707a685c60e72e70a7ce4e90c9080/packages/expo-sqlite/ios/SQLiteModule.swift#L433-L457

[mceachen]

The trigger approach mentioned in #62 (comment) is substantially more flexible and doesn’t have the surprising behavior of sqlite_update_hook (be sure to read all the comments above, there’s good stuff there!)

[xyc]

Triggers are flexible in regards to update individual table update, but how will it handle constantly changing or user created schema, without the need to create trigger on every table?

Also curious to learn what is the surprising behavior of sqlite_update_hook.