Understanding SQL Connection Errors

SQL connection errors in Go typically appear as:

bash
sql: unknown driver "postgres" (forgotten import?)
dial tcp 127.0.0.1:5432: connection refused
connection timeout
invalid connection string
too many connections
driver: bad connection

Common Scenarios and Solutions

Scenario 1: Driver Not Imported

Problem code: ```go package main

import ( "database/sql" "fmt" )

func main() { db, err := sql.Open("postgres", "postgres://user:pass@localhost/db") if err != nil { log.Fatal(err) } defer db.Close()

// Error: sql: unknown driver "postgres" (forgotten import?) } ```

Solution - Import driver with blank identifier: ```go package main

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

_ "github.com/lib/pq" // PostgreSQL driver )

func main() { db, err := sql.Open("postgres", "postgres://user:pass@localhost/db?sslmode=disable") if err != nil { log.Fatal(err) } defer db.Close()

// Verify connection if err := db.Ping(); err != nil { log.Fatal(err) }

fmt.Println("Connected!") } ```

Common drivers: ```go // PostgreSQL _ "github.com/lib/pq" db, _ := sql.Open("postgres", dsn)

// MySQL _ "github.com/go-sql-driver/mysql" db, _ := sql.Open("mysql", dsn)

// SQLite _ "github.com/mattn/go-sqlite3" db, _ := sql.Open("sqlite3", dsn)

// SQL Server _ "github.com/denisenkom/go-mssqldb" db, _ := sql.Open("mssql", dsn) ```

Scenario 2: Connection Refused

Error: `` dial tcp 127.0.0.1:5432: connection refused

Diagnosis steps: ```bash # Check if database is running docker ps | grep postgres

# Check port netstat -an | grep 5432

# Test connection manually psql -h localhost -U user -d db ```

Solution: ```go func connectWithRetry(dsn string, maxRetries int) (*sql.DB, error) { var db *sql.DB var err error

for i := 0; i < maxRetries; i++ { db, err = sql.Open("postgres", dsn) if err != nil { time.Sleep(time.Second * time.Duration(i+1)) continue }

err = db.Ping() if err == nil { return db, nil }

log.Printf("Connection attempt %d failed: %v", i+1, err) time.Sleep(time.Second * time.Duration(i+1)) }

return nil, fmt.Errorf("failed after %d retries: %w", maxRetries, err) } ```

Scenario 3: Invalid Connection String

Problem code: ``go // Missing required parameters dsn := "postgres://localhost/mydb" db, err := sql.Open("postgres", dsn)

Solution - Proper connection strings: ```go // PostgreSQL dsn := "postgres://user:password@localhost:5432/dbname?sslmode=disable" // Or key=value format dsn := "host=localhost port=5432 user=user password=pass dbname=db sslmode=disable"

// MySQL dsn := "user:password@tcp(localhost:3306)/dbname?parseTime=true"

// SQLite dsn := "/path/to/database.db"

// SQL Server dsn := "sqlserver://user:password@localhost:1433?database=dbname" ```

Connection string builder pattern: ```go func buildDSN(host, port, user, password, dbname string, sslmode bool) string { params := url.Values{} if sslmode { params.Set("sslmode", "require") } else { params.Set("sslmode", "disable") }

u := url.URL{ Scheme: "postgres", User: url.UserPassword(user, password), Host: fmt.Sprintf("%s:%s", host, port), Path: dbname, RawQuery: params.Encode(), } return u.String() } ```

Scenario 4: Connection Timeout

Error: `` context deadline exceeded i/o timeout

Solution - Configure timeouts: ```go func main() { dsn := "postgres://user:pass@localhost/db?connect_timeout=10" db, err := sql.Open("postgres", dsn) if err != nil { log.Fatal(err) }

// Configure connection pool timeouts db.SetConnMaxLifetime(time.Minute * 5) db.SetConnMaxIdleTime(time.Minute * 2)

// Use context with timeout for queries ctx, cancel := context.WithTimeout(context.Background(), 10*time.Second) defer cancel()

var result string err = db.QueryRowContext(ctx, "SELECT 'test'").Scan(&result) if err != nil { log.Fatal(err) } } ```

