SyntaxPilot
Courses/Go Programming/Lesson 68
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 68 of 89

Databases in practice

transactions with BeginTxCommitRollbackisolation levelsprepared statementsQueryContext timeoutspgx for PostgreSQLsqlc code generationORMs (GORM) trade-offsschema migrations (golang-migrate, goose)repository patterntesting with real databases (testcontainers concept)N+1 queries

These are the notes for Go episode 68. In episode 67 Raju's orders moved into a real SQL database with database/sql. Today we make that code ready for production (the real app that real customers use). We follow one chain of problems, and each fix is the next idea: an order that is only half saved leads to transactions; using the wrong handle inside a transaction gives database is locked; many users at once lead to isolation levels; a slow report leads to query timeouts; 2000 inserts lead to prepared statements; a slow list page leads to the N+1 queries trap and JOINs; changing a table leads to migrations; SQL spread everywhere leads to the repository pattern and tests on a real database; and repetitive Scan code leads to the tools pgx, sqlc and GORM.

All outputs are from real runs (Go 1.24.7, linux/amd64) with the SQLite driver github.com/mattn/go-sqlite3 v1.14.32. SQLite keeps the whole database in one file, so every program here runs on its own with no server. Timings change between runs and machines: where a time is printed, your numbers will differ, but the comparison (which one is faster, how many queries) stays the same.

Facts and sources

From the sources read for this episode (listed in the episode script) and the Go documentation of the installed toolchain (go doc database/sql, Go 1.24.7):

  • PostgreSQL 16 documentation: "Read Committed is the default isolation level in PostgreSQL" and "PostgreSQL's Read Uncommitted mode behaves like Read Committed".
  • MySQL 8.4 reference manual: "The default isolation level for InnoDB is REPEATABLE READ".
  • jackc/pgx README: "pgx is a pure Go driver and toolkit for PostgreSQL ... It also includes an adapter for the standard database/sql interface".
  • sqlc README: "sqlc generates type-safe code from SQL" (you write queries in SQL, run sqlc, and call the generated Go code).
  • GORM README: "The fantastic ORM library for Golang" (features include associations, hooks, Preload and auto migrations).
  • goose README: "Goose is a database migration tool. Both a CLI and a library".
  • golang-migrate README: "Database migrations written in Go. Use as CLI or import as library".
  • testcontainers-go README: it "makes it simple to create and clean up container-based dependencies for automated integration/smoke tests".
  • go doc database/sql.ErrTxDone: var ErrTxDone = errors.New("sql: transaction has already been committed or rolled back"), "returned by any operation that is performed on a transaction that has already been committed or rolled back".
  • go doc database/sql.Tx: "A transaction must end with a call to Tx.Commit or Tx.Rollback." and "The statements prepared for a transaction by calling the transaction's Tx.Prepare or Tx.Stmt methods are closed by the call to Tx.Commit or Tx.Rollback."
  • go doc database/sql.DB.BeginTx: "If the context is canceled, the sql package will roll back the transaction." and "The provided TxOptions is optional and may be nil if defaults should be used. If a non-default isolation level is used that the driver doesn't support, an error will be returned."
  • go doc database/sql.TxOptions: Isolation — "If zero, the driver or database's default level is used."; and a ReadOnly bool field.
  • Everything else is from the real runs below.

Every program's go.mod

Every program in these notes is a small module named shop with this go.mod:

module shop

go 1.24

require github.com/mattn/go-sqlite3 v1.14.32

Each program starts from a fresh, empty folder, so shop.db starts empty every time. The _ "github.com/mattn/go-sqlite3" line is the blank import from episode 67: it registers the "sqlite3" driver.

Why transactions

Real-life problem: you pay Raju ₹100 by UPI. Inside the bank that is two steps: take ₹100 from your account, then add ₹100 to Raju's. Now the bank's server crashes between step 1 and step 2. Your money left, Raju got nothing. Each step was fine on its own, but together the data is wrong. Banks make the two steps one unit: both happen, or neither does.

Raju's shop has the same problem. Placing an order is two steps:

  1. INSERT a row into orders;
  2. UPDATE the stock table to take the samosas out.

The stock table has a CHECK (qty >= 0) constraint (a rule the database itself enforces): stock can never go below zero.

The obvious attempt: two separate statements

There are 5 samosas. We order 3, then 4 (only 2 will be left).

main.go:

package main

import (
    "database/sql"
    "fmt"

    _ "github.com/mattn/go-sqlite3"
)

// placeOrder: step 1 saves the order, step 2 takes it out of stock.
func placeOrder(db *sql.DB, item string, n int) error {
    if _, err := db.Exec("INSERT INTO orders (item, qty) VALUES (?, ?)", item, n); err != nil {
        return err
    }
    _, err := db.Exec("UPDATE stock SET qty = qty - ? WHERE item = ?", n, item)
    return err
}

func main() {
    db, _ := sql.Open("sqlite3", "shop.db")
    defer db.Close()
    db.Exec(`CREATE TABLE stock (item TEXT PRIMARY KEY, qty INTEGER CHECK (qty >= 0));
             CREATE TABLE orders (id INTEGER PRIMARY KEY, item TEXT, qty INTEGER);
             INSERT INTO stock VALUES ('samosa', 5);`)

    fmt.Println("order 3 samosas:", placeOrder(db, "samosa", 3))
    fmt.Println("order 4 samosas:", placeOrder(db, "samosa", 4)) // only 2 left!

    var orders, left int
    db.QueryRow("SELECT COUNT(*) FROM orders").Scan(&orders)
    db.QueryRow("SELECT qty FROM stock WHERE item = 'samosa'").Scan(&left)
    fmt.Println("orders saved:", orders, "| samosas left:", left)
}
order 3 samosas: <nil>
order 4 samosas: CHECK constraint failed: qty >= 0
orders saved: 2 | samosas left: 2
  • Order 3: fine (5 → 2 samosas).
  • Order 4: the UPDATE would make the stock −2, so the CHECK rule stopped it: CHECK constraint failed: qty >= 0. Good, the stock is protected.
  • But orders saved: 2. The order for 4 samosas was already inserted before the stock update failed. Raju now has a ghost order: a saved order he can never serve.

Why it fails

Each db.Exec is its own little unit of work. The database finished the INSERT and made it permanent before the UPDATE even started. Nothing tells the database that the two statements belong together, so when the second fails, the first stays. Half a change = wrong data.

Transactions: all or nothing

A transaction is a group of SQL statements that the database treats as one unit:

  • BEGIN starts it.
  • You run your statements.
  • COMMIT makes all of them permanent together.
  • ROLLBACK undoes every statement since BEGIN (on any failure).

Databases promise four things about transactions, called ACID:

Letter Word Plain meaning In Raju's shop
A Atomic all or nothing the order and the stock change happen together, or neither
C Consistent rules like CHECK and NOT NULL always hold stock never below 0
I Isolated other users don't see your half-done work cashier 2 never sees the order before the stock changes
D Durable once committed, it survives a crash or power cut a committed order is still there after a restart

In Go, db.BeginTx(ctx, opts) starts a transaction and returns a *sql.Tx (tx). You run statements on tx (not on db), then call tx.Commit() or tx.Rollback().

The fix: BeginTx, defer Rollback, Commit

main.go:

package main

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

    _ "github.com/mattn/go-sqlite3"
)

func placeOrder(ctx context.Context, db *sql.DB, item string, n int) error {
    tx, err := db.BeginTx(ctx, nil) // start a transaction
    if err != nil {
        return err
    }
    defer tx.Rollback() // undo everything... unless Commit ran first

    if _, err := tx.ExecContext(ctx, "INSERT INTO orders (item, qty) VALUES (?, ?)", item, n); err != nil {
        return err
    }
    if _, err := tx.ExecContext(ctx, "UPDATE stock SET qty = qty - ? WHERE item = ?", n, item); err != nil {
        return err // the deferred Rollback removes the order too
    }
    return tx.Commit() // make both changes permanent, together
}

