8 ms·

Details. But sure. In truth the best way to do the data layer is to use stored procs with a generated binding at the application layer. This is absolutely safe

Details. But sure. In truth the best way to do the data layer is to use stored procs with a generated binding at the application layer. This is absolutely safe from injection, and is wicked fast as well.

FridgeSeal 3y ago
Having been on the team that inherited a project full on stored procedures (more than once): no thank you, not ever again. Opaque, difficult to debug, high risk to invoke, difficult to version and version-control properly. I’m sure they could be done “less worse”, but I doubt they can be done well.
- afavour 3y ago
  Holy crap yes. This thread has triggered a long lost memory from over a decade ago where everything, absolutely everything was done through stored procedure. Such a wild, wild waste of time.
- re-thc 3y ago
  > Opaque, difficult to debug, high risk to invoke, difficult to version and version-control properly. Traditionally, maybe. With databases like Neon (postgresql) and Planetscale (mysql) supporting easy branching / snapshot it's at least made this a lot nicer.
  - FridgeSeal 3y ago
    That might be true, but that’s like saying “now that my mechanic can repair my car for free, it’s way less worry to replace my radiator by removing it with a chainsaw”. The solution to stored procs being awful isn’t making a whole branch just to see what it does, it’s fixing the problems with stored procedures at the root level.
    - grugagag 3y ago
      Though im on the fence with stored procs and have seen complicated messes that make it depressingly no fun to work with I also had very good experiences with systems based around stored procs, not in an abusive dogmatic way. The usual answer I’m going to invoke here is that it depends how the tool is used. Any tool, methodology, philosophy can be borked in various ways, that experience traumatize people that they prefer to move on. Of course some tools are clearly worse or less useful than others but you can find some consensus if you know where to look.
- lyu07282 3y ago
  I would add impossible to test to that list. And the postgraphile people think it's the new hot stuff smh
- noisy_boy 3y ago
  Thanks for echoing my pain. I am paying for the bad decisions taken by a bloke who only knew SQL and used that hammer to bang on every nail he could find. Everytime someone asks why the result of the stored proc is the way it is, I need to manually run the steps one by one until I find the point where an expected row failed a condition and dropped off. And have extra fun wrapping those statements with declarations incase you are heavily using declared variables in those statements. To top it off, if you are troubleshooting in an environment where you don't have insert/update permissions, you day just became amazing. Fuck you to those who use stored procs without strong justification (like high volume data processing on database side where it totally makes sense). By version control, I assume you mean the inability to use different versions simultaneously without hacks, unlike a library built using Java where you get to pick and choose the version you want to use. Because of you mean version control of stored proc sources, that would be just like a normal git asset.
  - Canada 3y ago
    I like stored procedures, or I guess to be more specific functions in postgres, but they have to be done in a really specific way: 1) It's all or nothing. If there's functions then they better feel like a complete high level API, enforcing the consistency of data, perfectly handling locking, etc. If there's tons of complex SQL in the app and then also a some functions then it's better to have no functions at all. 2) They need to have a consistent interface, such as always returning an error flag, error message if applicable. Functions must never surprise the caller. It either succeeds and the caller commits, or fail and the caller rolls back and knows why. 3) No monkey business with triggers. They can maintain updated_at columns, or maybe create some audit trails, but anything beyond that will make behavior surprising to callers and that is the worst. As for version control it needs to be maintained as a separate application, which the server depends on having certain versions of. Even if you don't use functions you have to worry about schema versions and you can't always run two versions at the same time or roll back schema changes easily as a practical matter.
- westurner 3y ago
  Stored procedure: https://en.wikipedia.org/wiki/Stored_procedure https://en.wikipedia.org/wiki/Stored_procedure Are there SQL migration tools like Alembic and South that also version and sync Stored Procedures? (Alembic on "Replaceable objects" and schema: https://alembic.sqlalchemy.org/en/latest/cookbook.html#replaceable-objects https://alembic.sqlalchemy.org/en/latest/cookbook.html#repla... , alembic_utils: https://github.com/olirice/alembic_utils https://github.com/olirice/alembic_utils ) From "What ORMs have taught me: just learn SQL (2014)" https://news.ycombinator.com/item?id=15949610 https://news.ycombinator.com/item?id=15949610 : > ORMs: > - Are maintainable by a team. "Oh, because that seemed faster at the time." > - Are unit tested: eventually we end up creating at least structs or objects anyway, and then that needs to be the same everywhere, and then the abstraction is wrong because "everything should just be functional like SQL" until we need to decide what you called "the_initializer2". > - Can make it very easy to create maintainable test fixtures which raise exceptions when the schema has changed but the test data hasn't. ORMs may help catch incomplete migrations; for example when every reference to a renamed column hasn't been updated, or worse when foreign key relations changed breakingly. django_rest_assured generates many tests for the models (SQL)/views/API views in a very DRY way from ORM schema and view/controller/route registrations, but is not as fast as e.g. FastAPI w/ SQLalchemy or django-ninja.
