go-simple-api/lessons/lesson-03-config-and-mysql.md

Lesson 3 — Config & MySQL Connection

New Go concepts in this lesson: defer, blank imports (_), context.WithTimeout deadlines, working with *sql.DB/*sql.Rows. These build on pointers and error handling from the Go Basics lessons — review those if anything below feels unfamiliar.

Part A — standalone playground

First, run a throwaway MySQL with Docker so you have something to connect to:

docker run --name mysql-demo -e MYSQL_ROOT_PASSWORD=devpass -e MYSQL_DATABASE=demo -p 3306:3306 -d mysql:9

Then the playground project:

mkdir ~/go-playground/mysql-demo && cd ~/go-playground/mysql-demo
go mod init mysql-demo
go get github.com/go-sql-driver/mysql@latest

main.go

package main

import (
	"context"
	"database/sql"
	"log"
	"time"

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

func main() {
	// 1. Open a connection pool (this does NOT actually connect yet!)
	db, err := sql.Open("mysql", "root:devpass@tcp(127.0.0.1:3306)/demo?parseTime=true")
	if err != nil {
		log.Fatalf("sql.Open failed: %v", err)
	}
	defer db.Close()

	// 2. Configure the pool
	db.SetMaxOpenConns(10)
	db.SetMaxIdleConns(5)
	db.SetConnMaxLifetime(5 * time.Minute)

	// 3. Actually verify we can connect
	ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
	defer cancel()
	if err := db.PingContext(ctx); err != nil {
		log.Fatalf("ping failed: %v", err)
	}
	log.Println("connected to mysql")

	// 4. Create a table and insert a row
	_, err = db.ExecContext(ctx, `
		CREATE TABLE IF NOT EXISTS notes (
			id INT AUTO_INCREMENT PRIMARY KEY,
			body VARCHAR(255) NOT NULL,
			created_at DATETIME NOT NULL
		)`)
	if err != nil {
		log.Fatalf("create table failed: %v", err)
	}

	res, err := db.ExecContext(ctx,
		"INSERT INTO notes (body, created_at) VALUES (?, ?)",
		"hello from go", time.Now(),
	)
	if err != nil {
		log.Fatalf("insert failed: %v", err)
	}
	id, _ := res.LastInsertId()
	log.Printf("inserted note with id %d", id)

	// 5. Query rows back
	rows, err := db.QueryContext(ctx, "SELECT id, body, created_at FROM notes")
	if err != nil {
		log.Fatalf("query failed: %v", err)
	}
	defer rows.Close()

	for rows.Next() {
		var (
			noteID    int
			body      string
			createdAt time.Time
		)
		if err := rows.Scan(&noteID, &body, &createdAt); err != nil {
			log.Fatalf("scan failed: %v", err)
		}
		log.Printf("note: id=%d body=%q created=%s", noteID, body, createdAt)
	}
	if err := rows.Err(); err != nil {
		log.Fatalf("rows error: %v", err)
	}
}

Run it:

go run .

First, a quick primer on defer

You'll see defer constantly from this lesson onward. It schedules a function call to run right when the surrounding function returns — no matter how it returns (normal return, or via a log.Fatal-style exit, etc., with some caveats). It's most commonly used for cleanup:

func doSomething() {
	f := open()
	defer f.Close() // runs automatically when doSomething() returns, wherever that happens
	// ... lots of code, maybe with early returns ...
}

Without defer, you'd have to remember to call f.Close() before every return in the function — easy to forget on one path. defer guarantees it happens exactly once, right before the function actually exits.

Line by line, the rest of the file

• _ "github.com/go-sql-driver/mysql" — a blank import. The underscore means "import this package purely for its side effects; I'm not calling any of its functions directly by name." This package's init() function (a special function that runs automatically on import) registers "mysql" as a driver name with database/sql. This is a common pattern for database drivers and plugins in Go.
• sql.Open("mysql", dsn) — despite the name, this does not connect yet. It validates the DSN format and returns a *sql.DB, which is really a connection pool manager, not one live connection. Connections open lazily, on first actual use.
• The DSN (data source name) "root:devpass@tcp(127.0.0.1:3306)/demo?parseTime=true" is user:password@tcp(host:port)/dbname?options. parseTime=true tells the driver to convert MySQL DATETIME/TIMESTAMP columns into Go time.Time values automatically instead of raw bytes.
• defer db.Close() — closes the pool when main exits. In a real server, this only runs once, at shutdown — you build ONE *sql.DB for the whole app's lifetime, never one per request.
• SetMaxOpenConns / SetMaxIdleConns / SetConnMaxLifetime — pool tuning. Max open caps simultaneous connections (protects the database from being overwhelmed). Max idle keeps some connections warm instead of reopening constantly. Conn max lifetime forces periodic recycling (useful behind load balancers, or if MySQL itself closes long-idle connections).
• context.WithTimeout(context.Background(), 5*time.Second) — builds a context that automatically expires after 5 seconds (same construct used for graceful shutdown in Lesson 1). Passing this into PingContext means the ping gives up after 5 seconds instead of hanging forever if the database host is unreachable.
• db.PingContext(ctx) — this is what actually forces a real connection attempt, so bad credentials/host are caught immediately at startup, instead of failing later on the first real query.
• db.ExecContext(ctx, query, args...) — for statements that don't return rows (CREATE, INSERT, UPDATE, DELETE). The ? placeholders are parameterized queries — never build SQL by concatenating strings with user input; this is what prevents SQL injection.
• res.LastInsertId() — returns the auto-increment ID of the row you just inserted.
• db.QueryContext(ctx, query) — for SELECT statements, returns *sql.Rows.
• defer rows.Close() — always close rows, or you leak the underlying connection back to the pool. One of the most common Go/SQL bugs.
• rows.Next() — advances to the next row; returns false when there are no more, or on error.
• rows.Scan(&noteID, &body, &createdAt) — copies the current row's columns into your variables, in order, by pointer (note the & — same reason as always: Scan needs to write into your variables, so it needs their addresses). Types must match or be convertible.
• rows.Err() — check this after the loop. Next() returning false doesn't tell you why it stopped — could just mean "no more rows" (fine), or a connection error mid-scan (not fine). Always check.

Run the program twice and notice two notes get inserted, since we never cleared the table.

Part B — apply it to the project

Extend internal/config/config.go with DB settings:

package config

import "os"

type Config struct {
	Port string

	DBHost     string
	DBPort     string
	DBUser     string
	DBPassword string
	DBName     string
}

func Load() Config {
	return Config{
		Port: getEnv("PORT", "8080"),

		DBHost:     getEnv("DB_HOST", "127.0.0.1"),
		DBPort:     getEnv("DB_PORT", "3306"),
		DBUser:     getEnv("DB_USER", "root"),
		DBPassword: getEnv("DB_PASSWORD", "devpass"),
		DBName:     getEnv("DB_NAME", "go_simple_api"),
	}
}

func getEnv(key, fallback string) string {
	if v := os.Getenv(key); v != "" {
		return v
	}
	return fallback
}

Same getEnv pattern from Lesson 1 — just more fields.

internal/database/mysql.go

package database

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

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

	"git.hamidsoltani.com/hamid/go-simple-api/internal/config"
)

func NewMySQL(ctx context.Context, cfg config.Config) (*sql.DB, error) {
	dsn := fmt.Sprintf(
		"%s:%s@tcp(%s:%s)/%s?parseTime=true",
		cfg.DBUser, cfg.DBPassword, cfg.DBHost, cfg.DBPort, cfg.DBName,
	)

	db, err := sql.Open("mysql", dsn)
	if err != nil {
		return nil, fmt.Errorf("open mysql: %w", err)
	}

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

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

	if err := db.PingContext(pingCtx); err != nil {
		return nil, fmt.Errorf("ping mysql: %w", err)
	}

	return db, nil
}

This is Part A's connection logic, reshaped into a function that returns (*sql.DB, error) instead of calling log.Fatal directly — a library-style function shouldn't kill the whole program itself; it should return the error and let the caller (main.go) decide what to do about it.

New here: fmt.Errorf("open mysql: %w", err) wraps the original error (see Go Basics Part 3) — adding context ("open mysql: ...") while preserving the original error so it can still be inspected further up the call chain with errors.Is/errors.As. This is the idiomatic Go way to add context to errors as they bubble up through layers.

Update cmd/api/main.go to connect on startup and close on shutdown:

package main

import (
	"context"
	"net/http"
	"os"
	"os/signal"
	"syscall"
	"time"

	"git.hamidsoltani.com/hamid/go-simple-api/internal/config"
	"git.hamidsoltani.com/hamid/go-simple-api/internal/database"
	"git.hamidsoltani.com/hamid/go-simple-api/internal/logging"
	"git.hamidsoltani.com/hamid/go-simple-api/internal/router"
)

func main() {
	cfg := config.Load()
	logger := logging.New()

	ctx := context.Background()

	db, err := database.NewMySQL(ctx, cfg)
	if err != nil {
		logger.Error("failed to connect to database", "error", err)
		os.Exit(1)
	}
	defer db.Close()
	logger.Info("connected to database", "host", cfg.DBHost, "db", cfg.DBName)

	r := router.New(logger)

	srv := &http.Server{
		Addr:    ":" + cfg.Port,
		Handler: r,
	}

	go func() {
		logger.Info("server starting", "port", cfg.Port)
		if err := srv.ListenAndServe(); err != nil && err != http.ErrServerClosed {
			logger.Error("server error", "error", err)
			os.Exit(1)
		}
	}()

	quit := make(chan os.Signal, 1)
	signal.Notify(quit, syscall.SIGINT, syscall.SIGTERM)
	<-quit

	logger.Info("shutting down gracefully")
	shutdownCtx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
	defer cancel()

	if err := srv.Shutdown(shutdownCtx); err != nil {
		logger.Error("forced shutdown", "error", err)
		os.Exit(1)
	}
	logger.Info("server stopped")
}

• db, err := database.NewMySQL(ctx, cfg) — if this fails, we log and os.Exit(1) immediately; there's no point starting an HTTP server that can't reach its own database.
• defer db.Close() — closes the connection pool when main returns, i.e. after graceful shutdown finishes below.
• db isn't used by the router yet — that's Lesson 4, once we build a user repository that needs it to run queries. For now we're just proving the connection works at startup.

Add a .env file at the project root (we're not auto-loading it yet — export these manually for now, or run export $(grep -v '^#' .env | xargs)):

PORT=8080
DB_HOST=127.0.0.1
DB_PORT=3306
DB_USER=root
DB_PASSWORD=devpass
DB_NAME=go_simple_api

Try it

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

docker run --name mysql-api -e MYSQL_ROOT_PASSWORD=devpass -e MYSQL_DATABASE=go_simple_api -p 3306:3306 -d mysql:9

go run ./cmd/api

You should see "connected to database" in the JSON logs, then "server starting". Ctrl+C should still shut down gracefully, closing the DB pool cleanly along the way.

Once this works, move to Lesson 4 — the user model & repository pattern (this is also where the pointer concepts from Go Basics Part 2 pay off in full).