Lib.rs

🔱 MySQL、Postgres 和 SQLite 的动态查询构建器

SeaQuery

SeaQuery 是一个查询构建器，帮助您在 Rust 中构建动态 SQL 查询。您可以使用易用的 API 构建表达式、查询和模式，作为抽象语法树。我们支持 MySQL、Postgres 和 SQLite，在适当的地方通过一个统一的接口对它们的行为进行对齐。

我们为 SQLx、postgres 和 rusqlite 提供集成。请参阅 示例 了解用法。

SeaQuery 是 SeaORM 的基础，SeaORM 是一个异步且动态的 Rust ORM。

如果您喜欢我们的工作，请考虑 star、评论、分享和贡献！

加入我们的 Discord 服务器，与其他 SeaQL 社区成员聊天！

安装

# Cargo.toml
[dependencies]
sea-query = "0"

SeaQuery 非常轻量，所有依赖都是可选的（除了 inherent）。

特性标志

宏：derive attr

异步支持：thread-safe（用 Arc 代替 Rc）

SQL 引擎：backend-mysql、backend-postgres、backend-sqlite

类型支持：with-chrono、with-time、with-json、with-rust_decimal、with-bigdecimal、with-uuid、with-ipnetwork、with-mac_address、postgres-array、postgres-interval

用法

目录

1. 基础知识

   1. 标识符
   2. 表达式
   3. 条件
   4. 语句构建器

2. 查询语句

   1. 查询选择
   2. 查询插入
   3. 查询更新
   4. 查询删除

3. 高级

   1. 聚合函数
   2. 类型转换
   3. 自定义函数

4. 模式语句

   1. 创建表
   2. 修改表
   3. 删除表
   4. 重命名表
   5. 截断表
   6. 创建外键
   7. 删除外键
   8. 创建索引
   9. 删除索引

动机

你为什么要使用动态查询构建器呢？

1. 参数绑定

使用原始SQL时，参数绑定可能会引起头痛。使用SeaQuery，你可以

assert_eq!(
    Query::select()
        .column(Glyph::Image)
        .from(Glyph::Table)
        .and_where(Expr::col(Glyph::Image).like("A"))
        .and_where(Expr::col(Glyph::Id).is_in([1, 2, 3]))
        .build(PostgresQueryBuilder),
    (
        r#"SELECT "image" FROM "glyph" WHERE "image" LIKE $1 AND "id" IN ($2, $3, $4)"#
            .to_owned(),
        Values(vec![
            Value::String(Some(Box::new("A".to_owned()))),
            Value::Int(Some(1)),
            Value::Int(Some(2)),
            Value::Int(Some(3))
        ])
    )
);

2. 动态查询

您可以根据用户输入在运行时构建查询

Query::select()
    .column(Char::Character)
    .from(Char::Table)
    .conditions(
        // some runtime condition
        true,
        // if condition is true then add the following condition
        |q| {
            q.and_where(Expr::col(Char::Id).eq(1));
        },
        // otherwise leave it as is
        |q| {},
    );

标识符

Iden 是用于任何查询语句的标识符的 trait。

通常由 Enum 实现，其中每个 Enum 代表数据库中找到的表，其变体包括表名和列名。

[Iden::unquoted()] 必须实现，以在 Enum 变体与其对应的字符串值之间提供映射。

use sea_query::*;

// For example Character table with column id, character, font_size...
pub enum Character {
    Table,
    Id,
    FontId,
    FontSize,
}

// Mapping between Enum variant and its corresponding string value
impl Iden for Character {
    fn unquoted(&self, s: &mut dyn std::fmt::Write) {
        write!(
            s,
            "{}",
            match self {
                Self::Table => "character",
                Self::Id => "id",
                Self::FontId => "font_id",
                Self::FontSize => "font_size",
            }
        )
        .unwrap();
    }
}

如果您不介意运行另一个过程宏，可以激活 crate 上的 derive 或 attr 功能以节省一些样板代码。有关更多使用信息，请参阅 derive 示例 或 属性示例。

#[cfg(feature = "derive")]
use sea_query::Iden;

// This will implement Iden exactly as shown above
#[derive(Iden)]
enum Character {
    Table,
}
assert_eq!(Character::Table.to_string(), "character");

// You can also derive a unit struct
#[derive(Iden)]
struct Glyph;
assert_eq!(Glyph.to_string(), "glyph");

