How to Update CockroachDB Record using the Golang Web App

Written by Data Pilot
July 15, 2019
  • CockroachDB
  • Golang Web App
  • pq driver
Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

This tutorial will explain how to update a CockroachDB record with Golang, edit a CockroachDB record with Golang and change a CockroachDB with Golang. It will specifically cover how to use the Go pq driver, how to import dependencies and verify the update process was successful. CockroachDB and Golang must be installed on the same machine to execute the examples in this tutorial.

Prerequisites

  • Possess a basic working knowledge of the Golang “nethttp” package.

  • CockroachDB must be correctly installed and configured before beginning.

  • Golang must be correctly installed and configured before beginning. Confirm Go is properly installed with the following command:

1
go version

The results should resemble the following:

1
go version go1.12.5 linux/amd64

Golang will display the currently-installed version and what OS Golang was installed with. This may vary depending on the Golang version and operating system.

How to Start a CockroachDB Local Cluster

First, start a CockroachDB local cluster with the following command:

1
cockroach start --insecure --listen-addr=localhost:26257 --http-addr=localhost:8080
  1. The --insecure flag instructs CockroachDB to allow unencrypted communication.
  2. The --listen-addr=localhost:26257 instructs the node to listen only on localhost and specifies the port number of “26257”.
  3. The --http-addr=localhost:8080 instructs the node to listen for HTTP requests at port “8081”.

The results displayed in the terminal should resemble the following:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
*
* WARNING: RUNNING IN INSECURE MODE!
*
* - The cluster is accessible to any client who is able to access localhost.
* - All users, including root, will be able to log in without a password.
* - Any user, connected as root, can read and/or write any data in the cluster.
* - As there is neither network encryption or authentication there is no confidentiality.
*
* More information on how to secure a cluster can be found at: https://www.cockroachlabs.com/docs/v19.1/secure-a-cluster.html
*
CockroachDB node starting at 2019-07-08 02:43:17.290017599 +0000 UTC (took 2.0s)
build: CCL v19.1.1 @ 2019/05/15 20:27:00 (go1.11.6)
webui: http://localhost:8081
sql: postgresql://root@localhost:26257?sslmode=disable
client flags: cockroach <client cmd="cmd"> --host=localhost:26257 --insecure
logs: /home/username/cockroach-data/logs
temp dir: /home/username/cockroach-data/cockroach-temp962568206
external I/O path: /home/username/cockroach-data/extern
store[0]: path=/home/username/cockroach-data
status: restarted pre-existing node
clusterID: f21dc71c-88dc-497b-84b9-2264a0d2e497
nodeID: 1

Connect to the above CockroachDB instance by opening another terminal and executing the following command:

1
cockroach sql --insecure

The results should look like the following:

1
2
3
4
5
6
7
8
9
10
# Welcome to the cockroach SQL interface.
# All statements must be terminated by a semicolon.
# To exit: CTRL + D.
#
# Server version: CockroachDB CCL v19.1.1 (x86_64-unknown-linux-gnu, built 2019/05/15 20:27:00, go1.11.6) (same version as client)
# Cluster ID: f21dc71c-88dc-497b-84b9-2264a0d2e497
#
# Enter ? for a brief introduction.
#
root@:26257/defaultdb>

How to Creating the Sample Database

Now create a sample database to use in the code examples. With following statements, create the user “yeshua” and a “restaurants” database:

1
2
3
4
-- create user "yeshua"
CREATE USER IF NOT EXISTS yeshua;
-- create database "restaurants"
CREATE DATABASE restaurants;

Use the SQL statement SELECT DATABASE = restaurants; to access the database.

For this tutorial, the user “yeshua” will be granted both read and write permissions to the database with the following commands:

1
GRANT ALL ON DATABASE restaurants TO yeshua;

After the database has been created, a simple table can be inserted with the following SQL statement:

1
2
3
4
5
6
7
8
CREATE TABLE tblrestaurants(
id INT PRIMARY KEY,
name VARCHAR,
phone VARCHAR,
email VARCHAR,
stars INT,
category VARCHAR
);

Now add records to the “tblrestaurants” table as shown here:

1
2
3
4
5
6
7
INSERT INTO tblrestaurants (id, name, phone, email, stars, category) VALUES
(1,'Pure','847-585-0174','purebeauty2@example.net',4,'Coffee'),
(2,'yumster delicacy','225-456-0102','yumsterD@example.com',1,'Italian'),
(3,'raizel cafeteria','857-555-0182','raizelbalooga@example.com',3,'Dessert'),
(4,'Hungry Pirate Resto Bar','908-555-045','PirateBar@example.com',5,'Pasta'),
(5,'Abi Cafe','1234-2123-1231','Abi@example.com',5,'Pizza'),
(6,'Rommel','1222-1214-5678','rommelBurger@example.com',4,'Burgers');

Use q to exit the SQL shell after everything has been set up.

How to Use the Go pq Driver in CockroachDB

This section will explain how to connect the Golang pq driver to the CockroachDB created earlier.

Create a new file in the project directory and save it with a “.go” extension. In this case the file will be saved as main.go.

The Package Main

The package main in Golang script, as shown below, instructs the Golang compiler to compose the package as an executable program and not as a shared library.

1
package main

How to Import Dependencies

Execute the following commands to download the required dependencies:

