Hi all,
In this post I'm going to show you how to use PostgreSQL with GoLang. Briefly I'll introduce history of GoLang. Actually you can find more than one different document about history of GoLang however I found it on a nicely explained site. History
I've experienced .Net Core stack however I've been learning GoLang for 3 months. I believed that GoLang is very powerful and smooth language. I think we'll go head with steps.
Step 1. Install Docker
Step 2. Run PostgreSQL container
docker run --name postgresql-container -p 5432:5432 -e POSTGRES_PASSWORD=Password! -d postgres;
Step 3. Run Database Script
-- Drop table
-- DROP TABLE public.users;
CREATE TABLE public.users (
firstname varchar NULL,
lastname varchar NULL,
id serial NOT NULL
);
Insert Into public.Users(firstname,lastname) values("FirstName-1","LastName-1")
Insert Into public.Users(firstname,lastname) values("FirstName-2","LastName-2")
Insert Into public.Users(firstname,lastname) values("FirstName-3","LastName-3")
Step 4. Create new GoLang Solution
There are different IDE's development code.
- VSCode
- Vim-go
- Atom
- Sublime
- LiteIDE For more information GoLang Official
According to my habit, Visual Studio Code is quite suitable for me. Now Install the extension of GoLang. Go Extension
Packages:
- Web framework: go get -u github.com/labstack/echo/v4
- Read Configuration file : go get -u github.com/spf13/viper
- Logging : go get -u github.com/sirupsen/logrus
- PostgreSQL : go get -u github.com/lib/pq
Let's look at the project structure
- cmd>api : include with config file and initialization like Startup.cs
- cmd>utils: configuration struct
- pkg>api : include with middleware and routing also register with handlers
- pkg>entities: database entities
- pkg>handlers: related with request&response type also interact with repository layer
- pkg>models : api response datas
- pkg>repository: related with database operations
When I run a go run main.go this project starts with a cmd>api layer. This layer read a configuration parameters after that registered middleware and routings.
Let's connect to database. This function take postgresCustomerRepository parameter and return *sql.DB pointer. This method related with Open connection and check the connection status with Ping command.
func openDatabaseConn(r *postgresCustomerRepository) *sql.DB {
db, err := sql.Open("postgres", r.conn)
if err != nil {
log.Error("Connection failed")
}
pingError := db.Ping()
if pingError != nil {
log.Error("Ping != pong")
}
log.Info("Postgres connection success!!!")
return db
}
Add method
$1 and $2 hold the argument of struct. In the older numeric approach, arguments are referenced using the syntax $n: $1 refers to the first input argument, $2 to the second, and so on. This will work whether or not the particular argument was declared with a name. [PostgreSQL][https://www.postgresql.org/docs/9.5/xfunc-sql.html]
db := openDatabaseConn(r)
defer db.Close()
//add data
query := "Insert into Users(FirstName, LastName) values($1,$2)"
if _, err := db.ExecContext(ctx, query, customer.FirstName, customer.LastName); err != nil {
return customer, err
}
List method
//connect database
db := openDatabaseConn(r)
defer db.Close()
//read data from server
rows, _ := db.QueryContext(ctx, "Select id,firstname,lastname from Users")
defer rows.Close()
Delete method
db := openDatabaseConn(r)
defer db.Close()
query := "Delete From Users Where Id=$1"
affectedRow, err := db.ExecContext(ctx, query, id)
if err != nil {
return false, nil
}
Get one
db := openDatabaseConn(r)
defer db.Close()
data := db.QueryRowContext(ctx, "Select id,firstname,lastname from Users Where Id=$1", id)
customerRepository.go file
package repository
import (
"context"
"database/sql"
"fmt"
"time"
"github.com/bburaksseyhan/ctmapp/src/cmd/utils"
"github.com/bburaksseyhan/ctmapp/src/pkg/entities"
_ "github.com/lib/pq"
log "github.com/sirupsen/logrus"
)
type CustomerRepository interface {
List(cntxt context.Context, timeout int) ([]entities.CustomerEntity, error)
Add(customer entities.CustomerEntity, cntxt context.Context, timeout int) (entities.CustomerEntity, error)
Delete(id int, cntxt context.Context, timeout int) (bool, error)
Get(id int, cntxt context.Context, timeout int) (entities.CustomerEntity, error)
}
type postgresCustomerRepository struct {
dbSetting *utils.DbSettings
conn string
}
func NewPostgresCustomerRepository(dbSettings *utils.DbSettings) CustomerRepository {
//initial log formatter
log.SetFormatter(&log.JSONFormatter{})
repo := &postgresCustomerRepository{
dbSetting: dbSettings,
conn: fmt.Sprintf("host=%s port=%d user=%s password=%s dbname=%s sslmode=disable",
dbSettings.Host, dbSettings.Port, dbSettings.User, dbSettings.Password, dbSettings.DbName),
}
return repo
}
func (r *postgresCustomerRepository) List(cntxt context.Context, timeout int) ([]entities.CustomerEntity, error) {
//context
ctx, cancel := context.WithTimeout(cntxt, time.Duration(timeout)*time.Second)
defer cancel()
//connect database
db := openDatabaseConn(r)
defer db.Close()
//read data from server
rows, _ := db.QueryContext(ctx, "Select id,firstname,lastname from Users")
defer rows.Close()
//define slice for store customer information
var customerEntity []entities.CustomerEntity
//read data row by row
for rows.Next() {
var userId int
var firstName string
var lastName string
_ = rows.Scan(&userId, &firstName, &lastName)
customerEntity = append(customerEntity, entities.CustomerEntity{Id: userId, FirstName: firstName, LastName: lastName})
}
return customerEntity, nil
}
func (r *postgresCustomerRepository) Add(customer entities.CustomerEntity, cntxt context.Context, timeout int) (entities.CustomerEntity, error) {
ctx, cancel := context.WithTimeout(cntxt, time.Duration(timeout)*time.Second)
defer cancel()
db := openDatabaseConn(r)
defer db.Close()
//add data
query := "Insert into Users(FirstName, LastName) values($1,$2)"
if _, err := db.ExecContext(ctx, query, customer.FirstName, customer.LastName); err != nil {
return customer, err
}
return customer, nil
}
func (r *postgresCustomerRepository) Delete(id int, cntxt context.Context, timeout int) (bool, error) {
ctx, cancel := context.WithTimeout(cntxt, time.Duration(timeout)*time.Second)
defer cancel()
db := openDatabaseConn(r)
defer db.Close()
query := "Delete From Users Where Id=$1"
affectedRow, err := db.ExecContext(ctx, query, id)
if err != nil {
return false, nil
}
fmt.Println(affectedRow.LastInsertId())
fmt.Println(affectedRow.RowsAffected())
return true, nil
}
func (r *postgresCustomerRepository) Get(id int, cntxt context.Context, timeout int) (entities.CustomerEntity, error) {
ctx, cancel := context.WithTimeout(cntxt, time.Duration(timeout)*time.Second)
defer cancel()
db := openDatabaseConn(r)
defer db.Close()
data := db.QueryRowContext(ctx, "Select id,firstname,lastname from Users Where Id=$1", id)
var userId int
var firstName string
var lastName string
_ = data.Scan(&userId, &firstName, &lastName)
return entities.CustomerEntity{Id: userId, FirstName: firstName, LastName: lastName}, nil
}
func openDatabaseConn(r *postgresCustomerRepository) *sql.DB {
db, err := sql.Open("postgres", r.conn)
if err != nil {
log.Error("Connection failed")
}
pingError := db.Ping()
if pingError != nil {
log.Error("Ping != pong")
}
log.Info("Postgres connection success!!!")
return db
}
Test
go run main.go
Routings
e.GET("/health", customerHandler.Health)
e.GET("/api/v1/customer", customerHandler.List)
e.POST("/api/v1/customer", customerHandler.Add)
e.DELETE("/api/v1/customer/:id", customerHandler.Delete)
e.GET("api/v1/customer/:id", customerHandler.Get)
Create User
List Users
Get by id
Delete
Thank you.
Top comments (0)