codeloopback
Guide contents

Lesson 9 of 30

SeaORM: Entities, Relations, and ActiveModel

SeaORM 1.1 is an async object-relational mapper (ORM) built on SQLx. An ORM maps tables to Rust types and builds SQL from method calls. This lesson builds a Storefront service with products and orders on PostgreSQL, using entities, ActiveModel, batched relation loading, and transactions. The schema follows Relationships, indexes, and N+1.

Project setup

Start PostgreSQL and create the tables. Wait a few seconds after docker run until PostgreSQL accepts connections before the psql line.

Terminal
docker run --name pg -e POSTGRES_PASSWORD=pw -e POSTGRES_DB=storefront -p 5432:5432 -d postgres:16
docker exec -i pg psql -U postgres -d storefront < migrations/0001_init.sql
export DATABASE_URL=postgres://postgres:pw@127.0.0.1:5432/storefront

Cargo.toml

TOML
# Cargo.toml
[package]
name = "storefront-seaorm"
version = "0.1.0"
edition = "2021"

[dependencies]
axum = "0.8"
sea-orm = { version = "1.1", features = ["sqlx-postgres", "runtime-tokio-rustls", "macros"] }
serde = { version = "1", features = ["derive"] }
serde_json = "1"
tokio = { version = "1", features = ["full"] }

migrations/0001_init.sql

SQL
-- migrations/0001_init.sql
CREATE TABLE products (
    id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    sku         TEXT        NOT NULL UNIQUE,
    name        TEXT        NOT NULL,
    price_cents INTEGER     NOT NULL CHECK (price_cents > 0),
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE orders (
    id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id BIGINT      NOT NULL,
    status      TEXT        NOT NULL DEFAULT 'pending',
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE order_lines (
    order_id                BIGINT  NOT NULL REFERENCES orders (id) ON DELETE CASCADE,
    line_no                 INTEGER NOT NULL,
    product_id              BIGINT  NOT NULL REFERENCES products (id),
    quantity                INTEGER NOT NULL CHECK (quantity > 0),
    price_at_purchase_cents INTEGER NOT NULL CHECK (price_at_purchase_cents > 0),
    PRIMARY KEY (order_id, line_no)
);

CREATE INDEX orders_customer_idx ON orders (customer_id);
CREATE INDEX order_lines_product_idx ON order_lines (product_id);

Example 1: Entities

An entity file has a Model struct with one field per column. DeriveEntityModel generates the Entity, Column, PrimaryKey, and ActiveModel types from it. The Relation enum lists foreign keys, and each Related impl lets one entity navigate to another.

src/entities/mod.rs

Rust
// src/entities/mod.rs
pub mod order;
pub mod order_line;
pub mod product;

src/entities/product.rs

Rust
// src/entities/product.rs
use sea_orm::entity::prelude::*;
use serde::Serialize;

#[derive(Clone, Debug, PartialEq, Eq, DeriveEntityModel, Serialize)]
#[sea_orm(table_name = "products")]
pub struct Model {
    #[sea_orm(primary_key)]
    pub id: i64,
    #[sea_orm(unique)]
    pub sku: String,
    pub name: String,
    pub price_cents: i32,
    #[serde(skip)]
    pub created_at: DateTimeUtc,
}

#[derive(Copy, Clone, Debug, EnumIter, DeriveRelation)]
pub enum Relation {
    #[sea_orm(has_many = "super::order_line::Entity")]
    OrderLines,
}

impl Related<super::order_line::Entity> for Entity {
    fn to() -> RelationDef {
        Relation::OrderLines.def()
    }
}

impl ActiveModelBehavior for ActiveModel {}

src/entities/order.rs

Rust
// src/entities/order.rs
use sea_orm::entity::prelude::*;

#[derive(Clone, Debug, PartialEq, Eq, DeriveEntityModel)]
#[sea_orm(table_name = "orders")]
pub struct Model {
    #[sea_orm(primary_key)]
    pub id: i64,
    pub customer_id: i64,
    pub status: String,
    pub created_at: DateTimeUtc,
}

#[derive(Copy, Clone, Debug, EnumIter, DeriveRelation)]
pub enum Relation {
    #[sea_orm(has_many = "super::order_line::Entity")]
    OrderLines,
}

impl Related<super::order_line::Entity> for Entity {
    fn to() -> RelationDef {
        Relation::OrderLines.def()
    }
}

impl ActiveModelBehavior for ActiveModel {}

src/entities/order_line.rs

Rust
// src/entities/order_line.rs
use sea_orm::entity::prelude::*;

#[derive(Clone, Debug, PartialEq, Eq, DeriveEntityModel)]
#[sea_orm(table_name = "order_lines")]
pub struct Model {
    #[sea_orm(primary_key, auto_increment = false)]
    pub order_id: i64,
    #[sea_orm(primary_key, auto_increment = false)]
    pub line_no: i32,
    pub product_id: i64,
    pub quantity: i32,
    pub price_at_purchase_cents: i32,
}

#[derive(Copy, Clone, Debug, EnumIter, DeriveRelation)]
pub enum Relation {
    #[sea_orm(
        belongs_to = "super::order::Entity",
        from = "Column::OrderId",
        to = "super::order::Column::Id"
    )]
    Order,
    #[sea_orm(
        belongs_to = "super::product::Entity",
        from = "Column::ProductId",
        to = "super::product::Column::Id"
    )]
    Product,
}

