How to Build a Simple Golang and CockroachDB Web App using the MVC Pattern Part 1
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.
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:
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:
To check your current GOPATH setting, use the following command in your terminal:
You should see something like this as a result:
In the output shown above, we can see that
GOPATH was set to the following path:
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:
--insecureflag makes the CockroachDB communication unencrypted.
--listen-addr=localhost:26257tells the node to listen only on localhost and explicitly specifies the port number which, in this case, is “26257”.
--http-addr=localhost:8080tells 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)
client flags: cockroach client cmd="cmd" --host=localhost:26257 --insecure
temp dir: /home/username/cockroach-data/cockroach-temp962568206
external I/O path: /home/username/cockroach-data/extern
status: restarted pre-existing node
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.
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,
Then we can put some records in our new “tblrestaurants” table:
INSERT INTO tblrestaurants (id, name, phone, email, stars, category) VALUES
(4,'Hungry Pirate Resto Bar','908-555-045','PirateBar@example.com',5,'Pasta'),
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:
|config||Database connection setting and configuration files|
|model||Database processing module|
|templates||Front end presentation|
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