#[cfg(feature = "attr")]
use sea_query::{enum_def, Iden};

#[enum_def]
struct Character {
    pub foo: u64,
}

// It generates the following along with Iden impl
enum CharacterIden {
    Table,
    Foo,
}

assert_eq!(CharacterIden::Table.to_string(), "character");
assert_eq!(CharacterIden::Foo.to_string(), "foo");

表达式

使用 Expr 在查询中构建选择、连接、WHERE 和 HAVING 表达式。

assert_eq!(
    Query::select()
        .column(Char::Character)
        .from(Char::Table)
        .and_where(
            Expr::expr(Expr::col(Char::SizeW).add(1))
                .mul(2)
                .eq(Expr::expr(Expr::col(Char::SizeH).div(2)).sub(1))
        )
        .and_where(
            Expr::col(Char::SizeW).in_subquery(
                Query::select()
                    .expr(Expr::cust_with_values("ln($1 ^ $2)", [2.4, 1.2]))
                    .take()
            )
        )
        .and_where(
            Expr::col(Char::Character)
                .like("D")
                .and(Expr::col(Char::Character).like("E"))
        )
        .to_string(PostgresQueryBuilder),
    [
        r#"SELECT "character" FROM "character""#,
        r#"WHERE ("size_w" + 1) * 2 = ("size_h" / 2) - 1"#,
        r#"AND "size_w" IN (SELECT ln(2.4 ^ 1.2))"#,
        r#"AND (("character" LIKE 'D') AND ("character" LIKE 'E'))"#,
    ]
    .join(" ")
);

条件

如果您需要表达复杂条件，可以使用 Condition 构建器，适用于 ConditionalStatement::cond_where 和 SelectStatement::cond_having。

assert_eq!(
    Query::select()
        .column(Glyph::Id)
        .from(Glyph::Table)
        .cond_where(
            Cond::any()
                .add(
                    Cond::all()
                        .add(Expr::col(Glyph::Aspect).is_null())
                        .add(Expr::col(Glyph::Image).is_null())
                )
                .add(
                    Cond::all()
                        .add(Expr::col(Glyph::Aspect).is_in([3, 4]))
                        .add(Expr::col(Glyph::Image).like("A%"))
                )
        )
        .to_string(PostgresQueryBuilder),
    [
        r#"SELECT "id" FROM "glyph""#,
        r#"WHERE"#,
        r#"("aspect" IS NULL AND "image" IS NULL)"#,
        r#"OR"#,
        r#"("aspect" IN (3, 4) AND "image" LIKE 'A%')"#,
    ]
    .join(" ")
);

还有方便的 any! 和 all! 宏

Query::select().cond_where(any![
    Expr::col(Glyph::Aspect).is_in([3, 4]),
    all![
        Expr::col(Glyph::Aspect).is_null(),
        Expr::col(Glyph::Image).like("A%")
    ]
]);

语句构建器

语句分为两类：查询和模式，并且分别通过 QueryStatementBuilder 和 SchemaStatementBuilder 序列化为 SQL。

模式语句有以下接口

fn build<T: SchemaBuilder>(&self, schema_builder: T) -> String;

查询语句有以下接口

fn build<T: QueryBuilder>(&self, query_builder: T) -> (String, Values);

fn to_string<T: QueryBuilder>(&self, query_builder: T) -> String;

build 将 SQL 语句作为字符串构建，并通过二进制协议传递给数据库驱动程序作为参数。这是首选方式，因为它具有更少的开销，并且更安全。

to_string 构建 SQL 语句作为字符串，并注入参数。这对于测试和调试很有用。

查询选择

let query = Query::select()
    .column(Char::Character)
    .column((Font::Table, Font::Name))
    .from(Char::Table)
    .left_join(Font::Table, Expr::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)))
    .and_where(Expr::col(Char::SizeW).is_in([3, 4]))
    .and_where(Expr::col(Char::Character).like("A%"))
    .to_owned();

assert_eq!(
    query.to_string(MysqlQueryBuilder),
    r#"SELECT `character`, `font`.`name` FROM `character` LEFT JOIN `font` ON `character`.`font_id` = `font`.`id` WHERE `size_w` IN (3, 4) AND `character` LIKE 'A%'"#
);
assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"SELECT "character", "font"."name" FROM "character" LEFT JOIN "font" ON "character"."font_id" = "font"."id" WHERE "size_w" IN (3, 4) AND "character" LIKE 'A%'"#
);
assert_eq!(
    query.to_string(SqliteQueryBuilder),
    r#"SELECT "character", "font"."name" FROM "character" LEFT JOIN "font" ON "character"."font_id" = "font"."id" WHERE "size_w" IN (3, 4) AND "character" LIKE 'A%'"#
);

