Active Record vs Raw SQL

Direct Answer

Use Active Record relations for most Rails queries because they compose with scopes and associations, bind values safely, and return application models. Use carefully isolated SQL when the database operation is clearer or more capable in SQL—for example, a recursive CTE, window function, vendor-specific feature, bulk statement, or measured hot query that the relation API cannot express well. Raw SQL should keep values bound, define its return shape, remain adapter-aware, and be protected by focused tests; it is not a shortcut around understanding the generated query.

Deep Dive & Architecture

Active Record is strongest when the relation is still recognisable as a database operation. Scopes can add predicates, joins, projections, and ordering while preserving a relation for later composition. This also allows cross-cutting rules such as account scope to remain visible instead of being rebuilt inside a SQL string.

The abstraction becomes costly when a database-native operation is broken into many round trips or large Ruby collections. A raw query can express one atomic set operation, but only if its parameters, transaction boundary, and result mapping are explicit. Moving text into a heredoc alone does not make the design safer.

Safety has several dimensions. Bind parameters address injection for values, but identifiers and SQL fragments cannot generally be bound the same way and need a trusted allowlist. Read-only intent, timeouts, transaction locks, and permission boundaries still matter even when the syntax is parameterised.

Performance decisions need evidence. The generated SQL from Active Record may already match the hand-written form, and a missing index will harm both. Compare query plans, rows read, allocations, and end-to-end latency before accepting the maintenance and portability cost of a specialised query.

Code Example

class RegionalSalesRanking
  SQL = <<~SQL
    SELECT id, region_id, total_cents,
           ROW_NUMBER() OVER (
             PARTITION BY region_id ORDER BY total_cents DESC
           ) AS regional_rank
    FROM sales
    WHERE sold_at >= $1
  SQL

  def self.since(time)
    bind = ActiveRecord::Relation::QueryAttribute.new(
      'sold_at', time, ActiveRecord::Type::DateTime.new
    )
    ActiveRecord::Base.connection.exec_query(SQL, name, [bind])
  end
end

Common Mistakes & Pitfalls

Using raw SQL with string interpolation (injection) — always bind; or avoiding raw SQL even when N+1 hits.