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
);
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,
}
}
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)"`
}
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"`
}
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
}
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
}
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")
}
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
}
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
}
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
...
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
}
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"`
}
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
}
[{"id":2,"RoleID":1,"RoleName":"system","Name":"Masa","LastUpdateDate":"2023-06-11T01:39:42.712888+09:00"}]
Top comments (0)