Skip to main content

When Not to Use ActiveRecord (and What to Use Instead)

ActiveRecord is great until you need exact SQL semantics. These are the cases where SQL/Arel/views are safer.

The pragmatic rule: Use ActiveRecord for 80% of queries. When you hit its limits (window functions, complex CTEs, exact LEFT JOIN semantics), don't fight it — use raw SQL or database views.

Use ActiveRecord When…

  • Your query maps to standard Rails patterns: Basic WHERE, JOIN, ORDER BY, GROUP BY, and simple aggregations.
  • You want database portability: ActiveRecord abstracts differences between PostgreSQL, MySQL, SQLite.
  • You're working with associations: Eager loading (.includes), scopes, and model methods work great.
  • Future maintainability matters: Other Rails developers can read and modify ActiveRecord code faster than raw SQL.

Prefer Raw SQL When…

  • You need window functions: ROW_NUMBER() OVER, RANK(), LAG/LEAD. Rails 7+ has limited support, but raw SQL is clearer.
  • Complex reporting queries: Multi-step CTEs, UNION, FULL OUTER JOIN, recursive queries. Don't make ActiveRecord do SQL's job.
  • Database-specific features: PostgreSQL arrays/jsonb, MySQL JSON functions, Postgres full-text search. Use the database's native features.
  • You've tried ActiveRecord and the SQL is ugly: If .to_sql shows a mess of subqueries or the query is harder to read than raw SQL, just use SQL.

How to use raw SQL safely:

# Option 1: Execute and map to models
User.find_by_sql("SELECT * FROM users WHERE complex_condition")

# Option 2: Connection.execute for non-model queries
ActiveRecord::Base.connection.execute("SELECT COUNT(*) ...").first['count']

# Option 3: Prepared statements to prevent SQL injection
User.find_by_sql(["SELECT * FROM users WHERE name = ?", params[:name]])

Use Arel When…

Arel is the low-level SQL AST library that ActiveRecord uses internally. You rarely need it directly, but it's useful for:

  • Dynamic query building: Complex OR/AND conditions based on user input. Arel lets you compose conditions programmatically without string concatenation.
  • Functions ActiveRecord doesn't expose: COALESCE, NULLIF, custom database functions.
  • Reusable query fragments: Extract common Arel nodes into methods, compose them in different queries.

Arel example:

users = User.arel_table
query = users[:created_at].gt(1.week.ago)
        .and(users[:active].eq(true))
        .or(users[:admin].eq(true))

User.where(query)

When NOT to use Arel: If ActiveRecord's .where and .or methods work, use those. Arel is more verbose.

Consider Database Views / Materialized Views When…

Database views let you encapsulate complex SQL and query it like a table. Materialized views (PostgreSQL) cache results for even faster queries.

  • Complex queries used everywhere: Instead of duplicating SQL across models/controllers, define it once as a view.
  • Reporting dashboards: Aggregations that don't need real-time data. Refresh a materialized view every 5 minutes, query it instantly.
  • You want Rails-style model access: Map an ActiveRecord model to a view, get .where, .order, etc. for free.

Create a view in Rails migration:

class CreateUserStatsView < ActiveRecord::Migration[7.0]
  def up
    execute <<-SQL
      CREATE VIEW user_stats AS
      SELECT users.id, users.name,
             COUNT(posts.id) as post_count,
             MAX(posts.created_at) as latest_post_at
      FROM users
      LEFT JOIN posts ON users.id = posts.user_id
      GROUP BY users.id, users.name
    SQL
  end

  def down
    execute "DROP VIEW IF EXISTS user_stats"
  end
end

# Map to ActiveRecord
class UserStat < ApplicationRecord
  self.primary_key = 'id'
end

# Use like a normal model
UserStat.where('post_count > 10').order(latest_post_at: :desc)

Practical Rule of Thumb + Testing Strategy