impl Related<super::order::Entity> for Entity {
    fn to() -> RelationDef {
        Relation::Order.def()
    }
}

impl Related<super::product::Entity> for Entity {
    fn to() -> RelationDef {
        Relation::Product.def()
    }
}

impl ActiveModelBehavior for ActiveModel {}
  • #[sea_orm(primary_key)] marks the key. An integer key is treated as auto-increment, so ActiveModel leaves it NotSet on insert and the database assigns it.
  • order_line has a composite key. Each key field carries auto_increment = false.
  • #[serde(skip)] keeps created_at out of the JSON, so responses are stable.
  • sea-orm-cli generate entity can produce these files from an existing database. The generated files are a starting point to review and commit.

Example 2: Connect and read products

Database::connect returns a DatabaseConnection, which wraps an SQLx pool and is cheap to clone. Queries start from Entity::find() and filter with the generated Column enum, so a misspelled column is a compile error.

src/error.rs

Rust
// src/error.rs
use axum::{
    http::StatusCode,
    response::{IntoResponse, Response},
    Json,
};
use sea_orm::{DbErr, SqlErr};
use serde_json::json;

pub enum ApiError {
    NotFound,
    Conflict,
    BadRequest(&'static str),
    Internal,
}

impl From<DbErr> for ApiError {
    fn from(err: DbErr) -> Self {
        match err.sql_err() {
            Some(SqlErr::UniqueConstraintViolation(_)) => ApiError::Conflict,
            Some(SqlErr::ForeignKeyConstraintViolation(_)) => {
                ApiError::BadRequest("unknown reference")
            }
            _ => {
                eprintln!("database error: {err}");
                ApiError::Internal
            }
        }
    }
}

impl IntoResponse for ApiError {
    fn into_response(self) -> Response {
        let (status, message) = match self {
            ApiError::NotFound => (StatusCode::NOT_FOUND, "not found"),
            ApiError::Conflict => (StatusCode::CONFLICT, "conflict"),
            ApiError::BadRequest(message) => (StatusCode::BAD_REQUEST, message),
            ApiError::Internal => (StatusCode::INTERNAL_SERVER_ERROR, "internal error"),
        };
        (status, Json(json!({ "error": message }))).into_response()
    }
}

src/products.rs

Rust
// src/products.rs
use axum::{
    extract::{Path, Query, State},
    Json,
};
use sea_orm::{ColumnTrait, EntityTrait, QueryFilter, QueryOrder, QuerySelect};
use serde::Deserialize;

use crate::{entities::product, error::ApiError, AppState};

#[derive(Deserialize)]
pub struct ProductFilter {
    max_price: Option<i32>,
    name: Option<String>,
}

pub async fn get_one(
    State(state): State<AppState>,
    Path(id): Path<i64>,
) -> Result<Json<product::Model>, ApiError> {
    let found = product::Entity::find_by_id(id).one(&state.db).await?;
    found.map(Json).ok_or(ApiError::NotFound)
}

pub async fn list(
    State(state): State<AppState>,
    Query(filter): Query<ProductFilter>,
) -> Result<Json<Vec<product::Model>>, ApiError> {
    let mut query = product::Entity::find();
    if let Some(max) = filter.max_price {
        query = query.filter(product::Column::PriceCents.lte(max));
    }
    if let Some(name) = filter.name {
        query = query.filter(product::Column::Name.contains(name));
    }
    let rows = query
        .order_by_asc(product::Column::Id)
        .limit(50)
        .all(&state.db)
        .await?;
    Ok(Json(rows))
}

src/main.rs

Rust
// src/main.rs
mod entities;
mod error;
mod products;

use std::time::Duration;

use axum::{
    routing::{get, post},
    Router,
};
use sea_orm::{ConnectOptions, Database, DatabaseConnection};

#[derive(Clone)]
pub struct AppState {
    pub db: DatabaseConnection,
}

#[tokio::main]
async fn main() {
    let url = std::env::var("DATABASE_URL").expect("DATABASE_URL must be set");
    let mut options = ConnectOptions::new(url);
    options
        .max_connections(10)
        .min_connections(1)
        .acquire_timeout(Duration::from_secs(3))
        .sqlx_logging(false);
    let db = Database::connect(options).await.expect("database connection");

    let app = Router::new()
        .route("/products", get(products::list))
        .route("/products/{id}", get(products::get_one))
        .with_state(AppState { db });

    let listener = tokio::net::TcpListener::bind("127.0.0.1:3000").await.unwrap();
    axum::serve(listener, app).await.unwrap();
}
  • one returns Option<Model> and all returns Vec<Model>.
  • Repeated filter calls combine with AND. contains builds a LIKE with a bound value.
  • DbErr::sql_err() classifies unique and foreign key violations, so handlers map them to 409 and 400 without reading driver error codes.

Example 3: Insert and update with ActiveModel

A Model is a row that was read. An ActiveModel wraps each field in ActiveValue: Set(v) writes the field, NotSet leaves it out of the statement, and Unchanged(v) holds a value that was read and is not written. insert runs INSERT ... RETURNING on PostgreSQL and returns the stored Model.

src/products.rs gains create and update. Its imports change too. main.rs registers the new methods.

src/products.rs

Rust
// src/products.rs
use axum::{
    extract::{Path, Query, State},
    http::StatusCode,
    Json,
};
use sea_orm::{
    ActiveModelTrait, ColumnTrait, EntityTrait, QueryFilter, QueryOrder, QuerySelect, Set,
};
use serde::Deserialize;

use crate::{entities::product, error::ApiError, AppState};

#[derive(Deserialize)]
pub struct ProductFilter {
    max_price: Option<i32>,
    name: Option<String>,
}

#[derive(Deserialize)]
pub struct NewProduct {
    sku: String,
    name: String,
    price_cents: i32,
}

#[derive(Deserialize)]
pub struct UpdateProduct {
    name: Option<String>,
    price_cents: Option<i32>,
}

pub async fn get_one(
    State(state): State<AppState>,
    Path(id): Path<i64>,
) -> Result<Json<product::Model>, ApiError> {
    let found = product::Entity::find_by_id(id).one(&state.db).await?;
    found.map(Json).ok_or(ApiError::NotFound)
}

pub async fn list(
    State(state): State<AppState>,
    Query(filter): Query<ProductFilter>,
) -> Result<Json<Vec<product::Model>>, ApiError> {
    let mut query = product::Entity::find();
    if let Some(max) = filter.max_price {
        query = query.filter(product::Column::PriceCents.lte(max));
    }
    if let Some(name) = filter.name {
        query = query.filter(product::Column::Name.contains(name));
    }
    let rows = query
        .order_by_asc(product::Column::Id)
        .limit(50)
        .all(&state.db)
        .await?;
    Ok(Json(rows))
}

pub async fn create(
    State(state): State<AppState>,
    Json(body): Json<NewProduct>,
) -> Result<(StatusCode, Json<product::Model>), ApiError> {
    if body.price_cents <= 0 {
        return Err(ApiError::BadRequest("price must be positive"));
    }
    let created = product::ActiveModel {
        sku: Set(body.sku),
        name: Set(body.name),
        price_cents: Set(body.price_cents),
        ..Default::default()
    }
    .insert(&state.db)
    .await?;
    Ok((StatusCode::CREATED, Json(created)))
}

pub async fn update(
    State(state): State<AppState>,
    Path(id): Path<i64>,
    Json(body): Json<UpdateProduct>,
) -> Result<Json<product::Model>, ApiError> {
    if body.name.is_none() && body.price_cents.is_none() {
        return Err(ApiError::BadRequest("nothing to update"));
    }
    let existing = product::Entity::find_by_id(id)
        .one(&state.db)
        .await?
        .ok_or(ApiError::NotFound)?;

    let mut active: product::ActiveModel = existing.into();
    if let Some(name) = body.name {
        active.name = Set(name);
    }
    if let Some(price) = body.price_cents {
        if price <= 0 {
            return Err(ApiError::BadRequest("price must be positive"));
        }
        active.price_cents = Set(price);
    }
    let updated = active.update(&state.db).await?;
    Ok(Json(updated))
}

src/main.rs

Rust
// src/main.rs
mod entities;
mod error;
mod products;

use std::time::Duration;

use axum::{
    routing::{get, post},
    Router,
};
use sea_orm::{ConnectOptions, Database, DatabaseConnection};

#[derive(Clone)]
pub struct AppState {
    pub db: DatabaseConnection,
}

#[tokio::main]
async fn main() {
    let url = std::env::var("DATABASE_URL").expect("DATABASE_URL must be set");
    let mut options = ConnectOptions::new(url);
    options
        .max_connections(10)
        .min_connections(1)
        .acquire_timeout(Duration::from_secs(3))
        .sqlx_logging(false);
    let db = Database::connect(options).await.expect("database connection");

    let app = Router::new()
        .route("/products", get(products::list).post(products::create))
        .route("/products/{id}", get(products::get_one).patch(products::update))
        .with_state(AppState { db });

    let listener = tokio::net::TcpListener::bind("127.0.0.1:3000").await.unwrap();
    axum::serve(listener, app).await.unwrap();
}

update converts the Model into an ActiveModel and sets only the changed fields. Only fields marked Set appear in the UPDATE. The read followed by a write is a race between concurrent requests. The version-column technique in SQL transactions closes it.

Example 4: Relations without N+1

An order has many lines. SeaORM has three ways to load them.

Method Queries Result
model.find_related(Other) One per call Related rows of one model
Entity::find().find_with_related(Other) One join Vec<(Model, Vec<Related>)>; a limit applies to joined rows
models.load_many(Other, db) One extra IN query for all models Vec<Vec<Related>> in the order of the input

find_related inside a loop is the N+1 pattern from Relationships, indexes, and N+1. The handler below pages the parent rows first and then runs one load_many, so the query count stays at two for any page size.

src/orders.rs

Rust
// src/orders.rs
use axum::{
    extract::{Path, State},
    Json,
};
use sea_orm::{ColumnTrait, EntityTrait, LoaderTrait, QueryFilter, QueryOrder, QuerySelect};
use serde::Serialize;

use crate::{
    entities::{order, order_line},
    error::ApiError,
    AppState,
};

#[derive(Serialize)]
pub struct LineDto {
    line_no: i32,
    product_id: i64,
    quantity: i32,
    price_at_purchase_cents: i32,
}

#[derive(Serialize)]
pub struct OrderDto {
    id: i64,
    customer_id: i64,
    status: String,
    lines: Vec<LineDto>,
}

impl OrderDto {
    pub fn new(order: order::Model, mut lines: Vec<order_line::Model>) -> Self {
        lines.sort_by_key(|line| line.line_no);
        OrderDto {
            id: order.id,
            customer_id: order.customer_id,
            status: order.status,
            lines: lines
                .into_iter()
                .map(|line| LineDto {
                    line_no: line.line_no,
                    product_id: line.product_id,
                    quantity: line.quantity,
                    price_at_purchase_cents: line.price_at_purchase_cents,
                })
                .collect(),
        }
    }
}

pub async fn list_for_customer(
    State(state): State<AppState>,
    Path(customer_id): Path<i64>,
) -> Result<Json<Vec<OrderDto>>, ApiError> {
    let orders = order::Entity::find()
        .filter(order::Column::CustomerId.eq(customer_id))
        .order_by_desc(order::Column::Id)
        .limit(20)
        .all(&state.db)
        .await?;

    let lines = orders.load_many(order_line::Entity, &state.db).await?;

    let dtos = orders
        .into_iter()
        .zip(lines)
        .map(|(order, lines)| OrderDto::new(order, lines))
        .collect();
    Ok(Json(dtos))
}

src/main.rs

Rust
// src/main.rs
mod entities;
mod error;
mod orders;
mod products;

use std::time::Duration;

use axum::{
    routing::{get, post},
    Router,
};
use sea_orm::{ConnectOptions, Database, DatabaseConnection};

#[derive(Clone)]
pub struct AppState {
    pub db: DatabaseConnection,
}

#[tokio::main]
async fn main() {
    let url = std::env::var("DATABASE_URL").expect("DATABASE_URL must be set");
    let mut options = ConnectOptions::new(url);
    options
        .max_connections(10)
        .min_connections(1)
        .acquire_timeout(Duration::from_secs(3))
        .sqlx_logging(false);
    let db = Database::connect(options).await.expect("database connection");

    let app = Router::new()
        .route("/products", get(products::list).post(products::create))
        .route("/products/{id}", get(products::get_one).patch(products::update))
        .route("/customers/{customer_id}/orders", get(orders::list_for_customer))
        .with_state(AppState { db });

    let listener = tokio::net::TcpListener::bind("127.0.0.1:3000").await.unwrap();
    axum::serve(listener, app).await.unwrap();
}

With sqlx_logging(true) in ConnectOptions, the SQL of each request is logged, so the statements per request can be counted. The builder hides the SQL text, so this check is the way to see the query count.

Example 5: Transactions and raw SQL

db.begin() returns a DatabaseTransaction. It implements the same connection trait as DatabaseConnection, so every query accepts &txn where it accepts &db. Returning early drops the transaction, which rolls it back. create reads each product price inside the transaction and stores it as price_at_purchase_cents.

totals runs a grouped aggregate as raw SQL. FromQueryResult maps the columns to a struct. Window functions, complex aggregates, and FOR UPDATE SKIP LOCKED claims are usually clearer as SQL.

src/orders.rs

Rust
// src/orders.rs
use axum::{
    extract::{Path, State},
    http::StatusCode,
    Json,
};
use sea_orm::{
    ActiveModelTrait, ColumnTrait, DbBackend, EntityTrait, FromQueryResult, LoaderTrait,
    QueryFilter, QueryOrder, QuerySelect, Set, Statement, TransactionTrait,
};
use serde::{Deserialize, Serialize};

use crate::{
    entities::{order, order_line, product},
    error::ApiError,
    AppState,
};

#[derive(Serialize)]
pub struct LineDto {
    line_no: i32,
    product_id: i64,
    quantity: i32,
    price_at_purchase_cents: i32,
}

#[derive(Serialize)]
pub struct OrderDto {
    id: i64,
    customer_id: i64,
    status: String,
    lines: Vec<LineDto>,
}

impl OrderDto {
    pub fn new(order: order::Model, mut lines: Vec<order_line::Model>) -> Self {
        lines.sort_by_key(|line| line.line_no);
        OrderDto {
            id: order.id,
            customer_id: order.customer_id,
            status: order.status,
            lines: lines
                .into_iter()
                .map(|line| LineDto {
                    line_no: line.line_no,
                    product_id: line.product_id,
                    quantity: line.quantity,
                    price_at_purchase_cents: line.price_at_purchase_cents,
                })
                .collect(),
        }
    }
}

#[derive(Deserialize)]
pub struct NewLine {
    product_id: i64,
    quantity: i32,
}

#[derive(Deserialize)]
pub struct NewOrder {
    customer_id: i64,
    lines: Vec<NewLine>,
}

#[derive(Serialize, FromQueryResult)]
pub struct OrderTotal {
    order_id: i64,
    total_cents: i64,
}

pub async fn list_for_customer(
    State(state): State<AppState>,
    Path(customer_id): Path<i64>,
) -> Result<Json<Vec<OrderDto>>, ApiError> {
    let orders = order::Entity::find()
        .filter(order::Column::CustomerId.eq(customer_id))
        .order_by_desc(order::Column::Id)
        .limit(20)
        .all(&state.db)
        .await?;

    let lines = orders.load_many(order_line::Entity, &state.db).await?;

    let dtos = orders
        .into_iter()
        .zip(lines)
        .map(|(order, lines)| OrderDto::new(order, lines))
        .collect();
    Ok(Json(dtos))
}

pub async fn create(
    State(state): State<AppState>,
    Json(body): Json<NewOrder>,
) -> Result<(StatusCode, Json<OrderDto>), ApiError> {
    if body.lines.is_empty() {
        return Err(ApiError::BadRequest("order needs at least one line"));
    }

    let txn = state.db.begin().await?;

    let new_order = order::ActiveModel {
        customer_id: Set(body.customer_id),
        ..Default::default()
    }
    .insert(&txn)
    .await?;

    let mut saved_lines = Vec::new();
    for (index, line) in body.lines.into_iter().enumerate() {
        if line.quantity <= 0 {
            return Err(ApiError::BadRequest("quantity must be positive"));
        }
        let product = product::Entity::find_by_id(line.product_id)
            .one(&txn)
            .await?
            .ok_or(ApiError::BadRequest("unknown product"))?;

        let saved = order_line::ActiveModel {
            order_id: Set(new_order.id),
            line_no: Set(index as i32 + 1),
            product_id: Set(product.id),
            quantity: Set(line.quantity),
            price_at_purchase_cents: Set(product.price_cents),
        }
        .insert(&txn)
        .await?;
        saved_lines.push(saved);
    }

    txn.commit().await?;
    Ok((StatusCode::CREATED, Json(OrderDto::new(new_order, saved_lines))))
}

pub async fn totals(
    State(state): State<AppState>,
    Path(customer_id): Path<i64>,
) -> Result<Json<Vec<OrderTotal>>, ApiError> {
    let statement = Statement::from_sql_and_values(
        DbBackend::Postgres,
        "SELECT l.order_id,
                SUM(l.quantity::bigint * l.price_at_purchase_cents)::bigint AS total_cents
         FROM order_lines l
         JOIN orders o ON o.id = l.order_id
         WHERE o.customer_id = $1
         GROUP BY l.order_id
         ORDER BY l.order_id",
        [customer_id.into()],
    );
    let rows = OrderTotal::find_by_statement(statement).all(&state.db).await?;
    Ok(Json(rows))
}

SQLx or SeaORM

SQLx SeaORM
Query style SQL strings Rust method calls and generated Column types
Row types Own structs with FromRow Generated Model and ActiveModel
Partial writes Explicit SQL ActiveModel with Set
Relations Explicit joins and batched queries Related, load_many, find_with_related
Visible SQL Always Generated; needs logging to inspect

SeaORM runs on SQLx, so pool sizing, timeouts, and transaction behavior follow the same rules. Both can share a database in one service, at the cost of two query APIs.

Complete example

File tree:

Text
storefront-seaorm/
  Cargo.toml
  migrations/0001_init.sql
  src/
    main.rs
    error.rs
    products.rs
    orders.rs
    entities/
      mod.rs
      product.rs
      order.rs
      order_line.rs

Cargo.toml

TOML
# Cargo.toml
[package]
name = "storefront-seaorm"
version = "0.1.0"
edition = "2021"

[dependencies]
axum = "0.8"
sea-orm = { version = "1.1", features = ["sqlx-postgres", "runtime-tokio-rustls", "macros"] }
serde = { version = "1", features = ["derive"] }
serde_json = "1"
tokio = { version = "1", features = ["full"] }

migrations/0001_init.sql

SQL
-- migrations/0001_init.sql
CREATE TABLE products (
    id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    sku         TEXT        NOT NULL UNIQUE,
    name        TEXT        NOT NULL,
    price_cents INTEGER     NOT NULL CHECK (price_cents > 0),
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE orders (
    id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id BIGINT      NOT NULL,
    status      TEXT        NOT NULL DEFAULT 'pending',
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE order_lines (
    order_id                BIGINT  NOT NULL REFERENCES orders (id) ON DELETE CASCADE,
    line_no                 INTEGER NOT NULL,
    product_id              BIGINT  NOT NULL REFERENCES products (id),
    quantity                INTEGER NOT NULL CHECK (quantity > 0),
    price_at_purchase_cents INTEGER NOT NULL CHECK (price_at_purchase_cents > 0),
    PRIMARY KEY (order_id, line_no)
);

CREATE INDEX orders_customer_idx ON orders (customer_id);
CREATE INDEX order_lines_product_idx ON order_lines (product_id);

src/main.rs

Rust
// src/main.rs
mod entities;
mod error;
mod orders;
mod products;

use std::time::Duration;

use axum::{
    routing::{get, post},
    Router,
};
use sea_orm::{ConnectOptions, Database, DatabaseConnection};

#[derive(Clone)]
pub struct AppState {
    pub db: DatabaseConnection,
}

#[tokio::main]
async fn main() {
    let url = std::env::var("DATABASE_URL").expect("DATABASE_URL must be set");
    let mut options = ConnectOptions::new(url);
    options
        .max_connections(10)
        .min_connections(1)
        .acquire_timeout(Duration::from_secs(3))
        .sqlx_logging(false);
    let db = Database::connect(options).await.expect("database connection");

    let app = Router::new()
        .route("/products", get(products::list).post(products::create))
        .route("/products/{id}", get(products::get_one).patch(products::update))
        .route("/customers/{customer_id}/orders", get(orders::list_for_customer))
        .route("/customers/{customer_id}/order-totals", get(orders::totals))
        .route("/orders", post(orders::create))
        .with_state(AppState { db });

    let listener = tokio::net::TcpListener::bind("127.0.0.1:3000").await.unwrap();
    axum::serve(listener, app).await.unwrap();
}

src/error.rs

Rust
// src/error.rs
use axum::{
    http::StatusCode,
    response::{IntoResponse, Response},
    Json,
};
use sea_orm::{DbErr, SqlErr};
use serde_json::json;

pub enum ApiError {
    NotFound,
    Conflict,
    BadRequest(&'static str),
    Internal,
}

impl From<DbErr> for ApiError {
    fn from(err: DbErr) -> Self {
        match err.sql_err() {
            Some(SqlErr::UniqueConstraintViolation(_)) => ApiError::Conflict,
            Some(SqlErr::ForeignKeyConstraintViolation(_)) => {
                ApiError::BadRequest("unknown reference")
            }
            _ => {
                eprintln!("database error: {err}");
                ApiError::Internal
            }
        }
    }
}

impl IntoResponse for ApiError {
    fn into_response(self) -> Response {
        let (status, message) = match self {
            ApiError::NotFound => (StatusCode::NOT_FOUND, "not found"),
            ApiError::Conflict => (StatusCode::CONFLICT, "conflict"),
            ApiError::BadRequest(message) => (StatusCode::BAD_REQUEST, message),
            ApiError::Internal => (StatusCode::INTERNAL_SERVER_ERROR, "internal error"),
        };
        (status, Json(json!({ "error": message }))).into_response()
    }
}

src/entities/mod.rs

Rust
// src/entities/mod.rs
pub mod order;
pub mod order_line;
pub mod product;

src/entities/product.rs

Rust
// src/entities/product.rs
use sea_orm::entity::prelude::*;
use serde::Serialize;

#[derive(Clone, Debug, PartialEq, Eq, DeriveEntityModel, Serialize)]
#[sea_orm(table_name = "products")]
pub struct Model {
    #[sea_orm(primary_key)]
    pub id: i64,
    #[sea_orm(unique)]
    pub sku: String,
    pub name: String,
    pub price_cents: i32,
    #[serde(skip)]
    pub created_at: DateTimeUtc,
}

#[derive(Copy, Clone, Debug, EnumIter, DeriveRelation)]
pub enum Relation {
    #[sea_orm(has_many = "super::order_line::Entity")]
    OrderLines,
}

impl Related<super::order_line::Entity> for Entity {
    fn to() -> RelationDef {
        Relation::OrderLines.def()
    }
}

impl ActiveModelBehavior for ActiveModel {}

src/entities/order.rs

Rust
// src/entities/order.rs
use sea_orm::entity::prelude::*;

#[derive(Clone, Debug, PartialEq, Eq, DeriveEntityModel)]
#[sea_orm(table_name = "orders")]
pub struct Model {
    #[sea_orm(primary_key)]
    pub id: i64,
    pub customer_id: i64,
    pub status: String,
    pub created_at: DateTimeUtc,
}

#[derive(Copy, Clone, Debug, EnumIter, DeriveRelation)]
pub enum Relation {
    #[sea_orm(has_many = "super::order_line::Entity")]
    OrderLines,
}

impl Related<super::order_line::Entity> for Entity {
    fn to() -> RelationDef {
        Relation::OrderLines.def()
    }
}

impl ActiveModelBehavior for ActiveModel {}

src/entities/order_line.rs

Rust
// src/entities/order_line.rs
use sea_orm::entity::prelude::*;

#[derive(Clone, Debug, PartialEq, Eq, DeriveEntityModel)]
#[sea_orm(table_name = "order_lines")]
pub struct Model {
    #[sea_orm(primary_key, auto_increment = false)]
    pub order_id: i64,
    #[sea_orm(primary_key, auto_increment = false)]
    pub line_no: i32,
    pub product_id: i64,
    pub quantity: i32,
    pub price_at_purchase_cents: i32,
}

#[derive(Copy, Clone, Debug, EnumIter, DeriveRelation)]
pub enum Relation {
    #[sea_orm(
        belongs_to = "super::order::Entity",
        from = "Column::OrderId",
        to = "super::order::Column::Id"
    )]
    Order,
    #[sea_orm(
        belongs_to = "super::product::Entity",
        from = "Column::ProductId",
        to = "super::product::Column::Id"
    )]
    Product,
}

