5 ms·
Both raw SQL and using an ORM have their place. The latter is definitely less prone to unintended SQL injections but it's still possible. The reverse is true to
by sehrope 13y ago
Both raw SQL and using an ORM have their place. The latter is definitely less prone to unintended SQL injections but it's still possible. The reverse is true too. Raw SQL can be quite secure if you're not an idiot about it (golden rule: never build command strings from user input). If you use prepared statements across the board you don't even have to bother with sanitizing user input[1].
The main reason to use an ORM is for programmer efficiency. Sure I can write a bunch of CRUD operations but why bother if I can have the ORM do it for me? Since CRUD operations are either inserting a new row or accessing/updating by a primary key it'll be indexed as well so no perf issues[2]. The additional work by your app code for the ORM library is meaningless compared to the DB round trip anyway. My favorite part? Adding a new field goes straight to the model and nowhere else[3].
The big place for raw SQL is as the secret sauce on the meat and potatoes of your apps. Once you've got a real data set and you want to combine, slice, dice, you're not going to do that through an ORM. You want those queries to be as performant as possible. If you've structured your tables properly (proper foreign keys, normalization, etc) then the writing custom SQL will also be much more straightforward then trying to kludge together ORM commands to do what you want. On top of that, the work will happen on the database where it can filter it prior to your app processing it.
For our app[4] most of the CRUD pages are handled by an ORM but there's quite a bit of custom SQL too. One example is for security authorizations. Validating security authorizations (can user X access DB y) is a hierarchical check. The logic is all done in a Postgres stored proc (well technically a function). Doing it via an ORM would inefficient, both in programmer time and computer runtime.
[1]: You probably should though. It's generally a good idea to have some kind of white listing for what is an acceptable value for a field. Either way though you need to make sure you escape them when outputing HTML for webapps to prevent XSS. That combined with prepared statements when interacting with user inputs is the only right way to do things.
[2]: For basic CRUD operations and even simple one-to-many lists. Beyond that things can and do get hairy but those aren't the majority of cases. The majority of app code is vanilla id-based select, inserts, updates, and deletes.
[3]: http://en.wikipedia.org/wiki/Don%27t_repeat_yourself http://en.wikipedia.org/wiki/Don%27t_repeat_yourself
[4]: http://www.jackdb.com/ http://www.jackdb.com/
- dustingetz 13y agoi think your third paragraph is more or less false for apps over a certain size, think enterprise-y stuff. it is certainly false for the class of apps that i work on - big pharma regulatory compliance software, very complex data model, hundreds of tables etc. Our ORM can express higher level queries and a wider set of queries than raw SQL - you can express a query in one line (one thought) that compiles down to quite a few nested SQL expressions. Finally, the problems of orm stem from the fundamental nature of SQL, so dropping into raw sql couldn't possibly fix them. You need something like Datomic or CQRS/ES to remove the object/relational impedance mismatch at a fundamental level. (This is analogous to why Git rocks compared to SVN; Git solves the problem of centralized mutable state at a fundamental level which opens the door for a better model of the problem and more powerful abstractions.)
- taspeotis 13y ago> big pharma regulatory compliance software I hear that these sorts of things use EAV [1], which is traditionally something that ORMs do not handle well. The rationale of EAV over another model is that entities have MANY optional attributes and you'd have horrible data sparseness (without EAV). But you say you use an ORM: > Our ORM can express higher level queries and a wider set of queries than raw SQL Are the anecdotes I hear about using EAV in these sorts of applications right or is the problem domain so big that there's room for EAV and non-EAV and nobody's wrong? Just curious. [1] http://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80%93value_model http://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80%...
- dustingetz 13y agoyeah, Datomic is EAV (technically EAVT, it's event sourced so there is a notion of time and reads/writes need be acid with transactions and stuff) and EAVT can express even higher level queries than an ORM can, mostly because you can cache the indexes locally so you can do consistent read queries in your app process (Datomic's query engine is a library that runs inside your app, if cache is hot reads don't touch network... like git). From my understanding it doesn't have much to do with sparseness, though you certainly can support sparse objects performantly with EAVT. EAVT/Datomic is a better fit for apps with complex structured data than an ORM over SQL, but migrating there is no trivial feat. Nor is convincing my customers in their due diligence phase, as they have surely been burned before by some kid pushing MongoDB, but they understand SQL and know the product can be successful with SQL, they know they can go get Oracle consultants to save their ass in 10 years when my company is sold, etc.