class ReportQuery
SQL = <<~SQL.freeze
SELECT date_trunc('day', events.created_at) AS day,
count(*) AS total,
count(*) FILTER (WHERE events.kind = 'purchase') AS purchases
FROM events
WHERE events.account_id = $1
AND events.created_at >= $2
AND events.created_at < $3
GROUP BY 1
ORDER BY 1
SQL
def initialize(account_id:, since:, until_time:)
@account_id = account_id
@since = since
@until_time = until_time
end
def run
result = connection.exec_query(SQL, "ReportQuery daily_activity", binds)
result.to_a
end
private
def binds
[
bind("account_id", @account_id, ActiveModel::Type::Integer.new),
bind("since", @since, ActiveModel::Type::DateTime.new),
bind("until_time", @until_time, ActiveModel::Type::DateTime.new)
]
end
def bind(name, value, type)
ActiveRecord::Relation::QueryAttribute.new(name, value, type)
end
def connection
ActiveRecord::Base.connection
end
end
class ReportsController < ApplicationController
rescue_from ArgumentError, with: :bad_request
def daily_activity
rows = ReportQuery.new(
account_id: Integer(params.require(:account_id)),
since: Time.iso8601(params.require(:since)),
until_time: Time.iso8601(params.require(:until))
).run
render json: { data: rows }
end
private
def bad_request(error)
render json: { error: error.message }, status: :bad_request
end
end
Sometimes ActiveRecord's query interface gets in the way — a reporting query needs a window function, a lateral join, or a GROUP BY shape that the DSL cannot express cleanly. The temptation is to interpolate values straight into a SQL string, which opens the door to injection and prevents the database from reusing prepared-statement plans. This snippet shows the safe alternative: drop to raw SQL via exec_query while passing every value through bind parameters, so the driver sends the query and its arguments separately.
In ReportQuery the query text uses positional placeholders ($1, $2) rather than string interpolation. Each value is wrapped in an ActiveRecord::Relation::QueryAttribute built from a typed ActiveModel::Type, which is what exec_query expects for its binds argument. The bind helper centralizes that construction so casting is consistent, and column_types maps each parameter to the right type — Integer for the account id, DateTime for the range bounds. Because the values never touch the SQL string, a hostile since value cannot alter the statement.
The raw result comes back as an ActiveRecord::Result, and .to_a yields plain hashes keyed by column name. run returns those rows directly so callers stay decoupled from the driver detail. Notice the query name "ReportQuery daily_activity" passed to exec_query; that label surfaces in the Rails log and in tools like pg_stat_statements, which makes slow raw queries far easier to attribute later.
The ReportsController in the second tab shows the calling side. Params are coerced with Integer(...) and Time.iso8601 before they ever reach the query object, so bad input fails loudly with a 400 instead of producing malformed SQL. The controller never sees a bind object — it just passes ordinary Ruby values.
The trade-off is real: raw SQL bypasses model callbacks, scopes, and type coercion on the way out, so it suits read-only analytics far more than writes. The pitfall to avoid is mixing interpolation with binds; the moment any user value is concatenated into the string, the safety guarantee is gone. Reaching for this pattern makes sense when the query is genuinely beyond the DSL but the values are still untrusted.
Related snips
class CommentsController < ApplicationController
before_action :set_post
def create
@comment = @post.comments.build(comment_params)System test: asserting Turbo Stream responses
class Post < ApplicationRecord
belongs_to :author, class_name: 'User'
has_many :comments, dependent: :destroy
scope :published, -> { where.not(published_at: nil).where('published_at <= ?', Time.current) }
scope :draft, -> { where(published_at: nil) }ActiveRecord scopes for reusable query logic
payload = {
sub: user.id,
iss: 'https://auth.example.com',
aud: 'codesnips-api',
exp: 15.minutes.from_now.to_i,
iat: Time.now.to_i,JWT issuance and verification without common footguns
module Api
module V1
class UsersController < BaseController
def show
user = User.includes(:profile).find(params[:id])ETags for conditional requests and caching
class PostsController < ApplicationController
def index
@posts = Post.includes(:author)
.order(created_at: :desc)
.page(params[:page])
.per(10)Turbo Frames: infinite scroll with lazy-loading frame
package dbutil
import (
"context"
"github.com/jackc/pgconn"Retry Postgres serialization failures with bounded attempts
Share this code
Here's the card — post it anywhere.
