DEV Community


Discussion on: ORM vs. SQL?

databasesponge profile image
MetaDave 🇪🇺

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) {
    pub_date_between(, + months.to_i.months
scope :not_ebook, -> {
  where.not(product_form: Book::PRODUCT_FORM_EBOOK)
scope :sales_present, -> {

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.