How to Build a Simple Golang and CockroachDB Web App via the MVC pattern Part 3

Written by Data Pilot
July 15, 2019
  • Cockroach
  • Go
Have a Database Problem? Speak with an Expert for Free
Get Started >>

/ ID: 567 Title: How to Build a Simple Golang and CockroachDB Web App via the MVC pattern Part 3 Meta Description: This article continues the discussion of how to create a web app that can retrieve data in CockroachDB using Golang and the MVC pattern. Meta Keywords: create cockroachDB record using golang, update cockroachDB record using golang, delete cockroachDB using golang, retrieve data in cockroachDB using golang Author: orkb Template: Unstructured Tutorial Categories: Cockroach Tags: Cockroach, Go Status: Published /

Introduction

In the second part of our multi-part series, we took a look of our config files for the database, our template configuration and our first controller. Now we’ll move on to discuss the “model” and the “view” of our Model-View-Controller pattern, and we’ll run our web app for the first time.

Prerequisites

Before you proceed with the steps outlined in this article, it’s important to make sure you were able to finish Part 2 of this multi-part series.

The Model in Model-View-Controller

In the first part of this series, we defined our model as the place where all the database processing takes place.

The model for retrieving all records

There are many operations you can perform on a CockroachDB database via a Go script– you can create a CockroachDB record using Golang, you can update a CockroachDB record using Golang, and you can also choose to delete or simply retrieve records from the database. This first function in our model directory is used for retrieving all records in the CockroachDB database table tblrestraunts:

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
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
// (1)
package models

// (2)
import (
"database/sql"
"errors"
"net/http"
"strconv"
"webGo/config"
)

// (3)
type Restaurant struct {
Id int
Name string
Phone string
Email string
Stars int
Category string
}

// (4)
func AllResto() ([]Restaurant, error) {

rows, err := config.DB.Query("SELECT * FROM tblrestaurants")

if err != nil {
return nil, err
}
defer rows.Close()

restos := make([]Restaurant, 0)

// loop to the rows and display the records
for rows.Next() {
resto := Restaurant{}
err := rows.Scan(&resto.Id, &resto.Name, &resto.Phone, &resto.Email, &resto.Stars, &resto.Category)
if err != nil {
return nil, err
}
restos = append(restos, resto)
}

if err = rows.Err(); err != nil {
return nil, err
}

// the result to be exported to a template.
return restos, nil

}

As you can see, there’s quite a bit going on in this code. Let’s look at it more closely to understand how it works:

  1. First, package model states the name of the package.
  2. Then, import will proceed to import the necessary dependencies.
  3. The struct will be the collection of fields that matches the fields we have in the database.
  4. The func AllResto() will retrieve all the records in the tblrestaurants table.

We use db.Query("SELECT * FROM tblrestaurants") and assign the results to “rows”. Then, we check to see if any errors occurred and return a status of “Internal Server Error” if necessary.

We defer rows.Close() to ensure that resources are deallocated when we’re done with our database connection.

After that, we start appending restaurants to our variable “restos” with restos := make([]Restaurant, 0).

  1. Last but not least, we iterate over the rows using rows.Next() , and we return the results from the SELECT operation.

The View in the Model-View-Controller

Now that we have our controller and our model set up, let’s take a quick look at the “View” or the template that will be served as the front end to our user.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
{{template "header"}}



Restaurant Listing





Type something in the input field to search the table for name, email, or category:





id  name    phone   email   stars   category     
{{.Id}} {{.Name}}   {{.Phone}}  {{.Email}}  {{.Stars}}  {{.Category}}   View    Edit    Delete



{{template "footer"}}

From a code perspective, there’s not a lot going on in the above script– it’s mainly made up of HTML tags.

The “View”, as the name implies, simply presents the Golang-related files and data in a certain structure. In this case, we can see the Golang components are enclosed within the curly braces “{{}}”. The “.” represents the value of the structure within the variable “restos” that we passed from the previous section; that’s why you see the references to {{.Id}}, {{.Name}}, {{.Phone}}, {{.Email}}, {{.Stars}} and {{.Category}} which match up with the Restaurant{} struct that we created in the previous section.

The func main()

Now that we’ve looked at the initial code that comprises our “model-view-controller”, let’s try running our app using the func main(). To do this, create the file main.go in your project root directory and add the following Go code to it:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
// (1)
package main

// (2)
import (
"net/http"
"webGo/controllers"

_ "github.com/lib/pq"
)

// (3)
func main() {
http.HandleFunc("/", index)
http.HandleFunc("/restaurants", controllers.Index)

http.ListenAndServe(":9090", nil)
}

// (4)
func index(w http.ResponseWriter, r *http.Request) {
http.Redirect(w, r, "/restaurants", http.StatusSeeOther)
}

Let’s see what exactly is going on in func main():

  1. The code begins with package main telling the compiler that this script will compiled as an executable program, so that we can run the program via this package.

  2. After that, the import statements import all the necessary dependencies. Note that we are importing “webGo/controllers”, which is our controller.

  3. http.HandleFunc() takes a string and a function, and that function is in the controllers directory. This means that whenever the program receives a string with the value of “/restaurants” as the request, the controllers.Index function will be called.

  4. Finally, this function simply redirects to the controllers.Index whenever we receive a “/” in the URL.

At this point, we’ve written quite a bit of code, and we now have all the basic scripts in our project directory. Let’s try to run the web app to see if everything is working as expected.

We’ll need to run our executable program, func main(), using the following command in the terminal:

NOTE: Make sure you navigate to your project directory before running the command.

1
go run main.go

A confirmation message should display in your terminal saying: “Connected to the database”.

