Beyond ORMs

For years, the author has been fascinated by the design of object-relational mappings (ORMs), exploring various options and even creating one of their own. However, over the past few years, the author has shifted towards working more directly with databases, utilizing plain SQL, processing raw data, and building custom low-level abstractions. This change in approach led to the creation of Extralite, a Ruby gem for working with SQLite databases.

Direct interaction with the database, the author contends, is superior to using an ORM. ORMs fall short in supporting advanced features such as window functions, common table expressions (CTEs), and the RETURNING clause. Additionally, ORMs introduce performance costs in terms of memory and CPU time, and their fundamental design of mapping rows to objects is considered an anti-pattern.

The author acknowledges the popularity and influence of ActiveRecord, the most widely used ORM in the Ruby/Rails community. However, the author believes ActiveRecord's abstraction of the single record is inadequate, particularly when dealing with normalized databases containing numerous tables with ancillary data that should not be treated as separate entities.

The author argues that ORM APIs, which are centered around the concept of a separate object for each row, can lead novice programmers to believe they are working with discrete objects rather than entries in a record set. This can result in inefficient patterns, such as issuing multiple queries to delete or update records. The author demonstrates how using a single query with the RETURNING clause in databases like PostgreSQL and SQLite can resolve this issue, returning both the deleted records and their content.

The author further emphasizes that the number of distinct queries required by most web applications is finite and relatively small, often limited to a dozen or fewer queries per table. Therefore, the author questions the necessity of ORMs and their accompanying DSLs, which offer hundreds of instance and class methods. In most cases, the amount of SQL written in an application is negligible compared to the Ruby code.

The author concludes that the advantages provided by ORMs, such as a convenient DSL and built-in features like associations, can be outweighed by the complexity and limitations they introduce when working with more advanced or esoteric database queries.