Storing and retrieving data from MySQL in Go APIs
Tags: go, api, mysql, backend
Published: 2023-09-03 10:00:47

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](https://dev.to/the-arcade-01/write-apis-in-go-using-go-chi-d3g).<br>
`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.

```sql
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.
<br>

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

```js
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`

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

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

```js
➜  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.

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

```js
➜  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

```sql
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!!