Model Definition

Basic Definition

use ormer::Model;

#[derive(Debug, Model)]
#[table = "users"]
struct User {
    #[primary(auto)]
    id: i32,
    name: String,
    email: Option<String>,
}

Attributes

• #[table = "table_name"] - Specifies the table name
• #[table(schema = "schema", name = "table_name")] - Specifies schema and table independently
• #[column(name = "column_name")] - Specifies the SQL column name
• #[primary] - Primary key (supports composite primary keys)
• #[primary(auto)] - Auto-increment primary key (only for single primary key or the first field of composite primary key)
• #[unique] - Unique constraint (supports group and name)
• #[index] - Index (supports group, name, order, where, method, expression, and columns)
• #[default(...)] - Database default; use #[default(expr = "...")] for SQL expressions
• #[check(expr = "...")] - CHECK constraint, with optional name
• #[foreign(Type)] - Foreign key relationship, with optional name, on_delete, and on_update
• #[embed(prefix = "prefix_")] - Embeds a value object and expands its fields into prefixed columns
• #[data_type(i64)] - Database type override (e.g., Rust i32 field mapped to BIGINT in database)
• #[hypertable(Duration::from_secs(86400))] - TimescaleDB hypertable time chunk interval
• #[hypertable] - TimescaleDB space partition column (4 partitions by default)
• #[hypertable(route)] - Marks a String field as a PostgreSQL table route key (at most one per model)
• #[compress] - Column compression using PostgreSQL pglz by default
• #[compress(lz4)] - Select a compression algorithm; PostgreSQL emits column-level COMPRESSION lz4, while MySQL emits the table option COMPRESSION='LZ4'
• #[filter(filter_name, |m, ...| ...)] - Model-level reusable filter; the name must start with filter_
• #[version(u64)] - Adds an automatic version column for optimistic locking
• #[ormer_ignore] - Excludes a field from database columns; useful for dynamic table route values

PostgreSQL and MSSQL preserve the schema prefix in #[table = "schema.table"]; SQLite, MySQL, and QuestDB use the final table-name component.

Table Options

Dialect-specific table options are declared with per-dialect container attributes:

#[derive(Debug, Model)]
#[table = "events"]
#[mysql(engine = "InnoDB", charset = "utf8mb4")]
#[postgresql(fillfactor = 80)]
#[clickhouse(engine = "MergeTree", order_by = "(tenant_id, occurred_at)")]
struct Event {
    #[primary]
    id: i64,
    tenant_id: i64,
}

MySQL supports engine, charset, and collation; PostgreSQL supports storage and fillfactor; MSSQL supports filegroup; ClickHouse supports engine, order_by, partition_by, ttl, and settings. Models declaring #[clickhouse(engine = ...)] can call create_table::<T>() directly.

DbFirst Entity Generation

let code = db.generate_entities(None).await?;

Pass Some("public") for PostgreSQL or Some("dbo") for MSSQL when needed; for ClickHouse, the value selects the database. Omitted schemas use the backend default. DuckDB and ClickHouse infer Rust field types from the actual column types.

Optimistic Lock Version

#[derive(Debug, Model)]
#[version(u64)]
#[table = "orders"]
struct Order {
    #[primary]
    id: i32,
    status: String,
}

let version = order.version();

#[version(u64)] creates an invisible version column with initial value 1. After loading a model, version() returns the current version; set_model updates automatically add the version condition and increment the version.

Model Filters

#[derive(Debug, Model)]
#[table = "orders"]
#[filter(filter_valid, |o| o.deleted_at.is_null())]
#[filter(filter_tenant, |o, tenant_id: i64| o.tenant_id.eq(tenant_id))]
struct Order {
    #[primary]
    id: i64,
    tenant_id: i64,
    deleted_at: Option<chrono::NaiveDateTime>,
}

let orders: Vec<Order> = db
    .select::<Order>()
    .filter_tenant(tenant_id)
    .filter_valid()
    .collect()
    .await?;

let scoped = db.scope().filter_tenant(tenant_id).filter_valid();
let orders: Vec<Order> = scoped.select::<Order>().collect().await?;

let include_deleted: Vec<Order> = scoped
    .select::<Order>()
    .unset_filter_valid()
    .collect()
    .await?;

Filters enabled on scope() are inherited by queries, relationship loading, updates, and deletes. unset_filter_*() disables the named filter with the same name, whether inherited from a scope or applied on the current query via filter_*(); it does not remove filters added directly with filter(...).

Dynamic Table Routing

Table names may contain {variable} placeholders. Use route_table for queries; inserts read matching values from model fields.

#[derive(Debug, Model)]
#[table = "orders_{tenant_id}"]
struct Order {
    #[primary]
    id: i64,
    tenant_id: i64,
}

