Database Best Practices

// Define models with appropriate types and constraints
type User struct {
    ID        uint           `gorm:"primaryKey"`
    Username  string         `gorm:"index;unique;size:32"`
    Email     string         `gorm:"index;unique;size:255"`
    Password  string         `gorm:"size:255;not null"`
    CreatedAt time.Time      `gorm:"autoCreateTime"`
    UpdatedAt time.Time      `gorm:"autoUpdateTime"`
    DeletedAt gorm.DeletedAt `gorm:"index"`
}

// Use appropriate indexes for frequent queries
type Job struct {
    ID        uint
    UserID    uint           `gorm:"index"`
    ProjectID uint           `gorm:"index"`
    Status    string         `gorm:"index"`
    CreatedAt time.Time      `gorm:"index"`
    UpdatedAt time.Time
    
    // Composite index for common queries
}

// Create composite indexes for WHERE + ORDER BY combinations
func (Job) TableName() string {
    return "jobs"
}

2. GORM Query Patterns

// Always use prepared statements to prevent SQL injection
func (r *JobRepository) GetJobByID(ctx context.Context, jobID uint) (*Job, error) {
    var job Job
    if err := r.db.WithContext(ctx).First(&job, "id = ?", jobID).Error; err != nil {
        return nil, fmt.Errorf("failed to get job: %w", err)
    }
    return &job, nil
}

// Use eager loading to prevent N+1 queries
func (r *UserRepository) GetUserWithJobs(ctx context.Context, userID uint) (*User, error) {
    var user User
    if err := r.db.WithContext(ctx).
        Preload("Jobs").
        First(&user, "id = ?", userID).Error; err != nil {
        return nil, fmt.Errorf("failed to get user: %w", err)
    }
    return &user, nil
}

// Use select to retrieve only needed columns
func (r *UserRepository) GetUserEmails(ctx context.Context) ([]string, error) {
    var emails []string
    if err := r.db.WithContext(ctx).
        Table("users").
        Where("deleted_at IS NULL").
        Select("email").
        Scan(&emails).Error; err != nil {
        return nil, fmt.Errorf("failed to get emails: %w", err)
    }
    return emails, nil
}

// Use builder pattern for complex queries
func (r *JobRepository) ListJobs(ctx context.Context, filter *JobFilter) ([]Job, int64, error) {
    query := r.db.WithContext(ctx)
    
    if filter.UserID > 0 {
        query = query.Where("user_id = ?", filter.UserID)
    }
    if filter.Status != "" {
        query = query.Where("status = ?", filter.Status)
    }
    if filter.ProjectID > 0 {
        query = query.Where("project_id = ?", filter.ProjectID)
    }
    
    var total int64
    if err := query.Model(&Job{}).Count(&total).Error; err != nil {
        return nil, 0, fmt.Errorf("failed to count jobs: %w", err)
    }
    
    var jobs []Job
    if err := query.
        Order("created_at DESC").
        Offset((filter.Page - 1) * filter.Limit).
        Limit(filter.Limit).
        Find(&jobs).Error; err != nil {
        return nil, 0, fmt.Errorf("failed to list jobs: %w", err)
    }
    
    return jobs, total, nil
}

3. Database Transactions

// Always use transactions for multi-step operations
func (s *JobService) CreateJobWithSteps(ctx context.Context, jobReq *CreateJobRequest) (*Job, error) {
    // Start transaction
    tx := s.db.BeginTx(ctx, nil)
    if tx.Error != nil {
        return nil, fmt.Errorf("failed to start transaction: %w", tx.Error)
    }
    
    // Create job
    job := &Job{
        UserID:    jobReq.UserID,
        ProjectID: jobReq.ProjectID,
        Status:    "pending",
    }
    if err := tx.Create(job).Error; err != nil {
        tx.Rollback()
        return nil, fmt.Errorf("failed to create job: %w", err)
    }
    
    // Update related data
    if err := tx.Model(&Project{}).Where("id = ?", jobReq.ProjectID).
        Update("last_job_id", job.ID).Error; err != nil {
        tx.Rollback()
        return nil, fmt.Errorf("failed to update project: %w", err)
    }
    
    // Commit transaction
    if err := tx.Commit().Error; err != nil {
        return nil, fmt.Errorf("failed to commit transaction: %w", err)
    }
    
    return job, nil
}

// Use savepoint for nested transactions
func (s *Service) ComplexOperation(ctx context.Context) error {
    tx := s.db.BeginTx(ctx, nil)
    
    if err := tx.Create(&Entity1{}).Error; err != nil {
        tx.Rollback()
        return err
    }
    
    // Create savepoint
    sp := tx.SavePoint("before_entity2")
    
    if err := tx.Create(&Entity2{}).Error; err != nil {
        tx.RollbackTo(sp)
        // Continue with alternative path
    }
    
    return tx.Commit().Error
}

4. Query Optimization