paulryanrogers 3y ago
Stored procedures often increase CPU load of the DB instances, deploys can be more challenging, and security is only better if one takes care how the procedure is authorized or delegated. They can save some network round trips, and unify interfaces if there are a lot of different stacks sharing one data store. Though it's not universally better.
- JohnBooty 3y ago
  Stored procedures often increase CPU load of the DB instances This is of course true if you're doing a bunch of computational work in your stored procedure (as opposed to just doing pure CRUD stuff) but I'm struggling to think of a real world situation where this might be the case. Can you name some examples you've encountered? (I'm not doubting you, I'm just curious)
  - paulryanrogers 3y ago
    I did some complex expiration calculations in a stored function, it cut down on round trips and kept the app simpler. Though it did cause some modestly higher CPU load. I've also seen MVs cause CPU spikes where there is a lot of SELECT massaging going on. Even without the MVs themselves certain functions like REGEXP stuff can impact performance. Language of the PL matters too, if you've got choices like Python.
    - JohnBooty 3y ago
      Sorry, MVs? I'm probably going to feel like an idiot but I'm not recognizing this acronym. Also thanks for sharing your example, much appreciated
      - noisy_boy 3y ago
        Materialised Views.
        JohnBooty 3y ago
mmcclure 3y ago
I (softly) disagree with stored procs being definitively the "best way to do the data layer." Stored procs are an extremely powerful tool to have in your belt, but lord have mercy do they have their own tradeoffs.
- JohnMakin 3y ago
  some of the worst issues I’ve ever dealt with had one of these as a root cause
- xarope 3y ago
  As usual there's a time and place. What I've found is that devs tend to be able to write "OKish" SQLs, but fall over when trying to optimize them, so the DB side can then take over and write optimized stored procs, which can have a separate cadence of code updates and version control compared to the backend team.
preseinger 3y ago
an application (client) should be free to query a database (server) directly a stored procedure is an implicit dependency between client and server, fine as an optimization, but definitely not what you want to do by default
- grugagag 3y ago
  For CRUD stuff if you don’t care about execution plan cache and other optimizations it could save you some time sure to query directly. For chunks of code that transactions/procesing large data directly on the server I’d reach out for stored procedures without thinking too much
  - preseinger 3y ago
    it's not like stored procedures are inherently faster than normal queries, right? as long as you're not doing anything dumb like connection-per-request, query caching should work the same
    - JohnBooty 3y ago
      it's not like stored procedures are inherently faster than normal queries, right? They're doing the same amount of "work" with regards to finding/creating/updating/deleting rows. But you (potentially) avoid shuffling all of that data back and forth between your DB server and your app. This can be an orders-of-magnitude benefit if we are describing a multistep process that would involve lots of round trips, and/or involves a lot of rows that would have to be piped over the network from the DB server to the app. Suppose that when I create an order, I want to perform some other actions. I want to update inventory levels, calculate the user's new rewards balance, blah blah blah. I could do all of that in a single stored procedure without bouncing all of that data back and forth between DB and client in a multistep process. That could matter a lot in terms of scalability, because now maybe I only have to hold that transaction lock for 20ms instead of 200ms while I make all of those round trips. There are a lot of obvious downsides to using stored procedures, but they can be very effective as well. I would not use them as a default choice for most things but they can be a valuable optimization.
edandersen 3y ago
If your default stance in 2023 is that you should start with stored procedures, you are doing something very wrong and you are a risk to the survival of your business.
- grugagag 3y ago
  Really depends on what you need, depends on size, team structure, budget, and so many other things.
- JohnBooty 3y ago
  I'd say that any kind of absolutist "default stance" is suspect.