查询插入

let query = Query::insert()
    .into_table(Glyph::Table)
    .columns([Glyph::Aspect, Glyph::Image])
    .values_panic([5.15.into(), "12A".into()])
    .values_panic([4.21.into(), "123".into()])
    .to_owned();

assert_eq!(
    query.to_string(MysqlQueryBuilder),
    r#"INSERT INTO `glyph` (`aspect`, `image`) VALUES (5.15, '12A'), (4.21, '123')"#
);
assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"INSERT INTO "glyph" ("aspect", "image") VALUES (5.15, '12A'), (4.21, '123')"#
);
assert_eq!(
    query.to_string(SqliteQueryBuilder),
    r#"INSERT INTO "glyph" ("aspect", "image") VALUES (5.15, '12A'), (4.21, '123')"#
);

查询更新

let query = Query::update()
    .table(Glyph::Table)
    .values([(Glyph::Aspect, 1.23.into()), (Glyph::Image, "123".into())])
    .and_where(Expr::col(Glyph::Id).eq(1))
    .to_owned();

assert_eq!(
    query.to_string(MysqlQueryBuilder),
    r#"UPDATE `glyph` SET `aspect` = 1.23, `image` = '123' WHERE `id` = 1"#
);
assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"UPDATE "glyph" SET "aspect" = 1.23, "image" = '123' WHERE "id" = 1"#
);
assert_eq!(
    query.to_string(SqliteQueryBuilder),
    r#"UPDATE "glyph" SET "aspect" = 1.23, "image" = '123' WHERE "id" = 1"#
);

查询删除

let query = Query::delete()
    .from_table(Glyph::Table)
    .cond_where(
        Cond::any()
            .add(Expr::col(Glyph::Id).lt(1))
            .add(Expr::col(Glyph::Id).gt(10)),
    )
    .to_owned();

assert_eq!(
    query.to_string(MysqlQueryBuilder),
    r#"DELETE FROM `glyph` WHERE `id` < 1 OR `id` > 10"#
);
assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"DELETE FROM "glyph" WHERE "id" < 1 OR "id" > 10"#
);
assert_eq!(
    query.to_string(SqliteQueryBuilder),
    r#"DELETE FROM "glyph" WHERE "id" < 1 OR "id" > 10"#
);

聚合函数

max、min、sum、avg、count 等

let query = Query::select()
    .expr(Func::sum(Expr::col((Char::Table, Char::SizeH))))
    .from(Char::Table)
    .to_owned();
assert_eq!(
    query.to_string(MysqlQueryBuilder),
    r#"SELECT SUM(`character`.`size_h`) FROM `character`"#
);
assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"SELECT SUM("character"."size_h") FROM "character""#
);
assert_eq!(
    query.to_string(SqliteQueryBuilder),
    r#"SELECT SUM("character"."size_h") FROM "character""#
);

类型转换

let query = Query::select()
    .expr(Func::cast_as("hello", Alias::new("MyType")))
    .to_owned();

assert_eq!(
    query.to_string(MysqlQueryBuilder),
    r#"SELECT CAST('hello' AS MyType)"#
);
assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"SELECT CAST('hello' AS MyType)"#
);
assert_eq!(
    query.to_string(SqliteQueryBuilder),
    r#"SELECT CAST('hello' AS MyType)"#
);

自定义函数

struct MyFunction;

impl Iden for MyFunction {
    fn unquoted(&self, s: &mut dyn Write) {
        write!(s, "MY_FUNCTION").unwrap();
    }
}

let query = Query::select()
    .expr(Func::cust(MyFunction).arg(Expr::val("hello")))
    .to_owned();

assert_eq!(
    query.to_string(MysqlQueryBuilder),
    r#"SELECT MY_FUNCTION('hello')"#
);
assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"SELECT MY_FUNCTION('hello')"#
);
assert_eq!(
    query.to_string(SqliteQueryBuilder),
    r#"SELECT MY_FUNCTION('hello')"#
);

创建表

