1. go
  2. /database
  3. /sql-basics

Working with SQL Databases in Go

Go's standard library provides the database/sql package, which offers a powerful and flexible interface for working with SQL databases. This guide will help you understand how to effectively use SQL databases in your Go applications.

Core Concepts

Before diving into the code, let's understand some fundamental concepts:

  1. Database Connections:

    • A connection represents a single session with the database
    • Connections are managed through a connection pool
    • Each query or transaction uses one connection from the pool
    • Connections should be properly closed when no longer needed

  2. Connection Pooling:

    • Go maintains a pool of database connections
    • The pool can grow up to a maximum number of connections
    • Idle connections are retained up to a configured limit
    • Connection lifetime can be limited to prevent stale connections

  3. Query Types:

    • One-time Queries: Direct SQL statements executed once
    • Prepared Statements: Pre-compiled SQL statements that can be executed multiple times
    • Transactions: Groups of queries that must succeed or fail together

Database Connection

Understanding Connection Setup

The first step in working with a database is establishing a connection. In Go, this involves:

  1. Importing the necessary database driver
  2. Creating a connection string (DSN)
  3. Opening the connection pool
  4. Verifying the connection

Here's how to set up a basic connection:

package main

import (
    "database/sql"
    "log"
    
    _ "github.com/lib/pq" // PostgreSQL driver
)

func main() {
    // Connect to database
    db, err := sql.Open("postgres", "postgres://username:password@localhost/dbname?sslmode=disable")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()
    
    // Test connection
    if err := db.Ping(); err != nil {
        log.Fatal(err)
    }
}

Connection Configuration Best Practices

When setting up database connections in production applications, you should:

  • Use configuration structures for better organization
  • Set appropriate connection pool limits
  • Configure connection timeouts
  • Handle SSL/TLS settings
  • Use environment variables for sensitive information

Here's a more robust connection setup:

type DBConfig struct {
    Driver   string
    Host     string
    Port     int
    User     string
    Password string
    DBName   string
    SSLMode  string
}

func NewDB(config DBConfig) (*sql.DB, error) {
    dsn := fmt.Sprintf(
        "host=%s port=%d user=%s password=%s dbname=%s sslmode=%s",
        config.Host, config.Port, config.User,
        config.Password, config.DBName, config.SSLMode,
    )
    
    db, err := sql.Open(config.Driver, dsn)
    if err != nil {
        return nil, err
    }
    
    // Configure connection pool
    db.SetMaxOpenConns(25)  // Maximum number of open connections
    db.SetMaxIdleConns(5)   // Maximum number of idle connections
    db.SetConnMaxLifetime(5 * time.Minute) // Maximum connection lifetime
    
    return db, nil
}

Executing Queries

Understanding Query Types

Go provides several ways to execute queries, each suited for different scenarios:

  1. QueryRow: For queries expecting a single row result
  2. Query: For queries returning multiple rows
  3. Exec: For queries that don't return rows (INSERT, UPDATE, DELETE)

Let's look at each type in detail:

Single-Row Queries

When retrieving a single row, use QueryRow. This is common for lookups by ID or unique fields:

func GetUser(db *sql.DB, id int) (*User, error) {
    var user User
    
    query := "SELECT id, name, email FROM users WHERE id = $1"
    err := db.QueryRow(query, id).Scan(&user.ID, &user.Name, &user.Email)
    if err != nil {
        if err == sql.ErrNoRows {
            return nil, fmt.Errorf("user not found: %d", id)
        }
        return nil, err
    }
    
    return &user, nil
}

Key points about single-row queries:

  • Use parameter placeholders ($1, $2, etc.) to prevent SQL injection
  • Handle the special case of no rows found (sql.ErrNoRows)
  • Use Scan to map column values to Go variables

Multiple-Row Queries

For queries that return multiple rows, use Query. Important considerations:

  • Always close the rows object
  • Check for errors after iteration
  • Handle column scanning efficiently
func GetUsers(db *sql.DB) ([]User, error) {
    rows, err := db.Query("SELECT id, name, email FROM users")
    if err != nil {
        return nil, err
    }
    defer rows.Close()  // Important: always close rows
    
    var users []User
    for rows.Next() {
        var user User
        if err := rows.Scan(&user.ID, &user.Name, &user.Email); err != nil {
            return nil, err
        }
        users = append(users, user)
    }
    
    // Important: check for errors during iteration
    if err := rows.Err(); err != nil {
        return nil, err
    }
    
    return users, nil
}

Prepared Statements

Understanding Prepared Statements

Prepared statements offer several advantages:

  • Better performance for repeated queries
  • Protection against SQL injection
  • Cleaner code organization

However, they also come with considerations:

  • Statements must be closed when no longer needed
  • Each statement maintains its own database connection
  • Statements cannot be used after the database connection is closed

Here's how to effectively use prepared statements:

type UserRepository struct {
    db         *sql.DB
    insertStmt *sql.Stmt
    updateStmt *sql.Stmt
}

func NewUserRepository(db *sql.DB) (*UserRepository, error) {
    insertStmt, err := db.Prepare(`
        INSERT INTO users (name, email)
        VALUES ($1, $2)
        RETURNING id
    `)
    if err != nil {
        return nil, err
    }
    
    updateStmt, err := db.Prepare(`
        UPDATE users
        SET name = $1, email = $2
        WHERE id = $3
    `)
    if err != nil {
        insertStmt.Close()
        return nil, err
    }
    
    return &UserRepository{
        db:         db,
        insertStmt: insertStmt,
        updateStmt: updateStmt,
    }, nil
}

Best Practices and Common Pitfalls

Do's:

  • Always use parameter placeholders
  • Close resources (rows, statements)
  • Handle errors appropriately
  • Use transactions for related operations
  • Set appropriate timeouts
  • Monitor connection pool metrics

Don'ts:

  • Don't concatenate strings to build queries
  • Don't ignore errors during row iteration
  • Don't forget to close rows
  • Don't share statements between goroutines
  • Don't keep transactions open for long periods

Next Steps