MySQL #

Go doesn’t include database-specific drivers in the standard library — instead it provides database/sql as a uniform abstraction interface. All database operations (query, exec, transaction) go through database/sql, while the specific driver (MySQL, PostgreSQL, SQLite) is registered behind the scenes. This means your business code is nearly identical regardless of which database you use — only the connection string and driver differ. For MySQL, the most common and mature driver is github.com/go-sql-driver/mysql.

Installation #

go get github.com/go-sql-driver/mysql

Connecting to MySQL #

import (
    "database/sql"
    "fmt"
    "log"
    _ "github.com/go-sql-driver/mysql"  // blank import: register the driver
)

func main() {
    // DSN format: user:password@protocol(host:port)/dbname?param=value
    dsn := "root:password@tcp(localhost:3306)/onlinestore?parseTime=true&loc=Asia%2FJakarta"

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

    // sql.Open does NOT open a connection — it only validates the DSN
    // Use Ping to verify an active connection
    if err := db.Ping(); err != nil {
        log.Fatal("Ping failed:", err)
    }
    fmt.Println("Connected to MySQL!")
}

Important DSN Parameters #

parseTime=true        → scan DATETIME/TIMESTAMP columns into time.Time (REQUIRED)
loc=Asia%2FJakarta    → timezone for time interpretation
charset=utf8mb4       → support emoji and full Unicode characters
timeout=10s           → connection timeout
readTimeout=30s       → read timeout per query
writeTimeout=30s      → write timeout per query
multiStatements=true  → allow multiple statements in a single Exec

Connection Pool — Proper Configuration #

database/sql manages a connection pool automatically. Pool configuration is critical for performance:

func openDB(dsn string) (*sql.DB, error) {
    db, err := sql.Open("mysql", dsn)
    if err != nil {
        return nil, err
    }

    // Maximum number of open connections to the server
    // Adjust to match MySQL's max_connections (default 151)
    db.SetMaxOpenConns(25)

    // Idle connections kept in the pool
    // Should be equal to or smaller than MaxOpenConns
    db.SetMaxIdleConns(25)

    // Maximum connection lifetime (forces a new connection after this)
    // Useful to avoid stale connections due to firewalls/proxies
    db.SetConnMaxLifetime(5 * time.Minute)

    // Maximum time a connection may sit idle in the pool (Go 1.15+)
    db.SetConnMaxIdleTime(1 * time.Minute)

    // Verify the pool works
    if err := db.Ping(); err != nil {
        return nil, fmt.Errorf("ping database: %w", err)
    }

    return db, nil
}

Visualizing How the Go Connection Pool Works #

The database/sql package in Go manages database connections behind the scenes through a queue of open connections and idle connections:

flowchart TD
    App["Go Application (database/sql)"] -->|"Needs a Query"| Pool{"Check Connection Pool"}
    
    Pool -->|"1. Idle Connection Available?"| Idle["Use a Connection from the Pool"]
    Pool -->|"2. Pool Empty & < MaxOpenConns"| Create["Create a New MySQL Connection"]
    Pool -->|"3. Pool Empty & >= MaxOpenConns"| Wait["Queue Waiting for a Connection to Release"]

    Idle --> Query["Execute SQL Statement"]
    Create --> Query
    Wait --> Query

    Query -->|"Done (Connection Released)"| ReturnPool["Return to the Pool (Idle)"]
    ReturnPool --> Pool
Don’t close *sql.DB after every query. db is a connection pool that should be created once and used for the application’s lifetime. Call db.Close() only when the application stops (e.g. in a defer in main()).

Query — Reading Data #

QueryRowContext — One Row #

func getProductByID(ctx context.Context, db *sql.DB, id int) (*Product, error) {
    query := `
        SELECT id, name, price, stock, category, created_at
        FROM products
        WHERE id = ?
    `
    row := db.QueryRowContext(ctx, query, id)

    var p Product
    err := row.Scan(
        &p.ID,
        &p.Name,
        &p.Price,
        &p.Stock,
        &p.Category,
        &p.CreatedAt,
    )
    if err != nil {
        if errors.Is(err, sql.ErrNoRows) {
            return nil, ErrNotFound  // product not found
        }
        return nil, fmt.Errorf("scan product: %w", err)
    }
    return &p, nil
}

