Building GoLang Restful API Using Echo and MySQL

This is another rest api tutorial that help to create restful api using echo framework and MySQL database. We will create CRUD operations on employee module that will have create record, get all records and delete record.

I have left update record functionality, This work for you to create rest call to update record into MySQL database.I am using MySQL database to store and get data, Already shared Creating a Go(lang) API with Echo Framework and PostgreSQL.

We will use Echo framework to create restful api using Golang. Echo is very easy and fast framework to create restful api using MySQL or any database.

Table of Contents

Simple GoLang API Using Echo and MySQL

We will create simple server.go file and will have all ‘todo’ example functionality in it.The server.go file will have database connection information and routes information.I am not creating separate file for handlers, env and model class.This golang tutorial help to understand basics of routing and golang uses with MySQL database.

Please create Mysql 'test' database and 'employee' table(which has name ,salary and age column).

--
-- Table structure for table `employee`
--

CREATE TABLE IF NOT EXISTS `employee` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
  `employee_name` varchar(255) NOT NULL COMMENT 'employee name',
  `employee_salary` double NOT NULL COMMENT 'employee salary',
  `employee_age` int(11) NOT NULL COMMENT 'employee age',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COMMENT='datatable demo table' AUTO_INCREMENT=158 ;

How to connect Go(Lang) with MySQL database

We will create echo framework connection with MySQL database using MySQL database hostname, username, database name and password.

db, err := sql.Open("mysql", "root:@tcp(127.0.0.1:3306)/dummy_db")
	if err != nil {
				fmt.Println(err.Error())
	} else {
				fmt.Println("db is connected")
	}
	defer db.Close()
	// make sure connection is available
	err = db.Ping()
	if err != nil {
				fmt.Println(err.Error())
	}

You can also check other recommended tutorial of golang,

Step 1:We will create server.go file and add below code to import required package for this application.

package main
 
import (
            "database/sql"
            "fmt"
            _ "github.com/go-sql-driver/mysql"
            "github.com/labstack/echo"
            "github.com/labstack/echo/middleware"
            "net/http"
)

As you can see, GO standard library have packages inbuilt like sql, http but MySQL driver need to fetch from Github repo.
By using "_" with package that notify the compiler overlook that we’re not using it right now.

We will run below command to get all external packages,

$ go get github.com/labstack/echo
$ go get github.com/labstack/echo/middleware
$ go get github.com/go-sql-driver/mysql

Step 2: Create a "main()" function into server.go file which is a entry method for Go application.

e := echo.New()
e.Use(middleware.Logger())
e.Use(middleware.Recover())

e.Use(middleware.CORSWithConfig(middleware.CORSConfig{
			AllowOrigins: []string{"*"},
			AllowMethods: []string{echo.GET, echo.PUT, echo.POST, echo.DELETE},
}))

type Employee struct {
			Id     string `json:"id"`
			Name   string `json:"employee_name"`
			Salary string `json: "employee_salary"`
			Age    string `json : "employee_age"`
}
type Employees struct {
			Employees []Employee `json:"employee"`
}

We have created employee struct as JSON type and employees results set.We have used middleware.CORSWithConfig for CORS support rest api.

Step 3: We will add below database connection code into server.go file into main() method.

db, err := sql.Open("mysql", "root:@tcp(127.0.0.1:3306)/dummy_db")
	if err != nil {
				fmt.Println(err.Error())
	} else {
				fmt.Println("db is connected")
	}
	defer db.Close()
	// make sure connection is available
	err = db.Ping()
	if err != nil {
				fmt.Println(err.Error())
	}

Created database connection with Mysql using credentials, if the db connection failed, we will exit from application, Otherwise created new instance of echo framework and assigned into e variable.

Add record into MySQL database using GO Echo Framework

We will create POST type request and add employee data into table.We have passed json data and c.Bind() method map passed data to a Employee struct.

e.POST("/employee", func(c echo.Context) error {
	emp := new(Employee)
	if err := c.Bind(emp); err != nil {
				return err
	}
	//
	sql := "INSERT INTO employee(employee_name, employee_age, employee_salary) VALUES( ?, ?, ?)"
	stmt, err := db.Prepare(sql)

	if err != nil {
				fmt.Print(err.Error())
	}
	defer stmt.Close()
	result, err2 := stmt.Exec(emp.Name, emp.Salary, emp.Age)

	// Exit if we get an error
	if err2 != nil {
				panic(err2)
	}
	fmt.Println(result.LastInsertId())

	return c.JSON(http.StatusCreated, emp.Name)
})

We have created empty struct object and bind with passed employee record values, after that we created insert query and add employee record into MySQL table.

Delete record into postgreSQL using Echo

We will create DELETE type request to delete employee data from table using id.We have passed employee id to delete particular employee data.

e.DELETE("/employee/:id", func(c echo.Context) error {
                        requested_id := c.Param("id")
                        sql := "Delete FROM employee Where id = ?"
                        stmt, err := db.Prepare(sql)
                        if err != nil {
                                    fmt.Println(err)
                        }
                        result, err2 := stmt.Exec(requested_id)
                        if err2 != nil {
                                    panic(err2)
                        }
                        fmt.Println(result.RowsAffected())
                        return c.JSON(http.StatusOK, "Deleted")
            })

We have created delete query to delete record into MySQL table based on passed employee id.

Fetch all records from MySQL using GO Echo Framework

We will create GET type request to get all employees data from table. We have created select query to get all data from MySQL table.

e.GET("/employee/:id", func(c echo.Context) error {
	requested_id := c.Param("id")
	fmt.Println(requested_id)
	var name string
	var id string
	var salary string
	var age string

	err = db.QueryRow("SELECT id,employee_name, employee_age, employee_salary FROM employee WHERE id = ?", requested_id).Scan(&id, &name, &salary, &age)

	if err != nil {
				fmt.Println(err)
	}

	response := Employee{Id: id, Name: name, Salary: salary, Age: age}
	return c.JSON(http.StatusOK, response)
})

How to get Single record from postgreSQL using Echo Framework

We will create GET type request to get all employees data from table. We have created select query to get all data from postgreSQL table.

e.GET("/employee/:id", func(c echo.Context) error {
	requested_id := c.Param("id")
	fmt.Println(requested_id)
	var name string
	var id string
	var salary string
	var age string

	err = db.QueryRow("SELECT id,employee_name, employee_age, employee_salary FROM employee WHERE id = ?", requested_id).Scan(&id, &name, &salary, &age)

	if err != nil {
				fmt.Println(err)
	}

	response := Employee{Id: id, Name: name, Salary: salary, Age: age}
	return c.JSON(http.StatusOK, response)
})

Conclusion :

We have created simple CRUD operation into this GoLang tutorials except update employee record,We have created go database connection with MySQL using MySQL driver.