A Beginner-friendly Approach to Developing a REST API with Go, Gin and MSQL

Mastering Go is an effort worth sticking to. It is challenging when you attempt to learn everything in a day. The beginner-friend tutorial series is an honest effort to teach key concepts in bitwise and practical ways. This is the continuation and second part of this tutorial. See the first part here: A Beginner-friendly Approach to Developing a REST API with Go and Gin.

What We are building on

Here is our current code and structure:

|- go.mod
|- go.sum
|- main.go
|- readme.md

main.go/

1 package main
2
3 import (
4   "github.com/gin-gonic/gin"
5   "net/http"
6 )
7
8 // book struct represents data about a book record.
9 type book struct {
10  ID     string  `json:"id"`
11  Title  string  `json:"title"`
12  Author string  `json:"author"`
13  Price  float64 `json:"price"`
14 }
15
16 // books slice to seed record to book store.
17 var books = []book{
18  {ID: "1", Title: "A Day in the Life of Abed Salama", 
19 Author: "Nathan Thrall", Price: 56.99},
20  {ID: "2", Title: "King: A life", Author: "Jonathan 
21 Eig", Price: 56.99},
22  {ID: "3", Title: "Where we go from here", Author: 
23 "Bernie Sanders", Price: 17.99},
24  {ID: "4", Title: "Buiding a dream server", Author: 
25 "Yiga ue", Price: 39.99},
26  {ID: "5", Title: "Clean Code ", Author: "Robert C 
27 Martin", Price: 39.99},
28 }
29
30 // getBooks responds with the list of all books as json
31 func getBooks(c *gin.Context) {
32  c.IndentedJSON(http.StatusOK, books)
33 }
34
35 func main() {
36  router := gin.Default()
37  router.GET("/books", getBooks)
38  router.GET("/books/:id", getBook)
39  router.POST("/books", postBook)
40  router.Run("localhost:8080")
41 }
42
43 func getBook(c *gin.Context) {
44  id := c.Param("id")
45  for _, book := range books {
46      if book.ID == id {
47          c.IndentedJSON(http.StatusOK, book)
48          return
49      }
50  }
51  c.IndentedJSON(http.StatusNotFound, gin.H{"message": 
52 "book not found"})
53 }
54
55 func postBook(c *gin.Context) {
56  var newBook book
57
58  if err := c.BindJSON(&newBook); err != nil {
59      return
60  }
61
62  books = append(books, newBook)
63  c.IndentedJSON(http.StatusCreated, newBook)
64 }
65

Here is a linear structure where everything is in the main.go file. let's add structure bit by bit as we go along the tutorial.

Restructuring

To focus on learning, we will not do so much with the application's structure. However, we will add a little structure to the application.

Create a new models directory in the root and add a file name book.go to it.

mkdir models 
touch models/book.go

|- go.mod
|- go.sum
|- main.go
|- readme.md
|- models /
   |- book.go

Move the book struct code block into the book.go file and update the name of the 'book' struct to 'Book' (this makes it visible to other packages and we can import it into other packages). Add the code package models at the top of the book.go. The file should have the code:

package models

// book struct represents data about a book record.
type Book struct {
    ID     string  `json:"id"`
    Title  string  `json:"title"`
    Author string  `json:"author"`
    Price  float64 `json:"price"`
}

Update the two book struct references in the main.go file; the books and
postBook function as shown below.

// books slice to seed record to book store.
var books = []models.Book{ // 📌 Update reference
    {ID: "1", Title: "A Day in the Life of Abed Salama", Author: "Nathan Thrall", Price: 56.99},
    {ID: "2", Title: "King: A life", Author: "Jonathan Eig", Price: 56.99},
    {ID: "3", Title: "Where we go from here", Author: "Bernie Sanders", Price: 17.99},
    {ID: "4", Title: "Buiding a dream server", Author: "Yiga ue", Price: 39.99},
    {ID: "5", Title: "Clean Code ", Author: "Robert C Martin", Price: 39.99},
}

func postBook(c *gin.Context) {
    var newBook models.Book // 📌 Update reference

    if err := c.BindJSON(&newBook); err != nil {
        return
    }

    books = append(books, newBook)
    c.IndentedJSON(http.StatusCreated, newBook)
}

Database Setup

Set up a database to hold our book records. The database will replace our in-memory slice store.
From the terminal/command prompt, create a new directory database and add a bookstore.sql file. The instructions for setting up the bookstore database are shown in the SQL file.

mkdir database
touch database/bookstore.sql

Let's move the books collection into this file as a SQL CREATE command:

-- connect on CLI: mysql -u dbuser -p
-- Enter password:
-- mysql> CREATE DATABASE bookstore;
-- mysql> USE bookstore;
-- mysql> SOURCE /path to/bookstore.sql;
-- mysql> exit (closes connection);

DROP TABLE IF EXISTS book;
CREATE TABLE book (
  id         INT AUTO_INCREMENT NOT NULL,
  title      VARCHAR(128) NOT NULL,
  author     VARCHAR(255) NOT NULL,
  price      DECIMAL(5,2) NOT NULL,
  PRIMARY KEY (`id`)
);

INSERT INTO book
  (title, author, price)
VALUES
  ("A Day in the Life of Abed Salama", "Nathan Thrall", 90.18),
    ("King: A life", "Jonathan Eig", 56.99),
    ("Where we go from here", "Bernie Sanders", 23.99),
    ("Buiding a dream server", "Yiga ue", 39.99),
    ("Clean Code ", "Robert C Martin", 49.99);

Database connection

We need to create a connection to our database programmatically.
Two major dependencies are needed to do that, github.com/joho/godotenv and github.com/go-sql-driver/mysql.

Create a .env file in the root of the application.
Add the database connection variables into the .env file as shown.

DBUSER=root
DBPASS=password # replace with your password
DBHOST=localhost
DBNAME=bookstore # can be any name of your choice

In the database directory create a new file name dbconn.go. Add the following code in the dbconn.go file.

1 package database
2
3 import (
4   "database/sql"
5   "fmt"
6   "log"
7   "os"
8   "github.com/joho/godotenv"
9
10  "github.com/go-sql-driver/mysql"
11 )
12
13 func DBConnect() {
14  err := godotenv.Load()
15
16  if err != nil {
17      log.Fatalf("Some error occured. Err: %s", err)
18  }
19
20  config := mysql.Config{
21    User: os.Getenv("DBUSER"),
22    Passwd: os.Getenv("DBPASS"),
23    Net: "tcp",
24    Addr: os.Getenv("DBHOST"),
25    DBName: os.Getenv("DBNAME"),
26  }
27
28  db, err := sql.Open("mysql", config.FormatDSN())
29  if err != nil {
30    log.Fatal(err)
31  }
32  err = db.Ping()
33  if err != nil {
34    log.Fatal(err)
35  }
36
37  fmt.Println("DB connection successful")
38 }

On the CLI run the command to import all dependencies.

go get .

On line 14: godotenv.Load() provided by github.com/joho/godotenv dependency retrieves the values of our .env file.
On lines 20 - 26, use the MySQL package struct Config to collect the connection properties from the .env.
On line 28, open the SQL connection passing the driver and the connection string which is in this format "username:password@(127.0.0.1:3306)/dbname". The config.FormatDSN() formats the config struct into the connection string behind the scene so we don't have to do it manually. Note, there are other drivers aside from MySQL.
On line 32, test the connection is successful with db.Ping().
For a start, if there is any error we end execution with a call to log.Fatal("") and print the error to the console.

Finally, in the main.go file there is a call to the database.DBConnect() function just below line 35 in the main() function to temporarily test that the connection setup worked. Don't forget to import the "github.com/yigaue/bookstore/database" package.
Run:

go run .

It would help if you got "DB connection successful" response.
Your main function should look like this now.

func main() {
    database.DBConnect()
    router := gin.Default()
    router.GET("/books", getBooks)
    router.GET("/books/:id", getBook)
    router.POST("/books", postBook)
    router.Run("localhost:8080")
}

Good job if you got here so far.

Update the code to use the database

After the endpoints and database setup, let's update the code to use the database instead of the in-memory slice store.

There are currently three functions in the main.go file we need to update.

getBooks function
getBook function
postBook function

We'll add one more, a deleteBook function.

getBooks function
Below is the updated code for the getBooks function.

23 // getBooks responds with the list of all books as JSON
24 func getBooks(c *gin.Context) {
25  // var db *sql.DB
26  db := database.DBConnect()
27  var books []models.Book
28
29  rows, err := db.Query("SELECT * FROM book")
30  if err != nil {
31      fmt.Errorf("getBooks: %v", err)
32  }
33
34  defer rows.Close()
35  for rows.Next() {
36      var book models.Book
37      if err := rows.Scan(&book.ID, &book.Author, 
38 &book.Title, &book.Price); err != nil {
39          fmt.Errorf("getBooks: %v", err)
40      }
41
42      books = append(books, book)
43  }
44
45  if err := rows.Err(); err != nil {
46      fmt.Errorf("getBooks: %v", err)
47  }
48  c.IndentedJSON(http.StatusOK, books)
49 }

Much has not changed from the previous code except the addition of the SQL query to fetch the books and minor updates.
First, connect to the database on line 26,
DBConnect() is the function that connects to our DB in the database package

A slice of type Book is declared on line 27 imported from the models' package.

Line 29 - 32 we query the database for all books and check for any error.

Line 34, rows.Close() prevents further enumeration of the fetched rows.

In lines 35 - 43: loop through the rows fetched from the database with rows.Next(). rows.Next() returns true if there is a next row and false if none making it possible to use a for loop on it. Each row is scanned into our book struct with the column of the database copied into the appropriate struct property.
Line 42: append each book struct created from the row columns into the books slice defined on line 27.
Then we return a Json of the books slice.
fmt.Errorf is an error formatter handy to log where an error occurred.

Test the implementation with curl. First, run the go code.

go run .

Then open a new terminal and run

curl localhost:8080/books

getBook function
This function fetches a single row using the given ID of the book.

50 func getBook(c *gin.Context) {
51  db := database.DBConnect()
52  id := c.Param("id")
53  var book models.Book
54
55  row := db.QueryRow("SELECT * FROM book WHERE id = ?", id)
56  err := row.Scan(&book.ID, &book.Author, &book.Title,  
   &book.Price)
57
58  if err == sql.ErrNoRows {
59      c.IndentedJSON(http.StatusNotFound, 
   gin.H{"message": "book not found"})
60  }
61
62  if err != nil {
63      fmt.Errorf("book ID, %d: %v", id, err)
64  }
65
66  c.IndentedJSON(http.StatusOK, book)
67 }

Again, connect to the database.
On line 51 we get the id of the book.

On line 55: use db.QueryRow to get a single row. Note it’s different from db.Query method which returns multiple rows.

row.Scan method copies the columns of the row to the appropriate book struct fields.

Sql.ErrNoRows is returned by row.Scan method when no result is returned by db.QueryRow method.

On lines 62 - 64, we check for any error.

Finally, return the book as JSON.

Test the implementation with curl. First, run the go code:

go run .

Then open a new terminal and run

curl localhost:8080/books/1

postBook function
Let's update the postBook function.

69 func postBook(c *gin.Context) {
70  db := database.DBConnect()
71  var newBook models.Book
72  if err := c.BindJSON(&newBook); err != nil {
73      return
74  }
75  row, err := db.Exec("INSERT INTO book (title, author, price) VALUES (?, ?, ?)", newBook.Title, newBook.Author, newBook.Price)
76  if err != nil {
77      fmt.Errorf("postBook %v", err)
78  }
79
80  id, err := row.LastInsertId()
81
82  if err != nil {
83      fmt.Errorf("error getting lastID: %v", err)
84  }
85
86  newBook.ID = strconv.Itoa(int(id))
87
88  c.IndentedJSON(http.StatusCreated, newBook)
89 }

First, connect to the database.

Line 72, we get the request body JSON, bind it to the book struct fields and check for error at the same time.

Lines 75 -78: execute the INSERT SQL query to add the book to the database table.

Line 80, we get the ID of the recently inserted row and then assign this ID to the book struct ID field.
The int64 ID returned from row.LastInsertedId() is converted to a string on line 86 using strconv.Itoa (int to ASCII).
Note strconv.Itoa() takes an int type so the ID is first converted from int64 to int.
On line 88, return the book JSON with the status code of 201(http.StatusCreated
)

Test the implementation by running:

go run .

Then open another terminal tab and run

curl http://localhost:8080/books \
    --include \
    --header "Content-Type: application/json" \
    --request "POST" \
    --data '{"title": "Things fall apart", "author": "Chinua Achebe", "price": 30.90}'

deleteBook function
The deleteBook function was not part of the first tutorial.

91 func deleteBook(c *gin.Context) {
92  db := database.DBConnect()
93  id := c.Param("id")
94  _, err := db.Exec("DELETE FROM book WHERE id = ?", id)
95
96  if err != nil {
97      fmt.Errorf("deleteBook %v", err)
98  }
100 c.IndentedJSON(http.StatusOK, "Book deleted successfully")
101 }

In this code, connect to the database, get the ID of the book to be deleted, and then execute the DELETE SQL query. Finally, a response is returned. This function can be improved to check for errors if the book is not found and others.

Conclusion

This tutorial focused on interacting with our database using Go's built-in packages and the gin framework for routing, reducing the distraction of learning the intended core concepts.
In the next tutorial, we'll add more functionality and JWT authentication. I hope you enjoyed coding along. Do well to leave questions and feedback in the comment section.

See the complete code on Github. Feel free to contribute or open an issue.

Blog