If you are using Rails, chances are you must be using ActiveRecord extensively. Let’s explore how Active record internally creates queries.

How SQL query is created in Active Record?

In Active Record, the sql statement generation is handled by Arel, All the commands written in active record is passed to Arel, Arel constructs the query and when required will return the query and Active Record executes the query to the Database.

So, what is Arel?

Standard Defn : Arel is a Relational Algebra, a SQL AST manager for Ruby, which constructs SQL queries for ActiveRecord. Arel is highly composable and enables query chaining and optimization, making it safer and easier to write complex SQL queries.

To understand this definition, let’s explore what are Relational Algebra,AST, composability

What is a Relational Algebra?

A term coined in 1970 by Edgar Codd in his paper Relational Model of Data Large Shared Data Banks , which laid the foundation of relational database creation. In this influential paper, Codd introduced the concept of a “relational model” for organizing and accessing data. Here’s a summary of the key points in Codd’s white paper

• He introduced the concept of the “relational model,” which represents data in the form of tables (relations).
• In the relational model, data is organized into relations, where each relation corresponds to a table. Each relation consists of rows (tuples) and columns (attributes).
• He defined several operations for manipulating data in a relational database, including selection, projection, union, intersection, and difference.
• Codd introduced a formal query language for manipulating data called “Alpha.”
• Codd’s relational model laid the groundwork for the development of structured query languages (SQL) and the creation of relational database management systems (RDBMS).

What is composability?

Composability is a system design principle that deals with the inter-relationships of components. A highly composable system provides components that can be selected and assembled in various combinations to satisfy specific user requirements. Arel have attributes like (Where, Join, Select) which can be assembled to create more complex queries.

AST (Abstract Syntax Tree)

It is tree data structure, where the nodes represent SQL elements like JOIN, Where, projection etc. When you traverse this tree you get a SQL statement. Arel use AST for SQL statement creation.

Let’s create a AST tree.

Run following command in Rails console

users = Arel::Table.new(:user)
email = users.project(users[:email]) 
File.write("arel2.dot", email.to_dot)

Here, we are creating AST tree for SQL statement where we are selecting email column in User table and exporting the AST as png file

This will create a arel2.dot file, now run following command in terminal to convert the dot file to png. Note: Here we are not running the command in Rails console.

dot arel2.dot -T png -o arel2.png

The output.

Observe this diagram, it holds all the answer of this question “How Active Record Creates SQL statement Internally?”

Let’s take one more complex example, which has join as well

In Rails Console

cars = Arel::Table.new(:car)
predicate = user[:car_id].eq( cars[:id] )
join_selector = users.join(cars).on( predicate ).group( users[:car_id] ).project( cars[:id] ) 
File.write("join.dot", join_selector.to_dot)

In Terminal

dot join.dot -T png -o join.png

Let’s understand in detail.

Arel consists of following components,

• AST
• Manager
• Visitors
• Collectors
• Attribute

(AST)Abstract Syntax Tree

We have already seen this part in detail with visualiser.

The AST in Arel is the hierarchical data structure that represents a SQL query. It consists of nodes that correspond to various parts of a SQL query, such as SELECT, FROM, WHERE, and JOIN clauses. These nodes are organized in a tree structure, with a root node at the top.

Manager

Managers are the root element of the AST, The Manager is a central component that acts as an entry point for creating and managing Arel queries. It provides methods for creating tables, specifying columns, and building query expressions. Managers help in setting up the context for query construction.

Arel have following managers:

• Select
• Update
• Delete
• Tree

Expect Tree others are self explanatory, In SQL a statement generally start with Select, Update or Delete which act as a root node.

Visitors

Visitors are responsible for traversing the AST tree and generating SQL queries from the tree’s nodes. Arel uses visitors to visit each node in the AST and collect the corresponding SQL fragments. Visitors are instrumental in turning the abstract representation in the AST into executable SQL statements.

Arel use Visitor Design Pattern to visit nodes, so multiple visitor can be supported

Visitor supported by Arel.

• dot
• mysql
• postgresql
• sqlite
• to_sql

Reference: https://github.com/rails/rails/tree/main/activerecord/lib/arel/visitors

We used dot visitor for our example, where arel visited all Nodes of AST using dot visitor and converting the tree to Dot file. Arel use to_sql visitor for SQL statement creation.

Collectors

Collectors are components that gather SQL fragments generated by visitors like dot, to_sql and combine them to form complete SQL queries. These fragments might include expressions, clauses, and subqueries. Collectors ensure that the various parts of the query are properly combined to produce valid SQL statements.

You can view them as appenders.

Attribute

Attributes in Arel represent specific columns in database tables. They are a way to define column names, data types, and other attributes within the Arel framework. Attributes are essential for building queries, as they allow you to specify the columns you want to select, filter, or join in your SQL statements.

Reference:https://github.com/rails/rails/tree/main/activerecord/lib/arel