UpdateHook works on same connection only?

[tdang-github]

Hi,
Looks like UpdateHook works only on the same connection? This can be a problem when you're trying to integrate this SqLite driver into an existing framework that already has its own connection handling. Please confirm whether this is true. Any explanation and/or suggestion is much appreciated. Thanks!

Example test case below.

func TestUpdateHookDriver(t *testing.T) {
	dbPath := memdb.TestDB(t)

	conn, err := sqlite3.Open(dbPath)
	if err != nil {
		log.Fatal(err)
	}
	defer conn.Close()

	conn2, err := sqlite3.Open(dbPath)
	if err != nil {
		log.Fatal(err)
	}
	defer conn2.Close()

	updateHook := func(op sqlite3.AuthorizerActionCode, db string, table string, rowid int64) {
		log.Printf("Update: Op=%v, DB=%s, Table=%s, RowID=%d\n", op, db, table, rowid)
	}
	conn2.UpdateHook(updateHook)

	if err := conn.Exec("CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)"); err != nil {
		log.Fatal(err)
	}
	if err := conn2.Exec("INSERT INTO users (name, age) VALUES ('Alice', 30)"); err != nil {
		log.Fatal(err)
	}
	if err := conn.Exec("UPDATE users SET age = 33 WHERE name = 'Alice'"); err != nil {
		log.Fatal(err)
	}
}

[ncruces]

Of course it is per connection. You're setting it on the connection, why would you expect it to be global?

It's also per connection on SQLite:
https://sqlite.org/c3ref/update_hook.html

[tdang-github]

Thanks Nuno for the answer. I thought SqLite would notify any connection with such hook.

For context, I'm trying to replace the current driver mattn which is Cgo-based, with yours which is Cgo-free. Currently our framework opens the connection using the database/sql API, and sets the update hook via a "connect hook" (see code below). Is there an equivalent way to do that with your driver? i.e. setting an update hook without replacing the current connection method.

Code using mattn driver:

	db, err := sql.Open(driverWithUpdateHook, dbUri)
  ...
  
	sql.Register(driverWithUpdateHook,
		&sqlite3.SQLiteDriver{
			ConnectHook: func(conn *sqlite3.SQLiteConn) error {
  ...

[ncruces]

Yes, the simplest way for database/sql is to call driver.Open instead of sql.Open:

• the first (optional) parameter is a "connection opened" callback,
• the second a "connection closing" callback.

See for example:

go-sqlite3/gormlite/sqlite_test.go

Lines 20 to 25 in 970eb6a

	db, err := driver.Open(tmp, func(conn *sqlite3.Conn) error {
	return conn.CreateFunction("my_custom_function", 0, sqlite3.DETERMINISTIC,
	func(ctx sqlite3.Context, arg ...sqlite3.Value) {
	ctx.ResultText("my-result")
	})
	})

Or how to register an extension on every new connection:

go-sqlite3/embed/bcw2/bcw2_test.go

Lines 19 to 22 in 970eb6a

	db, err := driver.Open("file:"+tmp+"?_pragma=journal_mode(wal2)&_txlock=concurrent", stats.Register)
	if err != nil {
	t.Fatal(err)
	}

How to run PRAGMA optimize just before closing:

go-sqlite3/tests/driver_test.go

Lines 21 to 23 in 970eb6a

	db, err := driver.Open(tmp, nil, func(c *sqlite3.Conn) error {
	return c.Exec(`PRAGMA optimize`)
	})

The other option is to call sqlite3.AutoExtension which works for the low level API. This mimics the corresponding native C API.

But AutoExtension is global for every connection. The database/sql API is for connections created for that specific pool. Which you use, depends on the intent. Registering an extension (like stats) may make sense globally. A connection hook probably doesn't. But you decide.

[tdang-github]

You're so kind by also giving the extra tips in addition to the answer :), which does exactly what I was asking for. Many thanks!

The only curious thing I noticed is that the onOpen callback seems to be called after CREATE TABLE. I hope hook notifications are not asynchronous.

Test case:

func TestUpdateHookDriver(t *testing.T) {
	dbPath := memdb.TestDB(t)

	updateHook := func(op sqlite3.AuthorizerActionCode, db string, table string, rowid int64) {
		log.Printf("updateHook: Op=%v, DB=%s, Table=%s, RowID=%d\n", op, db, table, rowid)
	}
	onOpen := func(conn *sqlite3.Conn) error {
		log.Println("onOpen")
		conn.UpdateHook(updateHook)
		return nil
	}
	db, err := driver.Open(dbPath, onOpen)
	if err != nil {
		return
	}
	log.Println("CREATE TABLE")
	if _, err := db.Exec("CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)"); err != nil {
		t.Fatal(err)
	}
	log.Println("INSERT")
	if _, err := db.Exec("INSERT INTO users (name, age) VALUES ('Alice', 30)"); err != nil {
		t.Fatal(err)
	}
	...

Output:

2025/04/04 00:25:52 CREATE TABLE
2025/04/04 00:25:53 onOpen
2025/04/04 00:25:53 INSERT
2025/04/04 00:25:53 updateHook: Op=18, DB=main, Table=users, RowID=1
2025/04/04 00:25:53 UPDATE
2025/04/04 00:25:53 updateHook: Op=23, DB=main, Table=users, RowID=1
2025/04/04 00:25:53 DELETE
2025/04/04 00:25:53 updateHook: Op=9, DB=main, Table=users, RowID=1

[ncruces]

That's expected as database/sql is a pool and connections are only opened as needed, in this case when the first query is executed.

It's still synchronous and before the SELECT is actually executed, but it's after you asked, because at that point there are no opened connections available in the pool.

You can call Ping to ensure there's a valid connection before continuing.

[tdang-github]

Got it. Thanks again Nuno.