webprofusion 3y ago
False. I've developed enormous systems fully using stored procs for data layer and it's absolutely not the "best way to do the data layer". Next you'll be saying triggers are a good idea. Yes they can be fast, and they're controlled by the DBA, that's why you'd choose them. Injection is not an issue in modern data access layers, that's a 1999 problem.

FridgeSeal 3y ago

Having been on the team that inherited a project full on stored procedures (more than once): no thank you, not ever again. Opaque, difficult to debug, high risk to invoke, difficult to version and version-control properly. I’m sure they could be done “less worse”, but I doubt they can be done well.

afavour 3y ago
Holy crap yes. This thread has triggered a long lost memory from over a decade ago where everything, absolutely everything was done through stored procedure. Such a wild, wild waste of time.
re-thc 3y ago
> Opaque, difficult to debug, high risk to invoke, difficult to version and version-control properly. Traditionally, maybe. With databases like Neon (postgresql) and Planetscale (mysql) supporting easy branching / snapshot it's at least made this a lot nicer.
- FridgeSeal 3y ago
  That might be true, but that’s like saying “now that my mechanic can repair my car for free, it’s way less worry to replace my radiator by removing it with a chainsaw”. The solution to stored procs being awful isn’t making a whole branch just to see what it does, it’s fixing the problems with stored procedures at the root level.
  - grugagag 3y ago
    Though im on the fence with stored procs and have seen complicated messes that make it depressingly no fun to work with I also had very good experiences with systems based around stored procs, not in an abusive dogmatic way. The usual answer I’m going to invoke here is that it depends how the tool is used. Any tool, methodology, philosophy can be borked in various ways, that experience traumatize people that they prefer to move on. Of course some tools are clearly worse or less useful than others but you can find some consensus if you know where to look.
lyu07282 3y ago
I would add impossible to test to that list. And the postgraphile people think it's the new hot stuff smh
noisy_boy 3y ago
Thanks for echoing my pain. I am paying for the bad decisions taken by a bloke who only knew SQL and used that hammer to bang on every nail he could find. Everytime someone asks why the result of the stored proc is the way it is, I need to manually run the steps one by one until I find the point where an expected row failed a condition and dropped off. And have extra fun wrapping those statements with declarations incase you are heavily using declared variables in those statements. To top it off, if you are troubleshooting in an environment where you don't have insert/update permissions, you day just became amazing. Fuck you to those who use stored procs without strong justification (like high volume data processing on database side where it totally makes sense). By version control, I assume you mean the inability to use different versions simultaneously without hacks, unlike a library built using Java where you get to pick and choose the version you want to use. Because of you mean version control of stored proc sources, that would be just like a normal git asset.
- Canada 3y ago
  I like stored procedures, or I guess to be more specific functions in postgres, but they have to be done in a really specific way: 1) It's all or nothing. If there's functions then they better feel like a complete high level API, enforcing the consistency of data, perfectly handling locking, etc. If there's tons of complex SQL in the app and then also a some functions then it's better to have no functions at all. 2) They need to have a consistent interface, such as always returning an error flag, error message if applicable. Functions must never surprise the caller. It either succeeds and the caller commits, or fail and the caller rolls back and knows why. 3) No monkey business with triggers. They can maintain updated_at columns, or maybe create some audit trails, but anything beyond that will make behavior surprising to callers and that is the worst. As for version control it needs to be maintained as a separate application, which the server depends on having certain versions of. Even if you don't use functions you have to worry about schema versions and you can't always run two versions at the same time or roll back schema changes easily as a practical matter.
westurner 3y ago
Stored procedure: https://en.wikipedia.org/wiki/Stored_procedure https://en.wikipedia.org/wiki/Stored_procedure Are there SQL migration tools like Alembic and South that also version and sync Stored Procedures? (Alembic on "Replaceable objects" and schema: https://alembic.sqlalchemy.org/en/latest/cookbook.html#replaceable-objects https://alembic.sqlalchemy.org/en/latest/cookbook.html#repla... , alembic_utils: https://github.com/olirice/alembic_utils https://github.com/olirice/alembic_utils ) From "What ORMs have taught me: just learn SQL (2014)" https://news.ycombinator.com/item?id=15949610 https://news.ycombinator.com/item?id=15949610 : > ORMs: > - Are maintainable by a team. "Oh, because that seemed faster at the time." > - Are unit tested: eventually we end up creating at least structs or objects anyway, and then that needs to be the same everywhere, and then the abstraction is wrong because "everything should just be functional like SQL" until we need to decide what you called "the_initializer2". > - Can make it very easy to create maintainable test fixtures which raise exceptions when the schema has changed but the test data hasn't. ORMs may help catch incomplete migrations; for example when every reference to a renamed column hasn't been updated, or worse when foreign key relations changed breakingly. django_rest_assured generates many tests for the models (SQL)/views/API views in a very DRY way from ORM schema and view/controller/route registrations, but is not as fast as e.g. FastAPI w/ SQLalchemy or django-ninja.

