介绍
这次，我将尝试使用bun。
访问postgresql创建和交易
因为我不明白如何使用bun的迁移函数，所以我创建了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
);
楷模
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)"`
}
更新DB的Appuser类
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登录器
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
}
用户
package db
import (
"context"
"database/sql"
"encoding/base64"
"fmt"
"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)
}
// Get base 64 encoded Hasu value to save the password
key := pbkdf2.Key([]byte(user.Password), []byte(user.Password), 1000, 64, 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
}
主要的
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")
}
读
用户
...
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
}
恐慌：SQL：结果集中没有行
执行期望获得1行的代码，如果结果为0行，我会遇到错误。
用户
...
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
}
错误
panic: sql: no rows in result set
因为这是预期的行为，所以我应该分开无行和其他行的错误。
用户
...
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
}
映射到另一个类
要返回用户数据，我想删除他们的密码并将其放入另一个类。
我该怎么办？
我可以使用RAW SQL并映射一个类。
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"`
}
用户
...
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"}]