Use an ORM for CRUD, and use a SQL library (like Jooq or QueryDSL for Java) for custom querying
For example, Laravel's Eloquent database layer is awesome for the 95% use case. When stuff gets complicated I can drop down to the query builder or raw SQL if things get real hairy.