4 ms·
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 regre
by patkai 8y ago
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.
- idle_zealot 8y agoSo long as you're using prepared statements, you should be okay.
- weavie 8y agoWhat difference does using prepared statements make?
- skunkworker 8y agoYou can avoid SQL injection. It makes it harder to shoot yourself in the foot, though still possible to inject if your prepared statement includes a string interpolated variable.
- weavie 8y agoYes, good point.
- qaq 8y agoYou have abstraction between your code and DB you can change underlying tables but interface will remain consistent requiring no changes to the application. There are security benefits too easier to prevent SQL Injection, you also only give the app user permission to execute sps so no arbitrary queries can be run.
- reacweb 8y agoWriting better code makes programs more robust and reduces maintenance cost. For a management point of view, the boss may think that your project was more easy than the project given to the other team that butchered their work.
- weavie 8y agoI think one of the problems with SQL queries is that as far as your application is concerned they are just strings. There's no typing information or even syntax checking. Also if you do something like 'select * from' then there the results returned aren't deterministic. 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..
- adrianmsmith 8y agoYou can get the best of both worlds by using e.g. jOOQ in Java (allows you to write e.g. db.select(MY_TABLE.MY_COL).from(MY_TABLE) where those values are generated from the database therefore they exist and are of the right type. 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)
- weavie 8y agoI wasn't familiar with jOOQ before. It looks pretty comprehensive.
- matwood 8y agoHonestly, jOOQ is one of the killer libraries for Java that keep me using it for backend code.
- gldalmaso 8y agoCame here to endorse jOOQ as well. It alleviates a lot of boring boilerplate like ORMs do, but you don't trade off control neither take the risk of wrong use silently causing problems.
- FLUX-YOU 8y agoThe only way you'll regret it is if you type the query directly into an editor which doesn't support syntax highlighting or doesn't validate the query before you run it and it blows up in production. This is 'typo crashed my code' kind of stuff. 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? :)
- Yokohiii 8y agoDon't let the dark side blind you. Managing data is complex. ORMs add more complexity and they make data look easy. IMHO the biggest deal with most ORMs is that the satisfy the programming platform and coders aesthetics.