使用pig库简化pgx查询与结果扫描

pig是一个轻量级的Golang库，专门用于简化pgx库的查询执行和结果扫描过程。它提供了更简洁的API来处理数据库操作，减少了样板代码。

安装

首先安装pig和pgx：

go get github.com/alexeyco/pig
go get github.com/jackc/pgx/v4

基本用法

1. 初始化连接

import (
    "context"
    "github.com/alexeyco/pig"
    "github.com/jackc/pgx/v4/pgxpool"
)

func main() {
    ctx := context.Background()
    
    // 创建pgx连接池
    pool, err := pgxpool.Connect(ctx, "postgres://user:password@localhost:5432/dbname")
    if err != nil {
        panic(err)
    }
    defer pool.Close()
    
    // 创建pig查询器
    q := pig.New(pool)
}

2. 查询单行数据

type User struct {
    ID       int64  `db:"id"`
    Username string `db:"username"`
    Email    string `db:"email"`
}

func getUser(ctx context.Context, q *pig.Querier, id int64) (*User, error) {
    var user User
    err := q.QueryRow(ctx, &user, 
        "SELECT id, username, email FROM users WHERE id = $1", id)
    if err != nil {
        return nil, err
    }
    return &user, nil
}

3. 查询多行数据

func listUsers(ctx context.Context, q *pig.Querier) ([]User, error) {
    var users []User
    err := q.Query(ctx, &users, 
        "SELECT id, username, email FROM users ORDER BY id")
    if err != nil {
        return nil, err
    }
    return users, nil
}

4. 执行插入/更新/删除操作

func createUser(ctx context.Context, q *pig.Querier, username, email string) (int64, error) {
    var id int64
    err := q.QueryRow(ctx, &id, 
        "INSERT INTO users (username, email) VALUES ($1, $2) RETURNING id", 
        username, email)
    if err != nil {
        return 0, err
    }
    return id, nil
}

func updateUser(ctx context.Context, q *pig.Querier, id int64, email string) error {
    _, err := q.Exec(ctx, 
        "UPDATE users SET email = $1 WHERE id = $2", 
        email, id)
    return err
}

func deleteUser(ctx context.Context, q *pig.Querier, id int64) error {
    _, err := q.Exec(ctx, "DELETE FROM users WHERE id = $1", id)
    return err
}

高级特性

1. 事务处理

func transferMoney(ctx context.Context, q *pig.Querier, from, to int64, amount float64) error {
    tx, err := q.Begin(ctx)
    if err != nil {
        return err
    }
    defer tx.Rollback(ctx) // 安全回滚

    // 扣除发送方余额
    _, err = tx.Exec(ctx, 
        "UPDATE accounts SET balance = balance - $1 WHERE id = $2", 
        amount, from)
    if err != nil {
        return err
    }

    // 增加接收方余额
    _, err = tx.Exec(ctx, 
        "UPDATE accounts SET balance = balance + $1 WHERE id = $2", 
        amount, to)
    if err != nil {
        return err
    }

    return tx.Commit(ctx)
}

2. 自定义扫描逻辑

type CustomUser struct {
    ID       int64
    FullName string
}

func (u *CustomUser) ScanRow(row pgx.Row) error {
    return row.Scan(&u.ID, &u.FullName)
}

func getCustomUser(ctx context.Context, q *pig.Querier, id int64) (*CustomUser, error) {
    var user CustomUser
    err := q.QueryRow(ctx, &user, 
        "SELECT id, first_name || ' ' || last_name FROM users WHERE id = $1", id)
    if err != nil {
        return nil, err
    }
    return &user, nil
}

3. 批量插入

func batchInsertUsers(ctx context.Context, q *pig.Querier, users []User) error {
    batch := &pgx.Batch{}
    
    for _, user := range users {
        batch.Queue(
            "INSERT INTO users (username, email) VALUES ($1, $2)",
            user.Username, user.Email,
        )
    }
    
    return q.SendBatch(ctx, batch).Close()
}

性能考虑

1. pig在内部使用了pgx的预处理语句缓存，重复查询会有更好的性能
2. 对于大量数据的查询，考虑使用QueryFunc来流式处理结果
3. 批量操作时使用pgx的Batch功能

pig库通过简化pgx的API，让数据库操作更加直观和易于维护，同时保持了pgx的高性能特性。它特别适合中小型项目，可以显著减少数据库访问层的样板代码。