In Defense of Hand-coded SQL
Join the DZone community and get the full member experience.Join For Free
Check out this 8-step guide to see how you can increase your productivity by skipping slow application redeploys and by implementing application profiling, as you code! Brought to you in partnership with ZeroTurnaround.
The first thing to note is that I use a lot of SELECT * FROM table queries when querying tables that match the output structure. We all know that we run into tables that cannot be reasonably further normalized where the application structure can feed directly into the application. In a stored procedure wrapper, SELECT * reduces maintenance points of such tables if new fields need to be added (in which case the query still matches the specified return type with no modifications). This has costs in that it discourages refactoring of tables down the road, but this just needs to be checked. One can still have central management by using views if needed. Central management of type definitions is generally a good thing. Views can take the place of an ORM ...
The second point is that CRUD queries of this sort don't really take significant time to write, even on a well-normalized database, and having these encapsulated behind a reasonably well-designed procedural interface is not a bad thing provided that some of the classical difficulties of stored procedures are addressed.
I find that my overall development time is not slowed down by hand-writing SQL. This remains true even as the software matures. The time-savings of automatic query tools is traded for the fact that one doesn't get to spend time thinking about how to best utilize queries in the application. The fact is that, as application developers, we tend to do a lot in application code that could be done better as part of a query. Sitting down and thinking about how the queries fit into the application is one of the single most productive exercises one can do.
The reason is that a lot of data can be processed and filtered in the queries themselves. This allows one to request that the database send back data in the way the application can make best use of it. This can eliminate a lot of application-level code and lead to a shrinking codebase. This in turn allows application-level code to make better use of data returned from queries, which leads to better productivity all around.