Golang with database operations
Tags: go, postgres, tutorial, opensource
Published: 2021-10-31 18:27:41

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](https://www.geeksforgeeks.org/go-programming-language-introduction/)

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](https://docs.docker.com/get-docker/)

### Step 2. Run PostgreSQL container
```docker run --name postgresql-container -p 5432:5432 -e POSTGRES_PASSWORD=Password! -d postgres;```


![Status](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/we53z45iy3xpd2mm7h9y.png)

### 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](https://golang.org/doc/editors)

According to my habit, Visual Studio Code is quite suitable for me. Now Install the extension of GoLang. [Go Extension](https://code.visualstudio.com/docs/languages/go)

### 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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/umgsek4q2fydgw8acumx.png)
 
* 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`

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/d85laoxngumuodvzn9yy.png)

### 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

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/sf7jf5zmdthfcq794sc6.png)

#### List Users

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/9jaiyu9mnyxnxsshjy0k.png)

#### Get by id

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/yxp7bg9t7o6l8vj0qmmi.png)

#### Delete 
   
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/59faez5xkidg14o1v979.png)


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2bspub9m8kaaxb9nfq47.png)
 
 

[Repository](https://github.com/bburaksseyhan/cmtapp.git) 

Thank you.