sqlite

README

SQLite driver for Go database/sql

Work in progress. Nothing to see here.

Updating SQLite

The script update-sqlite.sh at the top of the repository partially automates this process. It expects a SQLite amalgamation URL as the first argument:

./update-sqlite.sh https://sqlite.org/2024/sqlite-amalgamation-3460100.zip

Documentation

Overview

Package sqlite implements a database/sql driver for SQLite3.

This driver requires a file: URI always be used to open a database. For details see https://sqlite.org/c3ref/open.html#urifilenames.

Initializing connections or tracing

If you want to do initial configuration of a connection, or enable tracing, use the Connector function:

connInitFunc := func(ctx context.Context, conn driver.ConnPrepareContext) error {
	return sqlite.ExecScript(conn.(sqlite.SQLConn), "PRAGMA journal_mode=WAL;")
}
db, err = sql.OpenDB(sqlite.Connector(sqliteURI, connInitFunc, nil))

Memory Mode

In-memory databases are popular for tests. Use the "memdb" VFS (*not* the legacy in-memory modes) to be compatible with the database/sql connection pool:

file:/dbname?vfs=memdb

Use a different dbname for each memory database opened.

Binding Types

SQLite is flexible about type conversions, and so is this driver. Almost all "basic" Go types (int, float64, string) are accepted and directly mapped into SQLite, even if they are named Go types. The time.Time type is also accepted (described below). Values that implement encoding.TextMarshaler or json.Marshaler are stored in SQLite in their marshaled form.

Binding Time

While SQLite3 has no strict time datatype, it does have a series of built-in functions that operate on timestamps that expect columns to be in one of many formats: https://sqlite.org/lang_datefunc.html

When encoding a time.Time into one of SQLite's preferred formats, we use the shortest timestamp format that can accurately represent the time.Time. The supported formats are:

2. YYYY-MM-DD HH:MM
3. YYYY-MM-DD HH:MM:SS
4. YYYY-MM-DD HH:MM:SS.SSS

If the time.Time is not UTC (strongly consider storing times in UTC!), we follow SQLite's norm of appending "[+-]HH:MM" to the above formats.

It is common in SQLite to store "Unix time", seconds-since-epoch in an INTEGER column. This is understood by the date and time functions documented in the link above. If you want to do that, pass the result of time.Time.Unix to the driver.

Reading Time

In general, time is hard to extract from SQLite as a time.Time. If a column is defined as DATE or DATETIME, then text data is parsed as TimeFormat and returned as a time.Time. Integer data is parsed as seconds since epoch and returned as a time.Time.

Index

• Constants
• Variables
• func BusyTimeout(sqlconn SQLConn, d time.Duration) error
• func Checkpoint(sqlconn SQLConn, dbName string, mode sqliteh.Checkpoint) (numFrames, numFramesCheckpointed int, err error)
• func Connector(sqliteURI string, connInitFunc ConnInitFunc, tracer sqliteh.Tracer) driver.Connector
• func CopyAll(ctx context.Context, conn *sql.Conn, dstSchemaName, srcSchemaName string) (err error)
• func DropAll(ctx context.Context, conn *sql.Conn, schemaName string) (err error)
• func ExecScript(sqlconn SQLConn, queries string) error
• func IsReadOnly(ctx context.Context) bool
• func ReadOnly(ctx context.Context) context.Context
• func SetWALHook(sqlconn SQLConn, hook func(dbName string, pages int)) error
• func TxnState(sqlconn SQLConn, schema string) (state sqliteh.TxnState, err error)
• func WithPersist(ctx context.Context) context.Context
• func WithQueryCancel(ctx context.Context) context.Context
• type ConnInitFunc
• type Error
  • func (err Error) Error() string
• type SQLConn

Constants

const TimeFormat = "2006-01-02 15:04:05.000-0700"

TimeFormat is the string format this driver uses to store microsecond-precision time in SQLite in text format.

Variables

var ErrClosed = errors.New("sqlite3: already closed")

ErrClosed is returned when an operation is attempted on a connection after Close has already been called.

