Rust数据库查询构建器sea-query-postgres的使用：支持PostgreSQL的高效SQL生成与操作

安装

在项目目录中运行以下Cargo命令：

cargo add sea-query-postgres

或者在Cargo.toml中添加以下行：

sea-query-postgres = "0.5.0"

示例代码

以下是一个使用sea-query-postgres构建PostgreSQL查询的完整示例：

use sea_query::{PostgresQueryBuilder, Query, Iden, Expr};
use std::error::Error;

// 定义一个表结构
#[derive(Iden)]
enum User {
    Table,
    Id,
    Name,
    Email,
}

fn main() -> Result<(), Box<dyn Error>> {
    // 创建查询构建器
    let query = Query::select()
        .columns([User::Id, User::Name, User::Email])
        .from(User::Table)
        .and_where(Expr::col(User::Name).eq("John"))
        .limit(10)
        .offset(0)
        .to_owned();

    // 生成PostgreSQL SQL语句
    let (sql, values) = query.build(PostgresQueryBuilder);
    
    println!("SQL: {}", sql);
    println!("Values: {:?}", values);

    Ok(())
}

更复杂的示例

use sea_query::{PostgresQueryBuilder, Query, Iden, Expr, Order, Func};
use std::error::Error;

// 定义表结构
#[derive(Iden)]
enum Product {
    Table,
    Id,
    Name,
    Price,
    CategoryId,
}

#[derive(Iden)]
enum Category {
    Table,
    Id,
    Name,
}

fn main() -> Result<(), Box<dyn Error>> {
    // 构建复杂查询
    let query = Query::select()
        .columns([
            (Product::Table, Product::Id),
            (Product::Table, Product::Name),
            (Product::Table, Product::Price),
            (Category::Table, Category::Name),
        ])
        .from(Product::Table)
        .left_join(
            Category::Table,
            Expr::col((Product::Table, Product::CategoryId))
                .equals((Category::Table, Category::Id)),
        )
        .and_where(Expr::col(Product::Price).gte(100))
        .and_where(Expr::col(Category::Name).like("Electronics%"))
        .order_by(Product::Price, Order::Desc)
        .order_by(Product::Name, Order::Asc)
        .group_by_col((Category::Table, Category::Id))
        .having(Expr::expr(Foc::count(Expr::col(Product::Id))).gte(5))
        .limit(20)
        .offset(10)
        .to_owned();

    // 生成PostgreSQL SQL语句
    let (sql, values) = query.build(PostgresQueryBuilder);
    
    println!("SQL: {}", sql);
    println!("Values: {:?}", values);

    Ok(())
}

插入数据示例

use sea_query::{PostgresQueryBuilder, Query, Iden, Value};
use std::error::Error;

#[derive(Iden)]
enum User {
    Table,
    Id,
    Name,
    Email,
    Age,
}

fn main() -> Result<(), Box<dyn Error>> {
    // 构建插入查询
    let query = Query::insert()
        .into_table(User::Table)
        .columns([User::Name, User::Email, User::Age])
        .values_panic([
            "John Doe".into(),
            "john@example.com".into(),
            30.into(),
        ])
        .values_panic([
            "Jane Smith".into(),
            "jane@example.com".into(),
            25.into(),
        ])
        .returning_cols([User::Id, User::Name])
        .to_owned();

    // 生成PostgreSQL SQL语句
    let (sql, values) = query.build(PostgresQueryBuilder);
    
    println!("SQL: {}", sql);
    println!("Values: {:?}", values);

    Ok(())
}

更新数据示例

use sea_query::{PostgresQueryBuilder, Query, Iden, Expr};
use std::error::Error;

#[derive(Iden)]
enum User {
    Table,
    Id,
    Name,
    Email,
}

fn main() -> Result<(), Box<dyn Error>> {
    // 构建更新查询
    let query = Query::update()
        .table(User::Table)
        .values([
            (User::Name, "John Smith".into()),
            (User::Email, "john.smith@example.com".into()),
        ])
        .and_where(Expr::col(User::Id).eq(1))
        .to_owned();

    // 生成PostgreSQL SQL语句
    let (sql, values)極query.build(PostgresQueryBuilder);
    
    println!("SQL: {}", sql);
    println!("Values: {:?}", values);

    Ok(())
}

