Before jumping in, let us design the API specification table, which shows the REST API signatures for various URL endpoints. Refer to the following table:

Now we come to the main program; let us add one more file to our jsonstore project. In this program, we will try to implement the first three endpoints. We suggest the implementation of the remaining two endpoints as an assignment for the reader. Take a look at the following command:

touch jsonstore/main.go

The program structure follows the same style as all the programs we have seen until now. We use Gorilla Mux as our HTTP router and import the database driver into our program:

package main

import (
  "encoding/json"
  "io/ioutil"
  "log"
  "net/http"
  "time"

  "github.com/gorilla/mux"
  "github.com/jinzhu/gorm"
    _ "github.com/lib/pq"
  "github.com/narenaryan/jsonstore/models"
)

// DB stores the database session imformation. Needs to be initialized once
type DBClient struct {
  db *gorm.DB
}

// UserResponse is the response to be send back for User
type UserResponse struct {
  User models.User `json:"user"`
  Data interface{} `json:"data"`
}

// GetUsersByFirstName fetches the original URL for the given encoded(short) string
func (driver *DBClient) GetUsersByFirstName(w http.ResponseWriter, r *http.Request) {
  var users []models.User
  name := r.FormValue("first_name")
  // Handle response details
  var query = "select * from "user" where data->>'first_name'=?"
  driver.db.Raw(query, name).Scan(&users)
  w.WriteHeader(http.StatusOK)
  w.Header().Set("Content-Type", "application/json")
  //responseMap := map[string]interface{}{"url": ""}
  respJSON, _ := json.Marshal(users)
  w.Write(respJSON)
}

// GetUser fetches the original URL for the given encoded(short) string
func (driver *DBClient) GetUser(w http.ResponseWriter, r *http.Request) {
  var user = models.User{}
  vars := mux.Vars(r)
  // Handle response details
  driver.db.First(&user, vars["id"])
  var userData interface{}
  // Unmarshal JSON string to interface
  json.Unmarshal([]byte(user.Data), &userData)
  var response = UserResponse{User: user, Data: userData}
  w.WriteHeader(http.StatusOK)
  w.Header().Set("Content-Type", "application/json")
  //responseMap := map[string]interface{}{"url": ""}
  respJSON, _ := json.Marshal(response)
  w.Write(respJSON)
}

// PostUser adds URL to DB and gives back shortened string
func (driver *DBClient) PostUser(w http.ResponseWriter, r *http.Request) {
  var user = models.User{}
  postBody, _ := ioutil.ReadAll(r.Body)
  user.Data = string(postBody)
  driver.db.Save(&user)
  responseMap := map[string]interface{}{"id": user.ID}
  var err string = ""
  if err != "" {
    w.Write([]byte("yes"))
  } else {
    w.Header().Set("Content-Type", "application/json")
    response, _ := json.Marshal(responseMap)
    w.Write(response)
  }
}

func main() {
  db, err := models.InitDB()
  if err != nil {
    panic(err)
  }
  dbclient := &DBClient{db: db}
  if err != nil {
    panic(err)
  }
  defer db.Close()
  // Create a new router
  r := mux.NewRouter()
  // Attach an elegant path with handler
  r.HandleFunc("/v1/user/{id:[a-zA-Z0-9]*}", dbclient.GetUser).Methods("GET")
  r.HandleFunc("/v1/user", dbclient.PostUser).Methods("POST")
  r.HandleFunc("/v1/user", dbclient.GetUsersByFirstName).Methods("GET")
  srv := &http.Server{
    Handler: r,
    Addr:    "127.0.0.1:8000",
    // Good practice: enforce timeouts for servers you create!
    WriteTimeout: 15 * time.Second,
    ReadTimeout:  15 * time.Second,
  }
  log.Fatal(srv.ListenAndServe())
}

There are three important aspects here:

• We replaced the traditional driver with the GORM driver
• Used GORM functions for CRUD operations
• We inserted JSON into PostgreSQL and retrieved results in the JSON field

Let us explain all the elements in detail. First, we imported all the necessary packages. The interesting ones are:

  "github.com/jinzhu/gorm"
   _ "github.com/lib/pq"
  "github.com/narenaryan/jsonstore/models"

GORM internally uses the database/sql package to some extent. We imported models from the package we created in the preceding code. Next, we created three functions, implementing the first three API specifications. They are GetUsersByFirstName,60;GetUser, and PostUser. Each function is inheriting the database driver and passed as the handler functions for the URL endpoints in the main function:

 r.HandleFunc("/v1/user/{id:[a-zA-Z0-9]*}", dbclient.GetUser).Methods("GET")
 r.HandleFunc("/v1/user", dbclient.PostUser).Methods("POST")
 r.HandleFunc("/v1/user", dbclient.GetUsersByFirstName).Methods("GET")

Now, if we enter the first function, which is simple, these statements will grab our attention:

driver.db.First(&user, vars["id"])

The preceding statement tells the DB to fetch the first record from the database with the given second parameter ID. It fills the data returned to the user struct. We are using UserResponse instead of the User struct in GetUser because User consists of the data field, which is a string. But, in order to return complete and proper JSON to the client, we need to convert the data into a proper struct and then marshal it: 

// UserResponse is the response to be send back for User
type UserResponse struct {
  User models.User `json:"user"`
  Data interface{} `json:"data"`
}

Here, we are creating an empty interface that can hold any JSON data. When we call the first function using the driver, the user struct has a data field, which is a string. We need to convert that string to a struct and then send it along with other details in UserResponse. Now let us see this in action. Run the program using the following command:

go run jsonstore/main.go

And make a few CURL commands to see the API response:

Create user:

curl -X POST 
  http://localhost:8000/v1/user 
  -H 'cache-control: no-cache' 
  -H 'content-type: application/json' 
  -d '{
     "username": "naren",
     "email_address": "[email protected]",
     "first_name": "Naren",
     "last_name": "Arya"
}'

It returns the inserted record in the DB:

{
  "id": 1
}

Now, if we GET the details of the inserted record: 

curl -X GET http://localhost:8000/v1/user/1 

It returns all the details about the user:

{"user":{"ID":1,"CreatedAt":"2017-08-27T11:55:02.974371+05:30","UpdatedAt":"2017-08-27T11:55:02.974371+05:30","DeletedAt":null,"Orders":null},"data":{"email_address":"[email protected]","first_name":"Naren","last_name":"Arya","username":"naren"}}

Insert one more record for checking the first name API:

curl -X POST 
  http://localhost:8000/v1/user 
  -H 'cache-control: no-cache' 
  -H 'content-type: application/json' 
  -d '{
     "username": "nareny",
     "email_address": "[email protected]",
     "first_name": "Naren",
     "last_name": "Yellavula"
}'

This inserts our second record. Let us test our third API, GetUsersByFirstName: 

curl -X GET 'http://localhost:8000/v1/user?first_name=Naren' 

This returns all the users with the given first name:

[{"ID":1,"CreatedAt":"2017-08-27T11:55:02.974371+05:30","UpdatedAt":"2017-08-27T11:55:02.974371+05:30","DeletedAt":null,"Orders":null},{"ID":2,"CreatedAt":"2017-08-27T11:59:41.84332+05:30","UpdatedAt":"2017-08-27T11:59:41.84332+05:30","DeletedAt":null,"Orders":null}]

The core motto of this project is to show how JSON can be stored and retrieved out of PostgreSQL. The special thing here is that we queried on the JSON field instead of the normal fields in the User table.

This concludes our journey through PostgreSQL. There is a lot more to explore in Postgres. It brings the best of both worlds by allowing us to store relational as well as JSON data in the same table.