How to Insert a Record in CockroachDB using the Go Language

Introduction

If you’re using CockroachDB to store data, you may want to perform certain database operations, such as inserting records, using the Go programming language. The Go pq driver makes this task an easy one to accomplish, enabling you to insert records into CockroachDB with a simple Golang script. In this article, we’ll show you how to insert a CockroachDB record using Golang.

Prerequisites

We’re almost ready to start looking at the code used to create a record in CockroachDB with Golang, but first we need to review a few prerequisites needed for the task:

  • First, you’ll need to ensure that CockroachDB is installed and configured.

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

To see if the Go language is installed on your system, run this command:

go version

The output of this command should look like the following:

go version go1.12.5 linux/amd64

When you run this command, Go will display the version that’s currently installed as well as the OS on which it was installed.

Installing the Go pq Driver

Installing the Go pq driver is a simple process. All you need to do is run the following command in the terminal:

go get -u github.com/lib/pq

Creating The Sample Database

In this section, we’ll set up a sample user and database to use in our code examples. You can use the following statements to create them:

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

To use the database after you’ve created it, use the statement SELECT DATABASE = userdatabase;

The user “yeshua” will be granted both read and write permission to the database:

GRANT ALL ON DATABASE userdatabase TO yeshua;

Now that our “userdatabase” database has been created, a simple table can be created as well. To do this, just use the following SQL statement:

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

When you’ve finished running all the SQL statements in this section, type \q to exit the SQL shell.

Using the Go pq driver in CockroachDB

At this point, we’re ready to look at the code that will be used to connect Golang to a CockroachDB database and create a new record using the Go pq driver.

To start this process, we will create a new file and save it with a “.go” extension. In this tutorial, our file will be saved as insert.go, but you can name your script whatever you want.

The package main

Our script needs to begin with the package main statement. This lets the Go compiler know that the package must be compiled as a an executable, not as a shared library.

package main

Importing Dependencies

The next step in our script is to import all of the required dependencies:

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

_ "github.com/lib/pq"
)

Let’s take a look at each of these dependencies:

  1. First, we import the database/sql package. This provides an interface for using SQL or another SQL-like database in Go.

  2. The fmt package provides I/O formatting through functions such as the Println() function.

  3. Importing the log package provides logging functionality to capture standard errors .

NOTE: Instead of using the driver packages directly, make sure your code refers to types that are defined in database/sql. This prevents your code from becoming dependent on the particular driver you are using.

  1. Finally, we import "github.com/lib/pq". This package is Go’s Postgres driver for the database/sql package that we just imported. The underscore _ is a package alias; we load the driver anonymously, which means that its exported names won’t be visible to our code.

Accessing Database

Next, let’s look at two ways you can connect to the database in your script: via the driver or via database/sql:

How to Connect to the CockroachDB Database via the Driver

func main() {

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

How to Connect to the CockroachDB Database via database/sql

func main() {

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

db, err := sql.Open("postgres", dbConnStr)
if err != nil {
log.Fatal(err)
}

defer db.Close()

Note that the first argument in sql.Open() is the name of the driver: "postgres". This is how the driver registers itself with database/sql. What happens is that database/sql looks up that name in an internal data map that contains information on all the registered drivers. This is how it learns about the specific driver being imported.

The second argument being passed into the sql.Open() method is driver-specific information about how to access the specified database. In this example, the code will connect to the userdatabase within the instance of postgresql.

  1. The following code handles any errors that our database query or operation may return.

  2. We include the defer db.Close() method to ensure that connections will be closed cleanly and resources will be properly deallocated when the program exits.

Insert Data in CockroachDB using the Go pq driver

The following code performs an insert 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")

}

It might look like there’s a lot going on in this code, but what’s happening is actually quite simple. Let’s dive in and take a closer look:

  • We’re using db.prepare() to prepare SQL queries that we can then use multiple times. When we execute the statement, we can use placeholders such as $1, $2 instead of ? for the parameters.

  • It’s best to use “defer” when you have open connections to the database. In this case, we’ll use defer stmt.Close() to deallocate resources and terminate the connection.

NOTE: In the background, db.Query will first prepare, then execute and ultimately close a SQL statement. When you write your code, pay attention to how it interacts with the database, because you don’t want to make unnecessary round trips of your application.

To test this script, just navigate to the project directory and use the following command:

go run insert.go

You should see output that looks something like this:

1 mark 22 user accounting assistant

Conclusion

When you’re working with CockroachDB, you’ll probably need to insert new records from time to time. Using the Go pq driver, it’s easy to create data in CockroachDB with Golang. With the help of the instructions provided in this tutorial, you’ll be ready to write your own script that can be used to insert records into CockroachDB.

Just The Code

Now that you understand how to insert a CockroachDB record with Golang, you may prefer to see all of the code at once. Shown below is the Go script from this tutorial in its entirety:

package main

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

_ "github.com/lib/pq"
)

func main() {

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


stmt, err := db.Prepare("INSERT INTO tblusers(id,name,age,accessLevel,department,title ) VALUES($1,$2,$3,$4,$5,$6)")
if err != nil {
log.Fatal(err)
}

defer stmt.Close()

res, err := stmt.Exec(9,"test8",28,"testadmin","testDept","testTitle")
if err != nil {
log.Fatal(err)
}

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

fmt.Println(affect, "record added")
}

Pilot the ObjectRocket platform free for 30 Days

It's easy to get started. Imagine the time you'll save by not worrying about database management. Let's do this!

PILOT FREE FOR 30 DAYS

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.