QueryContext — Many Rows #

func listProducts(ctx context.Context, db *sql.DB, category string) ([]*Product, error) {
    query := `
        SELECT id, name, price, stock, category
        FROM products
        WHERE category = ?
        ORDER BY name ASC
    `
    rows, err := db.QueryContext(ctx, query, category)
    if err != nil {
        return nil, fmt.Errorf("query products: %w", err)
    }
    defer rows.Close()  // REQUIRED: close rows to return the connection to the pool

    var products []*Product
    for rows.Next() {
        var p Product
        if err := rows.Scan(
            &p.ID, &p.Name, &p.Price, &p.Stock, &p.Category,
        ); err != nil {
            return nil, fmt.Errorf("scan row: %w", err)
        }
        products = append(products, &p)
    }

    // Check for errors after the iteration completes
    if err := rows.Err(); err != nil {
        return nil, fmt.Errorf("rows error: %w", err)
    }

    return products, nil
}
defer rows.Close() is mandatory. If rows aren’t closed, the connection isn’t returned to the pool and the pool can run out. Call it immediately after QueryContext — even if you haven’t iterated at all.

Exec — Writing Data #

ExecContext for INSERT, UPDATE, DELETE — it doesn’t return rows:

// INSERT
func createProduct(ctx context.Context, db *sql.DB, p *Product) (int64, error) {
    query := `
        INSERT INTO products (name, price, stock, category, created_at)
        VALUES (?, ?, ?, ?, NOW())
    `
    result, err := db.ExecContext(ctx, query,
        p.Name, p.Price, p.Stock, p.Category,
    )
    if err != nil {
        return 0, fmt.Errorf("insert product: %w", err)
    }

    // Get the newly created ID
    id, err := result.LastInsertId()
    if err != nil {
        return 0, fmt.Errorf("last insert id: %w", err)
    }
    return id, nil
}

// UPDATE
func updateProduct(ctx context.Context, db *sql.DB, p *Product) error {
    query := `
        UPDATE products
        SET name = ?, price = ?, stock = ?, category = ?
        WHERE id = ?
    `
    result, err := db.ExecContext(ctx, query,
        p.Name, p.Price, p.Stock, p.Category, p.ID,
    )
    if err != nil {
        return fmt.Errorf("update product: %w", err)
    }

    // Check whether any rows were affected
    rows, _ := result.RowsAffected()
    if rows == 0 {
        return ErrNotFound
    }
    return nil
}

// DELETE
func deleteProduct(ctx context.Context, db *sql.DB, id int) error {
    result, err := db.ExecContext(ctx,
        "DELETE FROM products WHERE id = ?", id,
    )
    if err != nil {
        return fmt.Errorf("delete product: %w", err)
    }
    rows, _ := result.RowsAffected()
    if rows == 0 {
        return ErrNotFound
    }
    return nil
}

Prepared Statements #

A prepared statement is parsed once by the server and can be executed many times with different parameters — safer than SQL injection and more efficient for repeated queries:

// Create the statement once, use it many times
func bulkUpdateStock(ctx context.Context, db *sql.DB, updates []StockUpdate) error {
    stmt, err := db.PrepareContext(ctx,
        "UPDATE products SET stock = ? WHERE id = ?",
    )
    if err != nil {
        return fmt.Errorf("prepare statement: %w", err)
    }
    defer stmt.Close()  // return the statement to the pool

    for _, u := range updates {
        if _, err := stmt.ExecContext(ctx, u.NewStock, u.ProductID); err != nil {
            return fmt.Errorf("update stock of product %d: %w", u.ProductID, err)
        }
    }
    return nil
}

Transactions #

Transactions ensure a group of operations runs atomically — either all succeed or all are rolled back:

