Add updateHook

A wrapper around sqlite3_update_hook. For now only as a low-level operation to Database. To be useful in projects it will probably need some wrapping in the worker but right now I have no idea yet how that should look.
sql-js · lovasoa · Mar 14, 2025 · Mar 11, 2025 · Mar 13, 2025 · Mar 14, 2025
commit 4c9602acd8b6b3cc02bc9d85eab2588b037d4fae
diff --git a/src/api.js b/src/api.js
@@ -71,6 +71,10 @@ Module["onRuntimeInitialized"] = function onRuntimeInitialized() {
    var SQLITE_BLOB = 4;
    // var - Encodings, used for registering functions.
    var SQLITE_UTF8 = 1;
+    // var - Authorizer Action Codes used to identify change types in updateHook
+    var SQLITE_INSERT = 18;
+    var SQLITE_UPDATE = 23;
+    var SQLITE_DELETE = 9;
    // var - cwrap function
    var sqlite3_open = cwrap("sqlite3_open", "number", ["string", "number"]);
    var sqlite3_close_v2 = cwrap("sqlite3_close_v2", "number", ["number"]);
@@ -239,6 +243,12 @@ Module["onRuntimeInitialized"] = function onRuntimeInitialized() {
        ["number"]
    );

+    var sqlite3_update_hook = cwrap(
+        "sqlite3_update_hook",
+        "number",
+        ["number", "number", "number"]
+    );
+
    /**
    * @classdesc
    * Represents a prepared statement.
@@ -1383,6 +1393,76 @@ Module["onRuntimeInitialized"] = function onRuntimeInitialized() {
        return this;
    };

+    /** Registers the update hook with SQLite
+        @param {function(operation, tableName, rowId)} callback
+             executed whenever a row in any rowid table is changed
+
+        For each changed row, the callback is called once with the change
+        ('insert', 'update' or 'delete'), the table name where the change
+        happened and the rowid of the row that has been changed.
+
+        rowid is cast to a plain number, if it exceeds Number.MAX_SAFE_INTEGER
+        an error will be thrown.
+
+        The callback MUST NOT modify the database in any way.
+
+        Only a single callback can be registered. Unregister the callback by
+        passing an empty function.
+
+        Not called for some updates like ON REPLACE CONFLICT and TRUNCATE.
+
+        See sqlite docs on sqlite3_update_hook for more details.
+       */
+    Database.prototype["updateHook"] = function updateHook(callback) {
+        this.updateHookCallback = callback;
+
+        // void(*)(void *,int ,char const *,char const *,sqlite3_int64)
+        function wrappedCallback(
+            ignored,
+            operationCode,
+            databaseNamePtr,
+            tableNamePtr,
+            rowIdBigInt
+        ) {
+            var operation;
+
+            switch (operationCode) {
+                case SQLITE_INSERT:
+                    operation = "insert";
+                    break;
+                case SQLITE_UPDATE:
+                    operation = "update";
+                    break;
+                case SQLITE_DELETE:
+                    operation = "delete";
+                    break;
+                default:
+                    throw "unknown operationCode in updateHook callback: "
+                        + operationCode;
+            }
+
+            var tableName = UTF8ToString(tableNamePtr);
+
+            if (rowIdBigInt > Number.MAX_SAFE_INTEGER) {
+                throw "rowId too big to fit inside a Number";
+            }
+
+            var rowId = Number(rowIdBigInt);
+
+            if (this.updateHookCallback) {
+                this.updateHookCallback(operation, tableName, rowId);
+            }
+        }
+
+        var funcPtr = addFunction(wrappedCallback.bind(this), "viiiij");
+
+        this.handleError(sqlite3_update_hook(
+            this.db,
+            funcPtr,
+            0 // passed as the first arg to wrappedCallback
+        ));
+    };
+
    // export Database to Module
    Module.Database = Database;
 };
diff --git a/src/exported_functions.json b/src/exported_functions.json
@@ -42,5 +42,6 @@
 "_sqlite3_result_int64",
 "_sqlite3_result_error",
 "_sqlite3_aggregate_context",
-"_RegisterExtensionFunctions"
+"_RegisterExtensionFunctions",
+"_sqlite3_update_hook"
 ]
diff --git a/test/test_update_hook.js b/test/test_update_hook.js
@@ -0,0 +1,43 @@
+exports.test = function(SQL, assert){
+  var db = new SQL.Database();
+
+  db.exec(
+    "CREATE TABLE consoles (id INTEGER PRIMARY KEY, company TEXT, name TEXT);" +
+    "INSERT INTO consoles VALUES (1, 'Sony', 'Playstation');" +
+    "INSERT INTO consoles VALUES (2, 'Microsoft', 'Xbox');"
+  );
+
+  // {operation: undefined, tableName: undefined, rowId: undefined};
+  var updateHookCalls = []
+
+  db.updateHook(function(operation, tableName, rowId) {
+    updateHookCalls.push({operation, tableName, rowId});
+  });
+
+  // INSERT
+  db.exec("INSERT INTO consoles VALUES (3, 'Sega', 'Saturn');");
+
+  assert.deepEqual(updateHookCalls, [{operation: 'insert', tableName: 'consoles', rowId: 3}], 'insert a single row');
+  updateHookCalls = []
+
+  // UPDATE
+  db.exec("UPDATE consoles SET name = 'Playstation 5' WHERE id = 1");
+
+  assert.deepEqual(updateHookCalls, [{operation: 'update', tableName: 'consoles', rowId: 1}], 'update a single row');
+  updateHookCalls = []
+
+  // UPDATE (multiple rows)
+  db.exec("UPDATE consoles SET name = name + ' [legacy]' WHERE id IN (2,3)");
+
+  assert.deepEqual(updateHookCalls, [
+    {operation: 'update', tableName: 'consoles', rowId: 2},
+    {operation: 'update', tableName: 'consoles', rowId: 3},
+  ], 'update two rows');
+  updateHookCalls = []
+
+  // DELETE
+  db.exec("DELETE FROM consoles WHERE company = 'Sega'");
+
+  assert.deepEqual(updateHookCalls, [{operation: 'delete', tableName: 'consoles', rowId: 3}], 'delete a single row');
+  updateHookCalls = []
+}