Rust sqlx包访问sqlite数据库

如果您正在钻研Rust并希望使用数据库，那么SQLx是一个极好的选择。在本教程中，我们将探索将SQLx与SQLite（一种轻量级嵌入式SQL数据库引擎）结合使用的基础知识。SQLx crate是一个异步纯Rust SQL crate，具有编译时检查查询的功能。然而，它不是一个ORM。我们将了解如何创建SQLite数据库，并使用SQLx对其执行SQL操作。读完你将对如何创建SQLite数据库、执行SQL操作和使用SQLx设置迁移有一个扎实的理解。

什么是sqlx

SQLx是一个易于使用的Rust异步SQL crate。以下是一些关键特性：

• 编译时检查查询：SQLx确保您的查询在编译时有效，从而减少运行时错误。
• 异步支持：它可以与异步运行时（如Async -std、tokio和actix）无缝协作。
• 跨平台：SQLx在任何支持Rust的地方编译。
• 连接池：内置连接池，用于高效的数据库访问。

您可以将SQLx用于各种数据库，包括PostgreSQL、MySQL、SQLite和MSSQL。

什么是SQLite

SQLite是一个无服务器的嵌入式SQL数据库引擎。它直接读取和写入普通磁盘文件，使其轻量级和高效。下面是关于SQLite的一些关键点：

• 紧凑：即使启用了所有功能，库的大小也可以小于750KiB。
• 跨平台：数据库文件格式可以在不同的系统上工作（例如，32位和64位）。
• 流行：SQLite被广泛用作应用程序文件格式，特别是在手机和平板电脑等边缘设备上

项目准备

创建项目 cargo new sqlite_demo ,然后增加相应依赖：

[package]
name = "sqlx-sqlite-basics-tutorial"
version = "0.1.0"
edition = "2021"
# See more keys and their definitions at https://doc.rust-lang.org/cargo/reference/manifest.html
[dependencies]
sqlx = { version = "0.6.2", features = ["runtime-tokio-native-tls", "sqlite"]}
tokio = { version = "1.20.0", features = ["full"]}

因为是简单的项目，我们不需要很多依赖项：

sqlx: Rust SQL工具包。一个异步的纯Rust SQL crate，具有编译时检查查询功能，没有DSL。支持PostgreSQL、MySQL和SQLite。这里我们选择tokio运行时和SQLite特性。

tokio：一个事件驱动的、非阻塞的I/O平台，用于编写异步I/O支持的应用程序。我们将用于异步SQL操作的异步运行时。

SQLx查询和基础操作

创建数据库

下面代码创建数据库：

use sqlx::{migrate::MigrateDatabase, Sqlite};
const DB_URL: &str = "sqlite://sqlite.db";

#[tokio::main]
async fn main() {
    if !Sqlite::database_exists(DB_URL).await.unwrap_or(false) {
        println!("Creating database {}", DB_URL);
        match Sqlite::create_database(DB_URL).await {
            Ok(_) => println!("Create db success"),
            Err(error) => panic!("error: {}", error),
        }
    } else {
        println!("Database already exists");
    }
}

我们将一些项目引入范围：MigrateDatabase和Sqlite。前者（MigrateDatabase）是一个trait，它具有create_database、database_exists和drop_database函数。我们必须将这些引入作用域以便能够在Sqlite上调用它们。Sqlite代表数据库驱动程序。

运行代码后，一个新文件应该出现在项目的根目录中：sqlite.db

创建数据表

有许多方法可以用SQL创建表。例如，在Rust代码中使用原始SQL查询或使用SQL迁移脚本。首先，我们将在Rust代码中使用查询。在后面的部分中，我们将讨论如何使用迁移脚本。

当然，要对数据库执行查询，我们首先必须连接到数据库。那么，让我们使用SqlitePool为连接创建一个池对象。然后使用它来执行CREATE TABLE查询：

use sqlx::{migrate::MigrateDatabase, Sqlite, FromRow, SqlitePool};

const DB_URL: &str = "sqlite://sqlite.db";

#[derive(Clone, FromRow, Debug)]
struct User {
    id: i64,
    name: String,
}

