5 ms·

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. > ..

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.

jeremyjh 16d ago
> 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.
- saxenaabhi 16d ago
  Why wouldn't you use stored procedures for it? For internal teams why is it better to have a API? 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 16d ago
    Because SQL is a beautiful declarative language, and a disgusting imperative language.
    - saxenaabhi 16d ago
      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.
      - jeremyjh 16d ago
        Compared to any modern language, the facilities are primitive.
        deleted 16d ago
        [deleted]
  - degamad 16d ago
    I think the idea is that the stored procedures ARE an Application Programming Interface, as an alternative to a REST API. (I agree with you that for some use cases, an API consisting of a set of stored procedures is just as good as an API layered on top of the database in another language.)
    - bunderbunder 16d ago
      They are, but a REST API likely isn’t a good alternative because few RDBMSes speak REST. More likely they meant creating a database interface library in whatever programming language the application uses. And then have it talk to the DB through that library instead of scattering a fine mist of ad-hoc querying (or, shudder, active records) throughout your application.
      - 16d ago
999900000999 16d ago
I agree. The author doesn’t understand advanced database concepts and demands others stop them. Stored procedures ensure consistency. You know to add an item using one proc call. Not 20 different SQL calls. Just say you hate SQL. I do. For some god forsaken reason I got placed in a SQL heavy role a while back and was out within months. I both understand Postgres is the best solution for most DB use cases and I still reach for Firebase for my personal projects.
mamcx 15d ago
> Databases are a great piece of technology! Learning how to use them properly can have huge payoff in terms of business value generation. Certainly. Is a bit sad that the most well known are applications made for users (most RDBMS) instead of for developers, combined with a glorified DSL (SQL) that has never been made for make code at "large", not even at the scale of Lua or similar. I worked with FoxPro and do all the DB code was not only a joy, it makes too much sense! Thinking that interface with the network to store some meager byte on a disk is normal, that use terrible languages for data programming (not only SQL but JS, C# or whatever) is fine is baffling. But well. In terms of what is available: * RDBMS despite being applications (including sqlite) and not developer frameworks are plenty flexible and powerful for at least solve most data-oriented coding "DATA"-oriented is the key: Is wrong to say "this is backend, frontend, ui, etc" that is more a deployment issue and totally orthogonal. The most obvious example is the effect of N+1 queries: You are not thinking in what is data-oriented and using a incorrect programming language to solve what even an anemic DSL like SQL do much better. With the right mental model, is so easy to build a database schema with types, functions, procedures, views(that are so underused!) and such that make the clients DUMB. IS MUCH EASIER! And I work with ERPs, that are much more "complicated" than the median app. Let the DB do what it does well and the rest will be simplified by leaps and bounds. Even testing, just not commit the stupid mistake of use "mocks" that should be mocked and just spin ephemeral dbs/schemas. Is fast (even with PG) and will not create tons of churn because your regular langs is not ACID but the DB is.

jeremyjh 16d ago

> 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.

saxenaabhi 16d ago
Why wouldn't you use stored procedures for it? For internal teams why is it better to have a API? 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 16d ago
  Because SQL is a beautiful declarative language, and a disgusting imperative language.
  - saxenaabhi 16d ago
    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.
    - jeremyjh 16d ago
      Compared to any modern language, the facilities are primitive.
      - deleted 16d ago
        [deleted]
- degamad 16d ago
  I think the idea is that the stored procedures ARE an Application Programming Interface, as an alternative to a REST API. (I agree with you that for some use cases, an API consisting of a set of stored procedures is just as good as an API layered on top of the database in another language.)
  - bunderbunder 16d ago
    They are, but a REST API likely isn’t a good alternative because few RDBMSes speak REST. More likely they meant creating a database interface library in whatever programming language the application uses. And then have it talk to the DB through that library instead of scattering a fine mist of ad-hoc querying (or, shudder, active records) throughout your application.
    - 16d ago

999900000999 16d ago

I agree. The author doesn’t understand advanced database concepts and demands others stop them. Stored procedures ensure consistency. You know to add an item using one proc call. Not 20 different SQL calls. Just say you hate SQL. I do. For some god forsaken reason I got placed in a SQL heavy role a while back and was out within months. I both understand Postgres is the best solution for most DB use cases and I still reach for Firebase for my personal projects.

mamcx 15d ago

> Databases are a great piece of technology! Learning how to use them properly can have huge payoff in terms of business value generation. Certainly. Is a bit sad that the most well known are applications made for users (most RDBMS) instead of for developers, combined with a glorified DSL (SQL) that has never been made for make code at "large", not even at the scale of Lua or similar. I worked with FoxPro and do all the DB code was not only a joy, it makes too much sense! Thinking that interface with the network to store some meager byte on a disk is normal, that use terrible languages for data programming (not only SQL but JS, C# or whatever) is fine is baffling. But well. In terms of what is available: * RDBMS despite being applications (including sqlite) and not developer frameworks are plenty flexible and powerful for at least solve most data-oriented coding "DATA"-oriented is the key: Is wrong to say "this is backend, frontend, ui, etc" that is more a deployment issue and totally orthogonal. The most obvious example is the effect of N+1 queries: You are not thinking in what is data-oriented and using a incorrect programming language to solve what even an anemic DSL like SQL do much better. With the right mental model, is so easy to build a database schema with types, functions, procedures, views(that are so underused!) and such that make the clients DUMB. IS MUCH EASIER! And I work with ERPs, that are much more "complicated" than the median app. Let the DB do what it does well and the rest will be simplified by leaps and bounds. Even testing, just not commit the stupid mistake of use "mocks" that should be mocked and just spin ephemeral dbs/schemas. Is fast (even with PG) and will not create tons of churn because your regular langs is not ACID but the DB is.