func transferStock(ctx context.Context, db *sql.DB, fromID, toID, qty int) error {
    tx, err := db.BeginTx(ctx, nil)
    if err != nil {
        return fmt.Errorf("begin tx: %w", err)
    }
    // Deferred rollback — harmless if the commit already succeeded
    defer tx.Rollback()

    // Reduce the stock from the source product
    result, err := tx.ExecContext(ctx,
        "UPDATE products SET stock = stock - ? WHERE id = ? AND stock >= ?",
        qty, fromID, qty,
    )
    if err != nil {
        return fmt.Errorf("reduce stock: %w", err)
    }
    if rows, _ := result.RowsAffected(); rows == 0 {
        return errors.New("insufficient stock")
    }

    // Add the stock to the destination product
    if _, err := tx.ExecContext(ctx,
        "UPDATE products SET stock = stock + ? WHERE id = ?",
        qty, toID,
    ); err != nil {
        return fmt.Errorf("add stock: %w", err)
    }

    // Record the transfer log
    if _, err := tx.ExecContext(ctx,
        "INSERT INTO stock_transfers (from_id, to_id, qty, created_at) VALUES (?, ?, ?, NOW())",
        fromID, toID, qty,
    ); err != nil {
        return fmt.Errorf("record transfer: %w", err)
    }

    // Commit — if successful, the deferred Rollback has no effect
    return tx.Commit()
}

Null Values #

MySQL allows columns to be NULL. Use the sql.Null* types to handle them:

type Product struct {
    ID          int
    Name        string
    Description sql.NullString  // can be NULL
    Price       float64
    DeletedAt   sql.NullTime    // soft delete, can be NULL
    Weight      sql.NullFloat64 // can be NULL
}

// Scanning with null values
var p Product
err := row.Scan(
    &p.ID,
    &p.Name,
    &p.Description,  // auto-handles NULL
    &p.Price,
    &p.DeletedAt,
    &p.Weight,
)

// Accessing the values
if p.Description.Valid {
    fmt.Println("Description:", p.Description.String)
} else {
    fmt.Println("No description")
}

// Insert with nulls
description := sql.NullString{}  // NULL
if desc := "Gaming laptop"; desc != "" {
    description = sql.NullString{String: desc, Valid: true}
}
db.ExecContext(ctx,
    "INSERT INTO products (name, description) VALUES (?, ?)",
    "Laptop", description,
)

Batch Inserts #

For inserting many rows at once, avoid a one-by-one loop:

func batchInsertProducts(ctx context.Context, db *sql.DB, products []Product) error {
    if len(products) == 0 {
        return nil
    }

    // Build a query with many placeholders
    // INSERT INTO products (name, price, stock) VALUES (?, ?, ?), (?, ?, ?), ...
    valueStrings := make([]string, len(products))
    valueArgs := make([]interface{}, 0, len(products)*3)

    for i, p := range products {
        valueStrings[i] = "(?, ?, ?)"
        valueArgs = append(valueArgs, p.Name, p.Price, p.Stock)
    }

    query := fmt.Sprintf(
        "INSERT INTO products (name, price, stock) VALUES %s",
        strings.Join(valueStrings, ","),
    )

    _, err := db.ExecContext(ctx, query, valueArgs...)
    if err != nil {
        return fmt.Errorf("batch insert: %w", err)
    }
    return nil
}

Complete Example Program — Repository Pattern #

package main

import (
    "context"
    "database/sql"
    "errors"
    "fmt"
    "log"
    "strings"
    "time"

    _ "github.com/go-sql-driver/mysql"
)

var ErrNotFound = errors.New("data not found")

type Product struct {
    ID        int
    Name      string
    Price     float64
    Stock     int
    Category  string
    CreatedAt time.Time
}

// ProductRepository — the interface for abstraction
type ProductRepository interface {
    Create(ctx context.Context, p *Product) (int64, error)
    FindByID(ctx context.Context, id int) (*Product, error)
    FindByCategory(ctx context.Context, category string) ([]*Product, error)
    Update(ctx context.Context, p *Product) error
    Delete(ctx context.Context, id int) error
    Search(ctx context.Context, keyword string, limit int) ([]*Product, error)
}

// mysqlProductRepo — the MySQL implementation
type mysqlProductRepo struct {
    db *sql.DB
}

func NewProductRepository(db *sql.DB) ProductRepository {
    return &mysqlProductRepo{db: db}
}

func (r *mysqlProductRepo) Create(ctx context.Context, p *Product) (int64, error) {
    result, err := r.db.ExecContext(ctx, `
        INSERT INTO products (name, price, stock, category, created_at)
        VALUES (?, ?, ?, ?, NOW())
    `, p.Name, p.Price, p.Stock, p.Category)
    if err != nil {
        return 0, fmt.Errorf("create product: %w", err)
    }
    return result.LastInsertId()
}

