DEV Community

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

Posted on • Originally published at dev.to

22 1

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

Image of Datadog

Create and maintain end-to-end frontend tests

Learn best practices on creating frontend tests, testing on-premise apps, integrating tests into your CI/CD pipeline, and using Datadog’s testing tunnel.

Download The Guide

Top comments (0)

Postmark Image

Speedy emails, satisfied customers

Are delayed transactional emails costing you user satisfaction? Postmark delivers your emails almost instantly, keeping your customers happy and connected.

Sign up