How to Build Simple Golang and CockroachDB Web App via MVC pattern Part 5

Written by Data Pilot
July 18, 2019
  • CockroachDB
  • Golang
  • MVC pattern
Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

This is last part in the five-part tutorial explaining how to build a simple Golang and CockroachDB web App via the MVC pattern. This five-part tutorial explains how to create a CockroachDB record using Golang, update a CockroachDB record using Golang, delete a CockroachDB using Golang and retrieve data in CockroachDB using Golang. This final section will discuss the remaining features of the web App. The full code for the entire five-part tutorial series is provided at the end of this article.

Prerequisites

  • An understating of the four previous sections of this tutorial.

How to Editing/Update a Single Record

First, click the Edit to make a call to a controller in the func main() while simultaneously passing the id of the record. Now examine the tags of the following link:

<td><a class="action" href="/restaurants/update?id={{.Id}}">Edit</a></td>

This the string that matches the function call in the func main() http.HandleFunc("/restaurants/update", controllers.Update) that will display the form for editing or updating the record.

Here is the controllers.Update code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
func Update(w http.ResponseWriter, r *http.Request) {
if r.Method != "GET" {
http.Error(w, http.StatusText(405), http.StatusMethodNotAllowed)
return
}

resto, err := models.OneRestaurant(r)
switch {
case err == sql.ErrNoRows:
http.NotFound(w, r)
return
case err != nil:
http.Error(w, http.StatusText(500), http.StatusInternalServerError)
return
}

config.TPL.ExecuteTemplate(w, "update.gohtml", resto)
}

This controller calls for the model models.OneRestaurant(r), the same model that was used in viewing the record. However, here passing the result set to the template update.gohtml allows for editing the details. The results are shown here:

this window will edit the selected record

Following is the HTML tags for the update.gohthml:

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
{{template "header"}}
<div id="content">
<a class="back-link" href="/"> « Back to the List</a>
<div class="subject edit">
<h2> Edit Restaurant's Details</h2>
<form action="/restaurants/update/process" method="post">
<div class="form-group col-md-3" >
<Label>Restaurant's Name</label>

</div>
<div class="form-group col-md-3" >
<Label>Restaurant's ID</label>

</div>
<div class="form-group col-md-3">
<label>Phone:</label>

</div>
<div class="form-group col-md-3">
<label >Email Address</label>

</div>
<div class="form-group col-md-3">
<label >Stars:</label>

</div>
<div class="form-group col-md-3">
<label >Category:</label>

</div>

</form>
</div>
</div>
{{template "footer"}}

The above form’s action /restaurants/update/process calls to another controller function in the func main() , being http.HandleFunc("/restaurants/update/process", controllers.UpdateProcess). This in turn calls to another controller function of controllers.UpdateProcess. This is shown in the following script:

1
2
3
4
5
6
7
8
9
10
11
12
func UpdateProcess(w http.ResponseWriter, r *http.Request) {
if r.Method != "POST" {
http.Error(w, http.StatusText(405), http.StatusMethodNotAllowed)
return
}
resto, err := models.UpdateResto(r)
if err != nil {
http.Error(w, http.StatusText(500), http.StatusInternalServerError)
return
}
config.TPL.ExecuteTemplate(w, "show.gohtml", resto)
}

The above controller function will update the details of the record and then call a controller that will show the updated version of the record. This is shown in the following image:

The updated version of the record

Notice the new category of the record with the ID of “7” was changed to “pizza.”

How to Delete a Record

To delete a specific record in the CockroachDB database, click in the Delete link with the following HTML tags <td><a class="action" href="/restaurants/delete/process?id={{.Id}}">Delete</a></td> . This calls for a controller function in the func main() of http.HandleFunc("/restaurants/delete/process", controllers.DeleteProcess). This is shown in the following script:

1
2
3
4
5
6
7
8
9
10
11
12
func DeleteProcess(w http.ResponseWriter, r *http.Request) {
if r.Method != "GET" {
http.Error(w, http.StatusText(405), http.StatusMethodNotAllowed)
return
}
err := models.DeleteResto(r)
if err != nil {
http.Error(w, http.StatusText(500), http.StatusInternalServerError)
return
}
http.Redirect(w, r, "/restaurants", http.StatusSeeOther)
}

This function only calls to the model function models.DeleteResto(r). If the “delete” operation was successful, the system will show the updated list of the records in the index page as shown here:

1
2
3
4
5
6
7
8
9
10
11
12
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
}

The above code simply performs the “delete” operation. As shown in the following image, the record with an ID of “7” has been deleted from the record:

Deleted the recoed with an id of 7

Conclusion

