Functional Programming in…SQL? • Sam Roberton • YOW! 2019

This presentation was recorded at YOW! 2019. #GOTOcon #YOW
yowcon.com
Sam Roberton - Director of Engineering at Criteria Corp ‪@SamRoberton‬
RESOURCES
/ sam-roberton-5030b513b
/ sroberton
ABSTRACT
Many real-world lots-of-business-value-providing systems use a #RelationalDB. (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? [...]
RECOMMENDED BOOKS
Charity Majors, Liz Fong-Jones & George Miranda • Observability Engineering • amzn.to/38scbma
Kelly Shortridge & Aaron Rinehart • Security Chaos Engineering • www.verica.io/sce-book
Nora Jones & Casey Rosenthal • Chaos Engineering • amzn.to/3hUmuAH
Mikolaj Pawlikowski • Chaos Engineering • amzn.to/2SQ5Olf
Russ Miles • Learning Chaos Engineering • amzn.to/3hCiUe8
/ gotocon
/ goto-
/ gotoconferences
#SQL #FunctionalProgramming #SamRoberton #YOWcon
Looking for a unique learning experience?
Attend the next GOTO conference near you! Get your ticket at gotopia.tech
Sign up for updates and specials at gotopia.tech/newsletter
SUBSCRIBE TO OUR CHANNEL - new videos posted almost daily.
th-cam.com/users/GotoConf...

[@RoamingAdhocrat]

I have always wanted to learn about SQL from a man shouting at me through a drainpipe
(this talk is very relevant to my interests, audio issues aside, thanks for uploading it!)

[@Tony-dp1rl]

Nice little talk. In MS SQL, the lateral joins are CROSS APPLY and OUTER APPLY. Also, Functions in MS SQL are automatically deterministic/pure if you don't use any nondeterministic functions from inside them (like GETDATE etc.). Memoization with a temp table would have been a great addition to the talk!

[@PaulSebastianM]

Totally understood. You need to know all your tools. And doing more things in SQL can lead to higher performance and better testability if done in a functional way.

[@allanwind295]

The counter point is the (singular) database is the least scalable tier. If you need a giant amount of data ordered maybe do that sort on the client side. It's self-evident that global variables are bad in code. In a database all (table) data is system global which is way worse. I have written my share of stored functions and procedures and it's a pretty hostile dev environment (edit/create function/run cycle, there is no debugger so it's back to printf, there is no good version control, error messages can be confusing, your program correctness may depend on state of your data so your function may break if you insert a row in a random table 3 months later, testing is primitive and you have to setup a database so it's all functional testing). Oh... yeah, and I love PostgreSQL ;-)

[@moestietabarnak]

duh ! giant amount of data sorted CLIENT side ? adding the huge delay in data transfer from the server, when you can ask the server to do it locally efficiently with direct storage access?!
OR just create an index that do that continually for you!

[@allanwind295]

@@moestietabarnak You missed the point entirely, that the giant amount of data is needed on the client side. You take the huge delay and data transfer either way, and you might as well just take the cpu overhead on the client side where you have n nodes vs the database where you may have 1 (optimized for i/o not compute). You can of course argue that this crazy but machine learning on giant data sets doesn't happen in the database but on the client side side of that equation. Indexing doesn't come free, you take that overhead on every write. If you modify or delete data then you spend time on the server that wasted comparing to deferring the sort till when the data is needed.