database/sql

module example.com/shelf

go 1.27

package main

import (
	"context"
	"database/sql"
	"database/sql/driver"
	"errors"
	"fmt"
	"log"

	"example.com/shelf/internal/fakedb"
)

// ISBN stores itself as a plain string column. In the shelf module these two
// methods would live next to the type in package catalog.
type ISBN string

// Value implements driver.Valuer: what the driver receives for this type.
func (i ISBN) Value() (driver.Value, error) {
	if len(i) != 13 {
		return nil, fmt.Errorf("isbn %q: want 13 digits", string(i))
	}
	return string(i), nil
}

// Scan implements sql.Scanner: how a column value becomes an ISBN.
func (i *ISBN) Scan(src any) error {
	s, ok := src.(string)
	if !ok {
		return fmt.Errorf("isbn: cannot scan %T", src)
	}
	*i = ISBN(s)
	return nil
}

func main() {
	ctx := context.Background()
	db := sql.OpenDB(fakedb.New())
	defer db.Close()

	const insert = "INSERT INTO books (isbn, title, year) VALUES (?, ?, ?)"
	res, err := db.ExecContext(ctx, insert, ISBN("9780596517748"), "JavaScript: The Good Parts", 2008)
	if err != nil {
		log.Fatal(err)
	}
	id, _ := res.LastInsertId()
	n, _ := res.RowsAffected()
	fmt.Println("inserted id", id, "rows affected", n)

	// A NULL year: pass nil, or a sql.Null[int64] whose Valid is false.
	_, err = db.ExecContext(ctx, insert, ISBN("9781098122980"), "Concurrency in Go", sql.Null[int64]{})
	fmt.Println("insert with NULL year:", err)

	// A duplicate key comes back as the driver's own error type.
	_, err = db.ExecContext(ctx, insert, ISBN("9780596517748"), "Duplicate", 2008)
	var dbErr *fakedb.Error
	if errors.As(err, &dbErr) {
		fmt.Println("driver error code:", dbErr.Code)
	}

	// Valuer errors surface before the statement is sent.
	_, err = db.ExecContext(ctx, insert, ISBN("123"), "Bad", 2000)
	fmt.Println(err)

	res, err = db.ExecContext(ctx, "UPDATE books SET copies = copies + ? WHERE isbn = ?", 2, ISBN("9780134190440"))
	if err != nil {
		log.Fatal(err)
	}
	n, _ = res.RowsAffected()
	fmt.Println("update affected", n)

	var copies int
	if err := db.QueryRowContext(ctx, "SELECT copies FROM books WHERE isbn = ?", ISBN("9780134190440")).Scan(&copies); err != nil {
		log.Fatal(err)
	}
	fmt.Println("copies now", copies)

	// Scanner: the isbn column is scanned straight into an ISBN.
	var (
		id2   int64
		isbn  ISBN
		title string
		year  sql.Null[int64]
	)
	err = db.QueryRowContext(ctx, "SELECT id, isbn, title, year FROM books WHERE isbn = ?", "9781617291784").
		Scan(&id2, &isbn, &title, &year)
	fmt.Printf("scanned %T %q %v\n", isbn, isbn, err)

	// This driver counts placeholders itself; see the prepared statement
	// example for the check database/sql performs.
	_, err = db.ExecContext(ctx, "DELETE FROM books WHERE isbn = ?")
	fmt.Println(err)
}

inserted id 4 rows affected 1
insert with NULL year: <nil>
driver error code: 23505
sql: converting argument $1 type: isbn "123": want 13 digits
update affected 1
copies now 5
scanned main.ISBN "9781617291784" <nil>
fakedb: query has 1 placeholders but got 0 arguments

package main

import (
	"database/sql"
	"database/sql/driver"
	"fmt"
	"uuid"
)

func main() {
	// ConvertAssign applies the same conversions Rows.Scan uses.
	var id uuid.UUID
	err := sql.ConvertAssign(driver.ScanContext{}, &id, "0190a0a0-7000-7000-8000-000000000001")
	fmt.Println(id, err)

	var s string
	err = sql.ConvertAssign(driver.ScanContext{}, &s, int64(2021))
	fmt.Printf("%q %v\n", s, err)

	var small uint8
	err = sql.ConvertAssign(driver.ScanContext{}, &small, "300")
	fmt.Println(small, err)
}

0190a0a0-7000-7000-8000-000000000001 <nil>
"2021" <nil>
0 converting driver.Value type string ("300") to a uint8: value out of range

