Working with PostgreSQL/MySQL

Introduction

The database/sql package provides a generic SQL interface with connection pooling. Drivers like pgx (PostgreSQL) and go-sql-driver/mysql register via blank import. Go favors raw SQL or lightweight query builders over heavy ORMs — though sqlc and GORM are popular choices.

Production database code uses context for query cancellation, prepared statements for repeated queries, and transactions for atomic operations. Connection pool tuning (SetMaxOpenConns, SetMaxIdleConns) prevents exhausting database connections under load.

This lesson connects to PostgreSQL, runs parameterized queries, and handles sql.ErrNoRows — patterns asked in every Go backend interview.

The story

A Uber trip-history service connects to PostgreSQL through database/sql with a pooled *sql.DB — not a single connection. The pool manages up to 25 open connections, reuses idle ones, and health-checks the database before a Kubernetes liveness probe marks the pod ready.

sql.Open doesn't connect immediately; the first query verifies connectivity. Always pass context.Context to QueryContext for cancellation during deploys.

Understanding the topic

Key concepts

• sql.Open returns *sql.DB — connection pool, not single connection.
• db.QueryContext(ctx, sql, args...) for SELECT returning rows.
• db.ExecContext for INSERT/UPDATE/DELETE.
• rows.Scan(&dest...) maps columns to Go variables.
• sql.ErrNoRows when QueryRow finds nothing — check with errors.Is.
• tx.BeginTx → Commit/Rollback for transactions.

flowchart TB
  Handler --> sql.DB
  sql.DB --> Pool[Connection Pool]
  Pool --> Driver[pgx / mysql driver]
  Driver --> Postgres[(PostgreSQL)]

Step-by-step explanation

1. Import driver: _ "github.com/jackc/pgx/v5/stdlib".
2. Open with DSN connection string from env.
3. PingContext verifies connectivity at startup.
4. Query with $1 placeholders (Postgres) or ? (MySQL).
5. Always close rows: defer rows.Close().
6. Configure pool limits matching database max_connections.

Practical code example

PostgreSQL connection with parameterized query and context:

package main

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

	_ "github.com/jackc/pgx/v5/stdlib"
)

type User struct {
	ID    int64
	Email string
}

type UserRepo struct {
	db *sql.DB
}

func NewUserRepo(db *sql.DB) *UserRepo {
	return &UserRepo{db: db}
}

func (r *UserRepo) GetByEmail(ctx context.Context, email string) (User, error) {
	const q = `SELECT id, email FROM users WHERE email = $1`
	var u User
	err := r.db.QueryRowContext(ctx, q, email).Scan(&u.ID, &u.Email)
	if errors.Is(err, sql.ErrNoRows) {
		return User{}, fmt.Errorf("user %s: %w", email, err)
	}
	return u, err
}

func openDB(dsn string) (*sql.DB, error) {
	db, err := sql.Open("pgx", dsn)
	if err != nil {
		return nil, err
	}
	db.SetMaxOpenConns(25)
	db.SetMaxIdleConns(5)
	db.SetConnMaxLifetime(5 * time.Minute)
	ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
	defer cancel()
	return db, db.PingContext(ctx)
}

func main() {
	dsn := os.Getenv("DATABASE_URL")
	if dsn == "" {
		dsn = "postgres://user:pass@localhost:5432/app?sslmode=disable"
	}
	db, err := openDB(dsn)
	if err != nil {
		panic(err)
	}
	defer db.Close()

	repo := NewUserRepo(db)
	u, err := repo.GetByEmail(context.Background(), "alice@example.com")
	fmt.Println(u, err)
}

Line-by-line code explanation

• db, err := sql.Open("postgres", dsn) creates a connection pool — doesn't ping yet.
• db.SetMaxOpenConns(25) limits concurrent connections to protect the database.
• db.SetMaxIdleConns(5) keeps warm connections ready for the next query.
• ctx, cancel := context.WithTimeout(ctx, 3*time.Second) bounds query execution time.
• row := db.QueryRowContext(ctx, "SELECT ...", id) fetches a single row.
• err := row.Scan(&name, &email) maps columns into Go variables — order must match SELECT.
• sql.ErrNoRows indicates no matching row — distinct from a connection error.
• db.PingContext(ctx) verifies connectivity in health-check endpoints.

Key takeaway: Never concatenate SQL — always parameterized. Ping at startup. Set pool limits. Map sql.ErrNoRows to domain error.

Real-world use

Where you'll use this in production

• User authentication and session storage in PostgreSQL.
• Order and inventory persistence in e-commerce backends.
• Analytics aggregation queries against read replicas.
• Migration scripts with golang-migrate or goose.

Best practices

• Parameterized queries only — prevent SQL injection.
• Pass context.Context to all Query/Exec calls.
• Configure connection pool for expected concurrency.
• Close rows; check rows.Err() after iteration.
• Use transactions for multi-statement atomic operations.
• Health check: db.PingContext in readiness probe.

Common mistakes

• String concatenation in SQL — injection vulnerability.
• Not closing sql.Rows — connection leak.
• sql.Open error ignored — Open doesn't connect until Ping.
• Default unlimited connections exhausting Postgres.
• Not handling sql.ErrNoRows — conflating with real errors.

Advanced interview questions

Summary

database/sql provides pooled connections with driver plugins. Always use parameterized queries and context. Configure pool limits; Ping at startup. Handle sql.ErrNoRows as domain not-found, not server error. Next lesson: full CRUD operations with transactions.