1
2
3
4
5
6
7
import (

"database/sql"
"fmt"
"net/http"
_ "github.com/lib/pq"
)

  1. The database/sql is the idiomatic way of using the SQL, or any SQL-like database, in Golang.

  2. The fmt package provides I/O formatting, such as the Println() function.

  3. The net/http package allows the use of the HTTP client and server implementations.

  4. The "github.com/lib/pq" package pq is Go’s pure Postgres driver for the above database/sql package.

The underscore _ is used as the package alias to anonymously load the driver so the exported names won’t be read by the code.

NOTE: It is very important to not use the driver packages directly. This can result in the code being dependent on the driver used to create it. Instead, refer to the types defined in database/sql.

The Golang ‘struct’ for the CockroachDB Field

Use the struct command, shown below, to create a collection of named fields/properties that matches the fields in the previously created tblrestaurants:

1
2
3
4
5
6
7
8
type Restaurant struct {
Id int
Name string
Phone string
Email string
Stars int
Category string
}

How to Access the Database

Now that the necessary dependencies have been imported and the struct that will hold the values of the database records are created, connect to the database by executing the following script:

NOTE: This action will generate a new package-level variable “db” that can then be executed globally inside this package.

1
var db *sql.DB

The below script uses the function init().

NOTE: This function will be used only once to allow the database connections to be set up properly.

1
2
3
4
5
6
7
8
9
10
11
12
13
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 above code is designed to just open a connection sql.open and then assign it to db to check for errors.

Now test the database connection with the following script entered into the func main():

1
2
3
func main(){
http.ListenAndServe(":9090", nil)
}

NOTE: This specific piece of script will be discussed in greater detail later in the tutorial.

Next, save the file and then open a terminal and navigate to the project directory. Now run the following command:

1
go run main.go

The result should resemble the following:

1
Connected to the database

How to Update Record in CockroachDB

Execute the following script:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
func updateRestaurant(w http.ResponseWriter, r *http.Request) {


resto := Restaurant{}
resto.Id = 2
resto.Name = "Yumster"


// update values
_, err = db.Exec("UPDATE tblrestaurants SET name=$2 WHERE id=$1;", resto.Id, resto.Name)
if err != nil {
http.Error(w, http.StatusText(500), http.StatusInternalServerError)
return
}
fmt.Fprintf(w, "Record Updated!")

}

An explanation of how the above code functions is as follows:

  1. Values, for later use, were explicitly assigned to the fields within the struct Restaurant{}.

  2. The db.Exec command was used to perform the INSERT operation for the new record and then checked for errors.

In this example, $1 and $2 are used as the parameters where $1 is the pointer that designates the record that will be update and $2 is the actual value that will be used to update the record.

  1. Now print a notification in the browser.

The Func main()

With an understanding of how the Golang script functions, the following is the script for the entry point to the func main():

1
2
3
4
func main() {
http.HandleFunc("/updateRetaurants", updateRestaurant) // (1)
http.ListenAndServe(":9090", nil) // (2)
}

The http.HandleFunc() simply takes a string as the first parameter and a function as the second parameter.

NOTE: Only the name of the function, without the parentheses, is required when passing a function as second parameter to the HandleFunc method. This is typically referred to as “callback functions.”

  1. This function will manage URLs with the prefix “/updateRetaurants” and calls the function updateRestaurant.

  2. The server will respond to port “9090”.

Now try the code again. Navigate to the project folder via the terminal with the following script:

1
go run main.go

View the results in the browser by navigating to the following URL: localhost:9090/restaurants.

The browser should now show the following text:

1
Record Updated!

Verify the changes via Shell with the following command:

1
SELECT * FROM tblrestaurants WHERE id=2;

The result should resemble following:

1
2
3
4
id | name | phone | email | stars | category
+----+---------+--------------+----------------------+-------+----------+
2 | Yumster | 225-456-0102 | yumsterD@example.com | 1 | Italian
(1 row)

As shown above, the name field of the record has been successfully updated.

Conclusion

This tutorial explained how to update a record in CockroachDB using the Go pq driver via the web app. This tutorial demonstrated how to update CockroachDB record with Golang, how to edit CockroachDB record with Golang and change a CockroachDB. Specifically it explained how to start a CockroachDB local cluster, how to create a sample database, how to use the Go pq Driver and how to import dependencies. Remember that when using the callback function only the name of the function is required. Also, do not use the driver packages directly or the code will become dependent on the driver that created it.

Just the Code

This tutorial provided specific examples for updating records in CockroachDB using the Golang script. The full code, without examples, follows:

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

import (
"database/sql"
"fmt"
"net/http"
"text/template"

_ "github.com/lib/pq"
)

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

var db *sql.DB
var tpl *template.Template
var err error

func init() {

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

tpl = template.Must(template.ParseGlob("templates/*gohtml"))
}

func main() {
http.HandleFunc("/createRestaurant", createRestaurant)
http.ListenAndServe(":9090", nil)
}


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

resto := Restaurant{}
resto.Id = 2
resto.Name = "Yumster"


// update values
_, err = db.Exec("UPDATE tblrestaurants SET name=$2 WHERE id=$1;", resto.Id, resto.Name)
if err != nil {
http.Error(w, http.StatusText(500), http.StatusInternalServerError)
return
}
fmt.Fprintf(w, "Record Updated!")
}
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.