Database Programming

🗄️ Database Programming in Go: Your Data’s Best Friend

Imagine your Go program is a librarian, and the database is a massive library. Today, you’ll learn how to help the librarian find books, organize them, and keep everything safe!

🚪 Opening Database Connections

The Story: Knocking on the Library Door

Before you can read any books, you need to open the library door. In Go, opening a database connection is like getting a special key to the library.

Think of it like this: Your phone needs Wi-Fi to connect to the internet. Your Go program needs a connection to talk to the database!

import (
    "database/sql"
    _ "github.com/lib/pq" // PostgreSQL driver
)

func main() {
    // This is like getting your library card!
    db, err := sql.Open("postgres",
        "host=localhost user=me dbname=mydb")
    if err != nil {
        panic("Can't open the door!")
    }
    defer db.Close() // Always close when done!

    // Check if the door really opened
    err = db.Ping()
    if err != nil {
        panic("Door opened but nobody home!")
    }
}

🔑 Key Points

• sql.Open() doesn’t actually connect - it just prepares the key
• db.Ping() really tests if the database is there
• Always use defer db.Close() to clean up!

🔍 Query Execution

The Story: Asking the Librarian Questions

Now that you’re inside the library, you can ask questions! “Show me all the adventure books!” or “How many books do we have?”

Two ways to ask:

1. “I want answers!” → Use Query() for multiple rows
2. “Just one answer, please!” → Use QueryRow() for one row

// Get ALL adventure books (multiple rows)
rows, err := db.Query(
    "SELECT title, author FROM books WHERE genre = $1",
    "adventure")
if err != nil {
    panic("Librarian is confused!")
}
defer rows.Close()

// Read each book one by one
for rows.Next() {
    var title, author string
    rows.Scan(&title, &author)
    fmt.Println(title, "by", author)
}

// Get just ONE book (single row)
var title string
err = db.QueryRow(
    "SELECT title FROM books WHERE id = $1",
    42).Scan(&title)

graph TD
    A["Your Question"] --> B{How many answers?}
    B -->|Many| C["db.Query"]
    B -->|One| D["db.QueryRow"]
    C --> E["Loop with rows.Next"]
    D --> F["Direct .Scan"]

🛡️ Prepared Statements

The Story: Making a Reusable Question Template

Imagine you keep asking: “Do you have a book called ___?” Instead of saying the whole question each time, you make a template!

Why use them?

• ⚡ Faster - Database remembers the question
• 🔒 Safer - Stops hackers (SQL injection)
• ♻️ Reusable - Ask many times with different values

// Create the template ONCE
stmt, err := db.Prepare(
    "SELECT price FROM products WHERE name = $1")
if err != nil {
    panic("Template broken!")
}
defer stmt.Close()

// Use it MANY times!
var price float64
stmt.QueryRow("Apple").Scan(&price)  // Ask about Apple
stmt.QueryRow("Banana").Scan(&price) // Ask about Banana
stmt.QueryRow("Orange").Scan(&price) // Ask about Orange

💡 When to Use

💰 Database Transactions

The Story: The All-or-Nothing Promise

Imagine you’re moving toys from Box A to Box B. What if you take from Box A but trip and drop everything? The toys are lost!

A transaction is a promise: Either everything works, or nothing changes.

Real example: Sending money from your account to a friend:

1. Take $50 from YOUR account
2. Add $50 to FRIEND’s account

If step 2 fails, step 1 must undo itself!

// Start the promise
tx, err := db.Begin()
if err != nil {
    panic("Can't start promise!")
}

// Try to do both things
_, err = tx.Exec(
    "UPDATE accounts SET balance = balance - 50 WHERE user = 'me'")
if err != nil {
    tx.Rollback() // UNDO everything!
    return
}

_, err = tx.Exec(
    "UPDATE accounts SET balance = balance + 50 WHERE user = 'friend'")
if err != nil {
    tx.Rollback() // UNDO everything!
    return
}

