Skip to main content

Go Database Best Practices

Database best practices in Go involve implementing performance optimization, security measures, migration strategies, and production-ready patterns. Following these best practices ensures your database operations are efficient, secure, and maintainable. Understanding database best practices is essential for building production-ready applications that can scale and perform reliably. This comprehensive guide will teach you everything you need to know about database best practices in Go.

Understanding Database Best Practices​

What Are Database Best Practices?​

Database best practices in Go encompass a comprehensive set of guidelines and patterns that ensure:

  • Performance Optimization - Efficient database operations and queries
  • Security Measures - Protecting against common vulnerabilities
  • Data Integrity - Ensuring data consistency and reliability
  • Scalability - Designing for growth and high load
  • Maintainability - Creating code that's easy to understand and modify

Best Practice Categories​

Performance Optimization​

Techniques for improving database operation speed and efficiency.

Security Implementation​

Measures to protect against SQL injection and other attacks.

Data Modeling​

Designing efficient database schemas and relationships.

Performance Optimization​

Query Optimization​

Efficient Query Design​

Writing queries that perform well at scale.

Indexing Strategies​

Using indexes to improve query performance.

package main

import (
    "context"
    "fmt"
    "log"
    "time"
    
    "gorm.io/driver/mysql"
    "gorm.io/gorm"
    "gorm.io/gorm/logger"
)

func main() {
    // Performance optimization examples
    fmt.Println("Performance optimization examples:")
    
    // Database connection with performance settings
    dsn := "root:password@tcp(localhost:3306)/testdb?charset=utf8mb4&parseTime=True&loc=Local"
    db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{
        Logger: logger.Default.LogMode(logger.Info),
        NowFunc: func() time.Time {
            return time.Now().UTC()
        },
    })
    if err != nil {
        log.Fatalf("Failed to connect to database: %v", err)
    }
    
    // Get underlying sql.DB for connection pool configuration
    sqlDB, err := db.DB()
    if err != nil {
        log.Fatalf("Failed to get underlying sql.DB: %v", err)
    }
    
    // Configure connection pool for performance
    sqlDB.SetMaxOpenConns(100)                // High concurrency
    sqlDB.SetMaxIdleConns(10)                 // Keep some connections alive
    sqlDB.SetConnMaxLifetime(5 * time.Minute) // Connection lifetime
    sqlDB.SetConnMaxIdleTime(1 * time.Minute) // Idle connection lifetime
    
    // Model with proper indexing
    type User struct {
        ID        uint      `gorm:"primaryKey"`
        Name      string    `gorm:"size:100;not null;index"`
        Email     string    `gorm:"size:100;uniqueIndex;not null"`
        Age       int       `gorm:"index"`
        City      string    `gorm:"size:100;index"`
        CreatedAt time.Time `gorm:"autoCreateTime;index"`
        UpdatedAt time.Time `gorm:"autoUpdateTime"`
    }
    
    // Auto migrate with indexes
    err = db.AutoMigrate(&User{})
    if err != nil {
        log.Printf("Failed to migrate User table: %v", err)
        return
    }
    
    // Efficient query with proper indexing
    func efficientQuery() {
        var users []User
        result := db.Where("age > ? AND city = ?", 25, "New York").Find(&users)
        if result.Error != nil {
            log.Printf("Failed to query users: %v", result.Error)
            return
        }
        
        fmt.Printf("Found %d users efficiently\n", len(users))
    }
    
    efficientQuery()
    
    // Query with limit and offset for pagination
    func paginatedQuery() {
        var users []User
        result := db.Order("created_at DESC").Limit(10).Offset(0).Find(&users)
        if result.Error != nil {
            log.Printf("Failed to query users: %v", result.Error)
            return
        }
        
        fmt.Printf("Found %d users for page 1\n", len(users))
    }
    
    paginatedQuery()
    
    // Query with select to limit fields
    func selectiveQuery() {
        var users []User
        result := db.Select("id", "name", "email").Where("age > ?", 30).Find(&users)
        if result.Error != nil {
            log.Printf("Failed to query users: %v", result.Error)
            return
        }
        
        fmt.Printf("Found %d users with selective fields\n", len(users))
    }
    
    selectiveQuery()
    
    // Query with context for timeout
    func queryWithContext() {
        ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
        defer cancel()
        
        var users []User
        result := db.WithContext(ctx).Where("age > ?", 25).Find(&users)
        if result.Error != nil {
            log.Printf("Failed to query users: %v", result.Error)
            return
        }
        
        fmt.Printf("Found %d users with context timeout\n", len(users))
    }
    
    queryWithContext()
}

