Storing and retrieving data from MySQL in Go APIs

In the last blog post, we create basic CRUD endpoints for a Todos app. Now, we'll use a real database like MySQL to store and reterive data for its tables.

PreRequisites

Should have gone through the previous blog post for basic understanding Write APIs in Go using go-chi.

MySQL install in your system and basic working knowledge.

Code

So, lets first setup our tables that we will use for storing the Todos. First, we'll create a database Practice, then inside that database we'll create Todos table.

CREATE DATABASE IF NOT EXISTS `Practice`;

CREATE TABLE IF NOT EXISTS `Practice`.`Todos` (
    `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `task` VARCHAR(255) NOT NULL,
    `completed` INT NOT NULL DEFAULT 0,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Note that we've added new fields in this Todos tables, id, created_at, updated_at. We'll look how to use these when we perform operations on this table.

Now create an environment file, add your MySQl <user>, <password> and <database> into it.
.env

PORT=:5000
DATABASE_URL=<user>:<password>@tcp(localhost)/<database>?parseTime=true
DB_DRIVER=mysql

Now, lets install few dependencies

go get github.com/joho/godotenv
go get github.com/go-sql-driver/mysql

Now lets start writing code for connecting to database

main.go

import (
    "database/sql"
    "encoding/json"
    "fmt"
    "log"
    "net/http"
    "os"

    "github.com/go-chi/chi/v5"
    _ "github.com/go-sql-driver/mysql"
    "github.com/joho/godotenv"
)

var (
    DATABASE_URL, DB_DRIVER, PORT string
)

func init() {
    err := godotenv.Load()
    if err != nil {
        log.Fatalln("Couldn't load env on startup!!")
    }
    DATABASE_URL = os.Getenv("DATABASE_URL")
    DB_DRIVER = os.Getenv("DB_DRIVER")
    PORT = os.Getenv("PORT")
}

func DBClient() (*sql.DB, error) {
    db, err := sql.Open(DB_DRIVER, DATABASE_URL)
    if err != nil {
        return nil, err
    }
    if err := db.Ping(); err != nil {
        return nil, err
    }
    fmt.Println("Connected to DB")
    return db, nil
}

Here, we first loaded our environment variables in init function, then we created a DBClient function in which we used Go's inbuilt library database/sql for creating a connection with the database, this function returns a client using which we interact/perform operations on tables.

Now, lets make changes to our Server struct, so that it holds the DB client.

type Server struct {
    Router *chi.Mux
    DB     *sql.DB
}

func CreateServer(db *sql.DB) *Server {
    server := &Server{
        Router: chi.NewRouter(),
        DB:     db,
    }
    return server
}

func main() {
    db, err := DBClient()
    if err != nil {
        log.Fatalln("Couldn't connect to DB")
    }
    server := CreateServer(db)
    server.MountHandlers()

    fmt.Println("server running on port:5000")
    http.ListenAndServe(PORT, server.Router)
}

Now with these changes you can start and check whether you are able to connect to the database or not.

➜  go-api git:(main) ✗ make run
Connected to DB
server running on port:5000

NOTE: If you're getting any errors check whether you've imported all the necessary libraries and create credentials are present in .env file

Now, lets write the GetTodos and AddTodo function again with the use of DB client
Since, we've our DB client present inside Server struct, so to access it in our GetTodos and AddTodo function, we have to make them as methods of Server struct and we also have to make changes in MountHandlers function.

func (server *Server) MountHandlers() {
    server.Router.Get("/greet", Greet)

    todosRouter := chi.NewRouter()
    todosRouter.Group(func(r chi.Router) {
        // make these as a methods of Server struct as to access DB client
        r.Get("/", server.GetTodos)
        r.Post("/", server.AddTodo)
    })

    server.Router.Mount("/todos", todosRouter)
}

type Todo struct {
    Id        int       `json:"id"`
    Task      string    `json:"task"`
    Completed bool      `json:"completed"`
    CreatedAt time.Time `json:"created_at"`
    UpdatedAt time.Time `json:"updated_at"`
}

type TodoRequestBody struct {
    Task      string `json:"task"`
    Completed bool   `json:"completed"`
}

func scanRow(rows *sql.Rows) (*Todo, error) {
    todo := new(Todo)
    err := rows.Scan(&todo.Id,
        &todo.Task,
        &todo.Completed,
        &todo.CreatedAt,
        &todo.UpdatedAt,
    )
    if err != nil {
        return nil, err
    }
    return todo, nil
}

func (server *Server) AddTodo(w http.ResponseWriter, r *http.Request) {
    todo := new(TodoRequestBody)
    if err := json.NewDecoder(r.Body).Decode(todo); err != nil {
        w.WriteHeader(http.StatusBadRequest)
        w.Write([]byte("Please enter a correct Todo!!"))
        return
    }

    query := `INSERT INTO Todos (task, completed) VALUES (?, ?)`
    _, err := server.DB.Exec(query, todo.Task, todo.Completed)
    if err != nil {
        w.WriteHeader(http.StatusInternalServerError)
        w.Write([]byte("Something bad happened on the server :("))
        return
    }

    w.WriteHeader(http.StatusOK)
    w.Write([]byte("Todo added!!"))
}

func (server *Server) GetTodos(w http.ResponseWriter, r *http.Request) {
    query := `SELECT * FROM Todos ORDER BY created_at DESC`

    rows, err := server.DB.Query(query)
    if err != nil {
        w.WriteHeader(http.StatusInternalServerError)
        w.Write([]byte("Something bad happened on the server :("))
        return
    }

    var todos []*Todo

    for rows.Next() {
        todo, err := scanRow(rows)
        if err != nil {
            w.WriteHeader(http.StatusInternalServerError)
            w.Write([]byte("Something bad happened on the server :("))
            return
        }
        todos = append(todos, todo)
    }

    w.WriteHeader(http.StatusOK)
    json.NewEncoder(w).Encode(todos)
}

In GetTodos function, we created the query to fetch all the Todos from Todos table sorted in descending order based on created at. DB client provides us with a method Query with which we can run our query, its returns rows, we then destructure them and returns todos array as json.

In AddTodo function, we first take the request body and map it into a TodoRequestBody and then create a insert query, we execute this query using Exec method provided by DB client.

Hit the below endpoints using curl

➜  go-api git:(main) ✗ curl -X POST 'http://localhost:5000/todos' -d '{"task": "Learn Go", "completed": false}'
Todo added!!

➜  go-api git:(main) ✗ curl -X GET 'http://localhost:5000/todos'
[{"id":1,"task":"Learn Go","completed":false,"created_at":"2023-09-03T15:18:54Z","updated_at":"2023-09-03T15:18:54Z"}]

You can also see the records directly into your MySQL table as well

mysql> SELECT * FROM Todos ORDER BY created_at DESC;
+----+----------+-----------+---------------------+---------------------+
| id | task     | completed | created_at          | updated_at          |
+----+----------+-----------+---------------------+---------------------+
|  1 | Learn Go |         0 | 2023-09-03 15:18:54 | 2023-09-03 15:18:54 |
+----+----------+-----------+---------------------+---------------------+
1 row in set (0.01 sec)

I encourage you to write PUT /todos, DELETE /todos endpoint on your own.

Conclusion

That's it, in this blog post we covered how we can connect and use MySQL databases and perform operations on tables and return response with our APIs.

Github: https://github.com/the-arcade-01/go-api

Thanks for reading till the end, really appreciate it!!