A Rubygem 💎 that makes working with raw SQL queries in Rails projects more convenient.
Specifically it provides:
- ...a dedicated folder for queries
e.g.app/queries/reports/weekly.sqlis instantiated viaAppQuery["reports/weekly"]. - ...Rails/rspec generators
$ rails generate query reports/weekly create app/queries/reports/weekly.sql invoke rspec create spec/queries/reports/weekly_query_spec.rb - ...helpers to rewrite a query for introspection during development and testing
See what a CTE yields:query.select_all(select: "SELECT * FROM some_cte").
Query the end result:query.select_one(select: "SELECT COUNT(*) FROM _ WHERE ...").
Append/prepend CTEs:query.prepend_cte(<<~CTE) articles(id, title) AS ( VALUES(1, 'Some title'), (2, 'Another article')) CTE
- ...rspec-helpers
RSpec.describe "AppQuery reports/weekly", type: :query do describe "CTE some_cte" do # see what this CTE yields expect(described_query.select_all(select: "select * from some_cte")).to \ include(a_hash_including("id" => 1)) # shorter: the query and CTE are derived from the describe-descriptions so this suffices: expect(select_all).to include ...
Important
Status: alpha. API might change. See the CHANGELOG for breaking changes when upgrading.
Install the gem and add to the application's Gemfile by executing:
bundle add appqueryNote
The following (trivial) examples are not meant to convince you to ditch your ORM, but just to show how this gem handles raw SQL queries.
Note
The included example Rails app contains all data and queries described below.
Create a query:
rails g query recent_articlesHave some SQL (for SQLite, in this example):
-- app/queries/recent_articles.sql
WITH settings(default_min_published_on) as (
values(datetime('now', '-6 months'))
),
recent_articles(article_id, article_title, article_published_on, article_url) AS (
SELECT id, title, published_on, url
FROM articles
RIGHT JOIN settings
WHERE published_on > COALESCE(?1, settings.default_min_published_on)
),
tags_by_article(article_id, tags) AS (
SELECT articles_tags.article_id,
json_group_array(tags.name) AS tags
FROM articles_tags
JOIN tags ON articles_tags.tag_id = tags.id
GROUP BY articles_tags.article_id
)
SELECT recent_articles.*,
group_concat(json_each.value, ',' ORDER BY value ASC) tags_str
FROM recent_articles
JOIN tags_by_article USING(article_id),
json_each(tags)
WHERE EXISTS (
SELECT 1
FROM json_each(tags)
WHERE json_each.value LIKE ?2 OR ?2 IS NULL
)
GROUP BY recent_articles.article_id
ORDER BY recent_articles.article_published_onThe result would look like this:
[{"article_id"=>292,
"article_title"=>"Rails Versions 7.0.8.2, and 7.1.3.3 have been released!",
"article_published_on"=>"2024-05-17",
"article_url"=>"https://rubyonrails.org/2024/5/17/Rails-Versions-7-0-8-2-and-7-1-3-3-have-been-released",
"tags_str"=>"release:7x,release:revision"},
...
]Even for this fairly trivial query, there's already quite some things 'encoded' that we might want to verify or capture in tests:
- only certain columns
- only published articles
- only articles with tags
- only articles published after some date
- either provided or using the default
- articles are sorted in a certain order
- tags appear in a certain order and are formatted a certain way
Using the SQL-rewriting capabilities shown below, this library allows you to express these assertions in tests or verify them during development.
Note
There's AppQuery#select_all, AppQuery#select_one and AppQuery#select_value to execute a query. select_(all|one) are tiny wrappers around the equivalent methods from ActiveRecord::Base.connection.
Instead of positional arguments, these methods accept keywords select, binds and cast. See below for examples.
Given the query above, you can get the result like so:
AppQuery[:recent_articles].select_all.entries
# =>
[{"article_id"=>292,
"article_title"=>"Rails Versions 7.0.8.2, and 7.1.3.3 have been released!",
"article_published_on"=>"2024-05-17",
"article_url"=>"https://rubyonrails.org/2024/5/17/Rails-Versions-7-0-8-2-and-7-1-3-3-have-been-released",
"tags_str"=>"release:7x,release:revision"},
...
]
# we can provide a different cut off date via binds^1:
AppQuery[:recent_articles].select_all(binds: [1.month.ago]).entries
1) note that SQLite can deal with unbound parameters, i.e. when no binds are provided it assumes null for $1 and $2 (which our query can deal with).
For Postgres you would always need to provide 2 values, e.g. `binds: [nil, nil]`.We can also dig deeper by query-ing the result, i.e. the CTE _:
AppQuery[:recent_articles].select_one(select: "select count(*) as cnt from _")
# => {"cnt" => 13}
# For these kind of aggregate queries, we're only interested in the value:
AppQuery[:recent_articles].select_value(select: "select count(*) from _")
# => 13Use AppQuery#with_select to get a new AppQuery-instance with the rewritten SQL:
puts AppQuery[:recent_articles].with_select("select * from _")You can select from a CTE similarly:
AppQuery[:recent_articles].select_all(select: "SELECT * FROM tags_by_article")
# => [{"article_id"=>1, "tags"=>"[\"release:pre\",\"release:patch\",\"release:1x\"]"},
...]
# NOTE how the tags are json strings. Casting allows us to turn these into proper arrays^1:
types = {"tags" => ActiveRecord::Type::Json.new}
AppQuery[:recent_articles].select_all(select: "SELECT * FROM tags_by_article", cast: types)
1) PostgreSQL, unlike SQLite, has json and array types. Just casting suffices:
AppQuery("select json_build_object('a', 1, 'b', true)").select_one(cast: true)
# => {"json_build_object"=>{"a"=>1, "b"=>true}}Using the methods (prepend|append|replace)_cte, we can rewrite the query beyond just the select:
AppQuery[:recent_articles].replace_cte(<<~SQL).select_all.entries
settings(default_min_published_on) as (
values(datetime('now', '-12 months'))
)
SQLYou could even mock existing tables (using PostgreSQL):
# using Ruby data:
sample_articles = [{id: 1, title: "Some title", published_on: 3.months.ago},
{id: 2, title: "Another title", published_on: 1.months.ago}]
# show the provided cutoff date works
AppQuery[:recent_articles].prepend_cte(<<-CTE).select_all(binds: [6.weeks.ago, nil, JSON[sample_articles]).entries
articles AS (
SELECT * from json_to_recordset($3) AS x(id int, title text, published_on timestamp)
)
CTEUse AppQuery#with_select to get a new AppQuery-instance with the rewritten sql:
puts AppQuery[:recent_articles].with_select("select * from some_cte")When generating a query reports/weekly, a spec-file like below is generated:
# spec/queries/reports/weekly_query_spec.rb
require "rails_helper"
RSpec.describe "AppQuery reports/weekly", type: :query, default_binds: [] do
describe "CTE articles" do
specify do
expect(described_query.select_all(select: "select * from :cte")).to \
include(a_hash_including("article_id" => 1))
# short version: query, cte and select are all implied from descriptions
expect(select_all).to include(a_hash_including("article_id" => 1))
end
end
endThere's some sugar:
described_query
...just likedescribed_classin regular class specs.
It's an instance ofAppQuerybased on the last word of the top-description (i.e. "reports/weekly" from "AppQuery reports/weekly").:cteplaceholder
When doingselect_all, you can rewrite theSELECTof the query by passingselect. There's no need to use the full name of the CTE as the spec-description contains the name (i.e. "articles" in "CTE articles").- default_binds
Thebinds-value used when not explicitly provided.
E.g. given a query with a where-clauseWHERE published_at > COALESCE($1::timestamp, NOW() - '3 month'::interval), when settingdefaults_binds: [nil]thenselect_allworks likeselect_all(binds: [nil]).
AppQuery.configure {|Configuration| ... } ⇒ void
Configure AppQuery.
AppQuery.configure do |cfg|
cfg.query_path = "db/queries" # default: "app/queries"
endAppQuery.configuration ⇒ AppQuery::Configuration
Get configuration
AppQuery.configure do |cfg|
cfg.query_path = "db/queries" # default: "app/queries"
end
AppQuery.configurationInstantiate via AppQuery(sql) or AppQuery[:query_file].
AppQuery::Q#cte_names ⇒ [Array< String >]
Returns names of CTEs in query.
AppQuery("select * from articles").cte_names # => []
AppQuery("with foo as(select 1) select * from foo").cte_names # => ["foo"]AppQuery::Q#recursive? ⇒ Boolean
Returns whether or not the WITH-clause is recursive or not.
AppQuery("select * from articles").recursive? # => false
AppQuery("with recursive foo as(select 1) select * from foo") # => trueAppQuery::Q#select ⇒ String
Returns select-part of the query. When using CTEs, this will be <select> in a query like with foo as (select 1) <select>.
AppQuery("select * from articles") # => "select * from articles"
AppQuery("with foo as(select 1) select * from foo") # => "select * from foo"AppQuery::Q#select_all(select: nil, binds: [], cast: false) ⇒ AppQuery::Result
select replaces the existing select. The existing select is wrapped in a CTE named _.
binds array with values for any (positional) placeholder in the query.
cast boolean or Hash indicating whether or not (and how) to cast. E.g. {"some_column" => ActiveRecord::Type::Date.new}.
# SQLite
aq = AppQuery(<<~SQL)
with data(id, title) as (
values('1', 'Some title'),
('2', 'Another title')
)
select * from data
where id=?1 or ?1 is null
SQL
# selecting from the select
aq.select_all(select: "select * from _ where id > 1").entries #=> [{...}]
# selecting from a CTE
aq.select_all(select: "select id from data").entries
# casting
aq.select_all(select: "select id from data", cast: {"id" => ActiveRecord::Type::Integer.new})
# binds
aq.select_all(binds: ['2'])AppQuery::Q#select_one(select: nil, binds: [], cast: false) ⇒ AppQuery::Result
First result from AppQuery::Q#select_all.
See examples from AppQuery::Q#select_all.
AppQuery::Q#select_value(select: nil, binds: [], cast: false) ⇒ AppQuery::Result
First value from AppQuery::Q#select_one. Typically for selects like select count(*) ..., select min(article_published_on) ....
See examples from AppQuery::Q#select_all.
AppQuery::Q#with_select(sql) ⇒ AppQuery::Q
Returns new instance with provided select. The existing select is available via CTE _.
puts AppQuery("select 1").with_select("select 2")
WITH _ as (
select 1
)
select 2AppQuery::Q#prepend_cte(sql) ⇒ AppQuery::Q
Returns new instance with provided CTE.
query.prepend_cte("foo as (values(1, 'Some article'))").cte_names # => ["foo", "existing_cte"]AppQuery::Q#append_cte(sql) ⇒ AppQuery::Q
Returns new instance with provided CTE.
query.append_cte("foo as (values(1, 'Some article'))").cte_names # => ["existing_cte", "foo"]AppQuery::Q#replace_cte(sql) ⇒ AppQuery::Q
Returns new instance with replaced CTE. Raises ArgumentError when CTE does not already exist.
query.replace_cte("recent_articles as (select values(1, 'Some article'))")- 💾 tested with SQLite and PostgreSQL
- 🚆 tested with Rails v6.1, v7 and v8.0
- 💎 requires Ruby >v3.1
Goal is to support maintained Ruby versions.
After checking out the repo, run bin/setup to install dependencies. Make sure to check it exits with status code 0.
Using direnv for env-vars recommended.
Then, run rake spec to run the tests. You can also run bin/console for an interactive prompt that will allow you to experiment.
To install this gem onto your local machine, run bundle exec rake install. To release a new version, update the version number in version.rb, and then run bundle exec rake release, which will create a git tag for the version, push git commits and the created tag, and push the .gem file to rubygems.org.
Bug reports and pull requests are welcome on GitHub at https://github.com/eval/appquery.
The gem is available as open source under the terms of the MIT License.