[dventimihasura]

One way to have tighter integration between the database and the application logic is to abandon the application code altogether. Express the application logic right in the database using (custom) data types, domains, defaults, constraints, views, triggers, and procedures. Then you can delete much or all of the "application tier", delete the ORM, and simplify the entire stack. That's one way to avoid having "the database [feel] very separate from the rest of the project."

[cwbriscoe]

I agree with everything you said except putting business logic in procedures. That should be in application code instead in my experience. I have worked with too many vendors that put all their business logic in the DB. That always makes things more difficult for maintenance IMO. It may be easy to get something up and running for the first time, but it makes things 10x more difficult to change in the future.

[dventimihasura]

That depends. I worked with customers 25 years ago who put loads of business logic in very long and involved T-SQL stored procedures in MSSQL or PL/SQL stored procedures in Oracle. That may have made maintenance more difficult for the database developers who wrote them. It did make maintenance more difficult for the Java and .Net developers when they had to look at them, but that may have been a skill issue. These days, between (custom) data types, domains, defaults, constraints, views, materialized views, and triggers, I think there's more opportunity to regard "stored procedures" as a measure-of-last-resort. When that's invoked, I think application of the "Single Responsibility Principle" can take you a long way.

[collyw]

This was the way things were done 20 years ago as far as I understand. Shame I mostly missed that (MySQL was flavor of the day, being free and fast and throwing a way a load of features, only to be repeated to an even greater extent by Mongo a decade later). I could see a lot of benefits to working like that.

[dventimihasura]

That's a good point about MySQL. PostgreSQL wasn't quite ready for primetime at that time, and Oracle and MSSQL aren't free. That left MySQL to fill the gap, but it's not really built for this style of development.

[panstromek]

This make sense to me logically, but whenever I try to actually do it, it becomes a disaster.

[dventimihasura]

Fair. I've only ever had success with this strategy as a solo hacker. It was once claimed to me that this would be a disaster on large teams and I had to admit that I had no evidence one way or the other. Don't know if that resonates with your experience. Just anecdotal.

[TeaBrain]

This idea is largely regarded as making the codebase less maintainable since it can be more difficult to test discrete logical units of the SQL code than doing so for the application code.

[dventimihasura]

I'm aware of that belief, but I do not share it.

[mrguyorama]

Sounds like we should be adding good testing frameworks to SQL then

[dventimihasura]

pgTAP is pretty good for PostgreSQL, but I don't know what if any options are available for other databases.

https://pgtap.org/

[SoftTalker]

This is what I like to do.

[sethammons]

application logic needs to live with application code in source control. I've not seen a solution that leverages the database the way you suggest and achieves that. It leads to people yeeting things into production and hoping they work because half the logic is not in front of them

[dventimihasura]

The customers I work with tend to use database migration tools like Flyway or Liquibase, have their DDL in source control, run tests, and have fairly conventional CI/CD. As I said above, I've never personally worked on a large team doing this, but our customers seem to make it work.

[SoftTalker]

Absolutely no reason you cannot keep your application logic in source control with this approach.