Golang数据库迁移与ORM操作：使用Soda库

Soda是一个流行的Golang数据库迁移和ORM操作工具，由gobuffalo项目开发。它提供了简洁的API来管理数据库迁移和执行常见的ORM操作。

安装Soda

首先安装Soda CLI工具和库：

go get -u github.com/gobuffalo/pop/...
go install github.com/gobuffalo/pop/soda@latest

初始化配置

1. 创建数据库配置文件database.yml：

development:
  dialect: postgres
  database: myapp_development
  user: postgres
  password: password
  host: 127.0.0.1
  pool: 5

test:
  url: postgres://postgres:password@127.0.0.1:5432/myapp_test?sslmode=disable

production:
  url: {{envOr "DATABASE_URL" "postgres://postgres:password@127.0.0.1:5432/myapp_production?sslmode=disable"}}

创建迁移文件

使用Soda CLI创建迁移：

soda generate fizz create_users

这会生成一个迁移文件，如migrations/20230101000000_create_users.up.fizz：

create_table("users") {
  t.Column("id", "uuid", {"primary": true})
  t.Column("name", "string", {"null": false})
  t.Column("email", "string", {"null": false, "unique": true})
  t.Column("password_hash", "string", {"null": false})
  t.Column("created_at", "timestamp", {"null": false})
  t.Column("updated_at", "timestamp", {"null": false})
}

执行迁移

运行迁移命令：

soda migrate up

定义模型

创建对应的Go模型：

package models

import (
	"time"
	
	"github.com/gobuffalo/pop/v6"
	"github.com/gobuffalo/validate/v3"
	"github.com/gobuffalo/validate/v3/validators"
	"github.com/gofrs/uuid"
)

type User struct {
	ID           uuid.UUID `json:"id" db:"id"`
	Name         string    `json:"name" db:"name"`
	Email        string    `json:"email" db:"email"`
	PasswordHash string    `json:"-" db:"password_hash"`
	CreatedAt    time.Time `json:"created_at" db:"created_at"`
	UpdatedAt    time.Time `json:"updated_at" db:"updated_at"`
}

// Users is a slice of User
type Users []User

// Validate gets run every time you call a "pop.Validate*" method.
func (u *User) Validate(tx *pop.Connection) (*validate.Errors, error) {
	return validate.Validate(
		&validators.StringIsPresent{Field: u.Name, Name: "Name"},
		&validators.StringIsPresent{Field: u.Email, Name: "Email"},
		&validators.StringIsPresent{Field: u.PasswordHash, Name: "PasswordHash"},
	), nil
}

基本CRUD操作

创建记录

func CreateUser(db *pop.Connection, user *models.User) error {
	verrs, err := db.ValidateAndCreate(user)
	if err != nil {
		return err
	}
	if verrs.HasAny() {
		return verrs
	}
	return nil
}

// 使用示例
user := &models.User{
	Name:         "John Doe",
	Email:        "john@example.com",
	PasswordHash: "hashed_password",
}
err := CreateUser(c.DB, user)

查询记录

// 获取单个用户
func GetUser(db *pop.Connection, id uuid.UUID) (*models.User, error) {
	user := &models.User{}
	err := db.Find(user, id)
	return user, err
}

// 查询多个用户
func ListUsers(db *pop.Connection) (models.Users, error) {
	users := models.Users{}
	err := db.All(&users)
	return users, err
}

// 条件查询
func FindUserByEmail(db *pop.Connection, email string) (*models.User, error) {
	user := &models.User{}
	err := db.Where("email = ?", email).First(user)
	return user, err
}

更新记录

func UpdateUser(db *pop.Connection, user *models.User) error {
	verrs, err := db.ValidateAndUpdate(user)
	if err != nil {
		return err
	}
	if verrs.HasAny() {
		return verrs
	}
	return nil
}

删除记录

func DeleteUser(db *pop.Connection, id uuid.UUID) error {
	user := &models.User{}
	if err := db.Find(user, id); err != nil {
		return err
	}
	return db.Destroy(user)
}

高级查询

// 分页查询
func PaginateUsers(db *pop.Connection, page, perPage int) (models.Users, paginator.PageNation, error) {
	users := models.Users{}
	q := db.Paginate(page, perPage)
	err := q.All(&users)
	return users, q.Paginator, err
}

// 关联查询
type Post struct {
	ID        uuid.UUID `json:"id" db:"id"`
	Title     string    `json:"title" db:"title"`
	Content   string    `json:"content" db:"content"`
	UserID    uuid.UUID `json:"user_id" db:"user_id"`
	CreatedAt time.Time `json:"created_at" db:"created_at"`
	UpdatedAt time.Time `json:"updated_at" db:"updated_at"`
	
	User *User `json:"user,omitempty" belongs_to:"user"`
}

// 获取带用户信息的文章
func GetPostWithUser(db *pop.Connection, id uuid.UUID) (*Post, error) {
	post := &Post{}
	err := db.Eager("User").Find(post, id)
	return post, err
}

事务处理

func TransferMoney(db *pop.Connection, fromID, toID uuid.UUID, amount int) error {
	return db.Transaction(func(tx *pop.Connection) error {
		from := &Account{}
		if err := tx.Find(from, fromID); err != nil {
			return err
		}
		
		to := &Account{}
		if err := tx.Find(to, toID); err != nil {
			return err
		}
		
		if from.Balance < amount {
			return errors.New("insufficient balance")
		}
		
		from.Balance -= amount
		to.Balance += amount
		
		if err := tx.Update(from); err != nil {
			return err
		}
		
		if err := tx.Update(to); err != nil {
			return err
		}
		
		return nil
	})
}

总结

Soda提供了强大的数据库迁移和ORM功能，主要特点包括：

1. 简洁的迁移文件语法
2. 支持多种数据库(PostgreSQL, MySQL, SQLite等)
3. 自动模型验证
4. 关联关系处理
5. 事务支持
6. 分页查询

通过Soda，你可以高效地管理数据库变更和执行ORM操作，同时保持代码的整洁和可维护性。