func (r *mysqlProductRepo) FindByID(ctx context.Context, id int) (*Product, error) {
    var p Product
    err := r.db.QueryRowContext(ctx, `
        SELECT id, name, price, stock, category, created_at
        FROM products WHERE id = ?
    `, id).Scan(&p.ID, &p.Name, &p.Price, &p.Stock, &p.Category, &p.CreatedAt)

    if errors.Is(err, sql.ErrNoRows) {
        return nil, ErrNotFound
    }
    if err != nil {
        return nil, fmt.Errorf("find product %d: %w", id, err)
    }
    return &p, nil
}

func (r *mysqlProductRepo) FindByCategory(ctx context.Context, category string) ([]*Product, error) {
    rows, err := r.db.QueryContext(ctx, `
        SELECT id, name, price, stock, category, created_at
        FROM products WHERE category = ? ORDER BY name
    `, category)
    if err != nil {
        return nil, fmt.Errorf("list products: %w", err)
    }
    defer rows.Close()

    var products []*Product
    for rows.Next() {
        var p Product
        if err := rows.Scan(&p.ID, &p.Name, &p.Price, &p.Stock,
            &p.Category, &p.CreatedAt); err != nil {
            return nil, fmt.Errorf("scan product: %w", err)
        }
        products = append(products, &p)
    }
    return products, rows.Err()
}

func (r *mysqlProductRepo) Update(ctx context.Context, p *Product) error {
    res, err := r.db.ExecContext(ctx, `
        UPDATE products SET name=?, price=?, stock=?, category=?
        WHERE id=?
    `, p.Name, p.Price, p.Stock, p.Category, p.ID)
    if err != nil {
        return fmt.Errorf("update product: %w", err)
    }
    if n, _ := res.RowsAffected(); n == 0 {
        return ErrNotFound
    }
    return nil
}

func (r *mysqlProductRepo) Delete(ctx context.Context, id int) error {
    res, err := r.db.ExecContext(ctx,
        "DELETE FROM products WHERE id = ?", id)
    if err != nil {
        return fmt.Errorf("delete product: %w", err)
    }
    if n, _ := res.RowsAffected(); n == 0 {
        return ErrNotFound
    }
    return nil
}

func (r *mysqlProductRepo) Search(ctx context.Context, keyword string, limit int) ([]*Product, error) {
    rows, err := r.db.QueryContext(ctx, `
        SELECT id, name, price, stock, category, created_at
        FROM products
        WHERE name LIKE ? OR category LIKE ?
        ORDER BY name LIMIT ?
    `, "%"+keyword+"%", "%"+keyword+"%", limit)
    if err != nil {
        return nil, fmt.Errorf("search products: %w", err)
    }
    defer rows.Close()

    var products []*Product
    for rows.Next() {
        var p Product
        if err := rows.Scan(&p.ID, &p.Name, &p.Price, &p.Stock,
            &p.Category, &p.CreatedAt); err != nil {
            return nil, err
        }
        products = append(products, &p)
    }
    return products, rows.Err()
}

// DDL for table setup
const createTableSQL = `
CREATE TABLE IF NOT EXISTS products (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    name       VARCHAR(200) NOT NULL,
    price      DECIMAL(15,2) NOT NULL DEFAULT 0,
    stock      INT NOT NULL DEFAULT 0,
    category   VARCHAR(100) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_category (category),
    INDEX idx_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;`

