loading...

re: ORM vs. SQL? VIEW POST

FULL DISCUSSION
 

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.

 

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

Could you please provide an example or two? :-)

 

Sure – I think that scopes are a good example.

The ability to define something as simple as:

scope :forthcoming_or_active, -> {
  where(publishing_status: [Book::PUB_STATUS_FORTHCOMING, Book::PUB_STATUS_ACTIVE])
}

scope :publishing_within_months, ->(months) {
  forthcoming_or_active.
    pub_date_between(
      Time.zone.today,
      Time.zone.today + months.to_i.months
    )
}
scope :not_ebook, -> {
  where.not(product_form: Book::PRODUCT_FORM_EBOOK)
}
scope :sales_present, -> {
  where(Sale.belonging_to_book.exists)
}

So these define class methods that I can daisy chain in the application to be able to identify the records I want:

  • Book.not_ebook
  • Book.sales_present.not_ebook
  • Book.not_ebook.sales_present.publishing_within_months(1)

These all generate the correct SQL, and if I change the definition of not_ebook in the scope then every SQL is automatically changed to the new definition. There are 18 places in our system where #not_ebook is used, and not only would I not want to correct them all by hand, I would not fancy my chances of being sure that I can find them all.

So that's pretty hard to do with SQL, efficiently at least. You could conceive of it being done with a PL./SQL function in Oracle perhaps, but when you stray from executing pure SQL there's the risk of compromising performance.

code of conduct - report abuse