Implementing SQL Queries with GORM in Gin

Welcome to this lesson on implementing SQL queries with GORM in Gin. In our previous lesson, we explored how to persist data in a ToDo app by introducing a database with the help of GORM and SQLite. Now, we'll build on that foundation by diving deeper into how SQL queries function in the context of our application. Using SQL queries, we can manage and analyze the large volumes of data that a web application typically handles. Throughout this lesson, we'll demonstrate how GORM simplifies these queries, allowing us to use Go's familiar syntax to interact with our databases.

Understanding SQL is crucial for efficiently interacting with and managing data in databases. SQL, or Structured Query Language, is a powerful language used for storing, manipulating, and retrieving data held in a relational database. It allows us to perform a variety of tasks, such as querying data, updating records, and managing database structures.

Key clauses in SQL relevant to our lesson include:

• SELECT: Used to retrieve data (rows) from one or more tables.
• FROM: Specifies the table from which to retrieve the data.
• WHERE: Filters rows based on specified conditions.
• COUNT: Returns the number of rows that match a specified condition.

The following is a simple SQL query that demonstrates the use of these clauses:

SELECT COUNT(*)
FROM todos
WHERE due_date <= DATE('now', '+7 days') AND completed = false;

This query selects all rows from the todos table where the due_date is within the next 7 days and the task is not yet completed. It then counts these rows with COUNT(*) to give us the number of pending tasks due soon.

GORM stands out as a powerful ORM (Object-Relational Mapping) library that helps manage database operations in Go applications. Rather than writing raw SQL queries, GORM lets us interact with the database using Go code, which offers significant advantages in terms of simplicity and maintaining cleaner code.

Here’s an example of how GORM abstracts SQL complexities:

• Filtering Records: Instead of writing raw SQL, we can use db.Where() as a method to filter records based on conditions.
• Counting Records: GORM's db.Count() offers a simple way to count records matching a given condition without crafting complex SQL statements.

Using these methods, GORM helps you avoid potential pitfalls of manual SQL manipulation and reduces the chances of syntax errors.

Let's implement some SQL queries in our ToDo application using GORM! We start by retrieving todos with a due date within the next week:

todoapp/router/router.go:

package router

import (
    "github.com/gin-gonic/gin"
    "gorm.io/gorm"
    
    "codesignal.com/todoapp/controllers"
)

func RegisterRoutes(router *gin.Engine, db *gorm.DB) {
    router.GET("/api/todos/due-soon", func(c *gin.Context) {
        controllers.GetTodosDueSoonHandler(c, db)
    })
}

todoapp/controllers/controllers.go:

package controllers

import (
    "net/http"
    
    "github.com/gin-gonic/gin"
    "gorm.io/gorm"
    
    "codesignal.com/todoapp/services"
)

func GetTodosDueSoonHandler(c *gin.Context, db *gorm.DB) {
    todos := services.GetTodosDueSoon(db)
    c.JSON(http.StatusOK, todos)
}

todoapp/services/services.go:

package services

import (
    "time"
    "gorm.io/gorm"
    
    "codesignal.com/todoapp/models"
    "codesignal.com/todoapp/repositories/todo_repository"
)

func GetTodosDueSoon(db *gorm.DB) []models.Todo {
    nextWeek := time.Now().AddDate(0, 0, 7)
    return todo_repository.FindTodosDueSoon(db, nextWeek)
}

todoapp/repositories/todo_repository/todo_repository.go:

package todo_repository

import (
    "time"
    
    "gorm.io/gorm"

    "codesignal.com/todoapp/models"
)

func FindTodosDueSoon(db *gorm.DB, nextWeek time.Time) []models.Todo {
    var todos []models.Todo
    db.Where("due_date <= ? AND completed = ?", nextWeek, false).Find(&todos)
    return todos
}

In the above snippets:

• In the GetTodosDueSoon service function, we calculate the nextWeek date using time.Now().AddDate(0, 0, 7).
• Low-level database operations are handled in todo_repository.go.
• We use db.Where() to filter todos with a due date within the next week that aren't completed.
• The Find(&todos) method then retrieves these entries, and we send them as a JSON response.

Next, we’ll create a route to provide an overview of the todo completion status:

todoapp/router/router.go (continued...):

func RegisterRoutes(router *gin.Engine, db *gorm.DB) {
    //... (other route registrations)
    
    router.GET("/api/todos/overview", func(c *gin.Context) {
        controllers.GetOverviewHandler(c, db)
    })
}

todoapp/controllers/controllers.go (continued...):

func GetOverviewHandler(c *gin.Context, db *gorm.DB) {
    overview := services.GetOverview(db)
    c.JSON(http.StatusOK, overview)
}

todoapp/services/services.go (continued...):

func GetOverview(db *gorm.DB) map[string]int64 {
    total, completed := todo_repository.GetTodoOverview(db)
    return map[string]int64{
        "totalTodos": total,
        "completed":  completed,
        "incomplete": total - completed,
    }
}

todoapp/repositories/todo_repository/todo_repository.go (continued...):

func GetTodoOverview(db *gorm.DB) (int64, int64) {
    var total, completed int64
    db.Model(&models.Todo{}).Count(&total)
    db.Model(&models.Todo{}).Where("completed = ?", true).Count(&completed)
    return total, completed
}

Let's focus on the database operations carried out in todo_repository.go:

• We use db.Model(&models.Todo{}).Count(&total) to count all the todos in the database.
• db.Model(&models.Todo{}) explicitly sets the model to work with, which ensures that GORM identifies the correct table for aggregate functions like Count().
• We then filter with Where("completed = ?", true).Count(&completed) to count only the completed todos.
• Finally, the overview showing total, completed, and incomplete todos is sent as a JSON response.

In this lesson, you learned how to implement SQL queries in a Gin web application using GORM. We explored key SQL clauses like WHERE and COUNT and saw how GORM simplifies these queries through methods like Where() and Count(). GORM helps us to write more readable and maintainable code.

Having now developed these essential querying skills, you're ready to apply them in the practice exercises that follow. These exercises will reinforce what you've learned and enhance your ability to interact with databases effectively. Keep practicing to strengthen your understanding and advance towards becoming proficient in building web applications with Go and Gin.