This was the final part in the five-part tutorial explaining how to build a simple Golang and CockroachDB web App via the MVC pattern. This five-part tutorial explained how to create a CockroachDB record using Golang, update a CockroachDB record using Golang, delete a CockroachDB using Golang and retrieve data in CockroachDB using Golang. This final section specifically explained how to edit, update and delete a single record. The full code for the entire five-part tutorial follows:

Just the Code for the Entire Five-Part Tutorial

The main.go

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

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

_ "github.com/lib/pq"
)
func main() {
http.HandleFunc("/", index)
http.HandleFunc("/restaurants", controllers.Index)
http.HandleFunc("/restaurants/show", controllers.Show)
http.HandleFunc("/restaurants/create", controllers.Create)
http.HandleFunc("/restaurants/create/process", controllers.CreateProcess)
http.HandleFunc("/restaurants/update", controllers.Update)
http.HandleFunc("/restaurants/update/process", controllers.UpdateProcess)
http.HandleFunc("/restaurants/delete/process", controllers.DeleteProcess)
http.ListenAndServe(":9090", nil)
}

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

The Config files

the db.go

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

import (
"database/sql"
"fmt"

_ "github.com/lib/pq"
)

var DB *sql.DB

func init() {
var err error
connStr := "postgres://yeshua:password@localhost:26257/restaurants?sslmode=disable"
DB, err = sql.Open("postgres", connStr)
if err != nil {
panic(err)
}

if err = DB.Ping(); err != nil {
panic(err)
}
fmt.Println("Connected to the database")
}

the tpl.go

1
2
3
4
5
6
7
8
package config

import "html/template"

var TPL *template.Template
func init() {
TPL = template.Must(template.ParseGlob("templates/*.gohtml"))
}

The Controllers

handlers.go

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
package controllers

import (
"database/sql"
"net/http"
"webGo/config"
"webGo/models"
)

func Index(w http.ResponseWriter, r *http.Request) {

if r.Method != "GET" {
http.Error(w, http.StatusText(405), http.StatusMethodNotAllowed)
return
}

restos, err := models.AllResto()

if err != nil {
http.Error(w, http.StatusText(500), 500)
return
}

config.TPL.ExecuteTemplate(w, "index.gohtml", restos)
}

func Show(w http.ResponseWriter, r *http.Request) {
if r.Method != "GET" {
http.Error(w, http.StatusText(405), http.StatusMethodNotAllowed)

return
}

resto, err := models.OneRestaurant(r)
switch {
case err == sql.ErrNoRows:
http.NotFound(w, r)
return
case err != nil:
http.Error(w, http.StatusText(500), http.StatusInternalServerError)
return
}

config.TPL.ExecuteTemplate(w, "show.gohtml", resto)
}

func Create(w http.ResponseWriter, r *http.Request) {

Count := models.CreateId()
config.TPL.ExecuteTemplate(w, "new.gohtml", Count+1)
}

func CreateProcess(w http.ResponseWriter, r *http.Request) {
if r.Method != "POST" {
http.Error(w, http.StatusText(405), http.StatusMethodNotAllowed)
return
}

_, err := models.CreateRestaurant(r)
if err != nil {
http.Error(w, http.StatusText(406), http.StatusNotAcceptable)
return
}

http.Redirect(w, r, "/restaurants", http.StatusSeeOther)
}

func Update(w http.ResponseWriter, r *http.Request) {
if r.Method != "GET" {
http.Error(w, http.StatusText(405), http.StatusMethodNotAllowed)
return
}

resto, err := models.OneRestaurant(r)
switch {
case err == sql.ErrNoRows:
http.NotFound(w, r)
return
case err != nil:
http.Error(w, http.StatusText(500), http.StatusInternalServerError)
return
}

config.TPL.ExecuteTemplate(w, "update.gohtml", resto)
}

func UpdateProcess(w http.ResponseWriter, r *http.Request) {
if r.Method != "POST" {
http.Error(w, http.StatusText(405), http.StatusMethodNotAllowed)
return
}
resto, err := models.UpdateResto(r)
if err != nil {
http.Error(w, http.StatusText(500), http.StatusInternalServerError)
return
}
config.TPL.ExecuteTemplate(w, "show.gohtml", resto)
}

func DeleteProcess(w http.ResponseWriter, r *http.Request) {
if r.Method != "GET" {
http.Error(w, http.StatusText(405), http.StatusMethodNotAllowed)
return
}
err := models.DeleteResto(r)
if err != nil {
http.Error(w, http.StatusText(500), http.StatusInternalServerError)
return
}

http.Redirect(w, r, "/restaurants", http.StatusSeeOther)
}

The Models

models.go

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
package models

import (
"errors"
"log"
"net/http"
"strconv"
"webGo/config"
)

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

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

}

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
}