// Use pagination for large result sets
func (r *Repository) GetPagedResults(ctx context.Context, page, limit int) ([]Item, int64, error) {
    if page < 1 {
        page = 1
    }
    if limit < 1 || limit > 100 {
        limit = 20
    }
    
    var items []Item
    var total int64
    
    offset := (page - 1) * limit
    if err := r.db.WithContext(ctx).
        Model(&Item{}).
        Count(&total).
        Offset(offset).
        Limit(limit).
        Find(&items).Error; err != nil {
        return nil, 0, fmt.Errorf("failed to get paged results: %w", err)
    }
    
    return items, total, nil
}

// Use batching for bulk operations
func (r *Repository) UpdateManyJobs(ctx context.Context, jobIDs []uint, status string) error {
    if len(jobIDs) == 0 {
        return nil
    }
    
    batchSize := 1000
    for i := 0; i < len(jobIDs); i += batchSize {
        end := i + batchSize
        if end > len(jobIDs) {
            end = len(jobIDs)
        }
        
        batch := jobIDs[i:end]
        if err := r.db.WithContext(ctx).
            Model(&Job{}).
            Where("id IN ?", batch).
            Update("status", status).Error; err != nil {
            return fmt.Errorf("failed to update jobs: %w", err)
        }
    }
    
    return nil
}

// Use explain to analyze slow queries
func (r *Repository) AnalyzeQuery() {
    var result map[string]interface{}
    r.db.Raw("EXPLAIN ANALYZE SELECT * FROM jobs WHERE status = ? LIMIT 100", "running").
        Scan(&result)
}

5. Database Migrations

// Use migrations for schema changes
type Migration struct {
    ID        uint
    Version   string `gorm:"unique"`
    AppliedAt time.Time
}

// Migration file: 001_create_users_table.go
func (m *Migrator) Up() error {
    return m.db.Migrator().CreateTable(&User{})
}

func (m *Migrator) Down() error {
    return m.db.Migrator().DropTable(&User{})
}

// Apply migrations
func RunMigrations(db *gorm.DB) error {
    return db.AutoMigrate(
        &User{},
        &Project{},
        &Job{},
        &Resource{},
    )
}

// Add indexes through migrations
func AddIndexes(db *gorm.DB) error {
    return db.Migrator().CreateIndex(&Job{}, "user_id", "project_id", "status")
}

// Rename column safely
func RenameColumn(db *gorm.DB) error {
    return db.Migrator().RenameColumn(&Job{}, "old_name", "new_name")
}

6. Connection Pool Management

// Configure connection pool for optimal performance
func InitDatabase(dsn string) (*gorm.DB, error) {
    db, err := gorm.Open(postgres.Open(dsn), &gorm.Config{
        PrepareStmt:                              true,
        DisableForeignKeyConstraintWhenMigrating: false,
    })
    if err != nil {
        return nil, fmt.Errorf("failed to connect database: %w", err)
    }
    
    sqlDB, err := db.DB()
    if err != nil {
        return nil, fmt.Errorf("failed to get database instance: %w", err)
    }
    
    // Configure connection pool
    sqlDB.SetMaxIdleConns(10)       // Idle connections
    sqlDB.SetMaxOpenConns(100)      // Max open connections
    sqlDB.SetConnMaxLifetime(time.Hour) // Connection lifetime
    
    return db, nil
}

7. Error Handling

// Handle specific database errors
func (r *Repository) CreateUser(ctx context.Context, user *User) error {
    if err := r.db.WithContext(ctx).Create(user).Error; err != nil {
        if errors.Is(err, gorm.ErrDuplicatedKey) {
            return fmt.Errorf("user with this email already exists: %w", err)
        }
        if errors.Is(err, gorm.ErrInvalidDB) {
            return fmt.Errorf("invalid database connection: %w", err)
        }
        return fmt.Errorf("failed to create user: %w", err)
    }
    return nil
}

// Timeout handling
func (r *Repository) QueryWithTimeout(timeout time.Duration, query string, args ...interface{}) error {
    ctx, cancel := context.WithTimeout(context.Background(), timeout)
    defer cancel()
    
    return r.db.WithContext(ctx).Raw(query, args...).Error
}

Database Checklist

• All database operations use context with timeout
• Prepared statements are used (GORM handles this by default)
• N+1 queries are eliminated with preloading
• Pagination is implemented for large result sets
• Indexes are created for frequently queried columns
• Transactions are used for multi-step operations
• Connection pool is properly configured
• Database errors are handled appropriately
• Slow queries are identified and optimized
• Migrations are versioned and tested

Performance Guidelines

• Query <100ms for standard operations
• Pagination with limit 20-100 items per page
• Batch operations in chunks of 1000 or less
• Use connection pool max of 100 for high concurrency
• Index foreign key columns and frequently filtered fields
• Avoid SELECT * - retrieve only needed columns
• Use EXPLAIN ANALYZE for slow queries over 500ms