Mastering Ruby: Sequel and ActiveRecord, an overview

Sequel and ActiveRecord are object-relational mapping for ruby-based applications, they have similarities and disparities, and this article will guide you to choose which one best suits your needs.

If you are not familiar with the term ORM, here is a short explanation: ORMs provide a set of tools and conventions for mapping database tables and rows to corresponding objects and their attributes in the application code. This simplifies database interactions, eliminates the need to write raw SQL queries (but you can if you want), and allows developers to work with data using familiar programming paradigms.

What is ActiveRecord?

ActiveRecord is an Object-Relational Mapping (ORM) library that is an integral part of the Ruby on Rails framework. It provides an abstraction layer that allows you to interact with a database using Ruby objects and methods rather than writing raw SQL queries. ActiveRecord simplifies database interactions, data validation, and the handling of associations between different types of data.

How can I use it?

Here's how you can use ActiveRecord in a Ruby-based application, particularly within the context of Ruby on Rails:

Setup and Configuration:
In a Rails application, ActiveRecord is included by default. There's no need to install it separately.

Configure your database connection in the config/database.yml file. Specify the database adapter, host, database name, user, password, and other connection details.

Creating Models:
Create Ruby classes that inherit from ActiveRecord::Base. Each model class typically corresponds to a database table.
Define attributes as instance variables in the model class to represent table columns.
Establish associations (e.g., has_many, belongs_to, has_and_belongs_to_many) between model classes to define relationships in the database.

Migrations:
Use migrations to manage your database schema. Migrations allow you to create, modify, and delete database tables and columns.
Migrations provide a version control-like system for your database structure, enabling you to keep track of changes over time.

CRUD Operations:
Create: Use the create method on your model class to insert new records into the database.
Read: Use methods like find, where, and all to retrieve records from the database.
Update: Use the update method or combination of find and save to modify existing records.
Delete: Use the destroy method to remove records from the database.

Validation:
Define validation rules using ActiveRecord's built-in validation methods. These ensure that the data entering the database meets specific criteria.
Common validations include presence, length, uniqueness, and more.

Callbacks:
Use callbacks to execute code at specific points in the lifecycle of a model instance. Examples include before_save, after_create, and before_destroy.

Associations:
Leverage ActiveRecord's association methods to establish relationships between different model classes. This allows you to navigate and manipulate related data.

Query Interface:
Use ActiveRecord's query interface to construct and execute complex database queries using a fluent and Ruby-like syntax.
Methods like where, joins, group, and order can be combined to build intricate queries.

Console and Rails Console:
Use the Rails console (rails console) to interact with your models and database in a live environment.
Test your queries, create records, and perform various operations directly from the console.

Error Handling:
ActiveRecord provides exceptions and error messages when validation or database-related errors occur, helping you diagnose and troubleshoot issues.

With AR you can also write custom SQL queries.

You can check the documentation: https://guides.rubyonrails.org/active_record_basics.html.

What is Sequel?

Sequel is a popular Object-Relational Mapping (ORM) library for Ruby that provides a simple and flexible way to interact with databases. It offers an alternative to ActiveRecord, the default ORM in Ruby on Rails. Sequel is known for its lightweight design, extensive feature set, and focus on providing a more SQL-centric approach.

How can I use it?

Here's how you can use Sequel in your Ruby-based application:
Installation: Add the Sequel gem to your Gemfile and run bundle install.

Database Configuration: Configure your database connection in your application. Sequel supports a wide range of database systems, including PostgreSQL, MySQL, SQLite, and more. You'll need to specify the appropriate adapter, host, database name, user, password, and any other relevant connection options.

Model Definitions: Define your models by creating Ruby classes that inherit from Sequel::Model. Each model class should represent a table in your database. Define associations, validations, and other model-specific configurations using Sequel's API.

Queries: Use Sequel's query methods to interact with the database. Sequel provides a wide range of methods to build complex queries, including filtering, sorting, joining tables, and more.

Creating Records: Instantiate your model classes to create new records in the database. Sequel provides an intuitive syntax to insert data.

Retrieving Records: Use the model class's methods to retrieve records from the database. You can chain query methods to build complex queries.

