Database Operations in Go

Go provides excellent support for database operations through the database/sql package. This chapter covers SQL databases, ORMs, and NoSQL solutions.

The database/sql Package

The database/sql package provides a generic interface for SQL databases. Think of database/sql as a universal remote control: it provides the same buttons (Query, Exec, Begin) regardless of which TV (database) you are talking to. The driver is the infrared translator that converts your button presses into the specific protocol your database understands. The package manages a connection pool, handles reconnection, and provides a consistent API regardless of which database you use. You need a driver for your specific database — the driver registers itself via an init function (this is one of the good uses of init and blank imports).

Critical Pitfall: sql.Open does not actually establish a connection to the database. It only validates the driver name and prepares the connection pool. The first actual connection happens when you execute a query. Always call db.Ping() or db.PingContext() after sql.Open to verify connectivity:

db, err := sql.Open("postgres", connStr)
if err != nil {
    return err // Driver name is wrong, or DSN is malformed
}
if err := db.Ping(); err != nil {
    return err // Actually cannot connect to the database
}

Setting Up a Connection

import (
    "database/sql"
    _ "github.com/lib/pq"           // PostgreSQL
    // _ "github.com/go-sql-driver/mysql" // MySQL
    // _ "github.com/mattn/go-sqlite3"     // SQLite
)

func main() {
    // Connection string format varies by driver
    connStr := "host=localhost port=5432 user=postgres password=secret dbname=myapp sslmode=disable"
    
    db, err := sql.Open("postgres", connStr)
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()
    
    // Verify connection
    if err := db.Ping(); err != nil {
        log.Fatal(err)
    }
    
    // Configure connection pool -- these settings are critical for production!
    // Without them, defaults can either exhaust database connections or be too conservative.
    db.SetMaxOpenConns(25)                  // Max open connections (prevents overwhelming the DB)
    db.SetMaxIdleConns(5)                   // Max idle connections (keeps warm connections in the pool)
    db.SetConnMaxLifetime(5 * time.Minute)  // Max connection lifetime (prevents stale connections)
    db.SetConnMaxIdleTime(1 * time.Minute)  // Max idle time (frees unused connections)
}

Connection Pool Sizing: A common rule of thumb is to set MaxOpenConns to 2-3x the number of CPU cores for your database server, but never exceed the database’s own max connections limit. For cloud databases (RDS, Cloud SQL), check your instance’s connection limit and leave headroom for monitoring and admin connections. Setting MaxOpenConns too high can actually decrease performance due to connection thrashing.

### Environment-Based Configuration

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

func NewDBConfig() *DBConfig {
    return &DBConfig{
        Host:     getEnv("DB_HOST", "localhost"),
        Port:     getEnvInt("DB_PORT", 5432),
        User:     getEnv("DB_USER", "postgres"),
        Password: getEnv("DB_PASSWORD", ""),
        DBName:   getEnv("DB_NAME", "myapp"),
        SSLMode:  getEnv("DB_SSLMODE", "disable"),
    }
}

func (c *DBConfig) ConnectionString() string {
    return fmt.Sprintf(
        "host=%s port=%d user=%s password=%s dbname=%s sslmode=%s",
        c.Host, c.Port, c.User, c.Password, c.DBName, c.SSLMode,
    )
}

Basic CRUD Operations

Inserting Data

type User struct {
    ID        int64
    Name      string
    Email     string
    CreatedAt time.Time
}

func (r *UserRepository) Create(ctx context.Context, user *User) error {
    query := `
        INSERT INTO users (name, email, created_at)
        VALUES ($1, $2, $3)
        RETURNING id`
    
    err := r.db.QueryRowContext(ctx, query, user.Name, user.Email, time.Now()).
        Scan(&user.ID)
    
    if err != nil {
        return fmt.Errorf("failed to create user: %w", err)
    }
    
    return nil
}

// Without RETURNING (MySQL style)
func (r *UserRepository) CreateMySQL(ctx context.Context, user *User) error {
    query := `INSERT INTO users (name, email, created_at) VALUES (?, ?, ?)`
    
    result, err := r.db.ExecContext(ctx, query, user.Name, user.Email, time.Now())
    if err != nil {
        return err
    }
    
    id, err := result.LastInsertId()
    if err != nil {
        return err
    }
    
    user.ID = id
    return nil
}

Querying Single Row

