SQLite’s virtual tables let you expose external data sources as if they were normal tables. The go-sqlite3 package includes an example of how to register a custom module, but it doesn’t cover cases where you need to initialize the module differently per connection.

A common use case is multitenancy—where each database connection needs its own configuration, like an API key. The standard approach of registering a virtual table using ConnectHook applies globally, making it difficult to provide per-connection parameters dynamically. Instead, we can wrap the SQLite driver in a driver.Connector and use sql.OpenDB, allowing each instance of SQLite to behave differently.

The Problem

If you register a virtual table globally using ConnectHook, every connection to the database gets the same setup. This works fine for things like defining a custom SQL function (e.g., adding regex support), but not if you need connection-specific behavior.

For example, say you’re building a virtual table that pulls data from an external API, and each tenant has a different API key. The table should return different results depending on which tenant’s key is in use. But with ConnectHook, there’s no clean way to register a new module per tenant dynamically.

The Solution: Using a driver.Connector Wrapper

To demonstrate, here’s a proof-of-concept where a virtual table returns different data based on an initialized runtime parameter. You can imagine this being an API key, where each connection gets a unique key, and the virtual table fetches different data accordingly.

Full Program

package main

import (
	"context"
	"database/sql"
	"database/sql/driver"
	"fmt"
	"log"

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

// SQLiteConnector wraps the SQLite driver and registers virtual table modules.
type SQLiteConnector struct {
	driver *sqlite3.SQLiteDriver
}

// Connect creates a new SQLite connection.
func (s *SQLiteConnector) Connect(ctx context.Context) (driver.Conn, error) {
	return s.driver.Open("")
}

// Driver returns the underlying driver.
func (s *SQLiteConnector) Driver() driver.Driver {
	return s.driver
}

// NewSQLiteConnector returns a connector that registers the provided modules.
func NewSQLiteConnector(modules map[string]sqlite3.Module) *SQLiteConnector {
	d := &sqlite3.SQLiteDriver{
		ConnectHook: func(conn *sqlite3.SQLiteConn) error {
			for name, module := range modules {
				if err := conn.CreateModule(name, module); err != nil {
					return err
				}
			}
			return nil
		},
	}
	return &SQLiteConnector{driver: d}
}

// exampleModule implements a virtual table module that returns data based on a parameter.
type exampleModule struct {
	param string
}

// Create declares the virtual table schema.
func (m *exampleModule) Create(c *sqlite3.SQLiteConn, args []string) (sqlite3.VTab, error) {
	schema := fmt.Sprintf("CREATE TABLE %s (value TEXT)", args[0])
	if err := c.DeclareVTab(schema); err != nil {
		return nil, err
	}
	return &exampleTable{param: m.param}, nil
}

// Connect is the same as Create.
func (m *exampleModule) Connect(c *sqlite3.SQLiteConn, args []string) (sqlite3.VTab, error) {
	return m.Create(c, args)
}

// DestroyModule is a no-op.
func (m *exampleModule) DestroyModule() {}

// exampleTable holds the parameter that influences query results.
type exampleTable struct {
	param string
}

// Open returns a cursor that iterates over example data.
func (t *exampleTable) Open() (sqlite3.VTabCursor, error) {
	return &exampleCursor{param: t.param, index: 0, data: []string{"foo", "bar", "baz"}}, nil
}

// BestIndex provides a default index result.
func (t *exampleTable) BestIndex(csts []sqlite3.InfoConstraint, ob []sqlite3.InfoOrderBy) (*sqlite3.IndexResult, error) {
	return &sqlite3.IndexResult{IdxNum: 0, IdxStr: "default"}, nil
}

func (t *exampleTable) Disconnect() error { return nil }
func (t *exampleTable) Destroy() error    { return nil }

// exampleCursor iterates over the dataset.
type exampleCursor struct {
	param string
	index int
	data  []string
}

// Column returns the value for the given column.
func (cur *exampleCursor) Column(ctx *sqlite3.SQLiteContext, col int) error {
	ctx.ResultText(fmt.Sprintf("%s_%s", cur.param, cur.data[cur.index]))
	return nil
}

// Filter resets the cursor.
func (cur *exampleCursor) Filter(idxNum int, idxStr string, vals []any) error {
	cur.index = 0
	return nil
}

// Next advances the cursor.
func (cur *exampleCursor) Next() error {
	cur.index++
	return nil
}

// EOF returns true if all rows have been iterated.
func (cur *exampleCursor) EOF() bool {
	return cur.index >= len(cur.data)
}

// Rowid returns the current row id.
func (cur *exampleCursor) Rowid() (int64, error) {
	return int64(cur.index), nil
}

// Close cleans up the cursor (no-op in this case).
func (cur *exampleCursor) Close() error {
	return nil
}

func main() {
	run("tenantA")
	run("tenantB")
}

func run(param string) {
	// Create a new SQLite connector with the "example" module registered.
	db := sql.OpenDB(NewSQLiteConnector(map[string]sqlite3.Module{
		"example": &exampleModule{param: param},
	}))
	defer db.Close()

	// Create the virtual table using the registered module.
	_, err := db.Exec("CREATE VIRTUAL TABLE example_table USING example")
	if err != nil {
		log.Fatal(err)
	}

	// Query the virtual table.
	rows, err := db.Query("SELECT value FROM example_table")
	if err != nil {
		log.Fatal(err)
	}
	defer rows.Close()

	// Display the results.
	fmt.Printf("Results for %s:\n", param)
	for rows.Next() {
		var value string
		if err := rows.Scan(&value); err != nil {
			log.Fatal(err)
		}
		fmt.Println(value)
	}
}

Running the Example

Since virtual tables require a specific build tag in go-sqlite3, you have to run the program like this:

go run -tags 'sqlite_vtable' main.go

You should see this as the output:

Results for tenantA:
tenantA_foo
tenantA_bar
tenantA_baz
Results for tenantB:
tenantB_foo
tenantB_bar
tenantB_baz

How It Works

Instead of relying on ConnectHook at the global level, this approach uses sql.OpenDB with a custom connector, allowing each database instance to register its own virtual table module with unique runtime parameters.

In the example above, we initialize two separate SQLite instances, one for "tenantA" and one for "tenantB". Each instance registers the "example" module with a different value for param. When queried, the virtual table returns results that incorporate the parameter—just like an API-driven virtual table would behave differently based on the API key in use.

This method provides flexibility when working with SQLite in multitenant applications, enabling per-connection behavior without modifying the global driver state.

With this approach, you can dynamically configure SQLite virtual tables at runtime—whether it’s for API-backed datasets, tenant-specific views, or other custom behavior.