The database handle is not a connection
You are building a service that needs to store user data. MySQL is running on a server. Now Go needs to talk to it. The standard library provides database/sql, which defines how Go interacts with databases. It does not speak MySQL. You need a driver to bridge the gap. The community standard is github.com/go-sql-driver/mysql.
Import the driver with a blank identifier to register it with the standard library. Then use sql.Open to create a database handle. The handle manages a pool of connections, handles concurrency, and caches statements. You create one handle per database and share it across your application.
How database/sql and drivers work
database/sql is an interface-driven package. It defines types like *sql.DB, *sql.Rows, and *sql.Tx. It does not implement network protocols. Drivers implement the driver.Driver interface and register themselves under a name. When you call sql.Open("mysql", dsn), the package looks up the "mysql" driver and delegates the work.
The blank import _ "github.com/go-sql-driver/mysql" triggers the driver's init function. The compiler sees the import and ensures the package loads. The _ tells the compiler you are importing for side effects, not to use exported names. This is the standard pattern for registering drivers.
*sql.DB is a handle to a connection pool. It is safe for concurrent use. Multiple goroutines can call Query or Exec on the same *sql.DB simultaneously. The pool manages the underlying network connections. It opens new connections when needed and reuses idle ones. You never pass a raw connection to handlers. You pass the *sql.DB handle.
database/sql handles the pool. You handle the queries.
Minimal connection example
Here is the simplest way to connect and verify the link. The code opens a handle, pings the server, and closes the handle.
package main
import (
"database/sql"
"log"
// Blank import registers the driver with database/sql.
_ "github.com/go-sql-driver/mysql"
)
func main() {
// DSN packs credentials, host, port, and database name into one string.
dsn := "user:password@tcp(127.0.0.1:3306)/dbname"
// Open parses the DSN and returns a pool handle. It does not connect.
db, err := sql.Open("mysql", dsn)
if err != nil {
log.Fatal(err)
}
// Defer ensures the pool closes when main exits.
defer db.Close()
// Ping forces a connection attempt to validate credentials and network.
if err := db.Ping(); err != nil {
log.Fatal(err)
}
log.Println("Connected to MySQL")
}
sql.Open parses. Ping connects. Trust the pool, verify the link.
What happens under the hood
When you call sql.Open, the driver parses the DSN. The DSN follows the format user:password@network(address)/dbname?params. The driver checks for syntax errors. If the DSN is malformed, sql.Open returns an error immediately. The compiler rejects missing imports with sql: unknown driver "mysql". This error appears if you forget the blank import or use the wrong driver name.
sql.Open returns a *sql.DB object. No network connection exists yet. This lazy initialization saves resources. If the handle is created but rarely used, connections open only when needed.
Ping triggers the handshake. The driver opens a connection from the pool, sends a ping command, and waits for the response. If the server is down, Ping returns an error like dial tcp 127.0.0.1:3306: connect: connection refused. If credentials are wrong, you get Error 1045: Access denied for user.
The pool starts with zero connections. The first query or ping opens a connection. Subsequent queries reuse idle connections. When the pool reaches MaxOpenConns, new queries wait for a free connection. If no connection becomes available within the timeout, the query fails with sql: no connections available.
Configuring the connection pool
The default pool settings work for many applications. Tuning them improves performance and stability under load. SetMaxOpenConns caps the total number of connections. SetMaxIdleConns keeps connections alive when idle. SetConnMaxLifetime rotates connections to avoid server-side timeouts.
MySQL servers often close idle connections after a period. If Go holds a stale connection, the next query fails. SetConnMaxLifetime forces the pool to discard old connections and create fresh ones. A lifetime of 5 to 10 minutes prevents stale connection errors.
// ConfigurePool sets sensible defaults for the connection pool.
func ConfigurePool(db *sql.DB) {
// Cap total connections to prevent overwhelming the database server.
db.SetMaxOpenConns(25)
// Keep idle connections alive to reduce latency on bursts.
db.SetMaxIdleConns(5)
// Rotate connections to avoid server-side idle timeouts.
db.SetConnMaxLifetime(5 * time.Minute)
}
Pool settings depend on your workload. Test under load and adjust.
DSN parameters matter
The DSN supports query parameters that change driver behavior. parseTime=true converts DATETIME and TIMESTAMP columns to time.Time values. Without it, these columns return strings. loc=Local sets the timezone for time conversions. timeout sets the dial timeout.
If you store timestamps in the database, enable parseTime. Scanning a DATETIME column into a time.Time variable fails with unsupported Scan, storing driver.Value type string into type *time.Time if parseTime is disabled.
The DSN format is user:password@tcp(host:port)/dbname?parseTime=true&loc=Local. URL-encode special characters in the password. The driver does not handle encoding automatically.
Realistic query example
Production code uses contexts for cancellation and deadlines. Contexts propagate signals to the database driver. If a request times out, the context cancels the query and releases the connection back to the pool.
context.Context always goes as the first parameter, conventionally named ctx. Functions that take a context should respect cancellation. The driver checks the context before executing queries.
// FetchUser retrieves a user by ID using a context for cancellation.
func FetchUser(ctx context.Context, db *sql.DB, id int) (string, error) {
var name string
// QueryRowContext returns a row scanner. Scan executes the query.
// The ? placeholder prevents SQL injection by escaping values.
err := db.QueryRowContext(ctx, "SELECT name FROM users WHERE id = ?", id).Scan(&name)
if err != nil {
// sql.ErrNoRows indicates the query matched no records.
if err == sql.ErrNoRows {
return "", fmt.Errorf("user %d not found", id)
}
return "", err
}
return name, nil
}
Context is plumbing. Pass it to every query.
Handling NULL values
MySQL columns can be NULL. Go variables have zero values. Scanning a NULL into a basic type like string or int fails. The driver returns sql: Scan error on column index 0: unsupported Scan, storing driver.Value type <nil> into type *string.
Use sql.NullString, sql.NullInt64, or pointer types to handle NULLs. sql.NullString has a String field and a Valid boolean. If Valid is false, the value is NULL.
// FetchProfile retrieves a user with nullable fields.
func FetchProfile(ctx context.Context, db *sql.DB, id int) (string, sql.NullString, error) {
var name string
var bio sql.NullString
// Scan into NullString handles NULL values safely.
err := db.QueryRowContext(ctx, "SELECT name, bio FROM users WHERE id = ?", id).Scan(&name, &bio)
if err != nil {
return "", bio, err
}
return name, bio, nil
}
Check bio.Valid before using bio.String. A leaked connection kills your pool. Close rows, or pay the price.
Closing rows and resources
Query returns *sql.Rows. You must call rows.Close() when done. If you skip this, the connection stays in use. The pool cannot reuse it. Under load, the pool exhausts connections and queries block.
QueryRow handles closing automatically. It scans the single row and closes the underlying result. Use QueryRow for single results. Use Query for multiple rows and close the rows explicitly.
// ListUsers fetches all users and closes rows properly.
func ListUsers(ctx context.Context, db *sql.DB) ([]string, error) {
// Query returns rows that must be closed to release the connection.
rows, err := db.QueryContext(ctx, "SELECT name FROM users")
if err != nil {
return nil, err
}
// Defer ensures rows close even if the loop returns early.
defer rows.Close()
var names []string
for rows.Next() {
var name string
if err := rows.Scan(&name); err != nil {
return nil, err
}
names = append(names, name)
}
// Check for errors that occurred during iteration.
if err := rows.Err(); err != nil {
return nil, err
}
return names, nil
}
rows.Err() checks for errors that happened during Next or Scan. Network failures can occur mid-iteration. Always check rows.Err() after the loop.
Common pitfalls and errors
sql.Open does not connect. Code that skips Ping might assume connectivity until the first query fails. Validate connections early.
*sql.DB is shared. Do not create a new handle per request. Do not close the handle per request. Create one handle at startup and pass it to handlers. Closing the handle closes the pool. Subsequent queries fail with sql: database is closed.
The compiler rejects unused imports. If you import the driver but do not use the blank identifier, you get imported and not used. The _ fixes this.
The compiler rejects type mismatches. Scanning a BIGINT into an int fails on 32-bit systems. Use int64 for large integers. The error sql: Scan error on column index 0: converting driver.Value type []uint8 to a int: invalid syntax indicates a type mismatch.
Goroutine leaks happen when queries block indefinitely. If a query hangs and the context does not cancel it, the goroutine waits forever. Always use contexts with deadlines or timeouts.
The worst goroutine bug is the one that never logs. Add logging to query failures.
Decision: when to use database/sql
Use database/sql with go-sql-driver/mysql when you want full control over queries and minimal dependencies. Use an ORM like GORM or SQLX when your project has complex models and you prefer struct mapping over manual scanning. Use a connection pool wrapper when you need custom metrics or connection routing logic that *sql.DB does not expose. Use a mock driver in tests to verify logic without a running database.
Simple queries stay simple. Complex models need helpers.