> Use stored procedures
For God's sake, please don't
For God's sake, please don't
Stored procedures are much faster than writing logic in some remote server (just by virtue of getting rid of all the round trips), require far less code (no DAOs, entities and all that crap which simply serves to duplicate existing definitions), and have built-in strong consistency checking primitives - which can even be safely delayed until the end of the transaction.
And what people do is, they throw all these advantages away because they can’t be bothered working out how to integrate the stored procedure code ergonomically into their workflow.
I mean - I even use an IDE (JetBrains) to write pl/pgsql. It’s just another file in my repo. Get to this point and stored procedures are a game changer.
- they live in source control
- they are covered by automated tests
- they are applied using some form of automatic database migration system (not by someone manually executing SQL against a database somewhere)
If you don't have the discipline to do these things then they are likely best avoided.
I'd go further and say you should avoid databases and maybe even persistence entirely if you don't have the discipline to do the above. Sprocs will be the least of your problems otherwise.
so that probably excludes 95% of legacy codebases out there from the 90s,00s
For "you" in my comment, please read "one" instead:
> I think stored procedures can be perfectly safe provides one follows these rules:
> ...
> If one doesn't have the discipline to do these things then they are likely best avoided.
This is the classic "carpenter blames his tools for crappy results" argument. Implementation isn't easy.
Now I do think there is a benefit in stored procedures and triggers (E.g. for audits) if they don't contain too much logic or complexity.
I think this is the catch. Most folks who are arguing against SP have been burned by huge complex stored procedures with nested dependencies with deeply intertwined business logic and rules. I completely agree that you shouldn't use a SP in that way. But to help perform maintenance, or to audit, or perform data correction all make sense when kept small and simple.
As a side note, I did not realize $diety was concerned about DDL/DML, so thanks for pointing it out. I never really thought about it.
So… why exactly would you exclude compute running close to the storage?
You can use that minimal latency.
Of course, people can create an uncontrolled mess of spaghetti code out of sps/funcs… like they can with any kind of code.
We made this tool to get the best of both worlds: