Gin Query Building

Introduction

When building web applications using Go's Gin framework with database integration, one of the most common tasks you'll encounter is constructing database queries based on API request parameters. Effective query building is crucial for creating flexible, performant APIs that can filter, sort, and paginate data according to client needs.

In this guide, we'll explore how to build queries in Gin applications that interact with databases. We'll cover extracting query parameters from requests, constructing SQL or ORM queries dynamically, and implementing common patterns like filtering, searching, sorting, and pagination.

Basic Query Parameter Extraction

Let's start with the basics of extracting query parameters from Gin requests:

go
func GetProducts(c *gin.Context) {
    // Extract query parameters
    name := c.Query("name")         // Get the 'name' parameter or empty string if not present
    category := c.DefaultQuery("category", "all")  // Get 'category' with a default value of "all"
    limit := c.DefaultQuery("limit", "10")        // Get 'limit' with default "10"
    page := c.DefaultQuery("page", "1")           // Get 'page' with default "1"
    
    // We'll use these parameters later to build our query
    fmt.Printf("Querying products: name=%s, category=%s, limit=%s, page=%s\n", 
               name, category, limit, page)
    
    // ...database operations will follow
}

The request URL might look like: /api/products?name=keyboard&category=electronics&limit=20&page=2

Converting Query Parameters to Appropriate Types

Query parameters come as strings, so we often need to convert them to appropriate types:

go
func GetProducts(c *gin.Context) {
    // Extract and convert parameters
    name := c.Query("name")
    category := c.DefaultQuery("category", "all")
    
    // Convert string parameters to integers
    limitStr := c.DefaultQuery("limit", "10")
    limit, err := strconv.Atoi(limitStr)
    if err != nil {
        limit = 10  // Default if conversion fails
    }
    
    pageStr := c.DefaultQuery("page", "1")
    page, err := strconv.Atoi(pageStr)
    if err != nil || page < 1 {
        page = 1    // Default if conversion fails or page is less than 1
    }
    
    // Calculate offset for pagination
    offset := (page - 1) * limit
    
    // Now we have properly typed values for our database query
    // ...
}

Building SQL Queries Dynamically

Here's how to build SQL queries dynamically based on parameters:

go
func GetProducts(c *gin.Context) {
    // Extract and validate parameters...
    name := c.Query("name")
    category := c.DefaultQuery("category", "all")
    sortBy := c.DefaultQuery("sort", "id")
    order := c.DefaultQuery("order", "asc")
    
    // Create base query
    query := "SELECT * FROM products WHERE 1=1"
    var args []interface{}
    
    // Add conditionals based on parameters
    if name != "" {
        query += " AND name LIKE ?"
        args = append(args, "%" + name + "%")
    }
    
    if category != "all" {
        query += " AND category = ?"
        args = append(args, category)
    }
    
    // Validate and add sorting
    validColumns := map[string]bool{"id": true, "name": true, "price": true, "created_at": true}
    validOrders := map[string]bool{"asc": true, "desc": true}
    
    if validColumns[sortBy] && validOrders[order] {
        query += fmt.Sprintf(" ORDER BY %s %s", sortBy, order)
    } else {
        query += " ORDER BY id ASC"  // Default sort
    }
    
    // Add pagination
    limit, _ := strconv.Atoi(c.DefaultQuery("limit", "10"))
    page, _ := strconv.Atoi(c.DefaultQuery("page", "1"))
    offset := (page - 1) * limit
    
    query += " LIMIT ? OFFSET ?"
    args = append(args, limit, offset)
    
    // Execute the query
    db := database.GetDB()
    rows, err := db.Query(query, args...)
    if err != nil {
        c.JSON(http.StatusInternalServerError, gin.H{"error": "Database error"})
        return
    }
    defer rows.Close()
    
    // Process results...
}

Query Building with GORM

If you're using GORM (a popular ORM for Go), query building becomes more intuitive:

go
func GetProducts(c *gin.Context) {
    var products []models.Product
    db := database.GetDB()
    
    // Start with a base query
    query := db.Model(&models.Product{})
    
    // Apply filters
    name := c.Query("name")
    if name != "" {
        query = query.Where("name LIKE ?", "%" + name + "%")
    }
    
    category := c.DefaultQuery("category", "")
    if category != "" {
        query = query.Where("category = ?", category)
    }
    
    minPrice, err := strconv.ParseFloat(c.DefaultQuery("min_price", "0"), 64)
    if err == nil && minPrice > 0 {
        query = query.Where("price >= ?", minPrice)
    }
    
    maxPrice, err := strconv.ParseFloat(c.DefaultQuery("max_price", "0"), 64)
    if err == nil && maxPrice > 0 {
        query = query.Where("price <= ?", maxPrice)
    }
    
    // Apply sorting
    sortBy := c.DefaultQuery("sort", "id")
    order := c.DefaultQuery("order", "asc")
    
    // Validate sorting parameters (to prevent SQL injection)
    validColumns := map[string]bool{"id": true, "name": true, "price": true, "created_at": true}
    if validColumns[sortBy] {
        orderClause := sortBy
        if order == "desc" {
            orderClause += " desc"
        }
        query = query.Order(orderClause)
    }
    
    // Apply pagination
    limit, _ := strconv.Atoi(c.DefaultQuery("limit", "10"))
    page, _ := strconv.Atoi(c.DefaultQuery("page", "1"))
    if page < 1 {
        page = 1
    }
    offset := (page - 1) * limit
    
    // Count total items for pagination metadata
    var totalCount int64
    query.Count(&totalCount)
    
    // Execute the paginated query
    query.Limit(limit).Offset(offset).Find(&products)
    
    // Return response with pagination metadata
    c.JSON(http.StatusOK, gin.H{
        "data": products,
        "meta": gin.H{
            "total": totalCount,
            "page": page,
            "limit": limit,
            "total_pages": math.Ceil(float64(totalCount) / float64(limit)),
        },
    })
}

Creating Reusable Query Builder Functions

For more complex applications, consider creating reusable query building functions:

go
// QueryBuilder contains methods to construct database queries
type QueryBuilder struct {
    DB *gorm.DB
}

// NewQueryBuilder creates a new query builder with the given database connection
func NewQueryBuilder(db *gorm.DB) *QueryBuilder {
    return &QueryBuilder{DB: db}
}

// ApplyFilters adds WHERE conditions based on the provided filters
func (qb *QueryBuilder) ApplyFilters(query *gorm.DB, c *gin.Context) *gorm.DB {
    // Name filter
    if name := c.Query("name"); name != "" {
        query = query.Where("name LIKE ?", "%"+name+"%")
    }
    
    // Category filter
    if category := c.Query("category"); category != "" {
        query = query.Where("category = ?", category)
    }
    
    // Price range filter
    if minPrice, err := strconv.ParseFloat(c.DefaultQuery("min_price", "0"), 64); err == nil {
        query = query.Where("price >= ?", minPrice)
    }
    
    if maxPrice, err := strconv.ParseFloat(c.DefaultQuery("max_price", "0"), 64); err == nil && maxPrice > 0 {
        query = query.Where("price <= ?", maxPrice)
    }
    
    // Status filter
    if status := c.Query("status"); status != "" {
        query = query.Where("status = ?", status)
    }
    
    // Date range filter
    if startDate := c.Query("start_date"); startDate != "" {
        query = query.Where("created_at >= ?", startDate)
    }
    
    if endDate := c.Query("end_date"); endDate != "" {
        query = query.Where("created_at <= ?", endDate)
    }
    
    return query
}

// ApplySorting adds ORDER BY clause
func (qb *QueryBuilder) ApplySorting(query *gorm.DB, c *gin.Context, defaultSort string) *gorm.DB {
    sortBy := c.DefaultQuery("sort", defaultSort)
    order := c.DefaultQuery("order", "asc")
    
    // Define allowed columns for sorting (whitelist to prevent SQL injection)
    validColumns := map[string]bool{
        "id": true, "name": true, "price": true, 
        "created_at": true, "updated_at": true, 
    }
    
    if validColumns[sortBy] {
        if order != "asc" && order != "desc" {
            order = "asc"
        }
        query = query.Order(fmt.Sprintf("%s %s", sortBy, order))
    } else {
        query = query.Order(defaultSort)
    }
    
    return query
}

// ApplyPagination adds LIMIT and OFFSET clauses
func (qb *QueryBuilder) ApplyPagination(query *gorm.DB, c *gin.Context) (*gorm.DB, int, int) {
    limit, _ := strconv.Atoi(c.DefaultQuery("limit", "10"))
    page, _ := strconv.Atoi(c.DefaultQuery("page", "1"))
    
    if limit <= 0 {
        limit = 10 // Default limit
    }
    if limit > 100 {
        limit = 100 // Maximum limit
    }
    if page <= 0 {
        page = 1
    }
    
    offset := (page - 1) * limit
    return query.Limit(limit).Offset(offset), page, limit
}

// BuildQuery constructs a query with all common parameters
func (qb *QueryBuilder) BuildQuery(c *gin.Context, model interface{}) (*gorm.DB, int, int) {
    query := qb.DB.Model(model)
    query = qb.ApplyFilters(query, c)
    query = qb.ApplySorting(query, c, "id asc")
    query, page, limit := qb.ApplyPagination(query, c)
    return query, page, limit
}

Now you can use this reusable query builder in your handlers:

go
func GetProducts(c *gin.Context) {
    var products []models.Product
    db := database.GetDB()
    qb := NewQueryBuilder(db)
    
    // Build the query using our reusable builder
    query, page, limit := qb.BuildQuery(c, &models.Product{})
    
    // Count total for pagination metadata
    var totalCount int64
    qb.ApplyFilters(db.Model(&models.Product{}), c).Count(&totalCount)
    
    // Execute the query
    if result := query.Find(&products); result.Error != nil {
        c.JSON(http.StatusInternalServerError, gin.H{"error": "Failed to fetch products"})
        return
    }
    
    c.JSON(http.StatusOK, gin.H{
        "data": products,
        "meta": gin.H{
            "page": page,
            "limit": limit,
            "total": totalCount,
            "pages": int(math.Ceil(float64(totalCount) / float64(limit))),
        },
    })
}

