sqlite

package module
v0.1.1 Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: Nov 4, 2026 License: MIT Imports: 15 Imported by: 0

README

sqlite

The fastest cgo-free SQLite driver for Go's database/sql.
It builds with CGO_ENABLED=0, and on most workloads it beats the cgo driver too.

English · 简体中文 · Benchmarks · How it works

Go Reference test

It is for Go programs that keep their data in SQLite and want to stay plain Go: one static binary, cross-compiled from any machine, no C compiler in the build or in the container. Until now that meant giving up speed. Here the cgo driver is the one that falls behind.

Benchmarks

Time per operation, lower is better, the median of 6 runs. The best of a row is in bold when its 95% confidence interval overlaps no other driver's. WAL, synchronous=NORMAL, the same settings checked on every driver.

linux/amd64 (AMD EPYC 7B13, Go 1.27.2, commit cc1ede5):

Workload this driver mattn (cgo) modernc ncruces
Insert one row 18.7 µs 19.6 µs 23.4 µs 21.5 µs
Insert 1000 rows in a transaction 1.30 ms 1.47 ms 1.94 ms 1.67 ms
Read one row by key 3.58 µs 6.70 µs 8.45 µs 6.54 µs
Read 100k rows 34.9 ms 73.4 ms 76.4 ms 50.2 ms
Join and group 2.87 ms 2.63 ms 4.51 ms 4.37 ms
Find a user with 400 comments 509 µs 830 µs 1.05 ms 762 µs
Read 100 rows of 200 KB text 8.85 ms 6.99 ms 8.13 ms 8.02 ms
Read by key, 8 threads 1.65 µs 2.00 µs 2.18 µs 2.29 µs
Insert, 8 goroutines at once 9.43 µs 19.4 µs 24.2 µs 21.9 µs
Insert, 64 goroutines at once 3.40 µs 20.2 µs 27.0 µs 27.0 µs
Insert, 64 goroutines, synchronous=FULL 21.0 µs 2.48 ms 5.31 ms 1.12 ms

darwin/arm64 (Apple M4, Go 1.27.2, commit 90e0e83, other work ran on the machine: read differences under 30% as noise):

Workload this driver mattn (cgo) modernc ncruces
Insert one row 6.32 µs 6.21 µs 7.96 µs 7.00 µs
Insert 1000 rows in a transaction 456 µs 594 µs 748 µs 583 µs
Read one row by key 1.12 µs 1.96 µs 2.86 µs 2.07 µs
Read 100k rows 14.4 ms 31.6 ms 30.1 ms 20.7 ms
Join and group 1.56 ms 1.19 ms 2.65 ms 1.90 ms
Find a user with 400 comments 236 µs 396 µs 594 µs 345 µs
Read 100 rows of 200 KB text 4.87 ms 3.58 ms 3.99 ms 3.64 ms
Read by key, 10 threads 1.29 µs 1.69 µs 1.79 µs 2.49 µs
Insert, 8 goroutines at once 3.30 µs 6.94 µs 8.12 µs 43.5 µs
Insert, 64 goroutines at once 1.37 µs 9.53 µs 8.28 ms 83.3 µs

Where another driver still wins: work that stays inside SQLite (the join), where this driver runs the same C but as Go assembly, and reading large text values. Why reads and many writers are faster, the versions of each driver, and how to run it all again are in docs/benchmarks.md.

Install

go get github.com/liustack/sqlite

Go 1.27 or later. linux and darwin on amd64 and arm64, windows on amd64.

Use

import (
	"database/sql"

	_ "github.com/liustack/sqlite"
)

db, err := sql.Open("sqlite", "file:app.db?_pragma=journal_mode(WAL)&_pragma=synchronous(NORMAL)")

The name is a file, :memory: or a SQLite URI. _pragma=name(value) runs a PRAGMA on every new connection, and _txlock=immediate makes BEGIN take the write lock at once. A connection waits 5 seconds for a lock unless busy_timeout says otherwise. The rest is on pkg.go.dev.

SQL functions, aggregates and collations written in Go go on the connector, so that every connection of the pool has them:

c, err := sqlite.NewConnector("file:app.db",
	sqlite.WithFunction("reverse", 1, reverse, sqlite.Deterministic),
	sqlite.WithAggregate("mode", 1, newMode),
	sqlite.WithCollation("fold", fold))
