How to Retrieve CockroachDB Record using Golang Web App

Written by Data Pilot
July 12, 2019

Introduction

Retrieving records from a database can be challenging sometimes, but it doesn’t have to become a stressful way of life. There’s a simpler way to retrieve CockroachDB record Golang. Find cockroachDB record Golang. Try creating an uncomplicated web app that utilizes the Go Postgres (package pg) driver. This tutorial will show you how to retrieve CockroachDB record using Golang web app. Every step is important. Follow along to make your coding more efficient and stress-free.

Skip to Just the Code to review the sample script in its entirety if you want to bypass this tutorial because you already know how to search CockroachDB Golang and fetch data from CockroachDB Golang.

Prerequisites

  • Download and install the latest Go language binary release for your OS.

  • Download and install the CockroachDB database.

  • Use the go command to verify that the correct Go language version is installed on your OS.

1
go version
  • Here’s an example of what the result should resemble if you installed the Go language on a Linux OS.
1
go version go1.12.5 linux/amd64

Begin a local cluster in CockroachDB

  • Begin with a CockroachDB local cluster in order to communicate with CockroachDB.
1
cockroach start --insecure --listen-addr=localhost:26257 --http-addr=localhost:8080

Here’s what the different parts of the code indicate:

  • The flag --insecure means unencrypted communication.

  • The --listen-addr=localhost:26257 specifically states the localhost port “26257” is the only port to which the node is to listen.

  • The --http-addr=localhost:8080 precisely states the localhost 8080 is for HTTP requests.

  • In your terminal, the result should resemble this:
1
2
3
4
5
6
7
8
9
10
*
* 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
*
  • Now you can open a new terminal to make a CockroachDB instance connection. Use the code below:
1
cockroach sql --insecure
  • You should see a result similar to this one:
1
2
3
4
5
6
7
8
9
10
# 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>

Make a database sample to test the examples of code

  • Since the connection to the CockroachDB instance was successful, you can go ahead and create a database sample to run the examples in this tutorial.

  • Make a new user’s name “yeshua”.

  • Call the sample database “restaurants”.

1
2
3
4
-- create user "yeshua"
CREATE USER IF NOT EXISTS yeshua;
-- create database "restaurants"
CREATE DATABASE restaurants;

  • Declare the SQL SELECT DATABASE = restaurants; statement to access and use the database.

  • Give database permission to read and write to the “yeshua” user.

1
GRANT ALL ON DATABASE restaurants TO yeshua;
  • Use a SQL statement and create a basic table and name it “tblerestaurants”.
1
2
3
4
5
6
7
8
CREATE TABLE tblrestaurants(
id INT PRIMARY KEY,
name VARCHAR,
phone VARCHAR,
email VARCHAR,
stars INT,
category VARCHAR
);
  • Next, add a few records to the table you just created.
1
2
3
4
5
6
7
INSERT INTO tblrestaurants (id, name, phone, email, stars, category) VALUES
(1,`Pure`,`847-585-0174`,`purebeauty2@example.net`,4,`Coffee`),
(2,'yumster delicacy','225-456-0102','yumsterD@example.com',1,'Italian'),
(3,'raizel cafeteria','857-555-0182','raizelbalooga@example.com',3,'Dessert'),
(4,'Hungry Pirate Resto Bar','908-555-045','PirateBar@example.com',5,'Pasta'),
(5,'Abi Cafe','1234-2123-1231','Abi@example.com',5,'Pizza'),
(6,'Rommel','1222-1214-5678','rommelBurger@example.com',4,'Burgers');
  • Type q to exit the SQL terminal shell when finished.

Connect the Go pq driver

You’re ready to connect the CockroachDB database and the Go pq driver.

  • Add to your project directory a new file. Name it main.go. * >NOTE: Be sure the file has the extension “.go”.

Properly compile the package main

  • Make as an executable program the package main. Use a script in the Go language to compile it correctly.

>NOTE: If you compile the package main as a shared library, it won’t work out right.

1
package main

Download dependencies

  • Import the required dependencies using the Go language.
1
2
3
4
5
6
7
import (

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

Here’s an explanation of the code for importing dependences:

  • To use SQL databases and those databases like SQL in Go, use database/sql.

  • Similar to the Print() function is the fmt function. It formats as I/O.

  • You’ll be able to make API calls to the HTTP server and client with the package net/http.

  • The package database/sql requires the Postgres driver package "github.com/lib/pq" for Go. You can load it without the names showing up in the code by using the underscore _ at the beginning of the line of code.

>NOTE: Make your code driver nondependent by only using driver packages named within database/sql.

Use struct to create fields that match the database table

*Using Go lang’s struct is the way to create named properties or fields that are the same as the newly created table, tblrestaurants.

1
2
3
4
5
6
7
8
type Restaurant struct {
Id int
Name string
Phone string
Email string
Stars int
Category string
}

Connect the database

  • Everything is on track. You’ve successfully imported dependencies, created matching fields using Go lang’s struct command. Next, let’s connect to the CockroachDB database.

  • Obtain global usage inside the package by making the “db” global package level variable.

1
var db *sql.DB
  • A fast way to make database connections seamlessly is to use the init() function. You just need to call it one time.
1
2
3
4
5
6
7
8
9
10
11
12
13
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")
}

  • Use sql.open to access a connection.

  • Next, check for any raised errors with db.

  • The func main() verifies if the database connection worked. You can learn more about that script later in this tutorial.

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

  • Save your project.

  • Open a new terminal window.

  • Locate your project directory.

  • Run the Go lang main.go command.

1
go run main.go
  • You should see a similar result like this one:
1
Connected to the database