func (r *UserRepository) GetByID(ctx context.Context, id int64) (*User, error) {
    query := `SELECT id, name, email, created_at FROM users WHERE id = $1`
    
    var user User
    err := r.db.QueryRowContext(ctx, query, id).
        Scan(&user.ID, &user.Name, &user.Email, &user.CreatedAt)
    
    if err == sql.ErrNoRows {
        return nil, ErrNotFound
    }
    if err != nil {
        return nil, fmt.Errorf("failed to get user: %w", err)
    }
    
    return &user, nil
}

Querying Multiple Rows

func (r *UserRepository) List(ctx context.Context, limit, offset int) ([]*User, error) {
    query := `
        SELECT id, name, email, created_at 
        FROM users 
        ORDER BY created_at DESC
        LIMIT $1 OFFSET $2`
    
    rows, err := r.db.QueryContext(ctx, query, limit, offset)
    if err != nil {
        return nil, err
    }
    defer rows.Close() // Always close rows -- leaked rows exhaust connection pool
    
    var users []*User
    for rows.Next() {
        var u User
        if err := rows.Scan(&u.ID, &u.Name, &u.Email, &u.CreatedAt); err != nil {
            return nil, err
        }
        users = append(users, &u)
    }
    
    // CRITICAL: Check for errors that occurred during iteration.
    // rows.Next() returns false on both exhaustion AND error.
    // Without rows.Err(), you silently swallow iteration errors.
    if err := rows.Err(); err != nil {
        return nil, err
    }
    
    return users, nil
}

Updating Data

func (r *UserRepository) Update(ctx context.Context, user *User) error {
    query := `
        UPDATE users 
        SET name = $1, email = $2, updated_at = $3
        WHERE id = $4`
    
    result, err := r.db.ExecContext(ctx, query, user.Name, user.Email, time.Now(), user.ID)
    if err != nil {
        return err
    }
    
    rowsAffected, err := result.RowsAffected()
    if err != nil {
        return err
    }
    
    if rowsAffected == 0 {
        return ErrNotFound
    }
    
    return nil
}

Deleting Data

func (r *UserRepository) Delete(ctx context.Context, id int64) error {
    query := `DELETE FROM users WHERE id = $1`
    
    result, err := r.db.ExecContext(ctx, query, id)
    if err != nil {
        return err
    }
    
    rowsAffected, err := result.RowsAffected()
    if err != nil {
        return err
    }
    
    if rowsAffected == 0 {
        return ErrNotFound
    }
    
    return nil
}

Transactions

Transactions are like an “all-or-nothing” switch: either every operation within the transaction succeeds and is committed together, or any failure rolls everything back as if nothing happened. In Go, the pattern is to defer tx.Rollback() immediately after beginning the transaction. If Commit() is called successfully, the deferred Rollback() becomes a no-op. If any error occurs, the deferred Rollback() cleans up.

Basic Transaction

func (r *OrderRepository) CreateOrder(ctx context.Context, order *Order) error {
    tx, err := r.db.BeginTx(ctx, nil)
    if err != nil {
        return err
    }
    
    // Idiomatic Go: defer Rollback immediately after BeginTx.
    // If Commit() succeeds, Rollback() is a safe no-op.
    // If any error path returns early, Rollback() cleans up.
    defer tx.Rollback()
    
    // Insert order
    query := `INSERT INTO orders (user_id, total, status) VALUES ($1, $2, $3) RETURNING id`
    err = tx.QueryRowContext(ctx, query, order.UserID, order.Total, "pending").
        Scan(&order.ID)
    if err != nil {
        return err
    }
    
    // Insert order items
    itemQuery := `INSERT INTO order_items (order_id, product_id, quantity, price) VALUES ($1, $2, $3, $4)`
    for _, item := range order.Items {
        _, err = tx.ExecContext(ctx, itemQuery, order.ID, item.ProductID, item.Quantity, item.Price)
        if err != nil {
            return err
        }
    }
    
    // Update inventory
    inventoryQuery := `UPDATE products SET stock = stock - $1 WHERE id = $2 AND stock >= $1`
    for _, item := range order.Items {
        result, err := tx.ExecContext(ctx, inventoryQuery, item.Quantity, item.ProductID)
        if err != nil {
            return err
        }
        affected, _ := result.RowsAffected()
        if affected == 0 {
            return errors.New("insufficient stock")
        }
    }
    
    // Commit
    return tx.Commit()
}

