4 ms·

If I could downvote topics, this would get one. We have an API for that. It's called prepared statements. We also have a lot of other APIs that try and do awa

If I could downvote topics, this would get one. We have an API for that. It's called prepared statements. We also have a lot of other APIs that try and do away with SQL, and fail in one of two ways. The first is people write code like personDAO.fetchAll().toList().size() - admittedly you can make the same mistake with SQL but I haven't noticed this quite as much. The second way is that SQL is a very powerful language once you combine cursors, grouping, inline views etc. and I haven't yet met an ORM libary that can come close. So you're back to fetchAll() and do the work on the web server if you think SQL is a scary language that you don't want to touch.

k__ 9y ago
You're right. Most ORMs are rather bad. I like query builders tho, so I don't have to fiddle around with SQL strings.
Shoothe 9y ago
Couldn't agree more. Just recently I've used SQL to write a "lock and fetch" data query in one relatively simple instruction reducing the application logic complexity considerably. By the way can anyone recommend a good book on advanced SQL (not a specific DBMS)?
- red_admiral 9y ago
  There was a HN topic on this about a year ago: https://news.ycombinator.com/item?id=13417326 https://news.ycombinator.com/item?id=13417326 The problem with "advanced SQL, not a speficic DBMS" is the more advanced you get, the less overlap there is between different dialects. MariaDB/MySQL for example didn't support WITH until the 10.x versions (WITH RECURSIVE was added in 10.2.2 according to the site) and it still needs an explicit alias for SELECT ... FROM (SELECT ...) style constructions. SQLite can't do RIGHT/FULL OUTER JOIN etc. Karwin's SQL Antipatterns is a good book for a "second look at SQL" but I wouldn't really call it advanced yet. The manuals for postgres and MariaDB, while written as manuals and not tutorials, contain a lot of useful information and a fair part of it applies to most relational databases. Performance tuning is even more specialised. Although I've been praised as a consultant before now for simply adding an index to something, my experience is mostly on the MySQL side of things - I don't think it would be hard to beat me in an "advanced postgres" quiz.
flukus 9y ago
Prepared statements only get you so far though, they're fine for parameters, but often you run into situations where you do have to build sql manually to be able to order and filter on fields dynamically. Then once part of the query is generated by string concatenation the temptation is always there to just add that one little string parameter. I've seen various mechanisms to order dynamically, but nothing as simple and readable as "if orderBy == 'foo' then sql += 'order by foo'". Dynamic filtering has a few approaches but I've seen a lot of performance issues from them. Most sql optimizers have a much better time if you concatenate the exact query you need in code. I would argue that 99% of the time such UI's arise from a failure to understand and cater to user workflows, but they still exist in the wild and I don't seem them disappearing any time soon.

k__ 9y ago

You're right. Most ORMs are rather bad. I like query builders tho, so I don't have to fiddle around with SQL strings.

Shoothe 9y ago

Couldn't agree more. Just recently I've used SQL to write a "lock and fetch" data query in one relatively simple instruction reducing the application logic complexity considerably. By the way can anyone recommend a good book on advanced SQL (not a specific DBMS)?

red_admiral 9y ago
There was a HN topic on this about a year ago: https://news.ycombinator.com/item?id=13417326 https://news.ycombinator.com/item?id=13417326 The problem with "advanced SQL, not a speficic DBMS" is the more advanced you get, the less overlap there is between different dialects. MariaDB/MySQL for example didn't support WITH until the 10.x versions (WITH RECURSIVE was added in 10.2.2 according to the site) and it still needs an explicit alias for SELECT ... FROM (SELECT ...) style constructions. SQLite can't do RIGHT/FULL OUTER JOIN etc. Karwin's SQL Antipatterns is a good book for a "second look at SQL" but I wouldn't really call it advanced yet. The manuals for postgres and MariaDB, while written as manuals and not tutorials, contain a lot of useful information and a fair part of it applies to most relational databases. Performance tuning is even more specialised. Although I've been praised as a consultant before now for simply adding an index to something, my experience is mostly on the MySQL side of things - I don't think it would be hard to beat me in an "advanced postgres" quiz.

flukus 9y ago

Prepared statements only get you so far though, they're fine for parameters, but often you run into situations where you do have to build sql manually to be able to order and filter on fields dynamically. Then once part of the query is generated by string concatenation the temptation is always there to just add that one little string parameter. I've seen various mechanisms to order dynamically, but nothing as simple and readable as "if orderBy == 'foo' then sql += 'order by foo'". Dynamic filtering has a few approaches but I've seen a lot of performance issues from them. Most sql optimizers have a much better time if you concatenate the exact query you need in code. I would argue that 99% of the time such UI's arise from a failure to understand and cater to user workflows, but they still exist in the wild and I don't seem them disappearing any time soon.