Stop Using Stored Procedures in Your Application Code
We need to stop using Stored Procedures
Stored procedures offer no unique benefit over parameterized queries: both get plan caching and parameterization. But sprocs introduce separate versioning, risky migrations, and near-impossible rollbacks. Application developers should own their SQL, colocate it with app logic, and follow basics like minimizing network requests, using indexes, and watching for Cartesian explosions.
Please, application developer, I beg you, stop allowing reliance on Stored Procedures to enter your codebase.
- saxenaabhi
This is very poorly written and is wrong on basic facts.
> So what does a stored procedure get us?
> Absolutely nothing! Well, I mean, headache for one.
> ... we have to deploy migrations to update our queries, and we have to run diff migration_for_my_sproc migration_for_my_sproc_n to see how things changed
1) You can version sql functions in your repo alongside your code and deploy sql function alongside your db migrations(even in the same transaction).
Have one file per sql function and you can also compute checksums to speed it up if like me you have a repo with 600 stored procedures.
2) With stored procedures you have no need to to db.startTransaction on server when executing multiple statements and wait for db round trips. That's often the biggest reason for preferring stored procedures.
3) I have seen systems in healthcare/finance where different teams have no access to underlying tables and the db only exposes sql procedures. Every-time a procedure is called it also adds a log entry to an audit table.
Databases are a great piece of technology! Learning how to use them properly can have huge payoff in terms of business value generation.
EDIT: not mentioned in this article but people often mention testing difficulties with stored procedures.
You can have normal vitest tests testing your postgres functions with in-memory pglite.
- JeffRosenberg
> our application code and DB access are separately versioned, our sprocs can change right under our feet from aberrant (i.e. extremely rare, insane) DBAs
Your database (and its migrations) should be source controlled along with the rest of your code. Problem solved, now you can take advantage of some of the legitimate benefits stored procedures have to offer!
- dxxvi
I don't have any concrete examples at this moment to show the advantages of using stored procedures. So, suppose that we have to update 2 tables A, B if data in table C satisfies some conditions. Then stored procedures allow us to send only 1 request to the db while with the update commands we have to send at least 2 requests.