Transaction Helper

func WithTransaction(ctx context.Context, db *sql.DB, fn func(*sql.Tx) error) error {
    tx, err := db.BeginTx(ctx, nil)
    if err != nil {
        return err
    }
    
    defer func() {
        if p := recover(); p != nil {
            tx.Rollback()
            panic(p)
        }
    }()
    
    if err := fn(tx); err != nil {
        tx.Rollback()
        return err
    }
    
    return tx.Commit()
}

// Usage
err := WithTransaction(ctx, db, func(tx *sql.Tx) error {
    if _, err := tx.ExecContext(ctx, query1, args...); err != nil {
        return err
    }
    if _, err := tx.ExecContext(ctx, query2, args...); err != nil {
        return err
    }
    return nil
})

Pitfall — Using db.Query Inside a Transaction: If you call r.db.QueryContext() instead of tx.QueryContext() inside a transaction block, the query runs on a different connection outside the transaction. This is a subtle bug that can cause phantom reads, missed updates, and data inconsistency. Always use tx.QueryContext, tx.ExecContext, and tx.QueryRowContext within a transaction — never the db.* methods.

Pitfall — Long-Running Transactions: Holding a transaction open for too long (e.g., waiting for an external API call within a transaction) blocks the database row or table and can cause timeouts for other queries. Keep transactions short: do all non-database work before beginning the transaction, then execute only the SQL statements inside it.

Prepared Statements

Using Prepared Statements

type UserRepository struct {
    db         *sql.DB
    stmtGet    *sql.Stmt
    stmtList   *sql.Stmt
    stmtCreate *sql.Stmt
}

func NewUserRepository(db *sql.DB) (*UserRepository, error) {
    repo := &UserRepository{db: db}
    
    var err error
    
    repo.stmtGet, err = db.Prepare(`SELECT id, name, email FROM users WHERE id = $1`)
    if err != nil {
        return nil, err
    }
    
    repo.stmtList, err = db.Prepare(`SELECT id, name, email FROM users LIMIT $1 OFFSET $2`)
    if err != nil {
        return nil, err
    }
    
    repo.stmtCreate, err = db.Prepare(`INSERT INTO users (name, email) VALUES ($1, $2) RETURNING id`)
    if err != nil {
        return nil, err
    }
    
    return repo, nil
}

func (r *UserRepository) Close() error {
    var errs []error
    if err := r.stmtGet.Close(); err != nil {
        errs = append(errs, err)
    }
    if err := r.stmtList.Close(); err != nil {
        errs = append(errs, err)
    }
    if err := r.stmtCreate.Close(); err != nil {
        errs = append(errs, err)
    }
    
    if len(errs) > 0 {
        return fmt.Errorf("failed to close statements: %v", errs)
    }
    return nil
}

func (r *UserRepository) GetByID(ctx context.Context, id int64) (*User, error) {
    var user User
    err := r.stmtGet.QueryRowContext(ctx, id).Scan(&user.ID, &user.Name, &user.Email)
    if err == sql.ErrNoRows {
        return nil, ErrNotFound
    }
    return &user, err
}

Handling NULL Values

Using sql.Null Types

type User struct {
    ID        int64
    Name      string
    Email     sql.NullString  // Nullable
    Phone     sql.NullString  // Nullable
    DeletedAt sql.NullTime    // Nullable
}

func (r *UserRepository) GetByID(ctx context.Context, id int64) (*User, error) {
    query := `SELECT id, name, email, phone, deleted_at FROM users WHERE id = $1`
    
    var user User
    err := r.db.QueryRowContext(ctx, query, id).
        Scan(&user.ID, &user.Name, &user.Email, &user.Phone, &user.DeletedAt)
    
    return &user, err
}

// Convert to application struct
type UserDTO struct {
    ID    int64   `json:"id"`
    Name  string  `json:"name"`
    Email *string `json:"email,omitempty"`
    Phone *string `json:"phone,omitempty"`
}

func (u *User) ToDTO() *UserDTO {
    dto := &UserDTO{
        ID:   u.ID,
        Name: u.Name,
    }
    
    if u.Email.Valid {
        dto.Email = &u.Email.String
    }
    if u.Phone.Valid {
        dto.Phone = &u.Phone.String
    }
    
    return dto
}

