database/sql

A database runs as its own program, usually on another machine. Your code sends it statements and reads back rows. database/sql is the package in Go's standard library that manages that exchange.

It deliberately knows nothing about any one database. The part that speaks the real wire format is a driver: a separate package written for Postgres, MySQL or SQLite. A driver is imported for one effect only. When it loads, it registers itself under a short name. You never call the package yourself, so the import is given the blank name _.

Open does not connect

sql.Open takes a driver name and a data source name, a string defined by the driver that holds the host, user, password and database. It looks up the registered driver and returns a *sql.DB. The documentation is explicit: Open may just validate its arguments without creating a connection. A wrong host or a wrong password therefore produces no error here. The only error Open reports is an unregistered driver name.

db.PingContext is normally the first line that really reaches the database. Call it once at startup so a bad address fails then, not on a user's first request.

One pool for the whole program

A *sql.DB is not a connection. It is a pool of connections, and it is safe for many goroutines to use at the same time. Open it once when the program starts and pass it where it is needed. Opening one per request builds a new pool every request.

Shorthand

db.Ping() is exactly db.PingContext(context.Background()). Every method on *sql.DB comes in that pair.

package main

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

	// Imported only for its side effect: loading it registers a driver
	// named "sqlite". The blank name says we never call this package.
	_ "modernc.org/sqlite"
)

func main() {
	// Open looks up the driver and builds a pool. It does not connect.
	db, err := sql.Open("sqlite", ":memory:")
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close() // closes every connection the pool opened

	// First real contact. db.Ping() is this same call with context.Background().
	if err := db.PingContext(context.Background()); err != nil {
		log.Fatal(err)
	}
	fmt.Println("ping ok")

	// A driver name nobody registered. Open reports it with no network involved.
	_, err = sql.Open("postgres", "postgres://localhost/app")
	fmt.Println("second open:", err)
}

// Output:
// ping ok
// second open: sql: unknown driver "postgres" (forgotten import?)

package storage

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

	_ "github.com/jackc/pgx/v5/stdlib" // registers the driver named "pgx"
)

// Open builds the pool and proves the database answers before returning it.
func Open(ctx context.Context, dsn string) (*sql.DB, error) {
	db, err := sql.Open("pgx", dsn)
	if err != nil {
		return nil, fmt.Errorf("open: %w", err) // only a bad driver name lands here
	}

	ctx, cancel := context.WithTimeout(ctx, 5*time.Second)
	defer cancel()
	if err := db.PingContext(ctx); err != nil {
		db.Close() // do not leak the pool we just built
		return nil, fmt.Errorf("ping: %w", err)
	}
	return db, nil
}

package storage

import (
	"context"
	"database/sql"
)

// Store owns the pool. Everything that touches the database hangs off it.
type Store struct {
	db *sql.DB
}

func NewStore(db *sql.DB) *Store {
	return &Store{db: db}
}

func (s *Store) UserEmail(ctx context.Context, id int64) (string, error) {
	var email string
	err := s.db.QueryRowContext(ctx, "SELECT email FROM users WHERE id = $1", id).Scan(&email)
	return email, err
}

package main

import (
	"context"
	"log"
	"os"

	"example.com/app/storage"
)

func main() {
	// A DSN holds a password, so it belongs in the environment, not in the source.
	dsn := os.Getenv("DATABASE_URL")
	if dsn == "" {
		log.Fatal("DATABASE_URL is not set")
	}

	db, err := storage.Open(context.Background(), dsn)
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()

	log.Println("database ready")
}