Golang with database operations

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)