impl Related<super::order::Entity> for Entity {
    fn to() -> RelationDef {
        Relation::Order.def()
    }
}

impl Related<super::product::Entity> for Entity {
    fn to() -> RelationDef {
        Relation::Product.def()
    }
}

impl ActiveModelBehavior for ActiveModel {}

src/products.rs

Rust
// src/products.rs
use axum::{
    extract::{Path, Query, State},
    http::StatusCode,
    Json,
};
use sea_orm::{
    ActiveModelTrait, ColumnTrait, EntityTrait, QueryFilter, QueryOrder, QuerySelect, Set,
};
use serde::Deserialize;

use crate::{entities::product, error::ApiError, AppState};

#[derive(Deserialize)]
pub struct ProductFilter {
    max_price: Option<i32>,
    name: Option<String>,
}

#[derive(Deserialize)]
pub struct NewProduct {
    sku: String,
    name: String,
    price_cents: i32,
}

#[derive(Deserialize)]
pub struct UpdateProduct {
    name: Option<String>,
    price_cents: Option<i32>,
}

pub async fn get_one(
    State(state): State<AppState>,
    Path(id): Path<i64>,
) -> Result<Json<product::Model>, ApiError> {
    let found = product::Entity::find_by_id(id).one(&state.db).await?;
    found.map(Json).ok_or(ApiError::NotFound)
}

