SeaORM Cookbook

The SeaORM Cookbook is a collection of frequently asked questions (FAQs) and the best practice of SeaORM.

This book is incomplete and we are still working on it. Any editing and content suggestion are welcomed! Please feel free to submit a PR or file an issue on GitHub.

Tips: you can search by keyword (open the search prompt by pressing "S" key).

   ____                 ___   ____   __  __        /\
  / ___|   ___   __ _  / _ \ |  _ \ |  \/  |      {.-}
  \___ \  / _ \ / _` || | | || |_) || |\/| |     ;_.-'\
   ___) ||  __/| (_| || |_| ||  _ < | |  | |    {    _.}_
  |____/  \___| \__,_| \___/ |_| \_\|_|  |_|     \.-' /  `,
                                                  \  |    /
  An async & dynamic ORM for Rust                  \ |  ,/
  ===============================                   \|_/

Getting Started with SeaORM:

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

invitation link

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

How to load nested relation eagerly?

If you are building a web API that queries nested relations extensively, consider building a GraphQL server. Seaography is a GraphQL framework for building GraphQL resolvers using SeaORM entities. Read "Getting Started with Seaography" to learn more.

The LoaderTrait provides an API to load related entities in batches.

Consider this one to many relation:

#![allow(unused)]
fn main() {
let cake_with_fruits: Vec<(cake::Model, Vec<fruit::Model>)> = Cake::find()
    .find_with_related(Fruit)
    .all(db)
    .await?;
}

The SQL query generated is:

SELECT
    "cake"."id" AS "A_id",
    "cake"."name" AS "A_name",
    "fruit"."id" AS "B_id",
    "fruit"."name" AS "B_name",
    "fruit"."cake_id" AS "B_cake_id"
FROM "cake"
LEFT JOIN "fruit" ON "cake"."id" = "fruit"."cake_id"
ORDER BY "cake"."id" ASC

This is great, but if the N is a large number, the 1 side's (Cake) data will be duplicated a lot. This results in more data being transferred over the wire. In the many to many case, both sides may duplicate. Using the Loader would ensure each model is transferred only once. For this reason, SeaORM currently can't eager load more than 2 entities together.

The following loads the same data as above, but with two queries:

#![allow(unused)]
fn main() {
let cakes: Vec<cake::Model> = Cake::find().all(db).await?;
let fruits: Vec<Vec<fruit::Model>> = cakes.load_many(Fruit, db).await?;

for (cake, fruits) in cakes.into_iter().zip(fruits.into_iter()) { .. }
}

SELECT "cake"."id", "cake"."name" FROM "cake"
SELECT "fruit"."id", "fruit"."name", "fruit"."cake_id" FROM "fruit" WHERE "fruit"."cake_id" IN (..)

You can stack these together:

#![allow(unused)]
fn main() {
let cakes: Vec<cake::Model> = Cake::find().all(db).await?;
let fruits: Vec<Vec<fruit::Model>> = cakes.load_many(Fruit, db).await?;
let fillings: Vec<Vec<filling::Model>> = cakes.load_many_to_many(Filling, CakeFilling, db).await?;
}

In an advanced use case, you can apply filters on the related entity:

#![allow(unused)]
fn main() {
let fruits_in_stock: Vec<Vec<fruit::Model>> = cakes.load_many(
    fruit::Entity::find().filter(fruit::Column::Stock.gt(0i32))
    db
).await?;
}

Conflicting implementation of `From<M>` or `IntoActiveModel<A>`

Say, we have a entity define inside the entity crate.

#![allow(unused)]
fn main() {
use sea_orm::entity::prelude::*;

#[derive(Clone, Debug, PartialEq, Eq, DeriveEntityModel)]
#[sea_orm(table_name = "posts")]
pub struct Model {
    #[sea_orm(primary_key)]
    pub id: i32,
    pub title: String,
    pub text: String,
}

#[derive(Copy, Clone, Debug, EnumIter, DeriveRelation)]
pub enum Relation {}

impl ActiveModelBehavior for ActiveModel {}
}

