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.

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.

Can't say I understood all of this but very relevant What ORMs have taught me: just learn SQL.

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

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!}

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

Hi nysus,

On the one hand, there are existing SQL query builders like SQL::Abstract, see e.g. my node here or Corion's node here. So I would recommend against writing your own, you'd just be re-inventing the wheel and giving yourself another thing to maintain. At the very least, you could use one of the existing builders, and write a few helper functions for the specific things they don't support and that you want (e.g. SUM).

On the other hand, I also agree with Corion's reply (Update: well, maybe except that I wouldn't use as strong a word as "hate" in regards to query builders... let's say I'm "skeptical" ;-) ). I look at it this way: what are the arguments for using a query builder? Sure, it might result in code written in Perl, but you said it yourself, "aren't I just learning one arcane syntax for another?". Another common argument is that the SQL generator allows you to target different DBs which is supposedly an advantage because it theoretically allows one to change databases later on. But in my experience, that almost never happens, and you end up sticking to one database anyway, and then you end up having to maintain the intermediate layer too. Plus, sometimes you just have to use features native to the database, and so you end up embedding SQL in your code and tying yourself to a specific database anyway. So overall, you will usually end up much happier by making an informed choice about which database to use and then just sticking with it, and writing plain SQL. Making all the SQL easy to find in your source code will make later maintenance easier.

I absolutely understand the sentiment "I don't use it a lot and every time I do I have to relearn it every time". There are other ways to alleviate the pain though. You could keep some notes on SQL in a document next to your code, and bookmark a good tutorial / reference site (e.g. I often end up at w3schools for quick lookups). I forget stuff all the time, just for example, I used XSLT extensively in a project years ago, now I couldn't tell you any of its syntax from memory - the trick is practicing and becoming efficient at looking stuff up ;-)

For example, my database object will automatically look up the column type to see if an argument needs apostrophes around it.

Be mindful of Bobby Tables! Use placeholders instead.

Hope this helps,
-- Hauke D

> Use placeholders instead.

Making placeholders automatic is actually my top argument for query builders ( which you forgot to list :)

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

Opportunity is missed by most people because it is dressed in overalls and looks like work.
-- attributed to Thomas A. Edison

I'd definitely recommend to sit down and have a look at SQL. For three reasons:

1) Abstractions have a tendency to be leaky. There's a good chance that at some point you're forced to look at the pre-built SQL anyway because something really odd is happening.

2) Proper written SQL can save your application and your database a lot of time. Putting the data selection and aggregation logic logic into the SQL can drastically reduce the amount of memory, disk I/O and network bandwidth required.

3) SQL is extremely good at hiding parallel execution. Something that might be difficult to express in Perl can be extremely simple (and also efficient) in SQL. Put differently: "If all you have is a hammer, every problem looks like a nail." (Coming from the other direction: I've seen database admins performing arithmetic calculations and string manipulation in SQL - it worked but it definitely was not pretty.)

Maybe you still decide to stick with an SQL builder because that's really all what's needed. That's alright. But SQL is pretty fantastic and well worth a look.

Yes, I totally get that. SQL is very powerful. But since it's not something I need to work with every day, I grow tired of learning it one year and then relearning it the next. I could spend a week learning it inside and out, I suppose. But my other thought is that since I'm not a professional db programmer, maybe building on someone else's work is good enough for me. And I'm particularly intrigued by the idea of being able to write a simple line of Perl code and have all the heavy lifting done for me.

Perhaps if I saw an example of how to properly use SQL queries with a Perl application I would at least have something to draw from. All I really know are snippets from SQL tutorials. I guess it feels little overwhelming to figure out best practices without seeing how it should be done.

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

"I grow tired of learning it one year and then relearning it the next."

You would feel the same about Perl if you treated it the same way.

"And I'm particularly intrigued by the idea of being able to write a simple line of Perl code and have all the heavy lifting done for me."

Generating SQL is not heavy lifting. Generating a robust schema that modules your entities and their relationships is the key here, and there is no substitution for learning this.

Go get some education! Search for "Intro to SQL: Querying and managing data" over at Kahn.

This is likely to be a personal preference thing. As Corion says, some people like to write the SQL directly; others prefer a tool. Personally I use SQL::Abstract::More most of the time. I like it more than SQL::Abstract, upon which it is based, because it allows use of named parameters (I hate positional parameters), and also supports more complex clauses such as joins, etc.

Yes, in a way you will be learning one syntax instead of another. But after becoming familiar with it I think you'll find it intuitive and effective. One thing I really like is not having to think at all about quoting values, or even about placeholders. You write your values right into the call to SQL::Abstract::More, and get back the SQL and a list of bind values that will be plugged into the placeholders by the DBI. Here's an example that's based on a recent script I was working on:

use SQL::Abstract::More;
my $sql_builder = SQL::Abstract::More->new;

my ( $sql, @bind ) = $sql_builder->select(
    -columns => "id, site_id",
    -from    => "jobs",
    -where   => {
        id => [ 10 .. 20 ],
        first_seen_time => [
            "null",
            { "<" => time - 86400, ">" => time - 86400 * 8 }, 
        ],
    },
);

print " SQL: $sql\n\n";
print "BIND: @bind\n";'

...
[download]

SQL: SELECT id, site_id FROM jobs WHERE ( ( ( first_seen_time = ? OR (
+ first_seen_time < ? AND first_seen_time > ? ) ) AND ( id = ? OR id =
+ ? OR id = ? OR id = ? OR id = ? OR id = ? OR id = ? OR id = ? OR id 
+= ? OR id = ? OR id = ? ) ) )

BIND: null 1486484465 1485879665 10 11 12 13 14 15 16 17 18 19 20
[download]

Hope this helps!

Really depends on your use case.

All you can ever do is recreate a subset of the structure that SQL already provides. You may say "My ORM can write to SQL and Mongo!" to which I would counter "but who needs it?"