func main() {
    ctx := context.Background()
    db, _ := sql.Open("sqlite3", "shop.db")
    defer db.Close()
    db.Exec(`CREATE TABLE stock (item TEXT PRIMARY KEY, qty INTEGER CHECK (qty >= 0));
             CREATE TABLE orders (id INTEGER PRIMARY KEY, item TEXT, qty INTEGER);
             INSERT INTO stock VALUES ('samosa', 5);`)

    fmt.Println("order 3 samosas:", placeOrder(ctx, db, "samosa", 3))
    fmt.Println("order 4 samosas:", placeOrder(ctx, db, "samosa", 4))

    var orders, left int
    db.QueryRow("SELECT COUNT(*) FROM orders").Scan(&orders)
    db.QueryRow("SELECT qty FROM stock WHERE item = 'samosa'").Scan(&left)
    fmt.Println("orders saved:", orders, "| samosas left:", left)
}
order 3 samosas: <nil>
order 4 samosas: CHECK constraint failed: qty >= 0
orders saved: 1 | samosas left: 2

Order 4 fails the same way, but now orders saved is 1 and 2 samosas are left: the failed order vanished completely. That's atomicity.

How the code works, line by line:

  • tx, err := db.BeginTx(ctx, nil) starts the transaction. nil options means "use the defaults".
  • defer tx.Rollback() right away. defer (episode 10) runs it when the function returns, whatever the path. It is a safety net:
  • if we return early with an error, the deferred Rollback undoes the insert;
  • if Commit already ran, Rollback does nothing harmful (it just returns sql.ErrTxDone, which we ignore; see below).
  • Both statements use tx.ExecContext, so both are inside the transaction.
  • return tx.Commit() makes both changes permanent together, and returns Commit's error (a commit can fail too, so return it).

The pattern is always the same:

tx, err := db.BeginTx(ctx, nil)
if err != nil {
    return err
}
defer tx.Rollback()
// ... every statement on tx ...
return tx.Commit()

db.Begin() (no context) also exists; BeginTx lets you pass a context and options, so prefer it.

The db-instead-of-tx bug

The most common transaction bug: inside an open transaction, you accidentally run a statement on db instead of tx. The DSN shop.db?_busy_timeout=500 tells the SQLite driver: when the database is locked by someone else, wait at most 500 milliseconds, then give up.

main.go:

package main

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

    _ "github.com/mattn/go-sqlite3"
)

func main() {
    ctx := context.Background()
    db, _ := sql.Open("sqlite3", "shop.db?_busy_timeout=500") // wait at most 500ms for a lock
    defer db.Close()
    db.Exec(`CREATE TABLE orders (id INTEGER PRIMARY KEY, item TEXT, qty INTEGER)`)

    tx, _ := db.BeginTx(ctx, nil)
    tx.ExecContext(ctx, "INSERT INTO orders (item, qty) VALUES ('chai', 2)")

    start := time.Now()
    _, err := db.ExecContext(ctx, "INSERT INTO orders (item, qty) VALUES ('vada pav', 1)") // oops: db, not tx
    fmt.Println("db.Exec inside the transaction:", err, "after", time.Since(start).Round(100*time.Millisecond))

    fmt.Println("commit:", tx.Commit())
    fmt.Println("rollback after commit:", tx.Rollback())
}
db.Exec inside the transaction: database is locked after 500ms
commit: <nil>
rollback after commit: sql: transaction has already been committed or rolled back

Why it fails

  • *sql.DB is a connection pool (episode 67). A transaction lives on one connection that it keeps until Commit or Rollback.
  • db.ExecContext borrowed a different connection from the pool, outside our transaction.
  • Our own transaction had written (inserted chai), so it held SQLite's write lock (a "do not touch, I'm writing" flag). The other connection waited 500 ms for that lock and gave up: database is locked. Without a timeout, it can hang.
  • On PostgreSQL the same bug means the statement is not part of the transaction, so a Rollback can't undo it.

Rule: inside a transaction, every statement goes through tx., never db. If a helper function needs to run inside a transaction, pass it the *sql.Tx.

The fix

The same program with tx.ExecContext on the second insert (fixbug.go):

_, err := tx.ExecContext(ctx, "INSERT INTO orders (item, qty) VALUES ('vada pav', 1)") // tx, not db
fmt.Println("tx.Exec inside the transaction:", err)
fmt.Println("commit:", tx.Commit())
tx.Exec inside the transaction: <nil>
commit: <nil>
orders saved: 2

Rollback after Commit: sql.ErrTxDone

The last line of the bug program printed sql: transaction has already been committed or rolled back. That is sql.ErrTxDone: every operation on a transaction that has already ended returns it. That's why defer tx.Rollback() is safe after a successful Commit: it returns ErrTxDone and the deferred call ignores the error. Don't check it.

Two more real ways to meet it, and a silent bug, in one program:

main.go:

package main

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

    _ "github.com/mattn/go-sqlite3"
)

// placeOrder forgets tx.Commit(): the deferred Rollback silently undoes the work.
func placeOrder(ctx context.Context, db *sql.DB, item string) error {
    tx, err := db.BeginTx(ctx, nil)
    if err != nil {
        return err
    }
    defer tx.Rollback()
    _, err = tx.ExecContext(ctx, "INSERT INTO orders (item, qty) VALUES (?, 1)", item)
    return err // oops: no tx.Commit()
}

func main() {
    ctx := context.Background()
    db, _ := sql.Open("sqlite3", "shop.db")
    defer db.Close()
    db.Exec(`CREATE TABLE orders (id INTEGER PRIMARY KEY, item TEXT, qty INTEGER)`)

    fmt.Println("place order:", placeOrder(ctx, db, "chai"))
    var n int
    db.QueryRow("SELECT COUNT(*) FROM orders").Scan(&n)
    fmt.Println("orders saved:", n)

    tx, _ := db.BeginTx(ctx, nil)
    tx.Commit()
    _, err := tx.ExecContext(ctx, "INSERT INTO orders (item, qty) VALUES ('chai', 1)")
    fmt.Println("exec after commit:", err)
}
place order: <nil>
orders saved: 0
exec after commit: sql: transaction has already been committed or rolled back
  • Forgetting Commit gives no error at all: placeOrder returned nil, but the deferred Rollback silently undid the insert, so 0 orders were saved. Always end the happy path with return tx.Commit().
  • Using tx after Commit returns ErrTxDone. A transaction is single-use: start a new one for the next unit of work.

Isolation levels

Real-life example: two cashiers share one stock register. Isolation is about many transactions running at the same time. Three surprises can happen:

Surprise At Raju's counters In the database
Dirty read cashier 2 reads a number cashier 1 is still writing, then cashier 1 erases it: cashier 2 used a number that never existed reading data another transaction hasn't committed yet
Non-repeatable read cashier 2 checks samosa stock, does some work, checks again, gets a different answer because cashier 1 sold some in between the same row changed between two reads inside one transaction
Phantom read counting today's orders twice, a new order appears the second time, like a ghost new rows now match the same query

An isolation level says which of these surprises a transaction is protected from. The SQL standard has four:

Level What it promises Protects you from Default in
Read Uncommitted almost nothing: you may see uncommitted data nothing (dirty reads possible) (PostgreSQL's Read Uncommitted behaves like Read Committed)
Read Committed you only see committed data dirty reads PostgreSQL
Repeatable Read rows you have read don't change under you dirty and non-repeatable reads MySQL InnoDB
Serializable the result is as if the transactions ran one at a time all three surprises

Stricter = safer, but slower: transactions wait for each other more, or the database aborts one with an error and you must retry it (run the whole transaction again). Most apps use the default and pick Serializable for money-like logic.

Choosing a level in Go: sql.TxOptions

You pass a *sql.TxOptions to BeginTx. Isolation picks the level (zero = the driver's or database's default), and ReadOnly: true marks a transaction that will only read.

main.go:

package main

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

    _ "github.com/mattn/go-sqlite3"
)

func main() {
    ctx := context.Background()
    db, _ := sql.Open("sqlite3", "shop.db")
    defer db.Close()
    db.Exec(`CREATE TABLE stock (item TEXT PRIMARY KEY, qty INTEGER);
             INSERT INTO stock VALUES ('samosa', 5);`)

    for _, lvl := range []sql.IsolationLevel{sql.LevelDefault, sql.LevelReadCommitted, sql.LevelSerializable} {
        tx, err := db.BeginTx(ctx, &sql.TxOptions{Isolation: lvl})
        fmt.Printf("%-16v begin err: %v\n", lvl, err)
        if err == nil {
            tx.Rollback()
        }
    }

    tx, err := db.BeginTx(ctx, &sql.TxOptions{ReadOnly: true})
    fmt.Println("read-only begin err:", err)
    var left int
    tx.QueryRowContext(ctx, "SELECT qty FROM stock WHERE item = 'samosa'").Scan(&left)
    fmt.Println("read inside it:", left)
    tx.Rollback()
}
Default          begin err: <nil>
Read Committed   begin err: <nil>
Serializable     begin err: <nil>
read-only begin err: <nil>
read inside it: 5

