README
¶
driver-sqlite — SQLite for sqlb
driver-sqlite opens SQLite databases for github.com/ubgo/sqlb, with the pragmas a library-owned schema needs already applied.
It is a separate Go module at contrib/driver-sqlite inside the ubgo/sqlb repository. The core stays dependency-free; modernc.org/sqlite is pulled in only when you import this. That driver is pure Go, so this module needs no cgo and cross-compiles.
Why it exists
Left at its defaults, SQLite does three things that are wrong for a process whose libraries own tables, and all three are invisible until production.
- Foreign keys are not enforced. SQLite parses the constraints your schema declares and then ignores them. A delete that should fail instead orphans rows, silently.
- A contended write fails immediately. The second writer gets
SQLITE_BUSYrather than waiting, which is the error everyone meets first and understands last. - Readers queue behind the writer. Without WAL, one write blocks every read in the process.
Open turns all three around, in one place, and says why in the source. Everything else is database/sql.
Install
go get github.com/ubgo/sqlb/contrib/driver-sqlite@latest
The module is tagged contrib/driver-sqlite/v0.1.0 and requires the core at v0.1.0. Inside the repository the committed go.work resolves the core against the checkout and needs nothing.
Use
db, err := driversqlite.Open("app.db")
if err != nil {
return err
}
defer db.Close()
var id, email string
q, err := sqlb.NewSelect(sqlb.SQLiteDialect{}).
Columns(sqlb.Bind(UserID, &id), sqlb.Bind(UserEmail, &email)).
From(Users).
Where(sqlb.Eq(UserEmail, "a@example.com")).
Build()
if err != nil {
return err
}
err = sqlb.One(ctx, db, q)
The dialect is sqlb.SQLiteDialect{} from the core. This module adds no dialect of its own; there would be nothing in it.
In tests:
db, err := driversqlite.Memory()
The defaults
| Pragma | Value | What it fixes |
|---|---|---|
journal_mode |
WAL |
readers run while a writer commits |
busy_timeout |
5000 |
a contended write waits up to five seconds instead of failing at once |
foreign_keys |
ON |
the constraints the schema declares are enforced |
synchronous |
NORMAL |
the documented companion of WAL: durable against a process crash |
They are applied through the DSN, not through an Exec. SQLite scopes most pragmas to a connection, so setting one with a bare Exec configures whichever pooled connection happened to serve it and leaves the others alone. In the DSN they hold for every connection the pool opens, including the ones it opens later. TestPragmas_AppliedToEveryConnection is what proves it.
busy_timeout does not cover every contended write, and two refusals escape it at once, both measured with two handles on one file. A deferred transaction whose first statement reads holds a read lock, and SQLite refuses its upgrade to a write while another connection writes, without consulting the timeout; begin a transaction that will write with BEGIN IMMEDIATE on a pinned *sql.Conn instead. And on a file not yet in WAL mode, the journal_mode=WAL this module applies to each new connection fails as busy while another connection is in the file, so opening a connection can fail; a file converted once stays in WAL and stops hitting it. contrib/migrate's ledger handles both itself, which is why two deploys on one file wait for each other there.
Override one, keep the rest:
db, err := driversqlite.Open("app.db", driversqlite.WithPragma("journal_mode", "DELETE"))
Drop them all and start from SQLite's own defaults:
db, err := driversqlite.Open("app.db", driversqlite.WithoutDefaultPragmas())
The set above is driversqlite.DefaultPragmas, a slice of Pragma values, so a caller can read what is about to be applied rather than take this table's word for it:
for _, p := range driversqlite.DefaultPragmas {
fmt.Println(p.Name, p.Value)
}
Pool size
db, err := driversqlite.Open("app.db", driversqlite.WithMaxOpenConns(1))
Worth knowing rather than worth ignoring. SQLite allows one writer at a time, so under write contention a larger pool converts what would be a queue into SQLITE_BUSY retries against busy_timeout. One connection serialises writes in Go instead, which is the right answer for a write-heavy embedded database and the wrong one for a read-heavy one.
The driver name
driversqlite.DriverName is the string this module registers with database/sql, exported so a caller can open a second handle themselves — for a tool, a migration runner, or a test — without hardcoding a name that would then be wrong if it changed:
db, err := sql.Open(driversqlite.DriverName, dsn)
Doing that gives up the pragmas, which is exactly why Open exists.
Documentation
A per-symbol cookbook is in docs/README.md and docs/features.md.
What this module really is for
Rendering SQL and executing it are different claims. The builder's own tests prove the text; only a database proves that the placeholders, the argument order and the scan targets line up. This module runs the shared conformance suite from github.com/ubgo/sqlb/sqlbtest against a real database, twice: in memory and file-backed.
task conformance
It also executes every construct in github.com/ubgo/sqlb/sqlite against that database: the JSON1 functions, FTS5 search, the time modifiers, the planner hints, and the difference between INSERT OR REPLACE and ON CONFLICT DO UPDATE, which is that the first changes the rowid and the second does not. That makes sqlite the only engine package in this repository whose SQL is proven rather than asserted.
Real output, both databases, every case:
--- PASS: TestConformance (0.00s)
--- PASS: TestConformance/insert_select_one (0.00s)
--- PASS: TestConformance/param_order (0.00s)
--- PASS: TestConformance/null_scan (0.00s)
--- PASS: TestConformance/no_rows (0.00s)
--- PASS: TestConformance/multiple_rows (0.00s)
--- PASS: TestConformance/each_order (0.00s)
--- PASS: TestConformance/empty_in (0.00s)
--- PASS: TestConformance/update (0.00s)
--- PASS: TestConformance/delete (0.00s)
--- PASS: TestConformance/join_group (0.00s)
--- PASS: TestConformance/limit_offset (0.00s)
--- PASS: TestConformance/returning (0.00s)
--- PASS: TestConformance_FileBacked/insert_select_one (0.00s)
--- PASS: TestConformance_FileBacked/param_order (0.00s)
--- PASS: TestConformance_FileBacked/null_scan (0.00s)
--- PASS: TestConformance_FileBacked/no_rows (0.00s)
--- PASS: TestConformance_FileBacked/multiple_rows (0.00s)
--- PASS: TestConformance_FileBacked/each_order (0.00s)
--- PASS: TestConformance_FileBacked/empty_in (0.00s)
--- PASS: TestConformance_FileBacked/update (0.00s)
--- PASS: TestConformance_FileBacked/delete (0.00s)
--- PASS: TestConformance_FileBacked/join_group (0.00s)
--- PASS: TestConformance_FileBacked/limit_offset (0.00s)
--- PASS: TestConformance_FileBacked/returning (0.00s)
ok github.com/ubgo/sqlb/contrib/driver-sqlite 0.379s
License
Apache License 2.0, the same as the parent module. The driver it depends on, modernc.org/sqlite, carries its own license.
Documentation
¶
Overview ¶
Package driversqlite opens SQLite databases for github.com/ubgo/sqlb.
It is a separate Go module inside the sqlb repository. The reason is the rule the core lives by: sqlb itself depends on nothing but the standard library, so a library that embeds it inherits no driver, no cgo and no version conflict. A driver is a dependency, so it lives out here, and an application pays for it only by importing this module.
What the module adds over calling sql.Open yourself is the pragma set. Left at its defaults, SQLite does not enforce foreign keys, fails a concurrent writer immediately instead of waiting, and serialises readers behind the writer. None of that is a sensible default for a process whose libraries own tables, and all three are invisible until production. Open turns them around and says so in one place.
The module's real work is its test: it runs the shared conformance suite in github.com/ubgo/sqlb/sqlbtest against a real database, which is the only thing that proves the builder's placeholders, argument order and scan targets line up outside of rendered text.
Index ¶
Constants ¶
const DriverName = "sqlite"
DriverName is the database/sql name modernc.org/sqlite registers.
Variables ¶
var DefaultPragmas = []Pragma{
{Name: "journal_mode", Value: "WAL"},
{Name: "busy_timeout", Value: "5000"},
{Name: "foreign_keys", Value: "ON"},
{Name: "synchronous", Value: "NORMAL"},
}
DefaultPragmas is what Open applies unless WithoutDefaultPragmas is passed.
Each one turns off a default that is wrong for a library-owned schema:
- journal_mode=WAL lets readers run while a writer commits. Without it a single write blocks every read in the process.
- busy_timeout=5000 makes a contended write wait up to five seconds instead of returning SQLITE_BUSY at once, which is the error everyone hits first and understands last.
- foreign_keys=ON enforces the constraints the schema declares. SQLite parses them and ignores them by default, so a delete that should fail quietly orphans rows.
- synchronous=NORMAL is the documented companion of WAL: durable against a process crash, and only a power loss can lose the last commits.
Functions ¶
func Memory ¶
Memory opens a private in-memory database for tests.
It pins the pool to one connection. An in-memory SQLite database belongs to the connection that created it, so a pool of two serves the second query from a second, empty database and the schema appears to vanish. One connection is the only setting where that cannot happen, and a test is never the place to debug it.
The database exists until the returned *sql.DB is closed.
func Open ¶
Open opens a SQLite database with the pragmas applied to every connection.
dsn is a modernc.org/sqlite DSN: a file path, or a file: URI. Open appends its pragmas as query parameters, so a dsn that already carries _pragma keeps it and the appended ones are applied after.
Open does not ping. A SQLite file is created on first use, so a failure to open is a permissions or path problem the first statement reports with more context than a ping would.
Types ¶
type Option ¶
type Option func(*config)
Option configures Open. It is the one escape hatch: anything the defaults get wrong for a caller is reachable by name rather than by giving up on the helper and calling sql.Open directly.
func WithMaxOpenConns ¶
WithMaxOpenConns caps the pool. SQLite takes one writer at a time, so a large pool buys nothing for writes and costs contention; it is worth raising only for a read-heavy load under WAL.
func WithPragma ¶
WithPragma adds a pragma, or overrides a default of the same name. The last one with a given name wins, so WithPragma("journal_mode", "DELETE") turns WAL off without turning the other defaults off with it.
func WithoutDefaultPragmas ¶
func WithoutDefaultPragmas() Option
WithoutDefaultPragmas drops DefaultPragmas, leaving only what WithPragma adds. For a caller who has their own considered set and wants nothing applied behind it.
type Pragma ¶
Pragma is one SQLite PRAGMA applied to every connection in the pool.
Per-connection is the invariant that matters: SQLite scopes most pragmas to a connection, so setting one with a bare Exec configures whichever pooled connection happened to serve it and silently leaves the others alone. Passing them in the DSN is what makes them hold for every connection the pool opens, including the ones it opens later.
Source Files