SyntaxPilot
Courses/Go Programming/Lesson 67
Video coming soonThis lesson’s video is being made

Start with the notes below; the video appears here as soon as it is on YouTube.

Go Programming · Lesson 67 of 89

Databases with database/sql

database/sql packagedrivers and blank importssql.Open vs Pingsql.DB as a connection poolQueryQueryRowExecScanrows.Nextrows.Closerows.Errplaceholders ($1 vs ?)sql.ErrNoRowssql.NullString and sql.Null[T]

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/sql source (sql.go): const defaultMaxIdleConns = 2, the default for SetMaxIdleConns.
  • 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=1 environment variable ... and the presence of the gcc compiler".
  • lib/pq README: "pq is a Go PostgreSQL driver for database/sql."; "Use the postgres driver name in the sql.Open() call".
  • jackc/pgx README: "pgx is a pure Go driver and toolkit for PostgreSQL."; it "exposes PostgreSQL-specific features such as LISTEN / NOTIFY and COPY. It also includes an adapter for the standard database/sql interface."
  • 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/sql is the standard: the same Open, Exec, Query, QueryRow, Scan for every database.
  • A driver is the package that knows how to talk to one particular database (github.com/mattn/go-sqlite3 for SQLite, github.com/lib/pq or github.com/jackc/pgx for 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 as shop.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.Open on a file inside a folder that doesn't exist returned nil: 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.Exec runs SQL that returns no rows (CREATE TABLE, INSERT, UPDATE, DELETE). It returns a sql.Result:
  • LastInsertId(): the id the database gave the new row (1). Not every database supports it (PostgreSQL drivers use INSERT ... RETURNING id with QueryRow instead).
  • 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.Query returns *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 returns false at 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 checking Query's error gives the connection back to the pool.
  • db.QueryRow is for queries that return exactly one row (like a SUM). It returns a *sql.Row; any error is reported by its Scan, 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 (or DELETE) that matches no row is not an error: it simply changes nothing. Check RowsAffected() and return your own ErrNotFound (an API turns it into 404).
  • The NOT NULL constraint 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.DB is not one connection: it's a connection pool. Every Query/Exec borrows a connection and gives it back. An idle connection is an open one waiting in the pool.
  • So call sql.Open once, at startup, and share the *sql.DB everywhere; 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: Scan returned sql.ErrNoRows. Check it with errors.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 string can'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, $1 and named :name (with sql.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:

  • MaxOpenConns protects the database: it can only handle so many connections, shared by all your servers (10 servers × 20 = 200).
  • MaxIdleConns close to your usual concurrent load, so most requests find a ready connection.
  • ConnMaxLifetime below any limit your network or database sets on connection age.
  • Watch db.Stats(): a growing WaitCount means 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

  1. firstChai reads only the first row. Which two lines would make it leak-free, and where do they go?
  2. Write totalFor(item string) (int, error) that returns 0 (not an error) when there are no orders for the item.
  3. Your service has 8 instances, and the database allows 100 connections, keeping 20 for admin tools. What SetMaxOpenConns should each instance use?
  4. db.Stats().WaitCount keeps growing while traffic is flat. Name two causes.
  5. Why is errors.Is(err, sql.ErrNoRows) better than err == sql.ErrNoRows in 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

  1. 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.
  2. Does sql.Open connect to the database? Not necessarily; it may only validate arguments. Call Ping (or PingContext) to verify.
  3. What is *sql.DB? A pool of connections, safe for concurrent use; open it once and share it.
  4. Why must you close Rows? An open Rows holds its connection; unclosed rows leak connections until the pool runs dry and requests block.
  5. When are Rows closed automatically? When Next returns false at the end; you still defer rows.Close() for early exits.
  6. How do you detect "not found" with QueryRow, and with UPDATE/DELETE? errors.Is(err, sql.ErrNoRows); RowsAffected() == 0.
  7. How do you handle NULL columns? sql.NullString and friends, sql.Null[T] (Go 1.22+) or pointers; better, NOT NULL columns; COALESCE for aggregates.
  8. 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.
  9. Which pool settings would you set in production? SetMaxOpenConns (database limit / instances), SetMaxIdleConns (usual load), SetConnMaxLifetime (below network/database limits), maybe SetConnMaxIdleTime; monitor db.Stats().
  10. Exec vs Query vs QueryRow? 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/sql is Go's standard API and a driver plugs in with a blank import.
  • sql.Open may not connect: Ping at startup.
  • *sql.DB is a connection pool: open it once, share it, and always defer rows.Close(), or the pool runs dry.
  • Exec for writes (check RowsAffected), Query with Next, Scan, Err, Close for many rows, QueryRow for one; sql.ErrNoRows is a normal "not found".
  • NULL needs sql.NullString, sql.Null[T] or pointers; NOT NULL constraints and COALESCE help.
  • Placeholders carry every value (? or $1), never Sprintf; tune the pool with SetMaxOpenConns, SetMaxIdleConns, SetConnMaxLifetime and watch db.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.