litesql is a Go library for working with SQLite3, providing reasonable
defaults and an easy-to-use API for reliable and performant database access.
Getting Started
The litesql package can be added to a Go project with go get.
go get cattlecloud.net/go/litesql@latest
import "cattlecloud.net/go/litesql"
Examples
Opening a SQLite database
The litesql.TypicalConfiguration contains reasonable defaults for many
general applications such as webapps. You may wish to use it as a reference
and fine-tune parameters for each use case.
db, err := litesql.Open("/path/to/file", litesql.TypicalConfiguration) // ... defer db.Close()
Starting SQLite transactions
The *LiteDB returned by Open provides StartRead and StartWrite for
starting a read or write transaction. Each passes a sql.TxOptions value to
BeginTx. The sqlite3 driver does not implement transaction isolation levels,
so the Isolation field is ignored; effective isolation is determined by the
connection's _txlock mode (deferred for reads, TransactionLock for writes).
A write transaction must be ended with a call to Commit.
Read transactions use a pool of connections, and may be used concurrently. They are restricted to reading; attempting to write using a read transaction results in an error.
Write transactions use a dedicated connection, so that only one write transaction is active at a time; a second write transaction waits for the first to complete. Because there is a single write connection, a write transaction must be committed or rolled back once complete; otherwise it blocks all subsequent write transactions.
All transactions started with StartRead and StartWrite return a CloseFunc
that should be called in a defer statement, which executes a rollback of the
transaction no matter what (which turn into no-ops if the transaction has been
committed or rolled back already).
// read transaction tx, done, xerr := db.StartRead(ctx) if xerr != nil { return xerr } defer done() // ... use tx to execute queries ...
// write transaction tx, done, xerr = db.StartWrite(ctx) if xerr != nil { return xerr } defer done() // ... use tx to execute statements ... // commit the write transaction return tx.Commit()
Query rows
Convenience functions QueryRow and QueryRows exist at the package level
for abstracting much of the boiler-plate code for reading rows. By supplying
a ScanFunc, you can fetch row(s) without managing most of the query logic.
func GetRecords(id int) ([]*record, error) { tx, done, xerr := db.StartRead(ctx) if xerr != nil { return nil, xerr } defer done() const statement = `select * from mytable where id > ?` f := func(scan litesql.ScanFunc) (*record, error) { var r record err := scan( // &r.field1 // &r.field2 // ... ) return &r, err } return litesql.QueryRows(ctx, tx, f, statement, id) }
Execute statement
The ExecID and Exec statements are for write transactions. The Exec
statement needs to know how many rows to expect to be modified, returning an
error if expectations are not met. There are special package constants for
indicating certain special cases. The ExecID method expects one row to be
changed, and will return the ROWID of the affected (or added) row.
ExpectAnything // do not enforce any expecation on number of rows changed ExpectNonZero // at least one row must be changed ExpectOneOrZero // exactly 0 or 1 row must be changed, useful for upserts ExpectOne // exactly 1 row must be changed ExpectNone // exactly 0 rows must be changed
A simple update example.
func example(id int, value string) error { tx, done, xerr := db.StartWrite(ctx) if xerr != nil { return xerr } defer done() const statement = `update mytable set v = ? where id = ?` if err := db.Exec(ctx, tx, litesql.ExpectOneOrZero, statement, value, id); err != nil { return err } return tx.Commit() }
Show pragma values
It is often helpful to dump the database pragma values on startup. This can
be done using the Pragmas method, which returns a map[string]string of
most common SQLite pragma configuration values.
m, _ := db.Pragmas(ctx) for k, v := range m { fmt.Println("pragma", k, "value", v) }
Creating a snapshot
The Snapshot method creates a point-in-time copy of the database, useful
for backups or creating read-only copies for sharing. Snapshots are copied
page-by-page using SQLite's backup API, with configurable Step and Gap
options to control concurrency with writers.
err := db.Snapshot(&litesql.SnapshotOptions{ Directory: "/path/to/snapshots", // where to write the snapshot Retention: 3, // keep last 3 snapshots Step: 100, // copy 100 pages per step Gap: 1 * time.Millisecond, // small pause between steps Progress: func(pages, remaining int) { fmt.Printf("progress: %d/%d pages\n", pages-remaining, pages) }, })
The snapshot files are named snapshot-<timestamp>.db in the specified
directory, and older files are automatically cleaned up according to the
Retention policy.
Sanitizing FTS5 queries
SQLite FTS5 uses special control characters (*, :, ^, ", etc.) that can
cause query errors or allow malicious query injection. Use SanitizeFTS5 to
strip these characters before passing user input to an FTS5 query.
query := "hello world*" // prefix-operator performance implications sanitized := litesql.SanitizeFTS5(query) // removes fts5 control characters fmt.Println(sanitized) // "hello world"
Database Maintenance
A *LiteDB provides three methods for database maintenance: Optimize,
Analyze, and Vacuum. Each serves a different purpose and should be run at
different intervals.
Optimize updates internal schema metadata used by the query planner. It
should be called periodically (e.g., weekly or after bulk inserts/deletes) to
maintain optimal query performance. The operation is fast and non-blocking.
Analyze gathers statistics about table and index data distribution. These
statistics help the query planner choose efficient execution plans. Run after
significant data changes (e.g., large inserts, deletes, or bulk updates) to
ensure the planner has accurate information. Fast and non-blocking.
Vacuum rebuilds the database file, reclaiming unused space and
defragmenting data. It should be run sparingly (e.g., monthly or after many
deletions) as it requires exclusive database access and can be slow for large
databases. Improves performance by reducing file size and greatly improving
sequential I/O efficiency.
A common maintenance schedule is to run Optimize daily or weekly, Analyze
after large data changes, and Vacuum monthly or after a large number of
delete operations.
Testing
The litesqltest subpackage provides helpers for writing unit tests that
exercise a real SQLite3 database. Instead of mocking the database layer, tests
run against a genuine on-disk database backed by a temporary file that is
removed automatically when the test completes.
import "cattlecloud.net/go/litesql/litesqltest"
Opening a test database
litesqltest.Open creates a *litesql.LiteDB in a temporary directory using
the litesqltest.TestConfiguration preset. That configuration keeps resource
usage low for tests by setting a smaller page cache and disables memory-mapped
I/O operations. Doing so preserves the same semantics (WAL journaling,
foreign keys, transactions) as a production database.
func TestUsingDatabase(t *testing.T) { ldb := litesqltest.Open(t) // The database is closed automatically when the test finishes. // ... use ldb exactly as you would any *litesql.LiteDB ... }
Because the database is a real SQLite3 file rather than an in-memory mock, the full feature set is available to your tests, including foreign-key enforcement and write transactions.
License
The cattlecloud.net/go/litesql module is opensource under the BSD-3-Clause license.