sqlx创建连接

安装sqlx

runtime-tokio: 让sqlx跑在Tokio runtime上。
postgres: 打开PostgreSQL支持。
- mysql: 支持MySQL。
- sqlite: 支持SQLite
json: 支持JSON相关类型映射。
- 需要安装serde_json库
chrono: 支持chrono的时间类型映射，比如DateTime<Utc>。
- 需要安装chrono库
macros: 打开sqlx的过程宏能力。
- query!
- query_as!
- query_scalar!
- migrate!

sqlx = { version = "0.9", features = ["runtime-tokio", "json", "postgres", "chrono", "macros"] }

# 启动psql
docker run -d --name test-postgres -p 15432:5432 -e POSTGRES_PASSWORD=123456 -e POSTGRES_USER=postgres -e POSTGRES_DB=testdb postgres:15
# 进入容器
docker exec -it 516d82fd11b7 /bin/bash
# 在容器内连接数据库
PGPASSWORD=123456 psql -h 127.0.0.1 -p 5432 -U postgres -d testdb

\c testdb
-- 查看当前数据库
select current_database();

drop table if exists users;

create table users (
    name text not null,
    age integer not null,
    insert_ts timestamp not null,
    comment text,
    is_test boolean not null
);

insert into users (name, age, insert_ts, comment, is_test) values
('Alice', 18, '2026-08-04 10:00:00', 'first test user', true),
('Bob', 25, '2026-08-04 10:05:00', null, false),
('Charlie', 30, '2026-08-04 10:10:00', 'backend developer', true),
('David', 16, '2026-08-04 10:15:00', 'under age sample', false),
('Eve', 22, '2026-08-04 10:20:00', null, true),
('Frank', 40, '2026-08-04 10:25:00', 'admin user', false),
('Grace', 19, '2026-08-04 10:30:00', 'new user', true),
('Heidi', 27, '2026-08-04 10:35:00', 'qa tester', false),
('Ivan', 35, '2026-08-04 10:40:00', null, true),
('Judy', 21, '2026-08-04 10:45:00', 'frontend developer', false),
('Mallory', 28, '2026-08-04 10:50:00', 'security test user', true),
('Niaj', 17, '2026-08-04 10:55:00', null, false),
('Olivia', 24, '2026-08-04 11:00:00', 'product manager', true),
('Peggy', 32, '2026-08-04 11:05:00', 'data engineer', false),
('Trent', 45, '2026-08-04 11:10:00', 'senior developer', true);

查看库的内容

cargo info sqlx --registry crates-io -v

连接数据库

写法	返回类型	类型
`PgPool::connect(...)`	`PgPool`	连接池
`PgPoolOptions::connect(...)`	`PgPool`	连接池
`PgPool::connect_lazy(...)`	`PgPool`	连接池
`PgPoolOptions::connect_lazy(...)`	`PgPool`	连接池
`PgPoolOptions::connect_with(...)`	`PgPool`	连接池
`PgConnection::connect(...)`	`PgConnection`	单连接

单连接

PgConnection

只想建立一个单独连接

use sqlx::{Connection, PgConnection};

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let mut conn = PgConnection::connect(
        "postgres://postgres:123456@localhost:5432/testdb"
    ).await?;

    Ok(())
}

使用conn

&mut conn这保证同一时间只有一个任务能使用这条连接
每次执行都要独占使用conn

PgConnection 代表一条真实的PostgreSQL连接。执行SQL时，这条连接内部状态会变化,所以它需要可变引用
- 发送查询请求
- 接收数据库响应
- 管理 pending 状态
- 处理协议缓冲区
- 更新连接状态
- 处理事务状态

let mut conn = PgConnection::connect(database_url).await?;
sqlx::query("SELECT 1")
    .execute(&mut conn)
    .await?;
sqlx::query("SELECT 2")
    .execute(&mut conn)
    .await?;

连接池

PgPool

PgPool本质是Pool<Postgres>

use sqlx::PgPool;

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let database_url = "postgres://user:password@localhost:5432/mydb";

    let pool = PgPool::connect(database_url).await?;

    Ok(())
}

PgPoolOptions

PgPoolOptions用来配置连接池参数，然后再创建PgPool

配置

use sqlx::postgres::PgPoolOptions;
use std::time::Duration;
let pool = PgPoolOptions::new()
    .max_connections(10) // 最大连接数
    .min_connections(1) // 最小保持连接数
    .acquire_timeout(Duration::from_secs(5)) // 获取连接的超时时间
    .idle_timeout(Duration::from_secs(10 * 60)) // 空闲连接多久后关闭
    .max_lifetime(Duration::from_secs(30 * 60)) // 连接最大存活时间
    .connect(database_url) // 使用database_url连接"postgres://postgres:123456@localhost:5432/testdb"
    .await?;

use sqlx::postgres::PgPoolOptions;

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let pool = PgPoolOptions::new()
        .max_connections(5)
        .connect("postgres://postgres:123456@localhost:5432/testdb")
        .await?;

    Ok(())
}

PgPoolOptions配合PgConnectOptions

可以从字符串解析

let options = PgConnectOptions::from_str(
    "postgres://postgres:123456@localhost:5432/testdb"
)?;

use sqlx::postgres::{PgConnectOptions, PgPoolOptions};
use std::str::FromStr;

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let options = PgConnectOptions::new()
        .host("localhost")
        .port(15432)
        .username("postgres")
        .password("123456")
        .database("testdb");

    let pool = PgPoolOptions::new()
        .max_connections(5)
        .connect_with(options) // 使用PgConnectOptions
        .await?;

    Ok(())
}

使用pool

PgPool/PgPoolOptions不是一条具体连接，而是连接池管理器

sqlx::query("SELECT 1")
    .execute(&pool)
    .await?;

PgPool可以被多个任务共享

let pool = PgPool::connect(database_url).await?;

let p1 = pool.clone();
let p2 = pool.clone();

tokio::spawn(async move {
    sqlx::query("SELECT 1").execute(&p1).await.unwrap();
});

tokio::spawn(async move {
    sqlx::query("SELECT 2").execute(&p2).await.unwrap();
});

懒连接

connect_lazy 不会立即连接数据库，而是在真正查询时才建立连接

PgPool::connect_lazy懒连接

use sqlx::PgPool;

fn main() -> Result<(), sqlx::Error> {
    let pool = PgPool::connect_lazy(
        "postgres://postgres:123456@localhost:5432/testdb"
    )?;

    Ok(())
}

PgPoolOptions懒连接

普通连接: 如果数据库没启动、地址错了、密码错了，会马上报错
懒连接: 创建一个连接池对象，不马上连接数据库。如果数据库没启动，不会报错。

use sqlx::postgres::PgPoolOptions;

let pool = PgPoolOptions::new()
    .max_connections(5)
    .connect_lazy("postgres://postgres:123456@localhost:5432/testdb")?;

使用pool

sqlx::query("SELECT 1")
    .execute(&pool)
    .await?;

区别

连接方式	什么时候连接数据库	适合场景
`connect()`	创建 pool 时立即连接	生产服务、启动时校验数据库
`connect_lazy()`	第一次查询/获取连接时才连接	CLI、多模式程序、测试、数据库非启动必需

增删改查

查询内容

查询方法

写法	返回类型	编译期检查	适合场景
`query`	`Row`	否	动态 SQL，手动取字段
`query_as`	struct / tuple	否	动态 SQL，映射结构体
`query!`	匿名结构体	是	固定 SQL，编译期检查
`query_as!`	指定 struct	是	固定 SQL，映射结构体
`query_scalar`	单个字段	否	只查一列
`query_scalar!`	单个字段	是	固定 SQL，只查一列
`query_file!`	匿名结构体	是	SQL 写在文件里
`query_file_as!`	指定 struct	是	SQL 文件 + 映射结构体
`query_file_scalar!`	单个字段	是	SQL 文件 + 只查一列

//query_file! / query_file_as! / query_file_scalar! 的路径是编译期字符串字面量，通常相对于 Cargo.toml 所在目录。
// 不能传变量，也不能传 Path。
// 报错
let path = "test_sql/a.sql";
sqlx::query_file!(path, 30i32);

