Gin MySQL Integration

In this tutorial, we'll explore how to integrate a MySQL database with the Gin web framework in Go. This powerful combination allows you to build robust web applications that can store and retrieve data persistently.

Introduction

Integrating a database is essential for most web applications, as it enables data persistence beyond the application's runtime. MySQL is one of the most popular open-source relational database management systems, and when combined with Gin's speed and flexibility, it creates an excellent foundation for web development.

By the end of this tutorial, you'll understand how to:

Connect to a MySQL database from a Gin application
Perform CRUD (Create, Read, Update, Delete) operations
Structure your Gin application with database integration
Handle common database operations in API endpoints

Prerequisites

Before we begin, make sure you have:

Go installed (version 1.16 or later recommended)
Basic knowledge of the Gin framework
MySQL server installed and running
A MySQL client driver for Go

Setting Up the Project

Let's start by creating a new project and installing the necessary dependencies:

bash
mkdir gin-mysql-demo
cd gin-mysql-demo
go mod init gin-mysql-demo
go get -u github.com/gin-gonic/gin
go get -u github.com/go-sql-driver/mysql

Database Connection Setup

First, let's create a database connection module that we can use throughout our application:

go
// db/db.go
package db

import (
	"database/sql"
	"fmt"
	"log"
	"time"

	_ "github.com/go-sql-driver/mysql"
)

var DB *sql.DB

// InitDB establishes a connection to the MySQL database
func InitDB() {
	var err error
	
	// Configure the database connection
	// Format: username:password@tcp(host:port)/database_name
	dsn := "root:password@tcp(127.0.0.1:3306)/gin_demo?parseTime=true"
	
	// Open a connection to the database
	DB, err = sql.Open("mysql", dsn)
	if err != nil {
		log.Fatalf("Failed to connect to database: %v", err)
	}
	
	// Configure connection pool settings
	DB.SetMaxOpenConns(10)
	DB.SetMaxIdleConns(5)
	DB.SetConnMaxLifetime(time.Hour)
	
	// Test the connection
	err = DB.Ping()
	if err != nil {
		log.Fatalf("Failed to ping database: %v", err)
	}
	
	fmt.Println("Connected to MySQL database successfully")
}

Creating the Database Schema

Before proceeding, let's create our database and table. Execute the following SQL commands in your MySQL client:

sql
CREATE DATABASE IF NOT EXISTS gin_demo;

USE gin_demo;