Using Pointers

type User struct {
    ID    int64
    Name  string
    Email *string // nil if NULL
    Phone *string // nil if NULL
}

func (r *UserRepository) GetByID(ctx context.Context, id int64) (*User, error) {
    query := `SELECT id, name, email, phone FROM users WHERE id = $1`
    
    var user User
    err := r.db.QueryRowContext(ctx, query, id).
        Scan(&user.ID, &user.Name, &user.Email, &user.Phone)
    
    return &user, err
}

Query Builder Pattern

type QueryBuilder struct {
    table      string
    columns    []string
    conditions []string
    args       []interface{}
    orderBy    string
    limit      int
    offset     int
}

func NewQueryBuilder(table string) *QueryBuilder {
    return &QueryBuilder{table: table}
}

func (qb *QueryBuilder) Select(columns ...string) *QueryBuilder {
    qb.columns = columns
    return qb
}

func (qb *QueryBuilder) Where(condition string, args ...interface{}) *QueryBuilder {
    qb.conditions = append(qb.conditions, condition)
    qb.args = append(qb.args, args...)
    return qb
}

func (qb *QueryBuilder) OrderBy(column string, desc bool) *QueryBuilder {
    direction := "ASC"
    if desc {
        direction = "DESC"
    }
    qb.orderBy = fmt.Sprintf("%s %s", column, direction)
    return qb
}

func (qb *QueryBuilder) Limit(limit int) *QueryBuilder {
    qb.limit = limit
    return qb
}

func (qb *QueryBuilder) Offset(offset int) *QueryBuilder {
    qb.offset = offset
    return qb
}

func (qb *QueryBuilder) Build() (string, []interface{}) {
    query := fmt.Sprintf("SELECT %s FROM %s",
        strings.Join(qb.columns, ", "),
        qb.table,
    )
    
    if len(qb.conditions) > 0 {
        query += " WHERE " + strings.Join(qb.conditions, " AND ")
    }
    
    if qb.orderBy != "" {
        query += " ORDER BY " + qb.orderBy
    }
    
    if qb.limit > 0 {
        query += fmt.Sprintf(" LIMIT %d", qb.limit)
    }
    
    if qb.offset > 0 {
        query += fmt.Sprintf(" OFFSET %d", qb.offset)
    }
    
    return query, qb.args
}

// Usage
qb := NewQueryBuilder("users").
    Select("id", "name", "email").
    Where("status = $1", "active").
    Where("created_at > $2", time.Now().AddDate(0, -1, 0)).
    OrderBy("created_at", true).
    Limit(10)

query, args := qb.Build()
rows, err := db.QueryContext(ctx, query, args...)

GORM ORM

GORM is the most popular ORM for Go.

Setup

import (
    "gorm.io/gorm"
    "gorm.io/driver/postgres"
)

type User struct {
    gorm.Model           // ID, CreatedAt, UpdatedAt, DeletedAt
    Name    string       `gorm:"size:100;not null"`
    Email   string       `gorm:"uniqueIndex;not null"`
    Age     int          `gorm:"default:0"`
    Profile Profile      `gorm:"constraint:OnDelete:CASCADE"`
    Orders  []Order
}

type Profile struct {
    ID     uint
    UserID uint
    Bio    string
    Avatar string
}

type Order struct {
    ID     uint
    UserID uint
    Total  float64
    Status string
}

func main() {
    dsn := "host=localhost user=postgres password=secret dbname=myapp port=5432 sslmode=disable"
    db, err := gorm.Open(postgres.Open(dsn), &gorm.Config{
        Logger: logger.Default.LogMode(logger.Info),
    })
    if err != nil {
        log.Fatal(err)
    }
    
    // Auto migrate
    db.AutoMigrate(&User{}, &Profile{}, &Order{})
}

CRUD with GORM

// Create
user := User{Name: "John", Email: "john@example.com"}
result := db.Create(&user)
if result.Error != nil {
    return result.Error
}
fmt.Println("Created ID:", user.ID)

// Read
var user User
db.First(&user, 1)                              // By primary key
db.First(&user, "email = ?", "john@example.com") // By condition

// Read multiple
var users []User
db.Where("age > ?", 18).Find(&users)
db.Where("name LIKE ?", "%john%").Order("created_at desc").Limit(10).Find(&users)