This run only shows the Go API: the SQLite driver accepted every option. What a level actually does is decided by the database (read your database's documentation), and BeginTx returns an error if you ask for a level the driver doesn't support. On PostgreSQL the same line from the video:

tx, err := db.BeginTx(ctx, &sql.TxOptions{Isolation: sql.LevelSerializable, ReadOnly: true})

Query timeouts with QueryContext

Real-life problem: a customer's request runs a slow report. Without a limit, the customer waits, the request holds a pool connection the whole time, and if many requests do this the pool runs dry and the whole app freezes.

The fix is a context with a deadline (episode 51 on context): context.WithTimeout gives the request a time budget, and the ...Context versions of the database methods pass it to the driver.

main.go:

package main

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

    _ "github.com/mattn/go-sqlite3"
)

// a deliberately slow report: count to 100 million inside the database
const slowReport = `WITH RECURSIVE n(x) AS (SELECT 1 UNION ALL SELECT x + 1 FROM n WHERE x < 100000000)
                    SELECT COUNT(*) FROM n`

func main() {
    db, _ := sql.Open("sqlite3", "shop.db")
    defer db.Close()

    // the request may spend at most 200ms in the database
    ctx, cancel := context.WithTimeout(context.Background(), 200*time.Millisecond)
    defer cancel()

    start := time.Now()
    var total int
    err := db.QueryRowContext(ctx, slowReport).Scan(&total)
    fmt.Println("report:", total, "| err:", err, "| took:", time.Since(start).Round(10*time.Millisecond))
    if errors.Is(err, context.DeadlineExceeded) {
        fmt.Println("-> answer 503 and free the connection, don't make the customer wait")
    }
}
report: 0 | err: context deadline exceeded | took: 200ms
-> answer 503 and free the connection, don't make the customer wait
  • The SQL WITH RECURSIVE n(x) AS (...) builds a list of numbers 1, 2, 3, ... up to 100 million inside SQLite, and COUNT(*) counts them: a deliberately slow query.
  • After 200 ms the context's deadline passed: context deadline exceeded, and total stayed 0. The driver stopped the query inside the database, and the connection went back to the pool.
  • errors.Is(err, context.DeadlineExceeded) recognises the timeout, so a web handler can answer 503 Service Unavailable (an HTTP status meaning "try again later") instead of hanging.
  • defer cancel() releases the context's timer even if the query finishes early. Always call cancel.

Use the Context versions everywhere: QueryContext, QueryRowContext, ExecContext, BeginTx, PrepareContext, and pass the request's context (r.Context() in an HTTP handler), so a cancelled request also stops its database work. BeginTx's documentation adds: "If the context is canceled, the sql package will roll back the transaction."

Prepared statements

Real-life example: writing every order slip from scratch is slow, and the cook reads each one fresh. A printed form with blanks is faster: you only fill in the values.

  • A plain Exec is the slip from scratch: the database parses (reads and checks) and plans (decides how to run) the SQL every time.
  • A prepared statement is the printed form: the database parses the SQL once, and you run it many times with different values. In Go: stmt, err := tx.Prepare(sql) (or db.Prepare), then stmt.Exec(values...), and stmt.Close() when done.

2000 inserts, two ways

main.go:

package main

import (
    "database/sql"
    "fmt"
    "time"

    _ "github.com/mattn/go-sqlite3"
)

func fresh() *sql.DB {
    db, _ := sql.Open("sqlite3", "file:shop.db")
    db.Exec(`DROP TABLE IF EXISTS orders; CREATE TABLE orders (id INTEGER PRIMARY KEY, item TEXT, qty INTEGER)`)
    return db
}

func main() {
    // A: 2000 separate INSERTs, each its own transaction, each SQL parsed again
    db := fresh()
    start := time.Now()
    for i := 0; i < 2000; i++ {
        db.Exec("INSERT INTO orders (item, qty) VALUES (?, ?)", "chai", i%5+1)
    }
    a := time.Since(start)
    db.Close()

    // B: one transaction, one prepared statement, executed 2000 times
    db = fresh()
    defer db.Close()
    start = time.Now()
    tx, _ := db.Begin()
    stmt, _ := tx.Prepare("INSERT INTO orders (item, qty) VALUES (?, ?)") // parsed once
    defer stmt.Close()
    for i := 0; i < 2000; i++ {
        stmt.Exec("chai", i%5+1) // only the values travel
    }
    tx.Commit()
    b := time.Since(start)

    fmt.Println("A, one by one:           ", a.Round(time.Millisecond))
    fmt.Println("B, prepared + one tx:    ", b.Round(time.Millisecond))
    fmt.Printf("B is %.0fx faster\n", float64(a)/float64(b))
}
A, one by one:            2.747s
B, prepared + one tx:     3ms
B is 806x faster

A second run on the same machine printed A, one by one: 1.466s, B, prepared + one tx: 3ms, B is 535x faster. Timings vary a lot (A depends mostly on how fast the disk confirms each write), but B is always many times faster.

Two reasons:

  1. Parsing: B parsed the SQL once, A parsed it 2000 times.
  2. Transactions: in A, each db.Exec is its own transaction, so the database saved to disk 2000 times. In B, the single Commit saved once.

Which reason matters more? A variant C with one transaction but no Prepare (txonly.go):

tx, _ := db.Begin()
for i := 0; i < 2000; i++ {
    tx.Exec("INSERT INTO orders (item, qty) VALUES (?, ?)", "chai", i%5+1)
}
tx.Commit()
C, one tx, no Prepare:    5ms
B, one tx + Prepare:      3ms
rows in B's table: 2000

In this SQLite run, the one transaction gave most of the speed-up, and Prepare added a smaller gain on top. On a database server, each round trip and parse costs more, so preparing helps more. Lessons:

  • Batch many writes into one transaction.
  • Prepare once, Exec many for statements in hot loops.
  • Close statements when done (defer stmt.Close()). A statement prepared on tx belongs to that transaction and is closed by its Commit or Rollback.
  • Placeholders (?, $1) already protect you from SQL injection (episode 66) without Prepare: prepare for speed, not for safety.

The N+1 queries trap

Real-life problem: Raju wants a list of 50 orders with each customer's name. His helper gets the order list in one trip to the godown (the store room), then, for each order, walks to the godown again to look up that customer: 1 + 50 trips. Smarter: one trip with the whole list, bringing every name back at once.

The N+1 queries problem: one query for a list (the "1"), then one more query for each of the N items. In SQL, the one-trip way is a JOIN: a query that matches rows from two tables (here, each order to its customer by id) and returns them together.

main.go:

package main

import (
    "database/sql"
    "fmt"
    "time"

    _ "github.com/mattn/go-sqlite3"
)

var queries int // how many times we talked to the database

func setup(db *sql.DB) {
    db.Exec(`CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
             CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, item TEXT)`)
    for i := 1; i <= 50; i++ {
        db.Exec("INSERT INTO customers (name) VALUES (?)", fmt.Sprint("customer ", i))
        db.Exec("INSERT INTO orders (customer_id, item) VALUES (?, 'chai')", i)
    }
}

