From Ruby to Raw SQL — Ruby Deep Dive [25]

ActiveRecord, Rails’ built-in ORM (Object-Relational Mapping), elegantly abstracts database operations behind Ruby methods. While this abstraction simplifies development, understanding the underlying SQL queries can help you write more efficient and performant code. Let’s dive deep into how ActiveRecord translates Ruby code into SQL operations.

Create Operations: Inserting Records

When you create new records using ActiveRecord’s create method:

User.create(name: "Bhavyansh", email: "bhavyansh@example.com")

ActiveRecord generates an INSERT statement, automatically handling timestamps:

INSERT INTO users (name, email, created_at, updated_at)
VALUES ('Bhavyansh', 'bhavyansh@example.com', '2024-10-21 12:34:56', '2024-10-21 12:34:56');

Read Operations: Querying Data

ActiveRecord offers various methods for retrieving data, each generating specific SQL queries:

Simple Queries

• Finding by ID:

User.find(1)

# Generates:
SELECT * FROM users WHERE users.id = 1 LIMIT 1;

Complex Queries

• Filtering with conditions:

User.where(name: "Bhavyansh").order(created_at: :desc).limit(5)

# Generates:
SELECT * FROM users WHERE users.name = 'Bhavyansh' ORDER BY users.created_at DESC LIMIT 5;

Update Operations: Modifying Records

Updates can be performed on individual records or in bulk:

Single Record Updates

user.update(name: "Bhavya")
# Generates:
UPDATE users SET name = 'Bhavya', updated_at = '2024-10-21 12:34:56' WHERE users.id = 1;

Bulk Updates

User.where(active: true).update_all(last_login: Time.now)
# Generates:
UPDATE users SET last_login = '2024-10-21 12:34:56' WHERE users.active = true;

Delete Operations: Removing Records

ActiveRecord provides multiple methods for deletion:

Single Record Deletion

User.find(1).destroy
# Generates:
DELETE FROM users WHERE users.id = 1;

Bulk Deletion

User.where("created_at < ?", 1.year.ago).delete_all
# Generates:
DELETE FROM users WHERE created_at < '2023-10-21 12:34:56';

Advanced Operations

Joins

ActiveRecord can handle complex table relationships:

User.joins(:posts).where(posts: { published: true })
# Generates:
SELECT users.* FROM users
INNER JOIN posts ON posts.user_id = users.id
WHERE posts.published = true;

Aggregations

For statistical operations, ActiveRecord generates appropriate aggregate functions:

User.group(:city).having("COUNT(*) > 5")
# Generates:
SELECT city, COUNT(*) FROM users GROUP BY city HAVING COUNT(*) > 5;

Transaction Management

ActiveRecord handles database transactions seamlessly:

ActiveRecord::Base.transaction do
  User.create!(name: "Bhavyansh")
  Post.create!(title: "Hello World", user_id: 1)
end

This generates a series of SQL statements wrapped in transaction blocks:

BEGIN;
INSERT INTO users (name, created_at, updated_at) VALUES ('Bhavyansh', '2024-10-21 12:34:56', '2024-10-21 12:34:56');
INSERT INTO posts (title, user_id, created_at, updated_at) VALUES ('Hello World', 1, '2024-10-21 12:34:56', '2024-10-21 12:34:56');
COMMIT;

Performance Considerations

Understanding these SQL translations is crucial for:

1. Writing optimized queries
2. Avoiding N+1 query problems
3. Proper indexing strategies
4. Debugging complex database operations

My previous blog posts takes all these into consideration, you are thereby asked to give them a read.

By knowing how ActiveRecord translates Ruby code into SQL, developers can make informed decisions about query optimization and database design, leading to more efficient Rails applications.

Connect with me on X: https://x.com/bhavyansh001