- Notifications
You must be signed in to change notification settings - Fork1
A lightweight, multi-DB-friendly, and high-performance query builder for ActiveRecord, featuring streaming, bulk updates, and read-model support.
License
kholdrex/simple_query
Folders and files
| Name | Name | Last commit message | Last commit date | |
|---|---|---|---|---|
Repository files navigation
SimpleQuery is a lightweight and efficient query builder for ActiveRecord, designed to provide a flexible and performant way to construct complex database queries in Ruby on Rails applications.
Add this line to your application's Gemfile:
gem'simple_query'
And then execute:
bundle install
Or install it yourself as:
gem install simple_query
By default,SimpleQuery doesnot automatically patchActiveRecord::Base. You canmanually include the module in individual models or in a global initializer:
# Manual include (per model)classUser <ActiveRecord::BaseincludeSimpleQueryend# or do it globallyActiveRecord::Base.include(SimpleQuery)
If you prefer a “just works” approach (i.e., every model has.simple_query), you can opt in:
# config/initializers/simple_query.rbSimpleQuery.configuredo |config|config.auto_include_ar=trueend
This tells SimpleQuery to automatically doActiveRecord::Base.include(SimpleQuery) for you.
SimpleQuery offers an intuitive interface for building queries with joins, conditions, and aggregations. Here are some examples:
Basic query
User.simple_query.select(:name,:email).where(active:true).execute
Query with join
SimpleQuery now supportsall major SQL join types — including LEFT, RIGHT, and FULL — through the following DSL methods:
User.simple_query.left_join(:users,:companies,foreign_key::user_id,primary_key::id).select("users.name","companies.name").execute
Complex query with multiple joins and conditions
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")).where(User.arel_table[:admin].eq(true)).execute
Lazy execution
User.simple_query.select(:name).where(active:true).lazy_execute
Placeholder-Based Conditions
SimpleQuery now supportsActiveRecord-style placeholders, letting you pass arrays with? or:named placeholders to your.where clauses:
# Positional placeholders:User.simple_query.where(["name LIKE ?","%Alice%"]).execute# Named placeholders:User.simple_query.where(["email = :email",{email:"alice@example.com"}]).execute# Multiple placeholders in one condition:User.simple_query.where(["age >= :min_age AND age <= :max_age",{min_age:18,max_age:35}]).execute
By default, SimpleQuery returns results asStruct objects for maximum speed. However, you can also define a lightweight model class for more explicit attribute handling or custom logic.
Create a read model inheriting fromSimpleQuery::ReadModel:
classMyUserReadModel <SimpleQuery::ReadModelattribute:identifier,column::idattribute:full_name,column::nameend
Map query results to your read model:
results=User.simple_query.select("users.id AS id","users.name AS name").where(active:true).map_to(MyUserReadModel).executeresults.eachdo |user|putsuser.identifier# => user.id from the DBputsuser.full_name# => user.name from the DBend
This custom read model approach provides more clarity or domain-specific logic while still being faster than typical ActiveRecord instantiation.
SimpleQuery now supports named scopes, allowing you to reuse common query logic in a style similar to ActiveRecord’s built-in scopes. To define a scope, use the simple_scope class method in your model:
classUser <ActiveRecord::BaseincludeSimpleQuerysimple_scope:activedowhere(active:true)endsimple_scope:adminsdowhere(admin:true)end# Block-based scope with parametersimple_scope:by_namedo |name|where(name:name)end# Lambda-based scope with parametersimple_scope:by_name,->(name){where(name:name)}end
You can then chain these scopes seamlessly with the normal SimpleQuery DSL:
# Parameterless scopesresults=User.simple_query.active.admins.execute# Parameterized scoperesults=User.simple_query.by_name("Jane Doe").execute# Mixing scopes with other DSL callsresults=User.simple_query.by_name("John").active.select(:id,:name).order(name::asc).execute
Each scope block (e.g. by_name) is evaluated in the context of the SimpleQuery builder, so you can call any DSL method (where, order, etc.) inside it.Parameterized scopes accept arguments — passed directly to the block (e.g. |name| above).Scopes return self, so you can chain multiple scopes or mix them with standard query methods.
For massive queries (millions of rows),SimpleQuery offers a.stream_each method to avoid loading the entire result set into memory. Itautomatically picks a streaming approach depending on your database adapter:
- PostgreSQL: Uses aserver-side cursor via
DECLARE ... FETCH. - MySQL: Uses
mysql2gem’sstreaming (stream: true, cache_rows: false, as: :hash).
# Example usage:User.simple_query.where(active:true).stream_each(batch_size:10_000)do |row|# row is a struct or read-model instanceputsrow.nameend
SimpleQuery aims to outperform standard ActiveRecord queries at scale. We’ve benchmarked1,000,000 records onboth PostgreSQL andMySQL, with the following results:
🚀 Performance Results (1000,000 records):ActiveRecord Query: 10.36932 secondsSimpleQuery Execution (Struct): 3.46136 secondsSimpleQuery Execution (Read model): 2.20905 seconds----------------------------------------------------ActiveRecord find_each: 6.10077 secondsSimpleQuery stream_each: 2.75639 seconds--- AR find_each Memory Report ---Total allocated: 1.98 GB (16,001,659 objects)Retained: ~2 KB--- SimpleQuery stream_each Memory Report ---Total allocated: 1.38 GB (8,000,211 objects)Retained: ~3 KB- Struct-based approach remains the fastest, skipping model overhead.
- Read model approach is still significantly faster than standard ActiveRecord while allowing domain-specific logic.
🚀 Performance Results (1000,000 records):ActiveRecord Query: 10.45833 secondsSimpleQuery Execution (Struct): 3.04655 secondsSimpleQuery Execution (Read model): 3.69052 seconds----------------------------------------------------ActiveRecord find_each: 5.04671 secondsSimpleQuery stream_each: 2.96602 seconds--- AR find_each Memory Report ---Total allocated: 1.32 GB (11,001,445 objects)Retained: ~2.7 KB--- SimpleQuery stream_each Memory Report ---Total allocated: 1.22 GB (8,000,068 objects)Retained: ~3.9 KB- Even in MySQL,Struct was roughlythree times faster than ActiveRecord’s overhead.
- Read models still outperform AR, though by a narrower margin in this scenario.
After checking out the repo, runbin/setup to install dependencies. Then, runrake spec to run the tests. You can also runbin/console for an interactive prompt that will allow you to experiment.
Bug reports and pull requests are welcome on GitHub athttps://github.com/kholdrex/simple_query. This project is intended to be a safe, welcoming space for collaboration, and contributors are expected to adhere to thecode of conduct.
The gem is available as open source under the terms of theMIT License.
Everyone interacting in the SimpleQuery project's codebases, issue trackers, chat rooms and mailing lists is expected to follow thecode of conduct.
About
A lightweight, multi-DB-friendly, and high-performance query builder for ActiveRecord, featuring streaming, bulk updates, and read-model support.