var Open sqliteh.OpenFunc = func(string, sqliteh.OpenFlags, string) (sqliteh.DB, error) {
	return nil, fmt.Errorf("cgosqlite.Open is missing")
}

var UsesAfterClose expvar.Map

UsesAfterClose is a metric that is incremented every time an operation is attempted on a connection after Close has already been called. The keys are internal identifiers for the code path that incremented a counter.

Functions

func BusyTimeout

func BusyTimeout(sqlconn SQLConn, d time.Duration) error

BusyTimeout calls sqlite3_busy_timeout on the underlying connection.

func Checkpoint

func Checkpoint(sqlconn SQLConn, dbName string, mode sqliteh.Checkpoint) (numFrames, numFramesCheckpointed int, err error)

Checkpoint calls sqlite3_wal_checkpoint_v2 on the underlying connection.

func Connector

func Connector(sqliteURI string, connInitFunc ConnInitFunc, tracer sqliteh.Tracer) driver.Connector

func CopyAll

func CopyAll(ctx context.Context, conn *sql.Conn, dstSchemaName, srcSchemaName string) (err error)

CopyAll copies the contents of one database to another.

Traditionally this is done in sqlite by closing the database and copying the file. However it can be useful to do it online: a single exclusive transaction can cross multiple databases, and if multiple processes are using a file, this lets one replace the database without first communicating with the other processes, asking them to close the DB first.

The dstSchemaName and srcSchemaName parameters follow the SQLite PRAMGA schema-name conventions: https://sqlite.org/pragma.html#syntax

func DropAll

func DropAll(ctx context.Context, conn *sql.Conn, schemaName string) (err error)

DropAll deletes all the data from a database.

The schemaName parameter follows the SQLite PRAMGA schema-name conventions: https://sqlite.org/pragma.html#syntax

func ExecScript

func ExecScript(sqlconn SQLConn, queries string) error

ExecScript executes a set of SQL queries on an sql.Conn. It stops on the first error. It is recommended you wrap your script in a BEGIN; ... COMMIT; block.

Usage:

c, err := db.Conn(ctx)
if err != nil {
	// handle err
}
if err := sqlite.ExecScript(c, queries); err != nil {
	// handle err
}
c.Close() // return sql.Conn to pool

func IsReadOnly

func IsReadOnly(ctx context.Context) bool

IsReadOnly reports whether the context has the ReadOnly key.

func ReadOnly

func ReadOnly(ctx context.Context) context.Context

ReadOnly applies the query_only pragma to the connection.

func SetWALHook

func SetWALHook(sqlconn SQLConn, hook func(dbName string, pages int)) error

SetWALHook calls sqlite3_wal_hook.

If hook is nil, the hook is removed.

func TxnState

func TxnState(sqlconn SQLConn, schema string) (state sqliteh.TxnState, err error)

TxnState calls sqlite3_txn_state on the underlying connection.

func WithPersist

func WithPersist(ctx context.Context) context.Context

WithPersist makes a ctx instruct the sqlite driver to persist a prepared query.

This should be used with recurring queries to avoid constant parsing and planning of the query by SQLite.

func WithQueryCancel

func WithQueryCancel(ctx context.Context) context.Context

WithQueryCancel makes a ctx that instructs the sqlite driver to explicitly interrupt a running query if its argument context ends. By default, without this option, queries will only check the context between steps.

Types

type ConnInitFunc

type ConnInitFunc func(ctx context.Context, conn driver.ConnPrepareContext) error

ConnInitFunc is a function called by the driver on new connections.

The conn can be used to execute queries, and implements SQLConn. Any error return closes the conn and passes the error to database/sql.

type Error

type Error struct {
	Code  sqliteh.Code // SQLite extended error code (SQLITE_OK is an invalid value)
	Loc   string       // method name that generated the error
	Query string       // original SQL query text
	Msg   string       // value of sqlite3_errmsg, set sqlite.ErrMsg = true
}

Error is an error produced by SQLite.

func (Error) Error

func (err Error) Error() string

type SQLConn

type SQLConn interface {
	Raw(func(driverConn any) error) error
}

SQLConn is a database/sql.Conn. (We cannot create a circular package dependency here.)