Are Databases and Functional Programming at odds?

Question

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.

First of all, I would not say that CLOS (Common Lisp Object System) is "pseudo-OO". It is first class OO.

Second, I believe that you should use the paradigm that fits your needs.

You cannot statelessly store data, while a function is flow of data and does not really need state.

If you have several needs intermixed, mix your paradigms. Do not restrict yourself to only using the lower right corner of your toolbox.

You should look at the paper "Out of the Tar Pit" by Ben Moseley and Peter Marks, available here: "Out of the Tar Pit" (Feb. 6, 2006)

It is a modern classic which details a programming paradigm/system called Functional-Relational Programming. While not directly relating to databases, it discusses how to isolate interactions with the outside world (databases, for example) from the functional core of a system.

The paper also discusses how to implement a system where the internal state of the application is defined and modified using a relational algebra, which obviously is related to relational databases.

This paper will not give an an exact answer to how to integrate databases and functional programming, but it will help you design a system to minimize the problem.

1. Functional languages do not have the goal to remain stateless, they have the goal to make management of state explicit. For instance, in Haskell, you can consider the State monad as the heart of "normal" state and the IO monad a representation of state which must exist outside of the program. Both of these monads allow you to (a) explicitly represent stateful actions and (b) build stateful actions by composing them using referentially transparent tools.

2. You reference a number of ORMs, which, per their name, abstract databases as sets of objects. Truely, this is not what the information in a relational database represents! Per its name, it represents relational data. SQL is an algebra (language) for handling relationships on a relational data set and is actually quite "functional" itself. I bring this up so as to consider that (a) ORMs are not the only way to map database information, (b) that SQL is actually a pretty nice language for some database designs, and (c) that functional languages often have relational algebra mappings which expose the power of SQL in an idiomatic (and in the case of Haskell, typechecked) fashion.

I would say most lisps are a poor man's functional language. It's fully capable of being used according to modern functional practices, but since it doesn't require them the community is less likely to use them. This leads to a mixture of methods which can be highly useful but certainly obscures how pure functional interfaces can still use databases meaningfully.

I don't think the stateless nature of fp languages is a problem with connecting to databases. Lisp is a non-pure functional programming language so it shouldn't have any problem dealing with state. And pure functional programming languages like Haskell have ways of dealing with input and output that can be applied to using databases.

From your question it seems like your main problem lies in finding a good way to abstract away the record-based data you get back from your database into something that is lisp-y (lisp-ish?) without having to write a lot of SQL code. This seems more like a problem with the tooling/libraries than a problem with the language paradigm. If you want to do pure FP maybe lisp isn't the right language for you. Common lisp seems more about integrating good ideas from oo, fp and other paradigms than about pure fp. Maybe you should be using Erlang or Haskell if you want to go the pure FP route.

I do think the 'pseudo-oo' ideas in lisp have their merit too. You might want to try them out. If they don't fit the way you want to work with your data you could try creating a layer on top of Weblocks that allows you to work with your data the way you want. This might be easier than writing everything yourself.

Disclaimer: I'm not a Lisp expert. I'm mostly interested in programming languages and have been playing with Lisp/CLOS, Scheme, Erlang, Python and a bit of Ruby. In daily programming life I'm still forced to use C#.

If your database doesn't destroy information, then you can work with it in a functional manner consistent with "pure functional" programming values by working in functions of the entire database as a value.

If at time T the database states that "Bob likes Suzie", and you had a function likes which accepted a database and a liker, then so long as you can recover the database at time T you have a pure functional program that involves a database. e.g.

# Start: Time T
likes(db, "Bob")
=> "Suzie"
# Change who bob likes
...
likes(db "Bob")
=> "Alice"
# Recover the database from T
db = getDb(T)
likes(db, "Bob")
=> "Suzie"

To do this you can't ever throw away information you might use (which in all practicality means you cannot throw away information), so your storage needs will increase monotonically. But you can start to work with your database as a linear series of discrete values, where subsequent values are related to the prior ones through transactions.

This is the major idea behind Datomic, for example.