I my experience, as soon as you grow beyond using the database as a way of just storing simple data, you will have joins in most of your queries, and so very little use for Class::DBI and related socalled simple tools.
Class::DBI is great if you see it as an SQL based object persistence framework. If you try to use it as a replacement for SQL itself, it suddenly becomes a whole lot less useful. It's not a querying language - in fact, that is what it tries very hard to not be :)
About so-called "simple" tools, I'm sure you will find that a simple interface can very well be used in complex designs, having looked at DBIx::Simple. It doesn't abstract, it simplifies. Please don't see one as the synonym of the other.