查询结果获取方法总览

方法	含义	返回
`fetch_one`	返回第一行；如果没有任何行，报 RowNotFound。	`T`
`fetch_optional`	返回第一行的 Some(row)；如果没有任何行，返回 None。	`Option<T>`
`fetch_all`	查出所有行	`Vec<T>`
`fetch`	流式读取多行	`Stream<Item = Result<T, sqlx::Error>>`
`execute`	执行不返回数据行的 SQL	`PgQueryResult`

查询

SQL 类型	需求	方法
查询一条	必须有一行	`fetch_one`
查询一条	可能没有一行	`fetch_optional`
查询多条	多行一次性加载	`fetch_all`
查询多条	多行流式读取	`fetch`

函数查询

query查询

PostgreSQL 类型	Rust 推荐类型
`smallint` / `int2`	`i16`
`integer` / `int4`	`i32`
`bigint` / `int8`	`i64`

fetch_all

返回Vec<sqlx::postgres::PgRow>

use sqlx::{Row, postgres::{PgConnectOptions, PgPoolOptions}};
use chrono::NaiveDateTime;

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let options = PgConnectOptions::new()
        .host("127.0.0.1")
        .port(15432)
        .username("postgres")
        .password("123456")
        .database("testdb");

    let pool = PgPoolOptions::new()
        .max_connections(5)
        .connect_with(options) // 使用PgConnectOptions
        .await?;
    

    let rows = sqlx::query("select name, age, insert_ts, comment, is_test from users where age > $1")
        .bind(17i32)
        .fetch_all(&pool)
        .await?;

    for row in rows {
        let name: String = row.get("name");
        let age: i32 = row.get("age");
        let insert_ts:NaiveDateTime  = row.get("insert_ts");
        let comment: Option<String> = row.get("comment");
        let is_test: bool = row.get("is_test");
        println!("{}|{}|{}|{:?}|{}", name, age, insert_ts, comment, is_test);
    }

    Ok(())
}

Alice|18|2026-08-04 10:00:00|Some("first test user")|true
Bob|25|2026-08-04 10:05:00|None|false
Charlie|30|2026-08-04 10:10:00|Some("backend developer")|true
Eve|22|2026-08-04 10:20:00|None|true
Frank|40|2026-08-04 10:25:00|Some("admin user")|false
Grace|19|2026-08-04 10:30:00|Some("new user")|true
Heidi|27|2026-08-04 10:35:00|Some("qa tester")|false
Ivan|35|2026-08-04 10:40:00|None|true
Judy|21|2026-08-04 10:45:00|Some("frontend developer")|false
Mallory|28|2026-08-04 10:50:00|Some("security test user")|true
Olivia|24|2026-08-04 11:00:00|Some("product manager")|true
Peggy|32|2026-08-04 11:05:00|Some("data engineer")|false
Trent|45|2026-08-04 11:10:00|Some("senior developer")|true

fetch_one

返回sqlx::postgres::PgRow

use sqlx::{Row, postgres::{PgConnectOptions, PgPoolOptions}};
use chrono::NaiveDateTime;

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let options = PgConnectOptions::new()
        .host("127.0.0.1")
        .port(15432)
        .username("postgres")
        .password("123456")
        .database("testdb");

    let pool = PgPoolOptions::new()
        .max_connections(5)
        .connect_with(options) // 使用PgConnectOptions
        .await?;
    

    let row = sqlx::query("select name, age, insert_ts, comment, is_test from users where name=$1")
        .bind("Bob")
        .fetch_one(&pool)
        .await?;

    let name: String = row.get("name");
    let age: i32 = row.get("age");
    let insert_ts:NaiveDateTime  = row.get("insert_ts");
    let comment: Option<String> = row.get("comment");
    let is_test: bool = row.get("is_test");
    println!("{}|{}|{}|{:?}|{}", name, age, insert_ts, comment, is_test);

    Ok(())
}

Bob|25|2026-08-04 10:05:00|None|false

fetch_optional

返回Option<sqlx::postgres::PgRow>

use std::result;

use sqlx::{Row, postgres::{PgConnectOptions, PgPoolOptions}};
use chrono::NaiveDateTime;

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let options = PgConnectOptions::new()
        .host("127.0.0.1")
        .port(15432)
        .username("postgres")
        .password("123456")
        .database("testdb");

    let pool = PgPoolOptions::new()
        .max_connections(5)
        .connect_with(options) // 使用PgConnectOptions
        .await?;
    

    let result = sqlx::query("select name, age, insert_ts, comment, is_test from users where name=$1")
        .bind("Bob")
        .fetch_optional(&pool)
        .await?;
    if let Some(row) = result {
        let name: String = row.get("name");
        let age: i32 = row.get("age");
        let insert_ts:NaiveDateTime  = row.get("insert_ts");
        let comment: Option<String> = row.get("comment");
        let is_test: bool = row.get("is_test");
        println!("{}|{}|{}|{:?}|{}", name, age, insert_ts, comment, is_test);
    };

    Ok(())
}

fetch

use sqlx::{
    Row,
    postgres::{PgConnectOptions, PgPoolOptions},
};

use chrono::NaiveDateTime;
// 需要引入futures_util
use futures_util::StreamExt;

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let options = PgConnectOptions::new()
        .host("127.0.0.1")
        .port(15432)
        .username("postgres")
        .password("123456")
        .database("testdb");

    let pool = PgPoolOptions::new()
        .max_connections(5)
        .connect_with(options) // 使用PgConnectOptions
        .await?;

    // 改为可变流
    let mut stream =
        sqlx::query("select name, age, insert_ts, comment, is_test from users where age > $1")
            .bind(35i32)
            .fetch(&pool);

    // 使用next
    while let Some(result) = stream.next().await {
        match result {
            Ok(row) => {
                let name: String = row.get("name");
                let age: i32 = row.get("age");
                let insert_ts: NaiveDateTime = row.get("insert_ts");
                let comment: Option<String> = row.get("comment");
                let is_test: bool = row.get("is_test");
                println!("{}|{}|{}|{:?}|{}", name, age, insert_ts, comment, is_test);
            }
            Err(err) => {
                eprintln!("query error: {:?}", err);
                return Err(err);
            }
        }
    }

    Ok(())
}

Frank|40|2026-08-04 10:25:00|Some("admin user")|false
Trent|45|2026-08-04 11:10:00|Some("senior developer")|true

query_as查询

把查询结果映射为struct

struct中需要实现sqlx::FromRow,否则query_as::<_, MyStruct>()不知道怎么把一行数据映射成结构体
字段名和类型需要一致
- rust字段名create_time, sql字段create_ts,可以在sql中起别名create_ts as create_time

fetch_all

返回Vec<struct>, 查不到返回空的vec

use sqlx::{
    FromRow,
    postgres::{PgConnectOptions, PgPoolOptions}
};
use chrono::NaiveDateTime;

#[derive(Debug, FromRow)]
struct User {
    name: String,
    age: i32,
    insert_ts: NaiveDateTime,
    comment: Option<String>,
    is_test: bool
}

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let options = PgConnectOptions::new()
        .host("127.0.0.1")
        .port(15432)
        .username("postgres")
        .password("123456")
        .database("testdb");

    let pool = PgPoolOptions::new()
        .max_connections(5)
        .connect_with(options) // 使用PgConnectOptions
        .await?;
    

    let users = sqlx::query_as::<_, User>("select name, age, insert_ts, comment, is_test from users where age > $1")
        .bind(20i32)
        .fetch_all(&pool)
        .await.unwrap();

    for user in users {
        println!("{:?}", user);
    }
    Ok(())
}