And, we have a struct to hold user's input in the API crate.

#![allow(unused)]
fn main() {
#[derive(Debug)]
pub struct InsertModel {
    pub title: String,
    pub text: String,
}
}

Then, we need to convert InsertModel to entity::post::ActiveModel for inserting it to the database. One of the below impl block will perform the conversion for us. However, if you place the impl block inside the API crate you might result in compile errors.

Implements From<InsertModel> for ActiveModel:

#![allow(unused)]
fn main() {
use sea_orm::Set;
use entity::post::ActiveModel;

impl From<InsertModel> for ActiveModel {
    fn from(model: InsertModel) -> Self {
        ActiveModel {
            title: Set(model.title),
            text: Set(model.text),
            ..Default::default()
        }
    }
}
}

The error messages:

error[E0119]: conflicting implementations of trait `std::convert::From<InsertModel>` for type `entity::post::ActiveModel`
    |
    | impl From<InsertModel> for ActiveModel {
    | ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    |
    = note: conflicting implementation in crate `entity`:
            - impl std::convert::From<<Entity as EntityTrait>::Model> for ActiveModel;

Implements IntoActiveModel<ActiveModel> for InsertModel:

#![allow(unused)]
fn main() {
use sea_orm::{Set, IntoActiveModel};
use entity::post::ActiveModel;

impl IntoActiveModel<ActiveModel> for InsertModel {
    fn into_active_model(self) -> ActiveModel {
        ActiveModel {
            title: Set(self.title),
            text: Set(self.text),
            ..Default::default()
        }
    }
}
}

The error messages:

error[E0119]: conflicting implementations of trait `actix_example_core::sea_orm::IntoActiveModel<entity::post::ActiveModel>` for type `InsertModel`
    |
    | impl IntoActiveModel<ActiveModel> for InsertModel {
    | ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    |
    = note: conflicting implementation in crate `entity`:
            - impl IntoActiveModel<ActiveModel> for <Entity as EntityTrait>::Model;

Why and how to get over it?

Rust's orphan rules are the reason behind this error. We could move the InsertModel struct and its conversion implementation blocks into entity crate.

#![allow(unused)]
fn main() {
use sea_orm::{entity::prelude::*, IntoActiveModel, Set};

#[derive(Clone, Debug, PartialEq, Eq, DeriveEntityModel)]
#[sea_orm(table_name = "posts")]
pub struct Model {
    #[sea_orm(primary_key)]
    pub id: i32,
    pub title: String,
    pub text: String,
}

#[derive(Copy, Clone, Debug, EnumIter, DeriveRelation)]
pub enum Relation {}

impl ActiveModelBehavior for ActiveModel {}

// Define InsertModel and its conversion implementation blocks

#[derive(Debug)]
pub struct InsertModel {
    pub title: String,
    pub text: String,
}

impl From<InsertModel> for ActiveModel {
    fn from(model: InsertModel) -> Self {
        ActiveModel {
            title: Set(model.title),
            text: Set(model.text),
            ..Default::default()
        }
    }
}

impl IntoActiveModel<ActiveModel> for InsertModel {
    fn into_active_model(self) -> ActiveModel {
        ActiveModel {
            title: Set(self.title),
            text: Set(self.text),
            ..Default::default()
        }
    }
}
}

How to run SeaQuery statement on SeaORM?

I have a complex query written in SeaQuery and I wish to run it on SeaORM.

#![allow(unused)]
fn main() {
let attr_stmt = Query::select()
    .column(EntityAttrIden::Name.prefixed_with_table())
    .column(EntityIden::Datatype)
    .from(EntityIden::Entity)
    .join(
        JoinType::Join,
        EntityAttrIden::EntityAttribute,
        Expr::tbl(EntityIden::Entity, EntityIden::Id)
            .equals(EntityAttrIden::EntityAttribute, EntityAttrIden::EntityId),
    )
    .and_where(Expr::col(EntityIden::Name.prefixed_with_table()).eq(selector.of.clone()))
    .to_owned();
}

You need to first build the SeaQuery statement into Statement with StatementBuilder.

#![allow(unused)]
fn main() {
let attr_stmt = Query::select() // Snip...

let builder = db.get_database_backend();
let stmt: Statement = builder.build(&attr_stmt);
}

Then, you can run the statement and fetch the result:

As QueryResult

#![allow(unused)]
fn main() {
let attr_stmt = Query::select() // Snip...
let builder = db.get_database_backend();

let query_res: Option<QueryResult> = db
    .query_one(builder.build(&attr_stmt))
    .await?;

let query_res = query_res.unwrap();
let name: String = query_res.try_get("", "name")?;

let query_res_vec: Vec<QueryResult> = db
    .query_all(builder.build(&attr_stmt))
    .await?;
}

Or, as any struct that implemented FromQueryResult

#![allow(unused)]
fn main() {
let attr_stmt = Query::select() // Snip...
let builder = db.get_database_backend();

#[derive(Debug, Clone, FromQueryResult)]
struct AttributeMeta {
    name: String,
    datatype: Datatype,
}

let attributes: Vec<AttributeMeta> = AttributeMeta::find_by_statement(builder.build(&attr_stmt))
    .all(db)
    .await?
}

Entity without primary key column

In SeaORM, we assume every entity has a primary key. A table without primary key breaks many assumptions about the concept of Entity, and hence methods like find_by_id is not possible.

error: Entity must have a primary key column. See <https://github.com/SeaQL/sea-orm/issues/485> for details.
 --> src/entity/lineitem.rs:4:35
  |
4 | #[derive(Clone, Debug, PartialEq, DeriveEntityModel, Eq)]
  |                                   ^^^^^^^^^^^^^^^^^
  |
  = note: this error originates in the derive macro `DeriveEntityModel` (in Nightly builds, run with -Z macro-backtrace for more info)

We strongly recommend adding a primary key to every entity if modifying the schema is an option.

use sea_orm::entity::prelude::*;

#[derive(Clone, Debug, PartialEq, Eq, DeriveEntityModel)]
#[sea_orm(table_name = "lineitem")]
pub struct Model {
+   #[sea_orm(primary_key)]
+   pub id: i32,
    pub price: Decimal,
    pub quantity: i32,
    pub order_id: i32,
    pub cake_id: i32,
}

#[derive(Copy, Clone, Debug, EnumIter, DeriveRelation)]
pub enum Relation {
    // ...
}

impl ActiveModelBehavior for ActiveModel {}

Select rows matching any of the enum values

Let say, we want to select any breakfast serving everyday tea and breakfast tea.

#![allow(unused)]
fn main() {
use sea_orm::entity::prelude::*;

// `Tea` enum
#[derive(Debug, Clone, PartialEq, Eq, EnumIter, DeriveActiveEnum)]
#[sea_orm(rs_type = "String", db_type = "Enum", enum_name = "tea")]
pub enum Tea {
    #[sea_orm(string_value = "EverydayTea")]
    EverydayTea,
    #[sea_orm(string_value = "BreakfastTea")]
    BreakfastTea,
    #[sea_orm(string_value = "EarlGreyTea")]
    EarlGreyTea,
}
}

#![allow(unused)]
fn main() {
use sea_orm::entity::prelude::*;

// `breakfast` entity
#[derive(Clone, Debug, PartialEq, Eq, DeriveEntityModel)]
#[sea_orm(table_name = "breakfast")]
pub struct Model {
    #[sea_orm(primary_key)]
    pub id: i32,
    pub bread: String,
    pub tea: Tea,
}

#[derive(Copy, Clone, Debug, EnumIter, DeriveRelation)]
pub enum Relation {}

impl ActiveModelBehavior for ActiveModel {}
}

We can perform the select as follows:

#![allow(unused)]
fn main() {
use sea_orm::sea_query::{BinOper, Expr};

// Filter `IS IN (EverydayTea, BreakfastTea)`
let rows: Vec<breakfast::Model> = Entity::find()
    .filter(
        Expr::col(breakfast::Column::Tea)
            .binary(BinOper::In, Expr::tuple([
                Tea::EverydayTea.as_enum(),
                Tea::BreakfastTea.as_enum(),
            ]))
    )
    .all(db)
    .await?
    .unwrap();

// Or, inverse of it, filter `IS NOT IN (EarlGreyTea)`
let rows: Vec<breakfast::Model> = Entity::find()
    .filter(
        Expr::col(breakfast::Column::Tea)
            .binary(BinOper::NotIn, Expr::tuple([Tea::EarlGreyTea.as_enum()]))
    )
    .all(db)
    .await?
    .unwrap();
}

Set column value with database function

How can we set the expiry date of all fruits contains the word "Apple"?

#![allow(unused)]
fn main() {
use sea_orm::entity::prelude::*;

#[derive(Clone, Debug, PartialEq, Eq, DeriveEntityModel)]
#[sea_orm(table_name = "fruit")]
pub struct Model {
    #[sea_orm(primary_key)]
    pub id: i32,
    pub name: String,
    pub expiry: Option<DateTimeWithTimeZone>,
}

#[derive(Copy, Clone, Debug, EnumIter, DeriveRelation)]
pub enum Relation {}

impl ActiveModelBehavior for ActiveModel {}
}

Since update one only takes value instead of expression. We need update many for this purpose.

#![allow(unused)]
fn main() {
use sea_orm::sea_query::Expr;

// UPDATE `fruit` SET `expiry` = CURRENT_TIMESTAMP WHERE `fruit`.`name` LIKE '%Apple%'
fruit::Entity::update_many()
    .col_expr(fruit::Column::Expiry, Expr::current_timestamp())
    .filter(fruit::Column::Name.contains("Apple"))
    .exec(db)
    .await?;
}

Run migration at application startup

How to apply all pending migration at application startup?

You can run the migration programmatically at startup by providing the database connection to the Migrator::up method.

#![allow(unused)]
fn main() {
// Initialize database connection
let db = Database::connect(&db_url).await?;

// Run all pending migrations
Migrator::up(&conn, None).await?;
}

Checkout the examples below:

`Iden` trait is not implemented

The Iden trait is a construct to represent identifier in SQL statement.

Static Identifier

Usually, identifier is static and Enum is a natural representation of it. You can define and implement Iden with the help of the derive macros.

#![allow(unused)]
fn main() {
use sea_orm::sea_query::Iden;

#[derive(Debug, Iden)]
enum User {
    Table,
    Id,
}
assert_eq!(Iden::to_string(&User::Table), "user");
assert_eq!(Iden::to_string(&User::Id), "id");

#[derive(Debug, Iden)]
#[iden = "user"]
enum Custom {
    Table,
    #[iden = "my_id"]
    Id,
    FirstName,
}
assert_eq!(Iden::to_string(&Custom::Table), "user");
assert_eq!(Iden::to_string(&Custom::Id), "my_id");
assert_eq!(Iden::to_string(&Custom::FirstName), "first_name");
}

Dynamic Identifier

However, if you have a dynamic identifier constructed at runtime, then you need Alias for this purpose.

#![allow(unused)]
fn main() {
use sea_orm::sea_query::{Alias, Iden};

let iden = Alias::new("id");
assert_eq!(Iden::to_string(&iden), "id");
}

Stream query result - no method named try_next

You can stream the query result to reduce memory allocation and improve efficiency.

#![allow(unused)]
fn main() {
// Stream all fruits
let mut stream = Fruit::find().stream(db).await?;

while let Some(item) = stream.try_next().await? {
    let item: fruit::ActiveModel = item.into();
    // do something with item
}
}

However, you might encountered an error like no method named try_next. If that so, you need to update your Cargo.toml at your project root.

futures = "0.3"

Then, import futures::TryStreamExt into the scope.

#![allow(unused)]
fn main() {
// Import the trait where `try_next` method is defined.
use futures::TryStreamExt;

// Stream all fruits
let mut stream = Fruit::find().stream(db).await?;

while let Some(item) = stream.try_next().await? {
    let item: fruit::ActiveModel = item.into();
    // do something with item
}
}

You're good to go. Compile the project again and enjoy!

How to mutate the underlying SeaQuery statement?

How can I borrow a mutable reference of sea_query::SelectStatement out of Entity::find(). So that I could add my custom window expression and select from a custom view etc.

You can use the QueryTrait::query() method to borrow the underlying SeaQuery statement out of any SeaORM types implemented QueryTrait (listed here).

use sea_orm::QueryTrait;

let find = cake::Entity::find();

// Borrow `sea_query::SelectStatement` and customize it
let select_stmt: &mut sea_query::SelectStatement = find.query()
    .expr_window_as(
        Func::cust(RowNumber),
        WindowStatement::partition_by(...),
        Alias::new(...),
    )
    .from(...);

How to define a struct with DeriveIntoActiveModel outside entity module?

I know I can define a custom struct and derive DeriveIntoActiveModel which implements IntoActiveModel for me.

use sea_orm::entity::prelude::*;

#[derive(Clone, Debug, PartialEq, Eq, DeriveEntityModel)]
#[sea_orm(table_name = "fruit")]
pub struct Model {
    #[sea_orm(primary_key)]
    pub id: i32,
    pub name: String,
    pub cake_id: Option<i32>,
}

#[derive(Copy, Clone, Debug, EnumIter, DeriveRelation)]
pub enum Relation {}

impl ActiveModelBehavior for ActiveModel {}

// My custom ActiveModel
#[derive(DeriveIntoActiveModel)]
pub struct NewFruit {
    pub name: String,
    pub cake_id: i32,
}

However, how can I define such custom struct in another module?

All you need is one extra line that import the target ActiveModel into the scope.

mod post_model {
    // Import the target ActiveModel into scope
    use super::entity::cake::ActiveModel;

    // My custom ActiveModel
    #[derive(DeriveIntoActiveModel)]
    pub struct NewFruit {
        pub name: String,
        pub cake_id: i32,
    }
}

How to make a static Database Connection

Read first: Documentation on Connection Pool

Everytime we access the database, we need to establish a connection, or as we call it, a "Connection Pool" beforehand. The most trivial way is to create the connection everytime we need to access it:

#![allow(unused)]
fn main() {
// Establish connection
let db = Database::connect(url).await?;

/* Some operations on db */

// Close connection
db.close().await?;
}

This connection is, however, only locally accessible, and each establishment of connection is very costly. To solve it, one would like to create a static connection that only has to be established once.

use tokio::sync::OnceCell;

static DB_CLIENT: OnceCell<DatabaseConnection> = OnceCell::const_new();

#[tokio::main]
async fn main() {
    DB_CLIENT
        .get_or_init(|| async {
            let database_url = "database_url".to_string();
            let mut opt = ConnectOptions::new(database_url);
            Database::connect(opt).await.unwrap()
        })
        .await;

    let conn = DB_CLIENT.get().unwrap();

}

Now any scopes inside of the same module can access the DB_CLIENT static variable and use it as a connection to the database without establishing connection everytime. For example

#![allow(unused)]
fn main() {
fn a_function() -> Result<(), !> {
    DB_CLIENT.do_something().await?;
}
}

For DB_CLIENT to be visible from other modules, simply add pub in front of static DB_CLIENT:....

How to use custom expressions and functions

Custom Expressions

Custom expressions are merely just SimpleExpr that you write yourself in SQL syntax instead of using the library's API. Many functions uses this type as parameters, see this.

Assume we have the following table:

#![allow(unused)]
fn main() {
thing {
    id: i32,
    field: String,
}
}

Here is one example of the use of Expr::cust:

#![allow(unused)]
fn main() {
Query::select()
    .column(thing::Column::Id)
    .from(thing::Table)
    .and_where(Expr::cust("field = \"asdf\""));

Query::select()
    .column(thing::Column::Id)
    .from(thing::Table)
    .and_where(Expr::col(thing::Column::Field).eq("asdf"));
}

They both evaluate to SELECT id FROM thing WHERE field = "asdf"

2 more advanced versions of Expr::cust are Expr::cust_with_exprs and Expr::cust_with_values, where IntoIterator<Item = Into<SimpleExpr>> and IntoIterator<Item = Into<Value>> parameters are accepted in addition to format the given string.

The example below is self explanatory:

#![allow(unused)]
fn main() {
let values = ["asdf", "fdas"];

// Evaluates to `SELECT id FROM thing WHERE field = "asdf" OR field = "fdsa"`
Query::select()
    .column(thing::Column::Id)
    .from(thing::Table)
    .and_where(Expr::cust_with_values("field = ? OR field = ?", values));

// Evaluates to `SELECT id FROM thing WHERE field = "fdsa" OR field = "asdf"`
// note the difference in order
Query::select()
    .column(thing::Column::Id)
    .from(thing::Table)
    .and_where(Expr::cust_with_values("field = $2 OR field = $1", values));
}

Expr::cust_with_exprs's usage is the exact same except only types that implement Into<SimpleExpr> are accepted.

Custom Functions

Custom functions are those defined in the following ways: MySQL, PostgreSQL, and SQLite.

SeaQuery provides a way to systematically call the custom functions, by the use of Iden and Func::cust.

Assume we have defined a function called MY_FUNCTION in the database. Before we are able to invoke it, a struct that implements Iden should be defined first since Func::cust accepts a IntoIden:

#![allow(unused)]
fn main() {
// Method 1: Do it the hard way
struct MyFunction;

impl Iden for MyFunction {
    fn unquoted(&self, s: &mut dyn Write) {
        write!(s, "MY_FUNCTION").unwrap();
    }
}

// Method 2: Do it the macro way
#[derive(Iden)]
#[iden = "MY_FUNCTION"]
struct MyFunction;
}

Now we can use Func::cust:

#![allow(unused)]
fn main() {
// `MY_FUNCTION()`
Func::cust(MyFunction);

// `MY_FUNCTION('hello')`
Func::cust(MyFunction).arg("hello");

// `MY_FUNCTION('a', 'b', 'c', 'd')`
Func::cust(MyFunction).args(["a", "b", "c", "d"]);
}

Func::cust can be used in many places, with the following being a few of them:

#![allow(unused)]
fn main() {
// `SELECT MY_FUNCTION('hello')`
Query::select()
    .expr(Func::cust(MyFunction).arg("hello"));

// `SELECT * FROM thing WHERE MY_FUNCTION(IFNULL('field', 'asdf'))`
Query::select()
    .from(thing::Table)
    .and_where(
        Func::cust(MyFunction).arg(
            Expr::col(thing::Column::Field)
                .if_null("asdf")
            )
        );
}

Create database connections lazily

Note that this means a new connection has to be established everytime you access the database, which might cause performance and desynchronisation issue. For example, your program might not know the database is defective until the first access.

It is possible to create a DatabaseConnection object with no minimum active connection in the connection pool until needed. The method is to create a SQLx connection pool with connect_lazy function then convert it into DatabaseConnection:

#![allow(unused)]
fn main() {
// MySQL
let mysql_pool = sqlx::MySqlPool::connect_lazy("mysql://root:root@localhost").unwrap();
let db: sea_orm::DatabaseConnection = sea_orm::SqlxMySqlConnector::from_sqlx_mysql_pool(mysql_pool);

// Postgres
let pg_pool = sqlx::PgPool::connect_lazy("postgres://root:root@localhost").unwrap();
let db: sea_orm::DatabaseConnection = sea_orm::SqlxPostgresConnector::from_sqlx_postgres_pool(pg_pool);

// SQLite
let sqlite_pool = sqlx::SqlitePool::connect_lazy("sqlite::memory:").unwrap();
let db: sea_orm::DatabaseConnection = sea_orm::SqlxSqliteConnector::from_sqlx_sqlite_pool(sqlite_pool);
}

Configuring Schema Search Path in PostgreSQL

This only works if you use PostgreSQL, and will have no effect on any other databases supported. For documentations on the schema search path settings in PostgreSQL, see here.

The ConnectOptions object allows for the configuration of search path:

#![allow(unused)]
fn main() {
let mut options = ConnectOptions::new(url);

// You need to define `path` yourself!
options.set_schema_search_path(path);

// Obtain the DatabaseConnection object here
Database::connect(options).await?
}

Automatically execute SQL after connecting to database

SeaORM does not provide direct support for it, but it is still possible. It involves establishing the connection with sqlx then converting it into a sea_orm::DatabaseConnection object.

#![allow(unused)]
fn main() {
// Only copy the one that you are using
type DB = sqlx::Postgres;
type DB = sqlx::Sqlite;
type DB = sqlx::MySql;
}

Creation of sqlx options:

#![allow(unused)]
fn main() {
// Method 1: Convert from the `sea_orm::ConnectOptions` that you are familiar with
let mut sqlx_options: sqlx::pool::PoolOptions<DB>
    = sea_options.pool_options().after_connect(callback);

// Method 2: Create option directly in sqlx space
let mut sqlx_options = sqlx::pool::PoolOptions<DB>::new()
    // See https://docs.rs/sqlx/latest/sqlx/pool/struct.PoolOptions.html
    .after_connect(callback);
}

Connect to database with sqlx:

#![allow(unused)]
fn main() {
let sqlx_pool: Pool<DB> = sqlx_options.connect(url).await?;
}

Convert to SeaORM's DatabaseConnection:

#![allow(unused)]
fn main() {
/// MySQL
from_sqlx_mysql_pool(sqlx_pool)
/// SQLite
from_sqlx_sqlite_pool(sqlx_pool)
/// PostgreSQL
from_sqlx_postgres_pool(sqlx_pool)
}

Raw and Unprepared Statements

Raw SQL

Documentation and Examples in the docs

While it is highly recommended to use SeaQuery's generalized interface for creating queries, it is possible to convert raw SQL statements into queries that the API accepts, with:

Note that they return Self, so you can use the result in any situations where they are respectively accepted.

Also note that this method requires knowing the type of database backend you are using at compile time, and you are responsible for giving it the correct sql statements.

Unprepared SQL

This method might be vulnerable to SQL injection!

An unprepared SQL are ones that are directly sent to the database without being processed by the ORM. It is highly not recommended unless you can prove the statements' correctness and security.

To send an unprepared SQL statement:

#![allow(unused)]
fn main() {
let db: DatabaseConnection = { /* */ };

// One statement
db.execute_unprepared("CREATE EXTENSION IF NOT EXISTS citext").await?

// Multiple statements
db.execute_unprepared("STATEMENT1; STATEMENT2; ...").await?
}

Select JSON or tuple query result

For example we have the following table:

#![allow(unused)]
fn main() {
Person {
    id: i32,
    name: String,
    age: u16,
}
}

Normally, we want the whole record, and do:

#![allow(unused)]
fn main() {
let result: Option<person::Model> = person::Entity::find_by_id(id).one(&db).await?;
}

This returns a person::Model object. But sometimes we just want some of the columns, for example, only name:

#![allow(unused)]
fn main() {
let result: Option<String> = person::Entity::find_by_id(id)
        .select_only()
        .column(person::Column::Name)
        .into_tuple()
        .one(&db)
        .await?;
}

For multiple columns, it can also output in JSON or tuple:

#![allow(unused)]
fn main() {
// JSON
let result: Option<sea_orm::query::JsonValue> = person::Entity::find_by_id(id)
        .select_only()
        .columns([person::Column::Name, person::Column::Age])
        .into_json()
        .one(&db)
        .await?;

// Tuple
let result: Option<(String, u16)> = person::Entity::find_by_id(id)
        .select_only()
        .column_as([person::Column::Name, person::Column::Age])
        .into_tuple()
        .one(&db)
        .await?;
}

Check if columns in ActiveModel are set

Given a ActiveModel object, one can check if a specific column is set:

#![allow(unused)]
fn main() {
let object: table::ActiveModel = { ... };

// Specific field
object.field_name.is_set()  // <- do something with this boolean

// Field iterator
for column in table::Column::iter() {
    object.get(column).is_set() // <- do something with this boolean
}
}

Count number of selected result

A naive way to approach this problem is to fetch the selection from the database, have it converted to a vector, then check the length of the vector. This is however, very expensive, and SeaORM provides a PaginatorTrait::count method to count without actually fetching it. PaginatorTrait is implemented by Select, SelectTwo, Selector, and SelectorRaw. So one can get the count by first creating such object, then call the method. For example:

#![allow(unused)]
fn main() {
// Create a Select object
let selection = table::Entity::find();

// Count the number of selection
selection.count(db).await?
}

Bulk ActiveModel field setting

Imagine the situation where one has the table:

#![allow(unused)]
fn main() {
TableName {
    id: i32,
    field1: T,
    field2: T,
    ...
    fieldN: T,
}
}

To create an ActiveModel of this table, one can painstakingly set each field to ActiveValue<T>::NotSet:

#![allow(unused)]
fn main() {
let new_thing = table_name::ActiveModel {
    id: ActiveValue::NotSet,
    field1: ActiveValue::NotSet,
    field2: ActiveValue::NotSet,
    // ...
    fieldN: ActiveValue::NotSet,
};
}

or implement the Default trait which I will not show here.

However, I shall enlighten you with a trick within SeaORM - creating the model with JSON:

#![allow(unused)]
fn main() {
let json = json!({ "fieldX": value });

let new_thing = table_name::ActiveModel::from_json(json)?;
}

Where new_thing will evaluate to fieldX being ActiveValue<T>::Set and all other fields set to ActiveValue<T>::NotSet automatically.

Setting of field values can also be performed on existing instances of ActiveModel with ActiveModelTrait::set_from_json. Note that this method will not change the primary key value if it is set.

Database Change Capture

Documentation on transaction in SeaORM: https://www.sea-ql.org/SeaORM/docs/advanced-query/transaction/, and see here for definitions

Sometimes it is important to have a log/history of what is changed in the database. One way to do this is to enclose the changes in a transaction, and perform the change capturing.

For example, here is a bundle of validation and change capture, all done within a transaction:

#![allow(unused)]
fn main() {
// Some kind of error type
type E;

// Function to perform change capture
async fn change_capture(object: &table_name::ActiveModel) -> Result<(), E> { ... }

// Function to perform validation
async fn validate(object: &table_name::ActiveModel) -> Result<(), E> { ... }

// ...

// Perform the transaction sandwiched between the validation and change capture
db.transaction::<_, ?, E>(|txn| {
    Box::pin(async move {
        validate(&object).await?;

        object.operation().await?;

        change_capture(&object).await?;

        Ok(?)
    })
})
}

One thing about this method is that the transaction will be automatically cancelled if anything throws an error, which includes changes to the database done during change capture.

Defining newtypes

Read the documentations at https://www.sea-ql.org/SeaORM/docs/generate-entity/newtype/

Note that any newtype defined will have to be convertible to one of the traditional database types defined in sea_query::value::Value, so use existing types unless really necessary.

ActiveModelBehavior not being triggered

Read first: https://docs.rs/sea-orm/latest/sea_orm/entity/trait.ActiveModelBehavior.html

Be careful that the behaviors only apply to the following methods:

It is possible to perform similar action with the functions of EntityTrait, but they will not trigger the behaviors, by design.

Subquery

Read first: Introduction to sub query in SQL

Also read: Documentation on subquery in SeaORM

As it is not mentioned explicitly in the documentation, one might think that SeaORM only supports subquery for WHERE..IN.. as in the documentation. However, looking at the documentation of SeaQuery, and search for subquery, one shall be presented with that SeaQuery supports subquery just like any other expressions.

A Select/Insert/Update/Delete/With Statement can be converted to a SubQueryStatement, then into SimpleExpr, with the variant SimpleExpr::SubQuery(Option<SubQueryOper>, Box<SubQueryStatement>).

The application is trivial and has been left as an exercise for the reader.