#[tokio::main]
async fn main() {
    if !Sqlite::database_exists(DB_URL).await.unwrap_or(false) {
        println!("Creating database {}", DB_URL);
        match Sqlite::create_database(DB_URL).await {
            Ok(_) => println!("Create db success"),
            Err(error) => panic!("error: {}", error),
        }
    } else {
        println!("Database already exists");
    }

    let db = SqlitePool::connect(DB_URL).await.unwrap();
    let result = sqlx::query("CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY NOT NULL, name VARCHAR(250) NOT NULL);").execute(&db).await.unwrap();
    println!("Create user table result: {:?}", result);
}

如前所述，我们在第23行使用DB_URL字符串创建连接池。这个SqlitePool::connect调用返回Pool, 我们在第24行执行CREATE TABLE查询时使用对该对象的引用。运行程序的结果应该是这样的：

Database already exists
Create user table result: SqliteQueryResult { changes: 0, last_insert_rowid: 0 }

除了查询成功之外，结果并没有告诉我们太多。我们可以在表模式（sqlite_schema）上使用查询来显示数据库中的所有表：

use sqlx::{migrate::MigrateDatabase, Sqlite, FromRow, SqlitePool, Row};

const DB_URL: &str = "sqlite://sqlite.db";

#[derive(Clone, FromRow, Debug)]
struct User {
    id: i64,
    name: String,
}

#[tokio::main]
async fn main() {
    if !Sqlite::database_exists(DB_URL).await.unwrap_or(false) {
        println!("Creating database {}", DB_URL);
        match Sqlite::create_database(DB_URL).await {
            Ok(_) => println!("Create db success"),
            Err(error) => panic!("error: {}", error),
        }
    } else {
        println!("Database already exists");
    }

    let db = SqlitePool::connect(DB_URL).await.unwrap();
    let result = sqlx::query("CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY NOT NULL, name VARCHAR(250) NOT NULL);").execute(&db).await.unwrap();
    println!("Create user table result: {:?}", result);

    let result = sqlx::query(
        "SELECT name
         FROM sqlite_schema
         WHERE type ='table' 
         AND name NOT LIKE 'sqlite_%';",
    )
    .fetch_all(&db)
    .await
    .unwrap();

    for (idx, row) in result.iter().enumerate() {
        println!("[{}]: {:?}", idx, row.get::<String, &str>("name"));
    }
}

因为我们希望使用get从第38行获取列值，所以必须在第1行将Row纳入作用域。因此，通过在sqlite_schema表中查询表类型为table的项，我们可以得到数据库中所有表的名称。

在第37-394行，我们通过循环结果Vec来显示它们。使用.iter().enumerate（）我们可以获得值和索引号。最后，我们在第37行使用get获取列的值。我们可以使用字符串（&str）来索引列，并以字符串的形式检索值。我们必须在这里指定类型，因为get是一个使用泛型的函数。

现在让我们再次运行程序：

Database already exists
Create user table result: SqliteQueryResult { changes: 0, last_insert_rowid: 0 }
[0]: "users"

查询结果转为struct

在本节中，我们将再次查询一些数据。但是，我们没有使用泛型get（）函数来获取值，而是将结果反序列化为一个对象。我们将为此自己编写一个结构体。

让我们从定义users表的数据结构开始。这个表只有两列，所以它是非常简单的：

const DB_URL: &str = "sqlite://sqlite.db";

#[derive(Clone, FromRow, Debug)]
struct User {
    id: i64,
    name: String,
}

我们在这里使用了派生宏来实现FromRow特性。这个特性将允许我们使用query_as来获得我们想要的数据结构的结果。我们还使用克隆来制作副本和调试，以便在需要时作为调试信息轻松显示。

    let result = sqlx::query("INSERT INTO users (name) VALUES (?)")
    .bind("rusts")
    .execute(&db)
    .await
    .unwrap();

    println!("Query result: {:?}", result);
    let user_results = sqlx::query_as::<_, User>("SELECT id, name FROM users")
        .fetch_all(&db)
        .await
        .unwrap();

    for user in user_results {
        println!("[{}] name: {}", user.id, &user.name);
    }

首先通过bind方法设置参数值，然后使用query_as查询users表并将结果映射到具体类型。最后，我们使用User结构体的字段打印结果，这比在行对象上使用get() 要方便得多。运行结果如下：

Database already exists
Create user table result: SqliteQueryResult { changes: 0, last_insert_rowid: 0 }
[0]: "users"
Query result: SqliteQueryResult { changes: 1, last_insert_rowid: 1 }
[1] name: rusts