func main() {
    db, _ := sql.Open("sqlite3", "file:shop.db?_busy_timeout=1000")
    defer db.Close()
    setup(db)

    // The N+1 way: 1 query for the orders, then 1 more query PER order
    start := time.Now()
    rows, _ := db.Query("SELECT id, customer_id FROM orders")
    queries++
    lines := 0
    for rows.Next() {
        var id, cid int
        var name string
        rows.Scan(&id, &cid)
        db.QueryRow("SELECT name FROM customers WHERE id = ?", cid).Scan(&name)
        queries++
        lines++
    }
    rows.Close()
    fmt.Println("N+1 :", lines, "lines,", queries, "queries,", time.Since(start).Round(10*time.Microsecond))

    // The JOIN way: the database matches orders to customers in ONE query
    queries = 0
    start = time.Now()
    rows, _ = db.Query(`SELECT o.id, c.name FROM orders o JOIN customers c ON c.id = o.customer_id`)
    queries++
    lines = 0
    for rows.Next() {
        var id int
        var name string
        rows.Scan(&id, &name)
        lines++
    }
    rows.Close()
    fmt.Println("JOIN:", lines, "lines,", queries, "query,  ", time.Since(start).Round(10*time.Microsecond))
}
N+1 : 50 lines, 51 queries, 860µs
JOIN: 50 lines, 1 query,   80µs
  • Same 50 lines: N+1 needed 51 queries, the JOIN 1. (Times vary between runs; the counts don't.)
  • SELECT o.id, c.name FROM orders o JOIN customers c ON c.id = o.customer_id: o and c are short aliases for the tables, and ON c.id = o.customer_id is the matching rule.
  • Here the database is a file on the same machine, so each query is cheap. With a real database server, each query is also a trip across the network, so 51 trips can turn a fast page into a slow one, and 1000 orders mean 1001 trips.
  • N+1 hides easily: in a loop, in a helper called from a loop, or in an ORM that loads related data lazily. Count your queries: any query inside a loop over rows is a suspect.

Fixes: a JOIN; or one extra query that fetches all the related rows at once (WHERE id IN (...)), the idea behind GORM's Preload (see below).

Schema migrations

Real-life example: renovating a running shop. You keep a numbered work log (1: build shelves, 2: add a fridge), you tick off each finished job so nobody builds the same shelf twice, and a good log also says how to undo a job.

The schema is the shape of the database: its tables, columns and constraints. Tables change over time (a new phone column, a new payments table). Changing them by hand on every server, laptop and test database is how databases end up with different shapes. Instead:

  • a migration is one numbered change to the schema (001_create_orders.sql, 002_add_phone.sql);
  • a table in the database (here schema_migrations) remembers which versions already ran on that database;
  • good tools give each migration an up step (apply it) and a down step (undo it).

A tiny migration runner

main.go:

package main

import (
    "database/sql"
    "fmt"

    _ "github.com/mattn/go-sqlite3"
)

// Every change to the schema is a numbered migration. Never edit one that already ran: add a new one.
var migrations = []struct {
    version int
    name    string
    sql     string
}{
    {1, "create_orders", `CREATE TABLE orders (id INTEGER PRIMARY KEY, item TEXT NOT NULL, qty INTEGER NOT NULL)`},
    {2, "add_phone", `ALTER TABLE orders ADD COLUMN phone TEXT`},
}

func migrate(db *sql.DB) error {
    db.Exec(`CREATE TABLE IF NOT EXISTS schema_migrations (version INTEGER PRIMARY KEY)`)
    var current int
    db.QueryRow("SELECT COALESCE(MAX(version), 0) FROM schema_migrations").Scan(&current)
    ran := 0
    for _, m := range migrations {
        if m.version <= current {
            continue // already applied on this database
        }
        tx, err := db.Begin()
        if err != nil {
            return err
        }
        if _, err := tx.Exec(m.sql); err != nil {
            tx.Rollback()
            return fmt.Errorf("migration %d %s: %w", m.version, m.name, err)
        }
        tx.Exec("INSERT INTO schema_migrations (version) VALUES (?)", m.version)
        if err := tx.Commit(); err != nil {
            return err
        }
        fmt.Printf("  applied %d_%s\n", m.version, m.name)
        ran++
    }
    if ran == 0 {
        fmt.Println("  up to date at version", current)
    }
    return nil
}

func main() {
    db, _ := sql.Open("sqlite3", "shop.db")
    defer db.Close()
    fmt.Println("first start:")
    fmt.Println("  err:", migrate(db))
    fmt.Println("second start (a restart or a deploy):")
    fmt.Println("  err:", migrate(db))
}
first start:
  applied 1_create_orders
  applied 2_add_phone
  err: <nil>
second start (a restart or a deploy):
  up to date at version 2
  err: <nil>
  • CREATE TABLE IF NOT EXISTS schema_migrations makes the tracking table only the first time.
  • SELECT COALESCE(MAX(version), 0) finds the highest version already applied (MAX of no rows is NULL, and COALESCE turns NULL into 0, as in episode 67).
  • Each newer migration runs in its own transaction, together with recording its version, so a migration is never "applied but not recorded" or the other way round.
  • ALTER TABLE orders ADD COLUMN phone TEXT changes an existing table: it adds a column.
  • On the second start nothing runs: up to date at version 2. Every database (a laptop, a test database, production) ends up with exactly the same shape.

The golden rule: never edit a migration that already ran somewhere. It will never run again on those databases (their schema_migrations already lists it), so they keep the old shape while new databases get the new one. Add a new migration instead.

Real migration tools: goose and golang-migrate

In real projects, use a tool:

  • goose is "a database migration tool. Both a CLI and a library" (a CLI is a command you run in the terminal; a library is a package you import into your Go program). A goose migration is one SQL file, such as 00002_add_phone.sql, with an Up section and a Down section marked by comments:
-- +goose Up
ALTER TABLE orders ADD COLUMN phone TEXT;

-- +goose Down
ALTER TABLE orders DROP COLUMN phone;
  • golang-migrate ("Database migrations written in Go. Use as CLI or import as library") uses a pair of files per change: 2_add_phone.up.sql and 2_add_phone.down.sql.
  • Both keep a table of applied versions in the database, just like our schema_migrations.

How teams use them:

  • Run migrations at deploy time, before the new code starts (a deploy is putting a new version of the app on the servers).
  • During a deploy, old and new versions of the app run side by side for a while. So add a column first and start using it in a later release, and never remove a column or table that old code still reads.

The repository pattern

Real-life example: the store-room clerk. Cashiers don't walk into the store room; they ask the clerk. Only the clerk knows the shelves. And a trainee clerk can stand in for practice: same questions, same answers.

When SQL is spread across every HTTP handler, changing one table means hunting through the whole app. A repository is that clerk: one type that owns all the SQL for one kind of data. The rest of the app calls methods like Create and Get and never sees SQL. Because the app only knows an interface (episode 21), you can swap in another implementation (a test database, an in-memory fake) without changing the app.

Testing with a real database

store.go:

package shop

import (
    "context"
    "database/sql"
    "errors"
)

type Order struct {
    ID   int64
    Item string
    Qty  int
}

var ErrNotFound = errors.New("order not found")

// OrderRepo is what the rest of the app knows about storage: no SQL outside this file.
type OrderRepo interface {
    Create(ctx context.Context, o *Order) error
    Get(ctx context.Context, id int64) (Order, error)
}

type SQLiteRepo struct{ db *sql.DB }

func (r SQLiteRepo) Create(ctx context.Context, o *Order) error {
    res, err := r.db.ExecContext(ctx, "INSERT INTO orders (item, qty) VALUES (?, ?)", o.Item, o.Qty)
    if err != nil {
        return err
    }
    o.ID, err = res.LastInsertId()
    return err
}

func (r SQLiteRepo) Get(ctx context.Context, id int64) (Order, error) {
    var o Order
    err := r.db.QueryRowContext(ctx, "SELECT id, item, qty FROM orders WHERE id = ?", id).Scan(&o.ID, &o.Item, &o.Qty)
    if errors.Is(err, sql.ErrNoRows) {
        return Order{}, ErrNotFound // callers never see database error types
    }
    return o, err
}

store_test.go:

package shop

import (
    "context"
    "database/sql"
    "errors"
    "path/filepath"
    "testing"

    _ "github.com/mattn/go-sqlite3"
)

// a REAL database, fresh for every test, deleted afterwards
func newRepo(t *testing.T) SQLiteRepo {
    db, err := sql.Open("sqlite3", filepath.Join(t.TempDir(), "test.db"))
    if err != nil {
        t.Fatal(err)
    }
    t.Cleanup(func() { db.Close() })
    db.Exec(`CREATE TABLE orders (id INTEGER PRIMARY KEY, item TEXT NOT NULL, qty INTEGER NOT NULL)`)
    return SQLiteRepo{db}
}

func TestCreateAndGet(t *testing.T) {
    repo, ctx := newRepo(t), context.Background()
    o := Order{Item: "chai", Qty: 2}
    if err := repo.Create(ctx, &o); err != nil {
        t.Fatal(err)
    }
    got, err := repo.Get(ctx, o.ID)
    if err != nil || got != o {
        t.Fatalf("Get(%d) = %+v, %v; want %+v", o.ID, got, err, o)
    }
}

func TestGetMissing(t *testing.T) {
    repo := newRepo(t)
    if _, err := repo.Get(context.Background(), 99); !errors.Is(err, ErrNotFound) {
        t.Fatalf("err = %v, want ErrNotFound", err)
    }
}

Run with go test -v .:

=== RUN   TestCreateAndGet
--- PASS: TestCreateAndGet (0.00s)
=== RUN   TestGetMissing
--- PASS: TestGetMissing (0.00s)
PASS
ok      shop    0.009s
  • OrderRepo is the interface the app depends on: Create and Get. No SQL outside store.go.
  • SQLiteRepo implements it with SQL. Create fills in the new id with LastInsertId (o *Order is a pointer so the caller sees the id).
  • Get turns sql.ErrNoRows into the repository's own ErrNotFound, so callers (handlers, other packages) never depend on database error types. A handler maps ErrNotFound to 404.
  • The tests use a real SQLite database in t.TempDir(): a fresh folder for each test, deleted automatically afterwards. t.Cleanup registers db.Close() to run when the test ends (testing was episodes 40 and 41).
  • got != o compares the structs field by field (structs of comparable fields can be compared with ==, episode 19).

Why test against a real database instead of a fake? A real database catches what a fake can't: a wrong column name, a broken constraint, SQL that only one database accepts. Fakes are still useful for testing handlers quickly; the repository itself should be tested on the real thing.

testcontainers (concept)

SQLite is a file, so a temp folder is enough. For PostgreSQL or MySQL you need a running server. testcontainers-go "makes it simple to create and clean up container-based dependencies for automated integration/smoke tests": your test starts a real database in a Docker container (a container is a small, isolated package that runs a program, here a database server, with everything it needs), runs against it, and removes it afterwards. An integration test is a test that checks your code together with a real outside part (here, the database), instead of a fake.

pgx, sqlc and GORM

Writing Scan calls by hand gets repetitive. Two popular answers, side by side (from the video; these are not run here):

sqlc: you write SQL, it writes Go. "sqlc generates type-safe code from SQL." You write plain SQL with a name comment in a file such as query.sql, run the sqlc tool, and it generates Go functions with the right parameter and result types:

-- name: GetOrder :one
SELECT id, item, qty FROM orders
WHERE id = $1;

-- sqlc generates, roughly:
-- func (q *Queries) GetOrder(
--     ctx context.Context, id int64,
-- ) (Order, error)
  • Code generation: a tool writes Go code for you from another file (here, SQL); you commit the generated code and call it like normal code.
  • Type-safe: the compiler checks you pass an int64 id and get back an Order; a mistake in your SQL shows up when you generate, not in production.

GORM: an ORM, Go methods write the SQL. GORM is "The fantastic ORM library for Golang". ORM means object-relational mapping: you work with Go structs and methods, and the library writes the SQL for you.

var o Order
db.First(&o, 1)
// GORM writes:
// SELECT * FROM orders
// WHERE id = 1 LIMIT 1

db.Preload("Items").Find(&orders)
  • It's quick to start and has extras like associations (related structs, such as an order's items), hooks (functions that run before or after saving) and auto migrations.
  • The trade-off: the SQL is hidden, so it's easy to cause N+1 queries or slow queries without noticing. Preload loads related rows in one extra query instead of one per row. Log the SQL it runs.

