How to Delete a Record in CockroachDB using the Go Language

Introduction

When you store records in CockroachDB, there will likely be times when you need to delete some of those records. You can accomplish this task with just a few lines of Golang code, thanks to the pq driver for Go. In this article, we’ll show you how to create a script to delete a record from CockroachDB with Golang.

Prerequisites

Before we start working on our script to delete a CockroachDB record with Golang, we need to review a few system requirements that are necessary for this task:

  • You must confirm that CockroachDB is installed and configured.

  • You’ll also need to confirm that the Go language is properly installed and configured.

To confirm that Go is installed on your system, run the following command:

go version

You should see output that looks something like the following:

go version go1.12.5 linux/amd64

Go displays information on the currently installed version and which operating system it was installed on.

Installing Go pq Driver

Our next step is to install the Go pq driver. Run the command shown below in the terminal:

go get -u github.com/lib/pq

Creating a Sample Database

Now we’ll create a small sample database for the purpose of this tutorial. You can use the SQL statements to create the “risa” user and “userdatabase” database:

-- create user "risa"
CREATE USER IF NOT EXISTS risa;
-- create database "userdatabase"
CREATE DATABASE userdatabase;

After creating the database, you can access it by using the statement SELECT DATABASE = userdatabase;.

In this example, the user “risa” will be granted both read and write permission to the “userdatabase” database.

GRANT ALL ON DATABASE userdatabase TO risa;

Now that we have a new database to work with, we can go ahead and create a simple table as well. To do this, we’ll use the following statement:

CREATE TABLE tblusers(
id INT PRIMARY KEY,
name STRING,
age INT,
accessLevel STRING,
department STRING,
title STRING
);

We’ll need to put some records in the “tblusers” so that we can delete some of them in our code examples:

INSERT INTO tblusers (id, name, age, accessLevel, department, title) VALUES
(1, 'mark', 22, 'user', 'accounting', 'assistant'),
(2, 'gene', 24, 'user', 'research', 'rankfile'),
(3, 'don', 25, 'admin', 'ict', 'supervisor'),
(4, 'isaac', 20, 'user', 'marketing', 'assistant');

Once you’re all done with your SQL work, simply type \q to quit the SQL shell.

Using the Go pq driver in CockroachDB

The script we’ll be looking at in this section will be used to connect to CockroachDB and delete a record using the Go pq driver. Before we can write the code, we’ll need to create a new file. We’ll name it delete.go.

The package main

The package main, found at the beginning of the script, tells the compiler that the package needs to be compiled as a an executable program instead of a shared library:

package main

Importing the Dependencies

Next, we’ll download the necessary dependencies:

import (
"database/sql"
"fmt"
"log"

_ "github.com/lib/pq"
)
  1. We import database/sql so that we can use SQL or other SQL-like databases in Go.

  2. The fmt package offers various formatting functions such as Println().

  3. The log package simply enables us to log standard errors.

NOTE: When writing code that makes use of database/sql, don’t use the driver packages directly. Ideally, your code should refer to types defined in database/sql so that your code is not dependent on the driver you are using.

  1. The "github.com/lib/pq" package, often referred to as pq, is Go’s pure Postgres driver for the above database/sql package. Using the underscore _ as a package alias means we don’t have to use the entire package name in our code.

Accessing the CockroachDB Database

In this section, we’ll be showing two different ways to connect to the database: via the driver or via the defined types in database/sql.

Connecting to CockroachDB Database via Driver

func main() {

db, err := sql.Open("postgres", "postgresql://risa@localhost:26257/userdatabase?sslmode=disable")
if err != nil {
log.Fatal("error connecting to the database: ", err)
}

defer db.Close()

Connecting to CockroachDB Database via database/sql

func main() {

dbConnStr := "user=risa dbname=userdatabase host=localhost port=26257 sslmode=disable"

db, err := sql.Open("postgres", dbConnStr)

if err != nil {
log.Fatal(err)
}

defer db.Close()

Let’s take a closer look at what’s happening in both of these code examples:

  1. In the sql.Open() function, the first argument passed in is the driver name which is "postgres". This string postgres is the driver’s way of registering itself with database/sql.

  2. The second argument passed into the sql.Open() method provides the driver with information on how to access the stated database. The code will connect to the userdatabase which is found within the postgresql instance.

  3. The code that comes after this handles any potential errors that our database query/operation will return.

  4. Last but not least, the defer db.Close() closes connections cleanly and deallocates resources when the program ends.

Delete Data in CockroachDB via id using Go pq driver

The following code will demonstrate how to perform a delete operation in CockroachDB using the Go pq driver:

stmt, err := db.Prepare("DELETE FROM tblusers WHERE id=$1")
if err != nil {
log.Fatal(err)
}

defer stmt.Close()

res, err := stmt.Exec(1)
if err != nil {
log.Fatal(err)
}

affect, err := res.RowsAffected()
if err != nil {
log.Fatal(err)
}

fmt.Println(affect, "row(s) deleted")

}

Let’s see what’s happening in the code shown above:

  • The db.Prepare() method prepares queries that we can use multiple times. We are using the placeholder $1 instead of ? for the parameters when we execute the statement.

  • It’s wise to deallocate resources by using the defer stmt.Close() statement, which ensures that the connection is closed at the end of the script.

NOTE: What’s going on in the background is that the db.Query will prepare a statement, execute the statement and finally close a statement. Be careful when coding your application to control how this object interacts with the database– the goal is to minimize the total number of round trips of your application.

To test “delete.go”, navigate to the project directory then use the following command:

go run delete.go

You should see output that looks like the following:

2 gene 24 user research rankfile
3 don 25 admin ict supervisor
4 isaac 20 user marketing assistant

The results confirm that we successfully deleted the record with an id of 1.

Delete All Records in CockroachDB using the Go pq driver

In the previous example, we deleted a single record. We can also delete all records in a CockroachDB table using the following code:

rs, err := db.Exec("DELETE FROM tblusers")
if err != nil {
log.Fatal(err)
}

affect, err := res.RowsAffected()
if err != nil {
log.Fatal(err)
}

fmt.Println(affect, "row(s) deleted")

}

Notice in the code shown above that we didn’t use the prepare() method this time– we simply passed the delete SQL statement to the db.Exec() function to execute the SQL.

Also note that we removed the WHERE clause in the SQL statement; therefore, the delete operation will delete all the records in the specified table.

To test this updated version of “delete.go”, navigate to the project directory then use the following command:

go run delete.go
3 row(s) deleted

We already deleted one record in the previous section, but now we are deleting all of them.

Conclusion

If you’re planning to work with CockroachDB from a Go script, you may find yourself needing to delete records from time to time. Fortunately, the Go pq driver makes this task quick and simple. Just use the instructions and code examples provided in this tutorial, and you’ll be able to create your own script to remove data from CockroachDB with Golang.

The Code

The Golang script shown below contains all the segments of code we looked at in this tutorial:

package main

import (
"database/sql"
"fmt"
"log"

_ "github.com/lib/pq"
)

func main() {

stmt, err := db.Prepare("DELETE FROM tblusers WHERE id=$1")
if err != nil {
log.Fatal(err)
}

defer stmt.Close()

res, err := stmt.Exec(1)
if err != nil {
log.Fatal(err)
}

affect, err := res.RowsAffected()
if err != nil {
log.Fatal(err)
}

fmt.Println(affect, "row(s) deleted")
}

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

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.