Scenario 5: Too Many Connections

Error: `` pq: sorry, too many clients already FATAL: remaining connection slots are reserved

Solution - Configure connection pool: ```go func main() { db, err := sql.Open("postgres", dsn) if err != nil { log.Fatal(err) } defer db.Close()

// Set pool limits db.SetMaxOpenConns(25) // Maximum open connections db.SetMaxIdleConns(5) // Maximum idle connections db.SetConnMaxLifetime(5 * time.Minute) // Connection lifetime db.SetConnMaxIdleTime(2 * time.Minute) // Idle connection timeout

// Use connection properly - always close rows rows, err := db.Query("SELECT id FROM users") if err != nil { log.Fatal(err) } defer rows.Close() // Important!

for rows.Next() { var id int if err := rows.Scan(&id); err != nil { log.Fatal(err) } } } ```

Scenario 6: Authentication Failed

Error: `` pq: password authentication failed for user "postgres" Access denied for user 'root'@'localhost'

Solution: ```go // Check credentials from environment func getDSN() string { user := os.Getenv("DB_USER") pass := os.Getenv("DB_PASSWORD") host := os.Getenv("DB_HOST") port := os.Getenv("DB_PORT") dbname := os.Getenv("DB_NAME")

return fmt.Sprintf( "postgres://%s:%s@%s:%s/%s?sslmode=disable", user, pass, host, port, dbname, ) }

// Or use a config file type DBConfig struct { Host string json:"host" Port int json:"port" User string json:"user" Password string json:"password" Database string json:"database" }

func loadConfig(path string) (*DBConfig, error) { data, err := os.ReadFile(path) if err != nil { return nil, err }

var cfg DBConfig if err := json.Unmarshal(data, &cfg); err != nil { return nil, err } return &cfg, nil } ```

Scenario 7: SSL/TLS Issues

Error: `` pq: SSL is not enabled on the server x509: certificate signed by unknown authority

Solution - Configure SSL: ```go // Disable SSL (for development) dsn := "postgres://user:pass@localhost/db?sslmode=disable"

// Require SSL dsn := "postgres://user:pass@localhost/db?sslmode=require"

// Verify CA certificate dsn := "postgres://user:pass@localhost/db?sslmode=verify-ca&sslrootcert=/path/to/ca.crt"

// Verify full certificate chain dsn := "postgres://user:pass@localhost/db?sslmode=verify-full&sslrootcert=/path/to/ca.crt"

// Custom TLS config func connectWithTLS() (*sql.DB, error) { cert, err := tls.LoadX509KeyPair("client.crt", "client.key") if err != nil { return nil, err }

caCert, err := os.ReadFile("ca.crt") if err != nil { return nil, err }

caCertPool := x509.NewCertPool() caCertPool.AppendCertsFromPEM(caCert)

tlsConfig := &tls.Config{ Certificates: []tls.Certificate{cert}, RootCAs: caCertPool, }

// Register custom TLS config pq.RegisterTLSConfig("custom", tlsConfig)

dsn := "postgres://user:pass@localhost/db?sslmode=custom" return sql.Open("postgres", dsn) } ```

Health Check Pattern

```go type DBHealthChecker struct { db *sql.DB }

func (h *DBHealthChecker) Check() error { ctx, cancel := context.WithTimeout(context.Background(), 3*time.Second) defer cancel()

if err := h.db.PingContext(ctx); err != nil { return fmt.Errorf("database ping failed: %w", err) }

// Check connection count var count int err := h.db.QueryRowContext(ctx, "SELECT count(*) FROM pg_stat_activity WHERE datname = current_database()", ).Scan(&count) if err != nil { return fmt.Errorf("failed to check connections: %w", err) }

if count > 90 { // Alert at 90% of max_connections return fmt.Errorf("high connection count: %d", count) }

return nil } ```

Verification

```bash # Test connection go run main.go

# Check database logs docker logs postgres-container

# Monitor connections psql -c "SELECT count(*) FROM pg_stat_activity;"

# Run integration tests go test -v -tags=integration ./... ```