How to Update Records in CockroachDB using Go language

Written by orkb

July 05, 2019

Introduction

This tutorial demonstrates how to edit a record in CockroachDB with Golang. Golang, or Go for short, is a statically-typed language similar in syntax to C. In order to change data in CockroachDB with Golang, both Cockroach and Golang must both be installed on the same machine to execute the functions outlined in this tutorial.

Prerequisites

CockroachDB must be properly installed and configured before beginning.
Go language must be properly installed and configured on the same machine as CockroachDB before beginning.
Confirm Go language is installed with the following command:

go version

The result should resemble the following:

go version go1.12.5 linux/amd64

As shown above, Golang will display the currently-installed version and what OS it was installed with. The results will of course vary depending on what version is installed.

How to Install the Go pq Driver

The Go pq driver is easily installed by running the below command in the terminal:

go get -u github.com/lib/pq

How to create a sample database

Use the following statements to create the user “abi” and userdatabase:

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

To employ the database execute SELECT DATABASE = userdatabase;

For this tutorial the user “abi” will be given read and write permission to the database as in the following example:

GRANT ALL ON DATABASE userdatabase TO abi;

user q to exit the SQL shell.

With the database now set up, a simple table can be created with the following statement:

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

Now place some records in the tblusers with the following commands:

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');

How to Use the Go pq Driver in CockroachDB

This section will demonstrate how to connect Go language to a CockroachDB database and edit a record in CockroachDB with the Golang pq driver.

Begin by creating a new file and naming it update.go to match the operation. The below code will be used in the newly created file. All codes will broken down into parts and discussed as the file is built.

The Package Main

The following package main command instructs the Go compiler to compile the package as an executable program, and not as a shared library:

package main

How to Import Dependencies

Executing the following block of code will download the necessary dependencies:

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

_ "github.com/lib/pq"
)

Import database/sql in order to be able to access its defined types in the code; this is the idiomatic way of using the SQL or any SQL-like database in Go.
The fmt – Provides a function similar to the Println() function.
The log – Allows for the performance of logging standard errors.

NOTE: The database/sql is the idiomatic way of using SQL, and the driver packages should not be used directly. Instead, the code should refer to types defined in database/sql to not make the code driver dependent.

The "github.com/lib/pq" package pq is Go’s pure Postgres driver for the above database/sql package. The underscore _ is the package alias for loading the driver anonymously so the exported names will not be visible to the code.

How to Access the Database

This section will show how to connect to the database via a driver or via defined types in database/sql.

How to connect to CockroachDB database via a driver

Enter the following code to connect via a driver:

func main() {

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

defer db.Close()

How to connect to CockroachDB Database via database/sql

Enter the following code to connect via a database:

func main() {

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

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

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

defer db.Close()

Understanding the two above methods

The first argument in the sql.Open() function is the driver name "postgres". This string postgres is the driver’s way of registering itself with database/sql.
The second argument in the sql.Open() is the driver-specific syntax on how to access the named database. The code will allow connection to the userdatabase within the postgresql instance.
The succeeding code will handle any errors the database query/operation returns.
The defer db.Close() ensures the connections will be terminated cleanly and resource properly deallocated when the program exits.

How to Update Data in CockroachDB Using Go pq Driver

This section will explain how to perform the update operation in CockroachDB using the Go pq driver.

stmt, err := db.Prepare("UPDATE tblusers SET name = $1 WHERE id=$2")
if err != nil {
log.Fatal(err)
}

defer stmt.Close()

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

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

fmt.Println(affect, "rows changed")

}

Understanding the above code

The db.Prepare() prepares queries that can be used multiple times. Here the placeholders of $1, $2 instead of ? are used for the parameters when executing the statement.

Its recommended to deallocate resources by using the defer stmt.Close() statement to free up memory.

NOTE: In the background the db.Query will prepare a statement, execute the statement and then finally closes the statement. Be careful when coding the application on telling it how it should interact with the database as it may double the round trips of the application. In the above instance there has already been three trips in the database.

Now navigate to the project directory and execute the following command to test “update.go” :

go run update.go

The result should resemble the following:

1 marks 22 user accounting assistant

The Complete Code

The entire code for this tutorial follows:

package main

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

_ "github.com/lib/pq"
)

func main() {

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

defer db.Close()

stmt, err := db.Prepare("UPDATE tblusers SET name = $1 WHERE id=$2")
if err != nil {
log.Fatal(err)
}

defer stmt.Close()

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

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

fmt.Println(affect, "rows changed")
}

Conclusion

This tutorial explained the idiomatic way of connecting Go driver to a CockroachDB database using the Go pq driver and how to edit a record in CockroachDB with Golang. It also provided examples of connecting to CockroachDB via database/sql, how to import dependencies, create a sample database and execute code for downloading the required dependencies. Remember that in order to change data in CockroachDB with Golang both programs must be installed on the same machine. It is also important to bear in mind that the database/sql is the idiomatic way of using SQL and the driver packages should not be used directly to update CockroachDB records with Golang. Modifying CockroachDB records with Golang is easily done by following the steps laid out in this tutorial.

Pilot the ObjectRocket Platform Free!

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

Get Started