func CreateId() int {

var Count int
rows, err := config.DB.Query("SELECT id FROM tblrestaurants ORDER BY id DESC LIMIT 1")
checkErr(err)
if err != nil {
return 0
}

defer rows.Close()
for rows.Next() {
err := rows.Scan(&Count)
if err != nil {
log.Fatal(err)
}
}

return Count

}


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

}

func CreateRestaurant(r *http.Request) (Restaurant, error) {

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")

if i == "" || resto.Name == "" || resto.Phone == "" || resto.Email == "" || s == "" || resto.Category == "" {

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

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)

_, 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

}

func UpdateResto(r *http.Request) (Restaurant, error) {

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")

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

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)

_, 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

}

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
}

The Templates

index.gohtml

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
{{template "header"}}

<br>
<div id="content">
<div class="restaurant listing">
<h1>Restaurant Listing</h1>
<div class="actions">
<a class="action" href="/restaurants/create">Create New Restaurant</a>
</div>
<br>
<div>
<p>Type something in the input field to search the table for name, email, or category:</p>

</div>
<br>
<table class="table table-sm table-bordered ">
<thead class="thead-dark">

<tr>
<th>id</th>
<th>name</th>
<th>phone</th>
<th>email</th>
<th>stars</th>
<th scope="col" colspan="3">category</th>
<th> </th>
</tr>

</thead>

<tbody id="myTable">
{{range .}}
<tr>
<td>{{.Id}}</td>
<td>{{.Name}}</td>
<td>{{.Phone}}</td>
<td>{{.Email}}</td>
<td>{{.Stars}}</td>
<td>{{.Category}}</td>
<td><a class="action" href="/restaurants/show?id={{.Id}}">View</a></td>
<td><a class="action" href="/restaurants/update?id={{.Id}}">Edit</a></td>
<td><a class="action" href="/restaurants/delete/process?id={{.Id}}">Delete</a></td>
</tr>
{{end}}
</tbody>

</table>


</div>
</div>

{{template "footer"}}

show.gohtml

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
{{template "header"}}
<div id="content">
<a class="back-link" href="/"> « Back to the List</a>
<div class="subject show">
<h2>Restaurant's Name: {{.Name}} </h2>
<div class="attributes">
<dl>
<dt>Phone Number:</dt>
<dd>{{.Phone}}</dd>
</dl>
<dl>
<dt>Email Address:</dt>
<dd>{{.Email}}</dd>
</dl>
<dl>
<dt>Stars:</dt>
<dd>{{.Stars}}</dd>
</dl>
<dl>
<dt>Category:</dt>
<dd>{{.Category}}</dd>
</dl>
</div>
</div>
</div>
{{template "footer"}}

update.gohtml

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
{{template "header"}}
<div id="content">
<a class="back-link" href="/"> « Back to the List</a>

<div class="subject edit">
<h2> Edit Restaurant's Details</h2>
<form action="/restaurants/update/process" method="post">
<div class="form-group col-md-3" >
<Label>Restaurant's Name</label>

</div>
<div class="form-group col-md-3" >
<Label>Restaurant's ID</label>

</div>
<div class="form-group col-md-3">
<label>Phone:</label>

</div>
<div class="form-group col-md-3">
<label >Email Address</label>

</div>
<div class="form-group col-md-3">
<label >Stars:</label>

</div>
<div class="form-group col-md-3">
<label >Category:</label>

</div>

</form>
</div>
</div>

{{template "footer"}}

new.gohtml

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
{{template "header"}}

<div id="content">
<a class="back-link" href="/"> « Back to the List</a>

<div class="subject new">
<h2> Create Restaurant</h2>
<form action="/restaurants/create/process" method="post">
<div class="form-group col-md-3" >
<Label>Restaurant's ID :</label>

</div>
<div class="form-group col-md-3" >
<Label>Restaurant's Name :</label>

</div>
<div class="form-group col-md-3">
<label>Phone :</label>

</div>
<div class="form-group col-md-3">
<label >Email Address :</label>


</div>
<div class="form-group col-md-3">
<label >Stars :</label>

</div>
<div class="form-group col-md-3">
<label >Category :</label>

</div>

</form>
</div>
</div>

{{template "footer"}}

header.gohtml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
{{define "header"}}
<!DOCTYPE html>
<!--[if lt IE 7]> <html class="no-js lt-ie9 lt-ie8 lt-ie7"> <![endif]-->
<!--[if IE 7]> <html class="no-js lt-ie9 lt-ie8"> <![endif]-->
<!--[if IE 8]> <html class="no-js lt-ie9"> <![endif]-->
<!--[if gt IE 8]><!--> <html class="no-js"> <!--<![endif]-->
<head>


<title>Golang Restaurant WebApp</title>




</body>
</div>

</html>
{{end}}
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.