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. You need a driver for your specific 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
    db.SetMaxOpenConns(25)                  // Max open connections
    db.SetMaxIdleConns(5)                   // Max idle connections
    db.SetConnMaxLifetime(5 * time.Minute)  // Max connection lifetime
    db.SetConnMaxIdleTime(1 * time.Minute)  // Max idle time
}

Environment-Based Configuration

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()
    
    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)
    }
    
    // Check for errors during iteration
    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

Basic Transaction

func (r *OrderRepository) CreateOrder(ctx context.Context, order *Order) error {
    tx, err := r.db.BeginTx(ctx, nil)
    if err != nil {
        return err
    }
    
    // Ensure rollback on error
    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
})

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: "[email protected]"}
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 = ?", "[email protected]") // 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: "[email protected]"}
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": "[email protected]",
    })
    
    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: "[email protected]", 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": "[email protected]"}).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

Testing & Code Quality

Crash Courses

AI Engineering

Math for ML - Understanding Linear Algebra

Probability & Statistics for ML

Math for ML - Understanding Calculus

ML Mastery

Deep Learning Mastery

NestJS Mastery

Microservices Mastery

Low Level Design

OOP Concepts

SOLID Principles

Design Patterns

LLD Case Studies

System Design (HLD)

Senior Level (L5+/Staff)

HLD Case Studies

Engineering Fundamentals

DevOps & Operations

AWS Cloud

AWS Monitoring & Observability

AWS Security Services

AWS Serverless

AWS Operations

AWS Advanced

AWS Case Studies

DevOps Tools

Database Engineering

HIPAA Compliance Mastery

Operating Systems

Linux Internals

Distributed Systems

Networking Mastery

Build Your Own X

Go Lang Mastery

C Programming

Distributed System Tools

​Database Operations in Go

​The database/sql Package

​Setting Up a Connection

​Environment-Based Configuration

​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