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