Optional: Building SQL Queries with SeaQuery
If you prefer the flexibility of SQL, you can use SeaQuery to build SQL-like statements for any queries or operations.
SeaQuery is built-in for SeaORM, so no extra setup is required.
Insert statements
Raw SQL:
INSERT INTO `bakery` (`name`, `profit_margin`) VALUES ('SQL Bakery', -100)
SeaQuery:
#![allow(unused)] fn main() { use sea_query::{Alias, Query}; let columns: Vec<Alias> = ["name", "profit_margin"] .into_iter() .map(Alias::new) .collect(); let mut stmt = Query::insert(); stmt.into_table(bakery::Entity).columns(columns); // Invoke `values_panic()` for each row stmt.values_panic(["SQL Bakery".into(), (-100.0).into()]); let builder = db.get_database_backend(); db.execute(builder.build(&stmt)).await?; }
Select statements
Raw SQL:
SELECT `chef`.`name` FROM `chef` JOIN `bakery` ON `chef`.`bakery_id` = `bakery`.`id` ORDER BY `chef`.`name` ASC
SeaQuery:
If you are only interested in some of the columns, define a struct to hold the query result. It has to derive from the trait FromQueryResult
.
If all columns are of interest, then the generated Model
structs (e.g. chef::Model
) can be used.
The fields of the struct must match the column names of the query result.
#![allow(unused)] fn main() { use sea_query::{Alias, Expr, JoinType, Order, Query}; #[derive(FromQueryResult)] struct ChefNameResult { name: String, } ... let column = (chef::Entity, Alias::new("name")); let mut stmt = Query::select(); stmt.column(column.clone()) // Use `expr_as` instead of `column` if renaming is necessary .from(chef::Entity) .join( JoinType::Join, bakery::Entity, Expr::tbl(chef::Entity, Alias::new("bakery_id")) .equals(bakery::Entity, Alias::new("id")), ) .order_by(column, Order::Asc); let builder = db.get_database_backend(); let chef = ChefNameResult::find_by_statement(builder.build(&stmt)) .all(db) .await?; let chef_names = chef.into_iter().map(|b| b.name).collect::<Vec<_>>(); assert_eq!( chef_names, vec!["Charles", "Frederic", "Jolie", "Madeleine"] ); }
Testing and Debugging
It's often useful to check the raw SQL of the SeaQuery-generated statements.
Use stmt.to_string(query_builder)
to do that.
#![allow(unused)] fn main() { // Check the raw SQL of `stmt` in MySQL syntax println!({}, stmt.to_string(MysqlQueryBuilder)); }