Published in DataTables
Prasomsak Khunmuen

Create an API for DataTables with Go

DataTables is a popular jQuery plugin that offers features like pagination, searching, and sorting, making it easy to handle large datasets. This article will demonstrate how to create an Go API to work with the DataTables. What are the parameters that DataTables sends to our API and the requirements of the data that DataTables needs.

To deal with DataTables, you need to understand the information that DataTables will send to the API through the query string.

draw = 1
columns[0][data] = id
columns[0][name] = 
columns[0][searchable] = true
columns[0][orderable] = true
columns[0][search][value] = 
columns[0][search][regex] = false
columns[1][data] = name
columns[1][name] = 
columns[1][searchable] = true
columns[1][orderable] = true
columns[1][search][value] = 
columns[1][search][regex] = false
columns[2][data] = price
columns[2][name] = 
columns[2][searchable] = true
columns[2][orderable] = true
columns[2][search][value] = 
columns[2][search][regex] = false
order[0][column] = 0
order[0][dir] = asc
order[0][name] = 
start = 0
length = 10
search[value] = 
search[regex] = false
  • draw the request ID that is used to synchronize between the client and server.
  • columns[x][data] the column's field name that we define on the client-side.
  • order[0] the sorting information.
  • start the start index of the record.
  • length the length per page (page size).
  • search[value] the search value information.

The DataTables expected data will require these information.

draw DataTables sends this ID to us, and we just send it back.
recordsTotal Total records number before filtering.
recordsFiltered Total records number after filtering.
data The records data.

Prerequisites

  • Go 1.21
  • MySQL

Setup project

Setting up the Go project dependencies.

go mod init app
go get github.com/gin-gonic/gin
go get gorm.io/gorm
go get gorm.io/driver/mysql
go get github.com/joho/godotenv

Create a testing database named "example" and run the database.sql file to import the table and data.

Project structure

├─ .env
├─ main.go
├─ config
│  └─ db.go
├─ controllers
│  └─ product_controller.go
├─ models
│  └─ product.go
├─ public
│  └─ index.html
└─ router
   └─ router.go

Project files

.env

This file contains the database connection information.

DB_HOST=localhost
DB_PORT=3306
DB_DATABASE=example
DB_USER=root
DB_PASSWORD=

db.go

This file sets up the database connection using GORM. It declares a global variable DB to hold the database connection instance to use later in our application.

package config

import (
	"fmt"
	"os"

	"github.com/joho/godotenv"
	"gorm.io/driver/mysql"
	"gorm.io/gorm"
	"gorm.io/gorm/schema"
)

var DB *gorm.DB

func SetupDatabase() {
	godotenv.Load()
	connection := fmt.Sprintf("%s:%s@tcp(%s:%s)/%s?charset=utf8mb4&parseTime=true", os.Getenv("DB_USER"), os.Getenv("DB_PASSWORD"), os.Getenv("DB_HOST"), os.Getenv("DB_PORT"), os.Getenv("DB_DATABASE"))
	db, _ := gorm.Open(mysql.Open(connection), &gorm.Config{NamingStrategy: schema.NamingStrategy{SingularTable: true}})
	DB = db
}

router.go

This file sets up routing for a Gin web application. It initializes a router for a DataTables API and serves a static index.html file at the root URL.

package router

import (
	"app/controllers"

	"github.com/gin-gonic/gin"
)

func SetupRouter() {
	productController := controllers.ProductController{}
	router := gin.Default()
	router.StaticFile("/", "./public/index.html")
	router.GET("/api/products", productController.Index)
	router.Run()
}

product.go

This file defines the Product model for the application.

package models

type Product struct {
	Id    int
	Name  string
	Price float64
}

product_controller.go

This file defines a function to handle incoming requests and return the DataTables data.

package controllers

import (
	"app/config"
	"app/models"
	"net/http"
	"strconv"

	"github.com/gin-gonic/gin"
)

type ProductController struct {
}

func (con *ProductController) Index(c *gin.Context) {
	size, _ := strconv.Atoi(c.DefaultQuery("length", "10"))
	start, _ := strconv.Atoi(c.Query("start"))
	order := "id"
	if c.Query("order[0][column]") != "" {
		order = c.Query("columns[" + c.Query("order[0][column]") + "][data]")
	}
	direction := c.DefaultQuery("order[0][dir]", "asc")
	var products []models.Product
	query := config.DB.Model(&products)
	var recordsTotal, recordsFiltered int64
	query.Count(&recordsTotal)
	search := c.Query("search[value]")
	if search != "" {
		search = "%" + search + "%"
		query.Where("name like ?", search)
	}
	query.Count(&recordsFiltered)
	query.Order(order + " " + direction).
		Offset(start).
		Limit(size).
		Find(&products)
	c.JSON(http.StatusOK, gin.H{"draw": c.Query("draw"), "recordsTotal": recordsTotal, "recordsFiltered": recordsFiltered, "data": products})
}
  • We utilize the query string to get page, start, order, direction information to create the paginated data by using the Limit and Offset method of the GORM object.
  • We return all DataTables required information including: draw, recordsTotal, recordsFiltered, data as object.

main.go

This file is the main entry point of our application. It will create and setting up the Gin web application.

package main

import (
	"app/config"
	"app/router"
)

func main() {
	config.SetupDatabase()
	router.SetupRouter()
}

index.html

This file will be used to setup the DataTables HTML and JavaScript to work with our API.

<!DOCTYPE html>
<head>
    <link rel="stylesheet" href="https://cdn.datatables.net/2.0.7/css/dataTables.dataTables.min.css">
</head>
<body>
    <table id="table" class="display">
        <thead>
            <td>id</td>
            <th>name</th>
            <th>price</th>
        </thead>
    </table>
    <script src="https://code.jquery.com/jquery-3.7.1.min.js"></script>
    <script src="https://cdn.datatables.net/2.0.7/js/dataTables.min.js"></script>
    <script>
        new DataTable('#table', {
            ajax: '/api/products',
            processing: true,
            serverSide: true,
            columns: [
                { data: 'Id' },
                { data: 'Name' },
                { data: 'Price' }
            ]
        })
    </script>
</body>
</html>
  • processing: true show a loading indicator when making the request.
  • serverSide: true makes the request to the server (API) for all operations.

Run project

go run main.go

Open the web browser and goto http://localhost:8080
You will find this test page.

test page

Testing

Page size test

Change page size by selecting 25 from the "entries per page" drop-down. You will get 25 records per page, and the last page will change from 10 to 4.

page size test

Sorting test

Click on the header of the first column. You will see that the id column will be sorted in descending order.

sorting test

Search test

Enter "no" in the search text-box, and you will see the filtered result data.

search test

Conclusion

In this article, you have learned how to create an Go API to work with the DataTables. Understand all the DataTables parameters sent to the API and utilize them to produce the appropriate data and send it back. You also learn how to setup the DataTables on the client-side using HTML and JavaScript. I hope this article will help you incorporate them into your next project.

Source code: https://github.com/stackpuz/Example-DataTables-Go

Create a CRUD Web App in Minutes: https://stackpuz.com

Related post

Recommended Topics

StackPuz is the Web App Code Generator. It will help you bootstrap a new project FASTER. Please check out it on stackpuz.com