User { name: "Bob", age: 25, insert_ts: 2026-08-04T10:05:00, comment: None, is_test: false }
User { name: "Charlie", age: 30, insert_ts: 2026-08-04T10:10:00, comment: Some("backend developer"), is_test: true }
User { name: "Eve", age: 22, insert_ts: 2026-08-04T10:20:00, comment: None, is_test: true }
User { name: "Frank", age: 40, insert_ts: 2026-08-04T10:25:00, comment: Some("admin user"), is_test: false }
User { name: "Heidi", age: 27, insert_ts: 2026-08-04T10:35:00, comment: Some("qa tester"), is_test: false }
User { name: "Ivan", age: 35, insert_ts: 2026-08-04T10:40:00, comment: None, is_test: true }
User { name: "Judy", age: 21, insert_ts: 2026-08-04T10:45:00, comment: Some("frontend developer"), is_test: false }
User { name: "Mallory", age: 28, insert_ts: 2026-08-04T10:50:00, comment: Some("security test user"), is_test: true }
User { name: "Olivia", age: 24, insert_ts: 2026-08-04T11:00:00, comment: Some("product manager"), is_test: true }
User { name: "Peggy", age: 32, insert_ts: 2026-08-04T11:05:00, comment: Some("data engineer"), is_test: false }
User { name: "Trent", age: 45, insert_ts: 2026-08-04T11:10:00, comment: Some("senior developer"), is_test: true }

fetch_one

返回struct, 查不到报错

use sqlx::{
    FromRow,
    postgres::{PgConnectOptions, PgPoolOptions}
};
use chrono::NaiveDateTime;

#[derive(Debug, FromRow)]
struct User {
    name: String,
    age: i32,
    insert_ts: NaiveDateTime,
    comment: Option<String>,
    is_test: bool
}

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let options = PgConnectOptions::new()
        .host("127.0.0.1")
        .port(15432)
        .username("postgres")
        .password("123456")
        .database("testdb");

    let pool = PgPoolOptions::new()
        .max_connections(5)
        .connect_with(options) // 使用PgConnectOptions
        .await?;
    

    let user = sqlx::query_as::<_, User>("select name, age, insert_ts, comment, is_test from users where name=$1")
        .bind("Bob")
        .fetch_one(&pool)
        .await.unwrap();

    println!("{:?}", user);
    Ok(())
}

User { name: "Bob", age: 25, insert_ts: 2026-08-04T10:05:00, comment: None, is_test: false }

fetch_optional

返回Option<struct>

use sqlx::{
    FromRow,
    postgres::{PgConnectOptions, PgPoolOptions}
};
use chrono::NaiveDateTime;

#[derive(Debug, FromRow)]
struct User {
    name: String,
    age: i32,
    insert_ts: NaiveDateTime,
    comment: Option<String>,
    is_test: bool
}

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let options = PgConnectOptions::new()
        .host("127.0.0.1")
        .port(15432)
        .username("postgres")
        .password("123456")
        .database("testdb");

    let pool = PgPoolOptions::new()
        .max_connections(5)
        .connect_with(options) // 使用PgConnectOptions
        .await?;
    

    // 返回Option<User>
    let user = sqlx::query_as::<_, User>("select name, age, insert_ts, comment, is_test from users where name=$1")
        .bind("Bob")
        .fetch_optional(&pool)
        .await.unwrap();

    println!("{:?}", user);
    Ok(())
}

Some(User { name: "Bob", age: 25, insert_ts: 2026-08-04T10:05:00, comment: None, is_test: false })

fetch

流式读取结果，不一次性把所有行加载到内存里

需要安装futures-util
使用next -> futures_util::StreamExt
使用try_next -> use futures_util::TryStreamExt;
对比fetch_all
- fetch_all()会把所有内容都放进内。fetch()一条一条处理，内存里只保留当前行和少量缓冲
- fetch_all()必须等所有数据读完。fetch() 是读到一条就处理一条
- fetch_all()，你会先把所有行都查回来，再筛选，浪费资源。fetch()可以提前break，使用drop(stream);释放连接
注意
- 流没消费完，会占用一个数据库连接
- 循环里不要做很慢的阻塞操作
- 小结果集没必要用fetch(), 几万、几十万、几百万才用

use sqlx::{
    FromRow,
    postgres::{PgConnectOptions, PgPoolOptions}
};
use chrono::NaiveDateTime;
// 引入futures_util
use futures_util::StreamExt;

#[derive(Debug, FromRow)]
struct User {
    name: String,
    age: i32,
    insert_ts: NaiveDateTime,
    comment: Option<String>,
    is_test: bool
}

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let options = PgConnectOptions::new()
        .host("127.0.0.1")
        .port(15432)
        .username("postgres")
        .password("123456")
        .database("testdb");

    let pool = PgPoolOptions::new()
        .max_connections(5)
        .connect_with(options)
        .await?;
    
    // 修改为可变
    let mut stream = sqlx::query_as::<_, User>("select name, age, insert_ts, comment, is_test from users where age > $1")
        .bind(20i32)
        .fetch(&pool);

    // 使用next
    while let Some(result) = stream.next().await {
        match result {
            Ok(user) => {
                println!("{:?}", user);
            },
            Err(err) => {
                eprintln!("query error: {:?}", err);
                return Err(err);
            }
        }
    }

    // 使用try_next，需要引入use futures_util::TryStreamExt;
    // while let Some(user) = stream.try_next().await? {
    //     println!("{:?}", user);
    // }

    // 提前释放连接
    // drop(stream);
    Ok(())
}

User { name: "Bob", age: 25, insert_ts: 2026-08-04T10:05:00, comment: None, is_test: false }
User { name: "Charlie", age: 30, insert_ts: 2026-08-04T10:10:00, comment: Some("backend developer"), is_test: true }
User { name: "Eve", age: 22, insert_ts: 2026-08-04T10:20:00, comment: None, is_test: true }
User { name: "Frank", age: 40, insert_ts: 2026-08-04T10:25:00, comment: Some("admin user"), is_test: false }
User { name: "Heidi", age: 27, insert_ts: 2026-08-04T10:35:00, comment: Some("qa tester"), is_test: false }
User { name: "Ivan", age: 35, insert_ts: 2026-08-04T10:40:00, comment: None, is_test: true }
User { name: "Judy", age: 21, insert_ts: 2026-08-04T10:45:00, comment: Some("frontend developer"), is_test: false }
User { name: "Mallory", age: 28, insert_ts: 2026-08-04T10:50:00, comment: Some("security test user"), is_test: true }
User { name: "Olivia", age: 24, insert_ts: 2026-08-04T11:00:00, comment: Some("product manager"), is_test: true }
User { name: "Peggy", age: 32, insert_ts: 2026-08-04T11:05:00, comment: Some("data engineer"), is_test: false }
User { name: "Trent", age: 45, insert_ts: 2026-08-04T11:10:00, comment: Some("senior developer"), is_test: true }

query_scalar

query_scalar + fetch_one: 必须取一行第一列T
query_scalar + fetch_optional: 取零或一行第一列Option<T>
query_scalar + fetch_all: 取所有行第一列Vec<T>
query_scalar + fetch: 流式取每行第一列 Stream<Item = Result<T, Error>>

query_scalar + fetch_one

查询不到报错

use sqlx::{postgres::{PgConnectOptions, PgPoolOptions}};

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let options = PgConnectOptions::new()
        .host("127.0.0.1")
        .port(15432)
        .username("postgres")
        .password("123456")
        .database("testdb");

    let pool = PgPoolOptions::new()
        .max_connections(5)
        .connect_with(options) // 使用PgConnectOptions
        .await?;
    
    let age: i32 = sqlx::query_scalar("select age from users where name=$1")
        .bind("Bob")
        .fetch_one(&pool).await.unwrap();

    println!("age: {}", age);
    Ok(())
}

age: 25

query_scalar + fetch_optional

use sqlx::{postgres::{PgConnectOptions, PgPoolOptions}};
use futures_util::TryStreamExt;

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let options = PgConnectOptions::new()
        .host("127.0.0.1")
        .port(15432)
        .username("postgres")
        .password("123456")
        .database("testdb");

    let pool = PgPoolOptions::new()
        .max_connections(5)
        .connect_with(options) // 使用PgConnectOptions
        .await?;
    
    let age: Option<i32> = sqlx::query_scalar("select age from users where name=$1")
        .bind("TomAAA")
        .fetch_optional(&pool).await.unwrap();

    println!("age: {:?}", age);
    Ok(())
}

age: None

query_scalar + fetch_all

