in reply to What Do Monks Think of SQL Query Builders?

My approach is that I hate all query builders, as I always end up fighting them to get the SQL I want.

I prefer to write and test my SQL separately from Perl and having an ORM or query builder between me and the database is one more ugly layer to debug.

My use case might not be your use case though, because I usually have several (left) joins and like to use SQL window functions or subselects to select interesting rows from the database. All query builders I've seen don't handle subselects or window functions well, and they all make it impossible to get at the SQL before running the program.

My advice would be to get more familiar with SQL as it is quite a versatile tool, but if you don't use SQL that often, I understand that a Perl wrapper is more alluring.

  • Comment on Re: What Do Monks Think of SQL Query Builders?

Replies are listed 'Best First'.
Re^2: What Do Monks Think of SQL Query Builders?
by Mr. Muskrat (Canon) on Feb 09, 2017 at 19:43 UTC

    I used to despise ORMs and query builders. I prefer writing raw SQL and really wish that all developers would take the time to learn SQL to the point where they can write decent queries for insert, update and delete (with joins if needed).

    Now that I have used DBIC at two different companies, I can appreciate how, if deployed properly, ORMs or query builders can save time and frustration as well as allowing developers to focus on the task at hand even if they haven't bothered to learn the basics of SQL.

[reply]
Re^2: What Do Monks Think of SQL Query Builders?
by nysus (Parson) on Feb 09, 2017 at 18:20 UTC
[reply]
      Thanks for the link, but it should be noted that ORM and query builder are 2 different things.

      ORM wrap DB tables with classes, rows are objects and columns attributes.

      This can be handy, but you are limiting your universe, because it's mathematically impossible to cover all possibilities of SQL that way. (Aka "the Object/Relational Impedance Mismatch")

      A query builder on the other hand creates SQL, and this not necessarily for an ORM.

      Query builders are limited by their grammar, this depends on their implementation.

      But there is no theoretical or practical reason why they shouldn't cover the whole spectrum of a relational language.

      Cheers Rolf
      (addicted to the Perl Programming Language and ☆☆☆☆ :)
      Je suis Charlie!

[reply]
Re^2: What Do Monks Think of SQL Query Builders?
by nysus (Parson) on Feb 08, 2017 at 15:53 UTC

    Had to look ORM up. I guess that's what I'm kind of doing, then. I try to build all the fetchall_array and similar calls in a lower level, hidden away from my objects so I don't have to think about them again. Now my curiousity is piqued.

    $PM = "Perl Monk's";
    $MCF = "Most Clueless Friar Abbot Bishop Pontiff Deacon Curate";
    $nysus = $PM . ' ' . $MCF;
    Click here if you love Perl Monks

[reply]
[d/l]

In Section Seekers of Perl Wisdom