// Update
db.Model(&user).Update("name", "Jane")
db.Model(&user).Updates(User{Name: "Jane", Age: 25})
db.Model(&user).Updates(map[string]interface{}{"name": "Jane", "age": 25})

// Delete (soft delete with gorm.Model)
db.Delete(&user, 1)

// Hard delete
db.Unscoped().Delete(&user, 1)

Relationships with GORM

// Eager loading
var users []User
db.Preload("Profile").Preload("Orders").Find(&users)

// Nested preloading
db.Preload("Orders.Items").Find(&users)

// Conditional preloading
db.Preload("Orders", "status = ?", "completed").Find(&users)

// Create with associations
user := User{
    Name: "John",
    Profile: Profile{Bio: "Developer"},
    Orders: []Order{
        {Total: 100.00, Status: "pending"},
    },
}
db.Create(&user)

GORM Transactions

func createOrderWithGORM(db *gorm.DB, order *Order) error {
    return db.Transaction(func(tx *gorm.DB) error {
        if err := tx.Create(order).Error; err != nil {
            return err
        }
        
        for _, item := range order.Items {
            result := tx.Model(&Product{}).
                Where("id = ? AND stock >= ?", item.ProductID, item.Quantity).
                Update("stock", gorm.Expr("stock - ?", item.Quantity))
            
            if result.RowsAffected == 0 {
                return errors.New("insufficient stock")
            }
        }
        
        return nil
    })
}

sqlx Package

sqlx extends database/sql with helpful features.

import "github.com/jmoiron/sqlx"

type User struct {
    ID        int64     `db:"id"`
    Name      string    `db:"name"`
    Email     string    `db:"email"`
    CreatedAt time.Time `db:"created_at"`
}

func main() {
    db, err := sqlx.Connect("postgres", connectionString)
    if err != nil {
        log.Fatal(err)
    }
}

// Get single row
var user User
err := db.GetContext(ctx, &user, "SELECT * FROM users WHERE id = $1", 1)

// Get multiple rows
var users []User
err := db.SelectContext(ctx, &users, "SELECT * FROM users WHERE age > $1", 18)

// Named queries
user := User{Name: "John", Email: "john@example.com"}
result, err := db.NamedExecContext(ctx,
    "INSERT INTO users (name, email) VALUES (:name, :email)",
    user)

// Named query with IN clause
query, args, err := sqlx.In("SELECT * FROM users WHERE id IN (?)", []int{1, 2, 3})
query = db.Rebind(query)
var users []User
err = db.SelectContext(ctx, &users, query, args...)

Redis

import "github.com/redis/go-redis/v9"

func main() {
    rdb := redis.NewClient(&redis.Options{
        Addr:     "localhost:6379",
        Password: "",
        DB:       0,
    })
    
    ctx := context.Background()
    
    // String operations
    err := rdb.Set(ctx, "key", "value", time.Hour).Err()
    val, err := rdb.Get(ctx, "key").Result()
    
    // Hash operations
    rdb.HSet(ctx, "user:1", map[string]interface{}{
        "name":  "John",
        "email": "john@example.com",
    })
    
    name, err := rdb.HGet(ctx, "user:1", "name").Result()
    user, err := rdb.HGetAll(ctx, "user:1").Result()
    
    // List operations
    rdb.LPush(ctx, "queue", "task1", "task2")
    task, err := rdb.RPop(ctx, "queue").Result()
    
    // Set operations
    rdb.SAdd(ctx, "tags", "go", "programming", "backend")
    tags, err := rdb.SMembers(ctx, "tags").Result()
    
    // Sorted set
    rdb.ZAdd(ctx, "leaderboard", redis.Z{Score: 100, Member: "player1"})
    top, err := rdb.ZRevRangeWithScores(ctx, "leaderboard", 0, 9).Result()
    
    // Pub/Sub
    pubsub := rdb.Subscribe(ctx, "channel")
    ch := pubsub.Channel()
    for msg := range ch {
        fmt.Println(msg.Channel, msg.Payload)
    }
}

Redis Caching Pattern

type CacheRepository struct {
    db    *sql.DB
    cache *redis.Client
}