package main

import (
	"context"
	"database/sql"
	"fmt"
	"log"
	"strings"

	"example.com/shelf/internal/fakedb"
)

func main() {
	ctx := context.Background()
	fake := fakedb.New()
	db := sql.OpenDB(fake)
	defer db.Close()

	// Prepare once, execute many times; the statement is bound to the pool
	// and re-prepared on other connections as needed.
	stmt, err := db.PrepareContext(ctx, "UPDATE books SET copies = copies + ? WHERE isbn = ?")
	if err != nil {
		log.Fatal(err)
	}
	defer stmt.Close()

	for _, isbn := range []string{"9780134190440", "9781492077213", "9781617291784"} {
		res, err := stmt.ExecContext(ctx, 1, isbn)
		if err != nil {
			log.Fatal(err)
		}
		n, _ := res.RowsAffected()
		fmt.Println(isbn, "rows affected:", n)
	}

	// database/sql checks the argument count against NumInput.
	_, err = stmt.ExecContext(ctx, 1)
	fmt.Println(err)

	fmt.Println("--- what the driver saw")
	for _, line := range fake.Log() {
		fmt.Println(strings.TrimSpace(line))
	}
}

9780134190440 rows affected: 1
9781492077213 rows affected: 1
9781617291784 rows affected: 1
sql: expected 2 arguments, got 1
--- what the driver saw
prepare UPDATE books SET copies = copies + ? WHERE isbn = ?
exec UPDATE books SET copies = copies + ? WHERE isbn = ? [1 "9780134190440"]
exec UPDATE books SET copies = copies + ? WHERE isbn = ? [1 "9781492077213"]
exec UPDATE books SET copies = copies + ? WHERE isbn = ? [1 "9781617291784"]

package main

import (
	"context"
	"database/sql"
	"errors"
	"fmt"
	"log"

	"example.com/shelf/internal/fakedb"
)

// inTx runs fn inside a transaction: it commits when fn returns nil and rolls
// back on any error or panic.
func inTx(ctx context.Context, db *sql.DB, opts *sql.TxOptions, fn func(*sql.Tx) error) (err error) {
	tx, err := db.BeginTx(ctx, opts)
	if err != nil {
		return err
	}
	defer func() {
		if p := recover(); p != nil {
			_ = tx.Rollback()
			panic(p)
		}
		if err != nil {
			if rbErr := tx.Rollback(); rbErr != nil {
				err = errors.Join(err, rbErr)
			}
		}
	}()
	if err = fn(tx); err != nil {
		return err
	}
	return tx.Commit()
}

func copies(ctx context.Context, q interface {
	QueryRowContext(context.Context, string, ...any) *sql.Row
}, isbn string) int {
	var n int
	if err := q.QueryRowContext(ctx, "SELECT copies FROM books WHERE isbn = ?", isbn).Scan(&n); err != nil {
		log.Fatal(err)
	}
	return n
}

// lend moves one copy of a book out of the catalogue.
func lend(ctx context.Context, tx *sql.Tx, isbn string) error {
	if _, err := tx.ExecContext(ctx, "UPDATE books SET copies = copies + ? WHERE isbn = ?", -1, isbn); err != nil {
		return err
	}
	if copies(ctx, tx, isbn) < 0 {
		return errors.New("no copies left")
	}
	return nil
}

func main() {
	ctx := context.Background()
	db := sql.OpenDB(fakedb.New())
	defer db.Close()
	const goBook = "9781617291784" // one copy on the shelf

	err := inTx(ctx, db, nil, func(tx *sql.Tx) error { return lend(ctx, tx, goBook) })
	fmt.Println("first loan:", err, "- copies:", copies(ctx, db, goBook))

	err = inTx(ctx, db, nil, func(tx *sql.Tx) error { return lend(ctx, tx, goBook) })
	fmt.Println("second loan:", err, "- copies:", copies(ctx, db, goBook))

	// After Commit or Rollback the Tx is finished.
	tx, _ := db.BeginTx(ctx, nil)
	_ = tx.Commit()
	fmt.Println("commit twice:", tx.Commit())
	_, err = tx.ExecContext(ctx, "DELETE FROM books WHERE isbn = ?", goBook)
	fmt.Println("use after commit:", err, errors.Is(err, sql.ErrTxDone))

	// Options: a read-only transaction rejects writes.
	err = inTx(ctx, db, &sql.TxOptions{ReadOnly: true}, func(tx *sql.Tx) error { return lend(ctx, tx, goBook) })
	fmt.Println("read-only:", err)

	// The driver decides which isolation levels exist.
	_, err = db.BeginTx(ctx, &sql.TxOptions{Isolation: sql.LevelRepeatableRead})
	fmt.Println("repeatable read:", err)
	fmt.Println(sql.LevelSerializable, sql.LevelReadCommitted)
}

