Whether you tie your app to one specific database, or use Class::DBI, you should still have your own abstraction. It should be application specific, yet hide the details of how the data is coming into existence. That way, even if you are hard tied to one database, all the hard ties are in one spot. Most of your application will simply talk to your data layer, and your data layer will talk to DBI, or Class::DBI, or whatever.

Update: to answer your question more directly, I usually tend to go with hard coded SQL directly against DBI. I rarely change databases, and if so, my standard abstraction layer approach saves me from most of the trouble.