ORMs vs Raw SQL

In Rust, you have three dominant approaches for database access: compile-time checked raw SQL (sqlx), a type-safe ORM (diesel), and an async ORM (sea-orm). This document compares all three with identical queries, explains the trade-offs, and gives concrete guidance on when to use each.

The Same Query in Three Frameworks

Consider this requirement: fetch the 10 most recent completed orders for a given user, returning the order ID, total, and creation timestamp.

sqlx (compile-time checked SQL)

STRUCTURE Order:
    id ← integer
    total_cents ← integer
    created_at ← datetime

PROCEDURE RECENT_ORDERS(pool, user_id):
    RETURN AWAIT QUERY pool:
        "SELECT id, total_cents, created_at
         FROM orders
         WHERE user_id = user_id AND status = 'completed'
         ORDER BY created_at DESC
         LIMIT 10"

How it works: The query_as! macro connects to your database at compile time, verifies the SQL syntax, checks that the columns exist and the types match, and generates the deserialization code. If you rename a column or change its type, the code fails to compile — not at runtime.

Requirements: A DATABASE_URL environment variable must point to a running database at compile time. Use sqlx prepare to generate offline metadata for CI builds without a database.

diesel (ORM)

// Schema definition (generated or manual)
TABLE orders:
    id ← integer
    user_id ← integer
    total_cents ← integer
    status ← text
    created_at ← timestamp

STRUCTURE Order:
    id ← integer
    total_cents ← integer
    created_at ← datetime

PROCEDURE RECENT_ORDERS(conn, user_id):
    RETURN orders
        .FILTER(user_id = user_id)
        .FILTER(status = "completed")
        .ORDER_BY(created_at DESCENDING)
        .LIMIT(10)
        .SELECT(Order fields)
        .LOAD(conn)

How it works: Diesel generates a Rust DSL from your database schema (diesel print-schema). Queries are built using method chaining on table objects. The schema is checked at compile time against the Rust type definitions — not against the live database.

sea-orm (async ORM)

// Entity generated by ORM code generator

PROCEDURE RECENT_ORDERS(db, user_id):
    RETURN AWAIT Entity.FIND()
        .FILTER(UserId = user_id)
        .FILTER(Status = "completed")
        .ORDER_BY(CreatedAt DESCENDING)
        .LIMIT(10)
        .ALL(db)

How it works: Sea-orm generates entity files from your database schema. It is async-native (built on sqlx under the hood) and uses a query builder pattern similar to Diesel but with async support.

Comparison at a Glance

| Feature | sqlx | diesel | sea-orm | |---------|------|--------|---------| | Compile-time checking | Against live DB | Against schema.rs | No (runtime) | | Async support | Native | Via diesel-async | Native | | Query style | Raw SQL strings | Rust DSL | Rust DSL | | Complex queries | Full SQL power | Limited (escape hatches) | Limited (falls back to raw SQL) | | Learning curve | Know SQL, learn macros | Learn Diesel DSL | Learn Sea-orm API | | Maturity | Very active, widely adopted | Mature, stable | Growing, newer |

A Complex Query: Joins and Aggregation

Requirement: for each active user, show their name, email, total number of orders, and total spending.

sqlx

STRUCTURE UserSummary:
    name ← string
    email ← string
    order_count ← integer
    total_spent ← integer

PROCEDURE USER_SUMMARIES(pool):
    RETURN AWAIT QUERY pool:
        "SELECT
            u.name,
            u.email,
            COUNT(o.id) AS order_count,
            COALESCE(SUM(o.total_cents), 0) AS total_spent
        FROM users u
        LEFT JOIN orders o ON u.id = o.user_id AND o.status = 'completed'
        WHERE u.status = 'active'
        GROUP BY u.id, u.name, u.email
        ORDER BY total_spent DESC"

The ! suffix in "order_count!" tells sqlx the value is non-nullable (overriding its inference for aggregates).

diesel and sea-orm