func (r *CacheRepository) GetUser(ctx context.Context, id int64) (*User, error) {
    key := fmt.Sprintf("user:%d", id)
    
    // Try cache first
    data, err := r.cache.Get(ctx, key).Bytes()
    if err == nil {
        var user User
        if err := json.Unmarshal(data, &user); err == nil {
            return &user, nil
        }
    }
    
    // Cache miss - query database
    user, err := r.getUserFromDB(ctx, id)
    if err != nil {
        return nil, err
    }
    
    // Cache result
    data, _ = json.Marshal(user)
    r.cache.Set(ctx, key, data, time.Hour)
    
    return user, nil
}

func (r *CacheRepository) InvalidateUser(ctx context.Context, id int64) error {
    key := fmt.Sprintf("user:%d", id)
    return r.cache.Del(ctx, key).Err()
}

MongoDB

import (
    "go.mongodb.org/mongo-driver/mongo"
    "go.mongodb.org/mongo-driver/mongo/options"
    "go.mongodb.org/mongo-driver/bson"
    "go.mongodb.org/mongo-driver/bson/primitive"
)

type User struct {
    ID        primitive.ObjectID `bson:"_id,omitempty"`
    Name      string             `bson:"name"`
    Email     string             `bson:"email"`
    CreatedAt time.Time          `bson:"created_at"`
}

func main() {
    ctx := context.Background()
    
    client, err := mongo.Connect(ctx, options.Client().ApplyURI("mongodb://localhost:27017"))
    if err != nil {
        log.Fatal(err)
    }
    defer client.Disconnect(ctx)
    
    db := client.Database("myapp")
    users := db.Collection("users")
    
    // Insert
    user := User{Name: "John", Email: "john@example.com", CreatedAt: time.Now()}
    result, err := users.InsertOne(ctx, user)
    user.ID = result.InsertedID.(primitive.ObjectID)
    
    // Find one
    var found User
    err = users.FindOne(ctx, bson.M{"email": "john@example.com"}).Decode(&found)
    
    // Find many
    cursor, err := users.Find(ctx, bson.M{"name": bson.M{"$regex": "^J"}})
    var results []User
    err = cursor.All(ctx, &results)
    
    // Update
    _, err = users.UpdateOne(ctx,
        bson.M{"_id": user.ID},
        bson.M{"$set": bson.M{"name": "Jane"}},
    )
    
    // Delete
    _, err = users.DeleteOne(ctx, bson.M{"_id": user.ID})
}

Interview Questions

What's the difference between Query and Exec?

Query/QueryContext: Returns rows, use for SELECT
Exec/ExecContext: Returns Result (LastInsertId, RowsAffected), use for INSERT/UPDATE/DELETE

Why is it important to close rows?

Not closing rows causes:

Connection leaks (connection stays open)
Resource exhaustion (max connections reached)
Memory leaks

Always use defer rows.Close() immediately after QueryContext.

How do you prevent SQL injection?

Use parameterized queries ($1, ? placeholders)
Never concatenate user input into SQL strings
Use prepared statements
Use an ORM with proper escaping

What are connection pool settings and why are they important?

MaxOpenConns: Max simultaneous connections (prevents overwhelming DB)
MaxIdleConns: Connections kept in pool (reduces connection overhead)
ConnMaxLifetime: How long a connection can be reused
ConnMaxIdleTime: How long idle connections stay open

Proper settings prevent connection exhaustion and improve performance.

Summary

Package/Tool	Use Case
`database/sql`	Standard SQL interface
`sqlx`	Extended sql with struct scanning
`GORM`	Full-featured ORM
`go-redis`	Redis client
`mongo-driver`	MongoDB client

Interview Deep-Dive

Explain how database/sql connection pooling works in Go. What do MaxOpenConns, MaxIdleConns, and ConnMaxLifetime actually do, and what happens if you misconfigure them?

Strong Answer:

database/sql maintains a pool of database connections internally. When you call QueryContext or ExecContext, it grabs a connection from the pool (or opens a new one), executes the query, and returns the connection to the pool.
MaxOpenConns limits the total number of connections (both in-use and idle). If all connections are in use and a new query arrives, it blocks until one is returned. Set this too high, and you overwhelm the database server. Set it too low, and your application blocks during traffic spikes.
MaxIdleConns controls how many connections stay open in the pool when not in use. Idle connections avoid TCP handshake overhead. Set too high, and you waste database resources. Set too low, and you repeatedly open/close connections.
ConnMaxLifetime forces connections to be closed and replaced after a duration. This prevents stale connections from database firewalls, DNS changes, and connection-level state accumulation. A typical value is 5 minutes.
Misconfiguration example: leaving defaults (MaxOpenConns = 0, meaning unlimited) in a service handling 10,000 concurrent requests. The pool opens 10,000 connections, PostgreSQL rejects them (default max is 100), and you get a flood of “too many connections” errors.