pgx is "a pure Go driver and toolkit for PostgreSQL". Pure Go means it doesn't need C (unlike go-sqlite3, which uses cgo). You can use it:

  • through database/sql (pgx "includes an adapter for the standard database/sql interface"), so all the code in these notes works the same; or
  • through its own API, with its own connection pool (pgxpool) and PostgreSQL features such as COPY (fast bulk loading) and LISTEN/NOTIFY (messages between connections).
  • Remember: PostgreSQL placeholders are $1, $2, not ?.

Which one when?

Choice Pick it when Watch out for
plain database/sql + your SQL small apps, full control repetitive Scan code
sqlc many queries, want type safety, keep real SQL a generate step in your build
GORM lots of simple CRUD screens, prototypes hidden SQL, N+1; log the queries
pgx (driver) you use PostgreSQL its native API is PostgreSQL-only

(CRUD = Create, Read, Update, Delete: the four basic operations on records.) Many Go teams pick pgx with sqlc.

Databases in practice in one page

  • Transactions: BeginTx → defer tx.Rollback() → every statement on tx., never db. → return tx.Commit().
  • Isolation: pick the level on purpose; Read Committed is PostgreSQL's default; Serializable for money-like logic, with retries.
  • Context: QueryContext / ExecContext / BeginTx with the request's context and a timeout.
  • Loops of writes: one transaction, a prepared statement.
  • No queries inside loops over rows: a JOIN (or Preload) instead of N+1.
  • Migrations: numbered, tracked, never edited once applied; run at deploy.
  • Repository: SQL behind an interface in one place; test it on a real database.

Mini project: safe checkout

Build a safe checkout for Raju:

  1. Migrations 1-3 create customers, orders and stock (with CHECK (qty >= 0)), using a migration runner with schema_migrations.
  2. OrderRepo.Checkout(ctx, customer, items) places a whole basket in one transaction.
  3. If any item is out of stock, nothing is saved and it returns ErrOutOfStock.
  4. ListWithCustomer lists orders with the customer's name in one JOIN, no N+1.
  5. Tests on a temporary database, with a 2-second request timeout on the context.
Full solution

shop.go:

package shop

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

var ErrOutOfStock = errors.New("out of stock")

// Three numbered migrations: customers, orders, stock.
var migrations = []struct {
    version int
    name    string
    sql     string
}{
    {1, "create_customers", `CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT NOT NULL)`},
    {2, "create_orders", `CREATE TABLE orders (id INTEGER PRIMARY KEY,
        customer_id INTEGER NOT NULL REFERENCES customers(id), item TEXT NOT NULL, qty INTEGER NOT NULL)`},
    {3, "create_stock", `CREATE TABLE stock (item TEXT PRIMARY KEY, qty INTEGER NOT NULL CHECK (qty >= 0))`},
}

// Migrate applies every migration newer than the database's version, each in its own transaction.
func Migrate(ctx context.Context, db *sql.DB) error {
    if _, err := db.ExecContext(ctx, `CREATE TABLE IF NOT EXISTS schema_migrations (version INTEGER PRIMARY KEY)`); err != nil {
        return err
    }
    var current int
    if err := db.QueryRowContext(ctx, "SELECT COALESCE(MAX(version), 0) FROM schema_migrations").Scan(&current); err != nil {
        return err
    }
    for _, m := range migrations {
        if m.version <= current {
            continue
        }
        tx, err := db.BeginTx(ctx, nil)
        if err != nil {
            return err
        }
        if _, err := tx.ExecContext(ctx, m.sql); err != nil {
            tx.Rollback()
            return fmt.Errorf("migration %d %s: %w", m.version, m.name, err)
        }
        if _, err := tx.ExecContext(ctx, "INSERT INTO schema_migrations (version) VALUES (?)", m.version); err != nil {
            tx.Rollback()
            return err
        }
        if err := tx.Commit(); err != nil {
            return err
        }
    }
    return nil
}

type Item struct {
    Name string
    Qty  int
}

type OrderLine struct {
    OrderID  int64
    Customer string
    Item     string
    Qty      int
}

type OrderRepo interface {
    Checkout(ctx context.Context, customerID int64, items []Item) error
    ListWithCustomer(ctx context.Context) ([]OrderLine, error)
}

type SQLiteRepo struct{ db *sql.DB }

// Checkout saves the whole basket, or nothing at all.
func (r SQLiteRepo) Checkout(ctx context.Context, customerID int64, items []Item) error {
    tx, err := r.db.BeginTx(ctx, nil)
    if err != nil {
        return err
    }
    defer tx.Rollback() // safety net: a no-op after Commit

    for _, it := range items {
        // take the stock only if there is enough of it
        res, err := tx.ExecContext(ctx,
            "UPDATE stock SET qty = qty - ? WHERE item = ? AND qty >= ?", it.Qty, it.Name, it.Qty)
        if err != nil {
            return err
        }
        n, err := res.RowsAffected()
        if err != nil {
            return err
        }
        if n == 0 {
            return fmt.Errorf("%s x%d: %w", it.Name, it.Qty, ErrOutOfStock)
        }
        if _, err := tx.ExecContext(ctx,
            "INSERT INTO orders (customer_id, item, qty) VALUES (?, ?, ?)", customerID, it.Name, it.Qty); err != nil {
            return err
        }
    }
    return tx.Commit()
}

