I love to write SQL queries and even use them extensively in my code. However, almost everyone I talk to resists this, and warns me that "one day you will regret..." It gives me an uneasy feeling that I actually might, though it hasn't happened yet.
If you scatter these throughout your code and then the database schema changes, you have a hell of a refactoring job to make sure everything still works. One advantage of an Orm is that if you keep your objects in line with your database the generated sql will stay correct.
Personally, I am more than happy to take that hit. I think it is a price well worth paying in order to have optimised queries that do exactly what I what them to do. To make it easier for myself though I will always try to keep my queries in one place in the code. Then all my code needs to know is it is getting clients_older_than(32) or whatever..
It maps 1:1 to the SQL statement that gets executed so there's no magic e.g. extra n+1 queries being introduced without you noticing.
But if you change your schema, re-generate, and immediate compile errors showing you where you're referencing something that's now been deleted, so it's not fragile like putting SQL in a String in your code (where if the schema changes, the compiler can't help you)
Intellij is pretty good at highlighting and pointing out typo errors. The code will still compile though.
> no typing information
Given the popularity of javascript, I thought this would now be considered a feature ;)
To me the line of argument around lacking static type checks always felt like FUD, but maybe it's best countered with counter-FUD...
If you've opened up your DB layer to accept strings (that you are supposed to build with a SqlBuilder statically referencing column and table names) you still have no guarantees that everything still works because some code somewhere might have just used a handwritten String instead. In other words you still need tests.
The static type checking value only helps against schema changes that rename or remove things, which is generally pretty rare, and depending on the level of autogeneration and query mapping might not even help if the column type changes. It doesn't help when the semantics change. For example (much more commonly) the introduction of a new column that is expected to be filtered on and/or required to be set a non-null value in inserts. So the static references are mainly reduced to being a mechanism to make finding users of a table easier, and hope that whoever is making the schema changes is going to look around for those users and update them accordingly. (When you don't own the table, as is usually the case in large software with many teams, the table owner similarly doesn't own your code, so that's kind of a vain hope. The best solution is what is done in open code with unknown consumers -- versioning. Stop renaming/deleting/changing the semantics of things, just provide new things, under different versions or namespaces if they really need to share names.) But you can accomplish this to the same effectiveness by creating a static reference to the table near where you execute queries on it, and use easy to read handwritten strings for the rest. In my experience though many queries are trivial enough that a SqlBuilder-esque pattern isn't much overhead, it's not a hard hit most of the time, and more complicated ones may belong as stored procedures if you've already invested in that direction.
If you go the full ORM route, which it sounds like you aren't suggesting since you mention optimized queries that do exactly what you tell them to do, the table owner may graciously update the central object builder to set a non-null default value for everyone (or specify one in the table def), which would maybe stop things from breaking immediately, but maybe wouldn't actually stop breakages (especially on the select side where data is retrieved that under the new semantics was meant to be filtered), so end users are still on their own for whether this new column matters to them or not. And that's just one type of schema change that's not a simple rename or column removal, there are many others.
The solution becomes really obvious if it happens once for 95% of people good at computers.
You might regret it a bit more if you do something like a filesystem read to get the query.
There's your regret. It hurts so much, doesn't it? :)