Decision Flowchart

  1. 1
    Start with ActiveRecord. Try the simplest approach first: .where, .joins, .order.
  2. 2
    Run .to_sql and check the output. If it matches your intent, you're done. If not, continue.
  3. 3
    Try Arel for dynamic conditions. If you need programmatic query building, Arel might help.
  4. 4
    Switch to raw SQL if ActiveRecord fights you. Don't spend 2 hours making ActiveRecord do what SQL does in 5 lines.
  5. 5
    Consider a view if you'll reuse the query. Encapsulate complex SQL in a view, query it from Rails like a table.

Testing Your Decision

No matter which approach you choose, validate it: 

# 1. Compare row counts
original_count = ActiveRecord::Base.connection.execute(sql).count
new_count = YourModel.your_scope.count
raise unless original_count == new_count

# 2. Compare actual IDs
original_ids = ActiveRecord::Base.connection.execute(sql).map { |r| r['id'] }.sort
new_ids = YourModel.your_scope.pluck(:id).sort
raise unless original_ids == new_ids

# 3. Add a regression spec
it "returns correct user stats" do
  expect(UserStat.count).to eq(expected_count)
  expect(UserStat.first.post_count).to eq(expected_post_count)
end

Complete Tool Comparison

Tool/Method Primary Use Setup Time Rails Focus Cost Team Collab

SQL to ActiveRecord

Our Tool

 SQL Conversion 0 seconds Native Free Individual

Manual Conversion

Hand-coding

 Custom Logic 20-45 min Variable Free Limited

dbdiagram.io

Visual DB Design

 Schema Design 2-5 min Generic Freemium Strong

DrawSQL

Collaborative Design

 Team Planning 5-10 min Generic Freemium Excellent

Rails Generators

Built-in Tools

 Model Creation Instant Native Free Code-based

Open Source Tools

GitHub Projects

 Various Setup Required Limited Free Depends

Detailed Tool Analysis

Visual Database Design Tools

Best For:

  • New project planning and design
  • Team collaboration on database structure
  • Creating database documentation
  • Multi-framework projects (not Rails-only)

Popular Options:

dbdiagram.io

Free tier available, clean interface, code generation

DrawSQL

Strong collaboration features, version control

Lucidchart

Enterprise features, broader diagramming tools

Limitations for Rails Developers:

  • No direct ActiveRecord code generation
  • Time-consuming for existing SQL queries
  • Requires manual Rails convention application
  • Limited complex query pattern support
💡 Pro Tip:

Use visual tools for initial design, then convert specific queries with our SQL-to-ActiveRecord tool.

Command Line & Open Source Tools

Popular Options:

Rails Built-in Generators

rails generate model, rails generate migration

✓ Native Rails integration

Annotate Gem

Auto-generates model annotations from schema

✓ Keeps models in sync with DB

Rails ERD

Generates entity-relationship diagrams

✓ Visual documentation from code

Strengths & Limitations:

✓ Strengths:
  • • Perfect Rails integration
  • • No external dependencies
  • • Free and open source
  • • Command-line workflow
✗ Limitations:
  • • No SQL-to-ActiveRecord conversion
  • • Limited to basic generation patterns
  • • Requires Rails environment setup
  • • Not suitable for query conversion

How to Choose the Right Tool

Decision Framework

Ask Yourself:

1. What's your primary goal?

Convert existing SQL vs Design new schema vs Learn Rails patterns

2. Are you working alone or with a team?

Individual development vs Team collaboration requirements

3. How much time do you want to invest?

Instant results vs Setup and learning curve acceptable

4. Is this Rails-specific or multi-framework?

Rails-only project vs Multiple database technologies

Recommended Combinations:

Rails Development Team

Visual tool for planning + Our converter for implementation

Solo Developer

Our SQL-to-ActiveRecord tool for speed + Rails generators for models

Learning Rails

Our tool for examples + Manual practice for understanding

Enterprise Team

Visual collaborative tool + Our converter + Documentation tools

Ready to Convert Your SQL?

When ActiveRecord works, use it. When it doesn't, use our converter to get started, then switch to raw SQL or views as needed.

Try the Converter → Performance Tips →
Free forever • No registration • Production-safe