// ListWithCustomer: one JOIN, not one query per order.
func (r SQLiteRepo) ListWithCustomer(ctx context.Context) ([]OrderLine, error) {
    rows, err := r.db.QueryContext(ctx, `SELECT o.id, c.name, o.item, o.qty
        FROM orders o JOIN customers c ON c.id = o.customer_id ORDER BY o.id`)
    if err != nil {
        return nil, err
    }
    defer rows.Close()
    var out []OrderLine
    for rows.Next() {
        var l OrderLine
        if err := rows.Scan(&l.OrderID, &l.Customer, &l.Item, &l.Qty); err != nil {
            return nil, err
        }
        out = append(out, l)
    }
    return out, rows.Err()
}

shop_test.go:

package shop

import (
    "context"
    "database/sql"
    "errors"
    "path/filepath"
    "testing"
    "time"

    _ "github.com/mattn/go-sqlite3"
)

// newRepo: a real, fresh SQLite database per test, migrated and filled with a little data.
func newRepo(t *testing.T) (SQLiteRepo, *sql.DB, context.Context) {
    ctx, cancel := context.WithTimeout(context.Background(), 2*time.Second) // the request timeout
    t.Cleanup(cancel)
    db, err := sql.Open("sqlite3", filepath.Join(t.TempDir(), "test.db"))
    if err != nil {
        t.Fatal(err)
    }
    t.Cleanup(func() { db.Close() })
    if err := Migrate(ctx, db); err != nil {
        t.Fatal(err)
    }
    if err := Migrate(ctx, db); err != nil { // running it twice must be harmless
        t.Fatal(err)
    }
    if _, err := db.ExecContext(ctx, `INSERT INTO customers (name) VALUES ('asha'), ('ravi');
        INSERT INTO stock VALUES ('chai', 10), ('samosa', 5);`); err != nil {
        t.Fatal(err)
    }
    return SQLiteRepo{db}, db, ctx
}

func stockOf(t *testing.T, db *sql.DB, item string) int {
    var n int
    if err := db.QueryRow("SELECT qty FROM stock WHERE item = ?", item).Scan(&n); err != nil {
        t.Fatal(err)
    }
    return n
}

func TestCheckoutSavesEverything(t *testing.T) {
    repo, db, ctx := newRepo(t)
    if err := repo.Checkout(ctx, 1, []Item{{"chai", 2}, {"samosa", 3}}); err != nil {
        t.Fatal(err)
    }
    if c, s := stockOf(t, db, "chai"), stockOf(t, db, "samosa"); c != 8 || s != 2 {
        t.Fatalf("stock chai=%d samosa=%d, want 8 and 2", c, s)
    }
    t.Logf("stock after checkout: chai=8 samosa=2")
}

func TestCheckoutOutOfStockSavesNothing(t *testing.T) {
    repo, db, ctx := newRepo(t)
    // chai is fine, but only 5 samosas exist
    err := repo.Checkout(ctx, 2, []Item{{"chai", 1}, {"samosa", 6}})
    if !errors.Is(err, ErrOutOfStock) {
        t.Fatalf("err = %v, want ErrOutOfStock", err)
    }
    t.Logf("checkout error: %v", err)
    var orders int
    db.QueryRow("SELECT COUNT(*) FROM orders").Scan(&orders)
    if orders != 0 || stockOf(t, db, "chai") != 10 {
        t.Fatalf("orders=%d chai=%d, want 0 and 10: the chai part must be rolled back", orders, stockOf(t, db, "chai"))
    }
    t.Logf("orders saved: %d, chai still %d", orders, stockOf(t, db, "chai"))
}

func TestListWithCustomer(t *testing.T) {
    repo, _, ctx := newRepo(t)
    repo.Checkout(ctx, 1, []Item{{"chai", 2}})
    repo.Checkout(ctx, 2, []Item{{"samosa", 1}, {"chai", 1}})
    lines, err := repo.ListWithCustomer(ctx)
    if err != nil || len(lines) != 3 {
        t.Fatalf("got %d lines, err %v; want 3", len(lines), err)
    }
    for _, l := range lines {
        t.Logf("#%d %s %s x%d", l.OrderID, l.Customer, l.Item, l.Qty)
    }
}

func TestMigrationsRecorded(t *testing.T) {
    _, db, ctx := newRepo(t)
    var n, max int
    db.QueryRowContext(ctx, "SELECT COUNT(*), MAX(version) FROM schema_migrations").Scan(&n, &max)
    if n != 3 || max != 3 {
        t.Fatalf("schema_migrations: %d rows, max %d; want 3 and 3", n, max)
    }
}

Run with go test -v . (the same go.mod as above):

=== RUN   TestCheckoutSavesEverything
    shop_test.go:52: stock after checkout: chai=8 samosa=2
--- PASS: TestCheckoutSavesEverything (0.01s)
=== RUN   TestCheckoutOutOfStockSavesNothing
    shop_test.go:62: checkout error: samosa x6: out of stock
    shop_test.go:68: orders saved: 0, chai still 10
--- PASS: TestCheckoutOutOfStockSavesNothing (0.00s)
=== RUN   TestListWithCustomer
    shop_test.go:80: #1 asha chai x2
    shop_test.go:80: #2 ravi samosa x1
    shop_test.go:80: #3 ravi chai x1
--- PASS: TestListWithCustomer (0.01s)
=== RUN   TestMigrationsRecorded
--- PASS: TestMigrationsRecorded (0.01s)
PASS
ok      shop    0.029s

How it works:

  • UPDATE stock SET qty = qty - ? WHERE item = ? AND qty >= ? only takes stock if there is enough. RowsAffected() == 0 means "not enough (or no such item)", and we return ErrOutOfStock wrapped with %w, so errors.Is(err, ErrOutOfStock) still matches. The CHECK (qty >= 0) constraint stays as a second line of defence.
  • In the out-of-stock test, chai (1 cup) was taken first, then samosa x6 failed: the deferred Rollback undid the chai part too, so 0 orders and chai still 10. All or nothing.
  • REFERENCES customers(id) declares a foreign key: customer_id points at a row in customers. (Here it documents the link between the tables; the solution doesn't rely on it.)
  • Every test calls Migrate twice to prove running it again is harmless, and TestMigrationsRecorded checks 3 versions were recorded.
  • The context.WithTimeout(..., 2*time.Second) context goes into every ...Context call, so a stuck query would fail the test after 2 seconds instead of hanging. t.Cleanup(cancel) releases it.

Common mistakes (and the real messages)

Mistake What you see Why Fix
two writes, no transaction CHECK constraint failed: qty >= 0 and orders saved: 2 (a ghost order) the first write was already saved BeginTx, defer tx.Rollback(), Commit
db.Exec inside a transaction database is locked (after the 500 ms busy timeout) db uses another connection, outside the transaction every statement on tx
checking the deferred Rollback's error after Commit sql: transaction has already been committed or rolled back that's sql.ErrTxDone, harmless here defer it and ignore the error
using tx after Commit sql: transaction has already been committed or rolled back a transaction is single-use begin a new transaction
forgetting tx.Commit() no error, orders saved: 0 the deferred Rollback undid everything return tx.Commit()
a query without a context requests stuck on a slow query nothing stops it QueryContext with a timeout: context deadline exceeded
many single writes in a loop A, one by one: 2.747s vs 3ms one transaction and one parse per write one transaction + a prepared statement
a query per row in a loop 50 lines, 51 queries N+1 one JOIN (or Preload)
editing an applied migration databases with different shapes it never runs again where it already ran add a new migration
removing a column old code still reads, in the same deploy errors from the old version during the deploy old and new code run side by side add first, remove in a later release

Practice

  1. Write a helper withTx(ctx, db, fn func(tx *sql.Tx) error) error that commits if fn returns nil and rolls back otherwise, and use it to rewrite placeOrder.
  2. In the TXBUG program, what would you change so both inserts are saved? What does orders saved print then?
  3. Your list page runs SELECT * FROM orders, then for each order SELECT name FROM customers WHERE id = ?. There are 200 orders. How many queries? Write the one-query version.
  4. A teammate edited 002_add_phone to also add an email column, because "it's the same feature". What goes wrong on production, and what should they do?
  5. Which isolation level would you pick for "move ₹100 from wallet A to wallet B", and what must your code then be ready for?
  6. Your handler calls db.QueryRow(...) (no context). The client disconnects after 1 second, but the query runs for 30. What changes would stop the query when the client leaves?
Solutions

1. main.go:

package main

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

    _ "github.com/mattn/go-sqlite3"
)