let orders: Vec<Order> = db
    .select::<Order>()
    .route_table("tenant_id", tenant_id)
    .collect()
    .await?;

If a route value should not be a database column, mark it with #[ormer_ignore]:

#[derive(Debug, Model)]
#[table = "events_{tenant_id}"]
struct Event {
    #[primary]
    id: i64,
    name: String,
    #[ormer_ignore]
    tenant_id: i64,
}

Time-series Chunking

#[hypertable(Duration)] is the unified chunking declaration for time-series backends: TimescaleDB uses it as the chunk interval, QuestDB maps it to timestamp(col) PARTITION BY <unit> at table creation, ClickHouse derives PARTITION BY from it when partition_by is not declared, and delete_blocks depends on it. InfluxDB resolves its timestamp column from the same declaration, falling back to the single DateTime #[primary] field. Duration to partition-unit mapping:

TimescaleDB supports space partitioning on top of time chunking: bare #[hypertable] marks a String field, and create_hypertable partitions by that column into 4 partitions by default.

#[derive(Debug, Model)]
#[table = "events"]
struct Event {
    #[primary]
    id: i64,
    payload: String,
    #[hypertable] // space partition column, 4 partitions by default
    tenant: String,
    #[hypertable(std::time::Duration::from_secs(86400))]
    created_at: chrono::NaiveDateTime,
}

Table Route Key

#[hypertable(route)] declares a String field (at most one per model) as a table route key: on PostgreSQL the physical table name becomes {table}_{route_value}, so table aaa with route value val reads and writes the subtable aaa_val; other backends ignore the route key and use the base table. This is a split style independent from the all-backend table-name template #[table = "orders_{tenant_id}"] (see "Dynamic Table Routing").

#[derive(Debug, Model)]
#[table = "aaa"]
struct Aaa {
    #[primary]
    id: i64,
    #[hypertable(route)]
    tenant: String, // subtables aaa_acme, aaa_other...
}

The route key is the field's SQL column name (the Rust field name when #[column] is not set); add #[ormer_ignore] when the field should not become a column. A route value must be a non-empty string of letters, digits, and underscores.

Writes (insert, upserts, insert_or_ignore, insert_partial) read the route value from the model field automatically; on PostgreSQL, the first write with a new route value creates the subtable from the model DDL (including the hypertable and indexes), with no manual table creation:

db.insert(&Aaa { id: 1, tenant: "acme".to_string() }).await?;
// creates and writes aaa_acme automatically

Queries, updates, deletes, truncates, table drops, and block deletes pass the subtable explicitly with route_table(key, value) or with_table_route(route):

let rows: Vec<Aaa> = db
    .select::<Aaa>()
    .route_table("tenant", "acme")
    .collect()
    .await?;

let count: usize = db
    .select::<Aaa>()
    .route_table("tenant", "acme")
    .count(|a| a.id)
    .await?;

db.delete::<Aaa>()
    .route_table("tenant", "acme")
    .filter(|a| a.id.eq(1))
    .execute()
    .await?;

let route = ormer::model::TableRoute::new().with("tenant", "acme");
db.truncate_table::<Aaa>().with_table_route(route).execute().await?;

delete_blocks (drop_chunks block deletion), drop_table, and create_table support with_table_route as well. related/multi/four table JOINs apply the route to the main table only; joined tables are not routed. ensure_table / migrate_table automatically migrate existing {table}_% subtables on PostgreSQL.

Subtable columnstore: when a model declares both #[hypertable] and a route key, each subtable is a TimescaleDB hypertable whose route column holds a fixed value and is written row by row in row storage. ormer automatically enables columnstore when auto-creating subtables and during ensure_table migration (attaching an automatic compression policy keyed to the chunk interval), so stale chunks are compressed over time and the route column's storage cost drops to nearly zero. create_table().with_table_name(subtable).with_route_columnstore() applies the same when creating subtables manually. Backends without support (TimescaleDB not installed or too old) skip it silently without affecting functionality.

Limitations:

• Convenience APIs such as find_by_id, preload, and select_related have no route entry; use db.select::<T>().with_table_route(...) when routing is needed.
• Without a route, queries use the base table name.
• Rendering a route fails with a panic (missing route value or invalid value), consistent with the existing Select behavior.

InfluxDB Models

One model maps to one measurement: the timestamp reuses #[primary] (exactly one time-typed field, no auto), tags reuse #[index] (must be String), and the remaining fields are measurements. Models declaring #[influxdb(...)] fail to compile when these constraints are violated. Table-level retention policy:

#[derive(Debug, Model)]
#[table = "cpu_usage"]
#[influxdb(retention = std::time::Duration::from_secs(30 * 86400))] // optional, 30-day retention
struct CpuUsage {
    #[primary]
    time: chrono::DateTime<chrono::Utc>,
    #[index]
    host: String,
    usage: f64,
}