Connection Pool Optimization​

Connection Pool Configuration​

Optimizing connection pool settings for performance.

Connection Monitoring​

Monitoring connection pool health and performance.

package main

import (
    "context"
    "fmt"
    "log"
    "sync"
    "time"
    
    "gorm.io/driver/mysql"
    "gorm.io/gorm"
    "gorm.io/gorm/logger"
)

func main() {
    // Connection pool optimization examples
    fmt.Println("Connection pool optimization examples:")
    
    // Database connection with optimized pool
    dsn := "root:password@tcp(localhost:3306)/testdb?charset=utf8mb4&parseTime=True&loc=Local"
    db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{
        Logger: logger.Default.LogMode(logger.Info),
    })
    if err != nil {
        log.Fatalf("Failed to connect to database: %v", err)
    }
    
    // Get underlying sql.DB
    sqlDB, err := db.DB()
    if err != nil {
        log.Fatalf("Failed to get underlying sql.DB: %v", err)
    }
    
    // Optimize connection pool for high concurrency
    sqlDB.SetMaxOpenConns(200)                // High concurrency
    sqlDB.SetMaxIdleConns(20)                 // More idle connections
    sqlDB.SetConnMaxLifetime(10 * time.Minute) // Longer connection lifetime
    sqlDB.SetConnMaxIdleTime(2 * time.Minute)  // Longer idle time
    
    // Model definition
    type User struct {
        ID        uint      `gorm:"primaryKey"`
        Name      string    `gorm:"size:100;not null"`
        Email     string    `gorm:"size:100;uniqueIndex;not null"`
        Age       int       `gorm:"default:0"`
        CreatedAt time.Time `gorm:"autoCreateTime"`
        UpdatedAt time.Time `gorm:"autoUpdateTime"`
    }
    
    // Auto migrate
    err = db.AutoMigrate(&User{})
    if err != nil {
        log.Printf("Failed to migrate User table: %v", err)
        return
    }
    
    // Simulate high concurrency
    func simulateHighConcurrency() {
        var wg sync.WaitGroup
        numGoroutines := 100
        
        for i := 0; i < numGoroutines; i++ {
            wg.Add(1)
            go func(id int) {
                defer wg.Done()
                
                // Simulate database operation
                var user User
                result := db.First(&user, 1)
                if result.Error != nil {
                    log.Printf("Goroutine %d: Error: %v", id, result.Error)
                    return
                }
                
                fmt.Printf("Goroutine %d: User found - %s\n", id, user.Name)
            }(i)
        }
        
        wg.Wait()
        fmt.Println("High concurrency simulation completed")
    }
    
    simulateHighConcurrency()
    
    // Connection pool monitoring
    func monitorConnectionPool() {
        ticker := time.NewTicker(2 * time.Second)
        defer ticker.Stop()
        
        for i := 0; i < 5; i++ {
            select {
            case <-ticker.C:
                stats := sqlDB.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)
                fmt.Printf("  Wait Duration: %v\n", stats.WaitDuration)
                fmt.Printf("  Max Idle Closed: %d\n", stats.MaxIdleClosed)
                fmt.Printf("  Max Idle Time Closed: %d\n", stats.MaxIdleTimeClosed)
                fmt.Printf("  Max Lifetime Closed: %d\n", stats.MaxLifetimeClosed)
            }
        }
    }
    
    monitorConnectionPool()
}

Security Implementation​

SQL Injection Prevention​

Parameterized Queries​

Using parameterized queries to prevent SQL injection.

Input Validation​

Validating and sanitizing user input.

package main

