4 ms·

I get the type-safe part, assuming it means the eventual query will produce a predictable result set compatible with wherever it's being read into (I'm not a ha

I get the type-safe part, assuming it means the eventual query will produce a predictable result set compatible with wherever it's being read into (I'm not a haskeller), but a) SQL is easy enough to learn syntactically b) there are good reasons not to create dynamic SQL; it's better to write it such that a query plan can be cached (for scalability). Which is very likely possible here but is it done? I wonder if the person who wrote this understands SQL to sufficient depth. Not saying either way, just asking.

hendi_ 6y ago
Your point a) is moot, this is not what this is about. When writing manual SQL nothing stops you from doing `SELECT * FROM persons WHERE age > 'eighteen'` (the `age` field is a number, obviously). Coming from Django I was often bitten by `Person.objects.filter(age__gt=trashhold) # boom!` (note the misspelled variable "threshold") or, if you prefer manual writin SQL: `Person.objects.raw("SELECT * FROM persons WHERE age > {}", trashhold) # boom! as well`. (Admittedly this is unrelated to type safety, just a disadvantage of Python being interpreted, but still something that Haskell/IHP protects me of.) Regarding b), yes there are good reasons for prepared statements. I won't go into your argument "for scalability" (premature optimization cough) but cached query plans aren't perfect either: data and data typologies change, and so does the optimal query plan. See https://blog.soykaf.com/post/postgresql-elixir-troubles/ https://blog.soykaf.com/post/postgresql-elixir-troubles/ for a blog post about Elixir/Phoenix/Ecto users being bitten by that. > I wonder if the person who wrote this understands SQL to sufficient depth. Not saying either way, just asking. I did not write the IHP code, just the above comment. But I've read many of CJ Date's books and have a good grasp on the relational model, and of SQL too. I simply don't see things as black/white, there are certain queries where I do prefer prepared statements and functions, or PL/pgSQL even, then there are ones where I use an external .sql file to keep things tidy. But most of the time (especially in the user facing part of a web app) I like my ORMs or SQL DSLs.
- necovek 6y ago
  Unless you've got `trashhold` actually defined, tools like flake8 would protect you from that mistake with Python. Using type hints would also protect you from using variables with incompatible types with a better designed API (Django's ORM API is anything but). As for SQL performance, I've usually only worked with up to hundreds of millions of rows per table, and the most important thing at that scale was to attempt to keep indexes in memory cache (that improves or degrades the performance by a couple of orders of magnitude). I haven't had a need for query plans to be cached, though I did use subselects to force particular query plans when needed (which has the similar problem of query plans not being the most efficient ones when data evolves sufficiently).
fabian2k 6y ago
You really can't avoid dynamic SQL if you need it for your software. For example, if you give your users the ability to select filters to apply to the data fetched from the DB, you will have to dynamically create the SQL unless the number of possible filters is extremely low. Above a certain scale this might not be feasible for predictable performance, but not everything has billions of rows. The choice isn't to use dynamic SQL or not in these cases, it's whether you implement the feature that requires dynamic SQL or leave it out entirely.
- hombre_fatal 6y ago
  Though interestingly (as you acknowledge via "if you need it") that also tends to be the only time a traditional site/app does need it: a /search endpoint.

hendi_ 6y ago

Your point a) is moot, this is not what this is about. When writing manual SQL nothing stops you from doing `SELECT * FROM persons WHERE age > 'eighteen'` (the `age` field is a number, obviously). Coming from Django I was often bitten by `Person.objects.filter(age__gt=trashhold) # boom!` (note the misspelled variable "threshold") or, if you prefer manual writin SQL: `Person.objects.raw("SELECT * FROM persons WHERE age > {}", trashhold) # boom! as well`. (Admittedly this is unrelated to type safety, just a disadvantage of Python being interpreted, but still something that Haskell/IHP protects me of.) Regarding b), yes there are good reasons for prepared statements. I won't go into your argument "for scalability" (premature optimization cough) but cached query plans aren't perfect either: data and data typologies change, and so does the optimal query plan. See https://blog.soykaf.com/post/postgresql-elixir-troubles/ https://blog.soykaf.com/post/postgresql-elixir-troubles/ for a blog post about Elixir/Phoenix/Ecto users being bitten by that. > I wonder if the person who wrote this understands SQL to sufficient depth. Not saying either way, just asking. I did not write the IHP code, just the above comment. But I've read many of CJ Date's books and have a good grasp on the relational model, and of SQL too. I simply don't see things as black/white, there are certain queries where I do prefer prepared statements and functions, or PL/pgSQL even, then there are ones where I use an external .sql file to keep things tidy. But most of the time (especially in the user facing part of a web app) I like my ORMs or SQL DSLs.

necovek 6y ago
Unless you've got `trashhold` actually defined, tools like flake8 would protect you from that mistake with Python. Using type hints would also protect you from using variables with incompatible types with a better designed API (Django's ORM API is anything but). As for SQL performance, I've usually only worked with up to hundreds of millions of rows per table, and the most important thing at that scale was to attempt to keep indexes in memory cache (that improves or degrades the performance by a couple of orders of magnitude). I haven't had a need for query plans to be cached, though I did use subselects to force particular query plans when needed (which has the similar problem of query plans not being the most efficient ones when data evolves sufficiently).

fabian2k 6y ago

You really can't avoid dynamic SQL if you need it for your software. For example, if you give your users the ability to select filters to apply to the data fetched from the DB, you will have to dynamically create the SQL unless the number of possible filters is extremely low. Above a certain scale this might not be feasible for predictable performance, but not everything has billions of rows. The choice isn't to use dynamic SQL or not in these cases, it's whether you implement the feature that requires dynamic SQL or leave it out entirely.

hombre_fatal 6y ago
Though interestingly (as you acknowledge via "if you need it") that also tends to be the only time a traditional site/app does need it: a /search endpoint.