sqlite

README

sqlite

SQLite driver module for rio, built on the pure-Go modernc.org/sqlite driver; no cgo. Provides constructors, error translation, and DSN hygiene; all SQL grammar lives in rio.

Install

go get github.com/go-rio/sqlite

Usage

import (
	"github.com/go-rio/rio"
	"github.com/go-rio/sqlite"
)

db, err := sqlite.Open("app.db")
if err != nil {
	// ...
}
defer db.Close()

users, err := rio.From[User]().Where("age > ?", 18).All(ctx, db)

New wraps an existing *sql.DB (bring your own pool):

sqlDB, err := sql.Open("sqlite", dsn) // modernc.org/sqlite
db := sqlite.New(sqlDB)

Default DSN parameters

Open appends two pragmas and one driver parameter unless the DSN already sets the same key.

Parameter	Default	Why
_pragma=foreign_keys	1	SQLite ships with foreign key enforcement off; constraints parse but never fire. Without it, rio.ErrForeignKeyViolated never fires on SQLite.
_pragma=busy_timeout	5000	Concurrent writers wait up to five seconds for the lock instead of failing immediately with SQLITE_BUSY.
_time_format	sqlite	time.Time written outside rio (raw database/sql through db.Unwrap()) is stored as SQLite-parseable text instead of Go's time.String() form, keeping the column format uniform. rio's own writes use its canonical text encoding regardless.

Override a default by setting the key yourself:

db, err := sqlite.Open("app.db?_pragma=busy_timeout(10000)")

New never touches the DSN; configure these on the pool you pass in.

In-memory databases

A plain :memory: DSN gives each pooled connection its own private empty database (SQLite's behavior). Since database/sql opens several connections by default, a table created on one is missing on the next. For a shared in-memory store, use a shared cache and pin the pool to one connection:

db, _ := sqlite.Open("file:app?mode=memory&cache=shared")
db.Unwrap().SetMaxOpenConns(1) // rio never tunes the pool for you

A file-backed DSN has no such caveat.

Concurrent writes

SQLite allows one writer at a time; concurrent writers serialize on the database lock. Two deployment shapes handle write concurrency.

Configure the file for concurrency (recommended for mixed read/write load):

db, err := sqlite.Open("app.db" +
	"?_txlock=immediate" +            // write txs take the lock up front: no
	                                  // non-retryable upgrade deadlocks
	"&_pragma=journal_mode(WAL)" +    // readers never block the writer
	"&_pragma=synchronous(NORMAL)" +  // WAL's recommended durability point
	"&_pragma=busy_timeout(10000)")

journal_mode(WAL) is persistent (it sticks to the database file) and creates -wal/-shm sidecar files. It does not work on read-only media or most network filesystems, which is why Open recommends rather than defaults it.

Serialize writes yourself: one connection, no contention, no busy handling.

db.Unwrap().SetMaxOpenConns(1)
db.Unwrap().SetMaxIdleConns(1)

Error translation

Unique and primary key violations return rio.ErrDuplicateKey; foreign key violations return rio.ErrForeignKeyViolated. The driver's own *sqlite.Error stays in the chain, so errors.As keeps working:

if err := rio.Insert(ctx, db, &user); errors.Is(err, rio.ErrDuplicateKey) {
	// email already taken
}

License

MIT

Documentation

Overview

Package sqlite connects rio, the zero-surprise Go ORM, to SQLite through the pure-Go modernc.org/sqlite driver — no cgo required.

Driver modules are deliberately thin. This package contains exactly three things, and nothing else:

• Constructors: Open (from a DSN) and New (bring your own *sql.DB), both returning a *rio.DB speaking the built-in rio.SQLite dialect.
• Precise error translation: SQLite constraint failures become rio.ErrDuplicateKey and rio.ErrForeignKeyViolated, with the driver error kept in the chain for errors.As.
• DSN hygiene: Open enables foreign key enforcement, a busy timeout, and the driver's SQLite-text time format (_time_format=sqlite, for non-rio writers sharing the handle) unless the DSN sets those keys itself.

All SQL grammar lives in github.com/go-rio/rio; this package never implements a dialect.

Index

• func New(db *sql.DB, opts ...rio.Option) *rio.DB
• func Open(dsn string, opts ...rio.Option) (*rio.DB, error)

Functions

func New

func New(db *sql.DB, opts ...rio.Option) *rio.DB

New wraps an existing *sql.DB — bring your own connection pool — in a *rio.DB speaking the rio.SQLite dialect with this package's error translator installed. A rio.WithErrorTranslator among opts wins over the built-in translator.

New performs no DSN hygiene, because the pool already exists. In particular, foreign key enforcement is whatever the caller's DSN says — and SQLite's historical default is off, in which case rio.ErrForeignKeyViolated can never happen (see Open).

func Open

func Open(dsn string, opts ...rio.Option) (*rio.DB, error)

Open opens a SQLite database and returns a *rio.DB speaking the rio.SQLite dialect with this package's error translator installed.

Before handing the DSN to modernc.org/sqlite, Open appends the default pragmas described on defaultPragmas — foreign_keys(1) and busy_timeout(5000) — plus the _time_format=sqlite driver parameter (see defaultParams). A key the DSN already sets is respected, never overridden. An empty DSN — SQLite's private, per-connection temporary on-disk database — is respelled as the equivalent "file:" URI first, so the defaults apply to it too.

Like database/sql itself, Open validates nothing eagerly: a bad path or DSN surfaces on first use (or on an explicit Ping).

In-memory databases: a plain ":memory:" (or "file::memory:" without a shared cache) gives every pooled connection its own private, empty database, so a table created on one connection is invisible to the next — the default database/sql pool opens several. For an in-memory database that behaves like one shared store, use a shared-cache DSN and pin the pool to a single connection:

db, _ := sqlite.Open("file:app?mode=memory&cache=shared")
db.Unwrap().SetMaxOpenConns(1) // rio never tunes the pool for you