Appquery
raw SQL 🥦 , cooked 🍲
Install / Use
/learn @eval/AppqueryREADME
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
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.
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.
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 a
