SBTB 2015: Rob Norris, Programs as Values: JDBC Programming with Doobie
Recording: SBTB 2015: Rob Norris, Programs as Values: JDBC Programming with Doobie
all right thanks thanks for coming it's it's it's a really awesome conference I've been having a really good time and I've met a lot of new people and everybody's awesome and it's it's just a lot of fun um oh let me do this sweet okay so my name is Rob uh I work for the Gemini Observatory so I write scholar programs uh that help scientists use this machine uh it's on top of a mountain in Hawaii there's another one in Chile dick yes what was the laser is that yes yes yeah we have we have space lasers we did uh artificial guide star I can explain it to you later okay uh this is my only uh visually interesting star uh slide so I I apologize for that all right so um okay so what's this about um so this is talk about duie which is a pure functional database layer for Scola I've been working on it uh in my spare time for a couple years now um we've had some releases uh there are people using it uh so today I just kind of want to talk about how it works and and why I think it's interesting uh so the takeaways I think for for this talk are are first and foremost uh jdbc is terrible this is not jtbc's fault really it has an impossible task you you cannot abstract over every possible uh relational database and and and end up with something that's satisfying so uh we just but this is Scola so we can improve on it a lot uh free monets are awesome if you saw David Ho's talk yesterday you already know this um but you'll get a chance to see some of these things uh from a different direction the uh the big idea that I want to get across is that we should really be uh leaning on composition uh as the primary tool for constructing programs uh because composition sort of naturally keeps complexity in check uh it's it's uh really nice if you can do it operational thinking really doesn't scale very well um oh and and this technique that I'm using here uh is very general and it's it's actually quite mechanical uh so even if you're not in the market for a database layer uh there may be something here that you can use to make some terrible thing in your life uh go away this sweep something under the uh which we do all the time all right so let's talk about what's wrong with jdbc uh so here's a little program uh so what does it do we got a a case class a person that has a name and an age uh we have a method that takes a result set and it reads A String from the First Column an INT from the second column constructs a person and returns it okay so uh this is how we would write it in Java and it looks okay uh but what's wrong with it um there actually quite a few things so the first thing is uh this result set that we're passing in this is a lifetime managed object okay so if you leak it by assigning it to a VAR or letting it hop onto a future somewhere then you're kind of breaking the contract you're holding on to this reference longer than uh the person calling this method is expecting okay so the loner pattern that we're using here by giving you a result set and and doing something with it it's just not safe in this context obviously we have side effects right so the methods on result set are uh dependent on the internal state of the result Set uh they might not return the same value when you uh call them again later uh and they can throw exceptions they can throw lots of exceptions uh and and it's not obviously uh composable I don't see anything here that really looks like a composable abstraction it's just sort of side affecting uh imperative procedure falling okay so my claim here is that the way we deal with this uh in a sane way is by turning this program into a data structure uh and you're just going to have to trust me for a few slides uh and and I think I can demonstrate uh that this is a useful thing to do all right so here's our strategy so we're going to identify the Primitive operations we care about and we're going to turn them into values uh we're just going to make a data type and these are going to be our smallest meaningful programs uh and then we're going to make some rules for sticking them together to build bigger programs and then we're going to build an interpreter that takes these programs and you can turn the crank and it'll connect to a database and actually do something useful okay so what does that look like okay so the first thing we want to do is Define a data type for the operations and here we're just going to use as an example the operations that you can perform on a result set okay so uh we have a a a sealed trait we have all these cases We have basically one Constructor uh for each method on the result Set uh this type parameter captures the return type uh so we can think of these little values that we can construct as just little programs they describe something that we want to do and and we could imagine uh maybe putting uh a bunch of these in a list and then you could write an interpreter that takes a result set and takes a list of these things and then uh you turn the crank and it it just calls uh corresponding methods on the result set and maybe it captures the the results of those calls in a list of any or something right so that's not very useful but in principle that's kind of what we want to do we want to have the uh the program as a piece of data that we can work with easily and separate that from from the process of of actually running it um stuffing things in a list isn't very useful so we we need a better way to put these things together so let's talk about that okay so I'm going to talk about some operations for composing these things that I claim are are going to be useful so if we have a program that returns something of type A another program that returns something of type B something that I claim is useful to do is to take those two results and combine them together using some function yielding a program that returns a value of some type c uh we might want to have a program that takes this C and then just turns it into something else just applies a function and you get a new program that returns a value of type D uh you might if you have a program that computes something of type D you might want to look at the result and based on what it is uh figure out what you're going to do next either go this way or go that way and then you might just want to say uh I I know what I want this program to compute just some arbitrary value ignore the result that just just give me some value right and you probably see what I'm doing here right so this is monatic unit uh this is flatmap this may not be how you think about flatmap but fundamentally that's what it is it allows you to look at the result of a computation to determine what the next computation should be this is just map and this is apply to which is one of the applicative uh composition tools okay so what we want is a monad okay uh if we had a monad for result set op then we could write this program this way okay so you could say okay get a name from get string get an age from get int construct a person and yield it and and you know have a result set op of person uh so that gives us much better compositional potential than just stuffing things in a list or a tree or something um but we don't have a Monet so we can't do this okay um but what if we could borrow one okay um so other talks have touched on this which is good so you're you're probably warmed up and I'm going to kind of uh Buzz through it so scholet has this type called free and free is a monad for any functor F that you give it there's another type called coona which is a functor for any s that you give it okay um by substitution free of Co of something with one typ hole in it is a monad uh and schol that abbreviates this as as fre C because it's a it's a useful type okay so if s is result set up now we have a monad well what do I mean what does that mean right we have a result set op is not a monad but we have a monad for it somehow in this other structure so that was really puzzling to me when I was first messing with this stuff so I want to talk about uh what that looks like okay so what we do is we have this new type and we'll call it result set iio and it's just an alias for free of of result setup which is just that simple data type we defined a minute ago so for each Constructor of results set up we have a corresponding Constructor uh for results set iio and it just uses this lift FC function that scaled gives us and that constructs a value of type results set iio and result Set iio uh has a Monet it's a free Monet okay so result set up is not a Monet right it's not even a functor it's just it's just a dumb data structure with no operations but results that iio is a monad and you see that this is correspondence between the constructors of result set iio and result set op so now we can write this program we want using this results that IO type so we can say uh get a name you know from get string get a name from get in get an age and then yield a person and we have something of results set IO of person and this compiles it's just uh constructing a piece of data it's not actually doing anything so if we compare this to this to the first sort of java style program we looked at uh we can see some improvements right so first of all these are values uh this is referentially transparent that's a VAR that's not a defa it's just a piece of data doesn't do anything there's no result Set uh you don't have a you can't leak a reference because you don't have a reference a result set does not appear in any of the types in this program okay and now we're composing right we have these two little programs we're putting them together and we're making a bigger program and we can do this indefinitely right and and and you'll always have something of type result iio of something okay so uh the surface complexity Remains the Same as we do this composition that's what composition means that's that's effective composition okay now we still can't do anything with this program we can't run it and we're going to get to that but uh first I want to talk about some of the other operations you get Beyond just four comprehensions just as a consequence of having this Monet available okay so Alm monets are functors so this green operation is maap so in this example we can say all right if we have an operation a program that will get us a long value at some column offset we can just take that and map it and take the value and use it to construct a Java date so now we have a way to construct a program that will read a date from uh from an arbitrary column and that's all there is to it uh and there are some other interesting operations you get that I won't talk about okay what else so all monets are also ative functors uh so you have this uh uh you have a lot of operations but this this apply to is a useful one and you also have uh this operation so we can write our program this way we can say get a string at offset one Cinnabon get an INT at offset two and we combine those and construct a person and this is equivalent to the program we had before because the get string and the get in have no data dependency between them uh we don't need a monad we can use this weaker construction okay so that's that's fine so what else can we do with with the with applicative well one thing we can do is we can say let's say we have a program that moves to the next row and tells us yes no is there another row we'll just run that and assume optimistically there's another row throw the Boolean away and then just call get person and uh return the result so we're composing these things together and just ignoring the result uh on the left okay so what else can we do well we can do this now we can that get next person then we can replicate it some number of times and uh now we have a program that will fetch a list of people okay and that's kind of cool all right so how does that work okay so first we make a list of programs that's easy we just call list. fill and pass the program in then we do the most awesome thing in all of functional programming we call sequence and that flips the type Constructors right so now instead of having a a list of programs we have a program that computes a list which is what we want okay this is possible yeah that's awesome so this is possible um because uh results said IO is an applicative fun but also because list is a traversible functor uh so if you don't know about applicative and traversible functors learn about them I I get really excited about them it's it's great stuff um okay so let's pause for just a second and and think about what we've been doing right right so we're really just following the types um we're using these uh compositional operations and working with uh these programs just like any other kind of data right it's really cool we're not thinking about operational concerns where's the result set going and and you know how am I looping and all that we're just sticking things together and following the types and these operations are just a consequence of having that monad instance uh but we haven't gotten to monad yet so what does monad give us on top of this so what it allows us to do is look at the result of running a program and determine what program to run next okay so now we can Branch so that next program that returns a Boolean we can say if it's true uh then the next thing we're going to do is get person and we'll lift it up into option otherwise uh we'll just construct a program that returns none okay so you can define a combinator called ifm uh that captures this pattern and someone was talking about it yesterday Michael maybe um so with applicative we could uh we could iterate for some number of given steps right uh but now since we can look at the results we can stop an iteration based on some computed value so we can do this we can say uh get person while I'm Vector next so what that means is as long as this next program is yielding True Values we'll get a person and then we're going to accumulate the results in a vector okay and this is this is legit right this is a totally legit way to read a result set into a vector of some structured data type okay and I I think it's just a really beautiful way to express our intent of what we want to do there's nothing extraneous uh and uh there's a accumulate combinator that's built into duie that's implemented basically in exactly this way okay so we can write these little programs with these madeup data type and this functor that we got out of nowhere or the monad we got out of nowhere um and they're pure values and they have these nice compositional properties but we need to actually run them at some point so let's talk about that so to run our program what we're going to do is we're going to interpret we're going to interpret it into some real monad something like IO or scaled scaled task so we'll sort of give up this monad we borrowed and in exchange for a real one so to do this we need to provide a mapping from results set op to our Target Monet so IO or task typically um and this is called a natural transformation and astonishingly I'm at least the third speaker to mention natural Transformations at this conference which I'm really excited about okay so uh so what does this look like so here we go so given a result set we can construct a natural transformation from a result set up to IO okay so for each Constructor of results set op remember this is the the thing we defined on like slide three for each Constructor there uh we map it to a corresponding IO primitive in IO that operates on that result set okay so that's very mechanical okay now what well now we can write this method okay so given a program written in free C which is our results set IO program plus this natural transformation we just defined we get an IO back okay so this program here uh that uh reads the result set into a vector uh you can actually run it now you give it uh a result set and call in safe perform iio and you get a normal Scala Vector of person okay so that's kind of that kind of closes the loop so an interesting thing here to me is is that um we never had to explain how to map or flatmap anything which is which is really interesting we borrowed some Machinery that let us pretend for a while that we could do this and then uh we just trade it in at some point by translating it to another monad and we never have to directly Implement that Machinery which I think is is is really cool all right so uh let's talk about duie uh finally so what is it well it's it's basically what I just showed you but it's for all of jdbc so this gives you a pure functional sort of substrate uh for all the Primitive operations that jdbc supports so if you need access to weird vendor specific ific stuff uh it's there you can just you can get to it and it's built in this nice compositional way all the way up uh there's no ugly plumbing underneath it's it's nice all the way down uh now it might have occurred to you that what I've been doing is very mechanical right we're just like making this data type that corresponds to an interface and then a natural transformation uh and it is mechanical in fact the the core uh of duie is machine generated so uh the free algebra is the interpreters are all machine generated and that accounts for about 70% of the scaly or the for of the uh duie core so I have written like 2200 lines of code and the rest is spat out by a computer so that's pretty nice okay so what we've seen so far has nice compositional properties uh but it's still very low level uh it it would be a pain to write programs using this so uh I want to show you very quickly some of the higher level stuff that I uh that I've built up on top of it and then at the end I'll give you a pointer to a reference where you can see some other some other things that that that doie can do okay so one of the one of the important things is exception handling so uh all the exception handling is based on these two Primitives attempt and fail so uh attempt takes a program that computes some value of type A and turns it into a program or it it constructs a program the computes a throwable or a so you can do that to say okay at this point uh I want to trap exceptions and and and take a look at them uh the fail uh combinator just induces a failure so uh it's if you want to rethrow for instance you you would use that combinator and from those two Primitives we can construct all this other stuff so we have a general purpose handlers for dealing with just arbitrary exceptions uh there are ones that are specific to SQL exceptions and SQL States because that's what we're normally going to be running into uh for postgress and so one of the reasons postgress is awesome is they actually publish a table of all of the errors uh and the corresponding SQL States so uh we can generate combinators here that you can use that are very they're specific to postgress but can catch any of these hundreds of specific errors which is really nice because otherwise you just have to look at the SQL state which is a string which is awful and and uh so that's a reason you use post grossery there okay so uh another thing uh so here we're reading a string from column one and in from column two this is Scola so we can abstract over the return type there just by using a type class uh we can generalize that to tupal so now we can just say we're going to read a string and then an INT from column one uh using shapeless we can gener generalize that to uh product type so we can just say we're going to read a person from column one uh okay this for comprehension doesn't do anything anymore so it's just this and when you're reading a row you're always you're always going to start at column one right so we can default that away so really this program we've been trying to write this whole time that's how you would write it in duie right okay okay so um so that's how you can read a value from a row so let's talk about how you deal with a bunch of rows which is what you're usually doing so uh we've seen this program before right uh this is written with our with our type class notation it just says get a person as long as there's another row and collect them into a list uh but another way we can do is we can say I just want to process a person and what that gives us is a scaled stream process the effect type is results that I owe and what it's yielding to you is uh person objects okay so what can we do with that well we can say we want to filter take drop Fork join send stuff out to a socket what you know whatever we want and then we finally run that okay in this case by just saying list which says okay what I want to do is collect all the results into a list what we get back is a program it's exactly the same type as the uh uh that's returned by the method uh at the top of the page okay so this lets you sort of jump into stream world and Define these nice Transformations uh and then when you run it you're just back where you started uh so it's a really nice way to sort of jump in and out of of of the streaming world okay so let's see yeah all right sorry um so we've talked about results sets but there are a lot of other sort of contexts that you have to operate in there there are connection pools connections statements um so the the the structure of a database program typically looks something like this this kind of a natural nesting you have you have a connection pool that probably lasts the lifetime of your application uh you have these interactions with the database that that allocate a connection and do some things with it execute some statements maybe get result sets that you have to deal with and duie programs written in these different contexts have different types they have they have different sets of operations that are legal okay so but duie allows you to S to click these things together to make bigger programs that handle these large interactions that have this nesting structure uh and and and some of these patterns are so common that there's a very high level API that just does the whole thing for you so you don't have to to look at the the nesting structure directly so let's look at that all right so let me back up all right so here we have a country object that has a name a code population uh we can use this uh string interpolation thing uh to construct a query and says okay we'll select code named GMP from country where population is greater than this value okay now this this gets turned into a prepared statement and that dollar sign whatever turns into a question mark and there's a properly typed call to set int at index one uh because everything's one indexed in in jdbc uh so so that's safe that's not a SQL injection risk what this thing is going to yield is values of type country uh and we can view this query thing in in in a couple ways and we'll we'll look at this so here's a reppel session so we can say bigger than 100 million I think and we can say Okay I want to look at as look at this as a pro process so we have a scaled stream process the effect type is connection IO which means we're all the way up at the very top this is something that needs a connection in order to run uh we'll take the first five collect them as a list and now we have a connection IO of list person similar to what we saw a minute ago uh then we call transact and I don't have time to talk about this but there's this thing called a transactor and it just abstracts over connection pools that's basically all it does this is something that knows how to uh produce a database connection uh and uh and put transact put commits and at the end and and roll back and and all that stuff so we do that and once we transact it we just get a schol task in this case of list person all the dubie stuff is gone now right we're just back in normal scholar ad land we have a task and when we run it it goes out and connects to the database and gets the first five things through the stream and gathers them up and does all that closes the stream uh cleans everything up and returns you the list of person person and then we're just printing them out uh so this is fine in the repple uh this is kind of a pain you don't want to have to do this every time so we have YOLO mode uh that lets you say Okay I I I want this transactor to sort of be the ambient one that I'm going to be using uh and then instead of then you can just say do quick. run and it'll just print out the results to the to the console so this is good for experimentation another nice thing you can do with yolo is you can type check your queries so instead of quick saying quick. run you can say check. run and it'll go out and talk to the database and it'll make sure the SQL compiles it'll make sure the parameters uh the aity lines up and that the types match up so uh there's one problem here uh that the GMP can actually be null in the database and we didn't know that so it advises us okay uh we can fix this we can we can either make the column not uh not null in the database or we can use an option type uh on the Scola side so it doesn't just say something's wrong it tries to give you hints about uh about how to fix it which which is useful I think oh and you can do this in your unit tests too so there's a I have a Specs trait uh that you can just mix in that allows you to do this in your tests and it would be trivial to do it for a scholar check or whatever I just nobody's offered to do that yet or scholar test rather okay um there's a lot of other stuff um so type mapping is very very simple if you saw U Michael's talk yesterday on uh sodc uh he had this codec type with invariant mapping and basically column mapping in in duie works the same way so it's it's very very simple uh to map arbitrary things into columns and arbitrary things across column vectors as well um connection pooling uh so uh I there's an implementation uh for Hikari that that comes with duie it's very easy to use any kind of connection pool you want it's that's that's not a big deal at all uh these types are kind of fancy um if you're familiar with scalled streams you know there's not a do list thing that you can call that says gather everything into a list so that was syntax that was added so here and there uh I've added syntax to make these fancy types a little easier to deal with uh the postgress support I I think so I I'm I'm I try to be very modest about this but I'm really proud of the postgress support I think it's the better support than any other database Library I know of we support you know all these geometric types postgis listen and notify which is really cool because you can you can make the database tell you when something changes uh and you can view these notifications as a stream of course so it's it's it's uh it all clicks together really nicely um and but it does work with any jdbc driver so people using H2 MySQL mssql um I have a crazy friend who's using it with Apache Hive which is just this horri like this really terrible like degenerate jdbc driver it doesn't do anything um but if you just like kind of crank the volume down you can get it to work and he's he's using it with duie and really likes it better than than the native interface so that that's a lot of fun um so I've kind of zoomed through this I have a little time for questions well let me talk a little bit about about uh support so um there are a couple things that bother me about libraries one of them is that the there's no a or the documentation uh is out of date so uh I wrote a tool called Tut that you use to type check the documentation for your project and if the examples in your doc don't compile then your project doesn't build uh so I wrote that in order to write the duie documentation so the book of duie is full of examples and explanations and everything works if you copy and paste the code examples from the page they will actually work which is which is great um and uh uh and there's a getter Channel too so it's a small community still um if you have a question I'm probably the only person who can answer it so I try to be responsive uh on the getter Channel there so if you want to try it out and you you have trouble with something please ask it's it's important to me that you that people who are trying it feel like they have a fighting chance to to to be successful with it um and that's all I got so questions yes I don't know what that is oh asking about it would it be applicable to sleepy cat but I don't know what that is um yeah so um dubie is very specific to jdbc right so the the the goal is to take jdbc and make it usable and then just see what happens sort of riffing on it what can we build on top of it but the technique I describe is totally applicable to that that kind of thing uh and you might even be able to take the code generator from duie and give it some tweaks and get it to to spit out some of this uh some of this code for you um uh a friend of mine in in in uh uh Portland is is thinking about trying to do this for Cassandra in fact and and Cassandra is a huge API it's much much bigger than jdbc so it's going to be hard um kind of the Saving Grace of jdbc is that it's so dumb like it it it doesn't really do very much it doesn't really have any any abstractions so that's nice it gives us a a very clean sort of floor we can build on top of it might be harder with with other apis yes uh you can get you can do whatever you want so um when you Define a query uh you it doesn't infer the return type you have to assert the return type um so you can say uh I want you know uh uh I want this thing as an hlist or I want it as a shapeless record that works you can say I want it as this one case class or a nesting of case classes and tup or or whatever so um uh you have a lot of choices in in how you do that whatever makes sense for you yes no listen uh so uh the listen notify stuff is specific to to to post Crest um and it's integrated with the uh with the transaction so sending sending a notification is a transactional operations it doesn't actually happen until until commit time uh so it doesn't work with any other databases um in terms of hooking in with something like reactive streams uh it's probably possible but I don't know I've never looked at reactive streams so I I couldn't tell you I was talking to somebody about it yesterday uh Dean I think and and uh it seems possible it just somebody needs to look at it yeah Eric I think well I mean I think just just sort of as a general principle of of software engineering I want I want the smallest I want these low-level operations I want the the absolute base of what I need to do and then I want to build stuff up on top of it um and and yeah so that's what I prefer to see when I'm working on stuff you know I prefer to build stuff up by combining um but yeah like if you if you tried to do use this technique like on swing for example it would just be a nightmare it would take you know you could never do it because there are all these awful abstractions that you would have to like beat down um before you got down to a set of operations that really made sense um however uh you could do this for like uh Graphics 2D and a fine transform and stuff like that because those are nice flat apis and that would work totally fine yes you uh I'm sorry yes uh so finish finish finish okay so the question is you you end up with scaled task is is there any other option uh yes there is um it will work with uh any The Interpreter that that is provided will work with uh any any IO like type right so you need a type that can capture side effecting expressions and turn them into values right so you couldn't do it with standard Library future because you can't do that with future but you do with task you knew with IO um there's this remoting system called remotely that that Stu and those guys work on uh and there's um uh there's a type like a response type uh that works that works this way so I was able to set up dub so that it could send results back over remotely with one line of code literally one line of code to to to connect them together now there's a bigger question is can you provide another interpreter that does something totally different like you can mock out the whole database for instance with another interpreter you're welcome to do that um it's just going to be a lot of work that's the problem because there are thousands and thousands of operations uh like callable statement has like over 200 operations that it supports so it'll be a big interpreter but you can certainly do it yeah uh yeah we have time okay yeah one um okay so the the question was about um Native support for Jason which which postgress has um yeah so uh duie deals with that just fine um there's an example in the documentation that shows how you can uh read stuff in uh using argonot and then use and then use um Argonaut deserializers to further turn that into real type so it just uses Argonaut as the intermediate to interpret the Json that you're getting back um yeah it's in the book I in the type mapping chapter yeah anybody else yes okay so the the question was about resource safety how do we get guarantee that uh how do we guarantee that result sets and streams and stuff get closed um so some databases are are are cool and you just close the connection and it cleans everything up they DAV this like Oracle where you got to close everything in exactly the right order or it'll leak and the guy in suspenders will come and yell at you um so um I I talked briefly let's see about uh this business uh where you're embedding you're lifting programs uh from one context up into the other and at each point uh there is a lifetime manag resource that's involved so um when you have a result set program let's say we have a prepared statement program and when you execute uh you're going to get a result set and you use that result Set uh in order to lift this result set program in and click them together and in the process uh it attaches an a u a Handler using that exception handling stuff I talked about that guarantees that no matter what you do uh that's always going to be closed now I think you had another question about maybe you don't want the connection to be closed uh when you're done uh and you don't have to use this transactor business that that that does that you can just say here's my connection here's a connection iio program go and that'll work fine yeah did that answer your question okay great anyone else yes okay so the the the question was about uh type safety and the queries and and I had this check method um that does go out to the database base and uh uh it checks and verifies that the uh uh the types that you've asserted line up with with what the database is actually reporting um and yeah and that's a runtime thing it has to talk to the database um but it could be done at compile time and uh uh SQL type does that and I think slick 3 uh will do that for you um and they can infer the return types uh as well by doing that uh it makes the build a little more complicated um you have to have a database connection or your code won't compile um so I've thought about doing that but I I I think uh I I personally prefer to kind of defer that as sort of a a something I can stuff in the unit tests once I get it right I just have this sanity check that runs every time I run the test and ensures everything's right but that's a that's a totally uh valid concern I think that's a it's valid arguments on both sides of that yes [Music] I don't have anything similar in Dy um I mean you could certainly start to build stuff up from from where I am but I'm I'm one guy slick as slick is like a full-time guy who's really smart and uh yeah so they they have a lot of really clever stuff going on in slick and and I think the if you haven't whipped at slick lately the stuff that done in slick 3 is really good so if you're a slick user you should definitely check it out your your life will be a lot happier anyone else I think we're about done okay thanks a lot