Skip to main content

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 Postgres.

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);