Go with Database

June 6, 2021

I made a simple REST API with Go in my previous article. That was without any database. We are going to create a similar REST API to handle articles with a MySQL database in this article. There is no need to use any third library such as Mux at this API.

MySQL

MySQL is an open-source relational database management system. Its name is a combination of "My", the name of co-founder Michael Widenius's daughter, and "SQL", the abbreviation for Structured Query Language.

A relational database organizes data into one or more data tables in which data types may be related to each other; these relations help structure the data. SQL is a language programmers use to create, modify and extract data from the relational database, and control user access to the database.

Installing MySQL to local for development is a little complicated. We can use the Docker image of MySQL as the easiest way. It also comes with Adminer as a user interface to manage databases.

Let's create a database. As an example, let's create a database for articles or a blog. Go to Adminer, create a database, and create a table for articles.
1 2 3 4 5

CREATE TABLE `articles` ( `id` int(10) UNSIGNED NOT NULL PRIMARY KEY, `title` varchar(64) NOT NULL, `content` text NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
We can check the columns of the database by this syntax.
1

SHOW COLUMNS FROM articles;
We created a field for ID with an unsigned integer type as it would not contain negative numbers, a field for title with 64 maximum characters, and a field for content that would contain a text. They are mandatory fields.

Let's create dummy data for articles. You can create as many records as you want. I suggest at least 10 records. The syntax below is an example.

1

INSERT INTO `articles` (`id`, `title`, `content`) VALUES(1, 'The First Article', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit.');

Go

Let's code the GoLang part. We only need a Go file for this tutorial. Simple right?

1
2
3
4
5


type article struct {
    ID      int
    Title   string
    Content string
}

The struct above is to handle article data.

1
2
3
4
5


import (
    "database/sql"

    _ "github.com/go-sql-driver/mysql"
)

Don't forget to import those packages.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13


func main() {
    db, err = sql.Open("mysql", "root:root@/go_database")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    err = db.Ping()
    if err != nil {
        log.Fatal(err)
    }
    fmt.Println("Connected!")
}

We tried to connect to MySQL and checked whether it is connected or not by pinging it. In the code above, we have "root" as a user and password, connect to MySQL on localhost, and use the database named "go_database". Go to https://github.com/go-sql-driver/mysql for more information. Remember, we need to store sensitive information on the env file for production.

Next, let's create functions to handle CRUD operations.

Index

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30


func index(w http.ResponseWriter, r *http.Request) {
    rows, err := db.Query(`SELECT * FROM articles;`)
    if err != nil {
        log.Fatal(err)
    }
    defer rows.Close()

    for rows.Next() {
        var (
            id      int
            title   string
            content string
        )
        err = rows.Scan(&id, &title, &content)
        if err != nil {
            log.Fatal(err)
        }
        articles = append(articles, article{id, title, content})
    }

    response, err := json.Marshal(articles)
    if err != nil {
        log.Fatal(err)
    }

    if response != nil {
        w.Header().Set("Content-Type", "application/json")
        w.Write(response)
    }
}

Create

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26


func create(w http.ResponseWriter, r *http.Request) {
    var request article
    err := json.NewDecoder(r.Body).Decode(&request)
    if err != nil {
        http.Error(w, err.Error(), http.StatusBadRequest)
        return
    }

    result, err := db.Exec("INSERT INTO articles(title, content) VALUES(?, ?)", request.Title, request.Content)
    if err != nil {
        log.Fatal(err)
    }
    rows, err := result.LastInsertId()
    if err != nil {
        log.Fatal(err)
    }
    if rows != 0 {
        response, err := json.Marshal(article{int(rows), request.Title, request.Content})
        if err != nil {
            log.Fatal(err)
        }

        w.Header().Set("Content-Type", "application/json")
        w.Write(response)
    }
}

Read

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32


func read(w http.ResponseWriter, r *http.Request, key int) {
    rows, err := db.Query(`SELECT * FROM articles WHERE id = ?`, key)
    if err != nil {
        log.Fatal(err)
    }
    defer rows.Close()

    var (
        id      int
        title   string
        content string
    )

    for rows.Next() {
        err = rows.Scan(&id, &title, &content)
        if err != nil {
            log.Fatal(err)
        }
    }

    response, err := json.Marshal(article{id, title, content})
    if err != nil {
        log.Fatal(err)
    }

    if title != "" && content != "" {
        w.Header().Set("Content-Type", "application/json")
        w.Write(response)
    } else {
        http.Error(w, http.StatusText(http.StatusNotFound), http.StatusNotFound)
    }
}

Update

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28


func update(w http.ResponseWriter, r *http.Request, key int) {
    var request article
    err := json.NewDecoder(r.Body).Decode(&request)
    if err != nil {
        http.Error(w, err.Error(), http.StatusBadRequest)
        return
    }

    result, err := db.Exec("UPDATE articles SET title = ?, content = ? WHERE id = ?", request.Title, request.Content, key)
    if err != nil {
        log.Fatal(err)
    }
    rows, err := result.RowsAffected()
    if err != nil {
        log.Fatal(err)
    }
    if rows != 0 {
        response, err := json.Marshal(article{key, request.Title, request.Content})
        if err != nil {
            log.Fatal(err)
        }

        w.Header().Set("Content-Type", "application/json")
        w.Write(response)
    } else {
        http.Error(w, http.StatusText(http.StatusNotFound), http.StatusNotFound)
    }
}

Delete

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13


func delete(w http.ResponseWriter, r *http.Request, key int) {
    result, err := db.Exec("DELETE FROM articles WHERE id = ?", key)
    if err != nil {
        log.Fatal(err)
    }
    rows, err := result.RowsAffected()
    if err != nil {
        log.Fatal(err)
    }
    if rows != 1 {
        http.Error(w, http.StatusText(http.StatusBadRequest), http.StatusBadRequest)
    }
}

Routing

Let's put this code below on the main function for routing.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29


http.HandleFunc("/", func(w http.ResponseWriter, r *http.Request) {
    key := 0

    if len(r.URL.Path) > 1 {
        key, err = strconv.Atoi(strings.Split(r.URL.Path, "/")[1])
        if err != nil {
            http.Error(w, err.Error(), http.StatusBadRequest)
            return
        }
    }

    switch r.Method {
    case http.MethodGet:
        if key == 0 {
            index(w, r)
        } else {
            read(w, r, key)
        }
    case http.MethodPost:
        create(w, r)
    case http.MethodPut:
        update(w, r, key)
    case http.MethodDelete:
        delete(w, r, key)
    default:
        http.Error(w, http.StatusText(http.StatusNotFound), http.StatusNotFound)
    }
})
http.ListenAndServe(":8000", nil)

Our route looks a little bit complicated because it doesn't use Mux as I mentioned earlier. But the point here is to understand how the system works.

Closing

There is also a library called GORM. It simplifies our code by creating an ORM (Object-Relational Mapping) based on the structs we have defined. Feel free to try that library. But I suggest you code the ORM exclusively.

You can get the source code on https://github.com/aristorinjuang/go-databases/tree/master/03_mysql.

References

https://en.wikipedia.org/wiki/MySQL