[Go] Accessing PostgreSQL with Bun 1
Tags: go, postgressql, bunjs
Published: 2023-06-14 13:15:15

## Intro
This time, I will try accessing PostgreSQL with Bun.

* [Bun: SQL client for Golang](https://bun.uptrace.dev/)
* [uptrace/bun - GitHub](https://github.com/uptrace/bun)

## CREATE & Transaction
Because I couldn't understand how to use Bun's migrate functions, I create tables by SQL.

```sql
CREATE TABLE app_user_role
(id serial PRIMARY KEY,
name varchar(64) not null);

INSERT INTO app_user_role (id, name)
VALUES (1, 'system');

INSERT INTO app_user_role (id, name)
VALUES (2, 'user');

CREATE TABLE app_user
(id serial PRIMARY KEY,
app_user_role_id bigint not null REFERENCES app_user_role(id),
name varchar(256) not null,
password text not null,
last_update_date timestamp with time zone not null default CURRENT_TIMESTAMP
);
```

* [Migrations [PostgreSQL MySQL] - Bun](https://bun.uptrace.dev/guide/migrations.html)

### Models
#### appUser.go
```go
package models

import (
	"time"

	"github.com/uptrace/bun"
)

type AppUsers struct {
	bun.BaseModel  `bun:"table:app_user,alias:usr"`
	ID             int64         `bun:"id,pk,autoincrement"`
	RoleID         int64         `bun:"app_user_role_id,notnull,type:bigint"`
	Name           string        `bun:"name,notnull,type:varchar(64)"`
	Password       string        `bun:"password,notnull,type:text"`
	LastUpdateDate time.Time     `bun:"last_update_date,notnull,type:timestamp with time zone,default:CURRENT_TIMESTAMP"`
	Role           *AppUserRoles `bun:"rel:has-one,join:app_user_role_id=id"`
}

func NewAppUsers(roleId int64, name string, hashedPassword string) *AppUsers {
	return &AppUsers{
		RoleID:   roleId,
		Name:     name,
		Password: hashedPassword,
	}
}
```

#### appUserRole.go
```go
package models

import (
	"github.com/uptrace/bun"
)

type AppUserRoles struct {
	bun.BaseModel `bun:"table:app_user_role,alias:url"`
	ID            int64  `bun:"id,pk,autoincrement"`
	Name          string `bun:"name,notnull,type:varchar(64)"`
}
```

### AppUser class for update DB
```go
package dto

import (
	"fmt"
)

type AppUserForUpdate struct {
	ID       int64  `json:"id"`
	RoleID   int64  `json:"role_id"`
	Name     string `json:"name"`
	Password string `json:"password"`
}
```

### DB Accessor
#### bookshelfContext.go
```go
package db

import (
	"database/sql"

	"github.com/uptrace/bun"
	"github.com/uptrace/bun/dialect/pgdialect"
	"github.com/uptrace/bun/driver/pgdriver"
)

type BookshelfContext struct {
	db    *bun.DB
	Users *Users
}

func NewBookshelfContext() *BookshelfContext {
	result := BookshelfContext{}
	dsn := "postgresql://{USERNAME}:{PASSWORD}@localhost:5432/{DATABASE_NAME}?sslmode=disable"
	result.db = bun.NewDB(
		sql.OpenDB(pgdriver.NewConnector(pgdriver.WithDSN(dsn))),
		pgdialect.New(),
	)
	result.Users = NewUsers(result.db)
	return &result
}
```

#### users.go
```go
package db

import (
	"context"
	"database/sql"
	"encoding/base64"
	"fmt"
	"math/big"

	"crypto/rand"
	"crypto/sha512"

	"github.com/uptrace/bun"
	"golang.org/x/crypto/pbkdf2"

	dto "github.com/web-db-sample/dto"
	models "github.com/web-db-sample/models"
)

type Users struct {
	db *bun.DB
}

func NewUsers(database *bun.DB) *Users {
	return &Users{
		db: database,
	}
}
func (u Users) CraeteUser(ctx *context.Context, user dto.AppUserForUpdate) error {
	// Use tx instead of db to enable transactions
	tx, err := u.db.BeginTx(*ctx, &sql.TxOptions{})
	if err != nil {
		return err
	}
	// Make sure the Role ID is registered
	exists, err := tx.NewSelect().Model(new(models.AppUserRoles)).
		Where("id=?", user.RoleID).Exists(*ctx)
	if err != nil {
		return err
	}
	if !exists {
		return fmt.Errorf("INVALID ROLE ID:%d", user.RoleID)
	}
	// Make sure the user name is unique
	exists, err = tx.NewSelect().Model(new(models.AppUsers)).
		Where("name=?", user.Name).Exists(*ctx)
	if err != nil {
		return err
	}
	if exists {
		return fmt.Errorf("USER NAME IS ALREADY EXITS:%s", user.Name)
	}
        salt, err := generateRandomSalt(128 / 8)
	if err != nil {
		return err
	}
	// Get base 64 encoded Hash value to save the password
	key := pbkdf2.Key([]byte(user.Password), salt, 100_000, 256/8, sha512.New)
	hashedPassword := base64.StdEncoding.EncodeToString(key)
	// Insert new user
	newUser := models.NewAppUsers(user.RoleID, user.Name, hashedPassword)
	_, err = tx.NewInsert().Model(newUser).Exec(*ctx)
	if err != nil {
		tx.Rollback()
		return err
	}
	tx.Commit()
	return nil
}

// Generate a salt value
func generateRandomSalt(length int) ([]byte, error) {
	results := make([]byte, length)
	for i := 0; i < length; i++ {
		salt, err := rand.Int(rand.Reader, big.NewInt(255))
		if err != nil {
			return nil, err
		}
		results[i] = byte(salt.Int64())
	}
	return results, nil
}
```

### main
#### main.go
```go
package main

import (
	"context"
	"log"

	db "github.com/web-db-sample/db"
	dto "github.com/web-db-sample/dto"
)

func main() {
	dbCtx := db.NewBookshelfContext()
	ctx := context.Background()

	sampleUser := dto.AppUserForUpdate{
		RoleID:   1,
		Name:     "Masa",
		Password: "Sample",
	}
	err = dbCtx.Users.CraeteUser(&ctx, sampleUser)
	if err != nil {
		log.Panicln(err.Error())
	}
	log.Println("OK")
}
```


* [pbkdf2 - golang.org/x/crypto/pbkdf2 - Go Pakages](https://pkg.go.dev/golang.org/x/crypto/pbkdf2)

## READ
#### users.go
```go
...
func (u Users) GetUsers(ctx *context.Context) ([]models.AppUsers, error) {
	users := make([]models.AppUsers, 0)
	err := u.db.NewSelect().
		Model(&users).
		// Add relation
		Relation("Role").
		Scan(*ctx)
	if err != nil {
		return nil, err
	}
	return users, nil
}
```

* [Golang Select PostgreSQL MySQL - Bun](https://bun.uptrace.dev/guide/query-select.html)
* [ORM: Table relationships - Bun](https://bun.uptrace.dev/guide/relations.html)

### panic: sql: no rows in result set
Executing code that expects to get 1 row, I will get an error if the result is 0 rows.

#### users.go
```go
...
func (u Users) GetUser(ctx *context.Context, userId int64) (*models.AppUsers, error) {
	user := new(models.AppUsers)
	err := u.db.NewSelect().
		Model(user).
		Relation("Role").
		Where("usr.id=?", userId).
		Limit(1).
		Scan(*ctx)
	if err != nil {
		return nil, err
	}
	return user, nil
}
```

#### Error
```
panic: sql: no rows in result set
```

Because this is expected behaviour, so I should separate the error of no rows and others.

#### users.go
```go
...
func (u Users) GetUser(ctx *context.Context, userId int64) (*models.AppUsers, error) {
	user := new(models.AppUsers)
	err := u.db.NewSelect().
		Model(user).
		Relation("Role").
		Where("usr.id=?", userId).
		Limit(1).
		Scan(*ctx)
	if err != nil {
		// Ignore no rows error and return nil
		if err == sql.ErrNoRows {
			return nil, nil
		}
		return nil, err
	}
	return user, nil
}
```

* [Why am I getting ErrNoRows("sql: no rows in result set") after inserting into postgres, even when insert was actually successful? - StackOverflow](https://stackoverflow.com/questions/53507760/why-am-i-getting-errnorowssql-no-rows-in-result-set-after-inserting-into-po)

### Mapping to another class
To return user data, I want to remove their passwords and put them into another class.
What should I do?

I can use raw SQL and map a class.

#### appUserForView.go
```go
package dto

import (
	"time"
)

type AppUserForView struct {
	ID             int64     `bun:"id" json:"id"`
	RoleID         int64     `bun:"roleId" json:"roleId"`
	RoleName       string    `bun:"roleName" json:"roleName"`
	Name           string    `bun:"name" json:"name"`
	LastUpdateDate time.Time `bun:"lastUpdateDate" json:"lastUpdateDate"`
}
```

#### users.go
```go
...
func (u Users) GetAllUsersForView(ctx *context.Context) ([]dto.AppUserForView, error) {
	results := make([]dto.AppUserForView, 0)
	err := u.db.NewRaw(
		`SELECT usr.id AS "id", url.id AS "roleId", usr.name AS "name", url.name AS "roleName",
		usr.last_update_date AS "lastUpdateDate" FROM app_user usr
		JOIN app_user_role url ON usr.app_user_role_id = url.id
		`).Scan(*ctx, &results)
	if err != nil {
		return nil, err
	}
	return results, nil
}
```

```json
[{"id":2,"RoleID":1,"RoleName":"system","Name":"Masa","LastUpdateDate":"2023-06-11T01:39:42.712888+09:00"}]
```

* [Is it possible to have a struct with multiple JSON tags? - StackOverflow](https://stackoverflow.com/questions/37118263/is-it-possible-to-have-a-struct-with-multiple-json-tags)
* [Golang multiple json tag names for one field - StackOverflow](https://stackoverflow.com/questions/38809137/golang-multiple-json-tag-names-for-one-field)