Show HN: I created a library for correctly using SQLite in Go programs

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-.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. err := db.Optimize(ctx) 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. err := db.Analyze(ctx) 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. err := db.Vacuum(ctx) 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. 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. The cattlecloud.net/go/litesql module is opensource under the BSD-3-Clause license.