Go Lang and PostgreSQL Web App MVC pattern Part 1

Introduction

This is the first part of a multi-part tutorial series on how to create a Go Lang and PostgreSQL Web App with MVC pattern. The tutorial will provide a step by step instruction on how to create the web app as well as set up the project development environment, how to create the project directory and create a sample dataset along with its respective user.

Prerequisite

In order to create the Go Lang and PostgreSQL Web App with MVC pattern, PostgreSQL and Go Language must be properly installed and configured. Confirm Go Language is properly installed by executing the following command in a terminal window:

go version

The results should resemble the following:

go version go1.13.4 windows/amd64

Here the system displays the currently installed version of Go and what operating system it was installed with. In this case it was a Windows machine.

The Go Lang setting

  • This section will explain how to set up the Go Lang configuration for the Go project to work properly.

Execute the following command to view the current Golang configuration:

go env

The output should resemble the following:

set GO111MODULE=
set GOARCH=amd64
set GOBIN=
set GOCACHE=C:\Users\rmgal\AppData\Local\go-build
set GOENV=C:\Users\rmgal\AppData\Roaming\go\env
set GOEXE=.exe
set GOFLAGS=
set GOHOSTARCH=amd64
set GOHOSTOS=windows
set GONOPROXY=
set GONOSUMDB=
set GOOS=windows
set GOPATH=C:\Projects\Go
set GOPRIVATE=
set GOPROXY=https://proxy.golang.org,direct
set GOROOT=c:\go
set GOSUMDB=sum.golang.org
set GOTMPDIR=
set GOTOOLDIR=c:\go\pkg\tool\windows_amd64

Note that the GOPATH in the above configuration is what is most important, as this is where projects will be stored. While the path was already properly set in the above result. However, to set the path for the same location just add the GOPATH in the ‘Environment Variables’ as shown here:

The Go Lang Project Directory Structure

With the Go Lang project properly setup via ‘Environment Variables’, the project directory structure will now be explained. It should look something like the following:

  • patientInfo – The main directory.
  • config – This directory holds the database connection setting and other files for configuration.
  • model – This holds the files that process the application’s business logic
  • templates – This directory keeps the templates of the application’s front-end presentation.

Create PostgreSQL Sample Dataset

The following table structure will be used for the sample database named patientinfo. First, login to the PostgreSQL shell and execute the following operation to create a database, a table and a user:

  • Creating the database patientinfo
CREATE DATABASE patientinfo
  • Use the following command to connect to the database: \c patientinfo

  • Creating the database table tbl_patientinfo

CREATE TABLE tbl_patientinfo (
    patient_id VARCHAR(10) PRIMARY KEY NOT NULL,
    name    VARCHAR(255) NOT NULL,
    lastname VARCHAR(255) NOT NULL,
    gender  VARCHAR(6) NOT NULL,
    age INTEGER NOT NULL
);

Now perform a simple SELECT query against the database table to verify the table was successfully created, as shown here:

patientinfo=# SELECT * FROM tbl_patientinfo;
patient_id | name | lastname | gender | age
------------+------+----------+--------+-----
(0 ROWS)

Now insert the following details in the table tbl_patientinfo:

INSERT INTO tbl_patientinfo (patient_id, name, lastname, gender, age) VALUES
('12042019', 'Maria', 'Bennet', 'Female', 31),
('12052019', 'Julia', 'Jennison', 'Female', 25),
('12062019', 'Mark', 'Davidson', 'Male', 40);

Now query the table again to verify the above details were successfully added to the table:

patientinfo=# SELECT * FROM tbl_patientinfo;
patient_id | name | lastname | gender | age
------------+-------+----------+--------+-----
12042019 | Maria | Bennet | Female | 31
12052019 | Julia | Jennison | Female | 25
12062019 | Mark | Davidson | Male | 40
(3 ROWS)
  • As shown below, create a user role to use for the remained of this tutorial:
CREATE USER rommel WITH Password 1234;
  • Grant the user a superuser privilege role as follows:
GRANT ALL DATABASE patientinfo to rommel;

Now execute the following psql command to verify the user rommel was granted a superuser privilege role: \du

patientinfo=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 rommel    | Superuser

Conclusion

This was the first part of a multi-part tutorial series on how to create a Go Lang and PostgreSQL Web App with MVC pattern. This first part in the tutorial series specifically explained how to properly setup via ‘Environment Variables’, create a PostgreSQL sample dataset, how to insert details in the table and verify the details were successfully added to the table. Part one also covered how to create a user role and grant the user privileges. Remember to always perform a simple SELECT query against the database table to verify the table was successfully created.

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.