use sqlx::{postgres::{PgConnectOptions, PgPoolOptions}};

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let options = PgConnectOptions::new()
        .host("127.0.0.1")
        .port(15432)
        .username("postgres")
        .password("123456")
        .database("testdb");

    let pool = PgPoolOptions::new()
        .max_connections(5)
        .connect_with(options) // 使用PgConnectOptions
        .await?;
    
    let names: Vec<String> = sqlx::query_scalar("select name from users where age > $1")
        .bind(18i32)
        .fetch_all(&pool).await.unwrap();

    println!("names: {:?}", names);
    Ok(())
}

names: ["Bob", "Charlie", "Eve", "Frank", "Grace", "Heidi", "Ivan", "Judy", "Mallory", "Olivia", "Peggy", "Trent"]

fetch

use sqlx::{postgres::{PgConnectOptions, PgPoolOptions}};
use futures_util::StreamExt;

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let options = PgConnectOptions::new()
        .host("127.0.0.1")
        .port(15432)
        .username("postgres")
        .password("123456")
        .database("testdb");

    let pool = PgPoolOptions::new()
        .max_connections(5)
        .connect_with(options) // 使用PgConnectOptions
        .await?;
    
    // 改为可变， 需要指定类型sqlx::query_scalar::<sqlx::Postgres, T>
    let mut stream = sqlx::query_scalar::<sqlx::Postgres, String>("select name from users where age > $1")
        .bind(18i32)
        .fetch(&pool);


    while let Some(result) = stream.next().await {
        match result {
            Ok(name) => {
                println!("name: {}", name)
            },
            Err(err) => {
                eprintln!("query error: {:?}", err);
                return Err(err);
            }
        }
    }

    Ok(())
}

name: Bob
name: Charlie
name: Eve
name: Frank
name: Grace
name: Heidi
name: Ivan
name: Judy
name: Mallory
name: Olivia
name: Peggy
name: Trent

动态拼接

query_with
query_as_with
query_scalar_with

query_with

动态拼接sql，你自己准备好完整 SQL + arguments。

use sqlx::{
    Arguments,
    Row,
    postgres::{PgArguments, PgConnectOptions, PgPoolOptions},
};

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let options = PgConnectOptions::new()
        .host("127.0.0.1")
        .port(15432)
        .username("postgres")
        .password("123456")
        .database("testdb");

    let pool = PgPoolOptions::new()
        .max_connections(5)
        .connect_with(options)
        .await?;

    let mut sql = String::from(
        "select name, age from users where is_test = false"
    );

    let mut args = PgArguments::default();
    let mut idx = 1;

    let min_age: Option<i32> = Some(18);

    if let Some(min_age) = min_age {
        sql.push_str(&format!(" and age >= ${}", idx));
        args.add(min_age).map_err(sqlx::Error::Encode)?;
        idx += 1;
    }

    let name: Option<String> = None;

    if let Some(name) = name {
        sql.push_str(&format!(" and name = ${}", idx));
        args.add(name).map_err(sqlx::Error::Encode)?;
        idx += 1;
    }

    let rows = sqlx::query_with(sqlx::AssertSqlSafe(sql), args)
        .fetch_all(&pool)
        .await?;

    for row in rows {
        let name: String = row.try_get("name")?;
        let age: i32 = row.try_get("age")?;

        println!("{}|{}", name, age);
    }

    Ok(())
}

Bob|25
Frank|40
Heidi|27
Judy|21
Peggy|32

QueryBuilder

动态拼接sql， sqlx 帮你一边拼 SQL，一边安全地绑定参数。

在 sqlx 0.9 里，动态 SQL 更推荐用 QueryBuilder

场景	推荐
动态 where 条件	`QueryBuilder`
动态 insert 多行	`QueryBuilder`
动态 in 列表	`QueryBuilder`

build区别
- query_with -> qb.build()
- query_as_with -> qb.build_query_as::<T>()
- query_scalar_with -> qb.build_query_scalar::<T>()
数据插入
- qb.push(): 拼接sql
- qb.push_bind(): 绑定参数
- qb.separated(): 拼接逗号分隔列表，比如 IN (...)
- qb.push_values(): 批量 insert values (...)

拼接

// 相当于select name, age from users where 1 = 1 and age >= $1 and name = $2
let mut qb = QueryBuilder::<Postgres>::new(
    "select name, age from users where 1 = 1"
);

qb.push(" and age >= ");
qb.push_bind(18_i32);

qb.push(" and name = ");
qb.push_bind("Alice");

use sqlx::{
    Row,
    Postgres, // 需要引入
    QueryBuilder, // 需要引入
    postgres::{PgConnectOptions, PgPoolOptions},
};

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let options = PgConnectOptions::new()
        .host("127.0.0.1")
        .port(15432)
        .username("postgres")
        .password("123456")
        .database("testdb");

    let pool = PgPoolOptions::new()
        .max_connections(5)
        .connect_with(options)
        .await?;

    let min_age: Option<i32> = Some(18);

    // 创建qb
    let mut qb = QueryBuilder::<Postgres>::new(
        "select name, age from users where is_test = false"
    );

    // 添加参数
    if let Some(min_age) = min_age {
        qb.push(" and age >= ");
        qb.push_bind(min_age);
    }

    // 查询
    let rows = qb
        .build()
        .fetch_all(&pool)
        .await?;

    for row in rows {
        let name: String = row.try_get("name")?;
        let age: i32 = row.try_get("age")?;

        println!("{}|{}", name, age);
    }

    Ok(())
}

Bob|25
Frank|40
Heidi|27
Judy|21
Peggy|32

动态IN列表

use sqlx::{
    Row,
    Postgres, // 需要引入
    QueryBuilder, // 需要引入
    postgres::{PgConnectOptions, PgPoolOptions},
};

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let options = PgConnectOptions::new()
        .host("127.0.0.1")
        .port(15432)
        .username("postgres")
        .password("123456")
        .database("testdb");

    let pool = PgPoolOptions::new()
        .max_connections(5)
        .connect_with(options)
        .await?;

    let names = vec!["Bob", "Judy", "Trent"];

    let mut qb = QueryBuilder::<Postgres>::new(
        "select name, age from users where name in ("
    );
    // 需要注意names不能为空
    let mut separated = qb.separated(", ");
    for name in names {
        separated.push_bind(name);
    }

    separated.push_unseparated(")");


    let rows = qb
        .build()
        .fetch_all(&pool)
        .await?;


    for row in rows {
        let name: String = row.try_get("name")?;
        let age: i32 = row.try_get("age")?;

        println!("{}|{}", name, age);
    }

    Ok(())
}

Bob|25
Judy|21
Trent|45

批量插入

use sqlx::{
    Postgres,     // 需要引入
    QueryBuilder, // 需要引入
    postgres::{PgConnectOptions, PgPoolOptions},
};

use chrono::NaiveDateTime;

struct User {
    name: String,
    age: i32,
    insert_ts: NaiveDateTime,
    comment: Option<String>,
    is_test: bool,
}

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let options = PgConnectOptions::new()
        .host("127.0.0.1")
        .port(15432)
        .username("postgres")
        .password("123456")
        .database("testdb");

    let pool = PgPoolOptions::new()
        .max_connections(5)
        .connect_with(options)
        .await?;

    // 设置插入数据
    let users = vec![
        User {
            name: "Alice1".to_string(),
            age: 18,
            insert_ts: NaiveDateTime::parse_from_str("2026-08-04 10:00:00", "%Y-%m-%d %H:%M:%S")
                .unwrap(),
            comment: Some("first user".to_string()),
            is_test: true,
        },
        User {
            name: "Bob1".to_string(),
            age: 25,
            insert_ts: NaiveDateTime::parse_from_str("2026-08-04 10:05:00", "%Y-%m-%d %H:%M:%S")
                .unwrap(),
            comment: None,
            is_test: false,
        },
    ];

    // 创建qb（不需要写values）
    let mut qb = QueryBuilder::<Postgres>::new(
        "insert into users (name, age, insert_ts, comment, is_test) ",
    );

    // 批量插入
    // insert into users (name, age, insert_ts, comment, is_test) values ($1, $2, $3, $4, $5), ($6, $7, $8, $9, $10)
    // users.iter()，如果不是Copy类型的，需要使用&
    qb.push_values(users.iter(), |mut b, user| {
        b.push_bind(&user.name)
            .push_bind(user.age)
            .push_bind(user.insert_ts)
            .push_bind(&user.comment)
            .push_bind(user.is_test);
    });

    let result = qb.build().execute(&pool).await?;
    println!("inserted rows: {}", result.rows_affected());

    Ok(())
}

