REST-API with Golang, Mux & MySQL
In this article we are building a complete REST-API in Golang along with Mux and MySQL. I expect you to know the basics of the SQL language as well as setting up a local MySQL instance.
Introduction
Before you start reading I recommend you to read my previous article on building a REST-API with Golang and Mux, where we don’t integrate with any database and is meant to teach you the basics. I recommend you to also read my article on the basics of using MySQL with Golang.
I guess you could call this part 2 as we are pretty much going to build on top of what I created before. Eather way I’m not going to get into the details of how Mux and Golang works, I expect you to know the basics.
Let’s get into it.
Creating our project
We are going to start by creating our project which is only going to contain one file.
mkdir rest-api-mysql && cd rest-api && touch main.goThis will create a new directory called “rest-api-mysql” and a file called “main.go” in it.
We also need to install some packages:
go get -u github.com/gorilla/muxgo get -u github.com/go-sql-driver/mysql
We will use a MySQL driver in order to connect to our database. The reason we do this is because the driver handles a lot of the stuff necessary to communicate with our database.
For the purpose of this tutorial I’ve already created a table in my database called “posts”, each post has an id and title. I also created some sample posts for us to get. You don’t really need to do that as we will define an endpoint for creating post.
Creating the database object
First we will add our database packages and create our database object. This does not open any connection to the database, instead it prepares the object for future use. We also create two global variables, “db” and “err”.
package mainimport (
"database/sql"
_"github.com/go-sql-driver/mysql"
)var db *sql.DB
var err errorfunc main() {
db, err = sql.Open("mysql", "<user>:<password>@tcp(127.0.0.1:3306)/<dbname>")
if err != nil {
panic(err.Error())
} defer db.Close()
}
Just replace <user> with you database user, <password> with the password and <dbname> with your database name. If your database doesn’t have a password replace the “sql.Open()” line with this:
db ,err = sql.Open("mysql", "root:@/<database-name>")Initialising the router
Now we have database object and are ready to create our endpoints. We import Mux, initialise the router and run our server on port 8000.
package mainimport (
"database/sql"
_"github.com/go-sql-driver/mysql"
"github.com/gorilla/mux"
)var db *sql.DB
var err errorfunc main() {
db, err = sql.Open("mysql", "<user>:<password>@tcp(127.0.0.1:3306)/<dbname>")
if err != nil {
panic(err.Error())
} defer db.Close() router := mux.NewRouter() http.ListenAndServe(":8000", router)
}
Creating our endpoints
Now we are going to establish the endpoints of our API, the way we will set this up is to create all of our endpoints in the main function, every endpoint needs a function to handle the request and we will define those below the main function.
Now I will create our endpoints and your file should look something like this:
package mainimport (
"database/sql"
_"github.com/go-sql-driver/mysql"
"github.com/gorilla/mux"
"net/http"
)var db *sql.DB
var err errorfunc main() {
db, err = sql.Open("mysql", "<user>:<password>@tcp(127.0.0.1:3306)/<dbname>")
if err != nil {
panic(err.Error())
} defer db.Close() router := mux.NewRouter() router.HandleFunc("/posts", getPosts).Methods("GET")
router.HandleFunc("/posts", createPost).Methods("POST")
router.HandleFunc("/posts/{id}", getPost).Methods("GET")
router.HandleFunc("/posts/{id}", updatePost).Methods("PUT")
router.HandleFunc("/posts/{id}", deletePost).Methods("DELETE") http.ListenAndServe(":8000", router)
}
What I’ve done is to create five different routes, I expect you to know at least the basics of REST-APIS so you should understand what the different enpoints does. I also added another package (“net/http”).
Defining our models (structs)
A struct in Golang is very similar to a ES6 or Java class. We will use a struct to represent a post.
package mainimport (
"database/sql"
_"github.com/go-sql-driver/mysql"
"github.com/gorilla/mux"
"net/http"
)type Post struct {
ID string `json:"id"`
Title string `json:"title"`
}var db *sql.DB
var err errorfunc main() {
db, err = sql.Open("mysql", "<user>:<password>@tcp(127.0.0.1:3306)/<dbname>")
if err != nil {
panic(err.Error())
}defer db.Close()router := mux.NewRouter()router.HandleFunc("/posts", getPosts).Methods("GET")
router.HandleFunc("/posts", createPost).Methods("POST")
router.HandleFunc("/posts/{id}", getPost).Methods("GET")
router.HandleFunc("/posts/{id}", updatePost).Methods("PUT")
router.HandleFunc("/posts/{id}", deletePost).Methods("DELETE")http.ListenAndServe(":8000", router)
}
Route handlers
Now we just need to define the functions that will handle the requests. You also need to import the packages “encoding/json”, “fmt” and “io/ioutil”.
import (
"encoding/json"
"fmt"
"io/ioutil"
)The syntax for creating these functions looks like this:
func <your-function-name>(w http.ResponseWriter, r *http.Request) {
}You can read more about it here: https://github.com/gorilla/mux
getPosts()
func getPosts(w http.ResponseWriter, r *http.Request) {
w.Header().Set("Content-Type", "application/json") var posts []Post result, err := db.Query("SELECT id, title from posts")
if err != nil {
panic(err.Error())
} defer result.Close() for result.Next() {
var post Post
err := result.Scan(&post.ID, &post.Title)
if err != nil {
panic(err.Error())
}
posts = append(posts, post)
} json.NewEncoder(w).Encode(posts)
}
Here I have defined the function that gets all posts from our database, we’re just setting the header “Content-Type” to “application/json”. Then we define an array of posts called “posts”.
When we query our database we use “db.Query()”, this should not be used with actions that doesn’t return any rows. Exemples of such actions is INSERT, UPDATE AND DELETE. Instead we use “db.Prepare()” and “.Exec()”, but more on that later.
After our query we check for errors (you should almost always check for errors). It is important that you “defer result.Close()” after every query, I will not get into the details about why. Basically it closes the connection after we are done.
Then we loop over the result and for every iteration we create a new Post instance, then we Scan the result into our post object. As usuall, we check for errors, if everything went alright we append our post object to our “posts” array.
Finally, we encode our posts to JSON and send send them away.
createPost()
func createPost(w http.ResponseWriter, r *http.Request) { stmt, err := db.Prepare("INSERT INTO posts(title) VALUES(?)")
if err != nil {
panic(err.Error())
} body, err := ioutil.ReadAll(r.Body)
if err != nil {
panic(err.Error())
} keyVal := make(map[string]string)
json.Unmarshal(body, &keyVal)
title := keyVal["title"] _, err = stmt.Exec(title)
if err != nil {
panic(err.Error())
} fmt.Fprintf(w, "New post was created")
}
Here we will not use “db.Query()” as we are inserting data which does not return any rows, this is very important. Instead we prepare a statement with “db.Prepare()” which we will then execute with “stmt.Exec()”. In this case we prepare a statement that will create new post. The syntax for placeholder parameters in prepared statements is database-specific, using MySQL it’s a question mark (?).
As we are getting data from the request body we need to deal with this too:
body, err := ioutil.ReadAll(r.Body)
if err != nil {
panic(err.Error())
}keyVal := make(map[string]string)
json.Unmarshal(body, &keyVal)
title := keyVal["title"]
We use these lines of code to read the request body and extract the “title” value.
Then we use “stmt.Exec()” and put in the title variable we created, check for errors and finally just sends a message to the client that a new post was created.
getPost()
func getPost(w http.ResponseWriter, r *http.Request) {
w.Header().Set("Content-Type", "application/json")
params := mux.Vars(r) result, err := db.Query("SELECT id, title FROM posts WHERE id = ?", params["id"])
if err != nil {
panic(err.Error())
} defer result.Close() var post Post for result.Next() {
err := result.Scan(&post.ID, &post.Title)
if err != nil {
panic(err.Error())
}
} json.NewEncoder(w).Encode(post)
}
Now you should be able to understand at least some of what I’m doing here. First we set the content type to “application/json” and as we need an ID from our url we define create a variable called “params” set to “mux.Vars(r)”.
We then create a query that gets the post with the id from our url, of course we check for errors as well as calling “defer result.Close()”.
Then we create a new Post object, iterate through the result (even if we know that there is only one post with that specific id) and scan the values into our object.
At last, we encode our post to JSON and send it away.
updatePost()
func updatePost(w http.ResponseWriter, r *http.Request) {
params := mux.Vars(r) stmt, err := db.Prepare("UPDATE posts SET title = ? WHERE id = ?")
if err != nil {
panic(err.Error())
} body, err := ioutil.ReadAll(r.Body)
if err != nil {
panic(err.Error())
} keyVal := make(map[string]string)
json.Unmarshal(body, &keyVal)
newTitle := keyVal["title"] _, err = stmt.Exec(newTitle, params["id"])
if err != nil {
panic(err.Error())
} fmt.Fprintf(w, "Post with ID = %s was updated", params["id"])
}
For the update handler, we combine some of the things we already have done.
We get the url parameters, prepares a statement that will update the title of a post and check for errors.
Then we read the request body the same way as before because we need access to the data. We define a variable called “newTitle” and set it to the “title” value that was sent from the client.
Finally, we execute the prepared statement and pass in the new title as well as the id from the url. Then we just send a message which says that the post has been updated.
deletePost()
func deletePost(w http.ResponseWriter, r *http.Request) {
params := mux.Vars(r) stmt, err := db.Prepare("DELETE FROM posts WHERE id = ?")
if err != nil {
panic(err.Error())
} _, err = stmt.Exec(params["id"])
if err != nil {
panic(err.Error())
}fmt.Fprintf(w, "Post with ID = %s was deleted", params["id"])
}
Now we only have the delete handler left, this should be pretty self explanatory. We get the url parameters, prepares a query that will delete a post and then executes this statement with the id from the url.
We then send a message that says that the post has been deleted.
We are now done and your file should look something like this:
package mainimport (
"github.com/gorilla/mux"
"database/sql"
_"github.com/go-sql-driver/mysql"
"net/http"
"encoding/json"
"fmt"
"io/ioutil"
)type Post struct {
ID string `json:"id"`
Title string `json:"title"`
}var db *sql.DB
var err errorfunc main() {db, err = sql.Open("mysql", "<user>:<password>@tcp(127.0.0.1:3306)/<dbname>")
if err != nil {
panic(err.Error())
} defer db.Close() router := mux.NewRouter() router.HandleFunc("/posts", getPosts).Methods("GET")
router.HandleFunc("/posts", createPost).Methods("POST")
router.HandleFunc("/posts/{id}", getPost).Methods("GET")
router.HandleFunc("/posts/{id}", updatePost).Methods("PUT")
router.HandleFunc("/posts/{id}", deletePost).Methods("DELETE") http.ListenAndServe(":8000", router)
}func getPosts(w http.ResponseWriter, r *http.Request) {
w.Header().Set("Content-Type", "application/json") var posts []Post result, err := db.Query("SELECT id, title from posts")
if err != nil {
panic(err.Error())
} defer result.Close() for result.Next() {
var post Post
err := result.Scan(&post.ID, &post.Title)
if err != nil {
panic(err.Error())
}
posts = append(posts, post)
} json.NewEncoder(w).Encode(posts)
}func createPost(w http.ResponseWriter, r *http.Request) {
w.Header().Set("Content-Type", "application/json") stmt, err := db.Prepare("INSERT INTO posts(title) VALUES(?)")
if err != nil {
panic(err.Error())
} body, err := ioutil.ReadAll(r.Body)
if err != nil {
panic(err.Error())
} keyVal := make(map[string]string)
json.Unmarshal(body, &keyVal)
title := keyVal["title"] _, err = stmt.Exec(title)
if err != nil {
panic(err.Error())
} fmt.Fprintf(w, "New post was created")
}func getPost(w http.ResponseWriter, r *http.Request) {
w.Header().Set("Content-Type", "application/json")
params := mux.Vars(r) result, err := db.Query("SELECT id, title FROM posts WHERE id = ?", params["id"])
if err != nil {
panic(err.Error())
} defer result.Close() var post Post for result.Next() {
err := result.Scan(&post.ID, &post.Title)
if err != nil {
panic(err.Error())
}
} json.NewEncoder(w).Encode(post)
}func updatePost(w http.ResponseWriter, r *http.Request) {
w.Header().Set("Content-Type", "application/json")
params := mux.Vars(r) stmt, err := db.Prepare("UPDATE posts SET title = ? WHERE id = ?")
if err != nil {
panic(err.Error())
} body, err := ioutil.ReadAll(r.Body)
if err != nil {
panic(err.Error())
} keyVal := make(map[string]string)
json.Unmarshal(body, &keyVal)
newTitle := keyVal["title"] _, err = stmt.Exec(newTitle, params["id"])
if err != nil {
panic(err.Error())
} fmt.Fprintf(w, "Post with ID = %s was updated", params["id"])
}func deletePost(w http.ResponseWriter, r *http.Request) {
w.Header().Set("Content-Type", "application/json")
params := mux.Vars(r) stmt, err := db.Prepare("DELETE FROM posts WHERE id = ?")
if err != nil {
panic(err.Error())
} _, err = stmt.Exec(params["id"])
if err != nil {
panic(err.Error())
} fmt.Fprintf(w, "Post with ID = %s was deleted", params["id"])
}
Testing our API
I’m going to use Postman to test the API but curl is also a good option, but I like Postman’s graphical interface. You can get Postman here: https://www.getpostman.com
Make sure you build and restart/run the server:
go build && ./rest-api-mysqlGetting all posts
Getting a single post
Creating a new post
Updating a post
Deleting a post
Thank you for reading!
I hope you found this article helpful. If you didn’t write the code yourself I encourage you to do so. You learn so much faster when you actually write the code yourself!
Youtube channel: https://www.youtube.com/channel/UCOWItB8IMJxFEqwHWaRaopA
