SQL Database
database/sql is Go’s stdlib database layer — a thin interface over any relational database. Switch drivers (SQLite → Postgres → MySQL) by changing one import; the rest of your code stays put. This exercise wires up the essentials: open, create, insert, query.
Open is not connect
db, err := sql.Open("sqlite", ":memory:")Open validates the driver name and DSN format; the actual connection is lazy. Call db.Ping() or issue a query to force it. :memory: gives an in-process SQLite database — no file, no cleanup, perfect for tests.
Schema and writes with ExecContext
_, err = db.ExecContext(ctx, `CREATE TABLE tickets (
id INTEGER PRIMARY KEY AUTOINCREMENT,
title TEXT NOT NULL,
status TEXT NOT NULL
)`)
_, err = db.ExecContext(ctx, `INSERT INTO tickets (title, status) VALUES (?, ?)`, title, status)ExecContext runs statements that return no rows. Always use ? placeholders — never string-concatenate values (SQL injection). The Context variants respect cancellation; prefer them over the bare Exec.
Single-row query with QueryRowContext
var t Ticket
err = db.QueryRowContext(ctx, `SELECT id, title, status FROM tickets WHERE id = ?`, id).
Scan(&t.ID, &t.Title, &t.Status)
if errors.Is(err, sql.ErrNoRows) {
return Ticket{}, ErrNotFound
}QueryRowContext returns exactly one row; Scan fills the variables in column order. sql.ErrNoRows is the sentinel for empty results — check it explicitly before treating the error as fatal.
Multi-row query with rows.Next
rows, err := db.QueryContext(ctx, `SELECT id, title, status FROM tickets`)
if err != nil { return nil, err }
defer rows.Close()
var tickets []Ticket
for rows.Next() {
var t Ticket
if err := rows.Scan(&t.ID, &t.Title, &t.Status); err != nil {
return nil, err
}
tickets = append(tickets, t)
}
return tickets, rows.Err() // rows.Err() surfaces iterator errorsAlways defer rows.Close() and check rows.Err() after the loop — the iterator can fail mid-stream.
Hint
Open with sql.Open("sqlite", ":memory:"), use db.ExecContext for DDL and writes, db.QueryRowContext(...).Scan for single-row reads, db.QueryContext + rows.Next loop for lists.
For Add, retrieve the auto-generated ID with result.LastInsertId() from the return value of ExecContext.
For List, return an empty []Ticket{} (not nil) when there are no rows so callers can safely range over it.
Setup
Install the pure-Go SQLite driver (no CGO required):
go get modernc.org/sqliteTask
In store.go, implement the four functions:
NewStore(ctx context.Context) (*Store, error)— open an in-memory SQLite DB withsql.Open("sqlite", ":memory:"), create theticketstable, return a*Store.(s *Store) Add(ctx context.Context, title, status string) (int64, error)— insert a ticket using?placeholders, return the new row’s auto-generated ID.(s *Store) Get(ctx context.Context, id int64) (Ticket, error)— query a single ticket by id; returnErrNotFoundwhen no row matches.(s *Store) List(ctx context.Context) ([]Ticket, error)— return all tickets ordered by id; return an empty (non-nil) slice when the table is empty.
Check
go test ./exercises/18_database/01_sql/ -vSource: exercises/18_database/01_sql/README.md · Inspired by Mainmatter’s 100 Exercises to Learn Rust (CC BY-NC 4.0) — all Go prose here is original, free for non-commercial use.