We need to stop using Stored Procedures | Hacker News Reader

We need to stop using Stored Procedures

heffree.dev

17 pointssnikolaev32 comments

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.

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.

For one example, I might choose against stored procedures when I’m at an organization with internal policies or a devops setup that makes schema migrations costly and I expect the table and indexing structure to change less frequently than the queries.

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

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.

The client of the REST API is not an RDBMS. The REST API serves application clients - may be browsers, may be other services. The REST API alone talks to the database.

An API that serves only backend applications could be implemented in stored procedures instead of REST or GRPC. It could also be implemented in SOAP, CORBA, DCOM and other fossils, but no one is doing that for new applications.

Because SQL is a beautiful declarative language, and a disgusting imperative language.

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.

Compared to any modern language, the facilities are primitive.

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.

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

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

I think stored procedures are just strong typed "serverless lambdas" which runs really, really close do your data storage.

Yes, this!

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.

But PL/SQL is a horrifying language. You have barely any facilities for modularity, encapsulation or composition. Every procedure is just one name in a huge flat name space. Development tools are primitive. The language is absurdly verbose - it was state of the art in the 1970s when COBOL reigned supreme.

This is an implementation problem, you can introduce a JIT'd language plus some "container" with "versioned deployments" in modern DB clusters.

That's the theory. I never ran across a project that used stored procedures which didn't have a code base split between stored procedures and whatever server language, with no rhyme or reason for what goes where. It's just a maintenance headache.

This is an opinion where the author is obviously coming from an application developer’s bias. If you’re a data engineer you love the idea of stored procedures. A data engineer is often more comfortable putting logic closer to the data, especially for bulk transformations, set-based processing, ETL/ELT, data quality rules, and involving large volumes of data. Doing this in an application just to process them would be inefficient.

I used to be pro-sproc, but I agree that making queries part of the database schema can be burdensome and creates some development friction that I’d rather avoid.

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.

Just learn how to use Liquibase (or flyway, or... ) in your git repo and CICD pipeline deployments alongside your code.

I did it with production Scala apps over a decade ago. I even built sproc TDD test suites.

Besides CI, what benefits were you getting from writing the logic as a stored procedure?

I haven't looked in to this in ... decades, but I seem to remember stored procs executed measurably faster 20+ years ago. That was one reason a project I was on pushed them. And the benchmarks they showed... it wasn't unreasonable. But we have faster networks, more memory, ssd, etc - I'm unsure if speed is a big reason any more. IIRC cached execution plans was a big win back then, but may not be as a win today. I'm thinking about sybase and mssql from the late 90s when I was first getting in to this topic.

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

I have this question as well .. granted there are a few targeted cases where SPs make sense, I generally agree with the headache perspective of SPs - such as: the business logic that inevitably ends up in SPs - how do you go about unit testing that in isolation, and particularly from a behavioral standpoint?

I am uneasy about databases being used in general how they are today. Nobody writes SQL to read and write records directly. it's all pumped through business logic. Then you write a stored procedure to update x-on-y and the business logic breaks, in a scenario that is impossible for the business logic to produce.

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.

(In our on-prem SQL server environment) We expose stored procedures to web developers like an API contract to ensure a level of integrity and robustness in how they interact with the database. We find SQL knowledge among web developers these days is a toss up and I'd rather have the DBA design the schema and provide stored procedures.

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.

Maybe they’re not properly called sprocs, but there’s at least one good use for logic stored in the database: data retention. I like CREATE EVENT / pg_cron for trimming append-only tables when the data has aged out.

Tell that to Spacetime DB which achieves amazing performance by making everything a stored procedure!

Absolutely!

That article misses the #1 reason to use stored procedures - putting the code right by the data can be a massive performance boost. Like, orders of magnitude faster than travelling over the network.

Apparently “we” need to learn how to use them properly.

20 years doing this, including tooling that parses stored procs out of systems nobody fully understands anymore, and I don't think sprocs are the problem people think they are. Where they go wrong is time, not technology.

A well-versioned, well-owned sproc estate today can still be unreadable in 10-15 years, not because the language is bad, but because nothing tracks the drift: the author leaves, the ticket explaining a weird CASE branch gets closed and forgotten, someone patches around a bug three teams away, and now you've got T-SQL nobody currently employed fully understands, with git blame pointing at people who don't work there and comments referencing tickets that don't resolve. That's not a stored-procedure problem, it's an "institutional knowledge decays and nothing makes that visible" problem. It happens to application code too, just slower, since app code at least throws stack traces.

The thing that makes sprocs worse: they stay syntactically correct and keep answering queries even after the business meaning underneath drifts, because there's no compiler error for "this no longer matches reality." I regularly find procs whose own header comment describes something the code three lines below no longer does. Nothing broke. It just quietly returned a slightly wrong number until someone looked.

Reply on news.ycombinator.com