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.
> 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.
For a large system that has many different teams working on it, it is better to have a core API layer with clear ownership, than to not have one. But why would you choose to build that with database stored procedures? If this was built 25+ years ago, then that is all the answer that is needed.
You mentioned elsewhere "But PL/SQL is a horrifying language. You have barely any facilities for modularity, encapsulation or composition"
That's not true about PL/SQL. You can modularize/encapsulate and compose multiple sql functions.
Even plain SQL can be composable in some cases via views.
saxenaabhi · · focus · HN ↗
> 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.
jeremyjh · · focus · HN ↗
For a large system that has many different teams working on it, it is better to have a core API layer with clear ownership, than to not have one. But why would you choose to build that with database stored procedures? If this was built 25+ years ago, then that is all the answer that is needed.
saxenaabhi · · focus · HN ↗
I can see usecases in which API could make sense, but it doesn't matter in most cases.
SQL already has authorization/authentication built in. For rate limiting you can use something like planetscale's traffic control.
jeremyjh · · focus · HN ↗
saxenaabhi · · focus · HN ↗
That's not true about PL/SQL. You can modularize/encapsulate and compose multiple sql functions.
Even plain SQL can be composable in some cases via views.
jeremyjh · · focus · HN ↗
[deleted] · · focus · HN ↗
[deleted]