let table = Table::create()
    .table(Char::Table)
    .if_not_exists()
    .col(ColumnDef::new(Char::Id).integer().not_null().auto_increment().primary_key())
    .col(ColumnDef::new(Char::FontSize).integer().not_null())
    .col(ColumnDef::new(Char::Character).string().not_null())
    .col(ColumnDef::new(Char::SizeW).integer().not_null())
    .col(ColumnDef::new(Char::SizeH).integer().not_null())
    .col(ColumnDef::new(Char::FontId).integer().default(Value::Int(None)))
    .foreign_key(
        ForeignKey::create()
            .name("FK_2e303c3a712662f1fc2a4d0aad6")
            .from(Char::Table, Char::FontId)
            .to(Font::Table, Font::Id)
            .on_delete(ForeignKeyAction::Cascade)
            .on_update(ForeignKeyAction::Cascade)
    )
    .to_owned();

assert_eq!(
    table.to_string(MysqlQueryBuilder),
    [
        r#"CREATE TABLE IF NOT EXISTS `character` ("#,
            r#"`id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,"#,
            r#"`font_size` int NOT NULL,"#,
            r#"`character` varchar(255) NOT NULL,"#,
            r#"`size_w` int NOT NULL,"#,
            r#"`size_h` int NOT NULL,"#,
            r#"`font_id` int DEFAULT NULL,"#,
            r#"CONSTRAINT `FK_2e303c3a712662f1fc2a4d0aad6`"#,
                r#"FOREIGN KEY (`font_id`) REFERENCES `font` (`id`)"#,
                r#"ON DELETE CASCADE ON UPDATE CASCADE"#,
        r#")"#,
    ].join(" ")
);
assert_eq!(
    table.to_string(PostgresQueryBuilder),
    [
        r#"CREATE TABLE IF NOT EXISTS "character" ("#,
            r#""id" serial NOT NULL PRIMARY KEY,"#,
            r#""font_size" integer NOT NULL,"#,
            r#""character" varchar NOT NULL,"#,
            r#""size_w" integer NOT NULL,"#,
            r#""size_h" integer NOT NULL,"#,
            r#""font_id" integer DEFAULT NULL,"#,
            r#"CONSTRAINT "FK_2e303c3a712662f1fc2a4d0aad6""#,
                r#"FOREIGN KEY ("font_id") REFERENCES "font" ("id")"#,
                r#"ON DELETE CASCADE ON UPDATE CASCADE"#,
        r#")"#,
    ].join(" ")
);
assert_eq!(
    table.to_string(SqliteQueryBuilder),
    [
       r#"CREATE TABLE IF NOT EXISTS "character" ("#,
           r#""id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,"#,
           r#""font_size" integer NOT NULL,"#,
           r#""character" text NOT NULL,"#,
           r#""size_w" integer NOT NULL,"#,
           r#""size_h" integer NOT NULL,"#,
           r#""font_id" integer DEFAULT NULL,"#,
           r#"FOREIGN KEY ("font_id") REFERENCES "font" ("id") ON DELETE CASCADE ON UPDATE CASCADE"#,
       r#")"#,
    ].join(" ")
);

修改表

let table = Table::alter()
    .table(Font::Table)
    .add_column(
        ColumnDef::new(Alias::new("new_col"))
            .integer()
            .not_null()
            .default(100),
    )
    .to_owned();

assert_eq!(
    table.to_string(MysqlQueryBuilder),
    r#"ALTER TABLE `font` ADD COLUMN `new_col` int NOT NULL DEFAULT 100"#
);
assert_eq!(
    table.to_string(PostgresQueryBuilder),
    r#"ALTER TABLE "font" ADD COLUMN "new_col" integer NOT NULL DEFAULT 100"#
);
assert_eq!(
    table.to_string(SqliteQueryBuilder),
    r#"ALTER TABLE "font" ADD COLUMN "new_col" integer NOT NULL DEFAULT 100"#,
);

删除表

let table = Table::drop()
    .table(Glyph::Table)
    .table(Char::Table)
    .to_owned();

assert_eq!(
    table.to_string(MysqlQueryBuilder),
    r#"DROP TABLE `glyph`, `character`"#
);
assert_eq!(
    table.to_string(PostgresQueryBuilder),
    r#"DROP TABLE "glyph", "character""#
);
assert_eq!(
    table.to_string(SqliteQueryBuilder),
    r#"DROP TABLE "glyph", "character""#
);

重命名表

let table = Table::rename()
    .table(Font::Table, Alias::new("font_new"))
    .to_owned();