Field Attributes

Unique Constraint

Single Column Unique

#[derive(Debug, Model)]
#[table = "users"]
struct User {
    #[primary(auto)]
    id: i32,
    
    #[unique]
    email: String,
}

Composite Unique

#[derive(Debug, Model)]
#[table = "user_roles"]
struct UserRole {
    #[primary(auto)]
    id: i32,
    
    #[unique(group = 1)]
    user_id: i32,
    
    #[unique(group = 1)]
    role_id: i32,
}

Indexes

#[derive(Debug, Model)]
#[table = "users"]
struct User {
    #[primary(auto)]
    id: i32,
    
    #[index]
    age: i32,
    
    #[index]
    created_at: String,
}

Nullable Fields

#[derive(Debug, Model)]
#[table = "users"]
struct User {
    #[primary(auto)]
    id: i32,
    name: String,
    
    email: Option<String>,
    phone: Option<String>,
}

Column Names, Defaults, and Constraints

#[derive(Debug, Model)]
#[table(schema = "auth", name = "users")]
struct User {
    #[primary(auto)]
    id: i32,
    #[column(name = "display_name")]
    #[default("")]
    #[check(expr = "length(display_name) > 0")]
    name: String,
    #[default(expr = "CURRENT_TIMESTAMP")]
    created_at: chrono::NaiveDateTime,
}

insert(&model) still writes all model fields explicitly; a database default applies only when the column is omitted from the INSERT, which insert_partial or insert_model can do.

Embedded Value Objects

Value objects can derive Embed, then model fields can use #[embed(prefix = "...")] to expand them into multiple columns:

#[derive(Debug, Clone, ormer::Embed)]
struct Address {
    city: String,
    street: String,
}

#[derive(Debug, Clone, ormer::Model)]
#[table = "users"]
struct User {
    #[primary(auto)]
    id: i32,
    #[embed(prefix = "addr_")]
    address: Address,
}

let users: Vec<User> = db
    .select::<User>()
    .filter(|u| u.address.city.eq("Shanghai"))
    .collect()
    .await?;

Supported Types

All basic types can be wrapped with Option<T> for nullable fields.

UUID fields can use uuid::Uuid or Option<uuid::Uuid> directly:

#[derive(Debug, Clone, ormer::Model)]
#[table = "sessions"]
struct Session {
    #[primary]
    id: uuid::Uuid,
    user_id: uuid::Uuid,
    revoked_at: Option<chrono::NaiveDateTime>,
}

UUID values are generated by the application, for example with uuid::Uuid::new_v4(); the application must enable the v4 feature of the uuid crate, and the ORM does not generate UUIDs automatically. SQLite uses TEXT, MySQL uses CHAR(36) for canonical UUID strings, and MSSQL uses the native UNIQUEIDENTIFIER type.

Field Types

use ormer::{FieldType, Model};

#[derive(Debug, Clone, FieldType, PartialEq)]
enum UserStatus {
    Active,
    Inactive,
    Banned,
}

#[derive(Debug, Clone, FieldType, PartialEq)]
pub struct ExceptionType(pub u16);

#[derive(Debug, Model)]
#[table = "users"]
struct User {
    #[primary(auto)]
    id: i32,
    status: UserStatus,
    exception_type: ExceptionType,
    name: String,
}

FieldType works for enums and single-field tuple struct wrappers. Wrapper types use their inner field type for database columns, so ExceptionType(pub u16) is stored as u16. Use Option<FieldType> for nullable fields.

FieldType values can also be used with IN, comparison, and ordering conditions:

let active: Vec<User> = db
    .select::<User>()
    .filter(|u| u.status.is_in([UserStatus::Active, UserStatus::Banned]))
    .collect()
    .await?;

A ModelEnum with named fields can be used as a polymorphic field inside a model. The model field column stores the discriminator value, and variant fields are flattened into nullable columns on the same table:

#[derive(Debug, Model)]
#[table = "documents"]
struct Document {
    #[primary]
    id: i64,
    title: String,
    body: DocumentBody,
}

#[derive(Debug, Clone, PartialEq, ormer::ModelEnum)]
#[db_type(String)]
enum DocumentBody {
    Article {
        article_body: String,
        article_word_count: i32,
    },
    Video {
        video_url: String,
        video_duration_seconds: i32,
    },
}

#[db_type(String)] stores the snake_case variant name as the discriminator value, so Article is stored as "article". Document::columns() includes body, article_body, article_word_count, video_url, and video_duration_seconds; reading and writing Document automatically dispatches by the body column.

For an existing numeric enum or wrapper type where you do not want to derive FieldType, use #[data_type(i32)]. A nullable field must use #[data_type(Option<i32>)]:

#[repr(i32)]
#[derive(Debug, Clone, Copy)]
enum Status {
    Active = 1,
    Disabled = 0,
}

#[derive(Debug, Model)]
#[table = "users"]
struct User {
    #[primary]
    id: i32,
    #[data_type(i32)]
    status: Status,
    #[data_type(Option<i32>)]
    old_status: Option<Status>,
}

PostgreSQL Arrays

PostgreSQL supports Vec<i32>, Vec<i64>, Vec<Option<i64>>, and Vec<String>. These fields map to native array types; Vec<String> uses TEXT[], not a JSON column:

#[derive(Debug, Model)]
#[table = "users"]
struct User {
    #[primary]
    id: i32,
    tags: Vec<String>,
    scores: Vec<i32>,
}

For a custom numeric enum inside Vec<T>, use #[data_type(Vec<i32>)]:

#[data_type(Vec<i32>)]
roles: Vec<Status>,

Array types and this syntax currently target the PostgreSQL backend.

Complete Example

use ormer::Model;

#[derive(Debug, Model, Clone)]
#[table = "products"]
struct Product {
    #[primary(auto)]
    id: i32,
    
    #[unique]
    sku: String,
    name: String,
    price: f64,
    
    #[index]
    category_id: i32,
    stock: i32,
    
    description: Option<String>,
    is_active: bool,
}

Foreign Key Relationships

#[derive(Debug, Model)]
#[table = "posts"]
struct Post {
    #[primary(auto)]
    id: i32,
    
    #[foreign(User)]
    user_id: i32,
    
    title: String,
    content: String,
}

Model Relations

Foreign-key fields describe the database constraint. To load related models, use #[has_many], #[belongs_to], #[has_one], and #[through]:

#[derive(Debug, Clone, Model)]
#[table = "users"]
struct User {
    #[primary(auto)]
    id: i32,
    name: String,
    #[has_many(Post.user_id)]
    posts: Vec<Post>,
    #[has_one(Profile.user_id)]
    profile: Option<Profile>,
    #[has_many(UserRole.user_id)]
    user_roles: Vec<UserRole>,
    #[through(user_roles.role)]
    roles: Vec<Role>,
}

#[derive(Debug, Clone, Model)]
#[table = "posts"]
struct Post {
    #[primary(auto)]
    id: i32,
    #[foreign(User.id)]
    user_id: i32,
    #[belongs_to(user_id)]
    user: Option<User>,
    title: String,
}

Relation fields are not database columns. #[belongs_to] and #[has_one] fields must be Option<T>, while #[has_many] and #[through] fields use Vec<T>. #[through(user_roles.role)] follows this model's user_roles relation and then the intermediate model's role relation.

Composite Primary Keys

Add #[primary] to multiple fields to define a composite primary key:

#[derive(Debug, Model)]
#[table = "user_roles"]
struct UserRole {
    #[primary]
    user_id: i32,
    #[primary]
    role_id: i32,
    assigned_at: String,
}

Only the first primary key field can use auto:

#[primary(auto)]
id: i32,
#[primary]
product_id: i32,

Use primary_field_names() to get the Rust primary key field names, model.primary_fields() to get the current primary key field value tuple, and primary_key_columns() to get the SQL primary key column names. Composite primary keys are returned in field declaration order.

Table Operations

Creating Tables

db.create_table::<User>().execute().await?;

Validating Tables

db.validate_table::<User>().await?;

validate_table checks column count, order, names, types, nullability, primary-key, auto-increment, unique constraints, indexes, and foreign keys. PostgreSQL models also validate the TimescaleDB hypertable and time-chunk interval.

Dropping Tables

db.drop_table::<User>().execute().await?;

Model Wrappers

// Base model
#[derive(Debug, Model, Clone)]
#[table = "users"]
struct User {
    #[primary(auto)]
    id: i32,
    name: String,
    age: i32,
    email: Option<String>,
}

// Wrapper - different table name
#[derive(Debug, Model)]
#[table = "archive_users"]
struct ArchiveUser(User);

#[derive(Debug, Model)]
#[table = "temp_users"]
struct TempUser(User);

Usage Example

db.create_table::<User>().execute().await?;
db.create_table::<ArchiveUser>().execute().await?;

db.insert(&User {
    id: 0,
    name: "Alice".to_string(),
    age: 25,
    email: Some("alice@example.com".to_string()),
}).await?;

let archive_user = ArchiveUser(User {
    id: 0,
    name: "Bob".to_string(),
    age: 30,
    email: Some("bob@example.com".to_string()),
});
db.insert(&archive_user).execute().await?;

let archived: Vec<ArchiveUser> = db
    .select::<ArchiveUser>()
    .collect::<Vec<_>>()
    .await?;

for au in &archived {
    println!("User: {}", au.inner().name);
}