pub async fn list(
    State(state): State<AppState>,
    Query(filter): Query<ProductFilter>,
) -> Result<Json<Vec<product::Model>>, ApiError> {
    let mut query = product::Entity::find();
    if let Some(max) = filter.max_price {
        query = query.filter(product::Column::PriceCents.lte(max));
    }
    if let Some(name) = filter.name {
        query = query.filter(product::Column::Name.contains(name));
    }
    let rows = query
        .order_by_asc(product::Column::Id)
        .limit(50)
        .all(&state.db)
        .await?;
    Ok(Json(rows))
}

pub async fn create(
    State(state): State<AppState>,
    Json(body): Json<NewProduct>,
) -> Result<(StatusCode, Json<product::Model>), ApiError> {
    if body.price_cents <= 0 {
        return Err(ApiError::BadRequest("price must be positive"));
    }
    let created = product::ActiveModel {
        sku: Set(body.sku),
        name: Set(body.name),
        price_cents: Set(body.price_cents),
        ..Default::default()
    }
    .insert(&state.db)
    .await?;
    Ok((StatusCode::CREATED, Json(created)))
}

pub async fn update(
    State(state): State<AppState>,
    Path(id): Path<i64>,
    Json(body): Json<UpdateProduct>,
) -> Result<Json<product::Model>, ApiError> {
    if body.name.is_none() && body.price_cents.is_none() {
        return Err(ApiError::BadRequest("nothing to update"));
    }
    let existing = product::Entity::find_by_id(id)
        .one(&state.db)
        .await?
        .ok_or(ApiError::NotFound)?;

    let mut active: product::ActiveModel = existing.into();
    if let Some(name) = body.name {
        active.name = Set(name);
    }
    if let Some(price) = body.price_cents {
        if price <= 0 {
            return Err(ApiError::BadRequest("price must be positive"));
        }
        active.price_cents = Set(price);
    }
    let updated = active.update(&state.db).await?;
    Ok(Json(updated))
}