assert_eq!(
    table.to_string(MysqlQueryBuilder),
    r#"RENAME TABLE `font` TO `font_new`"#
);
assert_eq!(
    table.to_string(PostgresQueryBuilder),
    r#"ALTER TABLE "font" RENAME TO "font_new""#
);
assert_eq!(
    table.to_string(SqliteQueryBuilder),
    r#"ALTER TABLE "font" RENAME TO "font_new""#
);

截断表

let table = Table::truncate().table(Font::Table).to_owned();

assert_eq!(
    table.to_string(MysqlQueryBuilder),
    r#"TRUNCATE TABLE `font`"#
);
assert_eq!(
    table.to_string(PostgresQueryBuilder),
    r#"TRUNCATE TABLE "font""#
);
// Sqlite does not support the TRUNCATE statement

创建外键

let foreign_key = ForeignKey::create()
    .name("FK_character_font")
    .from(Char::Table, Char::FontId)
    .to(Font::Table, Font::Id)
    .on_delete(ForeignKeyAction::Cascade)
    .on_update(ForeignKeyAction::Cascade)
    .to_owned();

assert_eq!(
    foreign_key.to_string(MysqlQueryBuilder),
    [
        r#"ALTER TABLE `character`"#,
        r#"ADD CONSTRAINT `FK_character_font`"#,
        r#"FOREIGN KEY (`font_id`) REFERENCES `font` (`id`)"#,
        r#"ON DELETE CASCADE ON UPDATE CASCADE"#,
    ]
    .join(" ")
);
assert_eq!(
    foreign_key.to_string(PostgresQueryBuilder),
    [
        r#"ALTER TABLE "character" ADD CONSTRAINT "FK_character_font""#,
        r#"FOREIGN KEY ("font_id") REFERENCES "font" ("id")"#,
        r#"ON DELETE CASCADE ON UPDATE CASCADE"#,
    ]
    .join(" ")
);
// Sqlite does not support modification of foreign key constraints to existing tables

删除外键

let foreign_key = ForeignKey::drop()
    .name("FK_character_font")
    .table(Char::Table)
    .to_owned();

assert_eq!(
    foreign_key.to_string(MysqlQueryBuilder),
    r#"ALTER TABLE `character` DROP FOREIGN KEY `FK_character_font`"#
);
assert_eq!(
    foreign_key.to_string(PostgresQueryBuilder),
    r#"ALTER TABLE "character" DROP CONSTRAINT "FK_character_font""#
);
// Sqlite does not support modification of foreign key constraints to existing tables

创建索引

let index = Index::create()
    .name("idx-glyph-aspect")
    .table(Glyph::Table)
    .col(Glyph::Aspect)
    .to_owned();

assert_eq!(
    index.to_string(MysqlQueryBuilder),
    r#"CREATE INDEX `idx-glyph-aspect` ON `glyph` (`aspect`)"#
);
assert_eq!(
    index.to_string(PostgresQueryBuilder),
    r#"CREATE INDEX "idx-glyph-aspect" ON "glyph" ("aspect")"#
);
assert_eq!(
    index.to_string(SqliteQueryBuilder),
    r#"CREATE INDEX "idx-glyph-aspect" ON "glyph" ("aspect")"#
);

删除索引

let index = Index::drop()
    .name("idx-glyph-aspect")
    .table(Glyph::Table)
    .to_owned();

assert_eq!(
    index.to_string(MysqlQueryBuilder),
    r#"DROP INDEX `idx-glyph-aspect` ON `glyph`"#
);
assert_eq!(
    index.to_string(PostgresQueryBuilder),
    r#"DROP INDEX "idx-glyph-aspect""#
);
assert_eq!(
    index.to_string(SqliteQueryBuilder),
    r#"DROP INDEX "idx-glyph-aspect""#
);

许可证

根据以下许可证之一授权

• Apache License，版本 2.0（LICENSE-APACHE 或 http://www.apache.org/licenses/LICENSE-2.0）
• MIT许可（《LICENSE-MIT》或http://opensource.org/licenses/MIT）

由您自行选择。

贡献

除非您明确说明，否则根据Apache-2.0许可证定义的您有意提交以包含在作品中的任何贡献，将双重许可如上所述，不附加任何额外条款或条件。

SeaQuery是一个社区驱动的项目。我们欢迎您参与、贡献，共同为Rust的未来建设。

向我们的贡献者表示衷心的感谢