Thursday, February 23, 2012

How much milage do you get with SQL::Abstract?


I have been hearing a lot about SQL::Abstract. So I started trying to
put some queries together with it. It is quite interesting but leaves
me unsure about whether or not it's right for me.

The module takes data structures and returns the query statement and
the bind values that need to be passed to a DBI handle. I like this
because it allows me to just worry about the data I need to make the
query without having to worry about the sql syntax.

On the other hand is writing sql hard enough to justify this kind of
abstraction? One thing that I see happening is that if I am writing
more complex queries, I would probably just go to my database client
and start typing out queries to get what I want. Plus, at least in the
beginning I would probably be translating from raw sql to SQL::Abstract
in my head.

I guess it's kind of nifty that SQL::Abstract keeps me from having to type
so much in order to interact with the database. Does anyone use
SQL::Abstract and (love|hate) it? I'd love to hear other people's
experience with this module

7 comments:

  1. I only use it via DBIx::Class which has a few other things such as reconnect on lost connection and cheap random pooling for replication.

    ReplyDelete
  2. I use SQLA (through DBIC) to do the boring bits of SQL that I would have to do for every single table, leaving me with the interesting bits that require thought or special DB-specific queries. I don't like copy/pasting 12 different tables' worth of "SELECT"/get(), "INSERT"/new(), "UPDATE"/set(), "DELETE"/delete(), and I won't write my own abstraction when one already exists.

    ReplyDelete
  3. You can use it to store complex query logic that you don't want to have to figure out again.

    https://gist.github.com/1901932

    Because of the 'sellable' method, I don't have to figure out what defines a sellable ticket every time. If someone comes along and decides to add more criteria to what is a sellable ticket, you only have to update the 'sellable' method instead of updating queries.

    ReplyDelete
  4. All my applications rely heavily on DBIx::DataModel, which is another ORM that also depends on SQLA, like DBIx::Class.

    The great thing about SQLA is not about avoiding to type SQL; it is about building queries dynamically, depending on some input that may come from config files, user forms, or whatever. Think of a generalized search form within your app : the user fills some search criteria, checks some boxes to decide which columns to retrieve, and the whole datastructure received from the HTML form can be given (almost) as is to SQLA in order to generate a database query

    ReplyDelete
  5. For me it is a godsend for conditionally building sql. For example you've got something like:

    # assuming Data::FormValidator
    if ( $dfv->{date} ) {
    $sql .= ' AND date = ?';
    push @sqlparams, $c->param->{date}
    }

    A lot of times I can just give my dfv results right to sqla, sometimes giving a benefit of hundreds of lines of code. For me fewer lines of code == fewer bugs.

    ReplyDelete
  6. In Dancer::Plugin::SQLSearch, search queries are broken into words which are assembled into a data structure suitable for SQL::Abstract. SQLA allows the plugin to be general, as it does not have to know much about the database to build the data structure.
    More over, producing SQL::Abstract makes the plugin ORM agnostic. I normally feed this data structure to DBIx::DataModel, but you can do the same with DBIx::Class or straight into SQL::Abstract and issue the query yourself.

    ReplyDelete
  7. It's not very useful. The functionality is so limited: no joins! Instead, I use Rose::DB::Object::QueryBuilder, which does not require anything else from Rose::DB::Object to work. It generates nicely formatted SQL from complicated sets of conditions, and handles join, group by, etc.

    ReplyDelete