DEV Community

Cover image for Recent changes in go-sqlite3
Yasuhiro Matsumoto
Yasuhiro Matsumoto

Posted on • Originally published at dev.to

Recent changes in go-sqlite3

In brief, I added some important features in go-sqlite3.

Hook events

It was introduced before, but it was not the default. Now you can use this in default. To use this, you need to register hook.

To be honest, I don't think this way is not smart, but no way to do in Go master.

sql.Register("sqlite3_with_hook_example",
    &sqlite3.SQLiteDriver{
        ConnectHook: func(conn *sqlite3.SQLiteConn) error {
            conn.RegisterUpdateHook(func(op int, db string, table string, rowid int64) {
                switch op {
                case sqlite3.SQLITE_INSERT:
                    log.Println("Notified of insert on db", db, "table", table, "rowid", rowid)
                }
            })
            return nil
        },
    })
Enter fullscreen mode Exit fullscreen mode

Then you can see when the record is inserted/deleted/updated. Similar to triggers but easier to handle events, I think. For example, this may be helpful to make replication of the database.

Get limits

This also need to register hook.

var sqlite3conn *sqlite3.SQLiteConn
sql.Register("sqlite3_with_limit", &sqlite3.SQLiteDriver{
    ConnectHook: func(conn *sqlite3.SQLiteConn) error {
        sqlite3conn = conn
        return nil
    },
})
db, err := sql.Open("sqlite3_with_limit", "./foo.db")
if err != nil {
    log.Fatal(err)
}
defer db.Close()
Enter fullscreen mode Exit fullscreen mode

After connect to your database, you can use SQLite3Conn#GetLimit().

varnum := sqlite3conn.GetLimit(sqlite3.SQLITE_LIMIT_VARIABLE_NUMBER)
Enter fullscreen mode Exit fullscreen mode

Export Go func into your SQL

RegisterFunc export your Go function into your SQL.

sql.Register("sqlite3_custom", &sqlite.SQLiteDriver{
    ConnectHook: func(conn *sqlite.SQLiteConn) error {
        if err := conn.RegisterFunc("pow", pow, true); err != nil {
            return err
        }
        return nil
    },
})
Enter fullscreen mode Exit fullscreen mode

pow should be like below. Just call Math.Pow. Note that you must use int64 instead of int for the arguments/return types of the func.

func pow(x, y int64) int64 {
    return int64(math.Pow(float64(x), float64(y)))
}
Enter fullscreen mode Exit fullscreen mode

That's all to be done for enable this feature. So now you can call pow in your query.

err = db.QueryRow("SELECT pow(2,3)").Scan(&i)
if err != nil {
    log.Fatal("POW query error:", err)
}
Enter fullscreen mode Exit fullscreen mode

If you want to use regular expression in the query:

sql.Register("sqlite3_with_go_func", &sqlite3.SQLiteDriver{
    ConnectHook: func(conn *sqlite3.SQLiteConn) error {
        return conn.RegisterFunc("regexp", func(re, s string) (bool, error) {
                return regexp.MatchString(re, s)
        }, true)
    },
})
Enter fullscreen mode Exit fullscreen mode

Top comments (0)