How to Delete CockroachDB Record using Golang Web App

Introduction

This tutorial will explain how to delete a record in CockroachDB using the Golang web app. Instructions on how to delete a CockroachDB record with Golang and how to remove a CockroachDB record with Golang will be provided. The tutorial will provide examples for the necessary dependencies, creating a package-level variable that can be used globally and how to connect to the database to delete a record in CockroachDB with Golang. CockroachDB and Golang must be properly installed and configured on the same machine to remove data in CockroachDB with the Golang web app.

Prerequisites

  • CockroachDB must be properly installed and configured.

  • Go language must be properly installed and configured on the same machine.

  • A basic understanding of Go language’s “nethttp” package is required.

Run the following command to confirm Golang is installed:

go version

The result should resemble the following:

go version go1.12.5 linux/amd64

Go will display the currently-installed version and what OS the program was installed with. Note that this can vary according to the version and OS.

How to Start a CockroachDB Local Cluster

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

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

The results in the terminal should resemble the following:

*
* WARNING: RUNNING IN INSECURE MODE!
*
* - Your cluster is open for any client that can access localhost.
* - Any user, even root, can log in without providing a password.
* - Any user, connecting as root, can read or write any data in your cluster.
* - There is no network encryption nor authentication, and thus no confidentiality.
*
* See how to secure the cluster: 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:

cockroach sql --insecure

The results should resemble the following:

# 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 Create the Sample Database

Now knowing how to connect to the CockroachDB instance, create a sample database to use in the code examples. Execute the following statements to create the “yeshua” user and “restaurants” database:

-- create user "yeshua"
CREATE USER IF NOT EXISTS yeshua;
-- create database "restaurants"
CREATE DATABASE restaurants;

Execute the SQL statement SELECT DATABASE = restaurants; to employ the database.

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

GRANT ALL ON DATABASE restaurants TO yeshua;

After the database has been created, create a basic table with the following SQL statement:

CREATE TABLE tblrestaurants(
id INT PRIMARY KEY,
name VARCHAR,
phone VARCHAR,
email VARCHAR,
stars INT,
category VARCHAR
);

Next, ad records in the “tblrestaurants” table with the following commands:

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 when finished.

How to Use the Go pq Driver in CockroachDB

Now connect the Go pq driver to the CockroachDB that was created.

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

The Package Main

The package main in Golang script tells the Go compiler to compile the package as an executable program, and not as a shared library, as shown here:

package main

How to Import Dependencies

Now download the necessary dependencies with the following commands:

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 Go.

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

  3. The net/http package allows the use of 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. Use the underscore _ as the package alias to load the driver anonymously so the exported names will not be visible to the code.

NOTE: It is best not to use the driver packages directly in the code. Instead, refer to types defined in database/sql to avoid making the code dependent on the specific driver being used.

How to Access the Database

Now that the necessary dependencies have been imported, connect to the database.

Use the following command to create the package level variable “db” that can then be used globally within this package:

var db *sql.DB

The below command uses the function init(). This command allows for the effective setup of the database connections and will be called only once:

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 code opens a sql.open connection and then assign it to the db variable to check for errors.

Enter the following script into the func main() to test the connection with the database (the script will be discussed later in the tutorial):

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

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

go run main.go

The result should resemble the following:

Connected to the database

How to Delete a Record in CockroachDB

Execute the following script:

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

id := 2
_, err := db.Exec("DELETE FROM tblrestaurants WHERE id=$1;", id)
if err != nil {
http.Error(w, http.StatusText(500), http.StatusInternalServerError)
return
}

fmt.Fprintf(w, "Record deleted!")

}
  1. Execute db.Exec to perform the DELETE operation for the new record and then check for errors. Use $1 as the parameter where $1 is the pointer to indicate what record will be deleted.

  2. Print the notification in the browser.

The func main()

With an understanding of how the Golang script functions, the func main() entry point of the script is as follows:

func main() {
http.HandleFunc("/delete", deleteRestaurant) // (1)
http.ListenAndServe(":9090", nil) // (2)
}

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

NOTE: The name of the function can be passed without the parentheses when passing a function as a second parameter to the HandleFunc method. This process is commonly known as “callback functions.”

  1. This will handle URLs with the prefix “/delete” and calls the function deleteRestaurant.

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

Now test the code again by navigating to the project folder via the terminal with the following command:

go run main.go

View the results in the browser using the following URL localhost:9090/delete.

The browser should now display the following text:

Record Deleted!

Now verify the changes via Shell using the following command:

SELECT * FROM tblrestaurants;

The result should resemble the following:

id | name | phone | email | stars | category
+----+-------------------------+----------------+---------------------------+-------+----------+
1 | Pure | 847-585-0174 | purebeauty2@example.net | 4 | Coffee
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
(5 rows)

As shown above, the record was successfully deleted with an id of “2.”

Conclusion

This tutorial explained how to delete a record in CockroachDB with Golang using Go pq driver via the web app. Explanations were provided for how to delete a CockroachDB record with Golang and how to remove a CockroachDB record with the Golang pq driver. Examples were also provided for accessing the database, importing dependencies, start the CockroachDB local cluster and how to create a sample database. When removing data in CockroachDB with Golang, remember that when operating in an insecure mode the cluster is open to any client that can access localhost. Additionally, when passing a function as a second parameter the name of the function can be passed without parentheses.

The Code

This tutorial examined the Go script examples one section at a time. Below is the code in its entirety:

package main

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

)


var db *sql.DB
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("/delete", deleteRestaurant)
http.ListenAndServe(":9090", nil)
}

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

id := 2
// delete restaurant
_, err := db.Exec("DELETE FROM tblrestaurants WHERE id=$1;", id)
if err != nil {
http.Error(w, http.StatusText(500), http.StatusInternalServerError)
return
}
fmt.Fprintf(w, "Record Deleted!")
}

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.