// withTx runs fn inside a transaction: Commit if fn returns nil, Rollback otherwise.
func withTx(ctx context.Context, db *sql.DB, fn func(tx *sql.Tx) error) error {
    tx, err := db.BeginTx(ctx, nil)
    if err != nil {
        return err
    }
    defer tx.Rollback()
    if err := fn(tx); err != nil {
        return err
    }
    return tx.Commit()
}

func placeOrder(ctx context.Context, db *sql.DB, item string, n int) error {
    return withTx(ctx, db, func(tx *sql.Tx) error {
        if _, err := tx.ExecContext(ctx, "INSERT INTO orders (item, qty) VALUES (?, ?)", item, n); err != nil {
            return err
        }
        _, err := tx.ExecContext(ctx, "UPDATE stock SET qty = qty - ? WHERE item = ?", n, item)
        return err
    })
}

func main() {
    ctx := context.Background()
    db, _ := sql.Open("sqlite3", "shop.db")
    defer db.Close()
    db.Exec(`CREATE TABLE stock (item TEXT PRIMARY KEY, qty INTEGER CHECK (qty >= 0));
             CREATE TABLE orders (id INTEGER PRIMARY KEY, item TEXT, qty INTEGER);
             INSERT INTO stock VALUES ('samosa', 5);`)

    fmt.Println("order 3 samosas:", placeOrder(ctx, db, "samosa", 3))
    fmt.Println("order 4 samosas:", placeOrder(ctx, db, "samosa", 4))
    var orders, left int
    db.QueryRow("SELECT COUNT(*) FROM orders").Scan(&orders)
    db.QueryRow("SELECT qty FROM stock WHERE item = 'samosa'").Scan(&left)
    fmt.Println("orders saved:", orders, "| samosas left:", left)
}
order 3 samosas: <nil>
order 4 samosas: CHECK constraint failed: qty >= 0
orders saved: 1 | samosas left: 2

Same result as the TX program: the helper holds the Begin / defer Rollback / Commit pattern once, so no function can forget the Commit or use db by accident (it only gets tx).

2. Use tx.ExecContext instead of db.ExecContext for the vada pav insert. The real run above printed tx.Exec inside the transaction: <nil>, commit: <nil>, orders saved: 2.

3. 1 + 200 = 201 queries (N+1). One query:

SELECT o.id, c.name FROM orders o JOIN customers c ON c.id = o.customer_id

The NPLUS1 run shows the same thing with 50 orders: 51 queries vs 1.