db := sql.OpenDB(c)

sqlite.Register gives them to every connector sql.Open makes after it. An error or a panic in a function fails its statement, not the connection.

Common functions come ready in packages under ext/: regexp (the REGEXP operator, regexp_like, regexp_substr, regexp_replace), uuid, hash (md5, sha1, sha256, sha512, crc32) and stats (variance and standard deviation). Each is one option, and sqlite.Join hands several over as one:

sqlite.Register(sqlite.Join(regexp.Option(), uuid.Option()))

Full-text search (FTS5), the spatial index (R*Tree), the math functions and median and percentile (percentile) are built in, with nothing to import. The math functions give the same bits on every target.

The driver registers as sqlite, as modernc.org/sqlite and glebarez/go-sqlite do, so a program that imports one of them as well panics at start. Open the database with sql.OpenDB(sqlite.NewConnector(dsn)) then.

What changes for you

  • No C anywhere. CGO_ENABLED=0 GOOS=linux GOARCH=arm64 go build from a Mac works, and the binary runs in a scratch image.
  • Many writers stop waiting on each other. When goroutines write at the same time, the driver runs their autocommit INSERT, UPDATE and DELETE statements as one transaction and commits once. Each call still gets its own error, LastInsertId and RowsAffected, and returns only after the commit, so nothing is less durable. Transactions you open yourself are never merged. How it works
  • Writers queue instead of failing with database is locked. Inside the driver one connection of your program writes a file at a time, and the others queue for it, up to busy_timeout, instead of sleeping in SQLite's busy handler. A transaction that reads first and writes later can still fail when another wrote in between, so start such transactions with _txlock=immediate. Other processes still meet SQLite's own locks.
  • Reads cost less. A call into SQLite is a plain Go call, and scanning a number into your variable allocates nothing (a string costs one allocation, its own).

Why you can trust it

  • Safety comes first, speed second, memory third. A change that is faster but could lose a write, return a wrong result or weaken a commit is not taken. Group commit keeps a SAVEPOINT around every merged statement for this reason, though it costs speed.
  • The C is SQLite's, unchanged. engine/ holds the 3.54.1 amalgamation as released, checked against its hash (engine/vendor.sh --verify). The compile options sit in one file, each with its reason.
  • The Go assembly is made, never written. LLVM compiles the C, and a generator turns what LLVM writes into Go assembly. It stops on anything it has no rule for, and checks the linked binary. make check-gen fails when the checked-in assembly is not what the generator makes from engine/ today.
  • The numbers show where it loses. The tables above keep the rows where another driver is faster, and docs/benchmarks.md says how each run was taken and what else ran on the machine.
  • Every push is tested on linux/amd64, linux/arm64, darwin/arm64 and windows/amd64 (with the race detector, except on windows), with the sqlite3 shell writing the same database at the same time, and with processes killed while they write.

Not there yet

  • No loadable extensions: SQLite is built without load_extension.
  • No shared cache: cache=shared gives an error. For a memory database that all connections of a pool see, use file:/name?vfs=memdb.
  • Double-quoted strings are identifiers only (SQLITE_DQS=0, as SQLite advises): write string literals in single quotes.
  • Memory SQLite has used is kept for reuse and not given back to the system while the process runs.
  • windows/arm64 and other targets do not build.

Documentation

Doc Read it when
Benchmarks You want the full numbers, or to run them on your machine
Architecture You want to know how C becomes Go assembly, or how group commit works
Upstream You want to know which SQLite it is, or how it moves to a new one
Testing, Commits You are about to change the code

Thanks

To D. Richard Hipp and the SQLite developers for SQLite, and to musl for the string functions the engine links. To the authors of mattn/go-sqlite3, modernc.org/sqlite and ncruces/go-sqlite3, who showed the ways SQLite can live in Go and whose drivers this one is measured against.

License

MIT for the code of this repository. SQLite is in the public domain, and musl's code keeps its MIT license. NOTICE.md says which license covers which path.

Documentation

Overview

Package sqlite is a SQLite driver for database/sql that needs no cgo.

It registers itself as "sqlite": 

