sql.DB is the concrete database/sql handle for working with SQL databases through registered drivers. It exposes a uniform API while managing a connection pool, so application code does not connect, disconnect, or reuse individual database connections directly. section 10.1
Create a handle with sql.Open(driverName, dataSourceName). Open verifies that the driver is registered but may return a DB whose pool has no active connections. Call PingContext to establish an initial connection and verify that the database is reachable. section 10.1 section 10.1.2
Dial SQLite once and reuse the pool
func Dial(ctx context.Context, dsn string) (*sql.DB, error) { db, err := sql.Open("sqlite", dsn) if err != nil { return nil, fmt.Errorf("opening: %w", err) } if err := db.PingContext(ctx); err != nil { return nil, fmt.Errorf("pinging: %w", err) } return db, nil } Keep the returned *sql.DB for the program's lifetime and use it for queries. The pool reuses available connections, establishes new ones when needed, and can close connections as required. section 10.1.2
Tune the pool only when the application's connection behavior requires it:
- SetMaxOpenConns limits active connections. section 10.1.2
- SetMaxIdleConns limits reusable idle connections. section 10.1.2
- SetConnMaxLifetime and SetConnMaxIdleTime control connection reuse duration. section 10.1.2
- Call DB.Close when the pool must be closed, although long-lived programs rarely need to do so. section 10.1.2
Figure 10.1 The sql package abstracts underlying databases with drivers. The sql package’s registry stores registered drivers, and *DB manages a connection pool.
Use the architecture figure to trace how the sql package registry and registered drivers lead to a *DB-managed connection pool. section 10.1