Finally, you can navigate to your index file via browser using this URL : “localhost:9090/” or “localhost:909/restaurants”. Either one of these will call your “index.gohtml” template.

You should see all the records in tblrestaurants presented via a table in your browser. The URL should look familiar– it’s the one we declared in our func main():

The table display all the records retrieve using Go language in a CockroachDB database

Conclusion

In this third installment of our multi-part series, we discussed how our model processes the request and how we serve our view to the user base from the parameters that are sent via URL. We’ve made a lot of progress in our example web app so far; using this web app as a model, you can write your own code that could delete from CockroachDB using Golang or retrieve data from CockroachDB using Golang. Once you’ve completed the examples in this article, you can move on to the next article, where we take a look at the templates used to serve our client and see the full implementation of our web application.

The Code

Here’s the code we’ve discussed so far in its entirety:

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
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
package models

import (
"database/sql"
"errors"
"net/http"
"strconv"
"webGo/config"
)

type Restaurant struct {
Id int
Name string
Phone string
Email string
Stars int
Category string
}

// Get all records in tblrestaurants
func AllResto() ([]Restaurant, error) {

rows, err := config.DB.Query("SELECT * FROM tblrestaurants")

if err != nil {
return nil, err
}
defer rows.Close()

restos := make([]Restaurant, 0)

for rows.Next() {
resto := Restaurant{}
err := rows.Scan(&resto.Id, &resto.Name, &resto.Phone, &resto.Email, &resto.Stars, &resto.Category)
if err != nil {
return nil, err
}
restos = append(restos, resto)
}

if err = rows.Err(); err != nil {
return nil, err
}

return restos, nil

}


// get a specific record in tblrestaurants
func OneRestaurant(r *http.Request) (Restaurant, error) {
resto := Restaurant{}

id := r.FormValue("id")
if id == "" {
return resto, errors.New("400. Bad Request")
}

row := config.DB.QueryRow("SELECT * FROM tblrestaurants WHERE id = $1", id)

err := row.Scan(&resto.Id, &resto.Name, &resto.Phone, &resto.Email, &resto.Stars, &resto.Category)
if err != nil {
return resto, err
}

return resto, nil
}


// count how many records in tblrestaurants
func CountRestaurant() int {

var Count int
rows, err := config.DB.Query("SELECT COUNT(*) as count FROM tblrestaurants")
checkErr(err)
if err != nil {
return 0
}
Count = checkCount(rows)

return Count
}


// simply counts the rows and put in the variable count as type int
func checkCount(rows *sql.Rows) (count int) {
for rows.Next() {
err := rows.Scan(&count)
checkErr(err)
}
return count
}

func checkErr(err error) {
if err != nil {
panic(err)
}

}


// Creates/inserts new record in tblrestaurant
func CreateRestaurant(r *http.Request) (Restaurant, error) {

// get form values
resto := Restaurant{}
i := r.FormValue("resto_id")
resto.Name = r.FormValue("resto_name")
resto.Phone = r.FormValue("phone")
resto.Email = r.FormValue("email")
s := r.FormValue("stars")
resto.Category = r.FormValue("category")

// validate form values
if i == "" || resto.Name == "" || resto.Phone == "" || resto.Email == "" || s == "" || resto.Category == "" {

return resto, errors.New("400. Bad Request. All fields must be complete!")
}

// convert form values
id64, err := strconv.ParseInt(i, 10, 32)
stars64, err := strconv.ParseInt(s, 10, 32)
if err != nil {
return resto, errors.New("406. Not Acceptable. Stars must be a number")
}
resto.Id = int(id64)
resto.Stars = int(stars64)

// insert values
_, err = config.DB.Exec("INSERT INTO tblrestaurants (id,name,phone,email,stars,category) VALUES ($1,$2,$3,$4,$5,$6)", resto.Id, resto.Name, resto.Phone, resto.Email, resto.Stars, resto.Category)
if err != nil {
return resto, errors.New("500. Internal Server Error." + err.Error())
}

return resto, nil

}


// updates a specific record in tblrestaurants
func UpdateResto(r *http.Request) (Restaurant, error) {

// get form values
resto := Restaurant{}
i := r.FormValue("id")
resto.Name = r.FormValue("resto_name")
resto.Phone = r.FormValue("phone")
resto.Email = r.FormValue("email")
s := r.FormValue("stars")
resto.Category = r.FormValue("category")

// validate form values
if i == "" || resto.Name == "" || resto.Email == "" || s == "" {
return resto, errors.New("406. Not Acceptable. Stars must be a number")
}

// convert form values
id64, err := strconv.ParseInt(i, 10, 32)
stars64, err := strconv.ParseInt(s, 10, 32)
if err != nil {
return resto, errors.New("406. Please hit back and enter a number for the Stars")
}
resto.Id = int(id64)
resto.Stars = int(stars64)

// insert values
_, err = config.DB.Exec("UPDATE tblrestaurants SET id=$1, name=$2, phone=$3, email=$4, stars=$5, category=$6 WHERE id=$1;", resto.Id, resto.Name, resto.Phone, resto.Email, resto.Stars, resto.Category)
if err != nil {
return resto, err
}

return resto, nil

}

// deletes a specific record
func DeleteResto(r *http.Request) error {

id := r.FormValue("id")
if id == "" {
return errors.New("400. Bad Request")
}

_, err := config.DB.Exec("DELETE FROM tblrestaurants WHERE id=$1;", id)
if err != nil {
return errors.New("500. Internal Server Error")
}
return nil
}
Rate 1 Star2 Stars3 Stars4 Stars5 Stars

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started
Related Topics:
Additional Information:

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.