How to Build a Simple Golang and CockroachDB Web App using the MVC Pattern Part 1

Introduction

If you’re working with CockroachDB, you may be interested in building a web application that can access your database. Fortunately, this task is easy to accomplish using the Go language and the MVC pattern. This article will be the first installment of a multi-part series that explains how to create a web application using Go and CockroachDB via the MVC pattern. Over the course of this series, we’ll discuss the entire process from start to finish: how to set up the project environment, how to create the database and its users, the design of the controllers and customized routers.

Prerequisites

Before we get started with our web app, let’s go over a few prerequisites that are necessary for this project:

  • You’ll need to ensure that CockroachDB is properly installed and configured.

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

  • A basic understanding of the Go language’s syntax and packages is necessary.

To check if Go is installed on your system, run the following command:

go version

The output should look something like this:

go version go1.12.5 linux/amd64

In the output, Go displays both the current installed version number and the operating system on which it was installed.

The Project Setting

Now that we’ve made sure all our prerequisites are in place, we can start setting up the GOPATH of Golang. This step is important since the GOPATH will contain the source of the Go projects and its binaries. In Linux, we can set up the GOPATH using the command export gopath=/path-to-the-directory and ensure that the directory contains the three subdirectories: pck, bin and src.

To check your current GOPATH setting, use the following command in your terminal:

go env

You should see something like this as a result:

GOARCH="amd64"
GOBIN="/home/username/go_projects/bin"
GOCACHE="/home/username/.cache/go-build"
GOEXE=""
GOFLAGS=""
GOHOSTARCH="amd64"
GOHOSTOS="linux"
GOOS="linux"
GOPATH="/home/username/go_projects"
GOPROXY=""
GORACE=""
GOROOT="/usr/local/go"
GOTMPDIR=""
GOTOOLDIR="/usr/local/go/pkg/tool/linux_amd64"

In the output shown above, we can see that GOPATH was set to the following path:

/home/username/go_projects

Connecting to the CockroachDB Local Cluster

Before we can interact with CockroachDB via our web app, we need to start up a CockroachDB local cluster:

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

Let’s take a closer look at what’s happening in this command:

  1. The --insecure flag makes the CockroachDB communication unencrypted.
  2. The --listen-addr=localhost:26257 tells the node to listen only on localhost and explicitly specifies the port number which, in this case, is “26257”.
  3. The --http-addr=localhost:8080 tells the node to listen at port “8081” for HTTP requests.

After running the command, you should 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 CockroachDB instance by opening another terminal window and using the following command:

cockroach sql --insecure

The output will look something like this:

# 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 Web App Sample Database

Now that we’re able to connect to the CockroachDB instance, we can create a sample database that we’ll be using in our code examples. Let’s use the following statements to create the “yeshua” user and “restaurants” database:

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

To use this newly-created database, you would use the SQL statement SELECT DATABASE = restaurants;

For the example shown in this article, the user “yeshua” will be given read and write permission to the database:

GRANT ALL ON DATABASE restaurants TO yeshua;

Once the database is created, we’ll proceed to create a simple table. To do this, we’ll use the following SQL statement:

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

Then we can put some records in our new “tblrestaurants” table:

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');

Once you’ve set up your sample database and table, use \q to exit the SQL shell.

The Project Directory Structure

Next, we’ll create the project directory. The structure should look something like this:

DirectoryDescription
webGoMain directory
configDatabase connection setting and configuration files
modelDatabase processing module
templatesFront end presentation

Conclusion

There are many ways you can interact with CockroachDB through a web app written with the Go programming language: You can create a CockroachDB record using Golong, you can update a CockroachDB record using Golang, and you can even delete from CockroachDB using Golang. In this first part of our multi-part series, we talked about how to set up our database and our GOPATH. We also discussed what to expect in the project structure of our project directory. If you’ve completed the steps in this article, you can move on to the next part of this series where we explain how to grow our project directory with the necessary files to complete our web application.

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.