It’s really handy to have a composable API for building SQL queries where different elements are contributed by different parts of the code. From example, having your authorisation code apply restrictions to queries through where clauses, joins, etc. to ensure a user only sees the records they are allowed to see.

I currently spend a large proportion of my time working in a Java code base that uses JDBC directly. There are many places where the complexity of the work to be done means code is being used to assemble the final SQL query based on conditionals and then the same conditional structure must be used to bind parameter values. Yes, in some places there are entire SQL statements as String literals, but that only really works for simple scenarios. There are also many bits of code that wrap up common query patterns, reimplementing some of what an ORM might bring.

I recently implemented soft deletion for one of the main entities in the system, and having to review every query for the table involved to see whether it needed the deleted_at field adding to the where clause took a significant amount of time. I think better architecture supported by a more structured query builder would have made this much easier. For me that’s the main benefit of an ORM.