SQLx Cached Prepared Statement for Repository Pattern

main.go

// README: https://medium.com/@rluders/prepared-statement-caching-in-go-repositories-with-sqlx-a-professional-pattern-43a1fcdb7ed8

package main

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

    _ "github.com/mattn/go-sqlite3"
    "github.com/jmoiron/sqlx"
)

type Category struct {
    ID   string `db:"id"`
    Name string `db:"name"`
}

type Repository struct {
    DB         *sqlx.DB
    statements map[string]*sqlx.NamedStmt
}

func (r *Repository) prepare(queries map[string]string) error {
    for name, query := range queries {
        stmt, err := r.DB.PrepareNamed(query)
        if err != nil {
            return fmt.Errorf("failed to prepare statement '%s': %w", name, err)
        }
        r.statements[name] = stmt
    }
    return nil
}

const (
    stmtGetCategoryByID = "GetCategoryByID"
    stmtListCategories  = "ListCategories"
)

type CategoryRepository struct {
    *Repository
}

func NewCategoryRepository(db *sqlx.DB) (*CategoryRepository, error) {
    r := &Repository{
        DB:         db,
        statements: make(map[string]*sqlx.NamedStmt),
    }
    repo := &CategoryRepository{Repository: r}

    if err := r.prepare(CategoryQueries()); err != nil {
        return nil, err
    }

    return repo, nil
}

func CategoryQueries() map[string]string {
    return map[string]string{
        stmtGetCategoryByID: `SELECT * FROM categories WHERE id = :id`,
        stmtListCategories:  `SELECT * FROM categories LIMIT :limit OFFSET :offset`,
    }
}

func (r *CategoryRepository) GetByID(categoryID string) (*Category, error) {
    params := map[string]any{"id": categoryID}

    stmt, ok := r.statements[stmtGetCategoryByID]
    if !ok {
        return nil, fmt.Errorf("prepared statement not found: %s", stmtGetCategoryByID)
    }

    var category Category
    err := stmt.Get(&category, params)
    if err != nil {
        if err == sql.ErrNoRows {
            return nil, sql.ErrNoRows
        }
        return nil, err
    }

    return &category, nil
}

func main() {
    // Open an in-memory SQLite database
    db, err := sqlx.Open("sqlite3", ":memory:")
    if err != nil {
        log.Fatal("failed to open database:", err)
    }
    defer db.Close()

    // Create the categories table
    schema := `
    CREATE TABLE categories (
        id TEXT PRIMARY KEY,
        name TEXT NOT NULL
    );`
    db.MustExec(schema)

    // Insert a sample category
    db.MustExec(`INSERT INTO categories (id, name) VALUES (?, ?)`, "cat1", "Electronics")

    // Initialize the repository
    repo, err := NewCategoryRepository(db)
    if err != nil {
        log.Fatal("failed to create repository:", err)
    }

    // Test: Fetch an existing category
    category, err := repo.GetByID("cat1")
    if err != nil {
        log.Fatal("failed to fetch category:", err)
    }
    fmt.Printf("Category found: ID=%s, Name=%s\n", category.ID, category.Name)

    // Test: Fetch a non-existing category
    category, err = repo.GetByID("cat2")
    if err != nil {
        if err == sql.ErrNoRows {
            fmt.Println("Category cat2 not found.")
        } else {
            log.Fatal("failed to fetch category:", err)
        }
    } else {
        fmt.Printf("Category found: ID=%s, Name=%s\n", category.ID, category.Name)
    }
}