BoiledCarrot
martinfowler.com
martinfowler.com
Misinformed. Squirrels are pretty tasty. Need a few, though.
More to the point of the article, I appreciate that Fowler calls out stored procedures as something often misused but secretly pretty awesome. If you are working in an environment where you can handle a SQL database as a canonical datastore--and of course there are literally kajillions of opportunities for that--then sprocs can do a lot to isolate business logic at a level lower than the application. I wouldn't use them in every case, but I've had good success using them in many cases.
For example, look at how many ORMs have facilities to add commonly-required conditions to every query (e.g., "is_active = true"), but lack the ability to use views.
The problem here, as always, is people. Pointing this phenomenon out to someone causes cognitive dissonance (e.g. "Rails doesn't suck, so I must suck?") which is often harder to deal with than eating boiled carrots.
Almost every time I've encountered an ORM it's been an over-boiled carrot. I think I've just had bad luck, though.
You really learn something new every day if you're observant. Thanks!
There's nothing about plain SQL that means you "don't need" to worry about the n+1 queries, although I suppose the author of our authentication class apparently thought that way, because while we have a way of getting all objects from the DB, and we have a way of checking the DB to see if the user has permission to view an object, we don't have a way of getting all the objects a user can view.
But at least all our queries use delightfully artisanal hand-crafted SQL! :)
I think the better lesson is that if you're writing code that touches the DB, you need to be aware of what your code (and whatever libraries or frameworks you're using) are actually doing to the DB, with a vague idea of how DBs work. The real crime of ORMs is they hint (or sometimes outright promise) that the DB is being abstracted away and you no longer need to worry about indexes or joins or constraints, or even whether you're data is being persisted to a RDBMS or a NoSQL datastore. Literally: I ran across one ORM recently that used as a major selling point that you could use MySQL or MongoDB interchangeably with no changes in your app code and I just shuddered.
So yeah, I'd say that the issue with ORMs isn't that they force you to worry about the n+1 query problem; it's that they encourage you not to worry about it. Everyone should be worried about it! :)
Ok... what if I was to tell you that you can get SQL injection with stored procedures? I worked for a company where we had that problem. I hadn't written them, but I knew that was impossible so I bet my boss that's not the reason.
And then I looked at them and discovered they were doing something like
s = "SELECT * FROM table WHERE id =" + @id
and then
exec_sql s
(I don't remember the exact syntax, but it was something like that.)
Misuse of what a stored procedure is supposed to do? Yeah, so's the "n+1 query problem" with ORMs... Though I will grant you that the second one is much easier to get wrong, the guy who wrote the SP had to work to make it vulnerable to injection...
Of course I realise lots of people will happily continue to use horrible ORMs and not learn how they work, but that's not a ORM specific problem.
Not just your bad luck. Personally, at this point, I consider ORMs an anti-pattern.
i s'p it's properly a database toolkit w/ an ORM component, written in such a way that the ORM can be refactored out once the database structure begins to stabilize.
... also (and this is _totally_ a crucial point), as someone else commented, squirrels are not terrible. probably better than McDonald's, depending on how they're cooked.
What exactly is the problem? :)
Stored procedures are often created and modified manually, directly in place, maybe by some external team/consultant, with total opacity about what SPs exist on which databases and with what code, when they changed etc.
There is of course no need for things to be this way.
This puts them on more-or-less the same level as shell scripts to me. They're fine if they're tiny, in source control, and unsurprising.
In both situations, the stored procedures were written by the DBA, and the DBA only. And they simply added them to the databases you needed, and then granted access. In one case, you did have 'permission' to write ad-hoc SQL, but it all had to be reviewed by the DBA.
And that was the big crux of the problem - the DBA. In both cases, they simply wrote sprocs and applied them to the databases. That was it. No version control, no testing, no application review, no sitting in on regular project meetings to understand the full problem. You'd have to describe to them what needed to be done, and they'd draft up something, put it on the server, and give you access.
So yes, these were not problems inherent to stored procedures. They were definitely a cultural problem. But those have been my exposures to "sprocs first" thinking (or "sprocs only" thinking). On most of my other projects, I use an ORM with schema definitions that are versioned, that are used to generate tables/indexes/migrations/etc, and then some queries are done by hand (usually complex reporting queries), and in some cases, stored procedures are used - maybe 90-95% queries run are ORM-generated, 3-5% are by-hand, and occasionally sprocs on an as-needed basis (slight perf boost, or logic reusable by multiple external applications are the two most common reasons).
And when I write the sprocs, they get version controlled and added to a migration process so they can be recreated in the build process as needed. By far the biggest issue I see with sprocs not being in version control is that it's DBAs writing/forcing the sprocs, and they're "too busy" for version control (or don't have the same tools, or don't understand it, or whatever other excuse is given on any day).
Seriously, why?
In this case, steam carrots.
(Apologies for not adding anything to the actual discussion!)
(might as well keep the off topic in the same thread)
In the past, regional dishes were a result of not just culture but the taste of foods grown locally. Things following "old" recipes with today's food will never capture the flavor. The foods are just that different.
I woulder why people were content with foods that are not tasty for prolonged period of time.
Disclosure: I have a back yard garden and fruit trees.
1) People had terrible teeth
2) Cooking devices were unreliable
3) Houses were cold so it was difficult to keep things warm without them continuing to cook & soften
4) People were accustomed to institutionalised cooking (boarding school, army, servants)
5) Bad practice was passed on through "Cultural acquisition"
There are pre-made tools to do this, and its relatively easy to author your own extracting of the DDL from all DB objects into a tree and commit them into revision control.
There is little difference between SQL and other languages in this context.