first loan: <nil> - copies: 0
second loan: no copies left - copies: 0
commit twice: sql: transaction has already been committed or rolled back
use after commit: sql: transaction has already been committed or rolled back true
read-only: fakedb: cannot execute in a read-only transaction (25006)
repeatable read: fakedb: isolation level 4 not supported
Serializable Read Committed

package main

import (
	"context"
	"database/sql"
	"errors"
	"fmt"
	"log"
	"time"

	"example.com/shelf/internal/fakedb"
)

func main() {
	db := sql.OpenDB(fakedb.New())
	defer db.Close()

	// 1. A deadline on one query.
	ctx, cancel := context.WithTimeout(context.Background(), 50*time.Millisecond)
	defer cancel()
	start := time.Now()
	rows, err := db.QueryContext(ctx, "SELECT slow()")
	fmt.Println("slow query:", err, errors.Is(err, context.DeadlineExceeded), time.Since(start) < 2*time.Second)
	if rows != nil {
		rows.Close()
	}

	// 2. Explicit cancellation, with a cause.
	cctx, cancelCause := context.WithCancelCause(context.Background())
	cancelCause(errors.New("client went away"))
	err = db.PingContext(cctx)
	fmt.Println("ping:", err, "| cause:", context.Cause(cctx))

	// 3. Cancelling the context passed to BeginTx rolls the transaction back.
	txCtx, cancelTx := context.WithCancel(context.Background())
	tx, err := db.BeginTx(txCtx, nil)
	if err != nil {
		log.Fatal(err)
	}
	if _, err := tx.ExecContext(txCtx, "DELETE FROM books WHERE isbn = ?", "9780134190440"); err != nil {
		log.Fatal(err)
	}
	cancelTx()
	time.Sleep(100 * time.Millisecond) // let the rollback goroutine run
	fmt.Println("commit after cancel:", tx.Commit())

	var n int
	if err := db.QueryRow("SELECT count(*) FROM books").Scan(&n); err != nil {
		log.Fatal(err)
	}
	fmt.Println("books still there:", n)
}

slow query: context deadline exceeded true true
ping: context canceled | cause: client went away
commit after cancel: sql: transaction has already been committed or rolled back
books still there: 3

package main

import (
	"context"
	"database/sql"
	"encoding/json"
	"errors"
	"fmt"
	"io"
	"log/slog"
	"net/http"
	"net/http/httptest"
	"time"

	"example.com/shelf/internal/fakedb"
)

type Book struct {
	ISBN  string `json:"isbn"`
	Title string `json:"title"`
	Year  *int64 `json:"year,omitempty"`
}

type server struct {
	db  *sql.DB
	log *slog.Logger
}

func (s *server) getBook(w http.ResponseWriter, r *http.Request) {
	// r.Context() is cancelled when the client disconnects; add a budget.
	ctx, cancel := context.WithTimeout(r.Context(), 2*time.Second)
	defer cancel()

	var b Book
	var year sql.Null[int64]
	var id int64
	err := s.db.QueryRowContext(ctx,
		"SELECT id, isbn, title, year FROM books WHERE isbn = ?", r.PathValue("isbn"),
	).Scan(&id, &b.ISBN, &b.Title, &year)
	switch {
	case errors.Is(err, sql.ErrNoRows):
		http.Error(w, "no such book", http.StatusNotFound)
		return
	case err != nil:
		s.log.ErrorContext(ctx, "query failed", "err", err)
		http.Error(w, "internal error", http.StatusInternalServerError)
		return
	}
	if year.Valid {
		b.Year = &year.V
	}
	w.Header().Set("Content-Type", "application/json")
	json.NewEncoder(w).Encode(b)
}