db, err := sql.Open("sqlite", "file:app.db?_pragma=journal_mode(WAL)&_pragma=busy_timeout(5000)")

modernc.org/sqlite and github.com/glebarez/go-sqlite register the same name, so a program that imports one of them as well panics at start. Use sql.OpenDB(sqlite.NewConnector(dsn)) to open a database without the registered name.

The data source name

A file name, ":memory:", or a URI ("file:app.db?mode=ro"), which SQLite reads itself (https://sqlite.org/uri.html). These parameters are the driver's and SQLite does not see them:

  • _pragma=name(value): a PRAGMA run on every new connection, in order. It may be given more than once.
  • _txlock=deferred, immediate or exclusive: how BEGIN starts a transaction (deferred by default). A read-only transaction always starts deferred.
  • _time_format=sqlite: how a time.Time is written. The only format, and the default, is "2006-01-02 15:04:05.999999999-07:00".

A connection waits 5 seconds for a lock unless a _pragma sets busy_timeout.

Index

Examples

Constants

This section is empty.

Variables

This section is empty.

Functions

func Register 

func Register(opts ...Option)

Register adds opts to every connector made after it, by sql.Open or by NewConnector, before the connector's own options. Call it before opening databases, as in an init function. An option it was given that is not valid fails those connectors.

Types

type Aggregate 

type Aggregate interface {
	Step(args []driver.Value) error
	Final() (driver.Value, error)
}

Aggregate is the state of one group of an aggregate function written in Go. Step runs for each row of the group, with arguments as a Func gets them, then Final once, with a result as a Func gives it. An error or a panic in either is the error of the statement.

type Connector 

type Connector struct {
	// contains filtered or unexported fields
}

Connector opens connections to one database.

func NewConnector 

func NewConnector(dsn string, opts ...Option) (*Connector, error)

NewConnector parses dsn, for sql.OpenDB. Its connections get what Register gave, then opts.

func (*Connector) Connect 

func (c *Connector) Connect(ctx context.Context) (driver.Conn, error)

Connect opens a connection.

func (*Connector) Driver 

func (c *Connector) Driver() driver.Driver

Driver is the driver of the connector.

type Driver 

type Driver struct{}

Driver is the database/sql driver.

func (*Driver) Open 

func (d *Driver) Open(dsn string) (driver.Conn, error)

Open opens a connection to the database named by dsn.

func (*Driver) OpenConnector 

func (d *Driver) OpenConnector(dsn string) (driver.Connector, error)

OpenConnector parses dsn once for every connection of a pool.

type Func 

type Func func(args []driver.Value) (driver.Value, error)

Func is a SQL function written in Go.

Its arguments are copies, each an int64, float64, string, []byte or nil, so it may keep them. It returns a value database/sql can bind (an integer, float, bool, string, []byte, time.Time, nil or a driver.Valuer), or an error, which becomes the error of the statement. A panic in it is recovered and becomes the error of the statement too: the connection stays good.

It may run on any connection of its connector, and from several goroutines at once. It must not use the database: it runs in the middle of a statement. It may also run more than once for the same row: SQLite does not promise how often it calls a function, and group commit runs a batch again when a failure of one statement ended its transaction. Keep side effects out of it, or make them safe to repeat.

type FuncFlag 

type FuncFlag int

FuncFlag tells SQLite how a function behaves. 

const (
	// Deterministic: the same arguments always give the same result, so
	// SQLite may compute it once, and use it in indexes and generated
	// columns.
	Deterministic FuncFlag = csqlite.FuncDeterministic
	// DirectOnly: the function may be called only from SQL the program
	// runs, never from a trigger, view, index or generated column of the
	// schema. Give it to a function with side effects, so that a database
	// file someone else made cannot call it.
	DirectOnly FuncFlag = csqlite.FuncDirectOnly
	// Innocuous: the function has no side effects and reveals nothing, so
	// it is safe wherever SQL comes from.
	Innocuous FuncFlag = csqlite.FuncInnocuous
)

type Option 

type Option struct {
	// contains filtered or unexported fields
}

An Option adds something to the connections of a connector: give it to NewConnector, or to Register for the connectors sql.Open makes.

func Join 

func Join(opts ...Option) Option

Join makes one Option of several, applied in order: how a package of functions hands them over.

func WithAggregate 

func WithAggregate(name string, nArg int, newAggregate func() Aggregate, flags ...FuncFlag) Option

WithAggregate makes an aggregate function name of nArg arguments: for each group, newAggregate makes a state. A group of no rows gets one too, for Final alone.

func WithCollation 

func WithCollation(name string, cmp func(a, b string) int) Option

WithCollation makes cmp the collation name, for COLLATE name in queries and in the schema. cmp returns a negative number, 0 or a positive number as a sorts before, with or after b, and must be a total order. A panic in cmp fails the statement with the panic, and SQLite undoes it as it does when memory runs out: it rolls back the statement, or the whole transaction. When the whole transaction is gone, the next statements of it and its Commit fail. The connection stays good.

func WithFunction 

func WithFunction(name string, nArg int, fn Func, flags ...FuncFlag) Option

WithFunction makes fn the SQL function name of nArg arguments (-1 for any number) on every connection of the connector. A later option of the same name and nArg replaces an earlier one.

Example 
//go:build ((linux || darwin) && (amd64 || arm64)) || (windows && amd64)

package main

import (
	"database/sql"
	"database/sql/driver"
	"fmt"
	"strings"

	"github.com/liustack/sqlite"
)

type longest struct{ s string }

func (l *longest) Step(args []driver.Value) error {
	if s, ok := args[0].(string); ok && len(s) > len(l.s) {
		l.s = s
	}
	return nil
}

func (l *longest) Final() (driver.Value, error) { return l.s, nil }

func main() {
	reverse := func(args []driver.Value) (driver.Value, error) {
		s, _ := args[0].(string)
		r := []rune(s)
		for i, j := 0, len(r)-1; i < j; i, j = i+1, j-1 {
			r[i], r[j] = r[j], r[i]
		}
		return string(r), nil
	}
	c, err := sqlite.NewConnector(":memory:",
		sqlite.WithFunction("reverse", 1, reverse, sqlite.Deterministic),
		sqlite.WithAggregate("longest", 1, func() sqlite.Aggregate { return &longest{} }),
		sqlite.WithCollation("nocase_go", func(a, b string) int {
			return strings.Compare(strings.ToLower(a), strings.ToLower(b))
		}))
	if err != nil {
		panic(err)
	}
	db := sql.OpenDB(c)
	defer db.Close()

	var r, l, first string
	err = db.QueryRow(`
		WITH w(word) AS (VALUES ('Banana'), ('apple'), ('cherry'))
		SELECT reverse('stressed'), longest(word),
			(SELECT word FROM w ORDER BY word COLLATE nocase_go LIMIT 1)
		FROM w`).Scan(&r, &l, &first)
	if err != nil {
		panic(err)
	}
	fmt.Println(r, l, first)
}

Output:
desserts Banana apple

Source Files

View all Source files

Directories

Path Synopsis
ext
hash
Package hash gives SQLite hash functions:
 Package hash gives SQLite hash functions:
internal/arg
Package arg reads the arguments of the functions of ext/.
 Package arg reads the arguments of the functions of ext/.
internal/sqltest
Package sqltest runs the queries of the tests of ext/.
 Package sqltest runs the queries of the tests of ext/.
regexp
Package regexp gives SQLite the REGEXP operator and regexp functions, with Go's regexp syntax (RE2: no backreferences, and time linear in the text).
 Package regexp gives SQLite the REGEXP operator and regexp functions, with Go's regexp syntax (RE2: no backreferences, and time linear in the text).
stats
Package stats gives SQLite the variance and the standard deviation as aggregate functions:
 Package stats gives SQLite the variance and the standard deviation as aggregate functions:
uuid
Package uuid gives SQLite the functions of SQLite's own uuid extension:
 Package uuid gives SQLite the functions of SQLite's own uuid extension:
internal
csqlite
goasm/gen
goasm/gen/cmd/engine command
goasm/gen/cmd/gen command
vfs
Package vfs is the operating system layer of SQLite, in Go: the files, locks and shared memory that os_unix.c gives a normal build.
 Package vfs is the operating system layer of SQLite, in Go: the files, locks and shared memory that os_unix.c gives a normal build.

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.