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:
1 | go version |
The results should resemble the following:
1 | 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:
1 | go env |
The output should resemble the following:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | 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
1 | CREATE DATABASE patientinfo |
Use the following command to connect to the database:
\c patientinfo
Creating the database table
tbl_patientinfo
1 2 3 4 5 6 7 | 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:
1 2 3 4 | patientinfo=# SELECT * FROM tbl_patientinfo; patient_id | name | lastname | gender | age ------------+------+----------+--------+----- (0 ROWS) |
Now insert the following details in the table tbl_patientinfo
:
1 2 3 4 | 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:
1 2 3 4 5 6 7 | 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:
1 | CREATE USER rommel WITH Password 1234; |
- Grant the user a
superuser
privilege role as follows:
1 | GRANT ALL DATABASE patientinfo to rommel; |
Now execute the following psql command to verify the user rommel was granted a superuser
privilege role: \du
1 2 3 4 5 6 | 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