Updating and Deleting Records: Use Sequel's methods to update and delete records from the database.

Associations: Define and work with associations between models using Sequel's association methods. These include one-to-one, one-to-many, and many-to-many associations.

Migrations: Migrations are optional, you don’t have to use them. However, you can use Sequel's migration system to manage changes to your database schema over time.

Hooks and callbacks: Implement hooks and callbacks to perform actions before or after certain events, such as saving records or validating data.

Error Handling: Sequel provides error handling mechanisms to catch and manage exceptions that might occur during database operations.

Custom SQL: While Sequel offers a wide range of high-level query methods, you can also write custom SQL queries when needed.

You can check the syntax and more information about the Sequel ORM: https://github.com/jeremyevans/sequel.

ActiveRecord vs. Sequel

Choosing between Sequel and ActiveRecord depends on your project's requirements and preferences.

Here are some scenarios where using Sequel might be more suitable than ActiveRecord:
Choosing between Sequel and ActiveRecord depends on your project's requirements and preferences. Here are some scenarios where using Sequel might be more suitable than ActiveRecord:

Performance and Control: Sequel is known for its lightweight design and can offer better performance in certain scenarios. If you need more control over how queries are executed and want to optimize performance, Sequel's flexibility can be an advantage.

Postgres Features: If you are using Postgres, Sequel employs the PG extended query protocol, making it significantly faster than ActiveRecord and reducing server-side resource usage. You can learn more about it (here)[https://www.postgresql.org/docs/current/protocol-flow.html#PROTOCOL-FLOW-EXT-QUERY]. Unlike Sequel, ActiveRecord doesn't fully leverage the potential of PostgreSQL.

SQL-Centric Approach: Sequel has a powerful SQL DSL. If you're comfortable working with a more SQL-oriented approach to database interactions, Sequel's API aligns closely with SQL syntax. This can be advantageous when you need fine-grained control over your queries.
Calling functions:

# Using Sequel

# defining the function on the database
DB.run(<<~SQL)
  CREATE FUNCTION add_numbers(a integer, b integer)
  RETURNS integer AS $$
  BEGIN
      RETURN a + b;
  END; $$
  LANGUAGE plpgsql;
SQL

Calling the function somewhere in the app.

Sequel.function(:add_numbers, 1, 2)
DB.select(f).first
# => {:add_numbers=>3}

# Using ActiveRecord

# defining the function on the database
execute <<~SQL
  CREATE OR REPLACE FUNCTION add_numbers(a integer, b integer)
  RETURNS integer AS $$
  BEGIN
    RETURN a + b;
  END;
  $$ LANGUAGE plpgsql;
SQL

Calling the function somewhere in the app.

# Call the SQL function using a raw SQL query
result = ActiveRecord::Base.connection.exec_query("SELECT add_numbers(1, 2) AS add_numbers")

# Access the result
add_numbers_result = result.first["add_numbers"]

This shows Sequel has better Ruby DSLs for calling functions, rather than having to go down and write raw SQL to call functions.

Advanced Features: Sequel also provides advanced features like subqueries, window functions, and CTEs (Common Table Expressions).
Subqueries example:

# Using Sequel

# Find users with the highest salary in each department
subquery = DB[:salaries].select_group(:department_id).select_append{max(salary).as(max_salary)}
result = DB[:salaries].join(subquery, [:department_id, :max_salary])

# Using ActiveRecord

# Find users with the highest salary in each department
subquery = Salary.select("department_id, MAX(salary) AS max_salary").group(:department_id)
result = Salary.joins("INNER JOIN (#{subquery.to_sql}) max_salaries ON salaries.department_id = max_salaries.department_id AND salaries.salary = max_salaries.max_salary")

Window functions example:

# Sequel

# Calculate the average salary for each employee
result = DB[:salaries].select_group(:employee_id).select_append{avg(salary).over(partition: :employee_id).as(average_salary)}

# ActiveRecord

# Calculate the average salary for each employee
result = Salary.select("employee_id, AVG(salary) OVER (PARTITION BY employee_id) AS average_salary")

CTEs example:

# Sequel

# Find employees with salaries above the department's average salary using a CTE
cte = DB[:salaries].select_group(:department_id).select_append{avg(salary).as(average_salary)}
result = DB[:salaries].with(:cte, cte).where{salary > cte[:average_salary]}

# ActiveRecord

# Find employees with salaries above the department's average salary using a subquery
subquery = Salary.select("department_id, AVG(salary) AS average_salary").group(:department_id)
result = Salary.joins("INNER JOIN (#{subquery.to_sql}) avg_salaries ON salaries.department_id = avg_salaries.department_id").where("salaries.salary > avg_salaries.average_salary")

Here we can see how challenging is to achieve the same thing using ActiveRecord's query interface.

Using Hanami or ROM: Hanami and ROM use Sequel under the hood, which is a powerful source and an alternative for Ruby on Rails.

Methods at build time: Sequel provides a metaprogramming feature that allows you to define methods at the time you define your model class. These methods are shared among all instances of the class. It can lead to more efficient memory usage, as the methods are shared among instances. It can also make your code more organized, as methods are defined alongside the class.

Complex Queries: When dealing with complex database queries, joins, subqueries, and advanced filtering, Sequel's expressive query-building methods can make it easier to construct intricate queries without sacrificing readability.

Custom Data Types: If your application works with custom or non-standard data types that require more control over serialization and deserialization, Sequel's custom-type support can be beneficial.

Legacy Databases: If your application interacts with a legacy database schema that doesn't adhere to Rails conventions, Sequel's ability to work with non-standard column and table names can be advantageous.

Less Magic: Sequel emphasizes explicitness and minimizes "magic" behaviors. If you prefer to have a clearer understanding of what happens behind the scenes in your database interactions, Sequel might be a better fit.

Lean Applications: For applications where you want to keep the overhead of the ORM to a minimum, Sequel's modular design and lack of excessive abstractions can be beneficial.

Small Projects: Sequel's straightforward and focused API can be more suitable for smaller projects where you don't need all the features and complexity of ActiveRecord.

Here are some scenarios where using ActiveRecord might be more suitable than Sequel:
You're building a Ruby on Rails application: ActiveRecord is tightly integrated into the Ruby on Rails framework. If you're building a Rails application, using ActiveRecord provides a cohesive and familiar development experience.

Convention Over Configuration: ActiveRecord follows Rails' convention over configuration principle. It comes with sensible defaults and a standardized approach to database interactions, which can speed up development.

Fast Development: ActiveRecord's ease of use, automagic associations, and built-in query methods can help you quickly build applications with less boilerplate code.

Community and Ecosystem: ActiveRecord is widely used, which means you'll find ample documentation, tutorials, and community support. If you're new to Ruby on Rails, the abundant resources can be a significant advantage.

ORM Integration: If your project involves features closely integrated with Rails, such as form helpers, view scaffolding, and other framework-specific components, ActiveRecord's seamless integration is beneficial.

It's important to note that ActiveRecord is the default ORM for Ruby on Rails, which provides a cohesive development experience for most Rails projects. ActiveRecord abstracts away much of the complexity of database interactions and integrates seamlessly with Rails conventions.

Disadvantages of both:
Using views: By default, database views are treated as read-only when using ActiveRecord. This means that you can query data from views just like you would from tables, but you cannot perform direct write operations (insert, update, or delete) on views through ActiveRecord. Sequel, like ActiveRecord, treats views as read-only by default. Sequel models associated with views are typically used for querying data, not for performing direct write operations.

Summary

Deciding between Sequel and ActiveRecord depends on your specific needs, preferences, and the nature of your project. Ultimately, the decision between Sequel and ActiveRecord should be based on your familiarity with SQL, your project's requirements, your team's preferences, and the specific trade-offs you're willing to make. If you value control, performance, flexibility, and a more SQL-centric approach, Sequel could be a strong choice. If you prioritize convention, ease of use, and seamless integration with Rails, ActiveRecord might be a better fit.

Comments

[Hartmut B.]

Hi,
nice summary of (dis-)advantages of the most popular RDMS-ORM's for ruby.
As you – in my view correctly – in favor of Sequel, It would be worth to compare Sequel with ruby adapters to NOSQL-Databases like mongodb or (this is my favorite) ArcadeDB.