inserted rows: 2

宏查询

需要设置

# 安装sqlx-cli
cargo install sqlx-cli --no-default-features --features postgres
# 设置DATABASE_URL
export DATABASE_URL=postgres://postgres:123456@127.0.0.1:15432/testdb
# 项目根目录执行
cargo sqlx prepare # 会生成一个.sqlx/xxx.json

# 消除set `DATABASE_URL` to use query macros online, or run `cargo sqlx prepare` to update the query cache错误

# 执行
SQLX_OFFLINE=true cargo run

宏	SQL 来源	返回内容	典型最终结果
`query!`	Rust 字符串	匿名结构体	`Record` / `Vec<Record>`
`query_file!`	`.sql` 文件	匿名结构体	`Record` / `Vec<Record>`
`query_as!`	Rust 字符串	自定义 struct	`User` / `Vec<User>`
`query_file_as!`	`.sql` 文件	自定义 struct	`User` / `Vec<User>`
`query_scalar!`	Rust 字符串	单列值	`T` / `Option<T>` / `Vec<T>`
`query_file_scalar!`	`.sql` 文件	单列值	`T` / `Option<T>` / `Vec<T>`

query!查询

会返回一个匿名结构体

fetch_all

use sqlx::{
    postgres::{PgConnectOptions, PgPoolOptions}
};

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let database_url = std::env::var("DATABASE_URL").unwrap();
    let options: PgConnectOptions = database_url.parse().unwrap();

    let pool = PgPoolOptions::new()
        .max_connections(5)
        .connect_with(options)
        .await?;

    let users = sqlx::query!("SELECT name, age  FROM users WHERE age > $1", 30i32)
        .fetch_all(&pool)
        .await?;
    for user in users {
        println!("{} {}", user.name, user.age);
    }
    Ok(())
}

Frank 40
Ivan 35
Peggy 32
Trent 45

fetch_one

use sqlx::{
    postgres::{PgConnectOptions, PgPoolOptions}
};

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let database_url = std::env::var("DATABASE_URL").unwrap();
    let options: PgConnectOptions = database_url.parse().unwrap();

    let pool = PgPoolOptions::new()
        .max_connections(5)
        .connect_with(options)
        .await?;

    let user = sqlx::query!("SELECT name, age  FROM users WHERE name=$1", "Bob")
        .fetch_one(&pool)
        .await?;
    println!("{} {}", user.name, user.age);
    Ok(())
}

Bob 25

fetch_optional

sqlx::query("SQL")
    .bind(...)
    .execute(&pool) // 可以设置fetch_one/fetch_optional/fetch_all/fetch
    .await?;

use sqlx::{
    postgres::{PgConnectOptions, PgPoolOptions}
};


#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let database_url = std::env::var("DATABASE_URL").unwrap();
    let options: PgConnectOptions = database_url.parse().unwrap();

    let pool = PgPoolOptions::new()
        .max_connections(5)
        .connect_with(options)
        .await?;

    let user = sqlx::query!("SELECT name, age  FROM users WHERE name=$1", "Bob")
        .fetch_optional(&pool)
        .await?;
    println!("{:?}", user);
    Ok(())
}

Some(Record { name: "Bob", age: 25 })

fetch

use futures_util::StreamExt;
use sqlx::postgres::{PgConnectOptions, PgPoolOptions};

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let database_url = std::env::var("DATABASE_URL").unwrap();
    let options: PgConnectOptions = database_url.parse().unwrap();

    let pool = PgPoolOptions::new()
        .max_connections(5)
        .connect_with(options)
        .await?;

    let mut stream =
        sqlx::query!("SELECT name, age  FROM users WHERE age > $1", 30i32).fetch(&pool);
    while let Some(result) = stream.next().await {
        match result {
            Ok(user) => {
                println!("{} {}", user.name, user.age);
            }
            Err(err) => {
                eprintln!("query error: {:?}", err);
                return Err(err);
            }
        }
    }
    Ok(())
}

Frank 40
Ivan 35
Peggy 32
Trent 45

query_file!查询

用法和query!查询一样，SQL语句从文件中导入

-- 在项目下创建test_sql/a.sql
SELECT name, age  FROM users WHERE age > $1

use sqlx::{
    postgres::{PgConnectOptions, PgPoolOptions}
};

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let database_url = std::env::var("DATABASE_URL").unwrap();
    let options: PgConnectOptions = database_url.parse().unwrap();

    let pool = PgPoolOptions::new()
        .max_connections(5)
        .connect_with(options)
        .await?;

    // 加载a.sql文件
    let users = sqlx::query_file!("test_sql/a.sql", 30i32)
        .fetch_all(&pool)
        .await?;
    for user in users {
        println!("{} {}", user.name, user.age);
    }
    Ok(())
}

Frank 40
Ivan 35
Peggy 32
Trent 45

query_as!查询

sqlx::query_as!(<struct>, <sql>, <params>, <params>, ...)

struct不需要sqlx::FromRow

fetch_all

use sqlx::postgres::{PgConnectOptions, PgPoolOptions};
use chrono::NaiveDateTime;

#[derive(Debug)]
struct User {
    name: String,
    age: i32,
    insert_ts: NaiveDateTime,
    comment: Option<String>,
    is_test: bool
}

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let database_url = std::env::var("DATABASE_URL").unwrap();
    let options: PgConnectOptions = database_url.parse().unwrap();

    let pool = PgPoolOptions::new()
        .max_connections(5)
        .connect_with(options) // 使用PgConnectOptions
        .await?;
    

    let users = sqlx::query_as!(User, "select name, age, insert_ts, comment, is_test from users where age > $1 and is_test=$2", 20i32, false)
        .fetch_all(&pool)
        .await.unwrap();

    for user in users {
        println!("{:?}", user);
    }
    Ok(())
}

User { name: "Bob", age: 25, insert_ts: 2026-08-04T10:05:00, comment: None, is_test: false }
User { name: "Frank", age: 40, insert_ts: 2026-08-04T10:25:00, comment: Some("admin user"), is_test: false }
User { name: "Heidi", age: 27, insert_ts: 2026-08-04T10:35:00, comment: Some("qa tester"), is_test: false }
User { name: "Judy", age: 21, insert_ts: 2026-08-04T10:45:00, comment: Some("frontend developer"), is_test: false }
User { name: "Peggy", age: 32, insert_ts: 2026-08-04T11:05:00, comment: Some("data engineer"), is_test: false }
User { name: "Bob1", age: 25, insert_ts: 2026-08-04T10:05:00, comment: None, is_test: false }

fetch_one

use sqlx::postgres::{PgConnectOptions, PgPoolOptions};
use chrono::NaiveDateTime;

#[derive(Debug)]
struct User {
    name: String,
    age: i32,
    insert_ts: NaiveDateTime,
    comment: Option<String>,
    is_test: bool
}

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let database_url = std::env::var("DATABASE_URL").unwrap();
    let options: PgConnectOptions = database_url.parse().unwrap();

    let pool = PgPoolOptions::new()
        .max_connections(5)
        .connect_with(options) // 使用PgConnectOptions
        .await?;
    

    let user = sqlx::query_as!(User, "select name, age, insert_ts, comment, is_test from users where name=$1", "Bob")
        .fetch_one(&pool)
        .await.unwrap();

    println!("{:?}", user);
    Ok(())
}

User { name: "Bob", age: 25, insert_ts: 2026-08-04T10:05:00, comment: None, is_test: false }

fetch_optional

use sqlx::postgres::{PgConnectOptions, PgPoolOptions};
use chrono::NaiveDateTime;