Both ORMs struggle here. Diesel requires sql() escape hatches for COALESCE and aggregate ordering — the DSL cannot express every SQL function. Sea-orm cannot express the join with aggregation at all and falls back to find_by_statement with raw SQL, at which point you lose the ORM's benefits entirely. This is the pattern with all Rust ORMs: simple CRUD is elegant, complex queries escape to raw SQL.

The N+1 Problem With ORMs

ORMs abstract away SQL, which makes it easy to write code that generates far more queries than necessary.

How it happens

// ORM example: fetch users, then fetch each user's orders separately
PROCEDURE DASHBOARD(conn):
    users ← users.FILTER(status = "active").LIMIT(50).LOAD(conn)  // Query 1

    result ← empty list
    FOR EACH user IN users:
        user_orders ← orders.FILTER(user_id = user.id).LOAD(conn)  // Queries 2..51
        APPEND (user, user_orders) TO result
    RETURN result
    // Total: 51 queries

The ORM-idiomatic fix (diesel)

Diesel provides belonging_to for batch-loading associations:

PROCEDURE DASHBOARD(conn):
    users ← users.FILTER(status = "active").LIMIT(50).LOAD(conn)  // Query 1

    orders ← Order.BELONGING_TO(users)
        .LOAD(conn)                    // Query 2 -- uses WHERE user_id IN (...)
        .GROUPED_BY(users)

    result ← ZIP(users, orders)
    RETURN result
    // Total: 2 queries

The sqlx approach (no N+1 by design)

With raw SQL, you write the query you want. There is no ORM to generate extra queries behind your back — you write a single LEFT JOIN query and group the results in application code. N+1 is structurally impossible.

When to Use Which

Choose sqlx when: your team knows SQL, you have complex queries (CTEs, window functions, LATERAL joins), you want compile-time guarantees, or you need async-native access with minimal abstraction.

Choose diesel when: your queries are mostly CRUD, you prefer a Rust DSL over SQL strings, you want schema-level type safety without a live database, or you are building a synchronous application.

Choose sea-orm when: you want an async-native ORM, you need multi-backend support (Postgres/MySQL/SQLite), or you want rapid development with code generation for simple CRUD.

Hybrid approach (recommended): Use sqlx for complex queries and an ORM for simple CRUD in the same codebase. There is no rule that says you must pick one.

Trade-offs Summary

| Dimension | sqlx | diesel | sea-orm | |-----------|------|--------|---------| | Boilerplate for simple CRUD | More (write full SQL) | Less (DSL is concise) | Least (code generation) | | Complex query support | Excellent (it is just SQL) | Limited (escape hatches needed) | Limited (falls back to raw SQL) | | Compile-time safety | Strongest (validates against DB) | Strong (validates against schema.rs) | Weakest (runtime errors for bad queries) | | N+1 risk | None (you write the query) | Present (mitigated with belonging_to) | Present (mitigated with eager loading) | | Async support | Native | Via diesel-async | Native | | Migration story | Good (SQL files) | Good (SQL files with up/down) | Good (Rust-based migrations) | | Debugging | Easy (you see the SQL) | Harder (DSL hides the SQL) | Harder (DSL hides the SQL) |

Pitfalls

Using an ORM to avoid learning SQL. ORMs generate SQL. If you do not understand the SQL they produce, you cannot debug performance problems. Learn SQL first, then use an ORM if it helps productivity.
Blindly trusting ORM-generated queries. Always check what queries your ORM actually generates, especially for joins and aggregations. Log queries in development and review them.
Not running sqlx prepare in CI. Without the offline metadata, sqlx requires a live database to compile. Run cargo sqlx prepare and commit the .sqlx directory so CI builds work without a database.
Letting diesel's schema.rs drift. After a migration, always re-run diesel print-schema and commit the updated file. A stale schema.rs compiles fine but fails at runtime.
Using sea-orm's find_with_related in a loop. This is the N+1 trap. Use find_also_related or write a custom query for batch loading.
Over-abstracting with repository patterns. Wrapping sqlx in three layers of abstraction defeats the purpose of using raw SQL. Keep the database layer thin.