How do you do that without massively violating DRY? I've done tiny little personal projects where I use raw SQL, then eventually I find myself storing parts of queries into variables and concatenating them later, and it doesn't take long to realize I should just use a decent ORM.
It depends on the project. Quite often there simply isn't that much repetition.
On the flipside, how do you feel about slower performance? About diagnosing problems through multiple abstraction layers? Or about having different abstraction layers for different languages?
Stored procedures are great for solving some of that pain. For anything really serious you might want to go all the way and only use stored procedures so there's no ability/risk of your app code ever making raw SQL calls.
Stored procedures are a fucking pain. Now you've managed to split your logic in two completely different locations, and you get to enjoy rolling out new versions of the procedures in their own migration. Not to mention you're tied to your database.
On the other hand, ORMs can be a huge source of inefficiency if you don't look at the generated SQL and use lazy collection thoughtlessly, though they avoid a lot of boilerplate when saving a complex object graph.
A happy(?) medium is probably something with a powerful data retrieval API, which makes for composable queries (SQL query strings compose very badly).
In some environments, having the logic in the database is actually the better choice. When you are dealing with data that is in the million plus rows, there is more to lose from the separation of logic than just letting the database do what its good at while adding your logic.
All these discussions of which is better (ORM or straight SQL) should be put in context of the type of application, the amount of data and the complexity of the schema.
I have worked with systems which constrain all logic to the application server and when the data set is small it works fine, when its not, its a nightmare. Why should you bring down a million rows down to your application layer just so you can update a couple of columns. Doing this in the database as a set operation becomes trivial and fast.
No doubt you occasionally need to push logic down to the database, much in the same way that you sometimes need to uglify your code to make it fast enough, but in my experience, it's a good way to get a painful-to-manage codebase full of fantastic ideas such as "let's build this complex XML structure by concatenating strings".
Can you explain? I have no problem and have used or written my own SQL composition libraries pretty easily. It is code-composition, no doubt, but the BNF for SQL is very easy to grok even given the variations in platform.
Raw SQL is terrible when you need something a bit complicated. Want to do a complex search? Unfortunately, your coworker forgot an " AND " in a little-used function, which is going to blow up on you at runtime. Or maybe you didn't count the placeholders right and you inserted the wrong numbers values. Can you make that work for you with a library? Sure. But on its own, it's just bad.
On the other hand, SQLAlchemy's query API is quite nice, and composes just fine.