4. Every database that already ran 002 (production, teammates' laptops) will never run it again, so they never get email, while new databases do: the shapes differ, and code using email fails there. Undo the edit and add 003_add_email instead.

5. Serializable (the strictest). The database may then abort one of two conflicting transactions with an error, so the code must be ready to retry the whole transaction (a withTx-style helper is a good place for a retry loop).

6. Use db.QueryRowContext(r.Context(), ...) (the request's context, which is cancelled when the client goes away), ideally with context.WithTimeout(r.Context(), ...) for a hard limit. The TIMEOUT run shows the query stopped with context deadline exceeded.

Quick quiz

1. The insert works, the update fails, and we return the error. What happens to the inserted order? (From the video.)

tx, _ := db.BeginTx(ctx, nil)
defer tx.Rollback()
tx.ExecContext(ctx, insertOrder)     // works
_, err := tx.ExecContext(ctx, updateStock) // fails
if err != nil {
    return err
}
return tx.Commit()

A. The order stays saved · B. The order is rolled back too · C. Commit still runs · D. It panics

Answer

B. The function returns the error before Commit, so the deferred tx.Rollback() runs and undoes every statement in the transaction, including the insert. All or nothing.

2. Inside an open SQLite transaction that has already written, you call db.ExecContext(...) with _busy_timeout=500. What happens?

Answer

After about 500 ms it fails with database is locked: db used another connection, outside the transaction, and the transaction held the write lock.

3. What does tx.Rollback() return after a successful tx.Commit()?

Answer

sql.ErrTxDone: sql: transaction has already been committed or rolled back. That's why the deferred Rollback is harmless after Commit.

4. A list of 50 orders loads each customer's name with its own query. How many queries in total?

Answer

51 (1 for the list + 50). A JOIN does it in 1.

Interview questions

  1. What is a transaction, and what does ACID mean? A group of statements treated as one unit, committed together or rolled back together. Atomic (all or nothing), Consistent (constraints hold), Isolated (others don't see half-done work), Durable (committed data survives crashes).
  2. Show the standard transaction pattern in Go. tx, err := db.BeginTx(ctx, nil); check err; defer tx.Rollback(); every statement on tx; return tx.Commit().
  3. Why is defer tx.Rollback() safe after Commit? After Commit it only returns sql.ErrTxDone, which the deferred call ignores; on every early return it undoes the work.
  4. What happens if you use db instead of tx inside a transaction? The statement runs on another pool connection, outside the transaction: it isn't rolled back with it, can't see its uncommitted rows, and can block on its locks (database is locked in SQLite).
  5. Explain dirty, non-repeatable and phantom reads, and the four isolation levels. Reading uncommitted data; a row changing between two reads; new rows matching a repeated query. Read Uncommitted, Read Committed (PostgreSQL default), Repeatable Read (MySQL InnoDB default), Serializable. Stricter levels mean more waiting and retries.
  6. How do you set an isolation level in Go? db.BeginTx(ctx, &sql.TxOptions{Isolation: sql.LevelSerializable, ReadOnly: true}); an unsupported level returns an error.
  7. How do you put a timeout on a query? ctx, cancel := context.WithTimeout(parent, d), defer cancel(), then QueryContext/ExecContext/QueryRowContext with ctx; check errors.Is(err, context.DeadlineExceeded).
  8. When do prepared statements help, and do you need them against SQL injection? For the same statement executed many times (parse once, run many); combined with one transaction for bulk writes. Placeholders alone already prevent injection.
  9. What is the N+1 queries problem and how do you fix it? One query for a list plus one per item; fix with a JOIN, an IN (...) query, or the ORM's eager loading (Preload). Detect it by counting or logging queries.
  10. What are schema migrations, and what are the rules? Numbered, tracked schema changes applied the same way everywhere (goose, golang-migrate); never edit an applied one; run at deploy; make changes compatible with old and new code running together.
  11. What is the repository pattern and why use it? One type (behind an interface) that owns all SQL for one kind of data; the app depends on the interface, SQL lives in one place, database errors are mapped to domain errors, and implementations can be swapped for tests.
  12. How do you test database code? Against a real database: a temp-file SQLite in t.TempDir(), or a real PostgreSQL in a container via testcontainers.
  13. pgx vs sqlc vs GORM? pgx is a pure Go PostgreSQL driver and toolkit (usable via database/sql or its own API); sqlc generates type-safe Go from your SQL; GORM is an ORM that writes the SQL for you (fast to start, hidden SQL, watch for N+1).

Glossary

Term Meaning Example
Production the real running app that real customers use Raju's live order API
Database a separate program or file that stores data safely and answers questions shop.db
SQL the language for creating tables and querying rows SELECT qty FROM stock
Table / row / column a sheet with fixed columns / one record / one field of every record orders, order 1, qty
Schema the shape of a database: its tables, columns and constraints orders(id, item, qty)
Constraint a rule the database itself enforces NOT NULL, CHECK
CHECK a custom constraint on a column CHECK (qty >= 0)
INSERT / UPDATE add rows / change rows UPDATE stock SET qty = qty - 4
Ghost order an order saved even though its stock change failed orders saved: 2
Transaction a group of statements treated as one unit: all or nothing insert order + update stock
BEGIN / COMMIT / ROLLBACK start a transaction / make it permanent / undo it tx.Commit()
ACID Atomic, Consistent, Isolated, Durable: what transactions promise a committed order survives a crash
Atomic all steps happen, or none the failed order vanished
Consistent the rules always hold stock never below 0
Isolated others don't see your half-done work cashier 2 can't see order 4 mid-way
Durable committed data survives crashes still there after a restart
db.BeginTx starts a transaction with a context and options tx, err := db.BeginTx(ctx, nil)
db.Begin starts a transaction without a context tx, _ := db.Begin()
*sql.Tx an in-progress transaction; run statements on it tx.ExecContext(ctx, ...)
tx.Commit makes the transaction's changes permanent return tx.Commit()
tx.Rollback undoes everything since Begin defer tx.Rollback()
defer runs a call when the function returns, on any path defer tx.Rollback()
Safety net the deferred Rollback that undoes work on early returns returns before Commit
sql.ErrTxDone error from any use of a finished transaction sql: transaction has already been committed or rolled back
db-instead-of-tx bug running a statement on db inside a transaction database is locked
Connection pool the set of reusable connections that *sql.DB manages db borrows one per query
Connection an open line between your program and the database a transaction keeps one
Lock a "do not touch, I'm writing" flag the database puts on data SQLite's write lock
database is locked SQLite's error when a lock wasn't released in time after 500 ms
Busy timeout how long SQLite waits for a lock before giving up shop.db?_busy_timeout=500
DSN the driver-specific "which database" string, with options file:shop.db?_busy_timeout=1000
Concurrent happening at the same time two cashiers selling at once
Isolation level which concurrency surprises a transaction is protected from Read Committed
Dirty read reading data another transaction hasn't committed a number later erased
Non-repeatable read the same row gives a different value on a second read stock 5, then 2
Phantom read new rows match a repeated query a new order appears
Read Uncommitted weakest level; dirty reads possible behaves like Read Committed in PostgreSQL
Read Committed only committed data is seen PostgreSQL's default
Repeatable Read rows you've read don't change under you MySQL InnoDB's default
Serializable strictest: as if transactions ran one at a time money transfers
InnoDB MySQL's main storage engine default Repeatable Read
Retry running a failed transaction again from the start after a serialization error
sql.TxOptions options for BeginTx: isolation level and read-only &sql.TxOptions{ReadOnly: true}
sql.LevelSerializable the Go constant for Serializable Isolation: sql.LevelSerializable
Read-only transaction a transaction that will only read ReadOnly: true
Context carries a deadline and cancellation through calls ctx
context.WithTimeout a context that expires after a duration 200*time.Millisecond
cancel releases a context's resources; always defer it defer cancel()
Deadline the moment a context expires 200 ms after start
context deadline exceeded the error when the deadline passed context.DeadlineExceeded
QueryContext / QueryRowContext / ExecContext the database methods that obey a context db.QueryRowContext(ctx, q)
WITH RECURSIVE SQL that builds rows step by step; used here as a slow query counting to 100 million
COUNT(*) SQL: number of rows SELECT COUNT(*) FROM orders
503 HTTP status "Service Unavailable, try again later" answer on a timeout
Parse / plan the database reading SQL / deciding how to run it done once for a prepared statement
Prepared statement SQL parsed once, executed many times with new values stmt, _ := tx.Prepare(q)
*sql.Stmt a prepared statement in Go stmt.Exec("chai", 2)
stmt.Close releases a prepared statement defer stmt.Close()
Placeholder marks where a value goes; the value travels separately ?, $1
SQL injection user input executed as SQL; placeholders prevent it episode 66
Hot loop code that runs many times, where speed matters 2000 inserts
N+1 queries 1 query for a list + 1 per item 51 queries for 50 rows
JOIN SQL that matches rows of two tables and returns them together orders o JOIN customers c ON c.id = o.customer_id
Alias a short name for a table in a query o, c
IN (...) SQL: matches any value in a list WHERE id IN (1, 2, 3)
Godown a store room or warehouse 50 trips for 50 names
Migration one numbered change to the schema 2_add_phone
schema_migrations table recording which migrations ran on this database version 2
Up / down apply a migration / undo it 2_add_phone.up.sql
ALTER TABLE SQL that changes an existing table ALTER TABLE orders ADD COLUMN phone TEXT
CREATE TABLE IF NOT EXISTS create a table only if missing the tracking table
MAX / COALESCE largest value / first non-NULL value COALESCE(MAX(version), 0)
DROP TABLE IF EXISTS delete a table if it exists fresh() in the PREP program
goose a migration tool: CLI and library -- +goose Up
golang-migrate a migration tool: CLI and library, up/down file pairs 2_add_phone.down.sql
CLI a command-line program run in a terminal goose, sqlc
Library a package you import into your program database/sql
Deploy putting a new version of the app on the servers run migrations first
Repository pattern one type that owns all SQL for one kind of data OrderRepo
Interface a set of methods a type must have (episode 21) Create, Get
Implementation a concrete type that satisfies an interface SQLiteRepo
ErrNotFound the repository's own "not found" error mapped from sql.ErrNoRows
sql.ErrNoRows QueryRow found no row order 99
LastInsertId the id the database gave the new row o.ID
Fake a simple in-memory stand-in for a real dependency a map-based repo
go test -v runs tests and prints each test's name and result --- PASS
t.TempDir a fresh folder for one test, deleted afterwards test.db inside it
t.Cleanup registers a function to run when the test ends db.Close()
t.Logf prints a line in the test output (shown with -v) checkout error: ...
Integration test a test of your code with a real outside part repo + real SQLite
Docker / container a tool / an isolated package that runs a program with everything it needs a PostgreSQL container
testcontainers a library that starts and removes containers for tests a real PostgreSQL per test run
PostgreSQL / MySQL / SQLite database server / database server / database in one file $1 / ? / ?
pgx a pure Go driver and toolkit for PostgreSQL pgxpool
Pure Go needs no C code (no cgo) pgx
pgxpool pgx's own connection pool for the native API
COPY PostgreSQL's fast bulk loading loading a big file
LISTEN / NOTIFY PostgreSQL messages between connections "new order" events
sqlc generates type-safe Go code from SQL -- name: GetOrder :one
Code generation a tool writing Go code for you the GetOrder function sqlc writes
Type-safe wrong types are caught by the compiler GetOrder(ctx, id int64)
ORM object-relational mapping: structs and methods instead of SQL GORM
GORM a popular Go ORM db.First(&o, 1)
Associations related structs loaded by an ORM an order's items
Hooks functions an ORM runs before or after saving check an order before it is saved
Preload GORM: load related rows in one extra query db.Preload("Items")
Auto migrations the ORM changing tables to match structs GORM feature
CRUD Create, Read, Update, Delete admin screens
Foreign key a column pointing at a row of another table REFERENCES customers(id)
ErrOutOfStock the mini project's own error samosa x6: out of stock
%w wraps an error so errors.Is still finds it fmt.Errorf("%s: %w", name, ErrOutOfStock)

Summary

  • Two related writes need a transaction: BeginTx, defer tx.Rollback(), every statement on tx (never db), return tx.Commit(). All or nothing (ACID).
  • db inside a transaction uses another connection: database is locked in SQLite, untracked work elsewhere. Rollback after Commit returns the harmless sql.ErrTxDone.
  • Isolation levels trade safety for speed: Read Committed (PostgreSQL default), Repeatable Read (MySQL InnoDB default), Serializable (strictest, may need retries); choose with sql.TxOptions.
  • Pass the request's context with a timeout to QueryContext/ExecContext: a slow query stops with context deadline exceeded.
  • Bulk writes: one transaction + a prepared statement (milliseconds instead of seconds in our run).
  • A query per row is N+1: use a JOIN (or Preload) and count your queries.
  • Change the schema only with numbered migrations (goose, golang-migrate), never edit an applied one, keep old and new code compatible.
  • Keep SQL in a repository behind an interface and test it on a real database (temp SQLite, or testcontainers for PostgreSQL). Tools: pgx (PostgreSQL driver), sqlc (Go from SQL), GORM (ORM; watch the SQL).

Next episode: gRPC and Protocol Buffers: .proto files, protoc, unary and streaming RPCs, status codes, deadlines, metadata, interceptors, and gRPC vs REST.