github.com/SeaQL/sea-query @1.0.0

<strong>🔱 A dynamic query builder for MySQL, Postgres and SQLite</strong>

SeaQuery

SeaQuery is a query builder to help you construct dynamic SQL queries in Rust. You can construct expressions, queries and schema as abstract syntax trees using an ergonomic API. We support MySQL, Postgres and SQLite behind a common interface that aligns their behaviour where appropriate. MS SQL Server Support is available under SeaORM X.

SeaQuery is written in 100% safe Rust. All workspace crates has #![forbid(unsafe_code)].

SeaQuery is the foundation of SeaORM, an async & dynamic ORM for Rust. We provide integration for SQLx, postgres and rusqlite. See examples for usage.

If you like what we do, consider starring, commenting, sharing and contributing!

Join our Discord server to chat with others in the SeaQL community!

Install

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

SeaQuery is very lightweight, all dependencies are optional.

Feature flags

Macro: derive

SQL engine: backend-mysql, backend-postgres, backend-sqlite

Type support: with-chrono, with-time, with-json, with-rust_decimal, with-bigdecimal, with-uuid, with-ipnetwork, with-mac_address, postgres-array, postgres-interval, postgres-vector

Usage

Table of Content

1. Basics

   1. Iden
   2. Expression
   3. Condition
   4. Statement Builders

2. Query Statement

   1. Query Select
   2. Query Insert
   3. Query Update
   4. Query Delete

3. Advanced

   1. Aggregate Functions
   2. Casting
   3. Custom Function

4. Schema Statement

   1. Table Create
   2. Table Alter
   3. Table Drop
   4. Table Rename
   5. Table Truncate
   6. Foreign Key Create
   7. Foreign Key Drop
   8. Index Create
   9. Index Drop

Motivation

Why would you want to use a dynamic query builder?

1. Parameter bindings

One of the headaches when using raw SQL is parameter binding. With SeaQuery you can inject parameters right alongside the expression, and the $N sequencing will be handled for you. No more "off by one" errors!

assert_eq!(
    Query::select()
        .expr(Expr::col("size_w").add(1).mul(2))
        .from("glyph")
        .and_where(Expr::col("image").like("A"))
        .and_where(Expr::col("id").is_in([3, 4, 5]))
        .build(PostgresQueryBuilder),
    (
        r#"SELECT ("size_w" + $1) * $2 FROM "glyph" WHERE "image" LIKE $3 AND "id" IN ($4, $5, $6)"#
            .to_owned(),
        Values(vec![
            1.into(),
            2.into(),
            "A".to_owned().into(),
            3.into(),
            4.into(),
            5.into(),
        ])
    )
);

If you need an "escape hatch" to construct complex queries, you can use custom expressions, and still have the benefit of sequentially-binded parameters.

