Installer avec Codex ou Claude Copiez ce prompt, collez-le dans Codex, Claude ou un autre assistant, puis laissez-le vérifier la page du skill et l'installer pour vous.
Une commande directe contourne le prompt de vérification. Examinez la source avant de l'exécuter.
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")
}
}
