One of the things that I found very useful in working with DB is Singleton pattern. It allows me to set the connection (or for several of them) data once and then simply ask for new instance where ever I need to work with DB - instead of "passing around the code" the initial DBH.

# Somewhere in init stage
use DBIx::Handy; # need a better name ...
DBIx::Handy->add_db(dsn  =>  'dbi:mysql:autoreorder',
                    username => 'alex',
                    name => 'oscommerce');

# Then latter
my $DB = DBIx::Handy->get_instance();
my $SQL = "SELECT p.products_id, p.products_image, pd.products_name,
pd.products_description FROM products p INNER JOIN
products_description pd ON p.products_id = pd.products_id
WHERE p.products_id = ?";
    
my $products_data = $DB->execute(sql        => $SQL,
                                 data       => [$form->{id}],
                                 database   => 'oscommerce')->fetchrow
+_hashref();
    
    
my $template = $self->load_tmpl('Product/details.dwt');
$template->param(%{$products_data});
    
return $template->output();
[download]

I didn't felt the need to add much SQL generation functions as I usually have either very simple or very complex queries (that I couldn't generate with abstraction modules). But it does generate SQL for simple INSERT and UPDATE statements.

It simply compares the table columns to keys in hash provided (data to be written) and only include those fields that are found in both.

So for instance you get hash (hashref) from CGI.pm, pass it througth Data::FormValidator and forward it to DBIx::Handy for update/insert.

Another thing that I added recently is possibility to return AoH reference from a query. I very often need those to print search results. And I just pass the reference to say HTML::Template.

my $schools = $DB->execute(sql      =>'SELECT * FROM users ORDER BY us
+ername',
                               method   =>'fetchall_aohref');
    
my $t = $self->load_tmpl('Admin/view_admin_users.dwt');
$t->param('schools' => $schools);
    
return $t->output();
[download]

Currently everything is MySQL specific (might work on other DB's) but I'm looking for a way to support other RDBMS as well. Either through sub classing and overriding needed methods, or in a similar way DBI does it with all DBD::XYZ modules ...