Full Example: Product Search API

Let's put everything together with a complete example of a product search API:

go
package handlers

import (
    "math"
    "net/http"
    "strconv"
    
    "github.com/gin-gonic/gin"
    "github.com/yourusername/yourproject/database"
    "github.com/yourusername/yourproject/models"
)

// ProductSearchRequest represents query parameters for product search
type ProductSearchRequest struct {
    Name        string  `form:"name"`
    Category    string  `form:"category"`
    MinPrice    float64 `form:"min_price"`
    MaxPrice    float64 `form:"max_price"`
    InStock     *bool   `form:"in_stock"`
    SortBy      string  `form:"sort" binding:"omitempty,oneof=id name price created_at"`
    Order       string  `form:"order" binding:"omitempty,oneof=asc desc"`
    Page        int     `form:"page" binding:"omitempty,min=1"`
    Limit       int     `form:"limit" binding:"omitempty,min=1,max=100"`
}

// SearchProducts handles product searching with filtering, sorting and pagination
func SearchProducts(c *gin.Context) {
    var searchReq ProductSearchRequest
    
    // Bind query parameters to our struct with validation
    if err := c.ShouldBindQuery(&searchReq); err != nil {
        c.JSON(http.StatusBadRequest, gin.H{"error": "Invalid search parameters"})
        return
    }
    
    // Set defaults if not provided
    if searchReq.Limit == 0 {
        searchReq.Limit = 10
    }
    if searchReq.Page == 0 {
        searchReq.Page = 1
    }
    if searchReq.SortBy == "" {
        searchReq.SortBy = "id"
    }
    if searchReq.Order == "" {
        searchReq.Order = "asc"
    }
    
    // Start building our query
    db := database.GetDB()
    query := db.Model(&models.Product{})
    
    // Apply filters based on search request
    if searchReq.Name != "" {
        query = query.Where("name LIKE ?", "%"+searchReq.Name+"%")
    }
    
    if searchReq.Category != "" {
        query = query.Where("category = ?", searchReq.Category)
    }
    
    if searchReq.MinPrice > 0 {
        query = query.Where("price >= ?", searchReq.MinPrice)
    }
    
    if searchReq.MaxPrice > 0 {
        query = query.Where("price <= ?", searchReq.MaxPrice)
    }
    
    if searchReq.InStock != nil {
        query = query.Where("in_stock = ?", *searchReq.InStock)
    }
    
    // Count total matching records for pagination metadata
    var totalCount int64
    query.Count(&totalCount)
    
    // Apply sorting
    query = query.Order(searchReq.SortBy + " " + searchReq.Order)
    
    // Apply pagination
    offset := (searchReq.Page - 1) * searchReq.Limit
    query = query.Limit(searchReq.Limit).Offset(offset)
    
    // Execute the final query
    var products []models.Product
    if result := query.Find(&products); result.Error != nil {
        c.JSON(http.StatusInternalServerError, gin.H{"error": "Failed to search products"})
        return
    }
    
    // Calculate total pages
    totalPages := int(math.Ceil(float64(totalCount) / float64(searchReq.Limit)))
    
    // Return the response
    c.JSON(http.StatusOK, gin.H{
        "data": products,
        "meta": gin.H{
            "total": totalCount,
            "page": searchReq.Page,
            "limit": searchReq.Limit,
            "pages": totalPages,
        },
    })
}

Security Considerations

When building database queries from user input, security should always be a priority:

Prevent SQL Injection: Always use parameterized queries with placeholders (?) for user input
Validate Input: Validate and sanitize all input parameters
Use Whitelists: For sorting columns and other database identifiers, use whitelists of allowed values
Limit Query Results: Always implement pagination to prevent resource exhaustion
Protect Sensitive Data: Be careful when filtering by sensitive fields

Summary

Building effective database queries in Gin applications involves several key steps:

Extract and validate query parameters from the request
Convert string parameters to appropriate types (integers, floats, booleans)
Construct dynamic database queries based on the parameters
Apply security measures to prevent SQL injection
Implement common patterns like filtering, searching, sorting, and pagination
Return proper responses with metadata for clients

By following these patterns and best practices, you can create flexible, secure, and efficient API endpoints that interact with your database while providing a great developer experience for API consumers.

Additional Resources

Exercises

Create a Gin endpoint that allows searching users by name, email, and role, with sorting and pagination.
Implement a product filtering system that includes price range, category, and availability filters.
Build an advanced search API that supports fuzzy matching of text fields.
Create a report generator that uses query parameters to filter data and return aggregated results.
Implement a query builder that handles complex nested conditions (e.g., "category = 'books' AND (price < 20 OR author = 'Smith')").

If you spot any mistakes on this website, please let me know at [email protected]. I’d greatly appreciate your feedback! :)

Introduction​

Basic Query Parameter Extraction​

Converting Query Parameters to Appropriate Types​

Building SQL Queries Dynamically​

Query Building with GORM​

Creating Reusable Query Builder Functions​

Full Example: Product Search API​

Security Considerations​

Summary​

Additional Resources​

Exercises​