Building a Complete Golang REST API with MySQL

Golang (Go) is a fast, statically typed language designed for modern backend development. In this tutorial, we’ll create a complete REST API in Go with MySQL integration and demonstrate unit testing to ensure our API works correctly. By the end, you’ll have a full CRUD API ready to extend for real-world applications.

Why Build a REST API in Go?

Go is ideal for backend APIs because:

Performance: Compiled language with low latency.
Concurrency: Handles multiple requests efficiently with goroutines.
Simplicity: Minimal boilerplate, clear syntax.
Scalability: Perfect for microservices and cloud-native apps.

Adding MySQL integration and unit tests ensures your API is reliable and production-ready.

Project Overview

We’ll build a User API with the following features:

CRUD operations: Create, Read, Update, Delete users
MySQL database integration
JSON API responses
Unit tests using Go’s testing package

Project Structure:

golang-mysql-api/
│
├── main.go
├── go.mod
├── db/
│   └── db.go
├── models/
│   └── user.go
├── handlers/
│   └── user_handler.go
└── tests/
    └── user_test.go

Step 1: Initialize Go Module

go mod init golang-mysql-api
go get -u github.com/go-sql-driver/mysql
go get -u github.com/gorilla/mux

This sets up your project and installs the necessary dependencies for MySQL and HTTP routing.

Step 2: Connect to MySQL (`db/db.go`)

package db

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

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

var DB *sql.DB

func Connect() {
	var err error
	dsn := "root:password@tcp(127.0.0.1:3306)/golang_api"
	DB, err = sql.Open("mysql", dsn)
	if err != nil {
		log.Fatal("Database connection failed: ", err)
	}

	err = DB.Ping()
	if err != nil {
		log.Fatal("Database ping failed: ", err)
	}

	fmt.Println("Database connected successfully")
}

This file handles connecting to your MySQL database. Make sure to update the dsn with your credentials.

Step 3: Define the User Model (`models/user.go`)

package models

type User struct {
	ID    int    `json:"id"`
	Name  string `json:"name"`
	Email string `json:"email"`
}

We define a simple User struct to represent our database table.

Step 4: Create API Handlers (`handlers/user_handler.go`)

package handlers

import (
	"encoding/json"
	"net/http"
	"strconv"

	"github.com/gorilla/mux"
	"golang-mysql-api/db"
	"golang-mysql-api/models"
)

func GetUsers(w http.ResponseWriter, r *http.Request) {
	rows, err := db.DB.Query("SELECT id, name, email FROM users")
	if err != nil {
		http.Error(w, err.Error(), http.StatusInternalServerError)
		return
	}
	defer rows.Close()

	users := []models.User{}
	for rows.Next() {
		var user models.User
		rows.Scan(&user.ID, &user.Name, &user.Email)
		users = append(users, user)
	}

	json.NewEncoder(w).Encode(users)
}

func GetUser(w http.ResponseWriter, r *http.Request) {
	params := mux.Vars(r)
	id, _ := strconv.Atoi(params["id"])

	var user models.User
	err := db.DB.QueryRow("SELECT id, name, email FROM users WHERE id=?", id).Scan(&user.ID, &user.Name, &user.Email)
	if err != nil {
		http.Error(w, "User not found", http.StatusNotFound)
		return
	}

	json.NewEncoder(w).Encode(user)
}

func CreateUser(w http.ResponseWriter, r *http.Request) {
	var user models.User
	json.NewDecoder(r.Body).Decode(&user)

	res, err := db.DB.Exec("INSERT INTO users(name, email) VALUES(?, ?)", user.Name, user.Email)
	if err != nil {
		http.Error(w, err.Error(), http.StatusInternalServerError)
		return
	}

	id, _ := res.LastInsertId()
	user.ID = int(id)
	json.NewEncoder(w).Encode(user)
}

func UpdateUser(w http.ResponseWriter, r *http.Request) {
	params := mux.Vars(r)
	id, _ := strconv.Atoi(params["id"])

	var user models.User
	json.NewDecoder(r.Body).Decode(&user)

	_, err := db.DB.Exec("UPDATE users SET name=?, email=? WHERE id=?", user.Name, user.Email, id)
	if err != nil {
		http.Error(w, err.Error(), http.StatusInternalServerError)
		return
	}

	user.ID = id
	json.NewEncoder(w).Encode(user)
}

func DeleteUser(w http.ResponseWriter, r *http.Request) {
	params := mux.Vars(r)
	id, _ := strconv.Atoi(params["id"])

	_, err := db.DB.Exec("DELETE FROM users WHERE id=?", id)
	if err != nil {
		http.Error(w, err.Error(), http.StatusInternalServerError)
		return
	}

	w.WriteHeader(http.StatusNoContent)
}

Step 5: Main File (`main.go`)

package main

import (
	"fmt"
	"log"
	"net/http"

	"github.com/gorilla/mux"
	"golang-mysql-api/db"
	"golang-mysql-api/handlers"
)

func main() {
	db.Connect()

	router := mux.NewRouter()

	router.HandleFunc("/users", handlers.GetUsers).Methods("GET")
	router.HandleFunc("/users/{id}", handlers.GetUser).Methods("GET")
	router.HandleFunc("/users", handlers.CreateUser).Methods("POST")
	router.HandleFunc("/users/{id}", handlers.UpdateUser).Methods("PUT")
	router.HandleFunc("/users/{id}", handlers.DeleteUser).Methods("DELETE")

	fmt.Println("Server running on port 8080")
	log.Fatal(http.ListenAndServe(":8080", router))
}

Step 6: MySQL Setup

CREATE DATABASE golang_api;
USE golang_api;

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
);

Step 7: Writing Unit Tests (`tests/user_test.go`)

package tests

import (
	"bytes"
	"encoding/json"
	"net/http"
	"net/http/httptest"
	"testing"

	"github.com/gorilla/mux"
	"golang-mysql-api/handlers"
)

func TestCreateUser(t *testing.T) {
	user := map[string]string{"name": "Alice", "email": "alice@example.com"}
	body, _ := json.Marshal(user)

	req, _ := http.NewRequest("POST", "/users", bytes.NewBuffer(body))
	rr := httptest.NewRecorder()
	handler := http.HandlerFunc(handlers.CreateUser)
	handler.ServeHTTP(rr, req)

	if status := rr.Code; status != http.StatusOK {
		t.Errorf("Expected status 200, got %v", status)
	}
}

func TestGetUsers(t *testing.T) {
	req, _ := http.NewRequest("GET", "/users", nil)
	rr := httptest.NewRecorder()
	handler := http.HandlerFunc(handlers.GetUsers)
	handler.ServeHTTP(rr, req)

	if status := rr.Code; status != http.StatusOK {
		t.Errorf("Expected status 200, got %v", status)
	}
}

func TestGetUserNotFound(t *testing.T) {
	req, _ := http.NewRequest("GET", "/users/9999", nil)
	rr := httptest.NewRecorder()

	router := mux.NewRouter()
	router.HandleFunc("/users/{id}", handlers.GetUser)
	router.ServeHTTP(rr, req)

	if status := rr.Code; status != http.StatusNotFound {
		t.Errorf("Expected status 404, got %v", status)
	}
}

Tips

Always use Go modules (go mod) for dependency management.
Use httptest to simulate requests for unit testing APIs.
Validate user input in production to prevent SQL injection.
Use environment variables for DB credentials instead of hardcoding.

Contact Info