删除数据示例

use sea_query::{PostgresQueryBuilder, Query, Iden, Expr};
use std::error::Error;

#[derive(Iden)]
enum User {
    Table,
    Id,
}

fn main() -> Result<(), Box<dyn Error>> {
    // 构建删除查询
    let query = Query::delete()
        .from_table(User::Table)
        .and_where(Expr::col(User::Id).eq(1))
        .to_owned();

    // 生成PostgreSQL SQL语句
    let (sql, values) = query.build(PostgresQueryBuilder);
    
    println!("SQL: {}", sql);
    println!("Values: {:?}", values);

    Ok(())
}

完整示例（事务处理）

以下是使用sea-query-postgres进行事务处理的完整示例：

use sea_query::{PostgresQueryBuilder, Query, Iden, Expr, Value};
use std::error::Error;
use tokio_postgres::{Client, NoTls};

// 定义表结构
#[derive(Iden)]
enum Account {
    Table,
    Id,
    UserId,
    Balance,
}

#[derive(Iden)]
enum Transaction {
    Table,
    Id,
    AccountId,
    Amount,
    Type,
}

async fn transfer_funds(
    client: &mut Client,
    from_account: i32,
    to_account: i32,
    amount: i64,
) -> Result<(), Box<dyn Error>> {
    // 开始事务
    let transaction = client.transaction().await?;

    // 1. 检查转出账户余额是否足够
    let check_balance_query = Query::select()
        .column(Account::Balance)
        .from(Account::Table)
        .and_where(Expr::col(Account::Id).eq(from_account))
        .to_owned();

    let (check_sql, check_values) = check_balance_query.build(PostgresQueryBuilder);
    let row = transaction.query_one(&check_sql, &check_values).await?;
    let current_balance: i64 = row.get(0);
    
    if current_balance < amount {
        return Err("Insufficient funds".into());
    }

    // 2. 扣除转出账户金额
    let deduct_query = Query::update()
        .table(Account::Table)
        .value(Account::Balance, Expr::col(Account::Balance).sub(amount))
        .and_where(Expr::col(Account::Id).eq(from_account))
        .to_owned();

    let (deduct_sql, deduct_values) = deduct_query.build(PostgresQueryBuilder);
    transaction.execute(&deduct_sql, &deduct_values).await?;

    // 3. 增加转入账户金额
    let add_query = Query::update()
        .table(Account::Table)
        .value(Account::Balance, Expr::col(Account::Balance).add(amount))
        .and_where(Expr::col(Account::Id).eq(to_account))
        .to_owned();

    let (add_sql, add_values) = add_query.build(PostgresQueryBuilder);
    transaction.execute(&add_sql, &add_values).await?;

    // 4. 记录交易
    let record_query = Query::insert()
        .into_table(Transaction::Table)
        .columns([
            Transaction::AccountId,
            Transaction::Amount,
            Transaction::Type,
        ])
        .values_panic([
            from_account.into(),
            amount.into(),
            "DEBIT".into(),
        ])
        .values_panic([
            to_account.into(),
            amount.into(),
            "CREDIT".into(),
        ])
        .to_owned();

    let (record_sql, record_values) = record_query.build(PostgresQueryBuilder);
    transaction.execute(&record_sql, &record_values).await?;

    // 提交事务
    transaction.commit().await?;

    Ok(())
}

#[tokio::main]
async fn main() -> Result<(), Box<dyn Error>> {
    let (mut client, connection) = tokio_postgres::connect(
        "host=localhost user=postgres dbname=test",
        NoTls,
    ).await?;

    tokio::spawn(async move {
        if let Err(e) = connection.await {
            eprintln!("Connection error: {}", e);
        }
    });

    // 转账示例
    transfer_funds(&mut client, 1, 2, 100).await?;

    Ok(())
}

这些示例展示了如何使用sea-query-postgres构建各种类型的PostgreSQL查询。该库提供了类型安全的查询构建方式，支持复杂的SQL操作，同时保持了代码的可读性和可维护性。