SimpleQuery

SimpleQuery is a lightweight query builder for ActiveRecord. It gives Rails applications an explicit, chainable API for read-heavy queries, joins, aggregations, read models, bulk updates, and adapter-aware streaming without replacing ActiveRecord.

Use it when you want ActiveRecord-backed SQL with less object-instantiation overhead, predictable result shapes, or a small query/read-model layer around reporting and data-processing code.

Features

• Chainable query builder for ActiveRecord models
• Struct results by default for lightweight reads
• Optional custom read models with explicit attributes
• Hash, Arel, raw SQL, and ActiveRecord-style placeholder conditions
• Inner, left, right, and full joins
• distinct, group, having, order, limit, and offset
• Aggregation helpers: count, sum, avg, min, max, variance, stddev, group_concat, stats, and custom aggregations
• Named simple_scope definitions for reusable SimpleQuery chains
• bulk_update for set-based updates
• stream_each for large result sets on PostgreSQL and MySQL

Installation

Add SimpleQuery to your application's Gemfile:

gem "simple_query"

Then install:

bundle install

Or install it directly:

gem install simple_query

Compatibility

SimpleQuery currently declares support for:

• Ruby >= 2.7
• ActiveRecord >= 7.0, < 8.1

The CI matrix covers ActiveRecord 7.0, 7.1, 7.2, and 8.0 across PostgreSQL and MySQL. ActiveRecord 8.0 is tested on Ruby 3.2 because Rails 8 requires Ruby 3.2 or newer.

Configuration

SimpleQuery does not patch every ActiveRecord model by default. Include it in the models that should expose .simple_query:

class User < ActiveRecord::Base
  include SimpleQuery
end

Or include it globally if you want every ActiveRecord model to have .simple_query:

# config/initializers/simple_query.rb
require "simple_query"

ActiveRecord::Base.include(SimpleQuery)

You can also opt into global inclusion through SimpleQuery's configuration hook:

# config/initializers/simple_query.rb
SimpleQuery.configure do |config|
  config.auto_include_ar = true
end

Basic usage

users = User.simple_query
            .select(:name, :email)
            .where(active: true)
            .order(name: :asc)
            .limit(50)
            .execute

users.first
# => #<struct name="Jane Doe", email="jane@example.com">

execute returns an array of lightweight Struct objects unless you map the query to a custom read model.

Conditions

SimpleQuery accepts several condition styles.

Hash conditions

User.simple_query
    .select(:name, :email)
    .where(active: true, admin: false)
    .execute

Hash conditions are simple equality predicates against the query's base table.

Arel conditions

users = User.arel_table

User.simple_query
    .select(:name)
    .where(users[:created_at].gteq(30.days.ago))
    .execute

Placeholder conditions

Array conditions are sanitized through ActiveRecord's sanitize_sql_array, so they are the preferred way to use SQL fragments with user-provided values:

User.simple_query
    .where(["name LIKE ?", "%Jane%"])
    .execute

User.simple_query
    .where(["email = :email", { email: "jane@example.com" }])
    .execute

Raw SQL conditions

Plain strings are treated as raw SQL fragments:

User.simple_query
    .where("active = TRUE")
    .execute

Only use raw SQL strings with trusted input. For external or user-provided values, prefer hash, Arel, or placeholder conditions.

Joins

Joins are explicit: pass the left table, right table, foreign key, and primary key.

User.simple_query
    .select(:name, :email)
    .join(:users, :companies, foreign_key: :user_id, primary_key: :id)
    .where(Company.arel_table[:name].eq("TechCorp"))
    .execute

Multiple joins can be chained:

User.simple_query
    .select(:name)
    .join(:users, :companies, foreign_key: :user_id, primary_key: :id)
    .join(:companies, :projects, foreign_key: :company_id, primary_key: :id)
    .where(Company.arel_table[:industry].eq("Technology"))
    .where(Project.arel_table[:status].eq("active"))
    .execute

Join helpers are also available:

User.simple_query
    .left_join(:users, :companies, foreign_key: :user_id, primary_key: :id)
    .select(:name)
    .execute

Supported join types:

• join(..., type: :inner) / join(...)
• left_join(...)
• right_join(...)
• full_join(...)

Database support for right and full outer joins depends on the adapter and database version.

Selecting, ordering, and paging

User.simple_query
    .select(:id, :name, "LOWER(email) AS normalized_email")
    .distinct
    .where(active: true)
    .order(name: :asc)
    .limit(25)
    .offset(50)
    .execute

select accepts symbols, SQL strings, and Arel nodes. SQL strings are inserted as SQL fragments, so keep them trusted.

order accepts a hash of column names to directions, such as order(created_at: :desc).

Aggregations

SimpleQuery can build common aggregate expressions without hand-writing each SQL expression.

Company.simple_query
       .count
       .sum(:annual_revenue)
       .avg(:annual_revenue)
       .min(:annual_revenue)
       .max(:annual_revenue)
       .execute