#[derive(Debug)]
struct User {
    name: String,
    age: i32,
    insert_ts: NaiveDateTime,
    comment: Option<String>,
    is_test: bool
}

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let database_url = std::env::var("DATABASE_URL").unwrap();
    let options: PgConnectOptions = database_url.parse().unwrap();

    let pool = PgPoolOptions::new()
        .max_connections(5)
        .connect_with(options) // 使用PgConnectOptions
        .await?;
    

    let user = sqlx::query_as!(User, "select name, age, insert_ts, comment, is_test from users where name=$1", "Bob")
        .fetch_optional(&pool)
        .await.unwrap();

    println!("{:?}", user);
    Ok(())
}

Some(User { name: "Bob", age: 25, insert_ts: 2026-08-04T10:05:00, comment: None, is_test: false })

fetch

use sqlx::postgres::{PgConnectOptions, PgPoolOptions};
use chrono::NaiveDateTime;
use futures_util::StreamExt;

#[derive(Debug)]
struct User {
    name: String,
    age: i32,
    insert_ts: NaiveDateTime,
    comment: Option<String>,
    is_test: bool
}

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let database_url = std::env::var("DATABASE_URL").unwrap();
    let options: PgConnectOptions = database_url.parse().unwrap();

    let pool = PgPoolOptions::new()
        .max_connections(5)
        .connect_with(options) // 使用PgConnectOptions
        .await?;
    

    let mut stream = sqlx::query_as!(User, "select name, age, insert_ts, comment, is_test from users where age>$1", 30i32)
        .fetch(&pool);


    while let Some(result) = stream.next().await {
        match result {
            Ok(user) => {
                println!("{:?}", user);
            }
            Err(err) => {
                eprintln!("query error: {:?}", err);
                return Err(err);
            }
        }
    }
    Ok(())
}

User { name: "Frank", age: 40, insert_ts: 2026-08-04T10:25:00, comment: Some("admin user"), is_test: false }
User { name: "Ivan", age: 35, insert_ts: 2026-08-04T10:40:00, comment: None, is_test: true }
User { name: "Peggy", age: 32, insert_ts: 2026-08-04T11:05:00, comment: Some("data engineer"), is_test: false }
User { name: "Trent", age: 45, insert_ts: 2026-08-04T11:10:00, comment: Some("senior developer"), is_test: true }

query_file_as!查询

用法和query_as!查询一样，SQL语句从文件中导入

select name, age, insert_ts, comment, is_test from users where age > $1 and is_test=$2

use sqlx::postgres::{PgConnectOptions, PgPoolOptions};
use chrono::NaiveDateTime;

#[derive(Debug)]
struct User {
    name: String,
    age: i32,
    insert_ts: NaiveDateTime,
    comment: Option<String>,
    is_test: bool
}

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let database_url = std::env::var("DATABASE_URL").unwrap();
    let options: PgConnectOptions = database_url.parse().unwrap();

    let pool = PgPoolOptions::new()
        .max_connections(5)
        .connect_with(options) // 使用PgConnectOptions
        .await?;
    

    let users = sqlx::query_file_as!(User, "test_sql/a.sql", 20i32, false)
        .fetch_all(&pool)
        .await.unwrap();

    for user in users {
        println!("{:?}", user);
    }
    Ok(())
}

User { name: "Bob", age: 25, insert_ts: 2026-08-04T10:05:00, comment: None, is_test: false }
User { name: "Frank", age: 40, insert_ts: 2026-08-04T10:25:00, comment: Some("admin user"), is_test: false }
User { name: "Heidi", age: 27, insert_ts: 2026-08-04T10:35:00, comment: Some("qa tester"), is_test: false }
User { name: "Judy", age: 21, insert_ts: 2026-08-04T10:45:00, comment: Some("frontend developer"), is_test: false }
User { name: "Peggy", age: 32, insert_ts: 2026-08-04T11:05:00, comment: Some("data engineer"), is_test: false }
User { name: "Bob1", age: 25, insert_ts: 2026-08-04T10:05:00, comment: None, is_test: false }

query_scalar!查询

fetch_one

use sqlx::postgres::{PgConnectOptions, PgPoolOptions};

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let database_url = std::env::var("DATABASE_URL").unwrap();
    let options: PgConnectOptions = database_url.parse().unwrap();

    let pool = PgPoolOptions::new()
        .max_connections(5)
        .connect_with(options) // 使用PgConnectOptions
        .await?;
    
    let num = sqlx::query_scalar!("select Count(1) from users where age>$1", 20i32)
        .fetch_one(&pool)
        .await.unwrap();

    println!("num: {:?}", num);

    let name = sqlx::query_scalar!("select name from users where name=$1", "Bob")
        .fetch_one(&pool)
        .await.unwrap();

    println!("name: {}", name);
    Ok(())
}

num: Some(12)
name: Bob

fetch_optional

use sqlx::postgres::{PgConnectOptions, PgPoolOptions};

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let database_url = std::env::var("DATABASE_URL").unwrap();
    let options: PgConnectOptions = database_url.parse().unwrap();

    let pool = PgPoolOptions::new()
        .max_connections(5)
        .connect_with(options) // 使用PgConnectOptions
        .await?;
    
    let num = sqlx::query_scalar!("select Count(1) from users where age>$1", 20i32)
        .fetch_optional(&pool)
        .await.unwrap();

    println!("num: {:?}", num);

    let name = sqlx::query_scalar!("select name from users where name=$1", "Bob")
        .fetch_optional(&pool)
        .await.unwrap();

    println!("name: {:?}", name);
    Ok(())
}

num: Some(Some(12))
name: Some("Bob")

fetch_all

use sqlx::postgres::{PgConnectOptions, PgPoolOptions};

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let database_url = std::env::var("DATABASE_URL").unwrap();
    let options: PgConnectOptions = database_url.parse().unwrap();

    let pool = PgPoolOptions::new()
        .max_connections(5)
        .connect_with(options) // 使用PgConnectOptions
        .await?;
    
    let names = sqlx::query_scalar!("select name from users where age>$1", 20i32)
        .fetch_all(&pool)
        .await.unwrap();

    println!("names: {:?}", names);
    Ok(())
}

names: ["Bob", "Charlie", "Eve", "Frank", "Heidi", "Ivan", "Judy", "Mallory", "Olivia", "Peggy", "Trent", "Bob1"]

fetch

use futures_util::StreamExt;
use sqlx::postgres::{PgConnectOptions, PgPoolOptions};

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let database_url = std::env::var("DATABASE_URL").unwrap();
    let options: PgConnectOptions = database_url.parse().unwrap();

    let pool = PgPoolOptions::new()
        .max_connections(5)
        .connect_with(options) // 使用PgConnectOptions
        .await?;

    let mut stream = sqlx::query_scalar!("select name from users where age>$1", 20i32).fetch(&pool);

    while let Some(result) = stream.next().await {
        match result {
            Ok(name) => {
                println!("name: {}", name);
            }
            Err(err) => {
                eprintln!("query error: {:?}", err);
                return Err(err);
            }
        }
    }
    Ok(())
}

name: Bob
name: Charlie
name: Eve
name: Frank
name: Heidi
name: Ivan
name: Judy
name: Mallory
name: Olivia
name: Peggy
name: Trent
name: Bob1

query_file_scalar!查询

用法和query_scalar!查询一样，SQL语句从文件中导入

-- test_sql/a.sql
select Count(1) from users where age>$1
-- test_sql/b.sql
select name from users where name=$1

use sqlx::postgres::{PgConnectOptions, PgPoolOptions};

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let database_url = std::env::var("DATABASE_URL").unwrap();
    let options: PgConnectOptions = database_url.parse().unwrap();

    let pool = PgPoolOptions::new()
        .max_connections(5)
        .connect_with(options) // 使用PgConnectOptions
        .await?;
    
    let num = sqlx::query_file_scalar!("test_sql/a.sql", 20i32)
        .fetch_one(&pool)
        .await.unwrap();

    println!("num: {:?}", num);

    let name = sqlx::query_file_scalar!("test_sql/b.sql", "Bob")
        .fetch_one(&pool)
        .await.unwrap();

    println!("name: {}", name);
    Ok(())
}

num: Some(12)
name: Bob

增删改

需要设置RETURNING才会返回增、删、改的值