assert_eq!(
    Query::select()
        .columns(["size_w", "size_h"])
        .from("character")
        .and_where(Expr::col("id").eq(1)) // this is $1
        // custom expressions only need to define local parameter sequence.
        // its global sequence will be re-written.
        // here, we flip the order of $2 & $1 to make it look tricker!
        .and_where(Expr::cust_with_values(r#""size_w" = $2 * $1"#, [3, 2]))
        .and_where(Expr::col("size_h").gt(4)) // this is $N?
        .build(PostgresQueryBuilder),
    (
        r#"SELECT "size_w", "size_h" FROM "character" WHERE "id" = $1 AND ("size_w" = $2 * $3) AND "size_h" > $4"#
            .to_owned(),
        Values(vec![1.into(), 2.into(), 3.into(), 4.into()])
    )
);

2. Dynamic query

You can construct the query at runtime based on user inputs with a fluent interface, so you don't have to append WHERE or AND conditionally.

fn query(a: Option<i32>, b: Option<char>) -> SelectStatement {
    Query::select()
        .column("id")
        .from("character")
        .apply_if(a, |q, v| {
            q.and_where(Expr::col("font_id").eq(v));
        })
        .apply_if(b, |q, v| {
            q.and_where(Expr::col("ascii").like(v));
        })
        .take()
}

assert_eq!(
    query(Some(5), Some('A')).to_string(MysqlQueryBuilder),
    "SELECT `id` FROM `character` WHERE `font_id` = 5 AND `ascii` LIKE 'A'"
);
assert_eq!(
    query(Some(5), None).to_string(MysqlQueryBuilder),
    "SELECT `id` FROM `character` WHERE `font_id` = 5"
);
assert_eq!(
    query(None, None).to_string(MysqlQueryBuilder),
    "SELECT `id` FROM `character`"
);

Conditions can be arbitrarily complex, thanks to SeaQuery's internal AST:

assert_eq!(
    Query::select()
        .column("id")
        .from("glyph")
        .cond_where(
            Cond::any()
                .add(
                    Cond::all()
                        .add(Expr::col("aspect").is_null())
                        .add(Expr::col("image").is_null())
                )
                .add(
                    Cond::all()
                        .add(Expr::col("aspect").is_in([3, 4]))
                        .add(Expr::col("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(" ")
);

There is no superfluous parentheses (((( cluttering the query, because SeaQuery respects operator precedence when injecting them.

3. Cross database support

With SeaQuery, you can target multiple database backends while maintaining a single source of query logic.

let query = Query::insert()
    .into_table("glyph")
    .columns(["aspect", "image"])
    .values_panic([
        2.into(),
        3.into(),
    ])
    .on_conflict(
        OnConflict::column("id")
            .update_columns(["aspect", "image"])
            .to_owned(),
    )
    .to_owned();

assert_eq!(
    query.to_string(MysqlQueryBuilder),
    r#"INSERT INTO `glyph` (`aspect`, `image`) VALUES (2, 3) ON DUPLICATE KEY UPDATE `aspect` = VALUES(`aspect`), `image` = VALUES(`image`)"#
);
assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"INSERT INTO "glyph" ("aspect", "image") VALUES (2, 3) ON CONFLICT ("id") DO UPDATE SET "aspect" = "excluded"."aspect", "image" = "excluded"."image""#
);
assert_eq!(
    query.to_string(SqliteQueryBuilder),
    r#"INSERT INTO "glyph" ("aspect", "image") VALUES (2, 3) ON CONFLICT ("id") DO UPDATE SET "aspect" = "excluded"."aspect", "image" = "excluded"."image""#
);

4. Improved raw SQL ergonomics

SeaQuery 1.0 added a new raw_query! macro with named parameters, nested field access, array expansion and tuple expansion. It surely will make crafting complex query easier.

let (a, b, c) = (1, 2, "A");
let d = vec![3, 4, 5];
let query = sea_query::raw_query!(
    PostgresQueryBuilder,
    r#"SELECT ("size_w" + {a}) * {b} FROM "glyph" WHERE "image" LIKE {c} AND "id" IN ({..d})"#
);

assert_eq!(
    query.sql,
    r#"SELECT ("size_w" + $1) * $2 FROM "glyph" WHERE "image" LIKE $3 AND "id" IN ($4, $5, $6)"#
);
assert_eq!(
    query.values,
    Values(vec![
        1.into(),
        2.into(),
        "A".into(),
        3.into(),
        4.into(),
        5.into()
    ])
);

Insert with vector-of-tuple expansion.

let values = vec![(2.1345, "24B"), (5.15, "12A")];
let query = sea_query::raw_query!(
    PostgresQueryBuilder,
    r#"INSERT INTO "glyph" ("aspect", "image") VALUES {..(values.0:1),}"#
);

assert_eq!(
    query.sql,
    r#"INSERT INTO "glyph" ("aspect", "image") VALUES ($1, $2), ($3, $4)"#
);
assert_eq!(
    query.values,
    Values(vec![2.1345.into(), "24B".into(), 5.15.into(), "12A".into()])
);

Update with nested field access.

struct Character {
    id: i32,
    font_size: u16,
}
let c = Character {
    id: 11,
    font_size: 22,
};
let query = sea_query::raw_query!(
    MysqlQueryBuilder,
    "UPDATE `character` SET `font_size` = {c.font_size} WHERE `id` = {c.id}"
);

assert_eq!(
    query.sql,
    "UPDATE `character` SET `font_size` = ? WHERE `id` = ?"
);
assert_eq!(query.values, Values(vec![22u16.into(), 11i32.into()]));

Basics

Iden

Iden is a trait for identifiers used in any query statement.

Commonly implemented by Enum where each Enum represents a table found in a database, and its variants include table name and column name.

You can use the Iden derive macro to implement it.

#[derive(Iden)]
enum Character {
    Table,
    Id,
    FontId,
    FontSize,
}

assert_eq!(Character::Table.to_string(), "character");
assert_eq!(Character::Id.to_string(), "id");
assert_eq!(Character::FontId.to_string(), "font_id");
assert_eq!(Character::FontSize.to_string(), "font_size");

#[derive(Iden)]
struct Glyph;
assert_eq!(Glyph.to_string(), "glyph");

use sea_query::{Iden, enum_def};

#[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");

Expression

Use [Expr] constructors and [ExprTrait] methods to construct SELECT, JOIN, WHERE and HAVING expression in query.

assert_eq!(
    Query::select()
        .column("char_code")
        .from("character")
        .and_where(
            Expr::col("size_w")
                .add(1)
                .mul(2)
                .eq(Expr::col("size_h").div(2).sub(1))
        )
        .and_where(
            Expr::col("size_w").in_subquery(
                Query::select()
                    .expr(Expr::cust_with_values("ln($1 ^ $2)", [2.4, 1.2]))
                    .take()
            )
        )
        .and_where(
            Expr::col("char_code")
                .like("D")
                .and(Expr::col("char_code").like("E"))
        )
        .to_string(PostgresQueryBuilder),
    [
        r#"SELECT "char_code" 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 ("char_code" LIKE 'D' AND "char_code" LIKE 'E')"#,
    ]
    .join(" ")
);

Condition

If you have complex conditions to express, you can use the [Condition] builder, usable for [ConditionalStatement::cond_where] and [SelectStatement::cond_having].

assert_eq!(
    Query::select()
        .column("id")
        .from("glyph")
        .cond_where(
            Cond::any()
                .add(
                    Cond::all()
                        .add(Expr::col("aspect").is_null())
                        .add(Expr::col("image").is_null())
                )
                .add(
                    Cond::all()
                        .add(Expr::col("aspect").is_in([3, 4]))
                        .add(Expr::col("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(" ")
);

There is also the [any!] and [all!] macro at your convenience:

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%")
    ]
]);

Statement Builders

Statements are divided into 2 categories: Query and Schema, and to be serialized into SQL with [QueryStatementBuilder] and [SchemaStatementBuilder] respectively.

Schema statement has the following interface:

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

Query statement has the following interfaces:

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

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

build builds a SQL statement as string and parameters to be passed to the database driver through the binary protocol. This is the preferred way as it has less overhead and is more secure.

to_string builds a SQL statement as string with parameters injected. This is good for testing and debugging.

Query Statement

Query Select

```rust let query = Query::select() .column("char_code") .column(("font", "name")) .from("character") .left_join("font", Expr::col(("character", "font_id")).equals(("font", "id"))) .and_where(Expr::col("size_w").is_in([3, 4])) .and_where(Expr::col("char_code").like("A%")) .to_owned();

assert_eq!( query.to