🔱 A dynamic query builder for MySQL, Postgres and SQLite
[](https://crates.io/crates/sea-query)
[](https://docs.rs/sea-query)
[](https://github.com/SeaQL/sea-query/actions/workflows/rust.yml)
## 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.
We provide integration for [SQLx](https://crates.io/crates/sqlx),
[postgres](https://crates.io/crates/postgres) and [rusqlite](https://crates.io/crates/rusqlite).
See [examples](https://github.com/SeaQL/sea-query/blob/master/examples) for usage.
SeaQuery is the foundation of [SeaORM](https://github.com/SeaQL/sea-orm), an async & dynamic ORM for Rust.
[](https://github.com/SeaQL/sea-query/stargazers/)
If you like what we do, consider starring, commenting, sharing and contributing!
[](https://discord.com/invite/uCPdDXzbdv)
Join our Discord server to chat with others in the SeaQL community!
## Install
```toml
# Cargo.toml
[dependencies]
sea-query = "0"
```
SeaQuery is very lightweight, all dependencies are optional (except `inherent`).
### Feature flags
Macro: `derive`
Async support: `thread-safe` (use `Arc` inplace of `Rc`)
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`
## Usage
Table of Content
1. Basics
1. [Iden](#iden)
1. [Expression](#expression)
1. [Condition](#condition)
1. [Statement Builders](#statement-builders)
1. Query Statement
1. [Query Select](#query-select)
1. [Query Insert](#query-insert)
1. [Query Update](#query-update)
1. [Query Delete](#query-delete)
1. Advanced
1. [Aggregate Functions](#aggregate-functions)
1. [Casting](#casting)
1. [Custom Function](#custom-function)
1. Schema Statement
1. [Table Create](#table-create)
1. [Table Alter](#table-alter)
1. [Table Drop](#table-drop)
1. [Table Rename](#table-rename)
1. [Table Truncate](#table-truncate)
1. [Foreign Key Create](#foreign-key-create)
1. [Foreign Key Drop](#foreign-key-drop)
1. [Index Create](#index-create)
1. [Index Drop](#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:
```rust
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. Dynamic query
You can construct the query at runtime based on user inputs:
```rust
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` 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.
[`Iden::unquoted()`] must be implemented to provide a mapping between Enum variants and its
corresponding string value.
```rust
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();
}
}
```
If you're okay with running another procedural macro, you can activate
the `derive` feature on the crate to save you some boilerplate.
For more usage information, look at
[the derive examples](https://github.com/SeaQL/sea-query/tree/master/sea-query-derive/tests/pass).
```rust
#[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");
```
```rust
#[cfg(feature = "derive")]
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");
```
### Expression
Use [`Expr`] to construct select, join, where and having expression in query.
```rust
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
If you have complex conditions to express, you can use the [`Condition`] builder,
usable for [`ConditionalStatement::cond_where`] and [`SelectStatement::cond_having`].
```rust
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(" ")
);
```
There is also the [`any!`] and [`all!`] macro at your convenience:
```rust
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:
```rust
fn build