Build an API Using Go and MySQL

Build an API Using Go and MySQL

It’s easier than you think

The project scope is to build an API to manage content posts. You’ll create an API with three functionalities: create a post, read a single post, and list posts. The database is MySQL installed in localhost.

The database library is using https://github.com/jmoiron/sqlx.

The HTTP routing library is using https://github.com/gin-gonic/gin.

Since the Go community already has a wonderful library, there is almost no reason to write everything from scratch.


Application Design

Image for post

Project Application Design

We have two packages: main and post.

The main package is an executable package in Golang that runs the application. It consists of an instance from the package post (postClient) and three functions to handle routing (listPostreadPost, and createPost).

The post package is the package that interacts with the database to manage the post. It has two object types: Client and PostClient is an interface to use the package. Post is an abstraction of the real-world content post model. The instance of Client has three functionalities to run a query or insert to the database.

The database structure of this project is only one table named “post” to store the object Post in the package post. The table structure is the following.


Code

'post’ package

These following code blocks explain how we build the Client and Post types and the NewClient function that requires DB input. The DB input is using a package that is initialized outside the package. The idea of this approach is the package does not care who and what creates the Client instance, as long as it provides a working DB connection pool. The inline comment explains the code.

// Client is instance of module, the attribute is DB instance
    type Client struct {
        DB *sqlx.DB
    }

    // NewClient is function to create new instance of Post module.
    func NewClient(db *sqlx.DB) (c Client) {
        return Client{
            DB: db,
        }
    }

    // Post is object structure of real-world post model
    type Post struct {
        ID         int64     `json:"id"`
        Content    string    `json:"content"`
        CreateTime time.Time `json:"create_time"`
    }

The next code block shows the function inside the Client. All of the functions are using a function from sqlx.

// CreatePost is function to create new content, require a content in string
    func (c *Client) CreatePost(content string) (id int64, err error) {
        // run the insert script to db
        result, err := c.DB.Exec(`
            INSERT INTO post (content, create_time)
            VALUES (?, now())
        `,
            content)
        if err != nil {
            log.Println(err)
            return
        }

        // get the last insert id
        id, err = result.LastInsertId()
        if err != nil {
            log.Println(err)
            return
        }

        return
    }

    // ReadPost is function to read single post
    func (c *Client) ReadPost(id int64) (post Post, err error) {
        // run the query with given id
        row := c.DB.QueryRow(`
                SELECT id, content, create_time
                FROM post
                WHERE id = ?`,
            id)

        // assign the query result to 'post' object
        err = row.Scan(&post.ID,
            &post.Content,
            &post.CreateTime)

        if err != nil {
            log.Println(err)
            return
        }

        return
    }

    // ListPost is function to read list of post with limit
    func (c *Client) ListPost(limit int64) (posts []Post, err error) {
        // run the query with limit
        rows, err := c.DB.Query(`
                SELECT id, content, create_time
                FROM post
                ORDER BY id DESC
                LIMIT ?`,
            limit)

        if err != nil {
            log.Println(err)
            return
        }

        // loop the result and assign each row to single object
        defer rows.Close()
        for rows.Next() {
            post := Post{}

            err = rows.Scan(&post.ID,
                &post.Content,
                &post.CreateTime)

            if err != nil {
                log.Println(err)
                return
            }
            posts = append(posts, post)

        }

        return
    }

software-engineering programming golang api data-science

Bootstrap 5 Complete Course with Examples

Bootstrap 5 Tutorial - Bootstrap 5 Crash Course for Beginners

Nest.JS Tutorial for Beginners

Hello Vue 3: A First Look at Vue 3 and the Composition API

Building a simple Applications with Vue 3

Deno Crash Course: Explore Deno and Create a full REST API with Deno

How to Build a Real-time Chat App with Deno and WebSockets

Convert HTML to Markdown Online

HTML entity encoder decoder Online

Is Software Engineering a Prerequisite for Data Science?

Find out here. Although data science job descriptions require a range of various skillsets, there are concrete prerequisites that can help you to become a successful data scientist. Some of those skills include, but are not limited to: communication, statistics, organization, and lastly, programming. Programming can be quite vague, for example, some companies in an interview could ask for a data scientist to code in Python a common pandas’ functions, while other companies can require a complete take on software engineering with classes.

Data Science Course in Dallas

Become a data analysis expert using the R programming language in this [data science](https://360digitmg.com/usa/data-science-using-python-and-r-programming-in-dallas "data science") certification training in Dallas, TX. You will master data...

50 Data Science Jobs That Opened Just Last Week

Data Science and Analytics market evolves to adapt to the constantly changing economic and business environments. Our latest survey report suggests that as the overall Data Science and Analytics market evolves to adapt to the constantly changing economic and business environments, data scientists and AI practitioners should be aware of the skills and tools that the broader community is working on. A good grip in these skills will further help data science enthusiasts to get the best jobs that various industries in their data science functions are offering.

Software Developer vs Software Engineer — Differences: Bogus or Real?

In this article, see if there are any differences between software developers and software engineers. What you’re about to read mostly revolves around my personal thoughts, deductions, and offbeat imagination. If you have different sentiments, add them in the comment section, and let’s dispute! So, today’s topic…

Best Free Courses For Computer Science, Software Engineering, and Data Science

Best Free Courses For Computer Science, Software Engineering, and Data Science. Become an Expert for Free! Learning Programming, Software Engineering, and Data Science Has Never Been Cheaper