ORM vs Raw SQL¶

Status: 🟢 Active | Owner: Data Engineering

Decision Guide¶

Situation	Recommendation
Simple CRUD on a single entity	ORM
Loading an entity graph (orders + items + customer)	ORM with explicit fetch joins
Aggregation query (GROUP BY, window functions)	Raw SQL or jOOQ
Report / analytics query	Raw SQL
Bulk upsert or batch insert	Raw SQL (`INSERT ... ON CONFLICT`)
Full-text search	Raw SQL (use `@@` operator in PostgreSQL)
Stored procedure calls	Raw SQL
Cross-database portability required	ORM (JPQL/HQL)

The guideline is: use the ORM for entity lifecycle management; use SQL when the query is naturally expressed in SQL.

Why Not ORM-Only?¶

ORM abstractions leak at scale. When a query generates a poor execution plan, the ORM often obscures what SQL is being produced, making diagnosis harder. Raw SQL keeps the database interaction transparent and auditable.

Why Not SQL-Only?¶

Hand-written SQL for every insert, update, and delete is repetitive and error-prone. ORMs provide transaction management, optimistic locking, audit trails, and schema migration integration that would all need to be rebuilt manually.

Spring Data JPA Best Practices¶

// ✅ Use the ORM for entity mutations
@Transactional
public void confirmOrder(String orderId) {
    Order order = repository.findById(orderId)
        .orElseThrow(() -> new OrderNotFoundException(orderId));
    order.confirm();  // domain method — JPA tracks the change
}

// ✅ Drop to native SQL for complex reads
@Query(value = """
    SELECT
        c.id         AS customer_id,
        c.name,
        COUNT(o.id)  AS order_count,
        SUM(o.total) AS lifetime_value
    FROM customers c
    LEFT JOIN orders o ON o.customer_id = c.id
    WHERE o.created_at >= :since
    GROUP BY c.id, c.name
    ORDER BY lifetime_value DESC
    LIMIT :limit
    """, nativeQuery = true)
List<CustomerSummaryProjection> findTopCustomers(
    @Param("since") Instant since,
    @Param("limit") int limit
);

jOOQ for Complex SQL¶

jOOQ provides type-safe SQL construction with full access to database-specific features:

// Type-safe, dialect-aware SQL — catches column name errors at compile time
Result<Record3<String, String, BigDecimal>> result = dsl
    .select(ORDERS.ID, CUSTOMERS.NAME, ORDERS.TOTAL)
    .from(ORDERS)
    .join(CUSTOMERS).on(ORDERS.CUSTOMER_ID.eq(CUSTOMERS.ID))
    .where(ORDERS.STATUS.eq(OrderStatus.PENDING))
    .orderBy(ORDERS.CREATED_AT.desc())
    .limit(100)
    .fetch();

Avoiding the `Open Session in View` Anti-Pattern¶

The Spring OpenSessionInView interceptor keeps the JPA session open for the duration of the HTTP request, enabling lazy loading in the view layer. This is disabled in our configuration:

spring:
  jpa:
    open-in-view: false

All data needed for a response must be loaded within a @Transactional service call. Lazy loading in controllers or serialisers is a source of N+1 problems and unexpected database connections.

References¶

Last reviewed: 2025-Q4 | Owner: Data Engineering