FR version is available. Content is displayed in original English for accuracy.
Advertisement
Advertisement
⚡ Community Insights
Discussion Sentiment
85% Positive
Analyzed from 1254 words in the discussion.
Trending Topics
#stored#procedures#sql#data#logic#database#queries#procedure#code#api
Discussion (28 Comments)Read Original on HackerNews
> 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.
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.
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.
That does not mean I’d let the queries devolve into chaos. Just that I’d do the query management and change control in a way that’s more pragmatic in light of other realities.
(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.)
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.
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!
The ancient wisdom which advocated for stored procedures, which modern developers find distasteful, were really advocating for microservices close to your data, which encapsulated security, business logic, and data persistence so that multiple consumers could share the same data without repeating the logic and code.
The fact that some people write those microservices in PL/SQL and some in JavaScript doesn't change the relevance of the encapsulation.
I’ve mostly moved on to storing queries as *.sql files in the application’s repository. You still get the good query caching and predictable plans. But you also get some other nice perks, like queries and the code that uses it being versioned together, and making it easy for developers to test queries in a SQL console. Most editors even have plugins that give you autocompletion and basic error checking in exchange for a connection string to the dev database.
That latter bit is why I don’t love inline SQL in string literals or ORMs’ querying DSLs. Both discourage tinkering with queries to observe how they work. I believe that’s a major reason why it’s so common for developers to commit awful performance sins like computing aggregations on the client side. It’s hard to expect people to get comfortable with SQL window functions when the project’s database interaction is set up in a way that actively discourages doing so.
I did it with production Scala apps over a decade ago. I even built sproc TDD test suites.
But... over the years, I've seen push to sprocs where the logic was never version controlled or subject to the same level of testing the regular app source code was. The DBA folks had total control and were treated as a separate class of engineer than mere 'app' engineers, which caused all sorts of problems...
additionally the logic is very far away from the data in many cases, obfuscated through layers of data modelling.
If there was a way to bring these closer, that would be nice.