Typed ORM with automatic migrations: Fitz vs SQLAlchemy + Alembic + Pydantic

tion logic, and migration generation.

1. Unified Type Definition

Instead of separate model and schema files, Fitz uses a single @table type. The compiler extracts metadata to generate parameterized SQL, OpenAPI specifications, and JSON deserializers.

// entities.rs
use fitz::prelude::*;

#[table("invoices")]
pub type Invoice {
    #[primary]
    pub id: Int,
    
    pub customer_email: Str,
    
    pub status: Str,
    
    pub total_amount: Decimal,
    
    pub metadata: Jsonb,
    
    #[has_many("LineItem", "invoice_id")]
    pub items: List<LineItem>,
    
    #[belongs_to]
    pub customer: Customer?,
}

#[table("line_items")]
pub type LineItem {
    #[primary]
    pub id: Int,
    
    pub invoice_id: Int,
    
    pub description: Str,
    
    pub quantity: Int,
    
    pub unit_price: Decimal,
}

Rationale: The #[table] decorator registers the type with the schema registry. Fields map directly to columns, and relationship decorators (#[has_many], #[belongs_to]) define foreign keys and association logic. This eliminates duplication; the type is the contract for the database, the API, and the validation layer.

2. Closure-to-SQL at Compile Time

Fitz queries use closures that the compiler analyzes to emit static SQL. This differs fundamentally from runtime DSLs.

// query.rs
let pending_invoices = Invoice::where(fn(i) => i.status == "pending")
    .order_by(fn(i) => i.total_amount, "desc")
    .limit(20)
    .all(db)
    .await?;

Mechanism: The compiler inspects the closure fn(i) => i.status == "pending". It resolves the field status to the column name, maps the == operator to =, and constructs the SQL string at compile time. The resulting binary contains the constant string:

SELECT id, customer_email, status, total_amount, metadata 
FROM invoices 
WHERE status = $1 
ORDER BY total_amount DESC 
LIMIT $2

At runtime, the ORM only binds parameters ($1 = "pending", $2 = 20) and executes the query. There is no AST construction, no reflection, and no string concatenation overhead.

3. Native Postgres Operators

Fitz exposes Postgres-specific operators as methods on field types, ensuring type safety and correct SQL generation.

// operators.rs
// JSONB containment
let pro_users = Invoice::where(fn(i) => i.metadata.get("plan") == "enterprise")
    .all(db)
    .await?;

// Array containment
let tagged = Invoice::where(fn(i) => i.tags.has("urgent"))
    .all(db)
    .await?;

// Case-insensitive pattern matching
let search = Invoice::where(fn(i) => i.customer_email.ilike("%@acme.com"))
    .all(db)
    .await?;

Coverage: The operator set includes string patterns (.like, .ilike, .starts_with), array operations (.has, .contains_all, .contained_in), and JSONB accessors (.get, .has_key). Auto-escaping is applied to pattern literals to prevent injection via wildcards.

4. Explicit Eager Loading with Compile-Time Validation

N+1 queries are a common pitfall in ORMs. Fitz defaults to non-eager loading but provides a .preload method that is validated at compile time.

// preload.rs
// Fetches invoices and eagerly loads line items in a single batched query
let invoices = Invoice::preload("items")
    .where(fn(i) => i.status == "paid")
    .all(db)
    .await?;

Safety: If a developer mistypes the relation name, the compiler emits an error:

// Compile error: Invoice has no relation named "itemz"
let bad = Invoice::preload("itemz").all(db).await?;

This contrasts with runtime ORMs where typos in relation names often result in empty collections or runtime exceptions only discovered during testing or production traffic.

5. Closure-Based Transactions

Fitz manages transactions via closures that encapsulate the scope, ensuring automatic commit or rollback based on the result.

// transaction.rs
let result = db.transaction(fn(tx) async {
    let invoice = Invoice::insert(tx, Invoice {
        id: 0,
        customer_email: "alice@example.com".into(),
        status: "draft".into(),
        total_amount: 0.0.into(),
        metadata: Jsonb::default(),
        items: vec![],
        customer: None,
    }).await?;

    let item = LineItem::insert(tx, LineItem {
        id: 0,
        invoice_id: invoice.id,
        description: "Consulting".into(),
        quantity: 10,
        unit_price: 150.0.into(),
    }).await?;

    Ok(item)
}).await;

Behavior: The closure receives a transactional connection tx. If the closure returns Ok, the transaction commits. If it returns Err or panics, the transaction rolls back automatically. There is no manual flush or commit call required, eliminating a class of bugs related to forgotten flushes or uncommitted writes.

6. Declarative Migrations

Schema changes are managed by comparing the code-defined types against the live database schema.

# Generate diff
$ fitz db diff
+ ALTER TABLE invoices ADD COLUMN metadata JSONB NOT NULL DEFAULT '{}';

# Apply migration
$ fitz db migrate
✓ applied migration 20260616_140000_add_metadata.sql

# Rollback
$ fitz db rollback
✓ reverted migration 20260616_140000_add_metadata.sql