删除记录

下面代码展示如何删除记录：

    let delete_result = sqlx::query("DELETE FROM users WHERE name=$1")
        .bind("bobby")
        .execute(&db)
        .await
        .unwrap();
    println!("Delete result: {:?}", delete_result);

SQLx 迁移SQL示例

到目前为止，我们已经用Rust代码完成了SQLite项目的SQLx基础。在创建和更新表时，使用迁移机制可能更方便。迁移或模式迁移是将数据库更新到所需状态的脚本。这可能是通过添加表或列，甚至删除列或表，更改列类型等。

安装SQLx CLI

要使用迁移，我们必须安装SQLx CLI工具：cargo install SQLx-CLI。这将全局安装命令行工具。

增加迁移脚本

首先，让我们删除当前的数据库文件sqlite.db以及任何其他数据库文件，如sqlite.db-shm和sqlite.db-wal。

然后，让我们在项目目录的根目录的命令提示符中使用以下命令添加迁移：sqlx migrate add users。这将创建一个目录migrations和一个带有时间戳前缀并以_users.sql结尾的文件。时间戳告诉迁移代码以什么顺序执行脚本。

目前，该文件只有一个注释行——此处添加迁移脚本。所以让我们打开它并添加以下脚本：

CREATE TABLE IF NOT EXISTS users
(
    id          INTEGER PRIMARY KEY NOT NULL,
    name        VARCHAR(250)        NOT NULL,
    active      BOOLEAN             NOT NULL DEFAULT 0
);

Rust执行迁移

现在让我们修改Rust代码，使用迁移脚本而不是代码中的CREATE TABLE查询。我们还应该更新我们的User结构来包含新的活动列：

    let crate_dir =  std::env::var("CARGO_MANIFEST_DIR").unwrap_or("/root/workspace/sqlx_demo".to_string());
    
    let migrations = std::path::Path::new(&crate_dir).join("./migrations");
    let migration_results = sqlx::migrate::Migrator::new(migrations)
        .await
        .unwrap()
        .run(&db)
        .await;
    match migration_results {
        Ok(_) => println!("Migration success"),
        Err(error) => {
            panic!("error: {}", error);
        }
    }
    println!("migration: {:?}", migration_results);

因为数据表已经改变，因此其他相关代码也要修改：

use sqlx::{migrate::MigrateDatabase, FromRow, Row, Sqlite, SqlitePool};

const DB_URL: &str = "sqlite://sqlite.db";

#[derive(Clone, FromRow, Debug)]
struct User {
    id: i64,
    name: String,
    active: bool,
}

#[tokio::main]
async fn main() {
    if !Sqlite::database_exists(DB_URL).await.unwrap_or(false) {
        println!("Creating database {}", DB_URL);
        match Sqlite::create_database(DB_URL).await {
            Ok(_) => println!("Create db success"),
            Err(error) => panic!("error: {}", error),
        }
    } else {
        println!("Database already exists");
    }

    let db = SqlitePool::connect(DB_URL).await.unwrap();

    // let result = sqlx::query("CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY NOT NULL, name VARCHAR(250) NOT NULL);").execute(&db).await.unwrap();
    // println!("Create user table result: {:?}", result);

    let crate_dir =  std::env::var("CARGO_MANIFEST_DIR").unwrap_or("/root/workspace/sqlx_demo".to_string());
    
    let migrations = std::path::Path::new(&crate_dir).join("./migrations");
    let migration_results = sqlx::migrate::Migrator::new(migrations)
        .await
        .unwrap()
        .run(&db)
        .await;
    match migration_results {
        Ok(_) => println!("Migration success"),
        Err(error) => {
            panic!("error: {}", error);
        }
    }
    println!("migration: {:?}", migration_results);


    let result = sqlx::query(
        "SELECT name
         FROM sqlite_schema
         WHERE type ='table' 
         AND name NOT LIKE 'sqlite_%';",
    )
    .fetch_all(&db)
    .await
    .unwrap();

    for (idx, row) in result.iter().enumerate() {
        println!("[{}]: {:?}", idx, row.get::<String, &str>("name"));
    }

    let result = sqlx::query("INSERT INTO users (name) VALUES (?)")
    .bind("rusts")
    .execute(&db)
    .await
    .unwrap();

    println!("Query result: {:?}", result);
    let user_results = sqlx::query_as::<_, User>("SELECT id, name, active FROM users")
        .fetch_all(&db)
        .await
        .unwrap();
    for user in user_results {
        println!("[{}] name: {}, active:{}", user.id, &user.name, user.active);
    }

    
    let delete_result = sqlx::query("DELETE FROM users WHERE name=$1")
        .bind("bobby")
        .execute(&db)
        .await
        .unwrap();
    println!("Delete result: {:?}", delete_result);

}