func main() {
    dsn := "root:password@tcp(localhost:3306)/onlinestore?parseTime=true"

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

    db.SetMaxOpenConns(25)
    db.SetMaxIdleConns(25)
    db.SetConnMaxLifetime(5 * time.Minute)

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

    // Set up the table
    if _, err := db.Exec(createTableSQL); err != nil {
        log.Fatal("Create table:", err)
    }

    ctx := context.Background()
    repo := NewProductRepository(db)

    // Create products
    products := []Product{
        {Name: "Pro Laptop 14", Price: 15_000_000, Stock: 10, Category: "electronics"},
        {Name: "Wireless Mouse", Price: 350_000, Stock: 50, Category: "electronics"},
        {Name: "Mech Keyboard", Price: 1_500_000, Stock: 25, Category: "electronics"},
        {Name: "Plain T-Shirt", Price: 85_000, Stock: 100, Category: "fashion"},
        {Name: "Chino Pants", Price: 250_000, Stock: 60, Category: "fashion"},
    }

    fmt.Println("=== Inserting Products ===")
    ids := make([]int64, 0)
    for _, p := range products {
        p := p
        id, err := repo.Create(ctx, &p)
        if err != nil {
            log.Printf("Failed to insert %s: %v", p.Name, err)
            continue
        }
        ids = append(ids, id)
        fmt.Printf("  [%d] %s — Rp%.0f\n", id, p.Name, p.Price)
    }

    // Find one product
    fmt.Println("\n=== FindByID ===")
    if len(ids) > 0 {
        p, err := repo.FindByID(ctx, int(ids[0]))
        if err != nil {
            log.Println("FindByID error:", err)
        } else {
            fmt.Printf("  ID=%d, %s, Rp%.0f, Stock=%d\n",
                p.ID, p.Name, p.Price, p.Stock)
        }
    }

    // List by category
    fmt.Println("\n=== FindByCategory: electronics ===")
    electronics, _ := repo.FindByCategory(ctx, "electronics")
    for _, p := range electronics {
        fmt.Printf("  [%d] %-20s Rp%10.0f  stock=%d\n",
            p.ID, p.Name, p.Price, p.Stock)
    }

    // Update
    fmt.Println("\n=== Update ===")
    if len(ids) > 0 {
        err := repo.Update(ctx, &Product{
            ID: int(ids[0]), Name: "Pro Laptop 14 (New)",
            Price: 14_500_000, Stock: 8, Category: "electronics",
        })
        if err != nil {
            log.Println("Update error:", err)
        } else {
            fmt.Println("  Update successful")
        }
    }

    // Search
    fmt.Println("\n=== Search: 'laptop' ===")
    results, _ := repo.Search(ctx, "laptop", 10)
    for _, p := range results {
        fmt.Printf("  [%d] %s — Rp%.0f\n", p.ID, p.Name, p.Price)
    }

    // Delete
    if len(ids) > 0 {
        fmt.Println("\n=== Delete ===")
        err := repo.Delete(ctx, int(ids[len(ids)-1]))
        if err != nil {
            log.Println("Delete error:", err)
        } else {
            fmt.Printf("  Product ID %d deleted\n", ids[len(ids)-1])
        }
    }

    // Pool statistics
    stats := db.Stats()
    fmt.Printf("\n=== Pool Stats ===\n")
    fmt.Printf("  Open connections  : %d\n", stats.OpenConnections)
    fmt.Printf("  In use            : %d\n", stats.InUse)
    fmt.Printf("  Idle              : %d\n", stats.Idle)
    fmt.Printf("  Wait count        : %d\n", stats.WaitCount)

    _ = strings.Join  // suppress the import
}

Summary #

  • database/sql is the standard abstraction; the driver is imported with the blank import _ "github.com/go-sql-driver/mysql".
  • The DSN must contain parseTime=true so DATETIME/TIMESTAMP columns are automatically scanned into time.Time.
  • Connection pool: SetMaxOpenConns, SetMaxIdleConns, SetConnMaxLifetime must be configured — the defaults don’t limit connections.
  • defer rows.Close() immediately after QueryContext — otherwise the connection doesn’t return to the pool.
  • sql.ErrNoRows from QueryRow.Scan means the row wasn’t found — not a fatal error.
  • result.RowsAffected() for UPDATE/DELETE — check whether any rows were actually affected.
  • Prepared statements for repeatedly executed queries — safer and more efficient.
  • Transactions with defer tx.Rollback() — safe because Rollback after Commit is harmless.
  • sql.NullString, sql.NullTime for columns that can be NULL — check the Valid field before accessing the value.
  • Batch inserts with a single multi-value query are much faster than a loop of individual INSERTs.

← Previous: YAML   Next: MSSQL →

About | Author | Content Scope | Editorial Policy | Privacy Policy | Disclaimer | Contact