import (
    "context"
    "fmt"
    "log"
    "regexp"
    "strings"
    "time"
    
    "gorm.io/driver/mysql"
    "gorm.io/gorm"
    "gorm.io/gorm/logger"
)

func main() {
    // Security implementation examples
    fmt.Println("Security implementation examples:")
    
    // Database connection
    dsn := "root:password@tcp(localhost:3306)/testdb?charset=utf8mb4&parseTime=True&loc=Local"
    db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{
        Logger: logger.Default.LogMode(logger.Info),
    })
    if err != nil {
        log.Fatalf("Failed to connect to database: %v", err)
    }
    
    // Model definition
    type User struct {
        ID        uint      `gorm:"primaryKey"`
        Name      string    `gorm:"size:100;not null"`
        Email     string    `gorm:"size:100;uniqueIndex;not null"`
        Age       int       `gorm:"default:0"`
        CreatedAt time.Time `gorm:"autoCreateTime"`
        UpdatedAt time.Time `gorm:"autoUpdateTime"`
    }
    
    // Auto migrate
    err = db.AutoMigrate(&User{})
    if err != nil {
        log.Printf("Failed to migrate User table: %v", err)
        return
    }
    
    // Input validation functions
    func validateEmail(email string) bool {
        emailRegex := regexp.MustCompile(`^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$`)
        return emailRegex.MatchString(email)
    }
    
    func validateName(name string) bool {
        if len(name) < 2 || len(name) > 100 {
            return false
        }
        // Check for only alphanumeric characters and spaces
        nameRegex := regexp.MustCompile(`^[a-zA-Z0-9\s]+$`)
        return nameRegex.MatchString(name)
    }
    
    func sanitizeInput(input string) string {
        // Remove potentially dangerous characters
        input = strings.TrimSpace(input)
        dangerousChars := []string{"<", ">", "\"", "'", "&", "/", "\\", "(", ")", "[", "]", "{", "}", "|", "`", "~", "!", "@", "#", "$", "%", "^", "*", "+", "=", "?", ":", ";", ","}
        for _, char := range dangerousChars {
            input = strings.ReplaceAll(input, char, "")
        }
        return input
    }
    
    // Secure user creation with validation
    func secureUserCreation() {
        user := User{
            Name:  sanitizeInput("John Doe"),
            Email: "[email protected]",
            Age:   30,
        }
        
        // Validate inputs
        if !validateName(user.Name) {
            log.Printf("Invalid name: %s", user.Name)
            return
        }
        
        if !validateEmail(user.Email) {
            log.Printf("Invalid email: %s", user.Email)
            return
        }
        
        if user.Age < 0 || user.Age > 150 {
            log.Printf("Invalid age: %d", user.Age)
            return
        }
        
        // Create user with validated input
        result := db.Create(&user)
        if result.Error != nil {
            log.Printf("Failed to create user: %v", result.Error)
            return
        }
        
        fmt.Printf("User created securely - ID: %d\n", user.ID)
    }
    
    secureUserCreation()
    
    // Secure query with parameterized input
    func secureQuery() {
        var users []User
        
        // This is safe because GORM uses parameterized queries
        result := db.Where("name = ? AND age > ?", "John Doe", 25).Find(&users)
        if result.Error != nil {
            log.Printf("Failed to query users: %v", result.Error)
            return
        }
        
        fmt.Printf("Found %d users with secure query\n", len(users))
    }
    
    secureQuery()
    
    // Secure search with validation
    func secureSearch(searchTerm string) {
        // Validate and sanitize search term
        searchTerm = sanitizeInput(searchTerm)
        if len(searchTerm) < 2 {
            log.Printf("Search term too short: %s", searchTerm)
            return
        }
        
        var users []User
        // Use parameterized query with validated input
        result := db.Where("name LIKE ?", "%"+searchTerm+"%").Find(&users)
        if result.Error != nil {
            log.Printf("Failed to search users: %v", result.Error)
            return
        }
        
        fmt.Printf("Found %d users matching search term: %s\n", len(users), searchTerm)
    }
    
    secureSearch("John")
    
    // Secure update with validation
    func secureUpdate(userID uint, updates map[string]interface{}) {
        // Validate updates
        if name, ok := updates["name"].(string); ok {
            if !validateName(name) {
                log.Printf("Invalid name in update: %s", name)
                return
            }
            updates["name"] = sanitizeInput(name)
        }
        
        if email, ok := updates["email"].(string); ok {
            if !validateEmail(email) {
                log.Printf("Invalid email in update: %s", email)
                return
            }
            updates["email"] = sanitizeInput(email)
        }
        
        if age, ok := updates["age"].(int); ok {
            if age < 0 || age > 150 {
                log.Printf("Invalid age in update: %d", age)
                return
            }
        }
        
        // Update with validated data
        result := db.Model(&User{}).Where("id = ?", userID).Updates(updates)
        if result.Error != nil {
            log.Printf("Failed to update user: %v", result.Error)
            return
        }
        
        fmt.Printf("User updated securely - Rows affected: %d\n", result.RowsAffected)
    }
    
    secureUpdate(1, map[string]interface{}{
        "name": "John Updated",
        "age":  31,
    })
}