Workflow: The fitz db diff command emits idempotent SQL statements. The developer reviews the diff, commits the migration file, and applies it. This provides the safety of version-controlled SQL scripts without the manual authoring burden.

Pitfall Guide

1. Implicit Eager Loading Assumption

   • Mistake: Assuming relations are loaded automatically, leading to N+1 queries.
   • Fix: Fitz defaults to non-eager. Always use .preload("relation_name") when accessing related data. The compiler will catch typos in relation names, but you must explicitly request the load.

2. Ignoring Migration Diffs

   • Mistake: Running fitz db migrate without reviewing fitz db diff output.
   • Fix: Treat db diff output as code review material. While Fitz generates accurate SQL, large table alterations (e.g., ALTER COLUMN type) may require downtime planning. Review the SQL for operational impact before applying.

3. Transaction Scope Leaks

   • Mistake: Attempting to use the main database connection inside a transaction closure.
   • Fix: The transaction closure provides a tx handle. Use tx for all operations within the closure. Using the outer db connection bypasses the transaction and can cause consistency errors.

4. Runtime Schema Drift

   • Mistake: Manually altering the database schema outside of Fitz migrations.
   • Fix: Fitz relies on the code-defined types as the source of truth. Manual schema changes will cause fitz db diff to report discrepancies. Always use fitz db migrate to apply changes, ensuring the code and database remain synchronized.

5. Over-Reliance on Autogenerate in Legacy Stacks

   • Mistake: Using Alembic's --autogenerate without manual review, leading to incorrect migrations.
   • Fix: If migrating from a fragmented stack, recognize that autogenerate is a heuristic, not a guarantee. Fitz eliminates this risk by deriving migrations directly from the type definitions, removing the need for heuristic generation.

6. Memory Bloat from Runtime AST

   • Mistake: Deploying high-concurrency applications with ORMs that construct ASTs per request.
   • Fix: Benchmark memory usage under load. Fitz's compile-time SQL generation reduces memory overhead by approximately 5× compared to runtime DSL ORMs, as evidenced by reproducible benchmarks.

Production Bundle

Action Checklist

• Define Types: Create @table types for all entities, including fields, types, and relationship decorators.
• Review Diffs: Run fitz db diff after every schema change and commit the generated SQL file.
• Use Preload: Explicitly call .preload("relation") for any query that accesses related data to avoid N+1 queries.
• Wrap Mutations: Enclose all write operations in db.transaction closures to ensure atomicity and automatic rollback.
• Leverage Operators: Use native Postgres operators (.ilike, .has, .get) for complex queries instead of raw SQL strings.
• Benchmark: Verify memory and throughput improvements in staging, especially for high-RPS endpoints.
• Audit Migrations: Review fitz db diff output for large alterations; plan operational windows for table rewrites.

Decision Matrix

Scenario	Recommended Approach	Why	Cost Impact
High-Concurrency API	Fitz	Compile-time SQL and low memory footprint sustain high RPS with minimal resource usage.	Lower infrastructure costs due to efficiency.
Strict Schema Compliance	Fitz	Single source of truth eliminates drift; compiler enforces consistency.	Reduced maintenance overhead and bug fixes.
Legacy Python Migration	Incremental Fitz	Fitz can coexist or replace fragmented stacks; migration path via db diff.	Moderate initial migration effort; long-term savings.
Complex Ad-Hoc Queries	Raw SQL / Fitz Hybrid	Use Fitz for standard CRUD; fall back to parameterized raw SQL for edge cases.	Flexibility without sacrificing safety.
Team with Rust Expertise	Fitz	Leverages Rust's type system and performance; natural syntax for closures.	High developer productivity and code quality.

Configuration Template

Use this template to define a new entity with relationships and operators.

// models/order.rs
use fitz::prelude::*;

#[table("orders")]
pub type Order {
    #[primary]
    pub id: Int,
    
    pub customer_id: Int,
    
    pub status: Str,
    
    pub total: Decimal,
    
    pub tags: Array<Str>,
    
    pub notes: Jsonb,
    
    #[has_many("OrderItem", "order_id")]
    pub items: List<OrderItem>,
    
    #[belongs_to]
    pub customer: Customer?,
}

#[table("order_items")]
pub type OrderItem {
    #[primary]
    pub id: Int,
    
    pub order_id: Int,
    
    pub product_sku: Str,
    
    pub qty: Int,
    
    pub price: Decimal,
}

CLI Workflow:

# Initialize project
$ fitz init

# Generate schema from types
$ fitz db diff
$ fitz db migrate

# Run queries
$ cargo run

Quick Start Guide

1. Install Fitz: Add the Fitz crate to your Rust project and configure the database connection in fitz.toml.
2. Define Schema: Create @table types with fields and relationship decorators.
3. Generate Migrations: Run fitz db diff to review changes, then fitz db migrate to apply them.
4. Query Data: Use Entity::where(fn(e) => ...).preload("rel").all(db).await to fetch data with compile-time safety.
5. Deploy: Commit migration files and type definitions; Fitz ensures schema consistency across environments.