src/orders.rs

Rust
// src/orders.rs
use axum::{
    extract::{Path, State},
    http::StatusCode,
    Json,
};
use sea_orm::{
    ActiveModelTrait, ColumnTrait, DbBackend, EntityTrait, FromQueryResult, LoaderTrait,
    QueryFilter, QueryOrder, QuerySelect, Set, Statement, TransactionTrait,
};
use serde::{Deserialize, Serialize};

use crate::{
    entities::{order, order_line, product},
    error::ApiError,
    AppState,
};

#[derive(Serialize)]
pub struct LineDto {
    line_no: i32,
    product_id: i64,
    quantity: i32,
    price_at_purchase_cents: i32,
}

#[derive(Serialize)]
pub struct OrderDto {
    id: i64,
    customer_id: i64,
    status: String,
    lines: Vec<LineDto>,
}

impl OrderDto {
    pub fn new(order: order::Model, mut lines: Vec<order_line::Model>) -> Self {
        lines.sort_by_key(|line| line.line_no);
        OrderDto {
            id: order.id,
            customer_id: order.customer_id,
            status: order.status,
            lines: lines
                .into_iter()
                .map(|line| LineDto {
                    line_no: line.line_no,
                    product_id: line.product_id,
                    quantity: line.quantity,
                    price_at_purchase_cents: line.price_at_purchase_cents,
                })
                .collect(),
        }
    }
}