Database Access Control​

Connection Security​

Securing database connections and credentials.

Permission Management​

Implementing proper database permissions.

package main

import (
    "context"
    "fmt"
    "log"
    "time"
    
    "gorm.io/driver/mysql"
    "gorm.io/gorm"
    "gorm.io/gorm/logger"
)

func main() {
    // Database access control examples
    fmt.Println("Database access control examples:")
    
    // Secure database connection
    func secureDatabaseConnection() {
        // Use environment variables for sensitive data
        // In production, use proper secret management
        dsn := "root:password@tcp(localhost:3306)/testdb?charset=utf8mb4&parseTime=True&loc=Local&tls=true"
        
        db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{
            Logger: logger.Default.LogMode(logger.Info),
        })
        if err != nil {
            log.Fatalf("Failed to connect to database: %v", err)
        }
        
        // Get underlying sql.DB
        sqlDB, err := db.DB()
        if err != nil {
            log.Fatalf("Failed to get underlying sql.DB: %v", err)
        }
        
        // Configure connection pool with security considerations
        sqlDB.SetMaxOpenConns(50)                // Limit concurrent connections
        sqlDB.SetMaxIdleConns(5)                 // Limit idle connections
        sqlDB.SetConnMaxLifetime(5 * time.Minute) // Short connection lifetime
        sqlDB.SetConnMaxIdleTime(1 * time.Minute) // Short idle time
        
        // Test connection
        err = sqlDB.Ping()
        if err != nil {
            log.Fatalf("Failed to ping database: %v", err)
        }
        
        fmt.Println("Secure database connection established")
    }
    
    secureDatabaseConnection()
    
    // Connection with context timeout
    func connectionWithTimeout() {
        dsn := "root:password@tcp(localhost:3306)/testdb?charset=utf8mb4&parseTime=True&loc=Local"
        db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{
            Logger: logger.Default.LogMode(logger.Info),
        })
        if err != nil {
            log.Fatalf("Failed to connect to database: %v", err)
        }
        
        // Create context with timeout
        ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
        defer cancel()
        
        // Test connection with timeout
        sqlDB, err := db.DB()
        if err != nil {
            log.Fatalf("Failed to get underlying sql.DB: %v", err)
        }
        
        err = sqlDB.PingContext(ctx)
        if err != nil {
            log.Fatalf("Failed to ping database with timeout: %v", err)
        }
        
        fmt.Println("Database connection with timeout established")
    }
    
    connectionWithTimeout()
}

Migration Strategies​

Database Migration Management​

Schema Versioning​

Managing database schema changes over time.

Migration Scripts​

Creating and managing migration scripts.

package main

import (
    "fmt"
    "log"
    "time"
    
    "gorm.io/driver/mysql"
    "gorm.io/gorm"
    "gorm.io/gorm/logger"
    "gorm.io/gorm/migrator"
)

