DEV Community

Discussion on: ORM vs. SQL?

Collapse
 
databasesponge profile image
MetaDave 🇪🇺 • Edited

Context: Rails monolith, with about 450 tables in PostgreSQL, on Heroku.

99.99999999etc% of database queries are generated by ActiveRecord.

Occasionally we use Arel for something like where pub_date >= ..., though accessed through a class method that would allow the code to express that as @client.books.pub_date_on_or_after Date.today.

We have a few that can't be expressed in vanilla ActiveRecord, and for which Arel would be more trouble than it's worth, and we try to do that by having the SQL statement in a dedicated file, in a dedicated folder.

Sometimes we will express complex queries in a view and place a read-only model over it. Mostly this is to get around limitations in efficient eager loading.

We have one 427 line SQL statement – an insert with 6 common table expressions, which is used to achieve in a bout one second what it would otherwise take Ruby about 30 minutes to do.

Further context: I was an Oracle data warehouse architect for about 20 years, and built systems with SQL embedded in in PL/SQL, so as a team we do not fear SQL. However, as an application developer I would rather use Rails + ActiveRecord – there are too many techniques I can use with Rails that would be impossible or just tedious to achieve in an application that relies on hand-coded SQL, mostly to do with DRY.