Functional Programming in ... SQL?
Many real-world lots-of-business-value-providing systems use a relational database. (Even more of them should!) Often, that database is used as a dumb data store – nothing more, logically, than an ACID-compliant coordinator of multiple flat files (tables). We send it basic queries – sometimes even joining multiple tables in one query! – inserts, updates and deletes. But nothing that might strain its little brain. Often, this is a mistake: a modern relational database is the most sophisticated data-munging tool in our toolkit!
We should consider doing more work in the database itself. But that's not easy to code well. How can we make our more complex SQL code easier to reason about, more reliable, and more testable? How can we make the overall system simpler?
These are questions that in not-the-database contexts, we solve with functional programming techniques. Without expecting SQL to out-lambda Haskell, are there techniques that we can borrow from functional programming and apply to improving our SQL?
Outline/Structure of the Talk
I would like to convince you that you can and should build SQL code in a genuinely "capital-FP Functional" style.
Map/reduce is one of the paragon virtues of functional programming: generic data manipulation is what we do best, right? But is it possible that maybe databases are good at data-munging too?
In this talk, we'll cover:
- Why would we even consider putting code in the DB in the first place?
- How do we build a good mental model of how stuff we might do in SQL relates to stuff we'd otherwise right in ?
- How do we get our favourite FP benefits – reasoning about code, and composing small, re-usable pieces into larger, more capable wholes – from code that's written in SQL?
- How do we avoid the hideous spaghetti mess of imperative craziness that most large SQL codebases devolve into?
Spoilers: we write pure functions and we compose them.
I hope that after this talk you do more of your data-munging work directly in SQL.
I hope you consider defining an API of SQL functions/procedures, with function names, parameter and return types, rather than having your application send whatever SQL text it feels like dynamically generating over the wire.
Most importantly, I hope you implement each function in that API functionally: using an individual SQL statement, making use of neatly factored-out, referentially-transparent helper functions, to operate on its outputs and predicates in obvious, easy-to-reason-about ways.
I hope you consider property-based testing your database API.
Functional programmers of systems which use a database
Prerequisites for Attendees
This talk will assume a reasonable working knowledge of SQL. I will be using PostgreSQL to demonstrate techniques, and some techniques may not translate directly to other database systems, but users of other database systems should still be able to follow along without difficulty.