Database Access

database/sql

Go's database/sql package provides a generic interface for SQL databases. It handles connection pooling, prepared statements, and transactions. You pair it with a driver for your specific database.

go get github.com/lib/pq          # PostgreSQL
go get github.com/go-sql-driver/mysql  # MySQL
go get modernc.org/sqlite          # SQLite (pure Go)

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

db, err := sql.Open("postgres", "postgres://user:pass@localhost/mydb?sslmode=disable")
if err != nil {
    log.Fatal(err)
}
defer db.Close()

// Verify connection
if err := db.Ping(); err != nil {
    log.Fatal(err)
}

Queries

// Single row
var name string
var age int
err := db.QueryRow("SELECT name, age FROM users WHERE id = $1", 42).Scan(&name, &age)

// Multiple rows
rows, err := db.Query("SELECT name, age FROM users WHERE age > $1", 18)
if err != nil { log.Fatal(err) }
defer rows.Close()

for rows.Next() {
    var name string
    var age int
    if err := rows.Scan(&name, &age); err != nil { log.Fatal(err) }
    fmt.Printf("%s: %d\n", name, age)
}

// Insert/Update/Delete
result, err := db.Exec("INSERT INTO users (name, age) VALUES ($1, $2)", "Alice", 30)
id, _ := result.LastInsertId()
affected, _ := result.RowsAffected()

Practice Exercises