This part of the learning series focuses on accessing the relational database via Go programs. The assumption is that you have basic knowledge of relational databases and the basics of programming. If you haven't done so yet, please complete Part 1 to Part 4 of this series before diving into Part 5.
Prerequisite
Installation of PostgreSQL database on your machine. A step-by-step installation guide can be found here: https://www.w3schools.com/postgresql/postgresql_install.php.Completion of Previous series (Part 1 to Part 4).
Setup DataBase
Create Database
Use the below script to create a database in PostgreSQL. I'm using PGAdmin 4 to work with the PostgreSQL database.CREATE DATABASE "LearnGoWithSharad"
WITH
OWNER = postgres --Default postGresSQL admin user
ENCODING = 'UTF8'
LC_COLLATE = 'C'
LC_CTYPE = 'C'
LOCALE_PROVIDER = 'libc'
TABLESPACE = pg_default
CONNECTION LIMIT = -1
IS_TEMPLATE = False;
Create schema and tables
We must have a schema to create tables. By default, PostgreSQL uses the 'public' schema. Use the script below to create a schema:CREATE SCHEMA IF NOT EXISTS learngo
AUTHORIZATION postgres;
Now, add a few tables for our exercise:
-- Table: learngo.Person
-- DROP TABLE IF EXISTS learngo."Person";
CREATE TABLE IF NOT EXISTS learngo."Person"
(
"PersonId" bigint NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 100 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
"FirstName" character varying(20) COLLATE pg_catalog."default" NOT NULL,
"LastName" character varying(20) COLLATE pg_catalog."default" NOT NULL,
"CreatedDate" date NOT NULL,
"UserId" character varying(20) COLLATE pg_catalog."default" NOT NULL,
CONSTRAINT "Person_pkey" PRIMARY KEY ("PersonId")
)
TABLESPACE pg_default;
ALTER TABLE IF EXISTS learngo."Person"
OWNER to postgres;
-- Table: learngo.Address
-- DROP TABLE IF EXISTS learngo."Address";
CREATE TABLE IF NOT EXISTS learngo."Address"
(
"AddressId" bigint NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 100 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
"PersonId" bigint NOT NULL,
"HouseNumber" character varying(10) COLLATE pg_catalog."default",
"StreetName" character varying(50) COLLATE pg_catalog."default",
"City" character varying(50) COLLATE pg_catalog."default",
"State" character varying(2) COLLATE pg_catalog."default",
"ZipCode" character varying(5) COLLATE pg_catalog."default",
CONSTRAINT "Address_pkey" PRIMARY KEY ("AddressId")
)
TABLESPACE pg_default;
ALTER TABLE IF EXISTS learngo."Address"
OWNER to postgres;
Insert Data
To insert test data into tables, use the script below:-- Insert person test data into Person table
INSERT INTO learngo."Person"("FirstName", "LastName", "CreatedDate", "UserId")
VALUES ('Johnny', 'Walker', CURRENT_DATE, 'user_learnGo');
INSERT INTO learngo."Person"("FirstName", "LastName", "CreatedDate", "UserId")
VALUES ('Bill', 'Gates', CURRENT_DATE, 'user_learnGo');
-- Insert Address test data into the Address table
INSERT INTO learngo."Address"("PersonId", "HouseNumber", "StreetName", "City", "State", "ZipCode")
VALUES (100, 1234, 'abc Pkwy', 'Frederick', 'MD', '21000');
INSERT INTO learngo."Address"("PersonId", "HouseNumber", "StreetName", "City", "State", "ZipCode")
VALUES (101, 5678, 'xyz lane', 'Silver Spring', 'MD', '22000');
Use database driver in the project
Add postGreSQL database driver into your project. Need to import the driver.
Add pg package for the PostgreSQL driver: go get github.com/lib/pq
Add configuration
Add DB connection configuration into the config.yaml under main folder.
config.yaml
User: postgres
DatabaseName: LearnGoWithSharad
Password: postgres
ConnectionPort: 5432
DatabaseHost: localhost
Add Code
Add a new module db_operation in the project and a file named 'config.go' in it.
Write the below code in the config.go file to read config.yaml file.
Write the below code in the config.go file to read config.yaml file.
You need a package to read/write yaml file in go
Command to install package - $go get gopkp.in/yaml.v2
Config.go
package databaselayer
import (
"fmt"
"io/ioutil"
"log"
"gopkg.in/yaml.v2"
)
// struct to define the structure of the YMAL file and holds the parsed data
type configFilesProperties struct {
User string `yaml:"User"`
DatabaseName string `yaml:"DatabaseName"`
Password string `yaml:"Password"`
ConnectionPort string `yaml:"ConnectionPort"`
DatabaseHost string `yaml:"DatabaseHost"`
}
// structs to holds the database connection strings properties
type GetDatabaseConfig struct {
user string
databaseName string
password string
connectionPort string
databaseHost string
}
func NewGetDatabaseConfig() GetDatabaseConfig {
// Read configuration file
fmt.Println("Start Reading configuration file:")
configFile := "config.yaml"
configData, err := ioutil.ReadFile(configFile)
if err != nil {
log.Fatalf("Error reading configuration file: %v", err)
}
// Parse configuration data
var configFileProps configFilesProperties
err = yaml.Unmarshal(configData, &configFileProps)
if err != nil {
log.Fatalf("Error parsing configuration data: %v", err)
}
fmt.Println("End Reading configuration file:")
// Create the database connetion properies structs instance and return
return GetDatabaseConfig{
user: configFileProps.User,
databaseName: configFileProps.DatabaseName,
password: configFileProps.Password,
connectionPort: configFileProps.ConnectionPort,
databaseHost: configFileProps.DatabaseHost,
}
}
// Return the DB host
func (config GetDatabaseConfig) GetDatabaseHost() string {
return config.databaseHost
}
// Return the port no
func (config GetDatabaseConfig) GetConnectionPort() string {
return config.connectionPort
}
// Returns the UserId
func (config GetDatabaseConfig) GetUser() string {
return config.user
}
// Returns the Password
func (config GetDatabaseConfig) GetPassword() string {
return config.password
}
// Returns the database name
func (config GetDatabaseConfig) GetDatabaseName() string {
return config.databaseName
}
Perform DDL/DML operation
Add another file db_operation.go to the db_operation module to open the DB connection, execute queries, and read the query result.
db_operation.go
package databaselayer
import (
"database/sql"
"fmt"
_ "github.com/lib/pq"
)
type DatabaseOperation struct {
ConnectionString string
}
// Constructor the read and prepare the DB connection string
func NewDatabaseOperation() DatabaseOperation {
config := NewGetDatabaseConfig()
connectionString := fmt.Sprintf("user=%s password=%s host=%s port=%s dbname=%s sslmode=disable", config.GetUser(), config.GetPassword(), config.GetDatabaseHost(), config.GetConnectionPort(), config.GetDatabaseName())
return DatabaseOperation{
ConnectionString: connectionString,
}
}
// Function to open the DB connection and execute the query
func (dbo *DatabaseOperation) ExecuteSelect(query string) (*sql.Rows, error) {
var err error
// Open a connection to the database
db, err := sql.Open("postgres", dbo.ConnectionString)
if err != nil {
panic(err)
}
defer db.Close()
rows, err := db.Query(query)
if err != nil {
panic(err)
}
//defer rows.Close() // Close the rows when done
return rows, err
}
Uses of DB module
Modify address format and name_formater files from the string_formater module to prepare DB query, and call DB layer to execute the query.
address_fomater.go
package stringformater
import (
"fmt"
databaseLayer "learngo/db_operation" // import data db layer package
)
type Address struct {
HouseNumber string
StreetName string
City string
State string
ZipCode string
}
func (a *Address) Format() string {
return a.HouseNumber + " " + a.StreetName + ", " + a.City + ", " + a.State + " " + a.ZipCode
}
// This function calls DB operation and returns the collection of address
func GetAddress() ([]Address, error) {
databaseOperation := databaseLayer.NewDatabaseOperation()
// Query to select data from the database table
query := `SELECT "HouseNumber", "StreetName", "City", "State", "ZipCode"
FROM learngo."Address"`
rows, err := databaseOperation.ExecuteSelect(query)
if err != nil {
return nil, err
}
// Initialize a slice to store Address structs
var addresses []Address
// Iterate over the rows
for rows.Next() {
var address Address
// Scan the values into variables
err := rows.Scan(&address.HouseNumber, &address.StreetName, &address.City, &address.State, &address.ZipCode)
if err != nil {
return nil, fmt.Errorf("error scanning row: %v", err)
}
// Append the scanned Person to the slice
addresses = append(addresses, address)
}
// Check for errors during iteration
if err := rows.Err(); err != nil {
return nil, fmt.Errorf("error iterating over rows: %v", err)
}
// Return the list of Person structs
return addresses, nil
}
name_formater.go
package stringformater
import (
"fmt"
databaseLayer "learngo/db_operation"
"time"
)
type PersonName struct {
FirstName string
LastName string
}
type Person struct {
PersonID int
FirstName string
LastName string
CreatedDate time.Time
UserID string
}
// Function to format the Name
func (rawName *PersonName) Format() string {
return rawName.FirstName + " " + rawName.LastName
}
// This function calls the DB module to execute the query and return the collection of person object
func GetPersonNames() ([]Person, error) {
fmt.Println("Inside Get Person function")
databaseOperation := databaseLayer.NewDatabaseOperation()
// Query to select data from the database table
query := `SELECT "PersonId", "FirstName", "LastName", "CreatedDate", "UserId"
FROM learngo."Person"`
rows, err := databaseOperation.ExecuteSelect(query)
if err != nil {
return nil, err
}
// Initialize a slice to store Person structs
var persons []Person
// Iterate over the rows
for rows.Next() {
var person Person
// Scan the values into variables
err := rows.Scan(&person.PersonID, &person.FirstName, &person.LastName, &person.CreatedDate, &person.UserID)
if err != nil {
return nil, fmt.Errorf("error scanning row: %v", err)
}
// Append the scanned Person to the slice
persons = append(persons, person)
}
// Check for errors during iteration
if err := rows.Err(); err != nil {
return nil, fmt.Errorf("error iterating over rows: %v", err)
}
// Return the list of Person structs
return persons, nil
}
Finally, modify the main.go file to loop through the names and addresses returned from DB to format and print into the console.
main.go
package main
import (
"fmt"
dateformater "learngo/date_formater" // import data formater package
stringformater "learngo/string_formater" // import string formater package
)
func main() {
fmt.Println("Crete new date instance using a constructor!")
dateConstructor := dateformater.NewDate("2021", "07", "01")
fmt.Println(dateConstructor.Format())
//Create a channel to communicate and receive data between goroutines
ch := make(chan string)
var channelStatus bool
fmt.Println("Use name format package to format name!")
//get the person's names
persons, err := stringformater.GetPersonNames()
if err != nil {
fmt.Println("Exception occured to get persons")
panic(err)
}
// Check if the address has values
if len(persons) > 0 {
// Loop through the collection and read properties
for _, person := range persons {
personName := &stringformater.PersonName{FirstName: person.FirstName, LastName: person.LastName}
go formatString(personName, ch)
formatedName, status := <-ch // receive formatted data from channel with channel status
channelStatus = status
fmt.Println(formatedName)
}
} else {
fmt.Println("No objects in the persons collection")
}
fmt.Println("Use name format package to format billing address!")
//get addresses
addresses, err := stringformater.GetAddress()
if err != nil {
fmt.Println("Exception occured to get address")
panic(err)
}
// Check if the address has values
if len(addresses) > 0 {
// Loop through the collection and read properties
for _, addressInfo := range addresses {
address := &stringformater.Address{HouseNumber: addressInfo.HouseNumber, StreetName: addressInfo.StreetName, City: addressInfo.City, State: addressInfo.State, ZipCode: addressInfo.ZipCode}
go formatString(address, ch)
formatedAddress, status := <-ch // receive formatted data from channel with channel status
channelStatus = status
fmt.Println(formatedAddress)
}
} else {
fmt.Println("No objects in the address collection")
}
//Check the status of the channel
if channelStatus {
fmt.Println("Channel Ch is not closed!")
} else {
fmt.Println("Channel Ch is closed!")
}
}
// function to format the strings. Takes the interface type as input.
// Example to implement polymorphism using interface
func formatString(formater stringformater.Formater, ch chan string) {
ch <- formater.Format()
}
Example Code:
soon, will provide the git repo link.
Great insights on pairing Go with PostGreSQL. Thanks for sharing!
ReplyDelete