Sea-ORM Introduction

Sea-ORM is an async/await ORM for Rust, providing an "Entity/Model/ActiveModel" data access paradigm and migration toolchain. It works well with Axum/Tokio and is suitable for implementing a stable, maintainable database layer in QiLuo.

You will learn:

• Core concepts: Entity (table/columns), Model (read-only data), ActiveModel (writable data)
• CRUD, conditional queries, pagination/sorting
• Relationships & eager loading (1-1/1-N/N-N)
• Transactions, soft deletes, event hooks (ActiveModelBehavior)
• JSON/enum type mapping & serialization

Supported Databases:

• PostgreSQL, MySQL/MariaDB, SQLite

Directory Structure & Setup

Dependencies:

# Cargo.toml
sea-orm = { version = "0.12", features = ["sqlx-postgres", "runtime-tokio-native-tls", "macros", "with-chrono", "with-json"] }
sea-orm-migration = "0.12"
chrono = { version = "0.4", features = ["serde"] }
serde = { version = "1", features = ["derive"] }

Recommended structure:

src/
  db/
    entities/          # Auto-generated by sea-orm-cli
    mod.rs             # Connection & multi-DataSource
migration/             # Migration crate or module

Entity/Model/ActiveModel

• Entity: Represents a table (with column definitions, primary keys, relationships)
• Model: A "read-only" structure for a row (query results)
• ActiveModel: A "writable" structure, using Set(NotSet) to mark columns for writing

Example (simplified user table):

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

#[derive(Clone, Debug, PartialEq, DeriveEntityModel, serde::Serialize, serde::Deserialize)]
#[sea_orm(table_name = "sys_user")]
pub struct Model {
    #[sea_orm(primary_key)]
    pub id: i64,
    pub username: String,
    pub password_hash: String,
    pub real_name: Option<String>,
    pub dept_id: Option<i64>,
    pub status: i32,
    pub created_at: DateTimeUtc,
    pub updated_at: DateTimeUtc,
    pub deleted_at: Option<DateTimeUtc>,
}

#[derive(Copy, Clone, Debug, EnumIter, DeriveRelation)]
pub enum Relation {
    #[sea_orm(belongs_to = "super::sys_dept::Entity", from = "Column::DeptId", to = "super::sys_dept::Column::Id")]
    Dept,
}

impl ActiveModelBehavior for ActiveModel {}

Generate Entities (CLI)

cargo install sea-orm-cli
sea-orm-cli generate entity -o src/db/entities -t sys_user

CRUD Operations

Insert:

use sea_orm::{ActiveModelTrait, Set};
let user = entity::sys_user::ActiveModel {
    username: Set("alice".to_owned()),
    password_hash: Set(hash),
    ..Default::default()
};
let result = user.insert(&db).await?;

Update:

let mut am: entity::sys_user::ActiveModel = entity::sys_user::Entity::find_by_id(1001)
    .one(&db).await?.ok_or_else(|| anyhow::anyhow!("not found"))?
    .into_active_model();
am.username = Set("bob".to_owned());
am.updated_at = Set(chrono::Utc::now());
let user = am.update(&db).await?;

Delete (hard):

entity::sys_user::Entity::delete_by_id(1001).exec(&db).await?;

Soft Delete (recommended):

let mut am: entity::sys_user::ActiveModel = entity::sys_user::Entity::find_by_id(1001)
    .one(&db).await?.ok_or_else(|| anyhow::anyhow!("not found"))?
    .into_active_model();
am.deleted_at = Set(Some(chrono::Utc::now()));
am.update(&db).await?;

Pagination & Transactions

Pagination:

use sea_orm::{QuerySelect, PaginatorTrait};
let paginator = entity::sys_user::Entity::find().paginate(&db, 20);
let total = paginator.num_items().await?;
let items = paginator.fetch_page(0).await?;

Transaction:

use sea_orm::{TransactionTrait, Set};
db.transaction(|txn| {
    Box::pin(async move {
        // Multi-table operations...
        Ok(())
    })
}).await?;

Integration with QiLuo

• Migrations first: all table structure changes go through migrations
• Generated entities placed under src/db/entities
• Service layer encapsulates queries and business logic
• Soft delete fields (deleted_at) and audit fields follow a consistent style