These are the notes for Go episode 67. Raju's API kept its orders in a Go map, so every restart wiped them and two servers disagreed about which orders existed. We move the data into a real SQL database using Go's standard database/sql package and a driver for SQLite. On the way we hit every classic mistake for real: a forgotten driver import, sql.Open returning nil for a database that can't be opened, an UPDATE that "succeeds" without changing anything, a forgotten rows.Close() that freezes the server, a missing row, and a NULL that won't fit in a Go string. Then we tune the connection pool and read its numbers with db.Stats().
All outputs are from real runs (Go 1.24.7, linux/amd64) with the SQLite driver github.com/mattn/go-sqlite3 v1.14.32, a cgo driver (it compiles SQLite's C code, so it needs CGO_ENABLED=1 and gcc). In this sandbox the module comes from tools/goproxy_x.sh, a local module proxy built from GitHub. Timings change between runs.
Facts and sources
From the Go documentation of the installed toolchain (go doc database/sql, Go 1.24.7), its source and API files, and the driver READMEs fetched in this run:
go doc database/sql.Open: "No database drivers are included in the Go standard library." and "Open may just validate its arguments without creating a connection to the database. To verify that the data source name is valid, call DB.Ping." and "The returned DB is safe for concurrent use by multiple goroutines and maintains its own pool of idle connections. Thus, the Open function should be called just once. It is rarely necessary to close a DB."go doc database/sql.DB: "DB is a database handle representing a pool of zero or more underlying connections. It's safe for concurrent use by multiple goroutines."go doc database/sql.ErrNoRows: returned "by Row.Scan when DB.QueryRow doesn't return a row".go doc database/sql.Rows.Close: "If Rows.Next is called and returns false and there are no further result sets, the Rows are closed automatically and it will suffice to check the result of Rows.Err. Close is idempotent and does not affect the result of Rows.Err."database/sqlsource (sql.go):const defaultMaxIdleConns = 2, the default forSetMaxIdleConns.sql.Null[T]is listed in$(go env GOROOT)/api/go1.22.txt: it was added in Go 1.22.- mattn/go-sqlite3 README: "A sqlite3 driver that conforms to the built-in database/sql interface."; "go-sqlite3 is cgo package."; it requires "the
CGO_ENABLED=1environment variable ... and the presence of thegcccompiler". - lib/pq README: "pq is a Go PostgreSQL driver for database/sql."; "Use the
postgresdriver name in thesql.Open()call". - jackc/pgx README: "pgx is a pure Go driver and toolkit for PostgreSQL."; it "exposes PostgreSQL-specific features such as
LISTEN/NOTIFYandCOPY. It also includes an adapter for the standarddatabase/sqlinterface." - Everything else is from the real runs below.
Why a database
Real-life example: orders written on a whiteboard. It's quick to write on, but the cleaning staff wipe it every night, and a second counter has its own whiteboard that doesn't match the first.
A Go map is that whiteboard:
- it lives in the memory of one running program, so a restart (a new version, a crash, a deploy) wipes it;
- two copies of the server each have their own map, so they disagree.
The fix is one register kept in the office safe that every counter asks: a database, a separate program (or file) that stores data safely on disk and answers questions about it. Most databases speak SQL (Structured Query Language), the language for creating tables and asking questions about their rows.
- PostgreSQL and MySQL are database servers: separate programs you connect to over the network.
- SQLite is a whole database in one file (or in memory), with no server. It's perfect for learning, tests and small apps, and the Go code is the same for all of them.
SQL basics primer
A table is like a sheet with fixed columns. Each row is one record (one order); each column is one field of every record (item, qty). The primary key is the column that uniquely identifies a row (id). NULL means "no value at all" (not an empty string, not 0). NOT NULL is a constraint: a rule the database itself enforces.
| SQL | What it does | Example |
|---|---|---|
CREATE TABLE |
makes a table with typed columns | CREATE TABLE orders (id INTEGER PRIMARY KEY, item TEXT NOT NULL, qty INTEGER NOT NULL) |
INSERT |
adds rows | INSERT INTO orders (item, qty) VALUES ('chai', 2) |
SELECT ... FROM |
reads columns of rows | SELECT id, item, qty FROM orders |
WHERE |
keeps only matching rows | SELECT id FROM orders WHERE item = 'chai' |
UPDATE ... SET |
changes rows | UPDATE orders SET qty = 5 WHERE id = 1 |
DELETE |
removes rows | DELETE FROM orders WHERE id = 1 |
SUM, COUNT |
aggregate functions over rows | SELECT SUM(qty) FROM orders |
PRIMARY KEY |
unique id of each row; SQLite fills INTEGER PRIMARY KEY for you |
id INTEGER PRIMARY KEY |
NOT NULL |
the column must have a value | item TEXT NOT NULL |
CHECK |
a custom rule | CHECK (qty BETWEEN 1 AND 20) |
Drivers and blank imports
Real-life example: a wall socket is a standard; every device plugs in the same way. But each phone brand needs its own adapter, bought separately, and the adapter must actually be plugged in.
database/sqlis the standard: the sameOpen,Exec,Query,QueryRow,Scanfor every database.- A driver is the package that knows how to talk to one particular database (
github.com/mattn/go-sqlite3for SQLite,github.com/lib/pqorgithub.com/jackc/pgxfor PostgreSQL). Go ships none. sql.Open(driverName, dataSourceName): the driver name says which registered driver to use ("sqlite3"); the data source name (DSN) says which database, in a driver-specific format (for SQLite, a file name such asshop.db).
The obvious attempt: forget the driver
Every program in these notes uses this go.mod:
module shop
go 1.24
require github.com/mattn/go-sqlite3 v1.14.32
main.go:
package main
import (
"database/sql"
"fmt"
)
func main() {
db, err := sql.Open("sqlite3", "shop.db") // no driver imported
fmt.Println("open:", db, err)
}
open: <nil> sql: unknown driver "sqlite3" (forgotten import?)
database/sql keeps a list of registered drivers, and nobody registered "sqlite3".
The fix: a blank import
import (
"database/sql"
_ "github.com/mattn/go-sqlite3" // blank import: registers the "sqlite3" driver
)
A blank import (_) imports a package only for its side effects: we never use the package by name, but importing it runs its init function, and the driver's init calls sql.Register("sqlite3", ...). (Blank imports and init were episode 27.)
sql.Open vs Ping
main.go:
package main
import (
"database/sql"
"fmt"
_ "github.com/mattn/go-sqlite3" // blank import: registers the "sqlite3" driver
)
func main() {
// a database file in a folder that doesn't exist
db, err := sql.Open("sqlite3", "file:/no/such/folder/shop.db")
fmt.Println("sql.Open error:", err) // nil?!
err = db.Ping() // now it really connects
fmt.Println("db.Ping error: ", err)
db, _ = sql.Open("sqlite3", "shop.db")
defer db.Close()
fmt.Println("db.Ping error: ", db.Ping())
res, err := db.Exec(`CREATE TABLE IF NOT EXISTS orders (id INTEGER PRIMARY KEY, item TEXT, qty INTEGER)`)
fmt.Println("create:", err)
res, _ = db.Exec("INSERT INTO orders (item, qty) VALUES (?, ?)", "chai", 2)
id, _ := res.LastInsertId()
n, _ := res.RowsAffected()
fmt.Println("inserted id", id, "rows affected", n)
}
sql.Open error: <nil>
db.Ping error: unable to open database file: no such file or directory
db.Ping error: <nil>
create: <nil>
inserted id 1 rows affected 1
sql.Openon a file inside a folder that doesn't exist returnednil: Open "may just validate its arguments without creating a connection".db.Ping()really connects, and returns the real error. At startup: Open, then Ping, and stop the program if Ping fails.db.Execruns SQL that returns no rows (CREATE TABLE,INSERT,UPDATE,DELETE). It returns asql.Result:LastInsertId(): the id the database gave the new row (1). Not every database supports it (PostgreSQL drivers useINSERT ... RETURNING idwithQueryRowinstead).RowsAffected(): how many rows the statement changed (1).- The
?marks are placeholders: the values travel separately from the SQL text (see below and episode 66).
Reading rows: Query, Next, Scan, Err, Close
Real-life example: a waiter reading orders off a long receipt roll, line by line. He moves to the next line (Next), copies the fields into his notepad (Scan), checks whether the roll ended or tore (Err), and hands the roll back (Close).
main.go:
package main
import (
"database/sql"
"fmt"
"log"
_ "github.com/mattn/go-sqlite3"
)
type Order struct {
ID int
Item string
Qty int
}
func main() {
db, err := sql.Open("sqlite3", "shop.db")
if err != nil {
log.Fatal(err)
}
defer db.Close()
db.Exec(`CREATE TABLE orders (id INTEGER PRIMARY KEY, item TEXT, qty INTEGER);
INSERT INTO orders (item, qty) VALUES ('chai', 2), ('samosa', 3), ('chai', 1), ('vada pav', 4);`)
rows, err := db.Query("SELECT id, item, qty FROM orders WHERE item = ?", "chai")
if err != nil {
log.Fatal(err)
}
defer rows.Close() // give the connection back to the pool
var chai []Order
for rows.Next() { // move to the next row; false at the end (or on an error)
var o Order
if err := rows.Scan(&o.ID, &o.Item, &o.Qty); err != nil { // columns -> variables, in order
log.Fatal(err)
}
chai = append(chai, o)
}
if err := rows.Err(); err != nil { // did the loop stop because of an error?
log.Fatal(err)
}
fmt.Printf("%+v\n", chai)
var total int
err = db.QueryRow("SELECT SUM(qty) FROM orders").Scan(&total) // exactly one row
fmt.Println("cups and plates today:", total, err)
}
[{ID:1 Item:chai Qty:2} {ID:3 Item:chai Qty:1}]
cups and plates today: 10 <nil>
db.Queryreturns*sql.Rows, a cursor: a moving position over the result rows, not all the rows at once.rows.Next()moves to the next row; it returnsfalseat the end or on an error.rows.Scan(&a, &b, ...)copies the current row's columns into your variables, in order, so you pass pointers. The number of targets must match the number of columns.rows.Err()after the loop tells you whether the loop stopped because of an error rather than the end.defer rows.Close()right after checkingQuery's error gives the connection back to the pool.db.QueryRowis for queries that return exactly one row (like aSUM). It returns a*sql.Row; any error is reported by itsScan, so it's one line.
Scan type conversion
Scan converts between the database's type and your variable's type when it can:
main.go:
package main
import (
"database/sql"
"fmt"
_ "github.com/mattn/go-sqlite3"
)
func main() {
db, _ := sql.Open("sqlite3", "shop.db")
defer db.Close()
db.Exec(`CREATE TABLE orders (id INTEGER PRIMARY KEY, item TEXT, qty INTEGER);
INSERT INTO orders (item, qty) VALUES ('chai', 2);`)
var qty int
err := db.QueryRow("SELECT item FROM orders WHERE id = 1").Scan(&qty) // TEXT "chai" into an int
fmt.Println("item into int:", qty, err)
var s string
err = db.QueryRow("SELECT qty FROM orders WHERE id = 1").Scan(&s) // INTEGER 2 into a string
fmt.Printf("qty into string: %q %v\n", s, err)
var a, b string
err = db.QueryRow("SELECT item FROM orders WHERE id = 1").Scan(&a, &b) // 1 column, 2 targets
fmt.Println("wrong count:", err)
}
item into int: 0 sql: Scan error on column index 0, name "item": converting driver.Value type string ("chai") to a int: invalid syntax
qty into string: "2" <nil>
wrong count: sql: expected 1 destination arguments in Scan, not 2
An integer fits in a string ("2"), but the text "chai" can't become an int, and the number of targets must match the columns.
Updates and constraints
main.go:
package main
import (
"database/sql"
"errors"
"fmt"
_ "github.com/mattn/go-sqlite3"
)
var ErrNotFound = errors.New("order not found")
// setQty changes one order's quantity. UPDATE never fails just because no row matched.
func setQty(db *sql.DB, id, qty int) error {
res, err := db.Exec("UPDATE orders SET qty = ? WHERE id = ?", qty, id)
if err != nil {
return err
}
n, err := res.RowsAffected()
if err != nil {
return err
}
if n == 0 {
return ErrNotFound // our own error -> 404 in the API
}
return nil
}
func main() {
db, _ := sql.Open("sqlite3", "shop.db")
defer db.Close()
db.Exec(`CREATE TABLE orders (id INTEGER PRIMARY KEY, item TEXT NOT NULL, qty INTEGER NOT NULL);
INSERT INTO orders (item, qty) VALUES ('chai', 2);`)
fmt.Println("update order 1:", setQty(db, 1, 5))
fmt.Println("update order 9:", setQty(db, 9, 5))
_, err := db.Exec("INSERT INTO orders (item, qty) VALUES (?, ?)", nil, 1) // item is NOT NULL
fmt.Println("insert without item:", err)
}
update order 1: <nil>
update order 9: order not found
insert without item: NOT NULL constraint failed: orders.item
- An
UPDATE(orDELETE) that matches no row is not an error: it simply changes nothing. CheckRowsAffected()and return your ownErrNotFound(an API turns it into 404). - The
NOT NULLconstraint stopped the bad insert in the database itself:NOT NULL constraint failed: orders.item. Constraints protect the data even when Go code forgets a check.
The connection pool
Real-life example: a taxi stand with 3 taxis. Starting a new taxi company for every trip would be slow, so a few taxis wait ready; each passenger takes one, rides, and brings it back. A passenger who never brings the taxi back empties the stand.
- A connection is an open line between your program and the database; opening one is slow.
sql.DBis not one connection: it's a connection pool. EveryQuery/Execborrows a connection and gives it back. An idle connection is an open one waiting in the pool.- So call
sql.Openonce, at startup, and share the*sql.DBeverywhere; it's safe for many goroutines at once.
The obvious mistake: forgetting rows.Close
main.go:
package main
import (
"database/sql"
"fmt"
"time"
_ "github.com/mattn/go-sqlite3"
)
// firstChai returns the first chai order's id... and forgets rows.Close.
func firstChai(db *sql.DB) (int, error) {
rows, err := db.Query("SELECT id FROM orders WHERE item = 'chai'")
if err != nil {
return 0, err
}
// defer rows.Close()
var id int
if rows.Next() {
rows.Scan(&id) // we only wanted the first row, so we stop reading here
}
return id, nil
}
func main() {
db, _ := sql.Open("sqlite3", "shop.db")
defer db.Close()
db.SetMaxOpenConns(3) // the pool: at most 3 connections
db.Exec(`CREATE TABLE orders (id INTEGER PRIMARY KEY, item TEXT);
INSERT INTO orders (item) VALUES ('chai'), ('chai'), ('samosa');`)
for i := 1; i <= 4; i++ {
done := make(chan error, 1)
go func() { _, err := firstChai(db); done <- err }()
select {
case err := <-done:
fmt.Printf("request %d: err=%v | connections in use: %d of 3\n", i, err, db.Stats().InUse)
case <-time.After(time.Second):
fmt.Printf("request %d: still waiting after 1s... (waits so far: %d)\n", i, db.Stats().WaitCount)
}
}
}
request 1: err=<nil> | connections in use: 1 of 3
request 2: err=<nil> | connections in use: 2 of 3
request 3: err=<nil> | connections in use: 3 of 3
request 4: still waiting after 1s... (waits so far: 1)
Each Rows we stopped reading early kept its connection: a connection leak. With SetMaxOpenConns(3), request 4 waits forever. In a real server, every request freezes.
The fix: one line
leak2.go is the same program with defer rows.Close() // give the connection back instead of the comment:
request 1: err=<nil> | connections in use: 0 of 3
request 2: err=<nil> | connections in use: 0 of 3
request 3: err=<nil> | connections in use: 0 of 3
request 4: err=<nil> | connections in use: 0 of 3
Rows close themselves only when Next has returned false. Whenever you might stop early (and you always might: an early return, an error, "only the first row"), defer rows.Close().
QueryRow and sql.ErrNoRows
main.go:
package main
import (
"database/sql"
"errors"
"fmt"
_ "github.com/mattn/go-sqlite3"
)
func main() {
db, _ := sql.Open("sqlite3", "shop.db")
defer db.Close()
db.Exec(`CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT NOT NULL, phone TEXT);
INSERT INTO customers (name, phone) VALUES ('asha', '98100 11111'), ('ravi', NULL);`)
for _, id := range []int{1, 99, 2} {
var name, phone string
err := db.QueryRow("SELECT name, phone FROM customers WHERE id = ?", id).Scan(&name, &phone)
switch {
case errors.Is(err, sql.ErrNoRows): // QueryRow found nothing
fmt.Printf("id %d: not found -> 404\n", id)
case err != nil:
fmt.Printf("id %d: %v\n", id, err)
default:
fmt.Printf("id %d: %s %s\n", id, name, phone)
}
}
}
id 1: asha 98100 11111
id 99: not found -> 404
id 2: sql: Scan error on column index 1, name "phone": converting NULL to string is unsupported
- No row for id 99:
Scanreturnedsql.ErrNoRows. Check it witherrors.Is(err, sql.ErrNoRows)(episode 25) and turn it into a 404. Not finding a row is a normal answer, not a server error. - Ravi's phone is NULL, and a Go
stringcan't be NULL (its zero value is"").
NULL values
Real-life example: a form with a phone box. "No phone given" (box left empty, NULL) is different from "phone given as blank" (""). The form needs a tick box "phone given? yes/no".
main.go:
package main
import (
"database/sql"
"fmt"
_ "github.com/mattn/go-sqlite3"
)
func main() {
db, _ := sql.Open("sqlite3", "shop.db")
defer db.Close()
db.Exec(`CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT NOT NULL, phone TEXT);
INSERT INTO customers (name, phone) VALUES ('asha', '98100 11111'), ('ravi', NULL);`)
for _, id := range []int{1, 2} {
var name string
var phone sql.NullString // a string that may be NULL
var phone2 sql.Null[string] // the generic form (Go 1.22)
var phone3 *string // or a pointer: nil means NULL
q := "SELECT name, phone, phone, phone FROM customers WHERE id = ?"
if err := db.QueryRow(q, id).Scan(&name, &phone, &phone2, &phone3); err != nil {
fmt.Println(err)
continue
}
fmt.Printf("%s: NullString{%q, Valid:%v} Null[string]{%q, %v} *string=%v\n",
name, phone.String, phone.Valid, phone2.V, phone2.Valid, phone3 != nil)
}
}
asha: NullString{"98100 11111", Valid:true} Null[string]{"98100 11111", true} *string=true
ravi: NullString{"", Valid:false} Null[string]{"", false} *string=false
Three ways to scan a value that may be NULL:
| Type | Fields | Notes |
|---|---|---|
sql.NullString (also NullInt64, NullBool, NullTime, ...) |
String, Valid |
one type per Go type |
sql.Null[T] (Go 1.22+) |
V, Valid |
generic: works for any type |
*string (a pointer) |
nil means NULL |
simple; one allocation per value |
Valid is the tick box. Better still, avoid NULL where you can with NOT NULL columns (like name here).
A common surprise: aggregates over no rows return NULL.
main.go:
package main
import (
"database/sql"
"fmt"
_ "github.com/mattn/go-sqlite3"
)
func main() {
db, _ := sql.Open("sqlite3", "shop.db")
defer db.Close()
db.Exec(`CREATE TABLE orders (id INTEGER PRIMARY KEY, item TEXT NOT NULL, qty INTEGER NOT NULL);
INSERT INTO orders (item, qty) VALUES ('chai', 2), ('chai', 5);`)
for _, item := range []string{"chai", "lassi"} {
var total int
err := db.QueryRow("SELECT SUM(qty) FROM orders WHERE item = ?", item).Scan(&total)
fmt.Printf("SUM for %-6s %d %v\n", item, total, err)
var safe sql.Null[int64]
db.QueryRow("SELECT SUM(qty) FROM orders WHERE item = ?", item).Scan(&safe)
db.QueryRow("SELECT COALESCE(SUM(qty), 0) FROM orders WHERE item = ?", item).Scan(&total)
fmt.Printf(" Null[int64]=%+v COALESCE=%d\n", safe, total)
}
}
SUM for chai 7 <nil>
Null[int64]={V:7 Valid:true} COALESCE=7
SUM for lassi 0 sql: Scan error on column index 0, name "SUM(qty)": converting NULL to int is unsupported
Null[int64]={V:0 Valid:false} COALESCE=0
SUM of zero rows is NULL. Scan into sql.Null[int64], or let SQL replace NULL with COALESCE(SUM(qty), 0).
Placeholders
A placeholder marks where a value goes in the SQL; the value is passed separately, so it is always data, never SQL. That's what stops SQL injection (episode 66). The symbol depends on the database/driver:
- SQLite and MySQL drivers:
? - PostgreSQL drivers:
$1,$2, ... - SQLite also accepts
?1,$1and named:name(withsql.Named). Real run:
main.go:
package main
import (
"database/sql"
"fmt"
_ "github.com/mattn/go-sqlite3"
)
func main() {
db, _ := sql.Open("sqlite3", "shop.db")
defer db.Close()
db.Exec(`CREATE TABLE orders (id INTEGER PRIMARY KEY, item TEXT NOT NULL, qty INTEGER NOT NULL);
INSERT INTO orders (item, qty) VALUES ('chai', 2), ('chai', 5), ('samosa', 3);`)
try := func(label, q string, args ...any) {
var n int
err := db.QueryRow(q, args...).Scan(&n)
fmt.Printf("%-12s count=%d err=%v\n", label, n, err)
}
try("?", "SELECT COUNT(*) FROM orders WHERE item = ? AND qty > ?", "chai", 1)
try("?1 ?2", "SELECT COUNT(*) FROM orders WHERE item = ?1 AND qty > ?2", "chai", 1)
try("$1 $2", "SELECT COUNT(*) FROM orders WHERE item = $1 AND qty > $2", "chai", 1)
try(":name", "SELECT COUNT(*) FROM orders WHERE item = :item AND qty > :min",
sql.Named("item", "chai"), sql.Named("min", 1))
try("too few", "SELECT COUNT(*) FROM orders WHERE item = ? AND qty > ?", "chai")
try("table name", "SELECT COUNT(*) FROM ?", "orders")
}
? count=2 err=<nil>
?1 ?2 count=2 err=<nil>
$1 $2 count=2 err=<nil>
:name count=2 err=<nil>
too few count=0 err=not enough args to execute query: want 2 got 1
table name count=0 err=near "?": syntax error
Placeholders work for values only: a table or column name can't be a placeholder (near "?": syntax error). If a name comes from a user, check it against a fixed list of allowed names. Never fmt.Sprintf values into SQL.
Pool settings and db.Stats
main.go:
package main
import (
"context"
"database/sql"
"fmt"
"sync"
"time"
_ "github.com/mattn/go-sqlite3"
)
func main() {
db, _ := sql.Open("sqlite3", "shop.db")
defer db.Close()
db.SetMaxOpenConns(3) // at most 3 connections at once
db.SetMaxIdleConns(2) // keep at most 2 idle ones ready
db.SetConnMaxLifetime(30 * time.Minute) // replace connections after 30 min
db.SetConnMaxIdleTime(5 * time.Minute) // close ones idle for 5 min
start := time.Now()
var wg sync.WaitGroup
for i := 0; i < 10; i++ { // 10 requests arrive together
wg.Add(1)
go func() {
defer wg.Done()
conn, _ := db.Conn(context.Background()) // borrow a connection
time.Sleep(100 * time.Millisecond) // a 100 ms query
conn.Close() // give it back
}()
}
wg.Wait()
s := db.Stats()
fmt.Println("10 requests took:", time.Since(start).Round(10*time.Millisecond))
fmt.Println("open:", s.OpenConnections, "idle:", s.Idle, "in use:", s.InUse)
fmt.Println("had to wait:", s.WaitCount, "times, for", s.WaitDuration.Round(10*time.Millisecond), "in total")
fmt.Println("closed because of MaxIdleConns:", s.MaxIdleClosed)
}
10 requests took: 400ms
open: 2 idle: 2 in use: 0
had to wait: 7 times, for 1.22s in total
closed because of MaxIdleConns: 1
(The total wait was 1.2s in the video's run and 1.22s in this one: timings vary.) 10 requests, 3 at a time, 100 ms each: 4 rounds, about 400 ms, and 7 requests had to wait.
| Setting | What it does | Default |
|---|---|---|
SetMaxOpenConns(n) |
at most n connections at once (in use + idle); more requests wait | 0 = unlimited |
SetMaxIdleConns(n) |
at most n idle connections kept ready | 2 |
SetConnMaxLifetime(d) |
a connection is closed (and replaced) after d since it was opened | 0 = forever |
SetConnMaxIdleTime(d) |
a connection idle for d is closed | 0 = forever |
db.Stats() field |
Meaning |
|---|---|
OpenConnections |
connections open now (in use + idle) |
InUse |
connections borrowed right now |
Idle |
connections waiting in the pool |
WaitCount |
how many times a request had to wait for a connection |
WaitDuration |
total time spent waiting |
MaxIdleClosed |
connections closed because of SetMaxIdleConns |
MaxOpenConnections |
the SetMaxOpenConns value |
db.Conn(ctx) borrows one dedicated connection until you call conn.Close() (it's returned to the pool, not closed).
How to choose:
MaxOpenConnsprotects the database: it can only handle so many connections, shared by all your servers (10 servers × 20 = 200).MaxIdleConnsclose to your usual concurrent load, so most requests find a ready connection.ConnMaxLifetimebelow any limit your network or database sets on connection age.- Watch
db.Stats(): a growingWaitCountmeans the pool is too small or rows are leaking.
PostgreSQL drivers
The code above is the same for PostgreSQL; only the driver, the DSN and the placeholders ($1) change. Two drivers, from their READMEs: lib/pq ("a Go PostgreSQL driver for database/sql", driver name postgres) and pgx ("a pure Go driver and toolkit for PostgreSQL" with its own interface and "an adapter for the standard database/sql interface"). Episode 68 uses PostgreSQL in practice.
Mini project: Raju's orders in SQLite
Build an orders table (id, item NOT NULL, qty NOT NULL with a 1-20 check, a nullable note), and a Store type with Create, Get, ListByItem and Delete. Get maps sql.ErrNoRows, and Delete maps RowsAffected() == 0, to your own ErrNotFound. Use a temporary database file and print db.Stats() at the end.
Full solution
main.go:
package main
import (
"database/sql"
"errors"
"fmt"
"log"
"os"
"path/filepath"
_ "github.com/mattn/go-sqlite3"
)
var ErrNotFound = errors.New("order not found")
type Order struct {
ID int64
Item string
Qty int
Note sql.Null[string] // may be NULL
}
type Store struct{ db *sql.DB }
func NewStore(db *sql.DB) (*Store, error) {
_, err := db.Exec(`CREATE TABLE IF NOT EXISTS orders (
id INTEGER PRIMARY KEY,
item TEXT NOT NULL,
qty INTEGER NOT NULL CHECK (qty BETWEEN 1 AND 20),
note TEXT)`)
return &Store{db: db}, err
}
func (s *Store) Create(o Order) (int64, error) {
res, err := s.db.Exec("INSERT INTO orders (item, qty, note) VALUES (?, ?, ?)", o.Item, o.Qty, o.Note)
if err != nil {
return 0, fmt.Errorf("create order: %w", err)
}
return res.LastInsertId()
}
func (s *Store) Get(id int64) (Order, error) {
var o Order
err := s.db.QueryRow("SELECT id, item, qty, note FROM orders WHERE id = ?", id).
Scan(&o.ID, &o.Item, &o.Qty, &o.Note)
if errors.Is(err, sql.ErrNoRows) {
return Order{}, ErrNotFound
}
return o, err
}
func (s *Store) ListByItem(item string) ([]Order, error) {
rows, err := s.db.Query("SELECT id, item, qty, note FROM orders WHERE item = ? ORDER BY id", item)
if err != nil {
return nil, err
}
defer rows.Close()
var out []Order
for rows.Next() {
var o Order
if err := rows.Scan(&o.ID, &o.Item, &o.Qty, &o.Note); err != nil {
return nil, err
}
out = append(out, o)
}
return out, rows.Err()
}
func (s *Store) Delete(id int64) error {
res, err := s.db.Exec("DELETE FROM orders WHERE id = ?", id)
if err != nil {
return err
}
n, err := res.RowsAffected()
if err != nil {
return err
}
if n == 0 {
return ErrNotFound
}
return nil
}
func main() {
dir, err := os.MkdirTemp("", "raju")
if err != nil {
log.Fatal(err)
}
defer os.RemoveAll(dir)
db, err := sql.Open("sqlite3", filepath.Join(dir, "shop.db"))
if err != nil {
log.Fatal(err)
}
defer db.Close()
if err := db.Ping(); err != nil {
log.Fatal(err)
}
db.SetMaxOpenConns(5)
store, err := NewStore(db)
if err != nil {
log.Fatal(err)
}
id1, err := store.Create(Order{Item: "chai", Qty: 2})
fmt.Println("create chai:", id1, err)
id2, err := store.Create(Order{Item: "chai", Qty: 1, Note: sql.Null[string]{V: "less sugar", Valid: true}})
fmt.Println("create chai with note:", id2, err)
_, err = store.Create(Order{Item: "samosa", Qty: 50})
fmt.Println("create 50 samosas:", err)
o, err := store.Get(id2)
fmt.Printf("get %d: %s x%d note=%q valid=%v err=%v\n", id2, o.Item, o.Qty, o.Note.V, o.Note.Valid, err)
_, err = store.Get(99)
fmt.Println("get 99:", err, "| is ErrNotFound:", errors.Is(err, ErrNotFound))
list, err := store.ListByItem("chai")
fmt.Println("chai orders:", len(list), err)
for _, o := range list {
fmt.Printf(" #%d %s x%d note valid=%v\n", o.ID, o.Item, o.Qty, o.Note.Valid)
}
fmt.Println("delete 1:", store.Delete(id1))
fmt.Println("delete 1 again:", store.Delete(id1))
s := db.Stats()
fmt.Printf("stats: open=%d in use=%d idle=%d waits=%d\n", s.OpenConnections, s.InUse, s.Idle, s.WaitCount)
}
go.mod:
module shop
go 1.24
require github.com/mattn/go-sqlite3 v1.14.32
create chai: 1 <nil>
create chai with note: 2 <nil>
create 50 samosas: create order: CHECK constraint failed: qty BETWEEN 1 AND 20
get 2: chai x1 note="less sugar" valid=true err=<nil>
get 99: order not found | is ErrNotFound: true
chai orders: 2 <nil>
#1 chai x2 note valid=false
#2 chai x1 note valid=true
delete 1: <nil>
delete 1 again: order not found
stats: open=1 in use=0 idle=1 waits=0
sql.Null[string] works in both directions: as an argument it inserts NULL when Valid is false, and as a Scan target it reads NULL. os.MkdirTemp gives each run a fresh database file, which is how tests should work too. To plug it into episode 65's REST handlers, errors.Is(err, ErrNotFound) becomes a 404 problem response.
Common mistakes (and the real messages)
| Mistake | What you see | Why | Fix |
|---|---|---|---|
| no driver import | sql: unknown driver "sqlite3" (forgotten import?) |
nothing registered the driver | import _ "github.com/mattn/go-sqlite3" |
trusting sql.Open's error |
nil, then unable to open database file: no such file or directory later |
Open may not connect | db.Ping() at startup |
forgetting rows.Close() |
request 4: still waiting after 1s... |
each open Rows holds a connection |
defer rows.Close() |
skipping rows.Err() |
fewer rows, no error | Next returns false on errors too |
check rows.Err() after the loop |
NULL into a string |
converting NULL to string is unsupported |
a Go string can't be NULL | sql.Null[string], sql.NullString or *string |
SUM over no rows into int |
converting NULL to int is unsupported |
SUM of nothing is NULL |
COALESCE(SUM(x), 0) or sql.Null[int64] |
text into an int |
converting driver.Value type string ("chai") to a int: invalid syntax |
wrong target type | match the column type |
| wrong number of targets | sql: expected 1 destination arguments in Scan, not 2 |
targets ≠ columns | one target per column |
UPDATE of a missing id treated as success |
<nil> |
0 rows matched is not an error | check RowsAffected() |
| missing argument | not enough args to execute query: want 2 got 1 |
one value per placeholder | pass every value |
| placeholder for a table name | near "?": syntax error |
placeholders are for values | a fixed list of names |
sql.Open per request |
slow requests, too many connections | a new pool each time | open once, share *sql.DB |
Practice
firstChaireads only the first row. Which two lines would make it leak-free, and where do they go?- Write
totalFor(item string) (int, error)that returns 0 (not an error) when there are no orders for the item. - Your service has 8 instances, and the database allows 100 connections, keeping 20 for admin tools. What
SetMaxOpenConnsshould each instance use? db.Stats().WaitCountkeeps growing while traffic is flat. Name two causes.- Why is
errors.Is(err, sql.ErrNoRows)better thanerr == sql.ErrNoRowsin a store that wraps errors?
Solutions
1. After rows, err := db.Query(...) and its if err != nil { return ... }, add defer rows.Close(). That's one line; the second is checking rows.Err() if you loop. (Run above: leak2.go shows 0 in use.)
2. Use COALESCE, as in the real run above:
func totalFor(db *sql.DB, item string) (int, error) {
var total int
err := db.QueryRow("SELECT COALESCE(SUM(qty), 0) FROM orders WHERE item = ?", item).Scan(&total)
return total, err
}
The run above printed COALESCE=0 for lassi and COALESCE=7 for chai.
3. (100 − 20) / 8 = 10 per instance, so all 8 together can never exceed 80.
4. The pool is too small for the real concurrency (raise MaxOpenConns within the database's limit), or connections are leaking (unclosed Rows, a db.Conn or transaction never closed), so fewer are free than you think: the leak demo shows in use rising with no traffic increase.
5. If a store returns fmt.Errorf("get order %d: %w", id, err), the error is no longer equal to sql.ErrNoRows, but errors.Is follows the wrap chain (episode 25) and still finds it.
Quick quiz
1. err := db.QueryRow("SELECT name FROM customers WHERE id = ?", 99).Scan(&name) and there's no customer 99. What does err hold? (From the video.)
A. nil, and name is "" · B. sql.ErrNoRows · C. a NULL error · D. it panics
Answer
B. QueryRow saves any error for Scan, and no row means sql.ErrNoRows. Check it with errors.Is.
2. sql.Open("sqlite3", "file:/no/such/folder/shop.db") returns what error?
Answer
nil. The real error, unable to open database file: no such file or directory, comes from db.Ping().
3. An UPDATE ... WHERE id = 9 matches no row. What does Exec return?
Answer
A Result and a nil error; RowsAffected() is 0. You must check it yourself.
Interview questions
- What is
database/sql, and what is a driver? The standard, database-independent API (Open,Exec,Query,QueryRow,Scan, transactions); a driver is a third-party package implementing it for one database, registered with a blank import. - Does
sql.Openconnect to the database? Not necessarily; it may only validate arguments. CallPing(orPingContext) to verify. - What is
*sql.DB? A pool of connections, safe for concurrent use; open it once and share it. - Why must you close
Rows? An openRowsholds its connection; unclosed rows leak connections until the pool runs dry and requests block. - When are
Rowsclosed automatically? WhenNextreturns false at the end; you stilldefer rows.Close()for early exits. - How do you detect "not found" with
QueryRow, and withUPDATE/DELETE?errors.Is(err, sql.ErrNoRows);RowsAffected() == 0. - How do you handle NULL columns?
sql.NullStringand friends,sql.Null[T](Go 1.22+) or pointers; better,NOT NULLcolumns;COALESCEfor aggregates. - Why use placeholders, and what can't they do? They send values separately from SQL, preventing injection; they can't replace identifiers like table or column names.
- Which pool settings would you set in production?
SetMaxOpenConns(database limit / instances),SetMaxIdleConns(usual load),SetConnMaxLifetime(below network/database limits), maybeSetConnMaxIdleTime; monitordb.Stats(). ExecvsQueryvsQueryRow? No rows returned / many rows / exactly one row.
Glossary
| Term | Meaning | Example |
|---|---|---|
| Database | a separate program or file that stores data safely and answers questions about it | SQLite file shop.db |
| SQL | the language for creating tables and querying rows | SELECT id FROM orders |
| Table | a sheet with fixed, typed columns | orders |
| Row | one record in a table | order 1: chai, 2 |
| Column | one field of every row | qty |
| Primary key | the column that uniquely identifies a row | id INTEGER PRIMARY KEY |
| NULL | "no value at all", not "" or 0 |
Ravi's phone |
| NOT NULL | constraint: the column must have a value | item TEXT NOT NULL |
| Constraint | a rule the database enforces | NOT NULL constraint failed: orders.item |
| CHECK | a custom constraint | CHECK (qty BETWEEN 1 AND 20) |
| PostgreSQL | a database server | driver pgx or lib/pq |
| MySQL | a database server | uses ? placeholders |
| SQLite | a whole database in one file, no server | sql.Open("sqlite3", "shop.db") |
database/sql |
Go's standard database API | db.Query(...) |
| Driver | package that talks to one kind of database | github.com/mattn/go-sqlite3 |
| cgo | Go calling C code; needs CGO_ENABLED=1 and gcc |
go-sqlite3 |
| Blank import | import only for side effects | import _ "github.com/mattn/go-sqlite3" |
init function |
runs automatically when a package is loaded | the driver registers itself |
| Driver name | the registered name passed to sql.Open |
"sqlite3", "postgres" |
| Data source name (DSN) | driver-specific "which database" string | shop.db |
sql.Open |
creates a *sql.DB; may not connect |
sql.Open("sqlite3", "shop.db") |
Ping |
really connects and checks | db.Ping() |
| Connection | an open line to the database | borrowed per query |
| Connection pool | a set of ready connections, reused | *sql.DB |
| Idle connection | open but not in use, waiting in the pool | Idle: 2 |
| Connection leak | a connection never given back | an unclosed Rows |
Exec |
runs SQL that returns no rows | INSERT, UPDATE, DELETE |
sql.Result |
what Exec returns |
res.RowsAffected() |
LastInsertId |
id of the row just inserted | 1 |
RowsAffected |
number of rows changed | 0 means none matched |
Query |
runs SQL returning many rows | db.Query("SELECT ...") |
*sql.Rows |
a cursor over results | for rows.Next() |
| Cursor | a moving position over result rows | Rows |
rows.Next |
move to the next row; false at the end or on error | for rows.Next() { |
Scan |
copy columns into variables, in order | rows.Scan(&o.ID, &o.Item) |
rows.Err |
the error that stopped the loop, if any | if err := rows.Err(); err != nil |
rows.Close |
release the connection | defer rows.Close() |
QueryRow |
query for exactly one row | db.QueryRow(q, id).Scan(&name) |
*sql.Row |
one-row result; Scan reports errors |
.Scan(&total) |
sql.ErrNoRows |
QueryRow found no row |
→ 404 |
errors.Is |
matches an error through wrapping | errors.Is(err, sql.ErrNoRows) |
ErrNotFound |
your own sentinel error | var ErrNotFound = errors.New("order not found") |
sql.NullString |
string that may be NULL | {String, Valid} |
sql.Null[T] |
generic nullable value (Go 1.22) | sql.Null[string]{V, Valid} |
Valid |
whether a Null value is present | false for NULL |
| Pointer for NULL | nil means NULL |
var phone *string |
COALESCE |
SQL: first non-NULL value | COALESCE(SUM(qty), 0) |
| Aggregate function | computes one value from many rows | SUM, COUNT |
| Placeholder | marks a value position; value sent separately | ?, $1, :name |
sql.Named |
a named argument | sql.Named("item", "chai") |
| SQL injection | user input executed as SQL | x' OR '1'='1 (episode 66) |
SetMaxOpenConns |
cap on open connections | db.SetMaxOpenConns(3) |
SetMaxIdleConns |
cap on idle connections (default 2) | db.SetMaxIdleConns(2) |
SetConnMaxLifetime |
replace connections after this age | 30 * time.Minute |
SetConnMaxIdleTime |
close connections idle this long | 5 * time.Minute |
db.Conn |
borrow one dedicated connection | conn, _ := db.Conn(ctx) |
db.Stats |
pool statistics | db.Stats().WaitCount |
WaitCount |
times a request waited for a connection | 7 |
WaitDuration |
total waiting time | 1.22s |
MaxIdleClosed |
connections closed by SetMaxIdleConns |
1 |
InUse / Idle / OpenConnections |
borrowed / ready / both | 0 / 2 / 2 |
| Store (repository) | a type that hides the SQL behind methods | store.Get(id) |
os.MkdirTemp |
makes a fresh temporary folder | test databases |
Summary
- Data that must survive restarts and be shared between servers belongs in a database;
database/sqlis Go's standard API and a driver plugs in with a blank import. sql.Openmay not connect: Ping at startup.*sql.DBis a connection pool: open it once, share it, and alwaysdefer rows.Close(), or the pool runs dry.Execfor writes (checkRowsAffected),Querywith Next, Scan, Err, Close for many rows,QueryRowfor one;sql.ErrNoRowsis a normal "not found".- NULL needs
sql.NullString,sql.Null[T]or pointers;NOT NULLconstraints andCOALESCEhelp. - Placeholders carry every value (
?or$1), neverSprintf; tune the pool withSetMaxOpenConns,SetMaxIdleConns,SetConnMaxLifetimeand watchdb.Stats().
Next episode: Databases in practice: transactions with BeginTx, Commit and Rollback, isolation levels, prepared statements, QueryContext timeouts, pgx, sqlc, ORMs, migrations, the repository pattern and N+1 queries.