How to Build a Simple Golang and CockroachDB Web App via the MVC pattern Part 3
/ 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:
- First,
package model
states the name of the package. - Then,
import
will proceed to import the necessary dependencies. - The
struct
will be the collection of fields that matches the fields we have in the database. - The
func AllResto()
will retrieve all the records in thetblrestaurants
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)
.
- 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()
:
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.After that, the
import
statements import all the necessary dependencies. Note that we are importing “webGo/controllers”, which is our controller.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, thecontrollers.Index
function will be called.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()
:
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 } |
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started