In this project we will develop a Golang based web application to display and edit a database of people’s name, birthday, and occupation. Users may enter new entries via a web form. New entries will be saved to the PostgreSQL database. Simultaneously, the table on the webpage will be updated with the new entry.
Our example will demonstrate the following:
- Running a web server based application in Go
- Web routing in Go
- Running a PostgreSQL database server
- Creating and editing database in PostgreSQL
- Interactions among: web browser \(\longleftrightarrow\) Go web server \(\longleftrightarrow\) PostgreSQL database
The following will be used:
- Golang (programming language)
- SQL (query language)
- PostgreSQL (Database)
- VS Code (integrated development environment)
It is recommended to use VS Code as the IDE since it provides excellent support for GO, github integration, and project file structure management capability.
The expected end product of this project is shown below:
The repository consists of the following:
- Golang, PostgreSQL, HTML, HTML DOM, CSS code - complete functioning sample code
The project structure is as follows:
GoWeb-PostgreSQL # main folder ├── static # contains static files of the project, such as HTML files │ └── index.html # primary webpage at root "/" ├── main.go # main file of Go code, initializes web router and sets up database connection ├── personHandler.go # handles get and insert of new objects into database └── store.go # interface between PostgreSQL database and web servers
The code provided in the repository is well commented, and when read together with the project structure listed above, it becomes self explanatory. Nevertheless, key points are covered below in sequence.
Please download two additional packages
go get github.com/*packagename*.
muxis used for web routing and
pqserves as the Postgres driver for the sql-package.
func newRouterdefines the web routing for page handlers and for serving static files (i.e., index.html). Note that the static files are placed inside
./static/folder in our local directory, but it is served at the root (i.e., http://localhost:8080/) when browsed in a browser.
index.htmlfile defines the main webpage, consisting of a table, form, and a HTML DOM script. The HTML DOM script runs on each page load and updates the table by querying the database. Upon submitting the web form, the new entry is written to the database and also reflected in the table.
structto hold each individuals property values. Additionally, it defines the handlers which retrieves data from and writes data to the database, according to the http requests made. The communication between page handlers and
index.htmlis transmitted in JSON format.
store.goacts as the interface between page handlers’ requests and our PostgreSQL database.
store.goexecutes SQL queries to retrieve or to enter data into the database.
Download and install the PostgreSQL open source database from postgresql.org
pgAdmin4 is equipped with in-built SQL query tool which can be found at: Tools –> Query Tool
Refer to the instructions pdf for step-by-step instruction on adding, editing entries, and querying a database in PostgreSQL. Although the instructions are written for pgAdmin3, the steps remain the same in pgAdmin4 (with some user interface improvements).
- Steps to create and populate a databse in PostgreSQL using the pgAdmin4 graphical user interface is surmised below.
- (a) Create a database and a table Create a database = Databases \(\rightarrow\)
Create \(\rightarrow\) Database \(\rightarrow\)
peopleDatabase. Create a table =
peopleDatabase\(\rightarrow\) Schemas \(\rightarrow\) Public \(\rightarrow\) Tables \(\rightarrow\) Create \(\rightarrow\) Table \(\rightarrow\)
peopleinfo. Use all small letters when naming a table inside a database.
- (b) Specify column headers
peopleinfo\(\rightarrow\) Properties \(\rightarrow\) Columns. Add the relevant columns with their corresponding settings as shown in the figure below.
- (c) SQL insert To enter new values programatically into the table, execute the following code in the SQL query tool:
INSERT INTO peopleinfo(nama,birthday,occupation) VALUES ('Elephant', '27 Nov 1994', 'Accountant'), ('Winnie the Pooh Bear', '5 Oct 1994', 'Farmer');
Note that table values can be edited using the GUI by simply clicking on the rows of the table.
- (d) Result
peopleinfo\(\rightarrow\) View/Edit Data \(\rightarrow\) All Rows. Final expected table in our database is displayed.
(a) Create a database and a table (b) Specify column headers (c) SQL insert (d) Result
- (a) Create a database and a table Create a database = Databases \(\rightarrow\) Create \(\rightarrow\) Database \(\rightarrow\)
- To have our Go code connect to the PostgreSQL database, setup the connection string as follows and execute the
connString := `user=postgres // username to access the database password=1234 // password to access the database host=localhost // default host of PostgreSQL port=5432 // default port of PostgreSQL dbname=peopleDatabase // name of PostgreSQL database sslmode=disable`
A good tutorial for connecting Golang to PostgreSQL database is available at calhoun.io
- To run the application, navigate to the project directory
and enter the command:
$ go run main.go personHandler.go store.go
Alternatively, you may run the .exe file created through
go installin the above directory.
For those wishing to run the Go web application without a database, a slighlty modified code is available at GoWeb-inMemory. Here, instead of saving to a database, the information is saved to a variable (i.e.,
var personList Person) held in memory by the Go web server. Any data added to the slice variable, via the web form, will be erased when the server restarts.
- Finally go to
localhost:8080in your browser to see the web application in action.