DBIx::Class get the dbh

Question

I'm using DBIx::Class in a Catalyst app I am building. It works great, but sometimes I need to use my own db functions that I've developed that are very specific to my needs. Because of this, I need a dbh. However, since I'm already using DBIx::Class I know that it already has a dbh that it is using. To avoid making another unnecessary connection to the database, I would like to just use the dbh that DBIx::Class has already created. I know that the DBIx::Class::Storage::DBI module has two methods dbh and dbh_do, but I'm not really sure what the difference is between the two and if they are the best way to get access to the dbh. Can anyone tell me what the best way to get the dbh from DBIx::Class would be in a Catalyst app? I'd prefer a method that I could forward to that would store the dbh in the stash like below:

sub dbh :Private { 
    my ($self, $c) = @_;
    $c->stash->{dbh} = #get dbh from DBIx::Class here
}

Thanks!

Pied

Answer 1

I always have to look this up. Assuming you have an instance of your schema object, you can get its Storage object via the storage method. Assuming that's a Storage::DBI, then there is a dbh method available which will get you your database handle. So:

my $dbh = $c->model( 'My::DB' )->storage->dbh;

should do the trick.

Vicious

And is there any reason to use dbh_do instead? Because the documentation for dbh says: Consider using "dbh_do" instead.. – Pied

I haven't used it myself, but the docs for dbh_do say that is uses a new exception system. (I guess by wrapping $dbh->do and executing the supplied function.) So I guess that's why. I've never had any problems just using the raw $dbh, though. – Vicious

@Pied yes, anything you run under dbh_do will be subject to the DBIx::Class connection management (reconnecting if the DB connection is lost). There's also txn_do for very nice transaction management. – Vange

@hobbs, so dbh is not subject to the DBIx::Class connection management? i.e., it won't reconnect if the connection is lost? – Pied

@Pied ->dbh will reconnect if the connection is down at the moment you call ->dbh, but it can't do anything about a connection that's lost later on while you're using the handle it gave you. – Vange

@hobbs, okay. that makes sense now. thanks for explaining the difference. – Pied

Answer 2

@srchulo Answers are great and dbh_do is way to go for it's inbuilt exception handling but I will advice to convert your function to rather not use dbh anymore and just work with DBIX::Class. That way, next time, you just have to change at one place and not keep looking for legacy dbh and raw sqls. Hope it makes sense.

Gothard