How to Retrieve CockroachDB Record using Golang Web App
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 ther.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 |
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) } } |
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started