Custom aliases are supported:

Company.simple_query
       .count(:id, alias_name: "company_count")
       .sum(:annual_revenue, alias_name: "total_revenue")
       .execute

Grouped aggregations work with selected fields:

Company.simple_query
       .select(:industry)
       .count(alias_name: "company_count")
       .sum(:annual_revenue, alias_name: "total_revenue")
       .group(:industry)
       .execute

Convenience helpers include:

Company.simple_query.stats(:annual_revenue).execute
Company.simple_query.total_count.execute
User.simple_query.variance(:score).stddev(:score).execute
User.simple_query.group_concat(:name, separator: ", ").execute

For database-specific expressions, use custom_aggregation with trusted SQL:

Company.simple_query
       .custom_aggregation("COUNT(DISTINCT industry)", "unique_industries")
       .execute

Custom read models

By default, query results are returned as Struct objects. For named methods and domain-specific result objects, define a read model:

class UserSummary < SimpleQuery::ReadModel
  attribute :identifier, column: :id
  attribute :full_name, column: :name
end

Map query results to the read model:

users = User.simple_query
            .select("users.id AS id", "users.name AS name")
            .where(active: true)
            .map_to(UserSummary)
            .execute

users.first.identifier
users.first.full_name

The selected SQL aliases must match the read model's configured column names.

Named scopes

Use simple_scope to define reusable query fragments on a model that includes SimpleQuery:

class User < ActiveRecord::Base
  include SimpleQuery

  simple_scope :active do
    where(active: true)
  end

  simple_scope :admins, -> { where(admin: true) }

  simple_scope :by_name do |name|
    where(name: name)
  end
end

Scopes are evaluated in the context of the SimpleQuery builder and can be chained with the normal DSL:

User.simple_query
    .active
    .admins
    .by_name("Jane Doe")
    .select(:id, :name)
    .execute

Lazy execution and streaming

lazy_execute returns an Enumerator over the query results:

enumerator = User.simple_query
                 .select(:name)
                 .where(active: true)
                 .lazy_execute

enumerator.each do |user|
  puts user.name
end

lazy_execute is useful when you want enumerator-style consumption, but it still uses ActiveRecord's select_all internally.

For large PostgreSQL or MySQL result sets, use stream_each:

User.simple_query
    .select(:id, :email)
    .where(active: true)
    .stream_each(batch_size: 10_000) do |user|
  puts user.email
end

Adapter behavior:

• PostgreSQL: uses a server-side cursor with DECLARE / FETCH; batch_size controls the number of rows fetched per cursor read
• MySQL: uses mysql2 streaming with stream: true, cache_rows: false, and as: :hash; batch_size is accepted by the public API but does not control MySQL batching
• Other adapters: stream_each raises an error

Bulk updates

bulk_update builds and executes a set-based UPDATE for the current query conditions:

User.simple_query
    .where(active: false)
    .bulk_update(set: { status: 0 })

bulk_update sends SQL directly through the ActiveRecord connection and does not instantiate models or run ActiveRecord callbacks. If no where conditions are present, it updates the entire table.

Subqueries

You can use build_query to embed a SimpleQuery query as an Arel subquery:

company_users = Company.simple_query
                       .select(:user_id)
                       .where(industry: "Technology")
                       .build_query

User.simple_query
    .select(:name)
    .where(User.arel_table[:id].in(company_users))
    .execute

Performance

SimpleQuery is designed for read paths where ActiveRecord model instantiation is unnecessary overhead. Returning structs or read models can reduce allocation costs for large reporting-style queries.

The repository includes performance specs and benchmark-oriented tests comparing ActiveRecord object loading with SimpleQuery result objects and streaming. Treat benchmark numbers as workload-specific: validate them against your database, indexes, adapter, and query shape before making production claims.

Safety notes

SimpleQuery is a query-building library, not an authorization or SQL-injection protection layer.

Recommended usage:

• Prefer hash, Arel, or placeholder conditions for values derived from users or external systems.
• Treat raw SQL strings in select, where, and custom_aggregation as trusted-only escape hatches.
• Keep group_concat separators static and trusted; they are interpolated into database-specific SQL.
• Use Arel nodes or Arel.sql(...) for having clauses; it does not share the same condition parser as where.
• Keep tenant, authorization, and visibility constraints explicit in your query code.
• Remember that bulk_update bypasses model callbacks and validations, like any direct SQL update.

Development

After checking out the repository:

bin/setup
bundle exec rspec
bundle exec rubocop

Database-sensitive behavior is tested against PostgreSQL and MySQL in CI.

You can also open a console:

bin/console

Contributing

Bug reports and pull requests are welcome on GitHub at kholdrex/simple_query. Contributors are expected to follow the Code of Conduct.

License

The gem is available as open source under the terms of the MIT License.

Code of Conduct

Everyone interacting in the SimpleQuery project's codebases, issue trackers, chat rooms, and mailing lists is expected to follow the Code of Conduct.