#[derive(Deserialize)]
pub struct NewLine {
    product_id: i64,
    quantity: i32,
}

#[derive(Deserialize)]
pub struct NewOrder {
    customer_id: i64,
    lines: Vec<NewLine>,
}

#[derive(Serialize, FromQueryResult)]
pub struct OrderTotal {
    order_id: i64,
    total_cents: i64,
}

pub async fn list_for_customer(
    State(state): State<AppState>,
    Path(customer_id): Path<i64>,
) -> Result<Json<Vec<OrderDto>>, ApiError> {
    let orders = order::Entity::find()
        .filter(order::Column::CustomerId.eq(customer_id))
        .order_by_desc(order::Column::Id)
        .limit(20)
        .all(&state.db)
        .await?;

    let lines = orders.load_many(order_line::Entity, &state.db).await?;

    let dtos = orders
        .into_iter()
        .zip(lines)
        .map(|(order, lines)| OrderDto::new(order, lines))
        .collect();
    Ok(Json(dtos))
}

pub async fn create(
    State(state): State<AppState>,
    Json(body): Json<NewOrder>,
) -> Result<(StatusCode, Json<OrderDto>), ApiError> {
    if body.lines.is_empty() {
        return Err(ApiError::BadRequest("order needs at least one line"));
    }

    let txn = state.db.begin().await?;

    let new_order = order::ActiveModel {
        customer_id: Set(body.customer_id),
        ..Default::default()
    }
    .insert(&txn)
    .await?;

    let mut saved_lines = Vec::new();
    for (index, line) in body.lines.into_iter().enumerate() {
        if line.quantity <= 0 {
            return Err(ApiError::BadRequest("quantity must be positive"));
        }
        let product = product::Entity::find_by_id(line.product_id)
            .one(&txn)
            .await?
            .ok_or(ApiError::BadRequest("unknown product"))?;

        let saved = order_line::ActiveModel {
            order_id: Set(new_order.id),
            line_no: Set(index as i32 + 1),
            product_id: Set(product.id),
            quantity: Set(line.quantity),
            price_at_purchase_cents: Set(product.price_cents),
        }
        .insert(&txn)
        .await?;
        saved_lines.push(saved);
    }

    txn.commit().await?;
    Ok((StatusCode::CREATED, Json(OrderDto::new(new_order, saved_lines))))
}

