How to use database integration and ORM in the Gin framework?

The database integration and ORM usage methods in the Gin framework are as follows:

1. Database connection configuration

1.1 Using GORM

go
import (
    "gorm.io/driver/mysql"
    "gorm.io/gorm"
)

var db *gorm.DB

func initDB() error {
    dsn := "user:password@tcp(127.0.0.1:3306)/dbname?charset=utf8mb4&parseTime=True&loc=Local"
    
    var err error
    db, err = gorm.Open(mysql.Open(dsn), &gorm.Config{
        Logger: logger.Default.LogMode(logger.Info),
    })
    
    if err != nil {
        return err
    }
    
    // Configure connection pool
    sqlDB, err := db.DB()
    if err != nil {
        return err
    }
    
    sqlDB.SetMaxIdleConns(10)
    sqlDB.SetMaxOpenConns(100)
    sqlDB.SetConnMaxLifetime(time.Hour)
    
    return nil
}

1.2 Using sqlx

go
import (
    "github.com/jmoiron/sqlx"
    _ "github.com/go-sql-driver/mysql"
)

var db *sqlx.DB

func initDB() error {
    var err error
    db, err = sqlx.Connect("mysql", "user:password@tcp(127.0.0.1:3306)/dbname")
    if err != nil {
        return err
    }
    
    db.SetMaxOpenConns(100)
    db.SetMaxIdleConns(10)
    db.SetConnMaxLifetime(time.Hour)
    
    return nil
}

2. Model definition

2.1 GORM model

go
type User struct {
    ID        uint           `gorm:"primaryKey" json:"id"`
    Username  string         `gorm:"uniqueIndex;size:50;not null" json:"username"`
    Email     string         `gorm:"uniqueIndex;size:100;not null" json:"email"`
    Password  string         `gorm:"size:255;not null" json:"-"`
    Age       int            `gorm:"not null" json:"age"`
    CreatedAt time.Time      `json:"created_at"`
    UpdatedAt time.Time      `json:"updated_at"`
    DeletedAt gorm.DeletedAt `gorm:"index" json:"-"`
}

func (User) TableName() string {
    return "users"
}

2.2 Database migration

go
func migrateDB() error {
    return db.AutoMigrate(&User{}, &Post{}, &Comment{})
}

3. CRUD operations

3.1 Creating records

go
func createUser(c *gin.Context) {
    var user User
    if err := c.ShouldBindJSON(&user); err != nil {
        c.JSON(400, gin.H{"error": err.Error()})
        return
    }
    
    // Password encryption
    hashedPassword, err := bcrypt.GenerateFromPassword([]byte(user.Password), bcrypt.DefaultCost)
    if err != nil {
        c.JSON(500, gin.H{"error": "Failed to hash password"})
        return
    }
    user.Password = string(hashedPassword)
    
    if err := db.Create(&user).Error; err != nil {
        c.JSON(500, gin.H{"error": "Failed to create user"})
        return
    }
    
    c.JSON(201, user)
}

3.2 Querying records

go
func getUser(c *gin.Context) {
    id := c.Param("id")
    
    var user User
    if err := db.First(&user, id).Error; err != nil {
        if errors.Is(err, gorm.ErrRecordNotFound) {
            c.JSON(404, gin.H{"error": "User not found"})
            return
        }
        c.JSON(500, gin.H{"error": err.Error()})
        return
    }
    
    c.JSON(200, user)
}

func listUsers(c *gin.Context) {
    page, _ := strconv.Atoi(c.DefaultQuery("page", "1"))
    pageSize, _ := strconv.Atoi(c.DefaultQuery("page_size", "10"))
    
    var users []User
    var total int64
    
    if err := db.Model(&User{}).Count(&total).Error; err != nil {
        c.JSON(500, gin.H{"error": err.Error()})
        return
    }
    
    offset := (page - 1) * pageSize
    if err := db.Offset(offset).Limit(pageSize).Find(&users).Error; err != nil {
        c.JSON(500, gin.H{"error": err.Error()})
        return
    }
    
    c.JSON(200, gin.H{
        "data":     users,
        "total":    total,
        "page":     page,
        "page_size": pageSize,
    })
}

3.3 Updating records

go
func updateUser(c *gin.Context) {
    id := c.Param("id")
    
    var user User
    if err := db.First(&user, id).Error; err != nil {
        c.JSON(404, gin.H{"error": "User not found"})
        return
    }
    
    var updateData map[string]interface{}
    if err := c.ShouldBindJSON(&updateData); err != nil {
        c.JSON(400, gin.H{"error": err.Error()})
        return
    }
    
    if err := db.Model(&user).Updates(updateData).Error; err != nil {
        c.JSON(500, gin.H{"error": "Failed to update user"})
        return
    }
    
    c.JSON(200, user)
}

3.4 Deleting records

go
func deleteUser(c *gin.Context) {
    id := c.Param("id")
    
    if err := db.Delete(&User{}, id).Error; err != nil {
        c.JSON(500, gin.H{"error": "Failed to delete user"})
        return
    }
    
    c.JSON(200, gin.H{"message": "User deleted successfully"})
}

