AppQuery - Raw SQL, ergonomically

Ergonomic raw SQL queries for ActiveRecord

Gem Version CI Status API Docs Downloads License

Installation • Quick Start • Usage • API Docs • Compatibility

A Ruby gem for working with raw SQL in Rails. Store queries in app/queries/, execute with proper type casting, filter/transform using CTEs, and parameterize via ERB.

# Load and execute
week = AppQuery[:weekly_sales].with_binds(week: 1, year: 2025)
week.entries
#=> [{"week" => 2025-01-13, "category" => "Electronics", "revenue" => 12500, "target_met" => true}, ...]

# Filter results (query wraps in CTE, :_ references it)
week.count
#=> 5
week.count("SELECT * FROM :_ WHERE NOT target_met")
#=> 3

# Extract a column efficiently (only fetches that column)
week.column(:category)
#=> ["Electronics", "Clothing", "Home & Garden"]

# Named binds with defaults
AppQuery[:weekly_sales].select_all(binds: {min_revenue: 5000})

# ERB templating
AppQuery("SELECT * FROM contracts <%= order_by(ordering) %>")
  .render(ordering: {year: :desc}).select_all

# Custom type casting
AppQuery("SELECT metadata FROM products").select_all(cast: {metadata: :json})

# Inspect/mock CTEs for testing
query.prepend_cte("sales AS (SELECT * FROM mock_data)")

Highlights

Feature Description
Query Files Store SQL in app/queries/ with Rails generator
Execution select_all / select_one / select_value / count / column / ids
CTE Manipulation Query transformation via prepend_cte / append_cte / replace_cte
Immutable Derive new queries from existing ones
Named Binds Safe parameterization with automatic defaults
ERB Helpers order_by, paginate, values, bind
Type Casting Automatic + custom type casting
RSpec Integration Built-in matchers and helpers for testing
Export Stream results via copy_to (PostgreSQL)

[!IMPORTANT] Status: Using in production for multiple projects, but API might change pre v1.0. See the CHANGELOG for breaking changes when upgrading.

Rationale

Sometimes ActiveRecord doesn't cut it: you need performance, prefer raw SQL over Arel, and hash-maps suffice instead of full ActiveRecord instances.

That introduces new problems: the not-so-intuitive select_all/select_one/select_value methods differ in type casting behavior across ActiveRecord versions. Then there's testability, introspection, and maintainability of SQL queries.

AppQuery provides:

  • Consistent interface across select_* methods and ActiveRecord versions
  • Easy inspection and testing—especially for CTE-based queries
  • Clean parameterization via named binds and ERB

Read this blog post for additional context and an overview.

Installation

bundle add appquery

Quick Start

Generate a query:

rails g query weekly_sales

Write your SQL in app/queries/weekly_sales.sql:

SELECT week, category, revenue
FROM sales
WHERE week = :week AND year = :year
ORDER BY revenue DESC

Execute it:

AppQuery[:weekly_sales].select_all(binds: {week: 1, year: 2025})
#=> [{"week" => 1, "category" => "Electronics", "revenue" => 12500}, ...]

Even better:

Use the query-class and define binds, vars, casts, middleware etc.

class WeeklySalesQuery < ApplicationQuery
  include AppQuery::Paginatable
  per_page 25

  bind :week
  bind :year, default: 2026

  cast metadata: :json

  # add factory methods for specific purposes
  def self.build(page: 1, week:, year: 2026)
    new(week:, year:).paginate(page:)
  end
end

WeeklySalesQuery.build(week: 1).entries

Read more about the query-class in the API docs.

Usage

[!NOTE] The following examples show how this gem handles raw SQL. The included example Rails app contains runnable queries.

Console Exploration

# Testdrive from console
[postgresql]> AppQuery(%{select date('now') as today}).select_all.entries
=> [{"today" => Fri, 02 Jan 2026}]

[postgresql]> AppQuery(%{select date('now') as today}).select_one
=> {"today" => Fri, 02 Jan 2026}

[postgresql]> AppQuery(%{select date('now') as today}).select_value
=> Fri, 02 Jan 2026
Database setup (the bin/console script does this for you) ```ruby ActiveRecord::Base.logger = Logger.new(STDOUT) ActiveRecord::Base.establish_connection(url: 'postgres://localhost:5432/some_db') ```

