How to Update Records in CockroachDB using Go language
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:
1 | go version |
The result should resemble the following:
1 | 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:
1 | go get -u github.com/lib/pq |
How to create a sample database
Use the following statements to create the user “abi” and userdatabase:
1 2 3 4 | -- 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:
1 | 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:
1 2 3 4 5 6 7 8 | 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:
1 2 3 4 5 | 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:
1 | package main |
How to Import Dependencies
Executing the following block of code will download the necessary dependencies:
1 2 3 4 5 6 7 | 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 thePrintln()
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"
packagepq
is Go’s pure Postgres driver for the abovedatabase/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:
1 2 3 4 5 6 7 8 | 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:
1 2 3 4 5 6 7 8 9 10 11 | 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 stringpostgres
is the driver’s way of registering itself withdatabase/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 theuserdatabase
within thepostgresql
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | 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” :
1 | go run update.go |
The result should resemble the following:
1 | 1 marks 22 user accounting assistant |
The Complete Code
The entire code for this tutorial 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 | 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