Notes — Module 14: Databases and Persistence¶

These are your personal study notes. Write freely and honestly. Incomplete notes are fine — they show where your understanding still needs work. Return to this file to add insights as they develop over time.

Module: [[go/14. Databases and Persistence]] Topic: [[go]] Date started: YYYY-MM-DD Status: In progress

Concept Map¶

Sketch how the concepts in this module relate to each other. Fill in the Mermaid diagram.

mindmap
  root((Databases & Persistence))
    database/sql architecture
      driver interface
      blank-import registration
      sql.Open is lazy
      db.PingContext verifies connectivity
    sql.DB is a connection pool
      long-lived single instance
      safe for concurrent use
      SetMaxOpenConns / SetMaxIdleConns
      SetConnMaxLifetime / SetConnMaxIdleTime
    querying
      QueryContext — multiple rows
      QueryRowContext — single row
      ExecContext — INSERT/UPDATE/DELETE
      always use context-aware variants
    rows iteration protocol
      rows.Next loop
      rows.Scan into struct fields
      rows.Err after loop
      defer rows.Close after err check
    NULL handling
      sql.NullString / sql.NullInt64
      pointer types (*string)
    prepared statements
      placeholder ? vs $1
      db.PrepareContext for loops
    transactions
      BeginTx with TxOptions
      defer tx.Rollback safety
      Commit
      isolation levels
    SQL injection
      never concatenate user input
      parameterized queries always
    migrations
      versioned SQL scripts
      golang-migrate / goose
    repository pattern
      UserStore interface
      SQLUserStore concrete impl
      FakeStore for tests
      inject via interface — testable
    ecosystem
      sqlx struct scanning
      sqlc code generation
      GORM ORM

Alternative: draw this on paper, photo it, and link the image here.

Key Insights¶

The "aha moments" — the things that, once understood, made the rest clear. Be specific: "I finally understood X because Y" is more useful than "X makes sense".

1. sql.DB is a pool, not a connection: I finally understood this when I realized that db.QueryContext and db.ExecContext called from different goroutines simultaneously each get their own connection from the pool — there's no serialization. This is why it's safe for concurrent use and why you must NOT create a new sql.DB per request.
2. defer tx.Rollback() after Commit is a no-op: The safety idiom clicked when I realized: after tx.Commit() succeeds, calling tx.Rollback() does nothing — the database ignores it. So the defer is free to always be there. It only fires on the "something went wrong" paths.
3. Add insights as you discover them

My Understanding¶

Explain the core concepts in your own words, as if teaching them to someone else. If you can't explain it simply, you don't understand it well enough yet.

The database/sql driver model¶

Your explanation here

What I'm still unsure about: (e.g., what exactly happens inside sql.Register — is there a global map?)

sql.DB as a connection pool¶

Your explanation here

What I'm still unsure about: (e.g., when does the pool actually create a new connection vs. reuse an idle one?)

The rows iteration protocol¶

Your explanation here

What I'm still unsure about: (e.g., what exactly does rows.Close() release — just the row cursor, or the connection back to the pool?)

Transactions¶

Your explanation here

What I'm still unsure about: (e.g., what does LevelSerializable actually prevent that LevelReadCommitted does not?)

The repository pattern¶

Your explanation here

Connections to Other Topics¶

How does this module connect to things you already know?

Questions That Arose¶

Log questions as they appear. Don't stop to answer them now — just capture them. Then move the serious ones to QUESTIONS.md.

• What happens to a transaction if the context is cancelled while a query inside the transaction is running? → added to QUESTIONS.md as Q001
• Does defer rows.Close() return the underlying connection to the pool, or just close the cursor? → added to QUESTIONS.md as Q002
• In PostgreSQL, does LastInsertId() work? (I remember reading it doesn't — you use RETURNING id instead.) → needs verification

Code Snippets Worth Remembering¶

Patterns, idioms, or examples that captured something important.

The full rows iteration protocol¶

rows, err := db.QueryContext(ctx, `SELECT id, name FROM users`)
if err != nil {
    return nil, fmt.Errorf("query: %w", err)
}
defer rows.Close()

var users []User
for rows.Next() {
    var u User
    if err := rows.Scan(&u.ID, &u.Name); err != nil {
        return nil, fmt.Errorf("scan: %w", err)
    }
    users = append(users, u)
}
if err := rows.Err(); err != nil {
    return nil, fmt.Errorf("rows: %w", err)
}
return users, nil

Why I'm saving this: Four steps — check initial err, defer Close, scan in loop, check rows.Err after. Skipping any one of them causes subtle bugs.

The transaction safety pattern¶

tx, err := db.BeginTx(ctx, nil)
if err != nil {
    return fmt.Errorf("begin: %w", err)
}
defer tx.Rollback() // no-op after Commit; safety net for all other paths

// ... do work with tx.ExecContext / tx.QueryContext ...

return tx.Commit()

Why I'm saving this: defer tx.Rollback() placed immediately after BeginTx is the canonical Go transaction pattern. Rollback after Commit is a no-op — you never need to check if you already committed.

Parameterized query (SQL injection prevention)¶

// NEVER:
query := "SELECT * FROM users WHERE name = '" + name + "'"

// ALWAYS:
rows, err := db.QueryContext(ctx, `SELECT * FROM users WHERE name = ?`, name)

Why I'm saving this: The wrong version is visually tempting (shorter, looks like standard string formatting). The habit of always using ? placeholders must be automatic.

Checking sql.ErrNoRows¶

err := db.QueryRowContext(ctx, `SELECT id FROM users WHERE email = ?`, email).Scan(&id)
if errors.Is(err, sql.ErrNoRows) {
    // user doesn't exist — not an error in the program sense, just "not found"
    return 0, ErrNotFound
}
if err != nil {
    return 0, fmt.Errorf("get by email: %w", err)
}

Why I'm saving this: sql.ErrNoRows is a normal condition (no matching row), not a bug. Must be checked before the generic error check — otherwise you'd return a "not found" as an internal error.

What Tripped Me Up¶

Mistakes I made, misconceptions I had, things that confused me more than they should have.

• sql.Open succeeds even when the DB is unreachable — I initially thought a successful sql.Open meant the connection was established. It doesn't. PingContext is the actual connectivity check.
• Forgetting rows.Err() after the loop — I initially thought the loop body's error check was sufficient. But if the query fails mid-iteration (e.g., network drop), rows.Next() silently returns false and I'd return a partial result.
• NULL scanning panic — I initially scanned a nullable TEXT column into a string field. It compiles fine but panics at runtime when the value is actually NULL. The fix is *string or sql.NullString.

Summary in My Own Words¶

Write a 3–5 sentence summary of this entire module without looking at any notes. If you can't do this, you need more study time.

Write your summary here after completing the module.

Last updated: YYYY-MM-DD