pub async fn totals(
    State(state): State<AppState>,
    Path(customer_id): Path<i64>,
) -> Result<Json<Vec<OrderTotal>>, ApiError> {
    let statement = Statement::from_sql_and_values(
        DbBackend::Postgres,
        "SELECT l.order_id,
                SUM(l.quantity::bigint * l.price_at_purchase_cents)::bigint AS total_cents
         FROM order_lines l
         JOIN orders o ON o.id = l.order_id
         WHERE o.customer_id = $1
         GROUP BY l.order_id
         ORDER BY l.order_id",
        [customer_id.into()],
    );
    let rows = OrderTotal::find_by_statement(statement).all(&state.db).await?;
    Ok(Json(rows))
}

Run (PostgreSQL started and migrated as in Project setup):

Terminal
cargo run

Requests, with a helper that prints the status code after each body:

Terminal
req() { curl -s -w ' [%{http_code}]\n' "$@"; }
J='content-type: application/json'

req -X POST localhost:3000/products -H "$J" -d '{"sku":"PEN-1","name":"Pen","price_cents":250}'
req -X POST localhost:3000/products -H "$J" -d '{"sku":"PEN-1","name":"Pen","price_cents":250}'
req -X POST localhost:3000/orders -H "$J" -d '{"customer_id":7,"lines":[{"product_id":1,"quantity":2}]}'
req -X PATCH localhost:3000/products/1 -H "$J" -d '{"price_cents":300}'
req localhost:3000/customers/7/orders
req localhost:3000/customers/7/order-totals
req -X POST localhost:3000/orders -H "$J" -d '{"customer_id":7,"lines":[{"product_id":99,"quantity":1}]}'
req localhost:3000/products/42

