REST-API with Golang, Mux & MySQL

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.

mkdir rest-api-mysql && cd rest-api && touch main.go
go get -u github.com/gorilla/muxgo get -u github.com/go-sql-driver/mysql

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 error
func main() {
db, err = sql.Open("mysql", "<user>:<password>@tcp(127.0.0.1:3306)/<dbname>")
if err != nil {
panic(err.Error())
}
defer db.Close()
}
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 error
func 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.

package mainimport (
"database/sql"
_"github.com/go-sql-driver/mysql"
"github.com/gorilla/mux"
"net/http"
)
var db *sql.DB
var err error
func 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)
}

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 error
func 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"
)
func <your-function-name>(w http.ResponseWriter, r *http.Request) {

}
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) {  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")
}
body, err := ioutil.ReadAll(r.Body)
if err != nil {
panic(err.Error())
}
keyVal := make(map[string]string)
json.Unmarshal(body, &keyVal)
title := keyVal["title"]
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) {
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) {
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"])
}
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 error
func 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

go build && ./rest-api-mysql

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!

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store