DEV Community: Yash Pandey

Deep Dive: Mastering Active Record Queries & Raw SQL

Yash Pandey — Thu, 01 Jan 2026 14:13:04 +0000

Happy New Year! As we kick off 2026, many of us are looking at our codebases and thinking about optimization.

When we first start with Ruby on Rails, ActiveRecord feels like magic. User.find(id) or Post.all are simple and intuitive. But as your application grows and your data becomes more complex, basic CRUD isn't enough. You need to perform aggregations, filter based on calculated values, and sometimes, drop down into raw SQL.

In this article, we’ll dive deep into the "heavy lifting" side of ActiveRecord: Select vs. Pluck, Group & Having, and Raw SQL.

1. Select vs. Pluck: The Memory Battle

Many developers use these interchangeably, but they behave very differently under the hood.

.select: Fetches only the columns you specify but still returns an ActiveRecord Relation object. This means Rails instantiates a Ruby object for every row returned.
.pluck: Queries the database and returns a raw Ruby Array of values. It skips the object instantiation entirely.

When to use which?

If you just need a list of IDs or names for a dropdown, use pluck. If you need to call model methods on the results, use select.

# ❌ High memory usage (instantiates 10,000 User objects just to get emails)
emails = User.select(:email).map(&:email)

# ✅ Low memory usage (returns a simple array of strings)
emails = User.pluck(:email)

2. Group and Having: Filtering the Aggregates

Grouping is essential for reporting. However, the biggest point of confusion is usually the difference between where and having.

.where: Filters individual rows before they are grouped.
.having: Filters the groups after the aggregation is calculated.

The Scenario

You want to find users who have spent more than $500 in total across all their orders. You can't use where for the sum, because the sum hasn't happened yet when where runs.

# This query groups orders by user_id and filters for groups where the sum is > 500
User.joins(:orders)
    .group("users.id")
    .having("SUM(orders.amount) > ?", 500)
    .select("users.*, SUM(orders.amount) as total_spent")

3. Complex Ordering with Logic

Sometimes, order(created_at: :desc) isn't enough. You might need to sort by a specific logic, like "Urgent" items first, then "Pending," then "Completed."

For this, we can use Arel.sql to pass fragments of SQL logic safely.

# Ordering by a custom CASE statement
tasks = Task.order(Arel.sql("
  CASE 
    WHEN status = 'urgent' THEN 1 
    WHEN status = 'pending' THEN 2 
    ELSE 3 
  END ASC"
))

4. When ActiveRecord Isn't Enough: Raw SQL

ActiveRecord is powerful, but it’s not perfect. For highly complex reports or database-specific features (like Postgres window functions), raw SQL is the way to go.

There are two main ways to execute raw SQL:

A. `find_by_sql`

Use this when you want the result to be returned as ActiveRecord objects.

users = User.find_by_sql("SELECT * FROM users WHERE some_complex_condition = true")

B. `ActiveRecord::Base.connection.execute`

Use this for pure data manipulation or when you don't need Model objects back. It returns an ActiveRecord::Result object which behaves like an array of hashes.

⚠️ Security Warning: Never interpolate strings directly into raw SQL (e.g., "#{params[:id]}"). Always use placeholders (?) or sanitized inputs to prevent SQL Injection.

Conclusion

Mastering these complex queries is what moves you from a "Rails Developer" to a "Database Expert."

As I move further into AI Engineering in 2026, I’m finding that these data manipulation skills are the exact foundation needed for handling large datasets in Python and Pandas. If you can think in SQL sets, you are halfway to thinking in Data Frames!

What’s the most complex query you’ve ever had to write in Rails? Let me know in the comments!

Mastering Middleware In Rails: Enhance Your App's Request Lifecycle

Yash Pandey — Sat, 30 Nov 2024 10:18:42 +0000

Middleware acts as a layer between the client and server. It processes requests before they reach the controller and modifies the response when it is sent back to the client.

Middleware Lifecycle

Request Phase: Middlewares can intercept, modify, or reject the HTTP method before it reaches the rails routes and controllers.
Examples:
- Authenticate checks
- Logging request details
- Rate Limiting
- Data Transformation
Response Phase: Middleware can also intercept or modify the response before it is returned to the client.
Examples:
- Adding security headers
- compressing the response
- caching the response

Create the custom middleware class

We can create the custom middlewares inside the lib directory of our rails controller, if it doesn't exist, create the lib directory.

# lib/custom_middleware.rb
class CustomMiddleware
  def initialize(app)
    @app = app
  end

  def call(env)
    # Logic before the request reaches the controller
    puts "Incoming request path: #{env['REQUEST_PATH']}" 

    # Call the next middleware
    status, header, response = @app.call(env)

    # Logic before response reaches client
    headers['X-Custom-Header'] = 'Hello from middleware'

    # Return the modified response
    [status, header, response]
  end
end

Rails does not automatically load files in the lib directory, so we need to ensure it is loaded in config/application.rb

config.autoload_paths << Rails.root.join('lib')

We need to register the middleware in the middleware stack

config.middleware.use CustomMiddleware

We can verify that our middleware is loaded by running:

rails middleware

Whenever we add or modify the rails middleware, we need to restart the rails server for the changes to take effect.

rails s

Conclusion:

Middleware in Rails is a set of components that process HTTP requests and responses. It provides a modular way to handle task like authentication, logging, and session management.

Understanding Rails Initializers: Configuring Your Application Easily

Yash Pandey — Sat, 23 Nov 2024 14:22:23 +0000

Rails initializers are files that run when the Rails application starts. They allow developers to configure third-party libraries. In this post, we will cover the points below.

What are the rails initializers?
Why are they important in rails applications?
Examples of common configurations.

What are the rails initializers?

Initializers are .rb files located inside the config/initializers directory. They are executed when Rails applications load up. Initializers are executed alphabetically, so consider file names if order matters.

Why are they important in rails applications?

Initializer provides a way to configure and customize applications during the bootup process. It allows developers to set the global configuration and initialize resources before the application starts accepting requests.

Examples of common configurations

# config/initializers/time_zone.rb
Rails.application.config.time_zone = 'Asia/Kolkata'

It will set the default timezone for the entire application's Indian Standard Time(IST).

Conclusion

Rails initializers are key to managing your rail application's configuration and setup. They help centralize configuration, integrate third-party services, optimize performances, and create more modular code.

DEV Community: Yash Pandey

Deep Dive: Mastering Active Record Queries & Raw SQL

1. Select vs. Pluck: The Memory Battle

When to use which?

2. Group and Having: Filtering the Aggregates

The Scenario

3. Complex Ordering with Logic

4. When ActiveRecord Isn't Enough: Raw SQL

A. find_by_sql

B. ActiveRecord::Base.connection.execute

Conclusion

Mastering Middleware In Rails: Enhance Your App's Request Lifecycle

Middleware Lifecycle

Create the custom middleware class

Conclusion:

Understanding Rails Initializers: Configuring Your Application Easily

What are the rails initializers?

Why are they important in rails applications?

Examples of common configurations

Conclusion

A. `find_by_sql`

B. `ActiveRecord::Base.connection.execute`