主要包括struct User, 以及查询与输出相关代码：

    println!("Query result: {:?}", result);
    let user_results = sqlx::query_as::<_, User>("SELECT id, name, active FROM users")
        .fetch_all(&db)
        .await
        .unwrap();
    for user in user_results {
        println!("[{}] name: {}, active:{}", user.id, &user.name, user.active);
    }

运行代码，输出结果如下：

Database already exists
Migration success
migration: Ok(())
[0]: "_sqlx_migrations"
[1]: "users"
Query result: SqliteQueryResult { changes: 1, last_insert_rowid: 2 }
[1] name: rusts, active:false
[2] name: rusts, active:false
Delete result: SqliteQueryResult { changes: 0, last_insert_rowid: 2 }

我们可以看到迁移是成功的，users表再次出现在sqlite_schema中。此外，还列出了另一个表：_sqlx_migrations表。这是系统注册已执行迁移的地方。

增加新的表

添加另一个表当然很简单，使用命令sqlx migrate add items,就像添加users表一样。这将在迁移目录中添加另一个后缀为_items.sql的文件。让我们添加以下脚本：

CREATE TABLE IF NOT EXISTS items
(
    id          INTEGER PRIMARY KEY NOT NULL,
    name        VARCHAR(250)        NOT NULL,
    price       FLOAT               NOT NULL DEFAULT 0
);

再次运行代码，输出结果如下：

Database already exists
Migration success
migration: Ok(())
[0]: "_sqlx_migrations"
[1]: "users"
[2]: "items"
Query result: SqliteQueryResult { changes: 1, last_insert_rowid: 3 }
[1] name: rusts, active:false
[2] name: rusts, active:false
[3] name: rusts, active:false
Delete result: SqliteQueryResult { changes: 0, last_insert_rowid: 3 }

我们看到items表已经创建好了。

更新表结构

当然，我们也可以通过更新表来添加新列。例如，在users表中添加lastname。让我们使用命令sqlx migrate add users_lastname添加一个迁移脚本。然后将下面的脚本添加到_users_lastname.sql文件中:

ALTER TABLE users  ADD lastname VARCHAR(250) NOT NULL DEFAULT 'unknown';

更新user相关代码：

#[derive(Clone, FromRow, Debug)]
struct User {
    id: i64,
    name: String,
    lastname: String,
    active: bool,
}

/// 插入相关代码
let result = sqlx::query("INSERT INTO users (name, lastname) VALUES (?,?)")
    .bind("bobby")
    .bind("fischer")
    .execute(&db)
    .await
    .unwrap();
println!("Query result: {:?}", result);

// 查询相关代码
let user_results = sqlx::query_as::<_, User>("SELECT id, name, lastname, active FROM users")
    .fetch_all(&db)
    .await
    .unwrap();
for user in user_results {
    println!(
        "[{}] name: {}, lastname: {}, active: {}",
        user.id, &user.name, &user.lastname, user.active
    );
}

运行代码，输出结果：

Database already exists
Migration success
migration: Ok(())
[0]: "_sqlx_migrations"
[1]: "users"
[2]: "items"
Query result: SqliteQueryResult { changes: 1, last_insert_rowid: 4 }
[1] name: rusts, lastname: unknown, active: false
[2] name: rusts, lastname: unknown, active: false
[3] name: rusts, lastname: unknown, active: false
[4] name: bobby, lastname: fischer, active: false
Delete result: SqliteQueryResult { changes: 1, last_insert_rowid: 4 }

最后总结

在这个简单而快速的教程中，我们学习了使用SQLx crate和创建SQLite数据库的基础知识。我们还学习了一些关于迁移和参数化查询的知识。现在我们已经为编写使用数据库进行信息存储的简单应用程序打下了基础。