Lib.rs

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

SeaQuery

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

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

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

如果您喜欢我们做的事情，请考虑点赞、评论、分享和贡献！

加入我们的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 是用于任何查询语句的标识符的特质。

通常由 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();
    }
}

如果您愿意运行另一个过程宏，可以激活 crateg 上的 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 许可证 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的未来建设。

向我们的贡献者致以热烈的问候