Go's database ecosystem provides multiple layers of abstraction for SQL database integration. From the standard library's database/sql to enhanced libraries like sqlx and PostgreSQL-optimized pgx, developers can choose the right tool for their performance and ergonomics needs.
Key Features:
🔌 database/sql: Standard interface for any SQL database
🚀 sqlx: Convenience methods with struct scanning and named queries
🐘 pgx: PostgreSQL-native driver with maximum performance
📦 Repository Pattern: Interface-based data access for testability
🔄 Migrations: Schema versioning with golang-migrate
Managing database schema evolution across environments
Optimizing database connection pooling for production
Testing database code with mock repositories
Handling concurrent database access patterns
Migrating from ORMs to SQL-first approaches
Integrating PostgreSQL-specific features (COPY, LISTEN/NOTIFY)
Core Database Libraries
Decision Tree: Choosing Your Database Library
┌─────────────────────────────────────┐
│ What database are you using? │
└──────────────┬──────────────────────┘
│
┌──────────┴──────────┐
│ │
PostgreSQL Other SQL DB
│ │
▼ ▼
┌─────────────────┐ Use database/sql
│ Need max perf? │ + sqlx for convenience
└─────┬───────────┘
│
┌──┴──┐
Yes No
│ │
pgx sqlx + pq driver
Use database/sql when:
Working with any SQL database (MySQL, SQLite, PostgreSQL, etc.)
Need database portability
Want standard library stability with no dependencies
# CLI tool
go install -tags 'postgres' github.com/golang-migrate/migrate/v4/cmd/migrate@latest
# Library
go get -u github.com/golang-migrate/migrate/v4
go get -u github.com/golang-migrate/migrate/v4/database/postgres
go get -u github.com/golang-migrate/migrate/v4/source/file
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
email VARCHAR(255) UNIQUENOT NULL,
password_hash VARCHAR(255) NOT NULL,
created_at TIMESTAMPDEFAULTCURRENT_TIMESTAMP,
updated_at TIMESTAMPDEFAULTCURRENT_TIMESTAMP
);
CREATE INDEX idx_users_email ON users(email);
000001_create_users_table.down.sql:
DROP INDEX IF EXISTS idx_users_email;
DROPTABLE IF EXISTS users;
# Apply all up migrations
migrate -path migrations -database "postgres://user:pass@localhost:5432/db?sslmode=disable" up
# Rollback one migration
migrate -path migrations -database $DATABASE_URL down 1
# Go to specific version
migrate -path migrations -database $DATABASE_URL goto 5
# Check current version
migrate -path migrations -database $DATABASE_URL version
NULL Handling
Using sql.Null* Types
type User struct {
ID int`db:"id"`
Name string`db:"name"`
Email string`db:"email"`
Phone sql.NullString `db:"phone"`// Nullable string
Age sql.NullInt64 `db:"age"`// Nullable int
UpdatedAt sql.NullTime `db:"updated_at"`// Nullable timestamp
}
func(r *PostgresUserRepository) GetUser(ctx context.Context, id int) (*User, error) {
var user User
err := r.db.GetContext(ctx, &user, `SELECT * FROM users WHERE id = $1`, id)
if err != nil {
returnnil, err
}
// Access nullable fieldsif user.Phone.Valid {
fmt.Println("Phone:", user.Phone.String)
}
return &user, nil
}
// Setting NULL valuesfunc(r *PostgresUserRepository) UpdatePhone(ctx context.Context, userID int, phone *string) error {
var nullPhone sql.NullString
if phone != nil {
nullPhone = sql.NullString{String: *phone, Valid: true}
}
// If phone is nil, nullPhone.Valid is false, SQL writes NULL
query := `UPDATE users SET phone = $1 WHERE id = $2`
_, err := r.db.ExecContext(ctx, query, nullPhone, userID)
return err
}
funcGetUsersWithPosts(ctx context.Context, db *sqlx.DB) ([]UserWithPosts, error) {
var users []User
db.SelectContext(ctx, &users, `SELECT * FROM users`)
for i, user := range users {
var posts []Post
// N+1: One query per user!
db.SelectContext(ctx, &posts, `SELECT * FROM posts WHERE user_id = $1`, user.ID)
users[i].Posts = posts
}
return users, nil
}
Correct:
funcGetUsersWithPosts(ctx context.Context, db *sqlx.DB) ([]UserWithPosts, error) {
// Single query with JOIN
query := `
SELECT u.id, u.name, p.id as post_id, p.title, p.content
FROM users u
LEFT JOIN posts p ON p.user_id = u.id
ORDER BY u.id
`
rows, err := db.QueryContext(ctx, query)
if err != nil {
returnnil, err
}
defer rows.Close()
usersMap := make(map[int]*UserWithPosts)
for rows.Next() {
var userID intvar userName stringvar postID sql.NullInt64
var title, content sql.NullString
rows.Scan(&userID, &userName, &postID, &title, &content)
if _, exists := usersMap[userID]; !exists {
usersMap[userID] = &UserWithPosts{ID: userID, Name: userName}
}
if postID.Valid {
usersMap[userID].Posts = append(usersMap[userID].Posts, Post{
ID: int(postID.Int64),
Title: title.String,
Content: content.String,
})
}
}
result := make([]UserWithPosts, 0, len(usersMap))
for _, user := range usersMap {
result = append(result, *user)
}
return result, nil
}
db, _ := sql.Open("postgres", dsn)
// Production-ready pool settings
db.SetMaxOpenConns(25) // Limit total connections
db.SetMaxIdleConns(5) // Limit idle connections
db.SetConnMaxLifetime(5 * time.Minute) // Recycle old connections
db.SetConnMaxIdleTime(1 * time.Minute) // Close idle connections
❌ Ignoring Context Cancellation
Wrong:
funcSlowQuery(db *sql.DB)error {
// No context - query runs until completion even if client disconnects
rows, err := db.Query("SELECT * FROM huge_table")
// ...
}
Correct:
funcSlowQuery(ctx context.Context, db *sql.DB)error {
// Context cancellation propagates to database
rows, err := db.QueryContext(ctx, "SELECT * FROM huge_table")
// If ctx is canceled, query is terminated
}
funcFindUser(db *sql.DB, email string) (*User, error) {
// NEVER concatenate user input into SQL!
query := fmt.Sprintf("SELECT * FROM users WHERE email = '%s'", email)
// Vulnerable to: ' OR '1'='1
row := db.QueryRow(query)
// ...
}
Correct:
funcFindUser(db *sql.DB, email string) (*User, error) {
// Use parameterized queries
query := "SELECT * FROM users WHERE email = $1"
row := db.QueryRow(query, email) // Safe// ...
}
❌ Ignoring Transaction Errors
Wrong:
funcUpdateUser(db *sql.DB, user *User)error {
tx, _ := db.Begin()
tx.Exec("UPDATE users SET name = $1 WHERE id = $2", user.Name, user.ID)
tx.Commit() // Ignores errors - data may not be committed!returnnil
}
Correct:
funcUpdateUser(db *sql.DB, user *User)error {
tx, err := db.Begin()
if err != nil {
return err
}
defer tx.Rollback() // Rollback if commit not reached
_, err = tx.Exec("UPDATE users SET name = $1 WHERE id = $2", user.Name, user.ID)
if err != nil {
return err
}
return tx.Commit() // Check commit error
}
Connection Pooling Best Practices
Tuning Parameters
funcOptimizeDatabasePool(db *sql.DB, config PoolConfig) {
// MaxOpenConns: Total connections (in-use + idle)// Rule of thumb: (CPU cores * 2) + disk spindles// Cloud databases often limit connections (e.g., AWS RDS: 100-5000)
db.SetMaxOpenConns(config.MaxOpen) // Example: 25 for small app// MaxIdleConns: Idle connections ready for reuse// Should be lower than MaxOpenConns// Too low: frequent reconnections (slow)// Too high: wasted resources
db.SetMaxIdleConns(config.MaxIdle) // Example: 5-10// ConnMaxLifetime: Maximum age of any connection// Prevents stale connections to load balancers// Recommended: 5-15 minutes
db.SetConnMaxLifetime(config.MaxLifetime)
// ConnMaxIdleTime: Close idle connections after this duration// Saves resources during low traffic// Recommended: 1-5 minutes
db.SetConnMaxIdleTime(config.MaxIdleTime)
}
type PoolConfig struct {
MaxOpen int
MaxIdle int
MaxLifetime time.Duration
MaxIdleTime time.Duration
}
// Example configurationsvar (
// Development: Low resource usage
DevConfig = PoolConfig{
MaxOpen: 10,
MaxIdle: 2,
MaxLifetime: 10 * time.Minute,
MaxIdleTime: 2 * time.Minute,
}
// Production: High throughput
ProdConfig = PoolConfig{
MaxOpen: 25,
MaxIdle: 10,
MaxLifetime: 5 * time.Minute,
MaxIdleTime: 1 * time.Minute,
}
// High-traffic API: Maximum performance
HighTrafficConfig = PoolConfig{
MaxOpen: 50,
MaxIdle: 20,
MaxLifetime: 5 * time.Minute,
MaxIdleTime: 30 * time.Second,
}
)
Monitoring Connection Pool
funcMonitorConnectionPool(db *sql.DB) {
stats := db.Stats()
fmt.Printf("Connection Pool Stats:\n")
fmt.Printf(" Open Connections: %d\n", stats.OpenConnections)
fmt.Printf(" In Use: %d\n", stats.InUse)
fmt.Printf(" Idle: %d\n", stats.Idle)
fmt.Printf(" Wait Count: %d\n", stats.WaitCount) // Queries waited for connection
fmt.Printf(" Wait Duration: %s\n", stats.WaitDuration) // Total wait time
fmt.Printf(" Max Idle Closed: %d\n", stats.MaxIdleClosed) // Connections closed due to idle
fmt.Printf(" Max Lifetime Closed: %d\n", stats.MaxLifetimeClosed)
// Alert if too many waits (need more connections)if stats.WaitCount > 100 {
fmt.Println("WARNING: High wait count - consider increasing MaxOpenConns")
}
// Alert if many idle closures (pool too large)if stats.MaxIdleClosed > 1000 {
fmt.Println("INFO: Many idle closures - consider reducing MaxIdleConns")
}
}