Responses on a fresh database:

Output
{"id":1,"sku":"PEN-1","name":"Pen","price_cents":250} [201]
{"error":"conflict"} [409]
{"id":1,"customer_id":7,"status":"pending","lines":[{"line_no":1,"product_id":1,"quantity":2,"price_at_purchase_cents":250}]} [201]
{"id":1,"sku":"PEN-1","name":"Pen","price_cents":300} [200]
[{"id":1,"customer_id":7,"status":"pending","lines":[{"line_no":1,"product_id":1,"quantity":2,"price_at_purchase_cents":250}]}] [200]
[{"order_id":1,"total_cents":500}] [200]
{"error":"unknown product"} [400]
{"error":"not found"} [404]

The order keeps the price of 250 after the product price changes to 300, because the line stores the price at purchase. The failed order rolls back, so no order row remains for it.

Summary

  • An entity has Model, Column, Relation, and ActiveModel, all derived from one annotated struct.
  • find, filter, and Column methods build type-checked queries. one returns an Option, all returns a Vec.
  • ActiveModel with Set handles inserts and partial updates. insert returns the stored Model.
  • find_related in a loop is N+1. load_many runs one extra query for all parents.
  • DatabaseTransaction accepts the same calls as DatabaseConnection and rolls back when dropped without commit.
  • DbErr::sql_err() classifies unique and foreign key violations.
  • Raw statements with FromQueryResult cover queries the builder does not express well.