A Beginner-friendly Approach to Developing a REST API with Go, Gin and MSQL
Tags: go, gin, mysql, beginners
Published: 2024-02-21 18:17:24

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](https://dev.to/goslek/a-beginner-friendly-approach-to-developing-a-rest-api-with-go-and-gin-301p).

## What We are building on
Here is our current code and structure: 

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

main.go/

```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.
```bash
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:
```go
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.
```go
// 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},
}
```

```go
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.
```bash
mkdir database
touch database/bookstore.sql
```
Let's move the `books` collection into this file as a SQL CREATE command:
```sql
-- 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.
1. Create a .env file in the root of the application.
2. Add the database connection variables into the .env file as shown.
```env
DBUSER=root
DBPASS=password # replace with your password
DBHOST=localhost
DBNAME=bookstore # can be any name of your choice
```
3. In the database directory create a new file name dbconn.go. 
Add the following code in the dbconn.go file.

```go
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.
```bash
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](https://go.dev/wiki/SQLDrivers) 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: ```bash
go run .
```
It would help if you got "DB connection successful" response.
Your main function should look like this now.
```go
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.
1. getBooks function
2. getBook function
3. postBook function

We'll add one more, a deleteBook function.

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

```go
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.

```bash
go run .
```
Then open a new terminal and run
```bash
curl localhost:8080/books
``` 

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

```go
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:

```bash
go run .
```
Then open a new terminal and run
```bash
curl localhost:8080/books/1
``` 

**postBook function**
Let's update the postBook function.

```go
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:
```bash
go run .
```
Then open another terminal tab and run
```bash
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](https://dev.to/goslek/a-beginner-friendly-approach-to-developing-a-rest-api-with-go-and-gin-301p).

```go
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](https://github.com/Yigaue/bookstore/tree/database-storage). Feel free to contribute or open an issue.