Retrieve CockroachDB record Golang

  • The code below starts with restoIndex. You’ll be writing a long script that requires a few steps to search CockroachDB Golang and fetch data from CockroachDB Golang, but in the end, it will be worth it. Take your time to read the script and then continue to the explanation that follows for a clearer understanding.
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
func restoIndex(w http.ResponseWriter, r *http.Request) {

if r.Method != "GET" {
http.Error(w, http.StatusText(405), http.StatusMethodNotAllowed)
return
} // (1)

rows, err := db.Query("SELECT * FROM tblrestaurants")

if err != nil {
http.Error(w, http.StatusText(500), 500)
return
} // (2)
defer rows.Close()

restos := make([]Restaurant, 0)

// loop to the rows and display the records
for rows.Next() {
resto := Restaurant{}
err := rows.Scan(&resto.Id, &resto.Name, &resto.Phone, &resto.Email, &resto.Stars, &resto.Category)
if err != nil {
http.Error(w, http.StatusText(500), 500)
return
}
restaurants = append(restaurants, resto)
} // (3)

if err = rows.Err(); err != nil {
http.Error(w, http.StatusText(500), 500)
return
}

for _, resto := range restaurants {
fmt.Fprintf(w, "%d %s %s %s %d %s\n", resto.Id, resto.Name, resto.Phone, resto.Email, resto.Stars, resto.Category)
} // (4)
}

Here’s an explanation of the above code:

  • Verified that GET is not the r.Method.

  • A “Method Not Allowed” error or 405 error is returned.

  • Took db.Query("SELECT * FROM tblrestaurants") and assigned rows.

  • Checked for errors

  • An “Internal Server Error” is returned.

  • Deallocated resources by deferring rows.Close().

  • Made a slice and assigned the restos := make([]Restaurant, 0) variable.

  • Completed iterations with rows.Next().

  • Copied the current row’s column with rows.Scan().

>NOTE: See the example table below for referencing the order of the database table.

1
2
3
4
5
6
7
8
id | name | phone | email | stars | category
+----+-------------------------+----------------+---------------------------+-------+----------+
1 | Pure's | 847-585-0174 | purebeauty2@example.net | 4 | Coffee
2 | yumster delicacy | 225-456-0102 | yumsterD@example.com | 1 | Italian
3 | raizel cafeteria | 857-555-0182 | raizelbalooga@example.com | 3 | Dessert
4 | Hungry Pirate Resto Bar | 908-555-045 | PirateBar@example.com | 5 | Pasta
5 | Abi Cafe | 1234-2123-1231 | Abi@example.com | 5 | Pizza
6 | Rommel | 1222-1214-5678 | rommelBurger@example.com | 4 | Burgers

Details of the func main() function

  • The point of entry of the script is func main(). Let’s dive into the details of this important function.
1
2
3
4
func main() {
http.HandleFunc("/restaurants", restoIndex) // (1)
http.ListenAndServe(":9090", nil) // (2)
}
  • The first parameter is the string and the second parameter is the function when you use http.HandleFunc() method in the above script.

>NOTE: You can just pass the function’s name when using the method http.HandleFunc() to pass a second parameter to it. You’ll want to pass the function as a callback function by eliminating the parentheses.

(1) It states that “/restaurants” prefixed URLS is what it will only handle. The name of the function in the HTTP request is restoIndex.

(2) The port “9090” is the assigned port.

  • It’s time to go to a window terminal and retest the code. Locate your project directory folder. Use the main.go command.
1
go run main.go

  • Open a browser window and enter this URL in the address bar: localhost:9090/restaurants

  • The results should look similar to this:

1
2
3
4
5
6
1 Pure's 847-585-0174 purebeauty2@example.net 4 Coffee
2 yumster delicacy 225-456-0102 yumsterD@example.com 1 Italian
3 raizel cafeteria 857-555-0182 raizelbalooga@example.com 3 Dessert
4 Hungry Pirate Resto Bar 908-555-045 PirateBar@example.com 5 Pasta
5 Abi Cafe 1234-2123-1231 Abi@example.com 5 Pizza
6 Rommel 1222-1214-5678 rommelBurger@example.com 4 Burgers

The result retrieve in CockroachDB using Golang

Conclusion

Congratulations! You’ve successfully finished this tutorial on how to retrieve CockroachDB record using Golang via web app. Today, you created a test database and table with retrievable records. You also connected the Go Postgres driver that enables communication between applications. Now you’re ready to fetch data from CockroachDB Golang correctly and confidently. Use the process outlined in this tutorial in your projects often.

Just The Code

Here’s the complete sample code for your reference on how to retrieve CockroachDB record Golang. Find cockroachDB record Golang.

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
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
package main

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

_ "github.com/lib/pq"
)

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

var db *sql.DB

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

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

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

if r.Method != "GET" {
http.Error(w, http.StatusText(405), http.StatusMethodNotAllowed)
return
}

rows, err := db.Query("SELECT * FROM tblrestaurants")

if err != nil {
http.Error(w, http.StatusText(500), 500)
return
}
defer rows.Close()

restaurants := make([]Restaurant, 0)

for rows.Next() {
resto := Restaurant{}
err := rows.Scan(&resto.Id, &resto.Name, &resto.Phone, &resto.Email, &resto.Stars, &resto.Category)
if err != nil {
http.Error(w, http.StatusText(500), 500)
return
}
restaurants = append(restaurants, resto)
}

if err = rows.Err(); err != nil {
http.Error(w, http.StatusText(500), 500)
return
}

for _, resto := range restaurants {
fmt.Fprintf(w, "%d %s %s %s %d %s\n", resto.Id, resto.Name, resto.Phone, resto.Email, resto.Stars, resto.Category)
}
}
Rate 1 Star2 Stars3 Stars4 Stars5 Stars

Pilot the ObjectRocket Platform Free!

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

Get Started
Related Topics:
Additional Information:

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.