// Everything worked! Make it permanent!
tx.Commit()

graph TD
    A["Begin Transaction"] --> B["Step 1: Take money"]
    B --> C{Success?}
    C -->|No| D["Rollback - Undo all!"]
    C -->|Yes| E["Step 2: Give money"]
    E --> F{Success?}
    F -->|No| D
    F -->|Yes| G["Commit - Save all!"]

🏊 Connection Pooling

The Story: The Swimming Pool of Connections

Opening a new database connection is slow - like building a new door every time someone wants to enter!

Connection pooling is like having a swimming pool with many lanes. People share the lanes instead of building personal pools!

db, _ := sql.Open("postgres", connectionString)

// Set the pool size
db.SetMaxOpenConns(25)     // Max 25 swimmers at once
db.SetMaxIdleConns(5)      // Keep 5 lanes ready
db.SetConnMaxLifetime(     // Each lane lasts 5 minutes
    5 * time.Minute)

🏊‍♂️ Pool Settings Explained

Pro tip: Start with MaxOpenConns = 25 and adjust based on your needs!

❓ sql.Null Types

The Story: The “I Don’t Know” Answer

Sometimes a database cell is empty. It’s not zero, not blank - it’s “I don’t know” or NULL.

Problem: Go doesn’t like surprises. If you expect a number but get NULL… 💥 crash!

Solution: Use special “maybe” types!

var name sql.NullString
var age sql.NullInt64

err := db.QueryRow(
    "SELECT name, age FROM users WHERE id = $1",
    1).Scan(&name, &age)

// Check if we actually got a value
if name.Valid {
    fmt.Println("Name:", name.String)
} else {
    fmt.Println("Name: Unknown")
}

if age.Valid {
    fmt.Println("Age:", age.Int64)
} else {
    fmt.Println("Age: Not specified")
}

📦 Available Null Types

⏰ Context-aware Queries

The Story: Setting a Timer on Your Questions

Imagine asking the librarian a question, but they take forever. You don’t want to wait all day!

A context is like setting a timer: “If you don’t answer in 5 seconds, I’m leaving!”

import "context"

// Create a 5-second timer
ctx, cancel := context.WithTimeout(
    context.Background(),
    5*time.Second)
defer cancel() // Clean up the timer

// Ask with the timer running
rows, err := db.QueryContext(ctx,
    "SELECT * FROM huge_table")

if err == context.DeadlineExceeded {
    fmt.Println("Too slow! Gave up waiting.")
}

🕐 Context Types

graph TD
    A["context.Background"] --> B["Starting point"]
    B --> C["WithTimeout"]
    B --> D["WithCancel"]
    B --> E["WithDeadline"]
    C --> F["Stops after X seconds"]
    D --> G["Stop whenever you want"]
    E --> H["Stop at specific time"]

💡 When to Use Context

🎯 Quick Summary

graph TD
    A["Database Programming"] --> B["Open Connection"]
    A --> C["Query Data"]
    A --> D["Prepared Statements"]
    A --> E["Transactions"]
    A --> F["Connection Pool"]
    A --> G["Handle NULLs"]
    A --> H["Use Contexts"]

    B --> B1["sql.Open + Ping"]
    C --> C1["Query / QueryRow"]
    D --> D1["Prepare once, use many"]
    E --> E1["Begin → Commit/Rollback"]
    F --> F1["Set pool limits"]
    G --> G1["sql.NullString etc"]
    H --> H1["WithTimeout/WithCancel"]

🚀 You Did It!

You’ve learned how Go talks to databases - like a librarian’s best helper! Remember:

1. Open carefully - Always ping and close
2. Query smartly - Use the right method for your needs
3. Prepare for speed - Templates save time
4. Transact safely - All or nothing!
5. Pool your connections - Share the lanes
6. Handle NULLs - Expect the unexpected
7. Set timeouts - Don’t wait forever

Now go build something amazing! 🎉