4. Complex queries

4.1 Association queries

go
type Post struct {
    ID        uint      `gorm:"primaryKey" json:"id"`
    Title     string    `gorm:"size:200;not null" json:"title"`
    Content   string    `gorm:"type:text" json:"content"`
    UserID    uint      `gorm:"not null" json:"user_id"`
    User      User      `gorm:"foreignKey:UserID" json:"user,omitempty"`
    Comments  []Comment `gorm:"foreignKey:PostID" json:"comments,omitempty"`
    CreatedAt time.Time `json:"created_at"`
}

func getPostWithUser(c *gin.Context) {
    id := c.Param("id")
    
    var post Post
    if err := db.Preload("User").Preload("Comments").First(&post, id).Error; err != nil {
        c.JSON(404, gin.H{"error": "Post not found"})
        return
    }
    
    c.JSON(200, post)
}

4.2 Conditional queries

go
func searchUsers(c *gin.Context) {
    keyword := c.Query("keyword")
    minAge := c.DefaultQuery("min_age", "0")
    
    var users []User
    
    query := db.Model(&User{})
    
    if keyword != "" {
        query = query.Where("username LIKE ? OR email LIKE ?", "%"+keyword+"%", "%"+keyword+"%")
    }
    
    if minAge != "0" {
        query = query.Where("age >= ?", minAge)
    }
    
    if err := query.Find(&users).Error; err != nil {
        c.JSON(500, gin.H{"error": err.Error()})
        return
    }
    
    c.JSON(200, users)
}

5. Transaction handling

5.1 Basic transactions

go
func transferFunds(c *gin.Context) {
    var transfer struct {
        FromID uint `json:"from_id" binding:"required"`
        ToID   uint `json:"to_id" binding:"required"`
        Amount int  `json:"amount" binding:"required,gt=0"`
    }
    
    if err := c.ShouldBindJSON(&transfer); err != nil {
        c.JSON(400, gin.H{"error": err.Error()})
        return
    }
    
    // Start transaction
    tx := db.Begin()
    
    // Check balance
    var fromUser User
    if err := tx.First(&fromUser, transfer.FromID).Error; err != nil {
        tx.Rollback()
        c.JSON(404, gin.H{"error": "User not found"})
        return
    }
    
    if fromUser.Balance < transfer.Amount {
        tx.Rollback()
        c.JSON(400, gin.H{"error": "Insufficient balance"})
        return
    }
    
    // Transfer
    if err := tx.Model(&fromUser).Update("balance", gorm.Expr("balance - ?", transfer.Amount)).Error; err != nil {
        tx.Rollback()
        c.JSON(500, gin.H{"error": "Failed to deduct balance"})
        return
    }
    
    if err := tx.Model(&User{}).Where("id = ?", transfer.ToID).Update("balance", gorm.Expr("balance + ?", transfer.Amount)).Error; err != nil {
        tx.Rollback()
        c.JSON(500, gin.H{"error": "Failed to add balance"})
        return
    }
    
    // Commit transaction
    if err := tx.Commit().Error; err != nil {
        c.JSON(500, gin.H{"error": "Failed to commit transaction"})
        return
    }
    
    c.JSON(200, gin.H{"message": "Transfer successful"})
}

6. Database middleware

6.1 Database context middleware

go
func dbMiddleware(db *gorm.DB) gin.HandlerFunc {
    return func(c *gin.Context) {
        c.Set("db", db)
        c.Next()
    }
}

// Usage example
func handlerWithDB(c *gin.Context) {
    db := c.MustGet("db").(*gorm.DB)
    // Use db for database operations
}

6.2 Transaction middleware

go
func transactionMiddleware(db *gorm.DB) gin.HandlerFunc {
    return func(c *gin.Context) {
        tx := db.Begin()
        c.Set("tx", tx)
        
        defer func() {
            if r := recover(); r != nil {
                tx.Rollback()
                panic(r)
            }
        }()
        
        c.Next()
        
        // If no errors, commit transaction
        if len(c.Errors) == 0 {
            tx.Commit()
        } else {
            tx.Rollback()
        }
    }
}

7. Best practices

1. Connection pool configuration

   • Adjust connection pool size based on application load
   • Set reasonable connection timeout
   • Monitor connection pool usage

2. Query optimization

   • Use indexes to accelerate queries
   • Avoid N+1 query problems
   • Reasonably use preloading
   • Paginate large datasets

3. Transaction management

   • Keep transactions short
   • Handle transaction errors correctly
   • Use transaction middleware to simplify code

4. Data validation

   • Validate at both database and business layers
   • Use GORM validation tags
   • Custom validation rules

5. Error handling

   • Distinguish different types of database errors
   • Provide friendly error messages
   • Log detailed error information

6. Security

   • Use parameterized queries to prevent SQL injection
   • Encrypt sensitive fields
   • Implement soft delete
   • Regularly backup database

Through the above methods, you can efficiently integrate and use databases in the Gin framework.