Type Casting

Values are automatically cast (unlike raw ActiveRecord):

# AppQuery
AppQuery(%{select date('now') as today}).select_one
=> {"today" => Fri, 02 Jan 2026}

# Compare with raw ActiveRecord
ActiveRecord::Base.connection.select_one(%{select date('now') as today})
=> {"today" => "2025-12-20"}  # String, not Date!

# Custom casting
AppQuery("SELECT metadata FROM products").select_all(cast: {metadata: :json})

Named Binds

# Named binds
AppQuery(%{select now() - (:interval)::interval as date})
  .select_value(binds: {interval: '2 days'})

# Binds default to nil - add SQL defaults via COALESCE
AppQuery(<<~SQL).select_all(binds: {ts1: 2.days.ago, ts2: Time.now})
  SELECT generate_series(
    :ts1::timestamp,
    :ts2::timestamp,
    COALESCE(:interval, '5 minutes')::interval
  ) AS series
SQL

CTE Manipulation

Rewrite queries using CTEs:

articles = [
  [1, "Using my new static site generator", 2.months.ago.to_date],
  [2, "Let's learn SQL", 1.month.ago.to_date],
]

q = AppQuery(<<~SQL, cast: {published_on: :date}).render(articles:)
  WITH articles(id, title, published_on) AS (<%= values(articles) %>)
  SELECT * FROM articles ORDER BY id DESC
SQL

# Query the CTE directly
q.select_all("SELECT * FROM articles WHERE id < 2")

# Query the result (via :_ placeholder)
q.select_one("SELECT * FROM :_ LIMIT 1")
q.first  # shorthand

# Rewrite CTEs
q.replace_cte("settings(cutoff) AS (VALUES(DATE '2024-01-01'))")
q.prepend_cte("mock_data AS (SELECT 1)")
q.append_cte("extra AS (SELECT 2)")

ERB Templating

# Dynamic ORDER BY
q = AppQuery("SELECT * FROM articles <%= order_by(ordering) %>")
q.render(ordering: {published_on: :desc, title: :asc}).select_all

# Pagination
AppQuery("SELECT * FROM users <%= paginate(page: page, per_page: per_page) %>")
  .render(page: 2, per_page: 25).select_all

# Optional clauses using instance variables
AppQuery(<<~SQL).render(order: nil)  # @order is nil, clause is skipped
  SELECT * FROM articles
  <%= @order.presence && order_by(order) %>
SQL

Data Export (PostgreSQL)

# Return as string
csv = AppQuery[:users].copy_to
#=> "id,name\n1,Alice\n2,Bob\n..."

# Write to file
AppQuery[:users].copy_to(to: "export.csv")

# Stream to IO
File.open("users.csv.gz", "wb") do |f|
  gz = Zlib::GzipWriter.new(f)
  AppQuery[:users].copy_to(to: gz)
  gz.close
end

See the method docs for more (Rails) examples.

RSpec Integration

Generated spec files include helpers:

# spec/queries/reports/weekly_query_spec.rb
RSpec.describe Reports::WeeklyQuery, type: :query, binds: {since: 3.weeks.ago} do
  describe "CTE articles" do
    specify do
      expect(described_query.entries).to \
        include(a_hash_including("article_id" => 1))
    end
  end
end

See the API docs for more RSpec examples.

Writing a Middleware

A BaseQuery middleware is a Module you include into a query class. There are three layers to extend at, depending on where you want to act. The three compose cleanly on the same query class.

You want to… Layer How
change/decorate each row row-level append to q.row_builder
filter, wrap, cap, paginate, cache the collection result-level override #entries (or #first, #last, …)
change the SQL/binds before it runs query-level override #query, return a different Q

Row-level (the row_builder pipeline)

Q#row_builder is a composable pipeline of callables that each receive a row Hash and return whatever should replace it — a Hash, a Data, a Struct, your own model.

module Stamping
  extend ActiveSupport::Concern

  def query
    @query ||= super.tap { |q| q.row_builder << ->(row) { row.merge("stamped_at" => Time.now) } }
  end
end

class ArticlesQuery < ApplicationQuery
  include Stamping
end

