How to Insert a CockroachDB Record using Golang via Web App

Introduction

If you’re a Golang developer working with CockroachDB, you might consider building a web app that can interact with your database. Using the pq driver for Go makes it easy to accomplish this task. In this article, we’ll show you how to create a web app that can insert a CockroachDB record with Golang.

Prerequisites

There are only a few prerequisites for this task, but it’s important to confirm that they’re in place before proceeding:

  • First, ensure that CockroachDB has been properly installed and configured.

  • In addition, you must also ensure that Golang is properly installed and configured.

  • You’ll need to have a basic understanding of the Go language’s “nethttp” package.

If you’re not sure whether Go is installed on your machine, run this command:

go version

You should get results that look like the following:

go version go1.12.5 linux/amd64

Notice that the output includes the currently installed version of Go and the particular OS on which it was installed.

Start the CockroachDB Local Cluster

Before we can do anything with CockroachDB, we’ll need to start a CockroachDB local cluster:

cockroach start --insecure --listen-addr=localhost:26257 --http-addr=localhost:8080

Let’s dissect this command to understand the various flags and options:

  1. First, the --insecure flag sets the CockroachDB communication to be unencrypted.
  2. Next, the --listen-addr=localhost:26257 instructs the node to only listen on localhost and specifies which port number to listen on, which is “26257”.
  3. The --http-addr=localhost:8080 instructs the node to listen at port “8081” for any HTTP requests.

If you run the command shown above, you’ll see something like this in your terminal:

*
* 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.
*
* Check out how to secure your 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

We can connect to this new CockroachDB instance by opening another terminal window and running the following command:

cockroach sql --insecure

The output of this command should look something like 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

Creating The Sample Database

Now that we successfully connected to CockroachDB, the next thing we’ll do is create a sample database that can be used in our examples. The following SQL statements can be used to create the “yeshua” user and “restaurants” database:

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

To access the database after creating it, you can use the statement SELECT DATABASE = restaurants;

For this tutorial, the user “yeshua” will be given both read and write permission to the database:

GRANT ALL ON DATABASE restaurants TO yeshua;

Once the “restaurants” database has been created, a simple table can be created in it. This can be accomplished with the following SQL statement:

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

Later in this tutorial, we’ll put some record(s) in the “tblrestaurants” table.

When you’re done creating everything, use \q to quit the SQL shell.

How to Use the Go pq driver in CockroachDB

At this point, we’re ready to move forward and connect the Go pq driver to our CockroachDB instance, so let’s start working on our code. Create a new file in your project directory and save it with a “.go” extension. In this example, we’ll name the file “main.go”.

The package main

In Golang, package main tells the Go compiler that the package ought to be compiled as an executable program, not as a shared library.

package main

Importing Dependencies

Be sure to download the necessary dependencies using import statements:

import (

"database/sql"
"fmt"
"net/http"
_ "github.com/lib/pq"
)

Let’s look at each of these packages and what they’re used for:

  1. The database/sql package enables the use of SQL or other SQL-like databases in Go.

  2. The fmt package provides a variety of formatting functions such as the Println() function.

  3. The net/http package allows us to use HTTP client and server implementations.

  4. The "github.com/lib/pq" package pq is Go’s pure Postgres driver for the database/sql package mentioned above. Note that we use an underscore _ as a package alias– this loads the driver anonymously so its exported names will not be visible to our code.

NOTE: Avoid using the driver packages directly in your script; instead, refer to the types defined in the database/sql package. This will prevent your code from being dependent on the driver you are using.

Go lang ‘struct’ for the CockroachDB field

We use struct to create a collection of named fields and properties that matches the fields in our tblrestaurants table:

type Restaurant struct {
Id int
Name string
Phone string
Email string
Stars int
Category string
}

Accessing the Database

So far in our script, we’ve imported the necessary dependencies and created the struct that will hold the values of our database records. Now, we’ll connect to the database using the following script.

