The database/sql Package
Go's database/sql package is the standard interface for working with SQL databases. It provides connection pooling, prepared statements, transactions, and a driver-agnostic API. You write your code against database/sql and swap drivers without changing application logic.
sql.Open: It Does Not Open a Connection
The most common misconception about database/sql:
db, err := sql.Open("postgres", "postgres://user:pass@localhost:5432/mydb?sslmode=disable")
if err != nil {
log.Fatal(err)
}
sql.Open validates the driver name and DSN format, but it does not connect to the database. To verify the connection, call Ping:
if err := db.Ping(); err != nil {
log.Fatal("cannot reach database:", err)
}
Or better, use PingContext with a timeout:
ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
defer cancel()
if err := db.PingContext(ctx); err != nil {
log.Fatal("cannot reach database:", err)
}
sql.DB Is a Pool, Not a Connection
sql.DB manages a pool of connections. You create one sql.DB at application startup and share it across your entire application. It is safe for concurrent use:
func main() {
db, err := sql.Open("pgx", os.Getenv("DATABASE_URL"))
if err != nil {
log.Fatal(err)
}
defer db.Close()
// Pass db to handlers, stores, etc.
userStore := store.NewUserStore(db)
server := api.NewServer(userStore)
server.Start()
}
Do not open a new sql.DB per request. Do not close it after each query. One pool for the entire application lifetime.
Drivers
database/sql defines the interface. Drivers implement it for specific databases.
PostgreSQL: pgx
import _ "github.com/jackc/pgx/v5/stdlib"
db, err := sql.Open("pgx", "postgres://user:pass@localhost:5432/mydb")
pgx is the recommended PostgreSQL driver. It supports LISTEN/NOTIFY, COPY, and all PostgreSQL-specific types. Import it with a blank identifier to register the driver.
MySQL: go-sql-driver
import _ "github.com/go-sql-driver/mysql"
db, err := sql.Open("mysql", "user:pass@tcp(localhost:3306)/mydb?parseTime=true")
Always set parseTime=true for MySQL to scan DATETIME columns into time.Time.
SQLite: modernc.org/sqlite
import _ "modernc.org/sqlite"
db, err := sql.Open("sqlite", "file:mydb.sqlite")
This is a pure-Go SQLite driver with no CGo dependency.
Query, QueryRow, Exec
Three methods cover all SQL operations:
QueryRow: One Row
var name string
var email string
err := db.QueryRowContext(ctx,
"SELECT name, email FROM users WHERE id = $1", userID,
).Scan(&name, &email)
if err == sql.ErrNoRows {
// No user found -- not an error, just empty
return nil, nil
}
if err != nil {
return nil, fmt.Errorf("querying user %d: %w", userID, err)
}
QueryRow always returns a *Row. You must call Scan to get the result or the error.
Query: Multiple Rows
rows, err := db.QueryContext(ctx,
"SELECT id, name, email FROM users WHERE active = $1", true,
)
if err != nil {
return nil, fmt.Errorf("querying users: %w", err)
}
defer rows.Close()
var users []User
for rows.Next() {
var u User
if err := rows.Scan(&u.ID, &u.Name, &u.Email); err != nil {
return nil, fmt.Errorf("scanning user: %w", err)
}
users = append(users, u)
}
if err := rows.Err(); err != nil {
return nil, fmt.Errorf("iterating users: %w", err)
}
return users, nil
Always check rows.Err() after the loop. It catches errors that occurred during iteration, like a lost connection.
Exec: No Rows Returned
Use Exec for INSERT, UPDATE, DELETE, and DDL:
result, err := db.ExecContext(ctx,
"INSERT INTO users (name, email) VALUES ($1, $2)",
user.Name, user.Email,
)
if err != nil {
return fmt.Errorf("inserting user: %w", err)
}
rowsAffected, _ := result.RowsAffected()
lastID, _ := result.LastInsertId() // not supported by all drivers
Scanning Results
Scan copies column values into Go variables. The types must be compatible:
// Scan into struct fields
var u User
rows.Scan(&u.ID, &u.Name, &u.Email, &u.CreatedAt)
// Scan nullable columns with sql.Null types
var bio sql.NullString
rows.Scan(&bio)
if bio.Valid {
fmt.Println(bio.String)
}
Common sql.Null types: NullString, NullInt64, NullFloat64, NullBool, NullTime.
Alternatively, use pointer types:
var bio *string
rows.Scan(&bio) // nil if NULL, pointer to string if not
Prepared Statements
Prepared statements are parsed once and executed many times. Use them in hot loops:
stmt, err := db.PrepareContext(ctx, "SELECT name FROM users WHERE id = $1")
if err != nil {
return err
}
defer stmt.Close()
for _, id := range userIDs {
var name string
err := stmt.QueryRowContext(ctx, id).Scan(&name)
if err != nil {
return fmt.Errorf("querying user %d: %w", id, err)
}
fmt.Println(name)
}
For most queries, QueryContext with parameters is fine. The driver may prepare statements automatically. Explicit Prepare matters when you execute the same query thousands of times.
Connection Pool Tuning
The default pool settings work for small applications but not for production under load:
db.SetMaxOpenConns(25) // max simultaneous connections
db.SetMaxIdleConns(10) // max idle connections kept in pool
db.SetConnMaxLifetime(5 * time.Minute) // max time a connection lives
db.SetConnMaxIdleTime(1 * time.Minute) // max idle time before closing
Guidelines
- MaxOpenConns: Set this to match what your database can handle. PostgreSQL defaults to 100 max connections. If you have 4 app instances, each gets 25.
- MaxIdleConns: Should be less than or equal to
MaxOpenConns. Higher values keep more connections warm. - ConnMaxLifetime: Prevents using stale connections. Important when connecting through load balancers that drop idle connections.
- ConnMaxIdleTime: Closes connections that have been idle too long, releasing database resources.
Setting Default Production recommendation
-----------------------------------------------------------------
MaxOpenConns unlimited 25-50 (divide DB max by app count)
MaxIdleConns 2 10-25
ConnMaxLifetime unlimited 5 minutes
ConnMaxIdleTime unlimited 1 minute
Leaving MaxOpenConns at the default (unlimited) is dangerous. Under load, Go opens connections until the database refuses them, and then every query fails simultaneously.
Putting It Together
func NewDB(databaseURL string) (*sql.DB, error) {
db, err := sql.Open("pgx", databaseURL)
if err != nil {
return nil, fmt.Errorf("opening database: %w", err)
}
db.SetMaxOpenConns(25)
db.SetMaxIdleConns(10)
db.SetConnMaxLifetime(5 * time.Minute)
db.SetConnMaxIdleTime(1 * time.Minute)
ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
defer cancel()
if err := db.PingContext(ctx); err != nil {
db.Close()
return nil, fmt.Errorf("pinging database: %w", err)
}
return db, nil
}
Common Pitfalls
- Opening a new sql.DB per request.
sql.DBis a pool. Create one at startup, share it everywhere, close it at shutdown. - Not calling rows.Close(). Leaked rows hold connections open. Always
defer rows.Close()immediately afterQuery. - Ignoring rows.Err(). The
for rows.Next()loop can exit due to an error. Always checkrows.Err()after the loop. - Leaving MaxOpenConns unlimited. Under load, you will exhaust the database connection limit and every query will fail at once.
- Using string formatting for SQL parameters. Never use
fmt.Sprintfto build queries. Use$1, $2(PostgreSQL) or?, ?(MySQL) placeholders to prevent SQL injection. - Not using context. Always use
QueryContext,ExecContext,QueryRowContext. Without a context, a slow query blocks forever.
Key Takeaways
sql.Opendoes not connect. CallPingContextto verify.sql.DBis a connection pool. Create one at startup, share it, close it at shutdown.- Use
QueryRowContextfor single rows,QueryContextfor multiple rows,ExecContextfor mutations. - Always
defer rows.Close()and checkrows.Err()after iteration. - Set
MaxOpenConns,MaxIdleConns, and lifetime settings in production. - Use parameterized queries to prevent SQL injection.
pgxfor PostgreSQL,go-sql-driverfor MySQL are the standard driver choices.