TheLazy.Dev
Portfolio GitHub

Blog

go |postgressql |bunjs

[Go] Accessing PostgreSQL with Bun 1

masanori_msl

Masui Masanori

Posted on June 14, 2023

[Go] Accessing PostgreSQL with Bun 1

Intro

This time, I will try accessing PostgreSQL with Bun.

CREATE & Transaction

Because I couldn't understand how to use Bun's migrate functions, I create tables by 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
);
Enter fullscreen mode Exit fullscreen mode

Models

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

appUserRole.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)"`
}
Enter fullscreen mode Exit fullscreen mode

AppUser class for update DB 

package dto

import (
    "fmt"
)

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

DB Accessor

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

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

main

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

READ

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

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 

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

Error 

panic: sql: no rows in result set
Enter fullscreen mode Exit fullscreen mode

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

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

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 

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

users.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
}
Enter fullscreen mode Exit fullscreen mode 
[{"id":2,"RoleID":1,"RoleName":"system","Name":"Masa","LastUpdateDate":"2023-06-11T01:39:42.712888+09:00"}]
Enter fullscreen mode Exit fullscreen mode
💖 💪 🙅 🚩
masanori_msl
Masui Masanori

Posted on June 14, 2023

Join Our Newsletter. No Spam, Only the good stuff.

Sign up to receive the latest update from our blog.

Related

[Go] Accessing PostgreSQL with Bun 1
go [Go] Accessing PostgreSQL with Bun 1

June 14, 2023