func main() {
	db := sql.OpenDB(fakedb.New())
	defer db.Close()

	s := &server{db: db, log: slog.New(slog.DiscardHandler)}
	mux := http.NewServeMux()
	mux.HandleFunc("GET /books/{isbn}", s.getBook)
	ts := httptest.NewServer(mux)
	defer ts.Close()

	for _, isbn := range []string{"9781492077213", "9781617291784", "0000000000000"} {
		resp, err := http.Get(ts.URL + "/books/" + isbn)
		if err != nil {
			fmt.Println(err)
			return
		}
		body, _ := io.ReadAll(resp.Body)
		resp.Body.Close()
		fmt.Printf("%d %s", resp.StatusCode, body)
	}
}

200 {"isbn":"9781492077213","title":"Learning Go","year":2021}
200 {"isbn":"9781617291784","title":"Go in Action"}
404 no such book

// WRONG: concatenation. Input such as  x' OR '1'='1  changes the query.
query := "SELECT id, isbn, title, year FROM books WHERE isbn = '" + isbn + "'"

// RIGHT: the text is constant, the value is an argument.
rows, err := db.QueryContext(ctx, "SELECT id, isbn, title, year FROM books WHERE isbn = ?", isbn)

package main

import (
	"context"
	"database/sql"
	"fmt"
	"log"
	"strings"

	"example.com/shelf/internal/fakedb"
)

// orderBy chooses a column name. Identifiers cannot be bound as parameters,
// so a fixed allow-list is the safe way to let callers pick one.
func orderBy(col string) (string, error) {
	switch col {
	case "title", "year", "isbn":
		return col, nil
	}
	return "", fmt.Errorf("cannot order by %q", col)
}

// placeholders returns "?, ?, ?" for n values, for use in an IN list.
func placeholders(n int) string {
	return strings.TrimSuffix(strings.Repeat("?, ", n), ", ")
}

func main() {
	ctx := context.Background()
	fake := fakedb.New()
	db := sql.OpenDB(fake)
	defer db.Close()

	hostile := "x' OR '1'='1"

	// WRONG: the input becomes part of the SQL text.
	unsafe := "SELECT id, isbn, title, year FROM books WHERE isbn = '" + hostile + "'"
	fmt.Println("concatenated:", unsafe)

	// RIGHT: the text is fixed; the value travels separately.
	rows, err := db.QueryContext(ctx, "SELECT id, isbn, title, year FROM books WHERE isbn = ?", hostile)
	if err != nil {
		log.Fatal(err)
	}
	n := 0
	for rows.Next() {
		n++
	}
	if err := rows.Err(); err != nil {
		log.Fatal(err)
	}
	rows.Close()
	fmt.Println("parameterized rows matched:", n)
	fmt.Println(strings.Join(fake.Log(), "\n"))

	for _, c := range []string{"title", "title; DROP TABLE books"} {
		col, err := orderBy(c)
		fmt.Printf("orderBy(%q) = %q, %v\n", c, col, err)
	}

	isbns := []any{"9780134190440", "9781492077213", "9781617291784"}
	in := "SELECT id, isbn, title, year FROM books WHERE isbn IN (" + placeholders(len(isbns)) + ")"
	fmt.Println(in, "with", len(isbns), "arguments")
}

concatenated: SELECT id, isbn, title, year FROM books WHERE isbn = 'x' OR '1'='1'
parameterized rows matched: 0
query SELECT id, isbn, title, year FROM books WHERE isbn = ? ["x' OR '1'='1"]
orderBy("title") = "title", <nil>
orderBy("title; DROP TABLE books") = "", cannot order by "title; DROP TABLE books"
SELECT id, isbn, title, year FROM books WHERE isbn IN (?, ?, ?) with 3 arguments

package main

import (
	"context"
	"database/sql"
	"fmt"
	"log"

	"example.com/shelf/internal/fakedb"
)

// titles forgets to call rows.Err after the loop.
func titles(ctx context.Context, db *sql.DB) []string {
	rows, err := db.QueryContext(ctx, "SELECT id, isbn, title, year FROM books ORDER BY id")
	if err != nil {
		log.Fatal(err)
	}
	defer rows.Close()

	var out []string
	for rows.Next() {
		var id int64
		var isbn, title string
		var year sql.Null[int64]
		if err := rows.Scan(&id, &isbn, &title, &year); err != nil {
			log.Fatal(err)
		}
		out = append(out, title)
	}
	return out // BUG: a mid-stream failure looks like a short result
}

func main() {
	fake := fakedb.New()
	fake.FailRowsAfter = 2
	db := sql.OpenDB(fake)
	defer db.Close()
	fmt.Println(titles(context.Background(), db))
}

[The Go Programming Language Learning Go]