ArticlesQuery.new.first         # => {"id" => 1, ..., "stamped_at" => 2026-...}
ArticlesQuery.new.entries.first # same — every row-returning path flows through row_builder

The pipeline propagates through with_select(non_nil), add_binds, with_binds, with_cast, with_sql, and CTE focusing (#cte), so chained calls keep the same mapping. Each child gets an independent copy — mutating it doesn't affect the parent.

Stacking row-level middlewares. Transformers run in include order — earliest include first, latest include last. With

class MyQuery < ApplicationQuery
  include Stamping            # runs first — its row goes into…
  include AppQuery::Mappable  # …which sees the stamped hash and builds an Item
end

Stamping's lambda runs first, then Mappable.build_row consumes the already-stamped hash. ⚠️ Once a transformer returns a non-Hash (e.g. a Data), downstream transformers see that object — so a hash-merging transformer placed after Mappable would fail. Order the chain accordingly.

Doesn't fit row_builder: filtering ("drop rows the viewer can't see") and collapsing rows. Both act on the collection, not a single row — use the result-level layer instead.

Result-level (wrap a row-returning method)

When you want to act on the whole collection — wrap, cap, cache, filter, paginate — override the row-returning method and call super. super returns rows that have already been through row_builder, so this composes with row-level middleware without thinking.

Paginatable is the canonical example (wraps #entries in a PaginatedResult). Some others:

module Caching
  # memoise the whole result on the instance
  def entries = @_entries ||= super
  def first   = @_first   ||= super
end

module ScopedToTenant
  def entries = super.select { |r| r["tenant_id"] == Current.tenant.id }
end

module Capped
  CapResult = Data.define(:records, :hit_cap?) do
    include Enumerable
    def each(&b) = records.each(&b)
  end

  def entries
    rows = super
    CapResult.new(records: rows.first(self.class.cap), hit_cap?: rows.size > self.class.cap)
  end
end

Query-level (rewrite SQL/binds before execution)

When you want to change what the database actually sees — tenant scoping, soft-delete filtering, default ordering — override #query and return a transformed Q. Use with_select / with_binds / add_binds etc.; they propagate the row_builder pipeline via deep_dup, so row-level middleware keeps working.

module TenantScoped
  def query
    @query ||= super.add_binds(tenant_id: Current.tenant.id)
  end
end

module HidesDeleted
  def query
    @query ||= super.with_select("SELECT * FROM :_ WHERE deleted_at IS NULL")
  end
end

Putting it together

All three layers can sit on one class:

class ArticlesQuery < ApplicationQuery
  include HidesDeleted            # query-level: rewrites SQL
  include Stamping                # row-level:   adds "stamped_at"
  include AppQuery::Mappable      # row-level:   builds Item (must come after row-mutating middleware)
  include AppQuery::Paginatable   # result-level: wraps entries
end

Pipeline at run time, top to bottom: SQL is rewritten to filter deleted_at IS NULL → DB returns rows → each row gets stamped_at → each row becomes an Item → the array of Items is wrapped in a PaginatedResult.

API Documentation

See the YARD documentation for the full API reference.

Compatibility

Component Supported
Databases PostgreSQL, SQLite
Rails 7.x, 8.x
Ruby 3.3+ (maintained versions)

Development

# Setup
bin/setup  # Make sure it exits with code 0

# Console (connects to database)
bin/console sqlite3::memory:
bin/console postgres://localhost:5432/some_db

# With specific Rails version
bin/run rails_head console

# Run tests
rake spec

# YARD with reload (requires entr and overmind/foreman)
bin/yard-dev

Using mise for env-vars is recommended.
Using bonchi allows for agentic working via git worktrees. See .worktree.yml.example for a config.

Releasing

Create a signed git tag and push:

# Regular release
git tag -s 1.2.3 -m "Release 1.2.3"

# Prerelease
git tag -s 1.2.3.rc1 -m "Release 1.2.3.rc1"

git push origin --tags

# then change version.rb for the next dev-cycle
VERSION = "1.2.4.dev"

CI will build, sign (Sigstore attestation), push to RubyGems, and create a GitHub release.

Contributing

Bug reports and pull requests are welcome on GitHub at https://github.com/eval/appquery.

License

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