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

异步支持：线程安全（用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 是用于任何查询语句的标识符的特例。

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

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

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许可证，版本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的未来建设。

向我们的贡献者致以崇高的敬意