New: How to connect to mysql database from go

The `dbconnect.go` Architecture

In Go, sql.Open doesn't actually connect to the database—it initializes a Pool. This pool is a manager that sits between your code and the database, handing out "check-out" tickets for connections.

1. The Connection (The "Dial")

The DSN string defines the network path. By adding parameters like timeout=30s, you tell the driver how long to wait for the initial handshake before giving up.

2. The Pooling (The "Manager")

By setting SetMaxOpenConns, you create a hard ceiling. If you set it to 10, Go will never attempt an 11th connection. Instead, it makes the next request wait in a queue. SetConnMaxLifetime ensures that old, "stale" connections are killed off before the server (DreamHost) forcibly drops them.

3. The Context (The "Stopwatch")

Using context.WithTimeout is how you control that queue. Without a context, a query might wait forever for a free connection. With a context, you say: "I am willing to wait 5 seconds for a connection; if one doesn't become free by then, cancel the request."

The dbconnect.go file that actually runs the connection:

package dbconnect

import (
    "context"
    "database/sql"
    "log/slog"
    "time"
    "understandmydreams/i18n"

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

var oneConnection *sql.DB

// stats := db.Stats()
// 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) // High number here means your app is slow/blocked

func GetConnection(whereFrom string) (*sql.DB, error) {
    slog.Info("GetConnection: " + whereFrom)
    if oneConnection == nil {
        var err error
        oneConnection, err = createConnection()
        if err != nil {
            return nil, err
        }
    }

    // implement ping
    // err := db.PingContext(ctx)
    // if err != nil {
    //  slog.Error("Database is unreachable", "err", err)
    //  // This is where you'd trigger a retry or shut down
    // }

    return oneConnection, nil
}

// create a real connection to the database
func createConnection() (*sql.DB, error) {
    var err error
 
    // ConnectionString:      fmt.Sprintf("user:password@tcp(host:3306)/database?charset=utf8mb3&timeout=%ds&readTimeout=%ds&writeTimeout=%ds", MaxExecutionTime, MaxExecutionTime, MaxExecutionTime),    
    db, err := sql.Open("mysql", i18n.I18N.ConnectionString)
    if err != nil {
        slog.Error("20241212.01314 createConnection", "err", err)
        return nil, err
    }

    db.SetMaxOpenConns(50)                   // Set max open connections
    db.SetMaxIdleConns(10)                   // Set max idle connections
    db.SetConnMaxLifetime(time.Second * 120) // Set the max lifetime of a connection

    // Create a 5-second deadline for this specific operation
    ctx, cancel := context.WithTimeout(context.Background(), time.Duration(i18n.MaxExecutionTime)*time.Second)
    defer cancel()

    // Setting session-specific parameters
    _, err = db.ExecContext(ctx, "SET SESSION max_execution_time=?", i18n.MaxExecutionTime*1000) // milliseconds 30*1000
    if err != nil {
        slog.Error("20241212.01641 set max_execution_time", "error", err)
        return nil, err
    }

    _, err = db.ExecContext(ctx, "SET SESSION wait_timeout=?", i18n.MaxExecutionTime) // seconds
    if err != nil {
        slog.Error("20241212.01641 set wait_timeout", "error", err)
        return nil, err
    }

    _, err = db.ExecContext(ctx, "SET SESSION interactive_timeout=?", i18n.MaxExecutionTime) // seconds
    if err != nil {
        slog.Error("20241212.01642 set interactive_timeout", "error", err)
        return nil, err
    }

    return db, nil
}

When we need to run a query:

func getMostViewedDreams(limit int) []DreamInfo {
    var dreams []DreamInfo = make([]DreamInfo, 0, limit)
    db, err := dbconnect.GetConnection("getMostViewedDreams")
    if err != nil {
        slog.Error("20250209.02158 getMostViewedDreams: connecting to the database", "error", err)
        return dreams
    }

    ctx, cancel := context.WithTimeout(context.Background(), time.Duration(i18n.MaxExecutionTime)*time.Second)
    defer cancel()
    results, err := db.QueryContext(ctx, "SELECT id,text,views FROM dreams ORDER BY views DESC LIMIT "+fmt.Sprint(limit))
    if err != nil {
        slog.Error("20250209.02200 getMostViewedDreams: selecting", "error", err)
        return dreams
    }
    defer results.Close()

    for results.Next() {
        var cw DreamInfo
        if err := results.Scan(&cw.Id, &cw.Text, &cw.Views); err != nil {
            slog.Error("20250209.02201 getMostViewedDreams", "error", err)
            return dreams
        }

        cw.Summary = textmanipulation.Summarize(cw.Text, " ")

        dreams = append(dreams, cw)
    }

    return dreams
}

Key Takeaway

The Pool prevents you from crashing the database.
The Context prevents the database from crashing your app's performance.
The Lifetime prevents "Broken Pipe" errors on shared hosting.

Search This Blog

breakpo