Skip to main content
Version: 0.6.x 🚧

Raw SQL

Query by raw SQL

You can select Model from raw query, with appropriate syntax for binding parameters, i.e. ? for MySQL and SQLite, and $N for PostgreSQL.

let cheese: Option<cake::Model> = cake::Entity::find()
.from_raw_sql(Statement::from_sql_and_values(
DbBackend::Postgres,
r#"SELECT "cake"."id", "cake"."name" FROM "cake" WHERE "id" = $1"#,
vec![1.into()],
))
.one(&db)
.await?;

You can also select a custom model. Here, we select all unique names from cake.

#[derive(Debug, FromQueryResult)]
pub struct UniqueCake {
name: String,
}

let unique: Vec<UniqueCake> = UniqueCake::find_by_statement(Statement::from_sql_and_values(
DbBackend::Postgres,
r#"SELECT "cake"."name" FROM "cake" GROUP BY "cake"."name"#,
vec![],
))
.all(&db)
.await?;

Get raw SQL query

Use build and to_string methods on any CRUD operations to get the database-specific raw SQL for debugging purposes.

use sea_orm::DatabaseBackend;

assert_eq!(
cake_filling::Entity::find_by_id((6, 8))
.build(DatabaseBackend::MySql)
.to_string(),
vec![
"SELECT `cake_filling`.`cake_id`, `cake_filling`.`filling_id` FROM `cake_filling`",
"WHERE `cake_filling`.`cake_id` = 6 AND `cake_filling`.`filling_id` = 8",
].join(" ")
);

Use Raw Query & Execute Interface

You can build SQL statements using sea-query and query / execute it directly on the DatabaseConnection interface inside SeaORM.

Get Custom Result using query_one and query_all methods

let query_res: Option<QueryResult> = db
.query_one(Statement::from_string(
DatabaseBackend::MySql,
"SELECT * FROM `cake`;".to_owned(),
))
.await?;
let query_res = query_res.unwrap();
let id: i32 = query_res.try_get("", "id")?;

let query_res_vec: Vec<QueryResult> = db
.query_all(Statement::from_string(
DatabaseBackend::MySql,
"SELECT * FROM `cake`;".to_owned(),
))
.await?;

Execute Query using execute method

let exec_res: ExecResult = db
.execute(Statement::from_string(
DatabaseBackend::MySql,
"DROP DATABASE IF EXISTS `sea`;".to_owned(),
))
.await?;
assert_eq!(exec_res.rows_affected(), 1);