CREATE TABLE IF NOT EXISTS books (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    author VARCHAR(255) NOT NULL,
    published_year INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Defining Models

Next, let's define a model for our data:

go
// models/book.go
package models

import (
	"time"
)

type Book struct {
	ID            int       `json:"id"`
	Title         string    `json:"title"`
	Author        string    `json:"author"`
	PublishedYear int       `json:"published_year"`
	CreatedAt     time.Time `json:"created_at"`
	UpdatedAt     time.Time `json:"updated_at"`
}

Creating Repository Functions

Now, let's create functions to interact with our database:

go
// repositories/book_repository.go
package repositories

import (
	"gin-mysql-demo/db"
	"gin-mysql-demo/models"
)

// GetAllBooks retrieves all books from the database
func GetAllBooks() ([]models.Book, error) {
	query := `SELECT id, title, author, published_year, created_at, updated_at FROM books`
	
	rows, err := db.DB.Query(query)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	
	var books []models.Book
	
	for rows.Next() {
		var book models.Book
		if err := rows.Scan(&book.ID, &book.Title, &book.Author, &book.PublishedYear, &book.CreatedAt, &book.UpdatedAt); err != nil {
			return nil, err
		}
		books = append(books, book)
	}
	
	if err = rows.Err(); err != nil {
		return nil, err
	}
	
	return books, nil
}

// GetBookByID retrieves a book by its ID
func GetBookByID(id int) (models.Book, error) {
	var book models.Book
	
	query := `SELECT id, title, author, published_year, created_at, updated_at FROM books WHERE id = ?`
	
	err := db.DB.QueryRow(query, id).Scan(
		&book.ID, &book.Title, &book.Author, &book.PublishedYear, &book.CreatedAt, &book.UpdatedAt,
	)
	
	return book, err
}

// CreateBook adds a new book to the database
func CreateBook(book models.Book) (int64, error) {
	query := `INSERT INTO books (title, author, published_year) VALUES (?, ?, ?)`
	
	result, err := db.DB.Exec(query, book.Title, book.Author, book.PublishedYear)
	if err != nil {
		return 0, err
	}
	
	id, err := result.LastInsertId()
	if err != nil {
		return 0, err
	}
	
	return id, nil
}

// UpdateBook updates an existing book in the database
func UpdateBook(id int, book models.Book) error {
	query := `UPDATE books SET title = ?, author = ?, published_year = ? WHERE id = ?`
	
	_, err := db.DB.Exec(query, book.Title, book.Author, book.PublishedYear, id)
	return err
}

// DeleteBook removes a book from the database
func DeleteBook(id int) error {
	query := `DELETE FROM books WHERE id = ?`
	
	_, err := db.DB.Exec(query, id)
	return err
}

Creating API Handlers

Now, let's implement the API handlers to handle HTTP requests:

go
// handlers/book_handler.go
package handlers

import (
	"database/sql"
	"gin-mysql-demo/models"
	"gin-mysql-demo/repositories"
	"net/http"
	"strconv"

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

// GetBooks handles GET request to fetch all books
func GetBooks(c *gin.Context) {
	books, err := repositories.GetAllBooks()
	if err != nil {
		c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()})
		return
	}
	
	c.JSON(http.StatusOK, books)
}

// GetBook handles GET request to fetch a single book
func GetBook(c *gin.Context) {
	idStr := c.Param("id")
	id, err := strconv.Atoi(idStr)
	if err != nil {
		c.JSON(http.StatusBadRequest, gin.H{"error": "Invalid ID format"})
		return
	}
	
	book, err := repositories.GetBookByID(id)
	if err != nil {
		if err == sql.ErrNoRows {
			c.JSON(http.StatusNotFound, gin.H{"error": "Book not found"})
			return
		}
		c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()})
		return
	}
	
	c.JSON(http.StatusOK, book)
}

// CreateBook handles POST request to add a new book
func CreateBook(c *gin.Context) {
	var book models.Book
	
	if err := c.ShouldBindJSON(&book); err != nil {
		c.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
		return
	}
	
	id, err := repositories.CreateBook(book)
	if err != nil {
		c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()})
		return
	}
	
	book.ID = int(id)
	c.JSON(http.StatusCreated, book)
}

// UpdateBook handles PUT request to update a book
func UpdateBook(c *gin.Context) {
	idStr := c.Param("id")
	id, err := strconv.Atoi(idStr)
	if err != nil {
		c.JSON(http.StatusBadRequest, gin.H{"error": "Invalid ID format"})
		return
	}
	
	var book models.Book
	if err := c.ShouldBindJSON(&book); err != nil {
		c.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
		return
	}
	
	// Check if book exists
	_, err = repositories.GetBookByID(id)
	if err != nil {
		if err == sql.ErrNoRows {
			c.JSON(http.StatusNotFound, gin.H{"error": "Book not found"})
			return
		}
		c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()})
		return
	}
	
	if err = repositories.UpdateBook(id, book); err != nil {
		c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()})
		return
	}
	
	book.ID = id
	c.JSON(http.StatusOK, book)
}

