> I've worked (and work) on old projects with hundreds
> of SQL queries spread throughout the code,
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".
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
$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});
} my $current_month = Month->new();
my $end_month = Month->new()->plus_months(4);
while ($current_month <= $end_month) {
$yyyymm = $current_month->strftme("%Y%m");
$last_day = $current_month->last_date()->strftime("%Y%m%d");
$first_day = $current_month->first_date()->strftime("%Y%m%d");
$dbh->do("create table my_partitioned_table_$yyyymm
() inherits from my_partitioned_table");
$dbh->do("alter table my_partitioned_table_$yyyymm
add constraint ck_my_partitioned_table_$yyyymm
check (date between '$first_day'::Date and '$last_day'::Date)");
$current_month->next_month();
}
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.
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.
1) Testability. you can write test cases against a stored proc. You cannot write test cases against a SQL query embedded in the middle of a function in another programming language.
2) Single point of control. You can have one function that runs the stored proc there and check properly for SQL injection, ensuring that everything is free of SQL injection as far as front-to-backend is concerned (and in Pg, that only leaves SQL injection issues in dynamic SQL which can be searched for and audited separately).
So that's why not to put it there.
As to changing the query.... I only use databases which come with transactional DDL so that's not an issue as long as inputs and outputs don't change. If the inputs and outputs have to change, we can handle that too with transactional DDL and the fact that we make the interfaces discoverable. In this case, we'd
BEGIN;
DROP FUNCTION IF EXISTS foo_bar(...);
CREATE OR REPLACE FUNCTION foo_bar(...)...;
COMMENT ON FUNCTION foo_bar(...) IS $$...$$;
COMMIT;
Because of the way Pg works this shouldn't really cause issues for currently running queries.
As for finding the right function, since these are all called on a declarative interface so you know what the function name is quickly, and so it is a matter of quickly finding it in a directory of SQL files.
As for testability, don't underrate this. You can insert data, run your tests, and roll back. So for example for our 1099-MISC and INT handling, we run more than 40 tests on a pre-written data set that we can run without ever committing to production and which will never conflict with production data (and therefore does not lock other users out). That's approx. 20 tests per sproc there.
* http://ootips.org/yonat/patterns/phrasebook.html
"ORM is an anti-pattern": http://seldo.com/weblog/2011/08/11/orm_is_an_antipattern
I suspect for certain parts of some apps, ORM's might work OK. But in general for applications of any complexity they will break down hard and fast, and lead to bad db design, bad performance, or both.
In other words, I see an ORM as an antipattern because the subset of cases where it works really well at bridging the gap you probably don't really want an RDBMS in there anyway.
And if you do make it work (encapsulating your db behind updatable views) without running into this, it's a heck of a lot more work than hand-coding SQL.....
What we've done in LedgerSMB is quite simple. All db statements in new code (outside the mapper functions) are in stored procedures. Everything is mapped in there using a Perl API so in the new code (post-1.2) otherwise all SQL is in one set of files and all Perl is in another, with minimal overlap.
This sort of thing makes for very clean development. No, spaghetti code is making me have 15 files open in VIM trying to figure out which of them has the function that is being called in it, with several candidates all bearing the same function name (oh, the horrors).
Something like SQL::Abstract (https://metacpan.org/module/SQL::Abstract) or Fey (https://metacpan.org/module/Fey) would slot in easy for most tasks like this:
use SQL::Abstract;
my $sql = SQL::Abstract->new;
$dbh->do( $sql->delete("my_table") );