sea-query-derive — Rust 的数据库接口 // Lib.rs • Rust 包仓库

7 个不稳定版本

0.4.1	2023 年 10 月 19 日
0.4.0	2023 年 7 月 12 日
0.3.1	~~2023 年 7 月 12 日~~
0.3.0	2022 年 12 月 9 日
0.1.0	2021 年 2 月 9 日

172 在 #sql-query

174,033 每月下载量
在 64 个 crate 中使用 (通过 sea-query)

MIT/Apache

21KB
510 行

🔱 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

动机

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

参数绑定

使用原始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))
        ])
    )
);

动态查询

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

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 是任何查询语句中使用的标识符的 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%")
    ]
]);

语句构建器

语句分为 2 类：查询和模式，并分别通过 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 或 https://apache.ac.cn/licenses/LICENSE-2.0）
MIT 许可证（LICENSE-MIT 或 http://opensource.org/licenses/MIT）

由你选择。

贡献

除非您明确说明，否则您根据Apache-2.0许可证定义提交给作品的所有贡献，均应以上述方式双重许可，不附加任何额外条款或条件。

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

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

依赖项

~0.3–0.8MB
~19K SLoC