// DeleteBook handles DELETE request to remove a book
func DeleteBook(c *gin.Context) {
	idStr := c.Param("id")
	id, err := strconv.Atoi(idStr)
	if err != nil {
		c.JSON(http.StatusBadRequest, gin.H{"error": "Invalid ID format"})
		return
	}
	
	// Check if book exists
	_, err = repositories.GetBookByID(id)
	if err != nil {
		if err == sql.ErrNoRows {
			c.JSON(http.StatusNotFound, gin.H{"error": "Book not found"})
			return
		}
		c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()})
		return
	}
	
	if err = repositories.DeleteBook(id); err != nil {
		c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()})
		return
	}
	
	c.JSON(http.StatusOK, gin.H{"message": "Book deleted successfully"})
}

Setting Up Routes

Now let's tie everything together in our main application:

go
// main.go
package main

import (
	"gin-mysql-demo/db"
	"gin-mysql-demo/handlers"

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

func main() {
	// Initialize database connection
	db.InitDB()
	defer db.DB.Close()
	
	// Create a new Gin router
	r := gin.Default()
	
	// Define API routes
	r.GET("/books", handlers.GetBooks)
	r.GET("/books/:id", handlers.GetBook)
	r.POST("/books", handlers.CreateBook)
	r.PUT("/books/:id", handlers.UpdateBook)
	r.DELETE("/books/:id", handlers.DeleteBook)
	
	// Start the server
	r.Run(":8080") // Listen and serve on 0.0.0.0:8080
}

Testing the API

Once your application is running, you can test the API using tools like curl, Postman, or any API client.

Adding a Book (POST)

Request:

bash
curl -X POST http://localhost:8080/books \
  -H "Content-Type: application/json" \
  -d '{"title":"The Go Programming Language","author":"Alan A. A. Donovan & Brian W. Kernighan","published_year":2015}'

Response:

json
{
  "id": 1,
  "title": "The Go Programming Language",
  "author": "Alan A. A. Donovan & Brian W. Kernighan",
  "published_year": 2015,
  "created_at": "0001-01-01T00:00:00Z",
  "updated_at": "0001-01-01T00:00:00Z"
}

Retrieving All Books (GET)

Request:

bash
curl http://localhost:8080/books

Response:

json
[
  {
    "id": 1,
    "title": "The Go Programming Language",
    "author": "Alan A. A. Donovan & Brian W. Kernighan",
    "published_year": 2015,
    "created_at": "2023-04-10T15:24:32Z",
    "updated_at": "2023-04-10T15:24:32Z"
  }
]

Retrieving a Specific Book (GET)

Request:

bash
curl http://localhost:8080/books/1

Response:

json
{
  "id": 1,
  "title": "The Go Programming Language",
  "author": "Alan A. A. Donovan & Brian W. Kernighan",
  "published_year": 2015,
  "created_at": "2023-04-10T15:24:32Z",
  "updated_at": "2023-04-10T15:24:32Z"
}

Updating a Book (PUT)

Request:

bash
curl -X PUT http://localhost:8080/books/1 \
  -H "Content-Type: application/json" \
  -d '{"title":"The Go Programming Language","author":"Alan A. A. Donovan & Brian W. Kernighan","published_year":2016}'

Response:

json
{
  "id": 1,
  "title": "The Go Programming Language",
  "author": "Alan A. A. Donovan & Brian W. Kernighan",
  "published_year": 2016,
  "created_at": "0001-01-01T00:00:00Z",
  "updated_at": "0001-01-01T00:00:00Z"
}

Deleting a Book (DELETE)

Request:

bash
curl -X DELETE http://localhost:8080/books/1

Response:

json
{
  "message": "Book deleted successfully"
}

Improving the Application

Using Prepared Statements

For better security and performance, you can use prepared statements for database operations:

go
// Example of prepared statement usage
func CreateBookWithPreparedStmt(book models.Book) (int64, error) {
    stmt, err := db.DB.Prepare(`INSERT INTO books (title, author, published_year) VALUES (?, ?, ?)`)
    if err != nil {
        return 0, err
    }
    defer stmt.Close()
    
    result, err := stmt.Exec(book.Title, book.Author, book.PublishedYear)
    if err != nil {
        return 0, err
    }
    
    return result.LastInsertId()
}

Implementing Transactions

For operations that require multiple database changes that should succeed or fail together:

go
func BatchCreateBooks(books []models.Book) error {
    tx, err := db.DB.Begin()
    if err != nil {
        return err
    }
    
    // Prepare the statement within the transaction
    stmt, err := tx.Prepare(`INSERT INTO books (title, author, published_year) VALUES (?, ?, ?)`)
    if err != nil {
        tx.Rollback()
        return err
    }
    defer stmt.Close()
    
    for _, book := range books {
        _, err := stmt.Exec(book.Title, book.Author, book.PublishedYear)
        if err != nil {
            tx.Rollback()
            return err
        }
    }
    
    // Commit the transaction
    return tx.Commit()
}

Using Environment Variables

For better security and configuration flexibility, use environment variables:

go
// db/db.go
func InitDB() {
    var (
        dbUser = getEnv("DB_USER", "root")
        dbPass = getEnv("DB_PASSWORD", "password")
        dbHost = getEnv("DB_HOST", "127.0.0.1")
        dbPort = getEnv("DB_PORT", "3306")
        dbName = getEnv("DB_NAME", "gin_demo")
    )
    
    dsn := fmt.Sprintf("%s:%s@tcp(%s:%s)/%s?parseTime=true", 
        dbUser, dbPass, dbHost, dbPort, dbName)
    
    // ... rest of the function remains the same
}

func getEnv(key, fallback string) string {
    if value, exists := os.LookupEnv(key); exists {
        return value
    }
    return fallback
}

Real-World Application: Book Management System

Now that we've covered the basics, let's look at how this integration forms the foundation of a real-world book management system:

User Authentication: Extend your database to include user accounts and JWT authentication
Role-Based Access: Add permissions for different user roles (admin, librarian, member)
Book Categories: Create relationships between books and categories
Book Lending: Track book borrowing history, due dates, and availability
Search Functionality: Add search endpoints with filtering options
Pagination: Implement pagination for listing large numbers of books

Summary

In this tutorial, we've learned how to:

Set up a MySQL database connection in a Gin application
Create models to represent database tables
Implement repository functions for database operations
Create REST API handlers for CRUD operations
Test the API with HTTP requests
Improve the application with prepared statements and transactions

Integrating MySQL with Gin gives you a powerful foundation for building data-driven web applications in Go. The combination provides excellent performance characteristics and a clean, maintainable codebase structure.

Additional Resources

Exercises

Add pagination support to the GetBooks handler
Implement a search endpoint that can filter books by title, author, or published year
Add validation to ensure that book titles and authors are not empty
Extend the application to include a "category" field for books
Implement soft deletion (marking records as deleted instead of physically removing them)
Create a middleware for request logging that includes the execution time of database operations

By completing these exercises, you'll gain a deeper understanding of how to work with MySQL and Gin in your applications!

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

Introduction​

Prerequisites​

Setting Up the Project​

Database Connection Setup​

Creating the Database Schema​

Defining Models​

Creating Repository Functions​

Creating API Handlers​

Setting Up Routes​

Testing the API​

Adding a Book (POST)​

Retrieving All Books (GET)​

Retrieving a Specific Book (GET)​

Updating a Book (PUT)​

Deleting a Book (DELETE)​

Improving the Application​

Using Prepared Statements​

Implementing Transactions​

Using Environment Variables​

Real-World Application: Book Management System​

Summary​

Additional Resources​

Exercises​

Introduction

Prerequisites

Setting Up the Project

Database Connection Setup

Creating the Database Schema

Defining Models

Creating Repository Functions

Creating API Handlers

Setting Up Routes

Testing the API

Adding a Book (POST)

Retrieving All Books (GET)

Retrieving a Specific Book (GET)

Updating a Book (PUT)

Deleting a Book (DELETE)

Improving the Application

Using Prepared Statements

Implementing Transactions

Using Environment Variables

Real-World Application: Book Management System

Summary

Additional Resources

Exercises