5 ms·
There is a huge cost to handwriting SQL too that one mist be careful of - it is very easy to result in poor abstractions as a result of inconsistent apis from l
by Bahamut 9y ago
There is a huge cost to handwriting SQL too that one mist be careful of - it is very easy to result in poor abstractions as a result of inconsistent apis from lack of abstraction over SQL.
My company is currently in the process of moving back to an ORM after 5 years or so abandoning ORM usage due to nightmarish performance using NHibernate in .Net (maybe more of an indictment on the code than anything else, but I don't know the specifics of the history). We found the company had a lot of problems that resulted from handwritten SQL, including lack of ability to adequately abstract the logic for proper unit/integrated testing, and inconsistent data fields being returned by various queries, resulting in inconsistent api endpoint data returned to the client and thus inability to safely abstract without significant hacks/nebulous data model state.
The worst thing we encountered with handrolled SQL over ORM was that it actually hindered our ability to deliver major business requirements. We had no ability to gate content by various values from other tables without modifying the handrolled SQL in every single location...not a good situation to be in at all.
- fnord123 9y ago> inconsistent data fields being returned by various queries, resulting in inconsistent api endpoint data returned to the client and thus inability to safely abstract without significant hacks/nebulous data model state. You are coding against actual queries? Why not functions to hide the table abstractions: https://www.postgresql.org/docs/current/static/sql-createfunction.html https://www.postgresql.org/docs/current/static/sql-createfun... https://dev.mysql.com/doc/refman/5.7/en/create-procedure.html https://dev.mysql.com/doc/refman/5.7/en/create-procedure.htm... >We had no ability to gate content by various values from other tables without modifying the handrolled SQL in every single location Again, don't sql functions solve this?
- icebraining 9y agoThat's essentially building an ORM (well, a data mapping layer) out of PL/SQL functions rather than your main language. Why would that be better?
- TimJYoung 9y agoI think he's saying that there was a lot of duplication in the client SQL for various applications (copy/paste, probably). Using SQL functions/procedures/views to return the same result sets would eliminate the duplication and provide "one true source" for such queries. Plus, it would reside in the actual database, allowing it to be portable and not requiring any extra work in the client applications.
- fnord123 9y agoYes, exactly. Also, tables could be refactored without having to update clients.
- ajuc 9y agoTo play devil's advocate - PL/SQL is much closer to SQL than Java/C#/C++/.... The impedance mismatch between relational and OO is much bigger.
- ch4s3 9y ago>Why not functions to hide the table abstractions Isn't that roughly what an ORM/ODM is?
- fnord123 9y agoORM is in the client. I'm talking about making wrapping functions for popular views in the database itself.