sql - Are Databases and Functional Programming at odds?

Question

I've been a web developer for some time now, and have recently started learning some functional programming. Like others, I've had some significant trouble apply many of these concepts to my professional work. For me, the primary reason for this is I see a conflict between between FP's goal of remaining stateless seems quite at odds with that fact that most web development work I've done has been heavily tied to databases, which are very data-centric.

One thing that made me a much more productive developer on the OOP side of things was the discovery of object-relational mappers like MyGeneration d00dads for .Net, Class::DBI for perl, ActiveRecord for ruby, etc. This allowed me to stay away from writing insert and select statements all day, and to focus on working with the data easily as objects. Of course, I could still write SQL queries when their power was needed, but otherwise it was abstracted nicely behind the scenes.

Now, turning to functional-programming, it seems like with many of the FP web frameworks like Links require writing a lot of boilerplate sql code, as in this example. Weblocks seems a little better, but it seems to use kind of an OOP model for working with data, and still requires code to be manually written for each table in your database as in this example. I suppose you use some code generation to write these mapping functions, but that seems decidedly un-lisp-like.

(Note I have not looked at Weblocks or Links extremely closely, I may just be misunderstanding how they are used).

So the question is, for the database access portions (which I believe are pretty large) of web application, or other development requiring interface with a sql database we seem to be forced down one of the following paths:

1. Don't Use Functional Programming
2. Access Data in an annoying, un-abstracted way that involves manually writing a lot of SQL or SQL-like code ala Links
3. Force our functional Language into a pseudo-OOP paradigm, thus removing some of the elegance and stability of true functional programming.

Clearly, none of these options seem ideal. Has found a way circumvent these issues? Is there really an even an issue here?

Note: I personally am most familiar with LISP on the FP front, so if you want to give any examples and know multiple FP languages, lisp would probably be the preferred language of choice

PS: For Issues specific to other aspects of web development see this question.

Answer 1

Coming at this from the perspective of a database person, I find that front end developers try too hard to find ways to make databases fit their model rather than consider the most effective ways to use database which are not object oriented or functional but relational and using set-theory. I have seen this generally result in poorly performing code. And further it creates code that is difficult to performance tune.

When considering database access there are three main considerations - data integrity (why all business rules should be enforced at the database level not through the user interface), performance, and security. SQL is written to manage the first two considerations more effectively than any front end language. Because it is specifically designed to do that. The task of a database is far different than the task of a user interface. Is it any wonder that the type of code that is most effective in managing the task is conceptually different?

And databases hold information critical to the survival of a company. Is is any wonder that businesses aren't willing to experiment with new methods when their survival is at stake. Heck many businesses are unwilling to even upgrade to new versions of their existing database. So there is in inherent conservatism in database design. And it is deliberately that way.

I wouldn't try to write T-SQL or use database design concepts to create your user-interface, why would you try to use your interface language and design concepts to access my database? Because you think SQL isn't fancy (or new) enough? Or you don't feel comfortable with it? Just because something doesn't fit the model you feel most comfortable with, doesn't mean it is bad or wrong. It means that it is different and probably different for a legitimate reason. You use a different tool for a different task.