Using an SQLite Extension in Go

· 475 words · 3 minute read

First, compile an SQLite extension.

Then, register a new SQL Driver:

package main

import (
    "database/sql"
    "log"

	"github.com/mattn/go-sqlite3"
)

func main() {
	sql.Register("sqlite3-uuid", &sqlite3.SQLiteDriver{
		ConnectHook: func(conn *sqlite3.SQLiteConn) error {
			err := conn.LoadExtension("uuid", "sqlite3_uuid_init")
			if err == nil {
				return nil
			}
			return err
		},
	})

	db, err := sql.Open("sqlite3-uuid", ":memory:")
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()
}

There were two critical pieces that had me stuck for too long.

The first is the driver name when opening the database. Notice that it’s not sqlite3, like it normally would be when using github.com/mattn/go-sqlite3. Instead, it’s sqlite3-uuid, the name we gave as the first argument to sql.Register(). The sql.Register() func is registering a new driver.

The docs show this, but leaves out the part about using the string that you use as the first argument to sql.Register() as the name of the engine to use when opening a database. It’s probably assumed that, since sql.Register() is a stdlib thing, you should already know that. It was not obvious to me.

Notice this same pattern used with the shaxbee/go-spatialite and dinedal/go-sqlite3-extension-functions extensions.

The second is what string to use for the second parameter in conn.LoadExtension(). I had no idea what to put there. Initially, I kept it as an empty string, and was getting an error:

/lib/uuid.so: undefined symbol:

That string should be the name of the function in the <extension>.c file that registers the sqlite function. In the case of uuid, that’s sqlite3_uuid_init.

Here’s where that name is in uuid.c

Example 🔗

package main

import (
	"context"
	"database/sql"
	"errors"
	"log"

	"github.com/mattn/go-sqlite3"
)

var ErrSqlite3UUIDNotFound = errors.New("sqlite3: uuid extension not found.")

const createTableAuthors = `-- name: CreateTableAuthors :exec
CREATE TABLE IF NOT EXISTS authors (
  id text NOT NULL,
  name text NOT NULL,
  bio text
)
`
const createAuthor = `-- name: CreateAuthor :execresult
INSERT INTO authors (
  id, name, bio
) VALUES (
  uuid(), ?, ? 
)
`

const listAuthors = `-- name: ListAuthors :many
SELECT id, name, bio FROM authors
ORDER BY name
`

type Author struct {
	ID   string
	Name string
	Bio  sql.NullString
}

func main() {
	sql.Register("sqlite3-uuid", &sqlite3.SQLiteDriver{
		ConnectHook: func(conn *sqlite3.SQLiteConn) error {
			err := conn.LoadExtension("uuid", "sqlite3_uuid_init")
			if err == nil {
				return nil
			}
			return ErrSqlite3UUIDNotFound
		},
	})

	db, err := sql.Open("sqlite3-uuid", ":memory:")
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()

	_, err = db.ExecContext(context.Background(), createTableAuthors)
	if err != nil {
		log.Fatal(err)
	}

	_, err = db.ExecContext(context.Background(), createAuthor, "myname", "my bio")
	if err != nil {
		log.Fatal(err)
	}

	rows, err := db.QueryContext(context.TODO(), listAuthors)
	if err != nil {
		log.Fatal(err)
	}
	defer rows.Close()
	var items []Author
	for rows.Next() {
		var i Author
		if err := rows.Scan(&i.ID, &i.Name, &i.Bio); err != nil {
			log.Fatal(err)
		}
		items = append(items, i)
	}
	if err := rows.Close(); err != nil {
		log.Fatal(err)
	}
	if err := rows.Err(); err != nil {
		log.Fatal(err)
	}
	log.Println(items)
}
sqlite3 golang mattn/go-sqlite3