Comment by bob1029 | Hacker News Reader

Comment by bob1029 | Hacker News Reader

FP is only part of the equation when working with meaningfully-complex systems. Having a clear data + relational model is the other big part.

I would strongly recommend reviewing the functional-relational programming model described in this paper - http://curtclifton.net/papers/MoseleyMarks06a.pdf

The overall theme goes something like: FP is for handling all your logic, and RP is for arranging all of your data. Some sort of relational programming model is the only way to practically represent a domain with high dimensionality.

For real-world applications, we asked ourselves: "Which programming paradigm already exhibits attributes of both FP and RP?" The most obvious answer we arrived at is SQL. Today, we use SQL as a FRP language for configuring our product. 100% of the domain logic is defined as SQL queries. 100% of the domain data lives in the database. The database is scoped per unit of work/session, so there are strong guarantees of determinism throughout.

Writing domain logic in SQL is paradise. Our tables are intentionally kept small, so we never have to worry about performance & indexing. CTEs and such are encouraged to make queries as human-friendly as possible. Some of our most incredibly complex procedural code areas were replaced with not even 500 characters worth of SQL text that a domain expert can directly understand.

Oh god, no! Please no!

Okay maybe it works for you, but I've worked on one of these systems for a financial engine with over 10m SQL LoC. This was a big product that was ~15 years old and used by dozens of companies having bespoke feature flags that changed significant parts of the calculation engine. Everyone except a couple grey beards who'd joined straight out of university left that place after a few years because of how insane it was to work on and we all became way too interested in good architecture design from that experience. My friends from that time who I still keep in touch with are almost entirely working on FP-based environments now.

When done poorly that is the result. And it is very easy to do poorly.

But I think the basic premise of combining functional and relational is valid. At the very least it avoids the object relational impedance mismatch.

Nearly every system I worked on that had significant business logic in SQL turned out to be a maintenance nightmare. Used to be a lot of this in the 90s or early 2000s. Where dB venders encouraged it for obvious reasons.

SQL isn't built with sensible decisions. Delete/update forget a where clause? Whoops. Lots of traps like that. Also it's not easily testable. Domain concepts don't always easily map.

Part of the reason is that SQL is simply a bad language (the JavaScript of databases).

Part of the reason is also the thinking and philosophy behind DBMSs as interactive systems. Especially with regards to code.

A function doesn't just exist. You create a function and it lives in the database until you alter it or drop it.

This creates a different (temporal?) impedance mismatch with standard code management tools like version control. The result is most often a maintenance nightmare.

A few points:

- I can understand why this might appear as hell to many. SQL is unwieldy and doesn't play nice with many tools we use to make life easier.

- I agree with the fundamental premise that a combination of functional and relational principles can be highly effective for data heavy scenarios

- i disagree that pure SQL is the solution to achieve this. If using MS SQL Server, there are great opportunities to leavrage the CLR and F# (with caveats because F# gets less love than C#). You can write functional logic once and use it both outside of the database and inside the database. PostgreSQL has extensions for other languages.

Does SQL CLR support .NET Core/.NET 5+ or just framework?

Unfortunately, for now, it is just .Net Framework, even on SQL Server on Linux. This is another caveat.

I am sure in the future, .Net Core and Standard will be supported.

SQL is not functional. But otherwise I support the desire for an FP and relational language.

> SQL is not functional

I agree that you are technically correct. It is simply declarative in nature. I've got a habit of conflating these terms. Functions aren't first-class citizens in SQL. We also have some UDFs that are impure (because the real world unfortunately exists).

I'd be perfectly happy to rename this a "Declarative-Relational" programming model if that sits better with everyone.

(Pure) SQL is a Set-theoretic language.

Reply on news.ycombinator.com