1. go
  2. /database

Working with Databases in Go

Go provides excellent support for database operations through its database/sql package and various third-party libraries. This guide covers everything you need to know about working with databases in Go.

Database Fundamentals

Basic Database Connection

package main

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

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

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

CRUD Operations

Basic CRUD operations using database/sql:

// Create
func createUser(db *sql.DB, name string, age int) error {
    _, err := db.Exec("INSERT INTO users (name, age) VALUES ($1, $2)", name, age)
    return err
}

// Read
func getUser(db *sql.DB, id int) (*User, error) {
    var user User
    err := db.QueryRow("SELECT id, name, age FROM users WHERE id = $1", id).
        Scan(&user.ID, &user.Name, &user.Age)
    if err != nil {
        return nil, err
    }
    return &user, nil
}

// Update
func updateUser(db *sql.DB, id int, name string) error {
    _, err := db.Exec("UPDATE users SET name = $1 WHERE id = $2", name, id)
    return err
}

// Delete
func deleteUser(db *sql.DB, id int) error {
    _, err := db.Exec("DELETE FROM users WHERE id = $1", id)
    return err
}

Connection Management

Connection Pooling

func setupDB() (*sql.DB, error) {
    db, err := sql.Open("postgres", "postgres://localhost/dbname?sslmode=disable")
    if err != nil {
        return nil, err
    }
    
    // Set maximum number of open connections
    db.SetMaxOpenConns(25)
    
    // Set maximum number of idle connections
    db.SetMaxIdleConns(25)
    
    // Set maximum lifetime of connections
    db.SetConnMaxLifetime(5 * time.Minute)
    
    return db, nil
}

Connection Monitoring

func monitorDBStats(db *sql.DB) {
    ticker := time.NewTicker(time.Minute)
    for range ticker.C {
        stats := db.Stats()
        log.Printf(
            "DB Stats: Open=%d, Idle=%d, InUse=%d, WaitCount=%d",
            stats.OpenConnections,
            stats.Idle,
            stats.InUse,
            stats.WaitCount,
        )
    }
}

Database Migrations

Using Migration Tools

Example with golang-migrate:

func runMigrations(dbURL string) error {
    m, err := migrate.New(
        "file://migrations",
        dbURL,
    )
    if err != nil {
        return err
    }
    
    if err := m.Up(); err != nil && err != migrate.ErrNoChange {
        return err
    }
    return nil
}

Migration Files

-- 001_create_users.up.sql
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 001_create_users.down.sql
DROP TABLE users;

Working with ORMs

GORM Example

type User struct {
    gorm.Model
    Name     string
    Email    string `gorm:"unique"`
    Age      int
    Articles []Article
}

func main() {
    db, err := gorm.Open(postgres.Open(dsn), &gorm.Config{})
    if err != nil {
        panic(err)
    }
    
    // Auto Migrate
    db.AutoMigrate(&User{}, &Article{})
    
    // Create
    user := User{Name: "John", Email: "[email protected]", Age: 30}
    db.Create(&user)
    
    // Read
    var foundUser User
    db.First(&foundUser, "email = ?", "[email protected]")
}

Transactions

Basic Transaction

func transferMoney(db *sql.DB, fromID, toID int, amount float64) error {
    tx, err := db.Begin()
    if err != nil {
        return err
    }
    defer tx.Rollback()

    // Deduct from source account
    if _, err := tx.Exec("UPDATE accounts SET balance = balance - $1 WHERE id = $2",
        amount, fromID); err != nil {
        return err
    }

    // Add to destination account
    if _, err := tx.Exec("UPDATE accounts SET balance = balance + $1 WHERE id = $2",
        amount, toID); err != nil {
        return err
    }

    return tx.Commit()
}

Transaction with Context

func transferMoneyContext(ctx context.Context, db *sql.DB, fromID, toID int, amount float64) error {
    tx, err := db.BeginTx(ctx, &sql.TxOptions{Isolation: sql.LevelSerializable})
    if err != nil {
        return err
    }
    defer tx.Rollback()

    // Transaction operations...

    return tx.Commit()
}

Query Building

Using Query Builders

Example with squirrel:

import "github.com/Masterminds/squirrel"

func getUsersByAge(db *sql.DB, minAge int) ([]User, error) {
    query := squirrel.Select("id", "name", "age").
        From("users").
        Where(squirrel.GtOrEq{"age": minAge}).
        OrderBy("name")
    
    sql, args, err := query.ToSql()
    if err != nil {
        return nil, err
    }
    
    rows, err := db.Query(sql, args...)
    // Handle results...
}

Best Practices

  1. Error Handling

    if err := row.Scan(&user.ID, &user.Name); err != nil {
    if err == sql.ErrNoRows {
        return nil, ErrUserNotFound
    }
    return nil, fmt.Errorf("scanning user: %w", err)
}

  2. Prepared Statements

    stmt, err := db.Prepare("INSERT INTO users(name, email) VALUES($1, $2)")
if err != nil {
    return err
}
defer stmt.Close()

  3. Context Usage

    ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
defer cancel()

var name string
err := db.QueryRowContext(ctx, "SELECT name FROM users WHERE id = $1", id).
    Scan(&name)

Next Steps