Database Handling with Golang Gorm (CRUD Handling)
Tags: go, tutorial, programming, beginners
Published: 2021-12-27 16:47:08

## Introduction

Hi, in this session we will learn on how to use Gorm in Golang, to handle database operations such as create, read, update and delete (or usually called by many of us CRUD operations)

## What is Gorm?

[Gorm](https://gorm.io/docs/) is just as many object relational mapper that every language / framework has, that handles database operations with defined models of our own tables inside our codes.  This post will only cover the basic connection, model definition, basic CRUD handling, and we will use postgresql as our database, so let's get start with it!

##Initialize Package 

First we need to initiate our project with command

```
go mod init {your package name}
```

after initializing the project, we can run the command to get the Gorm package and download it to our projects

```
go get -u gorm.io/gorm
go mod vendor
```

Okay, so we will separate a folders for configuration, model definition and a main package for running this tutorial, here's my folder structured looks like

```
- config
 |_ config.go
- models
 |_ payment.go
- test.go
```

##Connecting to DB (Using PostgreSQL)

the code inside `config.go` is utilize for handling database connection

```
package config

import (
	"fmt"

	"gorm.io/driver/postgres"
	"gorm.io/gorm"
)

const (
	Host     = "localhost"
	User     = "postgres"
	Password = "*******"
	Name     = "payment"
	Port     = "5432"
)

func Setup() (*gorm.DB, error) {
	connectionString := fmt.Sprintf("host=%s port=%s user=%s dbname=%s password=%s sslmode=disable",
		Host,
		Port,
		User,
		Name,
		Password,
	)

	db, err := gorm.Open(postgres.Open(connectionString), &gorm.Config{})
	if err != nil {
		return nil, err
	}
	return db, nil
}

```

notice that in this file, we defined the connection with enum to handle host, database name, and user authentication needed to connect into our database, also since we use postgre database we import only for the postgres driver

let's try our connection first in `test.go` file

```
package main

import (
	"errors"
	"fmt"
	"log"

	"github.com/yanoandri/simple-goorm/config"
	"gorm.io/gorm"
)

func main() {
	//connect to postgresql
	_, err := config.Setup()
	if err != nil {
		log.Panic(err)
		return
	}
	fmt.Println("Connected")
}
```
if the credentials provided in `config.go` are correctly provided, the program print "Connected" to mark our connection is successful.  For this part, we ignore the return value first, since we are going to talk about in the next part

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

## Model Definition

Let's try creating our first model, we will define the `payment.go` as our first table

```
package models

import (
	"time"

	"github.com/google/uuid"
)

type Payment struct {
	ID          uuid.UUID `gorm:"type:uuid;default:uuid_generate_v4();primaryKey;"`
	PaymentCode string
	Name        string
	Status      string
	Created     time.Time `gorm:"autoCreateTime"`
	Updated     time.Time `gorm:"autoUpdateTime"`
}
```

Inside the struct that we named as `payments`, we defined the datatypes of every column inside it, you can see this [docs](https://gorm.io/docs/models.html) on how to use it properly for your table definition

We will try to migrate this table definition inside model into our database, let's use the successful return value of `gorm.DB` in the earlier section and modify our `test.go`

```
package main

import (
	"errors"
	"fmt"
	"log"

	"github.com/yanoandri/simple-goorm/config"
    "github.com/yanoandri/simple-goorm/models"
	"gorm.io/gorm"
)

func main() {
	//connect to postgresql
	db, err := config.Setup()
	if err != nil {
		log.Panic(err)
		return
	}
	fmt.Println("Connected")
    //migrate models inside project
	db.AutoMigrate(models.Payment{})
	fmt.Println("Migrated")
}
```

Notice the `db.AutoMigrate` function is use to call the migration if there are any change inside `models` folder, if there are nothing to change, then it will not make any changes into it

## CRUD Handling

### Create

Let's try to cover create part this time, we will make the `CreatePayment` function outside the main function

```
func CreatePayment(db *gorm.DB, payment models.Payment) (int64, error) {
	result := db.Create(&payment)
	if result.RowsAffected == 0 {
		return 0, errors.New("payment not created")
	}
	return result.RowsAffected, nil
}
```

then run it inside our `main`

```
func main() {
	//connect to postgresql
	db, err := config.Setup()
	if err != nil {
		log.Panic(err)
		return
	}
	fmt.Println("Connected")

	// create a payment
	payment := models.Payment{
		PaymentCode: "XXX-1",
		Name:        "Payment for item #1",
		Status:      "PENDING",
	}

	result, err := CreatePayment(db, payment)
	if err != nil {
		log.Panic(err)
		return
	}
	fmt.Println("Payment created", result)
}
```

results in our database

![Create results](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4qf170655ylk8lqrvtv3.png)
 


### Select with id

In earlier create function, we only return the number of rows that we created.  This time we return the model as a struct

```
func SelectPaymentWIthId(db *gorm.DB, id string) (models.Payment, error) {
	var payment models.Payment
	result := db.First(&payment, "id = ?", id)
	if result.RowsAffected == 0 {
		return models.Payment{}, errors.New("payment data not found")
	}
	return payment, nil
}
```

in `test.go`

```
func main() {
	//connect to postgresql
	db, err := config.Setup()
	if err != nil {
		log.Panic(err)
		return
	}
	fmt.Println("Connected")

	// select a payment
	var id string
	fmt.Println("Input payment id : ")
	fmt.Scanln(&id)

	payment, _ := SelectPaymentWIthId(db, id)
	fmt.Println("Your payment is", payment)
}
```
results

![Selected](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/f5j19ju2gsbaicgeufm6.png) 
 
### Update

This time we will update the mark the status as `PAID` for the specific record

```
func UpdatePayment(db *gorm.DB, id string, payment models.Payment) (models.Payment, error) {
	var updatePayment models.Payment
	result := db.Model(&updatePayment).Where("id = ?", id).Updates(payment)
	if result.RowsAffected == 0 {
		return models.Payment{}, errors.New("payment data not update")
	}
	return updatePayment, nil
}
```

in `test.go`

```
func main() {
	//connect to postgresql
	db, err := config.Setup()
	if err != nil {
		log.Panic(err)
		return
	}
	fmt.Println("Connected")

	// select a payment
	var id string
	fmt.Println("Input payment id : ")
	fmt.Scanln(&id)

	payment, _ := SelectPaymentWIthId(db, id)
	fmt.Println("Your payment is", payment)

	// update a payment with previous id
	updatedPayment, _ := UpdatePayment(db, id, models.Payment{
		Status: "PAID",
	})
	fmt.Println("Your payment status now is ", updatedPayment)
}
```

results

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

notice the only information that returned inside struct is only the updated attributes
 
### Delete

The last part is how to delete from tables

```
func DeletePayment(db *gorm.DB, id string) (int64, error) {
	var deletedPayment models.Payment
	result := db.Where("id = ?", id).Delete(&deletedPayment)
	if result.RowsAffected == 0 {
		return 0, errors.New("payment data not update")
	}
	return result.RowsAffected, nil
}
```

in `test.go`

```
func main() {
	//connect to postgresql
	db, err := config.Setup()
	if err != nil {
		log.Panic(err)
		return
	}
	fmt.Println("Connected")

	// select a payment
	var id string
	fmt.Println("Input payment id : ")
	fmt.Scanln(&id)

	payment, _ := SelectPaymentWIthId(db, id)
	fmt.Println("Your payment is", payment)

	// delete a payment with previous id
	DeletePayment(db, id)
	fmt.Println("Your payment now is deleted")
}
```

in our script
![Results in script](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/yozj2guu9m3x42qmv36e.png)
 

let's see the results directly database
![Results Deleted DB](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/6na7zapgouw4iu562i1u.png)
 

## Conclusion

That's it on how to handle CRUD and connection to database with Golang Gorm.  This is really based on my experience and there's still so much more to explore about this package. Happy exploring!

Source :
* [Gorm](https://gorm.io/)
* [Full source](https://github.com/yanoandri/simple-goorm)