func main() {
    // Migration strategies examples
    fmt.Println("Migration strategies examples:")
    
    // Database connection
    dsn := "root:password@tcp(localhost:3306)/testdb?charset=utf8mb4&parseTime=True&loc=Local"
    db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{
        Logger: logger.Default.LogMode(logger.Info),
    })
    if err != nil {
        log.Fatalf("Failed to connect to database: %v", err)
    }
    
    // Model definitions for migration
    type User struct {
        ID        uint      `gorm:"primaryKey"`
        Name      string    `gorm:"size:100;not null"`
        Email     string    `gorm:"size:100;uniqueIndex;not null"`
        Age       int       `gorm:"default:0"`
        CreatedAt time.Time `gorm:"autoCreateTime"`
        UpdatedAt time.Time `gorm:"autoUpdateTime"`
    }
    
    type Post struct {
        ID        uint      `gorm:"primaryKey"`
        Title     string    `gorm:"size:200;not null"`
        Content   string    `gorm:"type:text"`
        UserID    uint      `gorm:"not null"`
        CreatedAt time.Time `gorm:"autoCreateTime"`
        UpdatedAt time.Time `gorm:"autoUpdateTime"`
    }
    
    // Auto migration
    func autoMigration() {
        err := db.AutoMigrate(&User{}, &Post{})
        if err != nil {
            log.Printf("Failed to auto migrate: %v", err)
            return
        }
        
        fmt.Println("Auto migration completed successfully")
    }
    
    autoMigration()
    
    // Manual migration
    func manualMigration() {
        migrator := db.Migrator()
        
        // Check if table exists
        if !migrator.HasTable(&User{}) {
            err := migrator.CreateTable(&User{})
            if err != nil {
                log.Printf("Failed to create User table: %v", err)
                return
            }
            fmt.Println("User table created")
        }
        
        // Check if column exists
        if !migrator.HasColumn(&User{}, "phone") {
            err := migrator.AddColumn(&User{}, "phone")
            if err != nil {
                log.Printf("Failed to add phone column: %v", err)
                return
            }
            fmt.Println("Phone column added to User table")
        }
        
        // Check if index exists
        if !migrator.HasIndex(&User{}, "idx_user_age") {
            err := migrator.CreateIndex(&User{}, "idx_user_age")
            if err != nil {
                log.Printf("Failed to create age index: %v", err)
                return
            }
            fmt.Println("Age index created on User table")
        }
    }
    
    manualMigration()
    
    // Migration with versioning
    func migrationWithVersioning() {
        // Create migration table
        type Migration struct {
            ID        uint      `gorm:"primaryKey"`
            Version   string    `gorm:"size:50;uniqueIndex;not null"`
            AppliedAt time.Time `gorm:"autoCreateTime"`
        }
        
        err := db.AutoMigrate(&Migration{})
        if err != nil {
            log.Printf("Failed to migrate Migration table: %v", err)
            return
        }
        
        // Check if migration has been applied
        var migration Migration
        result := db.Where("version = ?", "001_add_phone_column").First(&migration)
        if result.Error != nil {
            if result.Error == gorm.ErrRecordNotFound {
                // Apply migration
                err := db.Migrator().AddColumn(&User{}, "phone")
                if err != nil {
                    log.Printf("Failed to add phone column: %v", err)
                    return
                }
                
                // Record migration
                migration = Migration{Version: "001_add_phone_column"}
                db.Create(&migration)
                
                fmt.Println("Migration 001_add_phone_column applied")
            } else {
                log.Printf("Failed to check migration: %v", result.Error)
            }
        } else {
            fmt.Println("Migration 001_add_phone_column already applied")
        }
    }
    
    migrationWithVersioning()
}

Production Patterns​

Error Handling and Logging​

Comprehensive Error Handling​

Implementing robust error handling for database operations.

Database Logging​

Logging database operations for monitoring and debugging.

package main

import (
    "context"
    "fmt"
    "log"
    "time"
    
    "gorm.io/driver/mysql"
    "gorm.io/gorm"
    "gorm.io/gorm/logger"
)