SQL 类型	方法	说明
`INSERT / UPDATE / DELETE`	`execute`	只执行，不要返回值，可以看影响行数
`INSERT / UPDATE / DELETE ... RETURNING`	`fetch_one`	执行并返回一条，要求必须有一条结果
`INSERT / UPDATE / DELETE ... RETURNING`	`fetch_optional`	执行并返回零条或一条
`INSERT / UPDATE / DELETE ... RETURNING`	`fetch_all`	执行并一次性返回多条，结果是集合
`INSERT / UPDATE / DELETE ... RETURNING`	`fetch`	执行并流式返回多条，一行一行处理

-- RETURNING返回指定字段
INSERT INTO users (id, name, age) VALUES ($1, $2, $3) RETURNING id, name, age
-- RETURNING返回全部字段（不推荐）
INSERT INTO users (name, age) VALUES ($1, $2) RETURNING *

execute

插入、修改、删除通常用.execute(&pool).await?返回的是一个执行结果对象PgQueryResult
result.rows_affected()获取影响行数

不带RETURNING用法使用execute

新增

use sqlx::postgres::{PgConnectOptions, PgPoolOptions};
use chrono::NaiveDateTime;

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let options = PgConnectOptions::new()
        .host("127.0.0.1")
        .port(15432)
        .username("postgres")
        .password("123456")
        .database("testdb");

    let pool = PgPoolOptions::new()
        .max_connections(5)
        .connect_with(options) // 使用PgConnectOptions
        .await?;
    
    let result = sqlx::query("INSERT INTO users (name, age, insert_ts, comment, is_test) VALUES ($1, $2, $3, $4, $5)")
    .bind("Alice")
    .bind(18)
    .bind(NaiveDateTime::parse_from_str("2026-08-04 10:00:00", "%Y-%m-%d %H:%M:%S").unwrap())
    .bind(None::<String>)
    .bind(false)
    .execute(&pool)
    .await?;

    println!("affected rows: {}", result.rows_affected());
    
    Ok(())
}

affected rows: 1

删除

use sqlx::postgres::{PgConnectOptions, PgPoolOptions};

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let options = PgConnectOptions::new()
        .host("127.0.0.1")
        .port(15432)
        .username("postgres")
        .password("123456")
        .database("testdb");

    let pool = PgPoolOptions::new()
        .max_connections(5)
        .connect_with(options) // 使用PgConnectOptions
        .await?;
    
    let result = sqlx::query("delete from users where name in ($1, $2)")
        .bind("Alice1")
        .bind("Bob1")
        .execute(&pool)
        .await?;

    println!("affected rows: {}", result.rows_affected());
    
    Ok(())
}

affected rows: 2

修改

use sqlx::postgres::{PgConnectOptions, PgPoolOptions};

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let options = PgConnectOptions::new()
        .host("127.0.0.1")
        .port(15432)
        .username("postgres")
        .password("123456")
        .database("testdb");

    let pool = PgPoolOptions::new()
        .max_connections(5)
        .connect_with(options) // 使用PgConnectOptions
        .await?;
    
    let result = sqlx::query("update users set is_test=false where name in ($1, $2)")
        .bind("Alice1")
        .bind("Bob1")
        .execute(&pool)
        .await?;

    println!("affected rows: {}", result.rows_affected());
    
    Ok(())
}

affected rows: 0

fetch_one

可以获取插入那行信息
fetch_one/fetch_optional/fetch_all/fetch同理

新增

use sqlx::{
    Row,
    postgres::{PgConnectOptions, PgPoolOptions}
};
use chrono::NaiveDateTime;

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let options = PgConnectOptions::new()
        .host("127.0.0.1")
        .port(15432)
        .username("postgres")
        .password("123456")
        .database("testdb");

    let pool = PgPoolOptions::new()
        .max_connections(5)
        .connect_with(options) // 使用PgConnectOptions
        .await?;
    
    let row = sqlx::query("INSERT INTO users (name, age, insert_ts, comment, is_test) VALUES ($1, $2, $3, $4, $5) RETURNING name")
        .bind("Alice1")
        .bind(18)
        .bind(NaiveDateTime::parse_from_str("2026-08-04 10:00:00", "%Y-%m-%d %H:%M:%S").unwrap())
        .bind(None::<String>)
        .bind(false)
        .fetch_one(&pool)
        .await?;

    let name: String = row.get("name");

    println!("name: {}", name);
    
    Ok(())
}

修改

use sqlx::{
    Row,
    postgres::{PgConnectOptions, PgPoolOptions}
};
use chrono::NaiveDateTime;

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let options = PgConnectOptions::new()
        .host("127.0.0.1")
        .port(15432)
        .username("postgres")
        .password("123456")
        .database("testdb");

    let pool = PgPoolOptions::new()
        .max_connections(5)
        .connect_with(options) // 使用PgConnectOptions
        .await?;
    
    let row = sqlx::query("update users set is_test=true where name=$1 RETURNING name, age, insert_ts")
        .bind("Alice1")
        .fetch_one(&pool)
        .await?;

    let name: String = row.get("name");
    let age: i32 = row.get("age");
    let insert_ts: NaiveDateTime = row.get("insert_ts");

    println!("name: {}", name);
    println!("age: {}", age);
    println!("insert_ts: {:?}", insert_ts);
    
    Ok(())
}

name: Alice1
age: 18
insert_ts: 2026-08-04T10:00:00

删除

use sqlx::{
    Row,
    postgres::{PgConnectOptions, PgPoolOptions}
};
use chrono::NaiveDateTime;

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let options = PgConnectOptions::new()
        .host("127.0.0.1")
        .port(15432)
        .username("postgres")
        .password("123456")
        .database("testdb");

    let pool = PgPoolOptions::new()
        .max_connections(5)
        .connect_with(options) // 使用PgConnectOptions
        .await?;
    
    let row = sqlx::query("delete from users where name=$1 RETURNING name, age, insert_ts, is_test")
        .bind("Alice1")
        .fetch_one(&pool)
        .await?;

    let name: String = row.get("name");
    let age: i32 = row.get("age");
    let insert_ts: NaiveDateTime = row.get("insert_ts");
    let is_test: bool = row.get("is_test");

    println!("name: {}", name);
    println!("age: {}", age);
    println!("insert_ts: {:?}", insert_ts);
    println!("is_test: {:?}", is_test);
    
    Ok(())
}

name: Alice1
age: 18
insert_ts: 2026-08-04T10:00:00
is_test: true

fetch_optional

新增

use sqlx::postgres::{PgConnectOptions, PgPoolOptions};
use chrono::NaiveDateTime;

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let options = PgConnectOptions::new()
        .host("127.0.0.1")
        .port(15432)
        .username("postgres")
        .password("123456")
        .database("testdb");

    let pool = PgPoolOptions::new()
        .max_connections(5)
        .connect_with(options) // 使用PgConnectOptions
        .await?;
    
    let row = sqlx::query("INSERT INTO users (name, age, insert_ts, comment, is_test) VALUES ($1, $2, $3, $4, $5) RETURNING name")
        .bind("Alice1")
        .bind(18)
        .bind(NaiveDateTime::parse_from_str("2026-08-04 10:00:00", "%Y-%m-%d %H:%M:%S").unwrap())
        .bind(None::<String>)
        .bind(false)
        .fetch_optional(&pool)
        .await?;


    println!("row: {:?}", row);
    
    Ok(())
}

row: Some(sqlx_postgres::row::PgRow {"name": "Alice1"})

修改

use sqlx::postgres::{PgConnectOptions, PgPoolOptions};
use chrono::NaiveDateTime;

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let options = PgConnectOptions::new()
        .host("127.0.0.1")
        .port(15432)
        .username("postgres")
        .password("123456")
        .database("testdb");

    let pool = PgPoolOptions::new()
        .max_connections(5)
        .connect_with(options) // 使用PgConnectOptions
        .await?;
    
    let row = sqlx::query("update users set is_test=true where name=$1 RETURNING name, age, insert_ts")
        .bind("Alice1")
        .fetch_optional(&pool)
        .await?;


    println!("row: {:?}", row);
    
    Ok(())
}

