Hacker News Reader

Comment by StefanBatory | Hacker News Reader

May I ask question about this part?

"Push all your business logic into big long stored procedures/functions - don't be pulling the data back and munging it in some other language - make the database do the work!"

From my courses I had at university, I've been led to believe that the current trend is doing hexagonal architecture, as that allows for better modularisation of the project and helps keep code clean over many years with many software engineers coming in and out. As a part of that I've been taught that the only part you could trust then is your internal modules - and even database has to treated as an external source, whose only job is to pull data in and out. How does that work in what you're suggesting? Is it just a different way of approaching things that will work depending on what's your goal is?

I'm just curious about this as I'm trying to get myself to learn a bit more, just to clarify

View on HN

The model described in the parent comment is essentially using the database (in this case PostgreSQL, but any RDMS would do) as the hexagonal "core" in which adapters plug in to. This is a powerful pattern that works very well when you use the full features of the RDMS like constraints, triggers, views, etc. This does require "coupling" to the RDMS-specific features, which makes migrating to an alternative system difficult, but in practice this rarely happens if you choose a strong RDMS from the beginning.

You can certainly use the database as a "dumb storage" tool in the hexagonal architecture, that is, as just another adapter. But most of the time you'll end up re-creating RDMS features in poorly written/documented application code that has to interact with the database anyways. Why not just do it all in the database? With a RDMS core, hexagonal adapters can be pure functional components, making them much easier to reason about and maintain.

For more on this idea, and how to avoid pitfalls with the hexagonal pattern, I recommend reading Out of the Tar Pit [1]. It's a short but highly influential paper on "functional relational programming".

[1]: https://curtclifton.net/papers/MoseleyMarks06a.pdf

I see it, that makes sense! Right, I don't think that you'd have a reason to swap RDMS unless licencing issues come up, like with Oracle. Thank you for helping out.

> "Push all your business logic into big long stored procedures/functions - don't be pulling the data back and munging it in some other language - make the database do the work!"

This is one of the categories of opinions that I’ve heard, the proponents of which suggest that databases will typically be more efficient at querying and transforming data, since you’ll only need to transfer the end result over a network and will often avoid the N+1 problem altogether.

You probably don’t want some reporting or dashboard functionality in your app to have to pull tens or hundreds of thousands of rows to the back end, just because you have decided to iterate over the dataset and do some transformations there.

That said, I’ve worked in an app where the Java back end only called various stored procedures and displayed their results in tables and while it was blazingly fast, the developer experience was miserable compared to most other projects I’ve worked with - lots of tables with bad naming (symbol limits in that RDBMS to thank), badly commented (not) procedures with obscure flags, no way to step through anything with a debugger, no proper logging, no versioning or good CI tooling, no good tools for code navigation, no refactoring suggestions, no good tracing or metrics, nothing.

Sure, it might have just been a bad codebase, but it was worse than most of the ones where too much logic is in the back end, those just run badly, so I get the other category of opinions, which suggests that trying to use the DB for everything isn’t a walk in the park either.

There’s probably a good balance to be found and using tools in ways that both perform okay and don’t make the developer experience all that bad.

Thank you :)

Ofc I reserve the right to be wrong, just wanted to share my subjective experience, that there can be tradeoffs and there probably aren't any silver bullets.

For the most part, I think that you should put any mass/batch processing in the DB (just comment/version/test/deploy your code like you would on the back end, as best as you can with the tools available to you) and don't sweat too much about handling the CRUD operations in your back end, through whatever ORM you use or don't use (regular queries are also fine, as long as parametrized to prevent injection).

For complex schemas, a nice approach I've found is making one DB view per table/list/section of your front end, so you only need 1 DB call to load a particular component, otherwise the N+1 risk gets far greater ("Oh hey, I got this list of orders, but each other needs a delivery status, so I'll just iterate over those and fetch them for each item, whoops, the DB is spammed with requests.").

Good luck!

Not OP, but I think it's a valid approach.

You gain: Model consistency guaranteed by the database, your backend basically only acts as an external API for the database.

You lose: Modularity, makes it harder to swap out databases. Also, you have to write SQL for business logic which many developers are bad at or dislike or both.

I've seen a system running on this approach for ten years and it survived three generations of developers programming against this API. There's Python wx frontends, web frontends, Rust software, Java software, C software, etc. They all use the same database procedures for manipulating the model so it stays consistent. Postgres is (kinda, not very) heavy for small projects but it scales for medium up to large-ish projects (where it still scales but not as trivially). One downside I've seen in this project is that some developers were afraid to change the SQL procedures so they started to work around them instead of adding new ones or changing the existing ones. So in addition to your regular work horse programming language you also have to be pretty good at SQL.

That's interesting way of doing things, I'll admit. Thank you for helping out :)

With Pglite in a few years you'll be able to treat Postgres as a library, and merely allow selecting between in-process and remote via configuration.

The first job of a database is to be a data structure for persisting data, but you're allowed to extend said data structure in your own code. As long as you can come up with a way to keep all the code in version control, test it, etc., it's fine.

Reply on news.ycombinator.com