Arel (A Relational Algebra) is a Ruby library used under the hood by ActiveRecord to build SQL queries. It gives developers more control over how queries are constructed, allowing them to build complex, optimized SQL that ActiveRecord alone can’t easily express.
Why Should You Use Arel?
Rails ActiveRecord is great for simple queries, but sometimes:
- You need complex joins, subqueries, or conditions.
- Performance optimization that plain ActiveRecord can’t express.
- Raw SQL flexibility with Ruby-like syntax.
- You want to avoid writing unsafe or error-prone raw SQL strings.
Arel gives you:
- SQL power + Ruby syntax
- Chainable query building
- Safer and cleaner alternatives to raw SQL
Basic Arel Setup
user_table = Arel::Table.new(:users)Now you can use user_table to build complex queries.
10 Arel Usage Examples
1. Simple WHERE condition
user_table = Arel::Table.new(:users)
query = user_table.project(Arel.star).where(user_table[:age].gt(18))
puts query.to_sql
# SELECT * FROM "users" WHERE "users"."age" > 182. AND condition
query = user_table.where(user_table[:active].eq(true).and(user_table[:age].gt(21)))3. OR condition
query = user_table.where(user_table[:name].eq('John').or(user_table[:name].eq('Jane')))4. JOIN two tables
orders = Arel::Table.new(:orders)
query = user_table.join(orders).on(user_table[:id].eq(orders[:user_id]))5. SELECT specific columns
query = user_table.project(user_table[:id], user_table[:email])6. ORDER BY clause
query = user_table.order(user_table[:created_at].desc)7. LIMIT & OFFSET
query = user_table.take(10).skip(5)8. GROUP BY
query = user_table.group(user_table[:country])9. HAVING clause
query = user_table.group(user_table[:country])
.having(user_table[:age].average.gt(30))10. Subquery usage
subquery = user_table.project(user_table[:id]).where(user_table[:admin].eq(true))
main_table = Arel::Table.new(:posts)
query = main_table.where(main_table[:user_id].in(subquery))10 Common Questions & Answers
| Question | Answer |
|---|---|
| What is Arel? | A Ruby library to build SQL queries programmatically. |
| Is Arel used in Rails by default? | Yes, ActiveRecord uses Arel under the hood. |
| Why not just use ActiveRecord? | Arel is better for complex, dynamic, or performant queries. |
| Can I mix Arel with ActiveRecord? | Yes, but you’ll have to use .where(Arel condition) manually. |
| How do I convert Arel to SQL? | Use query.to_sql. |
| Does Arel prevent SQL injection? | Yes, it escapes inputs properly. |
| What’s the downside of Arel? | Slightly more verbose and less intuitive than ActiveRecord. |
| Can Arel build INSERT or UPDATE queries? | It’s not recommended—use ActiveRecord for that. |
| Does Arel support custom functions? | Yes, via Arel::Nodes::NamedFunction. |
| Should I replace ActiveRecord with Arel? | No. Use Arel only when necessary (for complex queries). |
Alternatives to Arel
| Tool | Description |
|---|---|
| Raw SQL | Direct SQL, fast but error-prone and not DB-agnostic. |
| Sequel | A Ruby ORM that provides SQL DSL like Arel. |
| Squeel (deprecated) | Extended Arel DSL, now replaced by Arel itself. |
| ArelHelpers gem | Adds helpers to make Arel easier in Rails. |
Real-World Use Case / Case Study
Scenario: You’re building an analytics dashboard that shows:
- Users who made more than 5 orders
- Orders must be within the last 30 days
- Sorted by total amount spent
Using ActiveRecord alone:
User.joins(:orders)
.where('orders.created_at > ?', 30.days.ago)
.group('users.id')
.having('COUNT(orders.id) > 5')
.order('SUM(orders.amount) DESC')With Arel (dynamic, safe, reusable):
users = Arel::Table.new(:users)
orders = Arel::Table.new(:orders)
join = users.join(orders).on(users[:id].eq(orders[:user_id]))
where = orders[:created_at].gt(30.days.ago)
group = users[:id]
having = orders[:id].count.gt(5)
order = orders[:amount].sum.desc
query = join.project(users[Arel.star])
.where(where)
.group(group)
.having(having)
.order(order)
puts query.to_sqlThis query can now be reused, modified, and made dynamic safely—ideal for reporting and analytics features in production apps.
Best Practices
- Use Arel only when ActiveRecord falls short.
- Always escape input; Arel does it for you.
- Keep Arel logic in query objects or services (not in controllers).
- Combine with ActiveRecord relations when needed.
- Profile SQL performance after adding Arel-based queries.
Arel Terms with Descriptions
Core Classes & Modules
| Term | Description |
|---|---|
Arel::Table | Represents a database table in Arel. Used to define and interact with tables. |
Arel::Nodes | Contains SQL expression nodes like And, Or, Eq, NotEq, etc. |
Arel::Attributes::Attribute | Represents a column/field of a table. Example: users[:name] |
Arel::SelectManager | Builds SELECT queries (most common manager). |
Arel::InsertManager | Builds INSERT queries. |
Arel::UpdateManager | Builds UPDATE queries. |
Arel::DeleteManager | Builds DELETE queries. |
Basic SQL Operations
| Term | Description |
|---|---|
project | SELECT clause – defines which columns to select. |
where | Adds a WHERE clause to a query. |
join | Adds a JOIN clause (inner join). |
outer_join | Adds a LEFT OUTER JOIN. |
on | Sets the ON condition for a join. |
group | Adds a GROUP BY clause. |
having | Adds a HAVING clause for filtered aggregates. |
order | Adds an ORDER BY clause. |
take(n) | Adds a LIMIT clause. |
skip(n) | Adds an OFFSET clause. |
distinct | Adds SELECT DISTINCT. |
Comparison & Logical Operators
| Term | Description |
|---|---|
eq(value) | = equals condition. |
not_eq(value) | != not equals condition. |
gt(value) | > greater than. |
gteq(value) | >= greater than or equal to. |
lt(value) | < less than. |
lteq(value) | <= less than or equal to. |
matches(value) | LIKE query (e.g., LIKE '%keyword%'). |
does_not_match(value) | NOT LIKE. |
in(values) | IN clause (WHERE x IN (1, 2, 3)). |
not_in(values) | NOT IN clause. |
and(other_condition) | Combines with AND logic. |
or(other_condition) | Combines with OR logic. |
SQL Functions & Nodes
| Term | Description |
|---|---|
Arel::Nodes::NamedFunction | Call SQL functions (e.g., COUNT, SUM). |
Arel.star | Represents * (select all). |
Arel.sql('RAW_SQL') | Injects raw SQL string (use cautiously). |
Arel::Nodes::As | Assigns aliases (e.g., SELECT name AS user_name). |
count | Shortcut for COUNT(column) via NamedFunction. |
sum | Shortcut for SUM(column). |
avg | Average aggregate function. |
max, min | Max and Min aggregates. |
asc / desc | Sort direction in ORDER BY. |
Joins and Aliases
| Term | Description |
|---|---|
join(table) | Inner join another table. |
outer_join(table) | Left outer join. |
alias(table, name) | Aliases a table or subquery. |
on(condition) | Join condition. |
Arel Managers
| Manager | Use |
|---|---|
Arel::SelectManager | Building SELECT statements (most common). |
Arel::InsertManager | Building INSERT statements. |
Arel::UpdateManager | Building UPDATE queries. |
Arel::DeleteManager | Building DELETE queries. |
Arel Terms with Examples and Descriptions (Cheat Sheet)
| Term | Description | Example | SQL Output |
|---|---|---|---|
Arel::Table.new(:users) | Defines a reference to the users table. | users = Arel::Table.new(:users) | – |
project | SELECT columns. | users.project(users[:id], users[:name]) | SELECT "users"."id", "users"."name" |
where | WHERE clause (conditions). | users.where(users[:age].gt(18)) | WHERE "users"."age" > 18 |
eq(value) | Equals (=) condition. | users[:name].eq('John') | "users"."name" = 'John' |
not_eq(value) | Not equals (!=) condition. | users[:active].not_eq(true) | "users"."active" != true |
gt(value) | Greater than. | users[:age].gt(21) | "users"."age" > 21 |
gteq(value) | Greater than or equal. | users[:age].gteq(21) | "users"."age" >= 21 |
lt(value) | Less than. | users[:age].lt(50) | "users"."age" < 50 |
lteq(value) | Less than or equal. | users[:age].lteq(50) | "users"."age" <= 50 |
matches('%keyword%') | LIKE clause. | users[:email].matches('%@gmail.com') | "users"."email" LIKE '%@gmail.com' |
does_not_match('%admin%') | NOT LIKE. | users[:role].does_not_match('%admin%') | "users"."role" NOT LIKE '%admin%' |
in([1, 2, 3]) | IN clause. | users[:id].in([1, 2, 3]) | "users"."id" IN (1, 2, 3) |
not_in([1, 2]) | NOT IN clause. | users[:id].not_in([1, 2]) | "users"."id" NOT IN (1, 2) |
and(...) | AND condition. | users[:age].gt(18).and(users[:active].eq(true)) | "age" > 18 AND "active" = true |
or(...) | OR condition. | users[:role].eq('admin').or(users[:role].eq('editor')) | "role" = 'admin' OR "role" = 'editor' |
join(...) | INNER JOIN. | users.join(orders).on(users[:id].eq(orders[:user_id])) | INNER JOIN orders ON users.id = orders.user_id |
outer_join(...) | LEFT OUTER JOIN. | users.outer_join(orders).on(users[:id].eq(orders[:user_id])) | LEFT OUTER JOIN orders ON users.id = orders.user_id |
on(...) | JOIN condition. | join.on(users[:id].eq(orders[:user_id])) | ON users.id = orders.user_id |
order(...) | ORDER BY clause. | users.order(users[:created_at].desc) | ORDER BY users.created_at DESC |
asc / desc | Sorting direction. | users[:name].asc, users[:age].desc | ORDER BY name ASC / age DESC |
take(10) | LIMIT. | users.take(10) | LIMIT 10 |
skip(5) | OFFSET. | users.skip(5) | OFFSET 5 |
group(...) | GROUP BY clause. | users.group(users[:country]) | GROUP BY country |
having(...) | HAVING condition for aggregates. | users.having(users[:age].average.gt(30)) | HAVING AVG(age) > 30 |
Arel.star | * select all columns. | users.project(Arel.star) | SELECT * |
Arel.sql('RAW_SQL') | Insert raw SQL (use cautiously). | Arel.sql('NOW()') | NOW() |
NamedFunction | SQL functions like COUNT, SUM. | Arel::Nodes::NamedFunction.new('COUNT', [users[:id]]) | COUNT(users.id) |
alias(...) | Alias for column or table. | users[:id].as('user_id') | users.id AS user_id |
SelectManager | SELECT builder. | users.project(...).to_sql | Builds SQL SELECT |
InsertManager | INSERT builder. | For inserting data using Arel. | – |
UpdateManager | UPDATE builder. | For update queries. | – |
DeleteManager | DELETE builder. | For delete queries. | – |
Basic Usage of Arel::InsertManager
users = Arel::Table.new(:users)
manager = Arel::InsertManager.new
manager.into(users)
manager.insert([
[users[:name], 'John Doe'],
[users[:email], 'john@example.com'],
[users[:created_at], Time.now]
])
sql = manager.to_sql
puts sqlLearn more about Rails setup
