database/sql — Go Standard Database Interface

Go's database/sql package provides a standard interface for working with relational databases. You're not tied to a specific DB engine, and by simply switching drivers, you can use various databases like MySQL, PostgreSQL, and SQLite with the same code.

---

database/sql Package Overview

database/sql is a general-purpose interface for database connections, queries, and transactions. Actual communication with the DB is handled by a driver, which is registered via _ import.

sql.DB (connection pool)
   ├── sql.Conn (single connection)
   ├── sql.Tx (transaction)
   └── sql.Stmt (Prepared Statement)

Major Drivers

DB	Driver Package
PostgreSQL	github.com/lib/pq or github.com/jackc/pgx
MySQL	github.com/go-sql-driver/mysql
SQLite	github.com/mattn/go-sqlite3

---

Environment Setup and Driver Registration

# PostgreSQL driver installation
go get github.com/lib/pq

# MySQL driver installation
go get github.com/go-sql-driver/mysql

# SQLite driver installation (requires CGO)
go get github.com/mattn/go-sqlite3

package main

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

    _ "github.com/lib/pq" // PostgreSQL driver registration (side effects only)
)

func main() {
    // DSN (Data Source Name) format
    // "host=localhost port=5432 user=myuser password=mypass dbname=mydb sslmode=disable"
    dsn := "host=localhost port=5432 user=myuser password=mypass dbname=mydb sslmode=disable"

    db, err := sql.Open("postgres", dsn)
    if err != nil {
        log.Fatal("Driver parsing failed:", err)
    }
    defer db.Close()

    // sql.Open does not establish a connection immediately. Verify connection with Ping
    if err := db.Ping(); err != nil {
        log.Fatal("DB connection failed:", err)
    }
    fmt.Println("DB connection successful!")
}

sql.Open() does not open a connection immediately. An actual connection is established when you call the first query or Ping().

---

Connection Pool Configuration

sql.DB internally manages a connection pool. Proper configuration has a major impact on performance.

func setupDB(dsn string) (*sql.DB, error) {
    db, err := sql.Open("postgres", dsn)
    if err != nil {
        return nil, fmt.Errorf("driver parsing: %w", err)
    }

    // Maximum number of open connections (default: 0 = unlimited)
    db.SetMaxOpenConns(25)

    // Maximum number of idle connections (default: 2)
    db.SetMaxIdleConns(25)

    // Maximum lifetime of a connection (default: 0 = unlimited)
    db.SetConnMaxLifetime(5 * time.Minute)

    // Maximum idle time before connection is closed
    db.SetConnMaxIdleTime(10 * time.Minute)

    if err := db.Ping(); err != nil {
        return nil, fmt.Errorf("DB connection: %w", err)
    }

    return db, nil
}

---

Table Creation Example

We'll create a users table for practice.