row: Some(sqlx_postgres::row::PgRow {"name": "Alice1", "age": 18, "insert_ts": 2026-08-04T10:00:00})

删除

use sqlx::postgres::{PgConnectOptions, PgPoolOptions};
use chrono::NaiveDateTime;

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let options = PgConnectOptions::new()
        .host("127.0.0.1")
        .port(15432)
        .username("postgres")
        .password("123456")
        .database("testdb");

    let pool = PgPoolOptions::new()
        .max_connections(5)
        .connect_with(options) // 使用PgConnectOptions
        .await?;
    
    let row = sqlx::query("delete from users where name=$1 RETURNING name, age, insert_ts, is_test")
        .bind("Alice1")
        .fetch_optional(&pool)
        .await?;


    println!("row: {:?}", row);
    
    Ok(())
}

row: Some(sqlx_postgres::row::PgRow {"name": "Alice1", "age": 18, "insert_ts": 2026-08-04T10:00:00, "is_test": true})

fetch_all

新增

use sqlx::{
    Row,
    postgres::{PgConnectOptions, PgPoolOptions}
};
use chrono::NaiveDateTime;

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let options = PgConnectOptions::new()
        .host("127.0.0.1")
        .port(15432)
        .username("postgres")
        .password("123456")
        .database("testdb");

    let pool = PgPoolOptions::new()
        .max_connections(5)
        .connect_with(options) // 使用PgConnectOptions
        .await?;
    
    let rows = sqlx::query("INSERT INTO users (name, age, insert_ts, comment, is_test) VALUES ($1, $2, $3, $4, $5), ($6, $7, $8, $9, $10) RETURNING name")
        .bind("Alice1")
        .bind(18)
        .bind(NaiveDateTime::parse_from_str("2026-08-04 10:00:00", "%Y-%m-%d %H:%M:%S").unwrap())
        .bind(None::<String>)
        .bind(false)
        .bind("Bob1")
        .bind(20)
        .bind(NaiveDateTime::parse_from_str("2026-08-04 12:11:00", "%Y-%m-%d %H:%M:%S").unwrap())
        .bind(None::<String>)
        .bind(true)
        .fetch_all(&pool)
        .await?;

    for row in rows {
        let name: String = row.get("name");
        println!("name: {}", name);
    }
    Ok(())
}

name: Alice1
name: Bob1

fetch

use sqlx::{
    Row,
    postgres::{PgConnectOptions, PgPoolOptions}
};
use chrono::NaiveDateTime;
use futures_util::StreamExt;

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let options = PgConnectOptions::new()
        .host("127.0.0.1")
        .port(15432)
        .username("postgres")
        .password("123456")
        .database("testdb");

    let pool = PgPoolOptions::new()
        .max_connections(5)
        .connect_with(options) // 使用PgConnectOptions
        .await?;
    
    let mut stream = sqlx::query("INSERT INTO users (name, age, insert_ts, comment, is_test) VALUES ($1, $2, $3, $4, $5), ($6, $7, $8, $9, $10) RETURNING name")
        .bind("Alice2")
        .bind(18)
        .bind(NaiveDateTime::parse_from_str("2026-08-04 10:00:00", "%Y-%m-%d %H:%M:%S").unwrap())
        .bind(None::<String>)
        .bind(false)
        .bind("Bob2")
        .bind(20)
        .bind(NaiveDateTime::parse_from_str("2026-08-04 12:11:00", "%Y-%m-%d %H:%M:%S").unwrap())
        .bind(None::<String>)
        .bind(true)
        .fetch(&pool);

    while let Some(result) = stream.next().await {
        match result {
            Ok(row) => {
                let name: String = row.get("name");
                println!("name: {}", name);
            }
            Err(err) => {
                eprintln!("query error: {:?}", err);
                return Err(err);
            }
        }
    }
    Ok(())
}

name: Alice2
name: Bob2

query_as的用法

新增

use sqlx::{
    FromRow,
    postgres::{PgConnectOptions, PgPoolOptions}
};
use chrono::NaiveDateTime;

#[derive(Debug, FromRow)]
struct User {
    name: String,
    age: i32,
    insert_ts: NaiveDateTime,
    comment: Option<String>,
    is_test: bool,
}

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let options = PgConnectOptions::new()
        .host("127.0.0.1")
        .port(15432)
        .username("postgres")
        .password("123456")
        .database("testdb");

    let pool = PgPoolOptions::new()
        .max_connections(5)
        .connect_with(options) // 使用PgConnectOptions
        .await?;
    
    let users = sqlx::query_as::<_, User>("INSERT INTO users (name, age, insert_ts, comment, is_test) VALUES ($1, $2, $3, $4, $5), ($6, $7, $8, $9, $10) RETURNING name, age, insert_ts, comment, is_test")
        .bind("Alice1")
        .bind(18)
        .bind(NaiveDateTime::parse_from_str("2026-08-04 10:00:00", "%Y-%m-%d %H:%M:%S").unwrap())
        .bind(None::<String>)
        .bind(false)
        .bind("Bob1")
        .bind(20)
        .bind(NaiveDateTime::parse_from_str("2026-08-04 12:11:00", "%Y-%m-%d %H:%M:%S").unwrap())
        .bind(None::<String>)
        .bind(true)
        .fetch_all(&pool)
        .await?;

    for user in users {
        println!("user: {:?}", user);
    }
    Ok(())
}

user: User { name: "Alice1", age: 18, insert_ts: 2026-08-04T10:00:00, comment: None, is_test: false }
user: User { name: "Bob1", age: 20, insert_ts: 2026-08-04T12:11:00, comment: None, is_test: true }

修改

use sqlx::{
    FromRow,
    postgres::{PgConnectOptions, PgPoolOptions}
};
use chrono::NaiveDateTime;

#[derive(Debug, FromRow)]
struct User {
    name: String,
    age: i32,
    insert_ts: NaiveDateTime,
    comment: Option<String>,
    is_test: bool,
}

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let options = PgConnectOptions::new()
        .host("127.0.0.1")
        .port(15432)
        .username("postgres")
        .password("123456")
        .database("testdb");

    let pool = PgPoolOptions::new()
        .max_connections(5)
        .connect_with(options) // 使用PgConnectOptions
        .await?;
    
    let users = sqlx::query_as::<_, User>("update users set insert_ts=$1, is_test=true where name=$2 RETURNING name, age, insert_ts, comment, is_test")
        .bind(NaiveDateTime::parse_from_str("2026-08-04 15:30:00", "%Y-%m-%d %H:%M:%S").unwrap())
        .bind("Alice1")
        .fetch_all(&pool)
        .await?;

    for user in users {
        println!("user: {:?}", user);
    }
    Ok(())
}

user: User { name: "Alice1", age: 18, insert_ts: 2026-08-04T15:30:00, comment: None, is_test: true }

删除

use sqlx::{
    FromRow,
    postgres::{PgConnectOptions, PgPoolOptions}
};
use chrono::NaiveDateTime;

#[derive(Debug, FromRow)]
struct User {
    name: String,
    age: i32,
    insert_ts: NaiveDateTime,
    comment: Option<String>,
    is_test: bool,
}

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let options = PgConnectOptions::new()
        .host("127.0.0.1")
        .port(15432)
        .username("postgres")
        .password("123456")
        .database("testdb");

    let pool = PgPoolOptions::new()
        .max_connections(5)
        .connect_with(options) // 使用PgConnectOptions
        .await?;
    
    let users = sqlx::query_as::<_, User>("delete from users where name in ($1, $2) RETURNING name, age, insert_ts, comment, is_test")
        .bind("Alice1")
        .bind("Bob1")
        .fetch_all(&pool)
        .await?;

    for user in users {
        println!("user: {:?}", user);
    }
    Ok(())
}

user: User { name: "Alice1", age: 18, insert_ts: 2026-08-04T10:00:00, comment: None, is_test: false }
user: User { name: "Bob1", age: 20, insert_ts: 2026-08-04T12:11:00, comment: None, is_test: true }

posted @ 2026-08-11 14:37 lxd670 阅读(7) 评论(0) 收藏举报

刷新页面返回顶部