Creating a Go(lang) API with Echo Framework and PostgreSQL

This go lang tutorial help to create CRUD operation api using postgreSQL database.We will create rest api to add, edit and delete record from postgreSQL table.Its very simple and easy using ECHO web framework. This 'todo' application help to create employee record, update employee and delete employee from database using Echo framework.

We will use GO programming language as to create restful api.Go is very lightweight, easy to learn and fast.Go was created by Google.The data is stored into PostgreSQL database.The PostgreSQL is a lightweight database as like MySQL or SQLite.

Echo is micro framework like lumen in php. Its has many features and well documented to create fast and easy web application using GO.You can create restful api without any framework as well but framework has its own feature to create secure and fast web development.

The ECHO framework Feature are:

• Build robust and scalable RESTful APIs
• Define middleware at root, group or route level
• Data binding for JSON, XML and form payload
• Group APIs
• Extensible middleware framework
• Handy functions to send variety of HTTP responses
• Centralized HTTP error handling
• Template rendering with any template engine
• Define your format for the logger
• Highly customizable
• Automatic TLS via Let’s Encrypt
• HTTP/2 support

You can also check other recommended tutorial of golang,

• Getting Started with GoLang
• Building GoLang Restful API Using Echo and MySQL
• How To Use BasicAuth Middleware With Go Echo Framework
• How to Write Log into Files in Go/Golang

Simple GoLang API Using Echo and postgreSQL

We will create main server main.go file and will add all functionality in it.The main file will have database connection information and routes information.I am not creating separate file handlers, env and model class.This golang tutorial help to understand basics of routing and golang uses with echo framework.

We will create following rest end points into this golang tutorial

Route	Method	Type	Posted JSON	Description
/employee	GET	JSON	–	Get all employees data
/employee/{id}	GET	JSON	–	Get a single employee data
/employee	POST	JSON	{"Name": "Rachel", "Salary": "1200", "Age" : "23"}	Insert new employeerecord into database
/employee	PUT/{id}	JSON	{"Name": "Rachel", "Salary": "1200", "Age" : "23", "Id" : "56"}	Update customer record into database
/employee	DELETE	JSON	{"Id" : 59}	Delete particular employee record from database

Please ready postgreSQL database and employees table(which has name ,salary and age column).

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

package main
 
import (
            "net/http"
            "database/sql"
            "github.com/labstack/echo"
            "fmt"
            "log"
            _"github.com/lib/pq"
)

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

We will run below command to get all external packages,

$ go get github.com/labstack/echo
$ go get github.com/lib/pq

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

var err error
	db, err = sql.Open("postgres", "user=postgres password=root dbname=books_database sslmode=disable")
	if err != nil {
		log.Fatal(err)
	}

	if err = db.Ping(); err != nil {
		panic(err)
	} else {
		fmt.Println("DB Connected...")
	}

	e := echo.New()

	type Employee struct {
		Id     string `json:"id"`
		Name   string `json:"name"`
		Salary string `json: "salary"`
		Age    string `json : "age"`
	}
	type Employees struct {
		Employees []Employee `json:"employees"`
	}

Created database connection with postgreSQL using credentials, if the db connection failed, we will exit from application.Created new instance of echo framework and assigned into e variable.We have created employee struct as json type and employees results set.

Add record into postgreSQL using Echo

We will create POST type request to add employee data into postreSQL 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 {
		u := new(Employee)
		if err := c.Bind(u); err != nil {
			return err
		}
		sqlStatement := "INSERT INTO employees (name, salry,age)VALUES ($1, $2, $3)"
		res, err := db.Query(sqlStatement, u.Name, u.Salary, u.Age)
		if err != nil {
			fmt.Println(err)
		} else {
			fmt.Println(res)
			return c.JSON(http.StatusCreated, u)
		}
		return c.String(http.StatusOK, "ok")
	})

We have created insert query and add record into postgreSQL table.

Update record into postgreSQL using Echo

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

e.PUT("/employee", func(c echo.Context) error {
	u := new(Employee)
	if err := c.Bind(u); err != nil {
		return err
	}
	sqlStatement := "UPDATE employees SET name=$1,salary=$2,age=$3 WHERE id=$5"
	res, err := db.Query(sqlStatement, u.Name, u.Salary, u.Age, u.Id)
	if err != nil {
		fmt.Println(err)
		//return c.JSON(http.StatusCreated, u);
	} else {
		fmt.Println(res)
		return c.JSON(http.StatusCreated, u)
	}
	return c.String(http.StatusOK, u.Id)
})

We have created update query to update record into postgreSQL table based on passed employee id.

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 {
	id := c.Param("id")
	sqlStatement := "DELETE FROM employees WHERE id = $1"
	res, err := db.Query(sqlStatement, id)
	if err != nil {
		fmt.Println(err)
		//return c.JSON(http.StatusCreated, u);
	} else {
		fmt.Println(res)
		return c.JSON(http.StatusOK, "Deleted")
	}
	return c.String(http.StatusOK, id+"Deleted")
})

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

How to get all 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", func(c echo.Context) error {
	sqlStatement := "SELECT id, name, salary, age FROM employees order by id"
	rows, err := db.Query(sqlStatement)
	if err != nil {
		fmt.Println(err)
		//return c.JSON(http.StatusCreated, u);
	}
	defer rows.Close()
	result := Employees{}

	for rows.Next() {
		employee := Employees{}
		err2 := rows.Scan(&employee.Id, &employee.Name, &employee.Salary, &employee.Age)
		// Exit if we get an error
		if err2 != nil {
			return err2
		}
		result.Employees = append(result.Employees, Employee)
	}
	return c.JSON(http.StatusCreated, result)

	//return c.String(http.StatusOK, "ok")
})

Conclusion:

We have build restful api using go lang Echo framework and postgreSQL database. We have created employee ‘todo’ list which has add record rest call,update rest API,get all data api and delete rest api data.