Here, we’re creating a package-level variable called “db” that we can use globally within this package:

var db *sql.DB

Then, we create the function init(). This will be called only one time and will allow us to set up the correct database connections:

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 shown above simply opens a connection with sql.Open(), assigns it to db and then checks for errors.

To test if you’re able to connect to the database, just add the following code to “main.go”. Don’t worry about what it does for now– we’ll explain that later in the article:

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

Save the file, then open a terminal window snd navigate to your project directory. Run the following command:

go run main.go

The output should look something like this:

Connected to the database

Insert a Record in CockroachDB

The next section of our code performs an insert operation into our “tblrestaurants” table.

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

var err error

resto := Restaurant{}
resto.Id = 1
resto.Name = "testRestaurant"
resto.Phone = "2212-1231-12312"
resto.Email = "testEmail"
resto.Stars = 3
resto.Category = "testCategory"

_, err = db.Exec("INSERT INTO tblrestaurants (id,name,phone,email,stars,category) VALUES ($1,$2,$3,$4,$5,$6)", resto.Id, resto.Name, resto.Phone, resto.Email, resto.Stars, resto.Category)
if err != nil {
http.Error(w, http.StatusText(500), http.StatusInternalServerError)
return
}

fmt.Fprintf(w, "Record Created: ")
fmt.Fprintf(w, "%d %s %s %s %d %s\n", resto.Id, resto.Name, resto.Phone, resto.Email, resto.Stars, resto.Category) // (3)
}

Let’s walk through this part of the code and talk about how it all works:

  1. First, we explicitly assign values to the fields within the struct Restaurant{} for later use.

  2. Then, we use db.Exec to perform the INSERT operation for the new record. We check to see if any errors occurred.

  3. Finally, we print the inserted record in the browser.

The func main()

We’ve already covered how much of our Golang script will function– now, let’s discuss the func main(), which is the entry point of our script.

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

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

NOTE: When passing a function as the second parameter to the HandleFunc() method, you only need to pass the name of the function without the parentheses. A function called inside another function in this manner is called a callback function.

Two things to note about this function:

  1. This will handle URLs with a prefix “/createRetaurants” and call the function createRestaurant.

  2. The server will listen on port “9090”.

Now test the code again by navigating to your project folder and using the following command:

go run main.go

After running it, you can view the results in the browser using the following URL localhost:9090/restaurants.

The browser should be able to display the following results:

The result of the INSERT operation in CockroachDB using Golang

Conclusion

If you’re planning to interact with CockroachDB via a web app, it’s important to know how to perform database operations such as inserting, deleting and updating records. In this article, we explained how to insert into CockroachDB with Golang by making use of the pq driver. You can use the code shown in this article as a model to create your own web app that inserts a CockroachDB record with Golang.

The Code

So far, we’ve looked at our example script one section at a time. Shown below is the code we reviewed in its entirety:

package main

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

_ "github.com/lib/pq"
)

type Restaurant struct {
Id int
Name string
Phone string
Email string
Stars int
Category string
}

var db *sql.DB
var tpl *template.Template

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")

tpl = template.Must(template.ParseGlob("templates/*gohtml"))
}

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


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

var err error

resto := Restaurant{}
resto.Id = 1
resto.Name = "testRestaurant"
resto.Phone = "2212-1231-12312"
resto.Email = "testEmail"
resto.Stars = 3
resto.Category = "testCategory"

_, err = db.Exec("INSERT INTO tblrestaurants (id,name,phone,email,stars,category) VALUES ($1,$2,$3,$4,$5,$6)", resto.Id, resto.Name, resto.Phone, resto.Email, resto.Stars, resto.Category)
if err != nil {
http.Error(w, http.StatusText(500), http.StatusInternalServerError)
return
}
fmt.Fprintf(w, "Record Created: ")
fmt.Fprintf(w, "%d %s %s %s %d %s\n", resto.Id, resto.Name, resto.Phone, resto.Email, resto.Stars, resto.Category)
}

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.