Rust SQLx 数据库访问实战类型安全的数据库操作引言在Rust后端开发中SQLx是一个非常强大的数据库库。作为一名从Python转向Rust的后端开发者我发现SQLx提供了类型安全的数据库访问方式结合Rust的编译时检查可以在开发阶段就捕获很多错误。SQLx 核心概念什么是SQLxSQLx是一个异步的、类型安全的SQL库具有以下特点编译时检查支持编译时验证SQL查询异步支持完全异步与Tokio无缝集成多数据库支持支持PostgreSQL、MySQL、SQLite等零运行时开销查询在编译时优化架构设计┌─────────────────────────────────────────────────────────────┐ │ Rust 应用程序 │ └─────────────────────────────┬─────────────────────────────┘ │ ▼ ┌─────────────────────────────────────────────────────────────┐ │ SQLx 层 │ │ ┌─────────────────────────────────────────────────────┐ │ │ │ 编译时SQL验证 → 类型推断 → 查询优化 │ │ │ └─────────────────────────────────────────────────────┘ │ └─────────────────────────────┬─────────────────────────────┘ │ ▼ ┌─────────────────────────────────────────────────────────────┐ │ 数据库驱动层 │ │ ┌──────────┐ ┌──────────┐ ┌──────────┐ │ │ │PostgreSQL│ │ MySQL │ │ SQLite │ │ │ └──────────┘ └──────────┘ └──────────┘ │ └─────────────────────────────────────────────────────────────┘环境搭建与基础配置Cargo.toml配置[package] name sqlx-app version 0.1.0 edition 2021 [dependencies] sqlx { version 0.7, features [runtime-tokio-native-tls, postgres] } tokio { version 1.0, features [full] } serde { version 1.0, features [derive] }连接数据库use sqlx::postgres::PgPool; #[tokio::main] async fn main() - Result(), sqlx::Error { let pool PgPool::connect(postgres://user:passwordlocalhost:5432/mydb).await?; let row: (i64,) sqlx::query_as(SELECT COUNT(*) FROM users) .fetch_one(pool) .await?; println!(Total users: {}, row.0); Ok(()) }基本查询操作查询单行数据use sqlx::postgres::PgPool; use serde::Deserialize; #[derive(Debug, Deserialize)] struct User { id: i64, name: String, email: String, age: Optioni32, } async fn get_user(pool: PgPool, user_id: i64) - ResultUser, sqlx::Error { let user sqlx::query_as!(User, SELECT * FROM users WHERE id $1, user_id) .fetch_one(pool) .await?; Ok(user) } #[tokio::main] async fn main() - Result(), sqlx::Error { let pool PgPool::connect(postgres://user:passwordlocalhost:5432/mydb).await?; let user get_user(pool, 1).await?; println!(User: {:?}, user); Ok(()) }查询多行数据async fn list_users(pool: PgPool, page: i64, page_size: i64) - ResultVecUser, sqlx::Error { let offset (page - 1) * page_size; let users sqlx::query_as!(User, SELECT * FROM users ORDER BY id LIMIT $1 OFFSET $2, page_size, offset ) .fetch_all(pool) .await?; Ok(users) }插入数据async fn create_user( pool: PgPool, name: str, email: str, age: Optioni32 ) - Resulti64, sqlx::Error { let result sqlx::query!( INSERT INTO users (name, email, age) VALUES ($1, $2, $3) RETURNING id, name, email, age ) .fetch_one(pool) .await?; Ok(result.id) }事务处理基本事务async fn transfer_money( pool: PgPool, from_id: i64, to_id: i64, amount: f64 ) - Result(), sqlx::Error { let mut tx pool.begin().await?; let from_balance: (f64,) sqlx::query_as(SELECT balance FROM accounts WHERE id $1) .bind(from_id) .fetch_one(mut tx) .await?; if from_balance.0 amount { return Err(sqlx::Error::RowNotFound); } sqlx::query(UPDATE accounts SET balance balance - $1 WHERE id $2) .bind(amount) .bind(from_id) .execute(mut tx) .await?; sqlx::query(UPDATE accounts SET balance balance $1 WHERE id $2) .bind(amount) .bind(to_id) .execute(mut tx) .await?; tx.commit().await?; Ok(()) }保存点async fn complex_transaction(pool: PgPool) - Result(), sqlx::Error { let mut tx pool.begin().await?; sqlx::query!(INSERT INTO logs (message) VALUES ($1), Starting transaction) .execute(mut tx) .await?; let savepoint tx.savepoint().await?; match sqlx::query!(INSERT INTO items (name) VALUES ($1), test) .execute(mut savepoint) .await { Ok(_) savepoint.commit().await?, Err(_) savepoint.rollback().await?, } tx.commit().await?; Ok(()) }类型安全查询查询宏#[derive(Debug, sqlx::FromRow)] struct Product { id: i64, name: String, price: f64, stock: i32, } async fn get_products_in_stock(pool: PgPool) - ResultVecProduct, sqlx::Error { let products sqlx::query_as!(Product, SELECT id, name, price, stock FROM products WHERE stock 0 ) .fetch_all(pool) .await?; Ok(products) }编译时验证// 编译时验证SQL语法和类型 #[sqlx::query(SELECT id, name FROM users WHERE id $1)] async fn get_user_query(pool: PgPool, id: i64) - ResultUser, sqlx::Error { let user sqlx::query_as!(User, SELECT id, name, email, age FROM users WHERE id $1, id ) .fetch_one(pool) .await?; Ok(user) }实际业务场景用户认证系统use argon2::verify_encoded; async fn authenticate_user( pool: PgPool, email: str, password: str ) - ResultUser, sqlx::Error { let user: (i64, String, String) sqlx::query_as!( SELECT id, name, password_hash FROM users WHERE email $1, email ) .fetch_one(pool) .await?; if verify_encoded(user.2, password.as_bytes()).unwrap() { let full_user sqlx::query_as!(User, SELECT * FROM users WHERE id $1, user.0) .fetch_one(pool) .await?; Ok(full_user) } else { Err(sqlx::Error::RowNotFound) } }数据分页async fn search_users( pool: PgPool, query: str, page: i64, page_size: i64 ) - Result(VecUser, i64), sqlx::Error { let offset (page - 1) * page_size; let search_pattern format!(%{}%, query); let users sqlx::query_as!(User, SELECT * FROM users WHERE name LIKE $1 OR email LIKE $1 ORDER BY id LIMIT $2 OFFSET $3, search_pattern, page_size, offset ) .fetch_all(pool) .await?; let total: (i64,) sqlx::query_as!( SELECT COUNT(*) FROM users WHERE name LIKE $1 OR email LIKE $1, search_pattern ) .fetch_one(pool) .await?; Ok((users, total.0)) }性能优化连接池配置use sqlx::postgres::PgPoolOptions; async fn create_pool() - ResultPgPool, sqlx::Error { let pool PgPoolOptions::new() .max_connections(20) .min_connections(5) .connect_timeout(std::time::Duration::from_secs(30)) .idle_timeout(std::time::Duration::from_secs(60)) .connect(postgres://user:passwordlocalhost:5432/mydb) .await?; Ok(pool) }批量操作async fn batch_insert_users(pool: PgPool, users: [(String, String)]) - Result(), sqlx::Error { let mut tx pool.begin().await?; for (name, email) in users { sqlx::query!(INSERT INTO users (name, email) VALUES ($1, $2), name, email) .execute(mut tx) .await?; } tx.commit().await?; Ok(()) }预编译语句async fn prepare_statement(pool: PgPool) - Result(), sqlx::Error { let query sqlx::query!( SELECT * FROM users WHERE id $1 ); let user1 query.bind(1).fetch_one(pool).await?; let user2 query.bind(2).fetch_one(pool).await?; println!(User 1: {:?}, user1); println!(User 2: {:?}, user2); Ok(()) }总结SQLx为Rust开发者提供了一种类型安全的数据库访问方式。通过编译时SQL验证和异步支持SQLx在保证类型安全的同时也提供了很好的性能。从Python开发者的角度来看SQLx类似于SQLAlchemy的Core但具有更强的类型安全保障。在实际项目中建议合理配置连接池使用事务处理保证数据一致性并充分利用SQLx的类型安全特性来提高代码质量。