Scale By The Bay 2019: Alexander loffe, Quill + Doobie = Better Together

Scale By The Bay 2019: Alexander loffe, Quill + Doobie = Better Together

Recording: Scale By The Bay 2019: Alexander loffe, Quill + Doobie = Better Together

[Music] from one end of functional to the other doing the absolute best possible to keep that together hi my name is Alexander Yaffe and I'm here to talk about the integration of doobie and quail this is a lot of fun this is a basically the key parts of this Rob and I wrote in about an hour so it's amazing how much you can get done with peer-to-peer collaboration and a short amount of time so this is this is really neat and I'm really excited to be up here and talking for the first time so this is something that we came up with together when we realized just how sort of correctly doobie and quill fit together because the purpose of them is to effectively solve different parts of that equation so you know let me adjust this thing so my name is Alexander Yaffe as I've said before Rob is not here unfortunately I was really excited to give the talk with him but he has bronchitis unfortunately so let's all wish him well both of us are very reachable we all go by the same handle on those channels the purpose of quill and doobie is to be able to get functional programming to talk with these culprits below in their respective personalities now that was effectively where quill and doobie aim to ask two different questions the question the quill asked is how do you compose queries well database queries well do be asked how do you compose data back database effects well and that's where sort of the divergence and the architecture began now let's talk about do B for a second now what you end up having is you start with some SQL right you go to some kind of query that's parameterised has some kind of structure and then what happens is that you have this ability to create effect full computation I think it was at a scale by the bay a few years ago where the question was how do you actually describe what IO and connection IO is without using the word monad and so I think it was actually John des goes who said that this is an application this is an application of connection state that manages connection state that exists within a particular context and that can be executed and returns a particular kind of type so in this case where you have a particular type of ro you could return a particular group of records that exist within some kind of collection the alternative to this is to be able to have something that represents a stream of records now this stream is an FS to stream and one of the very interesting decisions that they've made is to separate out the effect type so connection IO is effectively a application that manages connection state a stream of that is a stream of an application that manages connection state now ultimately these are functional abstractions we need to get to a point where the database is abstracted out that's the whole purpose of this and so what we end up doing is we end up having eventually this turn into an application of just records or of just a stream of records and the way that you do that ultimately is that you have this thing called a trans actor this has some very interesting architectural consequences the fact that for instance Dubey does not need to hold database connections inside of thread locals in order to be able to manage asynchronous computation because it free monads out the entirety of JDBC and so effectively you can set up your entire computation within these constructs and then have it all executed later so this is this is a very very interesting and exciting way of managing computations on databases now the thing is when it comes to do B is that you get full composability and you also get a guarantee of no side effects starting from the Green Line down right so quill is supposed to do it from the Green Line up but we'll talk about that in a minute now let's look at what this means practically so here's a doobie query right so we expect to produce a record with the particular kind of structure right if the query doesn't have that kind of structure if you don't return an age column or also return some other column the thing will fail now you have to write a test in order to do this because again this is a string you're composing with strings oh and by the way it's actually not that bad you don't have like sequel injection risks because ultimately the parameters of this our string interpolate 'add so you don't actually have to worry about that part of it it just ends up going into a prepared statement now what you can do because ultimately this is a structural kind of construct is that you can actually create case classes that have the same kind of structure and effectively they're both you know in fancy terminology they're both categorical products and because of that you can just assume that that these things are isomorphic and and so you can interchange one for the other now the next part of it is that what you need to consider here is that again all of this comes from a string and because of that what you can end up doing is you could end up reorganizing for instance the columns that are coming out in any sort of arbitrary way now you need to write tests for this and here's the thing the tests may or may not pass depending on whether you use do B to generate your data in the first place so for instance if you're using do B queries to read your data as well as write your data right the last name column is always going to be swapped for the first name column so you won't even catch that in a test on the other hand if your tests you know deal with raw raw DDL statements and inserts then you but the thing is that you might not even catch this and this is the part that makes do be no longer functionally programmed no longer functionally type saved on this kind of level so meanwhile meanwhile actual complexity comes in right in reality we have tables that need to be joined many tables that need to be joined and so for instance here's a here's a typical way of how you would do the permissions and roles of what somebody can do when they come to your applications party right typically all security databases work in a very similar way where you've got many too many mappings between roles and permissions now we decided to add the additional complexity of three kinds of entities here you know just what the hell but this is reality oh and by the way when it comes to databases you can't actually have a many-to-many mapping you need to have mapping tables wonderful right so in addition to our table or query for humans let's have a query for trolls right when it comes to trolls we want to do some additional things like check where they come from because we want to restrict their membership within our business logic in a particular way and oh by the way based on some business logic we may or may not allow them to have a particular kind of a particular kind of permission like trolls are not allowed to loudmouth in particular situations right this is the reality of business logic business logic is ugly and dirty and has arbitrary rules about what to allow and what to not allow now what ends up happening is that we copy these joins left and right and so we have copies and copies and copies and copies and copies of queries but we don't want that so we want to abstract it out all right let's abstract it out right how do we do this in Dewey well we have these fragments we have these fragments that we can factor out ooh were assuming that our table has a particular alias let's go back write troll has you here we're assuming that that alias is being used here oh and in addition to that we're assuming that all the aliases that we in reduced in order to join all of our other tables are not being used anywhere as well okay this is this is getting interesting well okay let's let's move back up a level so here's here's where we were oh that's right we need the name of the permission in the role and we assumed they had certain aliases right our type safety is quickly going downhill right what do we do about it in practice how do we solve this problem well we wish that we could do this on the level of SQL right we wish we actually had some kind of polymorphic API in SQL that could solve this problem right what if we had some kind of magical UDF into which you could pass a table type and it would join and return our table type and the stuff that we want out of it right that's what would be great that's what we have here right if we could just pass a table with a particular structure ie an ID column right that's all that we need to join our roles and permissions we just need an ID column right but oh no it doesn't actually exist but we wish we had this because then we could just use it to compose joins further downstream but again we don't right this is very sad this is what quill does quill gives you an interface that allows you to compose things like this constructs like this that are able to take a type of table in this case we're using duck typing and effectively this is a record type that contains an ID because that is the only thing that we need in order to join to our roles and permissions tables and so once we have this we can join it further downstream okay so this ultimately needs to be macro expanded and we macro expand it so here's an example of plugging query of person it could be an arbitrary complexity query of person actually you could add arbitrary filters and and whatever you want in there but that's the cons that's the that's the contract anything a query of anything that has an ID can be passed inside of here and again because this is a for comprehension this works exactly the same way as Scala collections all of the record types are what we what we would expect that's aquarii of t the yields key that's a user role that's a role etc etc etc right we continue to compose downward now the interesting consequence of doing it like this is that we can actually mix and match the kind of joins that we have right this is an explicit join syntax that is also that is also monadic right if you've used things like if you've used things like quill you have a choice of whether you want to be able to do explicit or monadic we have both now what's the consequence of this is that when you left join your entire row is null how does Scala do nulls how do we deal with nulls the idiomatic way of doing it is with option and so that's what we're able to do in this kind of construct or able to use option to effectively model nullity now this is the equivalent of something like that that you would have with slick which is effectively just doing the whole thing with if statements but again you can only do inner joins you cannot mix inner joins and outer joins in this kind of syntax and additionally there was a decision made effectively to have this as an implicit join not as an explicit join because you don't actually say what kind of join here you want to have okay so this is what slick is is it is a Swiss Army tank of writing SQL expressions and you we have selects we have inserts we have updates we have all sorts of dsls to be able to pure functionally write scala constructs that eventually generate SQL and you can use these things again to compose further downstream now eventually we have to turn this into SQL this has to be useful so that's what we do we turn it into SQL and execute it on a database but because this is where do be starts we just continue and pass selects into Dubey connection iOS that eventually go into trans actors and execute on your database so effectively what you end up having is you end up having a layer of quill that's managed for the query side of it you have a layer of Dubey that's managed for the effect side of it and oh there's that green line except for now it's pure functional above and below the green line okay so let's see a simple example of how this would happen here's our query that existed before right notice how what we have to do anyway is we have to turn it into connection i/o we have to turn it into an application that manages database connection state anyway all right so let's just do that and by the way again we can compose these things freely compose these things because again these are connection iOS and again all of these compositions do not need to have thread locals in order to store connections now let's add quill right right there there's your difference right this is not a very high commitment API you do not need to marry this API in order to use it you just insert it into little parts of your query oh we want to be able to have better composition here and there just splice it in here and there this is not something that you need to massively commit to and it fits perfectly into a do be pure functional stack again all of the types that you have with Dubey are unchanged now this is where we need to answer an interesting complication or an interesting an interesting difficulty that people talk about which is that when you are composing things like this what ends up happening is that you have all of these query compositions and because they offer you all of these nice abstractions you end up having a gigantic query that a database can easily choke on or at least that an optimizer can easily choke on now you actually don't know you don't know what's coming out of something as big as this right and this is what you can find potentially in production oh and by the way you don't have a leus as for variable names quill gives you a leus as for variable names forgot to mention that right but oh and by the way I hand formatted this right this does not come to you formatted right and then and then and then what what what the heck how do I make heads and tails of this and your DBAs are pissed off right and so how do you deal with this well the way that you deal with this and this is the this is sort of the brilliance of the decisions of Flavio who I hope is here up there is who created quill this was the brilliance do it all during compile time or as much as humanly possible do it during compile time so that you can see the query coming out of the compiler and so that you can see it before you actually execute it now you can have the best kind of unit testing in the world but to have every single permutation of this multiple e multiple joint multiply per muted slice-and-dice query is very difficult so the way that we do that the way that we implement that as we use the scala macro engine and the macro engine is able to take this quoted block this quotation and before it actually compiles thus the code it's able to parse it and so we have a Scala tree and were able to turn that Scala tree into a syntax tree a secondary syntax tree that eventually becomes an SQL query and we do all of this during compile time so here's what your SVT law log looks like here's what your scarcity log looks like it is right there you can see the query right there and so you don't have to worry about oh my god what is the giant construct that this eventually is gonna produce on some kind of database that's gonna blow up in my face somewhere you don't have to worry this is it this is what you are going to be executing right and you actually the ideas to try it out the idea is you can copy and paste it into into some kind of evaluator and you actually know that it works we don't have this by the way for Dubey yet for other flavors of quill what we're able to do is actually execute the query during the compiler so before your compilation is done we can actually tell you we went out and executed this query on a database and it worked or it didn't work we don't have this for Dubey yet it's it's another exciting exciting direction to go and then on top of that there are although they're more rare these days you still have some some shops where the DBA team is powerful and demands to know what is going to be executed and this is this is understandable because they need to be able to write the indexes if they're expected to manage the performance right they need to be able to know what indexes they need to create and so they need to know the SQL in advance of that so that's why our M is still forbidden in some places but certainly if you know it then you can create the indexes that are needed if if that's sort of if that's what your culture is like but but anyhow a couple of caveats to beware of when it comes to creating queries during compile time something that you have to keep in mind is that any value inside of this quoted block that's introduced from some external area needs special treatment because this is a runtime variable this isn't something that the compiler has a value for all right so this is not gonna work so effectively what we have is we have this lift construct and the error will tell you effectively this name that's coming from this function argument needs to be lifted and so you do that and that introduces query parameters into the queries that your compiler will generate you know insert here and we use preparedstatement ultimately to encode this value this this external value so that's the first part the other part is when you're really starting to compose and pass queries into other queries I don't have that yet I'll show you in in a minute what you eventually want to do is you want to bring the whole function that composes into your quotation because you want your quotations themselves to be able to fully compose within each other so that's why what we have here is were returning a lambda instead of just declaring a function that means that during compile time this value name of this person in this case is going to be known now you don't need to lift it anymore there's no lift because the function itself lives within its entirety within the quotation so another thing to be aware of by the way is that type annotations right if we actually annotate the type here quoted query of person anything that lives inside of a quote quoted block needs to be this quoted right well the challenge here is that what Coyle does is it uses refine types to store the intermediate syntax trees of all of your queries and that's the challenge there if you actually give them types than the type or fineman's disappear and we can no longer give you your query during compile time we can give it to you during run time well we can certainly still do that but you lose the ability to know what it is during compile time this is something that we're hoping to make significantly better with with Scala 3 but a talk for a different time the other thing that you have to consider is that when you're dealing again if you have heavy slick stacks you'll see something like this where you actually pick query substructures based on compile time values so Joe's and Jack's respectively based on a condition now the challenge here again is because you are building up your query based on runtime values we don't actually know during compile time what the value of this runtime condition boolean is gonna be and so we don't know which path to follow and we don't know ultimately how to construct your query so this is another thing you have to keep track of again we can give you the query during run we just can't give it to you during compiled time now the pattern that you use and there's definitely more to be said about how to integrate this into more complex constructs but the idea is that you wait to resolve your runtime conditions until you're actually executing your code and so what you can do is because you can compose queries and do queries and queries and queries what you do is you pass some kind of construct whether it be a pre-filtered query etc etc into this final stage that evaluates based on the runtime conditions so this is this is something that you have to keep track of this run method here is sort of our end of the world or end of the compile time world we also have by the way we also have stream and what stream does is basically the same thing now both run and scream if you take a look below what they do is they will actually build a query and when you use this kind of pattern you will will effectively be building every single variation of that query that you need to have now again what you can do if if you have dozens of filters that you need to potentially introduce that's another conversation but at least for this kind of logic you can build all the different variations that you need now let's put it all together let's let's combine dooby quail HTTP for s and FS to see how this kind of application actually looks like and we're gonna be introducing a little bit so for small applications and here what a here's what a small application with HTTP for s FS to do B and quayle looks like so here's our restful endpoint right there we create our arc will query inside of there and again this is not like the best development pattern in the world you should be having data access objects into which you abstract all of your database code away this is not this is not what you should actually be doing but this is really the beginning and end of it you you know introduce whatever abstract you want to introduce on top of that so again this is a relatively small query if you were not using quill that's what that would look like with pure Dubey so it's not it's not really that much of a sort of big bang for your buck for small queries now where it really starts getting good is with large queries so here's that giant join that we had before that we composed into an endpoint so there's our endpoint right there oh and we need to introduce trolls too right so there's there's our code copying or there's our there's our refactoring out that's what that looks like okay how do we do this with quill all right well there's our join tables like we saw before there's our function that we've written okay we take a query perfect and we just use it all right let's just put it in all right so now we have people and we can have people join to their respective permissions what's the record type what's coming out of this thing right so quill starts with a query that has a particular structure that's a person and that's actually a whole row right person first name last name right one of the things that SQL requires is everything to be fully flattened your record types that need to be fully flattened we have taken care of this Dubey actually also takes care of this so you have structure that exists and the two strings after that are the role in the permission name respectively so there's what's coming out this is the quill side we turn the quill side into the streaming side so again this is a streaming application of a connection IO effect that yields a record type of person string string okay we call the transact and then we just go to a stream of IO out of stream of connection IO so we have abstracted out the database at this level effectively now or ultimately returning this data to a client so we need to do some kind of marshaling and again this is probably not how you should do marshaling but you know just bear with me for the sake of example okay and then finally we have an entity that we return and that's what comes out so let's introduce trolls right that seems to be pretty reasonable there's our second end point I was actually going to introduce the third end point but not enough time so anyhow so I think I'll finish off here both Dubey and quill end up going roughly were they attempted to go where quill does query composition really well do be does affect composition really well and composes into further things it has great integrations into cats further downstream and that's it that's my conclusion go and insert some quill snippets into your DD Code today [Applause] and I'll take questions anyone have questions we have time for a few okay all right well thank micros are going away in Scylla three what's the approach there so in Scala three I am super excited about it we're gonna be using in line I think I actually did mention it if I'm not getting deja vu we are gonna use in line in order to effectively compose our our trees and this is really cool because remember how I said you can't have type annotations with dot e we will be able to have type annotations and that will again because that integrates into other things like shapeless for instance that as far as I can see it will actually vastly increase the power and the capability of quill once we have that functionality and the macro system really does provide everything that we need to have it's going to be is going to be an interesting challenge to get there but it will provide us with a lot of capability that we do not have now so you have shown us the table how we can join and everything I'm sorry say that again you have shown us how the table can be joined etcetera yes can we directly call the UDF on the like database or the store TOC um you wanna call store proc yep or the UDF okay directly call store proxxon UDF's so the way that you would do that what Quayle has it has a construct called a query schema and you can define within the query schema the thing that you want to call so you could do it that way there's also a query meta that you can play around with as well there are a couple of different strategies that we have one of the things that I haven't covered at all actually is that we have a construct called infix a quill construct called infix where what you can effectively do is splice in arbitrary SQL that you want and we use that for all sorts of things you know sort of in fixes like the giant leak of our quill abstraction but it's a really good abstraction leak and we use it for everything so whenever you need to whenever you need to introduce different kinds of sort of very nitty-gritty SQL constructs in fixes how you do it any other questions okay well let's give a round of applause for our speakers thank you so much [Music]