3 ms·

In small doses, you can get things done quickly like this. However, it builds up over time into a ball of spaghetti, so yeah, maybe after the 5th or 6th blob of

In small doses, you can get things done quickly like this. However, it builds up over time into a ball of spaghetti, so yeah, maybe after the 5th or 6th blob of inline SQL, you might think about using an abstraction while you still can. I've worked (and work) on old projects with hundreds of SQL queries spread throughout the code, it _is_ spaghetti code and there are better ways to do it.

pyre 15y ago
> I've worked (and work) on old projects with hundreds > of SQL queries spread throughout the code, So have/do I. The fact that there is SQL inlined with the code is probably the least of your worries when talking about such a large/old code base.
- postfuturist 15y ago
  Actually, the bits of SQL everywhere is the #1 problem with the code. We spent weeks just auditing them all to remove SQL injection vulnerabilities. All the SQL are fragile bits of code, difficult to write meaningful unit tests for, since you can't separate database interaction from logic, etc.
  - einhverfr 15y ago
    Been there too, which is why I like stored procedures now (testability, clean code, predictability).
einhverfr 15y ago
The issue with inline SQL queries isn't the fact that SQL is mixed in with other programming languages itself. It's the propensity to write BAD SQL that's inlined because now you can apply the full power of the programming language to generate your SQL. Stored procedures are a great way to abstract this out and force good SQL coding practices btw.
- ars 15y ago
  > Stored procedures are a great way to abstract this out and force good SQL coding practices btw. How exactly do stored procedures prevent you from writing "BAD SQL"? They do no such thing. Stored procedures are a great way of adding complexity to your code, complexity equals bugs and longer development time. There are reasons for stored procedures, but using them simply to remove SQL from your front end is not one of them. And certainly not because of a fear of "BAD SQL".
  - einhverfr 15y ago
    How exactly do stored procedures prevent you from writing "BAD SQL"? To be clear, they prevent one specific case of bad sql, namely queries that are written like this: SELECT a.*, $col_list FROM $table a JOIN $second_table b ON $join WHERE $where $group_by $having; Then when you go to find out how $where is define you see a lot of statements like: if ($http_query->{date_from}){ $where .= " AND date_entered > " . $dbh->quote($http_query->{date_from}); }
    - enqk 15y ago
      And if you forbid that in perl and end up having query generation inside the stored procedure, what have you gained?
      - einhverfr 15y ago
        There are very few valid cases for stored procedures to generate queries like that. And at least with PostgreSQL, dynamic SQL can be spotted with automated searches pretty easily. It's pretty easy to simply add all filters that might be of interest: WHERE (date_entered > in_date_from or in_date_from IS NULL) .... Then you also gain testability. We can write test cases for the stored procedures because they have discrete entry points and make sure they are acting according to spec.
        ars 15y ago
        Why can't you use that type of code in your frontend? Stored procedures make it much harder to understand the code since you can't just read the query - you have to find the stored procedure and check it there. (And be realistic - no one has perfect documentation.) And heaven forbid you have to change the query - now you need to make sure no one else is using it, and you end up with tons of versions of similar stored procedures, not all in use, and many that could be combined. It doesn't seem worth it to me. I use stored procedures for data reduction - I want to keep the data in the database, and process it before sending it out. (Saves bandwidth vs doing it in the front end, and often it's faster too.) But that's it. Using stored procedures just to wrap ordinary queries is a terrible (but very common) idea. (I am aware that it saves time on generating a query plan, but usually that's a case of premature optimization.) And using it to enforce code policies? Way overkill.

pyre 15y ago

> I've worked (and work) on old projects with hundreds > of SQL queries spread throughout the code, So have/do I. The fact that there is SQL inlined with the code is probably the least of your worries when talking about such a large/old code base.

postfuturist 15y ago
Actually, the bits of SQL everywhere is the #1 problem with the code. We spent weeks just auditing them all to remove SQL injection vulnerabilities. All the SQL are fragile bits of code, difficult to write meaningful unit tests for, since you can't separate database interaction from logic, etc.
- einhverfr 15y ago
  Been there too, which is why I like stored procedures now (testability, clean code, predictability).

einhverfr 15y ago

The issue with inline SQL queries isn't the fact that SQL is mixed in with other programming languages itself. It's the propensity to write BAD SQL that's inlined because now you can apply the full power of the programming language to generate your SQL. Stored procedures are a great way to abstract this out and force good SQL coding practices btw.

ars 15y ago
> Stored procedures are a great way to abstract this out and force good SQL coding practices btw. How exactly do stored procedures prevent you from writing "BAD SQL"? They do no such thing. Stored procedures are a great way of adding complexity to your code, complexity equals bugs and longer development time. There are reasons for stored procedures, but using them simply to remove SQL from your front end is not one of them. And certainly not because of a fear of "BAD SQL".
- einhverfr 15y ago
  How exactly do stored procedures prevent you from writing "BAD SQL"? To be clear, they prevent one specific case of bad sql, namely queries that are written like this: SELECT a.*, $col_list FROM $table a JOIN $second_table b ON $join WHERE $where $group_by $having; Then when you go to find out how $where is define you see a lot of statements like: if ($http_query->{date_from}){ $where .= " AND date_entered > " . $dbh->quote($http_query->{date_from}); }
  - enqk 15y ago
    And if you forbid that in perl and end up having query generation inside the stored procedure, what have you gained?
    - einhverfr 15y ago
      There are very few valid cases for stored procedures to generate queries like that. And at least with PostgreSQL, dynamic SQL can be spotted with automated searches pretty easily. It's pretty easy to simply add all filters that might be of interest: WHERE (date_entered > in_date_from or in_date_from IS NULL) .... Then you also gain testability. We can write test cases for the stored procedures because they have discrete entry points and make sure they are acting according to spec.
      - ars 15y ago
        Why can't you use that type of code in your frontend? Stored procedures make it much harder to understand the code since you can't just read the query - you have to find the stored procedure and check it there. (And be realistic - no one has perfect documentation.) And heaven forbid you have to change the query - now you need to make sure no one else is using it, and you end up with tons of versions of similar stored procedures, not all in use, and many that could be combined. It doesn't seem worth it to me. I use stored procedures for data reduction - I want to keep the data in the database, and process it before sending it out. (Saves bandwidth vs doing it in the front end, and often it's faster too.) But that's it. Using stored procedures just to wrap ordinary queries is a terrible (but very common) idea. (I am aware that it saves time on generating a query plan, but usually that's a case of premature optimization.) And using it to enforce code policies? Way overkill.