[TeMPOraL]

> Should I write this logic in the DB itself ?

Yes?

If it sounds impractical, it's because the whole industry got used to not learning databases beyond most basic SQL, and doing everything by hand in application code itself. But given how much of code in most applications is just ad-hoc reimplementation of databases, and then how much of the business logic is tied to data and not application-specific things, I can't help but wonder - maybe a better way would be to treat RDBMS as an application framework and have application itself be a thin UI layer on top?

On paper it definitely sounds like grouping concerns better.

[brulard]

While stored procedures/triggers etc. can be powerful, it has been taught for decades now that it is an antipattern to put business logic to the RDBMS (for more or less valid reasons). Some concerns I would have would be vendor lock-in and limits of the provided language.

[Tobani]

In very simple systems that makes sense. But as soon as your validation requires talking to a third party, or you have side effects like sending emails you have to suddenly move all that logic back out. You end up with system that isn't very easy to iterate on.

[Nextgrid]

You can model external system interactions with tables representing "mailboxes" - so for example if a DB stored procedure needs to call a third-party API to create a resource, it writes a row in the "outbox" table for that API, then application-level code picks that up, makes the API call, parses the response (extracts the required fields) and stores it in an "inbox" table so now the database has access to the response (and a trigger can run the remainder of the business process upon insertion of that row).

[Tobani]

Yes, but then you've removed parent comments' assertion that everything should be done by the RDBMS. And you've changed the contract of the action.

[TeMPOraL]

Surely some RDBMS has the ability to run REST queries, possibly via SQL by pretending it's a table or something.

I can imagine that working on a good day. I don't dare imagine error handling (though would love to look at examples).

Ultimately, it probably makes no sense to do everything in the database, but I still believe we're doing way too much in the application, and too little in the DB. Some of the logic really belongs to data (and needs to be duplicated for any program using the same data, or else...; probably why people don't like to share databases between programs).

And, at a higher level, I wonder how far we could go if we pushed all data-specific logic into the DB, and the rest (like REST calls) into dedicated components, and used a generic orchestrator to glue the parts together? What of the "application code" would remain then, and where would it sit?

[lloeki]

> treat RDBMS as an application framework and have application itself be a thin UI layer on top?

Stored procedures have been a thing. I've seen countless apps that had a thin VB UI and a MSSQL backend where most of the logic is implemented. Or, y'know, Access. Or spreadsheets even!

And before that AS/400&al.

But ORMs came in and the impedance mismatch is then too great. Splitting data wrangling across two completely differing points of views makes it extremely hard to reason about.