The Hand-Crafted SQL Anti Pattern

You probably should have used an ORM if...

• your app has a lot of SQL statements that exceed 200 lines
• you use multiple CTE (WITH Clauses) to deal with the complexity of your looong SQL statements
• you frequently implement helper functions that return SQL clauses
• you frequently refactor your helper functions that return SQL clauses
• you find it takes more time to add functionality to your SQL queries than it took to design the database DDL, indexes and write that super long SQL in the first place

Those are a few indications of deeper issues.

Before you discount the "exceed 200 lines" or "multiple CTE" items, please consider that the reason for having sql helper clauses is typically to reduce the complexity of the sql statement; If the sql statement needs simplification then odds are it's too darn long; Hence, the "exceeds 200 lines" and "multiple CTE" remarks.

Another rule of thumb of mine... If any logic (any language) exceeds 150 lines long, it's probably too long and should probably be refactored.

Technical Debt

With a lot of hard coded SQL, you'll likely encounter problems down the road like...

SQL Errors you might get with Pagination

In this case, we were using a WITH clause and wanted to enforce a new sorting order...

ERROR:  SELECT DISTINCT ON expressions must match initial ORDER BY expressions
LINE 52: select distinct on (s.id)
                             ^
********** Error **********

ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions
SQL state: 42P10
Character: 1213

We learn by ERROR that when we try to add the flexibility of changing the sort order, we have new, unforeseen problems to solve which are a direct result of our legacy, hard-coded SQL.

Reasons to use a lot of hard-coded SQL, instead of an ORM...

• Your business objects are not easy to map to database tables
• Your application needs lot of complex, hand-tuned SQL
• You use a lot of stored procedures and complex logic in your database

If this is your case, you should probably revisit your database design and Data Manipulation Language (DML) implementation strategies and actively reduce your technical debt before you are asked to maintain all that hard-coded SQL.

Intelligent enterprise application design begins with information architecture and a solid database design.

If you frequently feel that it takes a lot longer to add functionality to your application than it should, you should closely examine your application architecture.

Disclaimer

Q: Am I saying that you should always use an ORM to solve your all of your DML needs?

A: No.

However, I am saying that if the vast majority of your DML code is hard-coded SQL, you should probably find a good ORM that fits in your technology stack and learn how to use it.

References

None. That's just my hard-knock experience speaking.

This work is licensed under the Creative Commons Attribution 3.0 Unported License.