func main() {
    // Production patterns examples
    fmt.Println("Production patterns examples:")
    
    // Database connection with logging
    dsn := "root:password@tcp(localhost:3306)/testdb?charset=utf8mb4&parseTime=True&loc=Local"
    db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{
        Logger: logger.Default.LogMode(logger.Info),
    })
    if err != nil {
        log.Fatalf("Failed to connect to database: %v", err)
    }
    
    // Model definition
    type User struct {
        ID        uint      `gorm:"primaryKey"`
        Name      string    `gorm:"size:100;not null"`
        Email     string    `gorm:"size:100;uniqueIndex;not null"`
        Age       int       `gorm:"default:0"`
        CreatedAt time.Time `gorm:"autoCreateTime"`
        UpdatedAt time.Time `gorm:"autoUpdateTime"`
    }
    
    // Auto migrate
    err = db.AutoMigrate(&User{})
    if err != nil {
        log.Printf("Failed to migrate User table: %v", err)
        return
    }
    
    // Error handling wrapper
    func handleDatabaseError(operation string, err error) {
        if err != nil {
            log.Printf("Database error in %s: %v", operation, err)
            // In production, you might want to send this to a monitoring service
        }
    }
    
    // Database operation with error handling
    func createUserWithErrorHandling() {
        user := User{
            Name:  "John Doe",
            Email: "[email protected]",
            Age:   30,
        }
        
        result := db.Create(&user)
        handleDatabaseError("create user", result.Error)
        
        if result.Error != nil {
            return
        }
        
        fmt.Printf("User created successfully - ID: %d\n", user.ID)
    }
    
    createUserWithErrorHandling()
    
    // Database operation with context and timeout
    func queryWithContextAndTimeout() {
        ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
        defer cancel()
        
        var users []User
        result := db.WithContext(ctx).Where("age > ?", 25).Find(&users)
        handleDatabaseError("query users", result.Error)
        
        if result.Error != nil {
            return
        }
        
        fmt.Printf("Found %d users with context timeout\n", len(users))
    }
    
    queryWithContextAndTimeout()
    
    // Database operation with retry logic
    func queryWithRetry() {
        maxRetries := 3
        var users []User
        var result *gorm.DB
        
        for i := 0; i < maxRetries; i++ {
            result = db.Where("age > ?", 25).Find(&users)
            if result.Error == nil {
                break
            }
            
            log.Printf("Retry %d failed: %v", i+1, result.Error)
            if i < maxRetries-1 {
                time.Sleep(time.Duration(i+1) * time.Second)
            }
        }
        
        handleDatabaseError("query users with retry", result.Error)
        
        if result.Error != nil {
            return
        }
        
        fmt.Printf("Found %d users with retry logic\n", len(users))
    }
    
    queryWithRetry()
}

What You've Learned​

Congratulations! You now have a comprehensive understanding of Go's database best practices:

Performance Optimization​

  • Understanding query optimization techniques
  • Configuring connection pools for performance
  • Using indexes and efficient query patterns
  • Implementing pagination and selective queries

Security Implementation​

  • Preventing SQL injection attacks
  • Validating and sanitizing user input
  • Securing database connections
  • Implementing proper access control

Migration Strategies​

  • Managing database schema changes
  • Implementing migration versioning
  • Using auto and manual migrations
  • Handling migration rollbacks

Production Patterns​

  • Implementing comprehensive error handling
  • Using context for timeouts and cancellation
  • Adding retry logic for resilience
  • Logging and monitoring database operations

Key Concepts​

  • Performance Optimization - Techniques for improving database performance
  • Security Measures - Protecting against common vulnerabilities
  • Migration Management - Handling database schema changes
  • Error Handling - Robust error handling patterns
  • Production Readiness - Patterns for production environments

Next Steps​

You now have a solid foundation in Go's database best practices. These concepts are essential for building production-ready database-driven applications that are secure, performant, and maintainable.

Understanding database best practices is crucial for building scalable and reliable applications. These concepts form the foundation for all production database operations and should be implemented from the beginning of development.

Congratulations! You have completed the comprehensive Go Database Integration chapter. You now have the knowledge to build robust, secure, and performant database-driven applications using Go's powerful database capabilities.

Ready to continue with the next chapter? Let's explore testing and quality assurance to learn how to test your database operations and ensure code quality!