How to Update CockroachDB Record using the Golang Web App
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 |
- The
--insecure
flag instructs CockroachDB to allow unencrypted communication. - The
--listen-addr=localhost:26257
instructs the node to listen only on localhost and specifies the port number of “26257”. - 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" ) |
The
database/sql
is the idiomatic way of using the SQL, or any SQL-like database, in Golang.The
fmt
package provides I/O formatting, such as the Println() function.The
net/http
package allows the use of the HTTP client and server implementations.The
"github.com/lib/pq"
packagepq
is Go’s pure Postgres driver for the abovedatabase/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:
Values, for later use, were explicitly assigned to the fields within the struct
Restaurant{}
.The
db.Exec
command was used to perform theINSERT
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.
- 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.”
This function will manage URLs with the prefix “/updateRetaurants” and calls the function
updateRestaurant
.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!") } |
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started