Follow-up: You forgot to call rows.Close() after a QueryContext. What happens?The connection is never returned to the pool. It is “in use” forever. As more queries leak connections, the pool reaches MaxOpenConns, and subsequent queries block indefinitely — classic connection pool exhaustion. The fix is always defer rows.Close() immediately after QueryContext.

Walk me through how you would implement the repository pattern for a Go service, and how does this help with testing?

Strong Answer:

Define a repository interface at the service layer: type UserRepository interface { GetByID(ctx context.Context, id int64) (*User, error); Create(ctx context.Context, user *User) error }.
The concrete implementation uses actual SQL in a postgres package. The service constructor takes the interface: func NewUserService(repo UserRepository) *UserService.
For testing, a mock implements the same interface with in-memory operations. Integration tests inject the real PostgreSQL implementation against a test database.
Key design decisions: the interface uses domain types (*User), not database types (sql.NullString). Error types are domain-specific (ErrNotFound). Context propagates as the first parameter.
The pattern also makes caching transparent: a CachedUserRepo wraps a UserRepository, checks Redis first, falls back to the wrapped repo. Same interface, invisible to the service.

Follow-up: sql.Open does not actually connect to the database. Why was it designed this way?sql.Open creates the pool configuration but opens no TCP connections. The pool is lazy, creating connections on demand. This allows the application to start before the database is ready (common in container orchestration). To verify connectivity, call db.PingContext(ctx) after sql.Open.

Your service needs to insert an order with 50 line items atomically. Show me the transaction pattern and explain the `defer tx.Rollback()` idiom.

Strong Answer:

Start with tx, err := db.BeginTx(ctx, nil). Then defer tx.Rollback() immediately — rollback on an already-committed transaction is a no-op, so this is safe. Execute queries with tx.ExecContext. If any step fails, return the error (deferred rollback fires). On success, call tx.Commit().
This idiom handles all failure paths: early returns, panics, and context cancellation. You never need manual rollback in error branches.
If context is cancelled mid-transaction, the current query fails. The deferred rollback sends ROLLBACK to the database. If the connection was lost, the database server rolls back after its idle timeout.
For 50 line items: use batch inserts rather than 50 individual INSERTs. This reduces round trips from 50 to 1.
Edge case: if tx.Commit() fails (network error), the transaction might or might not have committed server-side. For critical operations, use idempotency keys.

Follow-up: What is the difference between QueryContext and ExecContext?QueryContext returns *sql.Rows for SELECT queries. ExecContext returns sql.Result (with LastInsertId() and RowsAffected()) for INSERT, UPDATE, DELETE. Using QueryContext for an INSERT works but you must still call rows.Close() or you leak a connection. Using ExecContext for a SELECT discards the data. Rule: QueryContext for reading, ExecContext for writing.

​Database Operations in Go

​The database/sql Package

​Setting Up a Connection

​Basic CRUD Operations

​Inserting Data

​Querying Single Row

​Querying Multiple Rows

​Updating Data

​Deleting Data

​Transactions

​Basic Transaction

​Transaction Helper

​Prepared Statements

​Using Prepared Statements

​Handling NULL Values

​Using sql.Null Types

​Using Pointers

​Query Builder Pattern

​GORM ORM

​Setup

​CRUD with GORM

​Relationships with GORM

​GORM Transactions

​sqlx Package

​Redis

​Redis Caching Pattern

​MongoDB

​Interview Questions

​Summary

​Interview Deep-Dive

Database Operations in Go

The database/sql Package

Setting Up a Connection

Basic CRUD Operations

Inserting Data

Querying Single Row

Querying Multiple Rows

Updating Data

Deleting Data

Transactions

Basic Transaction

Transaction Helper

Prepared Statements

Using Prepared Statements

Handling NULL Values

Using sql.Null Types

Using Pointers

Query Builder Pattern

GORM ORM

Setup

CRUD with GORM

Relationships with GORM

GORM Transactions

sqlx Package

Redis

Redis Caching Pattern

MongoDB

Interview Questions

Summary

Interview Deep-Dive