CREATE TABLE users (
    id         SERIAL PRIMARY KEY,
    name       VARCHAR(100) NOT NULL,
    email      VARCHAR(150) UNIQUE NOT NULL,
    age        INT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

---

CRUD Basic Examples

Create — Data Insertion

package main

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

type User struct {
    ID        int
    Name      string
    Email     string
    Age       int
    CreatedAt time.Time
}

// ExecContext: for queries with no result rows (INSERT, UPDATE, DELETE)
func createUser(ctx context.Context, db *sql.DB, name, email string, age int) (int64, error) {
    query := `INSERT INTO users (name, email, age) VALUES ($1, $2, $3) RETURNING id`

    var id int64
    err := db.QueryRowContext(ctx, query, name, email, age).Scan(&id)
    if err != nil {
        return 0, fmt.Errorf("create user failed: %w", err)
    }
    return id, nil
}

// For MySQL, use LastInsertId
func createUserMySQL(ctx context.Context, db *sql.DB, name, email string, age int) (int64, error) {
    query := `INSERT INTO users (name, email, age) VALUES (?, ?, ?)`

    result, err := db.ExecContext(ctx, query, name, email, age)
    if err != nil {
        return 0, fmt.Errorf("create user failed: %w", err)
    }
    return result.LastInsertId()
}

Read — Single Row Query

func getUserByID(ctx context.Context, db *sql.DB, id int) (*User, error) {
    query := `SELECT id, name, email, age, created_at FROM users WHERE id = $1`

    user := &User{}
    err := db.QueryRowContext(ctx, query, id).Scan(
        &user.ID,
        &user.Name,
        &user.Email,
        &user.Age,
        &user.CreatedAt,
    )
    if err == sql.ErrNoRows {
        return nil, nil // No record (not an error)
    }
    if err != nil {
        return nil, fmt.Errorf("query user failed: %w", err)
    }
    return user, nil
}

Read — Multiple Rows Query

func getAllUsers(ctx context.Context, db *sql.DB) ([]User, error) {
    query := `SELECT id, name, email, age, created_at FROM users ORDER BY id`

    rows, err := db.QueryContext(ctx, query)
    if err != nil {
        return nil, fmt.Errorf("query all users failed: %w", err)
    }
    defer rows.Close() // Must close to return connection to pool

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

    // Must check for errors after rows.Next() loop
    if err := rows.Err(); err != nil {
        return nil, fmt.Errorf("row iteration error: %w", err)
    }
    return users, nil
}

Update — Data Modification

func updateUserAge(ctx context.Context, db *sql.DB, id, age int) (int64, error) {
    query := `UPDATE users SET age = $1 WHERE id = $2`

    result, err := db.ExecContext(ctx, query, age, id)
    if err != nil {
        return 0, fmt.Errorf("update age failed: %w", err)
    }

    affected, _ := result.RowsAffected()
    return affected, nil
}

Delete — Data Deletion

func deleteUser(ctx context.Context, db *sql.DB, id int) error {
    query := `DELETE FROM users WHERE id = $1`

    result, err := db.ExecContext(ctx, query, id)
    if err != nil {
        return fmt.Errorf("delete user failed: %w", err)
    }

    affected, _ := result.RowsAffected()
    if affected == 0 {
        return fmt.Errorf("user ID %d not found", id)
    }
    return nil
}

---

Prepared Statement

For frequently executed queries, use Prepared Statement to pre-compile for better performance and security.

func bulkInsertUsers(ctx context.Context, db *sql.DB, users []User) error {
    // Parse the query only once and reuse
    stmt, err := db.PrepareContext(ctx, `INSERT INTO users (name, email, age) VALUES ($1, $2, $3)`)
    if err != nil {
        return fmt.Errorf("prepared statement creation failed: %w", err)
    }
    defer stmt.Close()

    for _, u := range users {
        if _, err := stmt.ExecContext(ctx, u.Name, u.Email, u.Age); err != nil {
            return fmt.Errorf("insert user %s failed: %w", u.Name, err)
        }
    }
    return nil
}

---

Transactions

Use transactions when you need to execute multiple queries atomically.

func transferPoints(ctx context.Context, db *sql.DB, fromID, toID, points int) error {
    // Begin transaction
    tx, err := db.BeginTx(ctx, &sql.TxOptions{
        Isolation: sql.LevelSerializable, // Set isolation level
        ReadOnly:  false,
    })
    if err != nil {
        return fmt.Errorf("begin transaction failed: %w", err)
    }
    // defer ensures rollback (Commit after this will error, which is ignored)
    defer tx.Rollback()

    // 1. Deduct points from source account
    _, err = tx.ExecContext(ctx,
        `UPDATE accounts SET points = points - $1 WHERE id = $2`,
        points, fromID,
    )
    if err != nil {
        return fmt.Errorf("deduct points failed: %w", err)
    }

    // 2. Add points to destination account
    _, err = tx.ExecContext(ctx,
        `UPDATE accounts SET points = points + $1 WHERE id = $2`,
        points, toID,
    )
    if err != nil {
        return fmt.Errorf("add points failed: %w", err)
    }

    // Commit if both queries succeed
    if err := tx.Commit(); err != nil {
        return fmt.Errorf("commit transaction failed: %w", err)
    }
    return nil
}

---

NULL Handling — sql.NullString, sql.NullInt64

Use sql.Null* types when DB columns allow NULL.

type Product struct {
    ID          int
    Name        string
    Description sql.NullString  // Nullable column
    Price       sql.NullFloat64
}

func getProduct(ctx context.Context, db *sql.DB, id int) (*Product, error) {
    query := `SELECT id, name, description, price FROM products WHERE id = $1`

    p := &Product{}
    err := db.QueryRowContext(ctx, query, id).Scan(
        &p.ID, &p.Name, &p.Description, &p.Price,
    )
    if err != nil {
        return nil, err
    }

    // Check for NULL
    if p.Description.Valid {
        fmt.Println("Description:", p.Description.String)
    } else {
        fmt.Println("No description")
    }
    return p, nil
}

---

Complete Practical Example — Repository Pattern

package userrepo

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

type User struct {
    ID        int
    Name      string
    Email     string
    Age       int
    CreatedAt time.Time
}

type UserRepository struct {
    db *sql.DB
}

func NewUserRepository(db *sql.DB) *UserRepository {
    return &UserRepository{db: db}
}

func (r *UserRepository) Create(ctx context.Context, name, email string, age int) (*User, error) {
    query := `INSERT INTO users (name, email, age) VALUES ($1, $2, $3)
              RETURNING id, name, email, age, created_at`

    u := &User{}
    err := r.db.QueryRowContext(ctx, query, name, email, age).
        Scan(&u.ID, &u.Name, &u.Email, &u.Age, &u.CreatedAt)
    if err != nil {
        return nil, fmt.Errorf("create user: %w", err)
    }
    return u, nil
}

func (r *UserRepository) FindByID(ctx context.Context, id int) (*User, error) {
    query := `SELECT id, name, email, age, created_at FROM users WHERE id = $1`

    u := &User{}
    err := r.db.QueryRowContext(ctx, query, id).
        Scan(&u.ID, &u.Name, &u.Email, &u.Age, &u.CreatedAt)
    if err == sql.ErrNoRows {
        return nil, nil
    }
    if err != nil {
        return nil, fmt.Errorf("find user by id: %w", err)
    }
    return u, nil
}

func (r *UserRepository) FindAll(ctx context.Context) ([]User, error) {
    query := `SELECT id, name, email, age, created_at FROM users ORDER BY id`

    rows, err := r.db.QueryContext(ctx, query)
    if err != nil {
        return nil, fmt.Errorf("find all users: %w", err)
    }
    defer rows.Close()

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

func (r *UserRepository) Update(ctx context.Context, id int, name string, age int) error {
    query := `UPDATE users SET name = $1, age = $2 WHERE id = $3`

    result, err := r.db.ExecContext(ctx, query, name, age, id)
    if err != nil {
        return fmt.Errorf("update user: %w", err)
    }
    affected, _ := result.RowsAffected()
    if affected == 0 {
        return fmt.Errorf("user %d not found", id)
    }
    return nil
}

func (r *UserRepository) Delete(ctx context.Context, id int) error {
    query := `DELETE FROM users WHERE id = $1`

    result, err := r.db.ExecContext(ctx, query, id)
    if err != nil {
        return fmt.Errorf("delete user: %w", err)
    }
    affected, _ := result.RowsAffected()
    if affected == 0 {
        return fmt.Errorf("user %d not found", id)
    }
    return nil
}

---

Key Summary

Function	Purpose
db.QueryRowContext	Single row returning query
db.QueryContext	Multiple row returning query
db.ExecContext	Query with no result rows (INSERT/UPDATE/DELETE)
db.PrepareContext	Pre-compile reusable query
db.BeginTx	Begin transaction
sql.ErrNoRows	Sentinel error for no results

• rows.Close() should always be called with defer
• rows.Err() must be checked after the loop completes
• Use parameter binding ($1, ?) to prevent SQL Injection