Ask HN: Why is building apps around stored procs frowned on? I'm loving it

There’s nothing wrong with it. Enterprise environments often do it so business rules and logic live in one place, so application code (in multiple languages) doesn’t have multiple implementations.

I think a few things came together to make programmers crap on stored procedures and putting business logic in the database:

- OOP, which typically treats relational databases as dumb backing store for objects.

- No enterprise experience, and weak RDBMS/SQL skills.

- Popular open source relational databases with weak and clunky languages and tools, such as MySQL. Compared to Oracle and SQL Server the low-end RDBMSs most programmers learn look hobbled.

- Unless you use Oracle or SQL Server or DB/2 you may have trouble debugging procedural code running in the database.

If this style works for you don’t worry what other programmers say or what you read online. No matter how you choose to build your application you will get people telling you that you made bad choices.

Don't be too sensitive. Someone will sneer at you whatever you do. If it works, satisfies the business case and is maintainable all is good.

Combine that with Postgraphile or PostgREST and you don't need to do any server code.

The hard thing is how to horizontally scale.

Scaling vertically and horizontally with relational databases got solved decades ago and continues to get better. Oracle and SQL Server run practically every huge enterprise database. Do you think those don’t scale?

Not sure about Oracle or MS SQL, I work with Postgres. Back when I worked with Oracle and MS SQL it was about one-few huge DB servers, not many smaller ones.

To scale the database you need replication, and replication makes it a distributed stateful system that might be harder to reason about than a classic server+database setup.

In comparison, scaling a standard stateless backend server that connects to one huge DB server is fairly easy.

If you have any guides to point me to I'd appreciate it.

The state comes from the data, not the procedural code implemented in the database.

Issues around replication happen with any distributed database. That doesn’t get worse with stored procedures, which don’t themselves have state.

It's just as hard, indeed. But you don't need to replicate as early with server+DB because your DB is focused on data transactions only. You can have a replica ready for a potential outage and that's all you need to care about for a long time.

When you run everything in your DB, replication issues will hit you much earlier.

Of course this all depends on what kind of app are you building. If it's an internal tool or something like that, you probably don't need to worry.

The stored procedures and views and triggers get replicated too.

I think you’re imagining that moving business logic into stored procedures will overload the database server sooner than having that in application code. I have never seen that. Instead you get fewer simultaneous connections and less data passed back and forth. Querying a compiled view or stored proc will often use fewer resources than parsing, planning, and optimizing SQL sent from application code. And the RDBMS can make better use of caching with business logic in the database.

Scaling and replication may present complications with any architecture, but those aren’t good reasons in my opinion to not put business logic into the RDBMS. Real enterprise-scale databases tend to do that and they actually have scaling and replication issues, not just “may happen someday” issues.

Completely agree; the issue I see with people who try putting logic in the DB ultimately to reject it is they try and bring their procedural programming paradigms (e.g. “for” loops and “if” statements) into the DB and that’s incredibly inefficient, requiring row-by-row looped fetches and ultimately causing their systems to grind to a halt and them to blame the concept of putting logic in the DB. If instead they were to embrace the declarative nature of the database and used efficient patterns such as statement triggers rather than row triggers, common table expressions (CTEs) rather than row-by-row looped processing, set-returning functions and joins rather than row-by-row function calls, etc then they would find that their code is both more expressive and often thousands of times more performant. As you say, embracing this model means less data transferred between cluster and client which can result in reduced load across the entire system without increasing complexity or having to worry about cache invalidation issues. And time spent learning databases is knowledge that will still be useful in a decade or two, it’s well worth investing into.

I completely agree with you. I think of relational databases as a fundamental programming skill. I have made a good career from RDBMS and SQL expertise, and that technology shows no signs of going away.

If your app works with data in the DB and nothing else, sure.

What if you have complex integrations of external services, need to serialize and parse huge JSON or XML, do processing on the data and send it back and forth, accept and trigger webhooks...? That's the workload I'm imagining would be easier to scale on a separate app server.

You use the best tool and write code at the appropriate layer. I don’t think anyone suggests writing the entire application in the database (though you can with Oracle).

UI and interfacing with external services will most likely prove simpler to implement in an application layer. Google and Stripe don’t have SDKs for Postgres.

If the application needs a relational database at all it makes sense to get the most value out of the RDBMS.

Postgres has extensions to make HTTP requests, to write JS (etc) code, and so on. Check out Supabase, the goal is exactly this - use Postgres as your whole application layer, Supabase as the access point.

The funny thing is that there was a time (at least with Microsoft) where you were supposedly doing it totally wrong if you didn't use stored procedures enough.

And I swear there was a time when every web developer knew that if you wanted a consistent, reliable layout for a home page, you sliced the design up into a table, and stayed away from that new CSS stuff because it was buggy and incompatible with different browsers.

The reality is that there are several very different general approaches that can work for any particular information system. Some of them will be more compatible with each other or with certain tools.

But I think that for the vast majority of applications, this comes down to trends more than any particular technical issue.

I mean, technically speaking there are an effectively infinite number of ways to organize and distribute the code and data that will given the same outputs from given inputs.

People use "best practices" as a shortcut to make it easier to judge things or decide how to approach a problem. I think one mark of a good engineer is their ability to adapt to different approaches. This actually requires more intelligence or at least energy because it means they might need to actually read code to decide if the approach makes sense.

But it will save people quite a lot of energy to simply say "we [always/never] use stored procedures here!" And also even for brilliant engineers, constantly trying to wrap your head around completely different approaches can be impractical.

So programming communities adopt their own worldviews. For the most part, it just means that you spend slightly more time on certain types of problems than the other group. But that group may not realize they are spending time on another thing. So there are trade-offs with every approach.

Which is not to say that some frameworks can't save you more time than others. One aspect of this is compatibility. The whole approach may get thrown off if you use stored procedures versus not, depending on the rest of the framework.

But I would suggest that there is usually just as much cultural preference as actual technical merit to these debates when you dig deep into the details.