DEV Community 👩‍💻👨‍💻

Cover image for Golang with database operations
bseyhan
bseyhan

Posted on

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;

Status

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")
Enter fullscreen mode Exit fullscreen mode

Step 4. Create new GoLang Solution

There are different IDE's development code.

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

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
}
Enter fullscreen mode Exit fullscreen mode

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
    }
Enter fullscreen mode Exit fullscreen mode

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()
Enter fullscreen mode Exit fullscreen mode

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
    }
Enter fullscreen mode Exit fullscreen mode

Get one

db := openDatabaseConn(r)
    defer db.Close()

    data := db.QueryRowContext(ctx, "Select id,firstname,lastname from Users Where Id=$1", id)

Enter fullscreen mode Exit fullscreen mode

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
}

Enter fullscreen mode Exit fullscreen mode

Test

go run main.go

Image description

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)

Enter fullscreen mode Exit fullscreen mode

Create User

Image description

List Users

Image description

Get by id

Image description

Delete

Image description

Image description

Repository

Thank you.

Top comments (0)

🌚 Life is too short to browse without dark mode