数据库:SQLite

你会学到什么

rusqlite 的 bundled 特性会一起编译 SQLite,无需系统安装。
用 execute 做写操作,用 prepare + query_map 做查询。
内存数据库 open_in_memory() 让数据库逻辑也能轻松单测。

最小示例

let conn = Connection::open_in_memory()?;
conn.execute("CREATE TABLE task (id INTEGER PRIMARY KEY, title TEXT NOT NULL)", [])?;
conn.execute("INSERT INTO task (title) VALUES (?1)", ["learn sql"])?;

运行代码

cd examples
cargo run -p rt_38_sqlite
cargo test -p rt_38_sqlite

依赖:

[dependencies]
rusqlite = { version = "0.32", features = ["bundled"] }

代码讲解

写操作用 execute,占位符 ?1、?2 防止 SQL 注入:

conn.execute("INSERT INTO task (title) VALUES (?1)", [title])?;
let id = conn.last_insert_rowid();

查询先 prepare 再 query_map,把每一行映射成结构体:

let mut stmt = conn.prepare("SELECT id, title, done FROM task ORDER BY id")?;
let rows = stmt.query_map([], |row| {
    Ok(Task {
        id: row.get(0)?,
        title: row.get(1)?,
        done: row.get::<_, i64>(2)? != 0,
    })
})?;
let tasks: Vec<Task> = rows.collect::<Result<_, _>>()?;

SQLite 没有原生布尔,用 INTEGER 存 0/1,读出时转换。open_in_memory() 建立临时库,测试互不影响。

常见错误

直接把用户输入拼进 SQL 字符串,造成注入风险:

let sql = format!("SELECT * FROM task WHERE title = '{title}'"); // ❌

永远用占位符 ?1 传参。

练习

加一个 delete_task(conn, id) 函数并测试。
加一个按 done 过滤的查询,只返回未完成任务。

小结

rusqlite + bundled SQLite 提供零依赖的本地数据库,占位符参数化查询是安全底线,内存库让数据层可测。

下一步

接下来进入进阶主题:宏、unsafe 与 FFI。

完整示例代码

下面是 examples/38_sqlite/ 的完整源码。无需 clone 仓库，直接在页面上阅读、复制、对照运行。

examples/38_sqlite/src/main.rs

//! 用 rusqlite(内置 SQLite)做基本的增查改。

use rusqlite::{Connection, Result};

#[derive(Debug, PartialEq)]
struct Task {
    id: i64,
    title: String,
    done: bool,
}

/// 建表。
fn setup(conn: &Connection) -> Result<()> {
    conn.execute(
        "CREATE TABLE task (
            id    INTEGER PRIMARY KEY,
            title TEXT NOT NULL,
            done  INTEGER NOT NULL DEFAULT 0
        )",
        [],
    )?;
    Ok(())
}

/// 插入一条任务,返回自增 id。
fn add_task(conn: &Connection, title: &str) -> Result<i64> {
    conn.execute("INSERT INTO task (title) VALUES (?1)", [title])?;
    Ok(conn.last_insert_rowid())
}

/// 把任务标记为完成。
fn complete(conn: &Connection, id: i64) -> Result<()> {
    conn.execute("UPDATE task SET done = 1 WHERE id = ?1", [id])?;
    Ok(())
}

/// 按 id 顺序列出所有任务。
fn list_tasks(conn: &Connection) -> Result<Vec<Task>> {
    let mut stmt = conn.prepare("SELECT id, title, done FROM task ORDER BY id")?;
    let rows = stmt.query_map([], |row| {
        Ok(Task {
            id: row.get(0)?,
            title: row.get(1)?,
            done: row.get::<_, i64>(2)? != 0,
        })
    })?;
    rows.collect()
}

fn main() -> Result<()> {
    // 内存数据库:进程结束即销毁,适合演示与测试。
    let conn = Connection::open_in_memory()?;
    setup(&conn)?;

    let first = add_task(&conn, "学习 SQLite")?;
    add_task(&conn, "写测试")?;
    complete(&conn, first)?;

    for task in list_tasks(&conn)? {
        println!("{task:?}");
    }
    Ok(())
}

#[cfg(test)]
mod tests {
    use super::*;

    fn seeded() -> Connection {
        let conn = Connection::open_in_memory().unwrap();
        setup(&conn).unwrap();
        conn
    }

    #[test]
    fn adds_and_lists() {
        let conn = seeded();
        add_task(&conn, "a").unwrap();
        add_task(&conn, "b").unwrap();
        let tasks = list_tasks(&conn).unwrap();
        assert_eq!(tasks.len(), 2);
        assert_eq!(tasks[0].title, "a");
        assert!(!tasks[0].done);
    }

    #[test]
    fn completes_task() {
        let conn = seeded();
        let id = add_task(&conn, "todo").unwrap();
        complete(&conn, id).unwrap();
        let tasks = list_tasks(&conn).unwrap();
        assert!(tasks[0].done);
    }
}

examples/38_sqlite/Cargo.toml

[package]
name = "rt_38_sqlite"
version.workspace = true
edition.workspace = true
publish.workspace = true

[dependencies]
rusqlite = { version = "0.32", features = ["bundled"] }