paulryanrogers 3y ago

Stored procedures often increase CPU load of the DB instances, deploys can be more challenging, and security is only better if one takes care how the procedure is authorized or delegated. They can save some network round trips, and unify interfaces if there are a lot of different stacks sharing one data store. Though it's not universally better.

JohnBooty 3y ago
Stored procedures often increase CPU load of the DB instances This is of course true if you're doing a bunch of computational work in your stored procedure (as opposed to just doing pure CRUD stuff) but I'm struggling to think of a real world situation where this might be the case. Can you name some examples you've encountered? (I'm not doubting you, I'm just curious)
- paulryanrogers 3y ago
  I did some complex expiration calculations in a stored function, it cut down on round trips and kept the app simpler. Though it did cause some modestly higher CPU load. I've also seen MVs cause CPU spikes where there is a lot of SELECT massaging going on. Even without the MVs themselves certain functions like REGEXP stuff can impact performance. Language of the PL matters too, if you've got choices like Python.
  - JohnBooty 3y ago
    Sorry, MVs? I'm probably going to feel like an idiot but I'm not recognizing this acronym. Also thanks for sharing your example, much appreciated
    - noisy_boy 3y ago
      Materialised Views.
      - JohnBooty 3y ago

mmcclure 3y ago

I (softly) disagree with stored procs being definitively the "best way to do the data layer." Stored procs are an extremely powerful tool to have in your belt, but lord have mercy do they have their own tradeoffs.

JohnMakin 3y ago
some of the worst issues I’ve ever dealt with had one of these as a root cause
xarope 3y ago
As usual there's a time and place. What I've found is that devs tend to be able to write "OKish" SQLs, but fall over when trying to optimize them, so the DB side can then take over and write optimized stored procs, which can have a separate cadence of code updates and version control compared to the backend team.

preseinger 3y ago

an application (client) should be free to query a database (server) directly a stored procedure is an implicit dependency between client and server, fine as an optimization, but definitely not what you want to do by default

grugagag 3y ago
For CRUD stuff if you don’t care about execution plan cache and other optimizations it could save you some time sure to query directly. For chunks of code that transactions/procesing large data directly on the server I’d reach out for stored procedures without thinking too much
- preseinger 3y ago
  it's not like stored procedures are inherently faster than normal queries, right? as long as you're not doing anything dumb like connection-per-request, query caching should work the same
  - JohnBooty 3y ago
    it's not like stored procedures are inherently faster than normal queries, right? They're doing the same amount of "work" with regards to finding/creating/updating/deleting rows. But you (potentially) avoid shuffling all of that data back and forth between your DB server and your app. This can be an orders-of-magnitude benefit if we are describing a multistep process that would involve lots of round trips, and/or involves a lot of rows that would have to be piped over the network from the DB server to the app. Suppose that when I create an order, I want to perform some other actions. I want to update inventory levels, calculate the user's new rewards balance, blah blah blah. I could do all of that in a single stored procedure without bouncing all of that data back and forth between DB and client in a multistep process. That could matter a lot in terms of scalability, because now maybe I only have to hold that transaction lock for 20ms instead of 200ms while I make all of those round trips. There are a lot of obvious downsides to using stored procedures, but they can be very effective as well. I would not use them as a default choice for most things but they can be a valuable optimization.

edandersen 3y ago

If your default stance in 2023 is that you should start with stored procedures, you are doing something very wrong and you are a risk to the survival of your business.

grugagag 3y ago
Really depends on what you need, depends on size, team structure, budget, and so many other things.
JohnBooty 3y ago
I'd say that any kind of absolutist "default stance" is suspect.

webprofusion 3y ago

False. I've developed enormous systems fully using stored procs for data layer and it's absolutely not the "best way to do the data layer". Next you'll be saying triggers are a good idea. Yes they can be fast, and they're controlled by the DBA, that's why you'd choose them. Injection is not an issue in modern data access layers, that's a 1999 problem.