Automate the Boring Stuff with Go, Ch. 19: SQLite
Part of Automate the Boring Stuff with Go, a beginner-friendly guide to programming in Go.
New in the 2026 edition.
Sooner or later a script needs to remember something between runs: what it already processed, what the folder looked like yesterday, the running total. The Python original reaches for Google Sheets at this point, which needs an account, an API key and a network. SQLite needs a file. It is a complete SQL database in a single file on disk, it is already on your phone and in your browser, and from Go it takes one import. The code is under ch19.
The driver is modernc.org/sqlite, a pure Go translation of SQLite: no C compiler, and the cross-compiled binaries from chapter 16 keep working. In the repo it is already in go.mod; elsewhere, go get modernc.org/sqlite.
The basics
// a database in one file, no server to install: create, insert, query, update, delete
package main
import (
"database/sql"
"fmt"
"os"
"path/filepath"
_ "modernc.org/sqlite" // registers the "sqlite" driver; pure Go, no C compiler needed
)
type Book struct {
ID int
Title string
Year int
}
func run() error {
dir, err := os.MkdirTemp("", "sqlite-basics")
if err != nil {
return err
}
defer os.RemoveAll(dir)
db, err := sql.Open("sqlite", filepath.Join(dir, "library.db"))
if err != nil {
return err
}
defer db.Close()
// the schema: run it every start; IF NOT EXISTS makes it harmless the second time
_, err = db.Exec(`CREATE TABLE IF NOT EXISTS books (
id INTEGER PRIMARY KEY,
title TEXT NOT NULL,
year INTEGER NOT NULL
)`)
if err != nil {
return err
}
// ? placeholders: the driver passes values separately, so a title with a quote in it
// is data, never SQL
for _, b := range []Book{{Title: "The Go Programming Language", Year: 2015}, {Title: "Automate the Boring Stuff with Go", Year: 2026}, {Title: "Let's Go", Year: 2019}} {
if _, err := db.Exec(`INSERT INTO books (title, year) VALUES (?, ?)`, b.Title, b.Year); err != nil {
return err
}
}
rows, err := db.Query(`SELECT id, title, year FROM books WHERE year >= ? ORDER BY year`, 2015)
if err != nil {
return err
}
defer rows.Close()
for rows.Next() {
var b Book
if err := rows.Scan(&b.ID, &b.Title, &b.Year); err != nil {
return err
}
fmt.Printf("%d %-36s %d\n", b.ID, b.Title, b.Year)
}
if err := rows.Err(); err != nil {
return err
}
// one value: QueryRow
var count int
if err := db.QueryRow(`SELECT count(*) FROM books`).Scan(&count); err != nil {
return err
}
fmt.Println("books:", count)
res, err := db.Exec(`UPDATE books SET year = ? WHERE title = ?`, 2016, "The Go Programming Language")
if err != nil {
return err
}
n, _ := res.RowsAffected()
fmt.Println("updated rows:", n)
if _, err := db.Exec(`DELETE FROM books WHERE year < ?`, 2019); err != nil {
return err
}
db.QueryRow(`SELECT count(*) FROM books`).Scan(&count)
fmt.Println("books after delete:", count)
return nil
}
func main() {
if err := run(); err != nil {
fmt.Fprintln(os.Stderr, "error:", err)
os.Exit(1)
}
}
1 The Go Programming Language 2015
3 Let's Go 2019
2 Automate the Boring Stuff with Go 2026
books: 3
updated rows: 1
books after delete: 2
Everything goes through database/sql, the standard interface every Go SQL driver plugs into. Exec for statements that change things, Query for rows, QueryRow for exactly one value, Scan to copy a row into Go variables. The ? placeholders are not a convenience: the driver sends the values separately from the SQL, so a title containing a quote is data and nothing more. Never build SQL with Sprintf.
Transactions
// a transaction makes many writes one unit: all of them land, or none, and it is faster
package main
import (
"database/sql"
"errors"
"fmt"
"os"
"path/filepath"
"time"
_ "modernc.org/sqlite"
)
func run() error {
dir, err := os.MkdirTemp("", "transactions")
if err != nil {
return err
}
defer os.RemoveAll(dir)
db, err := sql.Open("sqlite", filepath.Join(dir, "t.db"))
if err != nil {
return err
}
defer db.Close()
if _, err := db.Exec(`CREATE TABLE readings (sensor TEXT, value REAL)`); err != nil {
return err
}
// one statement at a time: every Exec is its own transaction and its own disk sync
start := time.Now()
for i := range 500 {
if _, err := db.Exec(`INSERT INTO readings VALUES (?, ?)`, "a", float64(i)); err != nil {
return err
}
}
fmt.Println("500 separate inserts:", time.Since(start).Round(time.Millisecond))
// the same work inside one transaction
start = time.Now()
tx, err := db.Begin()
if err != nil {
return err
}
stmt, err := tx.Prepare(`INSERT INTO readings VALUES (?, ?)`)
if err != nil {
return err
}
for i := range 500 {
if _, err := stmt.Exec("b", float64(i)); err != nil {
tx.Rollback()
return err
}
}
stmt.Close()
if err := tx.Commit(); err != nil {
return err
}
fmt.Println("500 inserts in one transaction:", time.Since(start).Round(time.Millisecond))
// all or nothing: a failure in the middle rolls everything back
err = func() error {
tx, err := db.Begin()
if err != nil {
return err
}
defer tx.Rollback() // a no-op after Commit; the safety net otherwise
if _, err := tx.Exec(`INSERT INTO readings VALUES ('c', 1)`); err != nil {
return err
}
if true {
return errors.New("sensor c reported nonsense, abandoning the batch")
}
return tx.Commit()
}()
fmt.Println("batch c:", err)
var n int
db.QueryRow(`SELECT count(*) FROM readings WHERE sensor = 'c'`).Scan(&n)
fmt.Println("rows from sensor c:", n)
return nil
}
func main() {
if err := run(); err != nil {
fmt.Fprintln(os.Stderr, "error:", err)
os.Exit(1)
}
}
500 separate inserts: 114ms
500 inserts in one transaction: 1ms
batch c: sensor c reported nonsense, abandoning the batch
rows from sensor c: 0
A hundred times faster is not a typo: every statement outside a transaction waits for the disk to confirm the write. The second half matters more than the speed. A transaction either commits whole or leaves no trace, so a loop that fails halfway cannot leave a half-imported file behind. The defer tx.Rollback() right after Begin is the idiom: it does nothing after a successful Commit, and it cleans up on every other path out of the function.
Project: an expenses database
The first project takes chapter 12’s expenses CSV and keeps it in SQLite, where the questions are one line of SQL each:
// expenses-db keeps the expenses from chapter 12 in a SQLite file and answers questions
// about them with SQL. Import a CSV as often as you like (duplicates are skipped),
// print a report, or run any query.
//
// go run ./ch19/projects/expenses-db import ./ch12/projects/csv-to-json/expenses.csv
// go run ./ch19/projects/expenses-db report
// go run ./ch19/projects/expenses-db query "SELECT item, amount FROM expenses ORDER BY amount DESC LIMIT 3"
package main
import (
"database/sql"
"encoding/csv"
"errors"
"fmt"
"io"
"os"
"strconv"
"strings"
"text/tabwriter"
_ "modernc.org/sqlite"
)
const schema = `
CREATE TABLE IF NOT EXISTS expenses (
id INTEGER PRIMARY KEY,
date TEXT NOT NULL,
category TEXT NOT NULL,
item TEXT NOT NULL,
amount REAL NOT NULL,
UNIQUE (date, category, item, amount)
);`
// Open opens (or creates) the database and makes sure the schema exists.
func Open(path string) (*sql.DB, error) {
db, err := sql.Open("sqlite", path)
if err != nil {
return nil, err
}
if _, err := db.Exec(schema); err != nil {
db.Close()
return nil, err
}
return db, nil
}
// Import loads CSV rows with the columns date, category, item, amount (any order) and
// returns how many were new. Rows already present are skipped, so re-importing is safe.
func Import(db *sql.DB, r io.Reader) (int, error) {
rows, err := csv.NewReader(r).ReadAll()
if err != nil {
return 0, err
}
if len(rows) == 0 {
return 0, errors.New("empty file")
}
col := map[string]int{}
for i, name := range rows[0] {
col[strings.ToLower(strings.TrimSpace(name))] = i
}
for _, need := range []string{"date", "category", "item", "amount"} {
if _, ok := col[need]; !ok {
return 0, fmt.Errorf("missing column %q", need)
}
}
tx, err := db.Begin()
if err != nil {
return 0, err
}
defer tx.Rollback()
stmt, err := tx.Prepare(`INSERT OR IGNORE INTO expenses (date, category, item, amount) VALUES (?, ?, ?, ?)`)
if err != nil {
return 0, err
}
defer stmt.Close()
added := 0
for n, row := range rows[1:] {
amount, err := strconv.ParseFloat(strings.TrimSpace(row[col["amount"]]), 64)
if err != nil {
return 0, fmt.Errorf("row %d: amount %q: %w", n+2, row[col["amount"]], err)
}
res, err := stmt.Exec(row[col["date"]], row[col["category"]], row[col["item"]], amount)
if err != nil {
return 0, err
}
if k, _ := res.RowsAffected(); k > 0 {
added++
}
}
return added, tx.Commit()
}
// Query runs any SELECT and prints the result as a table, whatever its columns are.
func Query(db *sql.DB, w io.Writer, query string, args ...any) error {
rows, err := db.Query(query, args...)
if err != nil {
return err
}
defer rows.Close()
cols, err := rows.Columns()
if err != nil {
return err
}
tw := tabwriter.NewWriter(w, 0, 4, 2, ' ', 0)
fmt.Fprintln(tw, strings.Join(cols, "\t"))
values := make([]any, len(cols))
ptrs := make([]any, len(cols))
for i := range values {
ptrs[i] = &values[i]
}
for rows.Next() {
if err := rows.Scan(ptrs...); err != nil {
return err
}
cells := make([]string, len(cols))
for i, v := range values {
switch x := v.(type) {
case []byte:
cells[i] = string(x)
case float64:
cells[i] = strconv.FormatFloat(x, 'f', 2, 64)
default:
cells[i] = fmt.Sprint(x)
}
}
fmt.Fprintln(tw, strings.Join(cells, "\t"))
}
if err := rows.Err(); err != nil {
return err
}
return tw.Flush()
}
// Report prints totals by category and by month.
func Report(db *sql.DB, w io.Writer) error {
fmt.Fprintln(w, "by category")
if err := Query(db, w, `SELECT category, count(*) AS items, round(sum(amount), 2) AS total FROM expenses GROUP BY category ORDER BY total DESC`); err != nil {
return err
}
fmt.Fprintln(w, "\nby month")
return Query(db, w, `SELECT substr(date, 1, 7) AS month, round(sum(amount), 2) AS total FROM expenses GROUP BY month ORDER BY month`)
}
func run(args []string, out io.Writer) error {
if len(args) == 0 {
return errors.New("usage: expenses-db [-db file] import <csv> | report | query <sql>")
}
dbPath := "expenses.db"
if args[0] == "-db" && len(args) > 2 {
dbPath, args = args[1], args[2:]
}
db, err := Open(dbPath)
if err != nil {
return err
}
defer db.Close()
switch args[0] {
case "import":
if len(args) != 2 {
return errors.New("import needs a CSV file")
}
f, err := os.Open(args[1])
if err != nil {
return err
}
defer f.Close()
n, err := Import(db, f)
if err != nil {
return err
}
fmt.Fprintf(out, "imported %d new row(s) into %s\n", n, dbPath)
return nil
case "report":
return Report(db, out)
case "query":
if len(args) != 2 {
return errors.New("query needs one SQL string")
}
return Query(db, out, args[1])
default:
return fmt.Errorf("unknown command %q", args[0])
}
}
func main() {
if err := run(os.Args[1:], os.Stdout); err != nil {
fmt.Fprintln(os.Stderr, "error:", err)
os.Exit(1)
}
}
$ go run ./ch19/projects/expenses-db import ./ch12/projects/csv-to-json/expenses.csv
imported 5 new row(s) into expenses.db
$ go run ./ch19/projects/expenses-db report
by category
category items total
transport 1 32.00
food 3 21.00
books 1 8.25
by month
month total
2026-03 61.25
$ go run ./ch19/projects/expenses-db query "SELECT item, amount FROM expenses ORDER BY amount DESC LIMIT 3"
item amount
bus pass 32.00
lunch 12.00
Go book, used 8.25
Three ideas to keep. The UNIQUE constraint plus INSERT OR IGNORE makes import safe to run twice: the second run adds nothing, and the test checks exactly that. Query prints any result as a table without knowing the columns in advance, by asking rows.Columns() and scanning into a slice of any; that one function turns the program into a small SQL console. And the import runs inside a transaction, so a bad row on line 4,000 leaves the database as it was.
Project: a file index
The second project remembers what a folder looked like. Each index run records every file’s path, size, modification time and SHA-256 as a new run; the other commands are queries over those runs:
// file-index records every file under a folder (path, size, modified time, SHA-256) in a
// SQLite database, one run at a time, and then answers the questions that take a long
// time to answer by walking: what changed since last time, what is duplicated, what is
// biggest.
//
// go run ./ch19/projects/file-index index ~/Documents
// go run ./ch19/projects/file-index changes
// go run ./ch19/projects/file-index dupes
// go run ./ch19/projects/file-index largest -n 10
package main
import (
"crypto/sha256"
"database/sql"
"encoding/hex"
"errors"
"flag"
"fmt"
"io"
"io/fs"
"os"
"path/filepath"
"strconv"
"text/tabwriter"
"time"
_ "modernc.org/sqlite"
)
const schema = `
CREATE TABLE IF NOT EXISTS runs (
id INTEGER PRIMARY KEY,
root TEXT NOT NULL,
started TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS files (
run_id INTEGER NOT NULL REFERENCES runs(id),
path TEXT NOT NULL,
size INTEGER NOT NULL,
mtime TEXT NOT NULL,
sha256 TEXT NOT NULL,
PRIMARY KEY (run_id, path)
);
CREATE INDEX IF NOT EXISTS files_sha ON files (run_id, sha256);`
func Open(path string) (*sql.DB, error) {
db, err := sql.Open("sqlite", path)
if err != nil {
return nil, err
}
if _, err := db.Exec(schema); err != nil {
db.Close()
return nil, err
}
return db, nil
}
func hashFile(path string) (string, error) {
f, err := os.Open(path)
if err != nil {
return "", err
}
defer f.Close()
h := sha256.New()
if _, err := io.Copy(h, f); err != nil {
return "", err
}
return hex.EncodeToString(h.Sum(nil)), nil
}
// Index walks root and records every regular file as a new run. It returns the run id
// and the number of files recorded.
func Index(db *sql.DB, root string, now time.Time) (runID int64, count int, err error) {
abs, err := filepath.Abs(root)
if err != nil {
return 0, 0, err
}
tx, err := db.Begin()
if err != nil {
return 0, 0, err
}
defer tx.Rollback()
res, err := tx.Exec(`INSERT INTO runs (root, started) VALUES (?, ?)`, abs, now.UTC().Format(time.RFC3339))
if err != nil {
return 0, 0, err
}
runID, _ = res.LastInsertId()
stmt, err := tx.Prepare(`INSERT INTO files (run_id, path, size, mtime, sha256) VALUES (?, ?, ?, ?, ?)`)
if err != nil {
return 0, 0, err
}
defer stmt.Close()
err = filepath.WalkDir(abs, func(p string, d fs.DirEntry, walkErr error) error {
if walkErr != nil || d.IsDir() {
return nil
}
info, err := d.Info()
if err != nil || !info.Mode().IsRegular() {
return nil
}
sum, err := hashFile(p)
if err != nil {
return nil // vanished or unreadable: not this run's problem
}
rel, _ := filepath.Rel(abs, p)
if _, err := stmt.Exec(runID, rel, info.Size(), info.ModTime().UTC().Format(time.RFC3339Nano), sum); err != nil {
return err
}
count++
return nil
})
if err != nil {
return 0, 0, err
}
return runID, count, tx.Commit()
}
// lastTwoRuns returns the ids of the latest and the previous run for the same root.
func lastTwoRuns(db *sql.DB) (latest, previous int64, err error) {
rows, err := db.Query(`SELECT id FROM runs WHERE root = (SELECT root FROM runs ORDER BY id DESC LIMIT 1) ORDER BY id DESC LIMIT 2`)
if err != nil {
return 0, 0, err
}
defer rows.Close()
var ids []int64
for rows.Next() {
var id int64
if err := rows.Scan(&id); err != nil {
return 0, 0, err
}
ids = append(ids, id)
}
if len(ids) < 2 {
return 0, 0, errors.New("need two runs of the same folder to compare; run index again later")
}
return ids[0], ids[1], nil
}
// Changes prints files added, removed or modified between the last two runs.
func Changes(db *sql.DB, w io.Writer) error {
latest, previous, err := lastTwoRuns(db)
if err != nil {
return err
}
queries := []struct{ label, sql string }{
{"added", `SELECT path FROM files WHERE run_id = ?1 AND path NOT IN (SELECT path FROM files WHERE run_id = ?2) ORDER BY path`},
{"removed", `SELECT path FROM files WHERE run_id = ?2 AND path NOT IN (SELECT path FROM files WHERE run_id = ?1) ORDER BY path`},
{"modified", `SELECT a.path FROM files a JOIN files b ON a.path = b.path AND b.run_id = ?2 WHERE a.run_id = ?1 AND a.sha256 <> b.sha256 ORDER BY a.path`},
}
for _, q := range queries {
rows, err := db.Query(q.sql, latest, previous)
if err != nil {
return err
}
for rows.Next() {
var p string
if err := rows.Scan(&p); err != nil {
rows.Close()
return err
}
fmt.Fprintf(w, "%-9s %s\n", q.label, p)
}
rows.Close()
}
return nil
}
// Dupes prints groups of identical files from the latest run.
func Dupes(db *sql.DB, w io.Writer) error {
rows, err := db.Query(`
SELECT sha256, size, group_concat(path, char(10))
FROM files
WHERE run_id = (SELECT max(id) FROM runs)
GROUP BY sha256 HAVING count(*) > 1
ORDER BY size DESC`)
if err != nil {
return err
}
defer rows.Close()
n := 0
for rows.Next() {
var sum, paths string
var size int64
if err := rows.Scan(&sum, &size, &paths); err != nil {
return err
}
n++
fmt.Fprintf(w, "group %d (%d bytes each, %s...)\n%s\n", n, size, sum[:12], indent(paths))
}
fmt.Fprintf(w, "%d group(s) of duplicates\n", n)
return rows.Err()
}
func indent(lines string) string {
out := ""
for _, l := range splitLines(lines) {
out += " " + l + "\n"
}
return out
}
func splitLines(s string) []string {
var out []string
start := 0
for i := 0; i < len(s); i++ {
if s[i] == '\n' {
out = append(out, s[start:i])
start = i + 1
}
}
return append(out, s[start:])
}
// Largest prints the n biggest files from the latest run.
func Largest(db *sql.DB, w io.Writer, n int) error {
rows, err := db.Query(`SELECT size, path FROM files WHERE run_id = (SELECT max(id) FROM runs) ORDER BY size DESC LIMIT ?`, n)
if err != nil {
return err
}
defer rows.Close()
tw := tabwriter.NewWriter(w, 0, 4, 2, ' ', tabwriter.AlignRight)
for rows.Next() {
var size int64
var p string
if err := rows.Scan(&size, &p); err != nil {
return err
}
fmt.Fprintf(tw, "%s\t %s\n", strconv.FormatInt(size, 10), p)
}
tw.Flush()
return rows.Err()
}
func run(args []string, out io.Writer) error {
if len(args) == 0 {
return errors.New("usage: file-index [-db file] index <folder> | changes | dupes | largest [-n 10]")
}
dbPath := "file-index.db"
if args[0] == "-db" && len(args) > 2 {
dbPath, args = args[1], args[2:]
}
db, err := Open(dbPath)
if err != nil {
return err
}
defer db.Close()
switch args[0] {
case "index":
if len(args) != 2 {
return errors.New("index needs a folder")
}
id, n, err := Index(db, args[1], time.Now())
if err != nil {
return err
}
fmt.Fprintf(out, "run %d: %d file(s) indexed into %s\n", id, n, dbPath)
return nil
case "changes":
return Changes(db, out)
case "dupes":
return Dupes(db, out)
case "largest":
flags := flag.NewFlagSet("largest", flag.ContinueOnError)
n := flags.Int("n", 10, "how many")
if err := flags.Parse(args[1:]); err != nil {
return err
}
return Largest(db, out, *n)
default:
return fmt.Errorf("unknown command %q", args[0])
}
}
func main() {
if err := run(os.Args[1:], os.Stdout); err != nil {
fmt.Fprintln(os.Stderr, "error:", err)
os.Exit(1)
}
}
$ go run ./ch19/projects/file-index index ./ch14/projects/find-duplicates/sample
run 1: 6 file(s) indexed into file-index.db
$ go run ./ch19/projects/file-index dupes
group 1 (28 bytes each, 2cde9f23508b...)
backup/IMG_0001.txt
photos/IMG_0001 copy.txt
photos/IMG_0001.txt
group 2 (15 bytes each, c8ddebce1666...)
backup/IMG_0002.txt
photos/IMG_0002.txt
2 group(s) of duplicates
$ go run ./ch19/projects/file-index largest -n 3
28 backup/IMG_0001.txt
28 photos/IMG_0001 copy.txt
28 photos/IMG_0001.txt
Chapter 14 found duplicates by hashing everything every time. Here the hashing happens once per run and the duplicate question becomes GROUP BY sha256 HAVING count(*) > 1, which SQLite answers in milliseconds over a million rows. changes compares the last two runs with three queries, added, removed and modified, and that is a backup-verification tool, a “what did the update touch” tool, and a “who put a 4 GB file in the shared drive” tool, depending on where you point it. The test indexes a folder, edits it, indexes again, and checks all three lists.
Two design choices to notice: the walk, the hashing and the inserts happen inside one transaction per run, so an interrupted run leaves no partial run behind; and files that vanish or cannot be read mid-walk are skipped rather than fatal, because an index that gives up on the first locked file is not an index.
Conclusion
database/sql plus one import, ? placeholders, transactions around every batch, and a schema with IF NOT EXISTS run at every start. SQLite is the right answer for most “my script needs to remember” problems, and knowing a little SQL turns ten-line loops into one-line questions.