Slick, Typesafe's library for accessing databases

funconf 2013, Brendan McAdams: Slick, Typesafe's library for accessing databases

Recording: funconf 2013, Brendan McAdams: Slick, Typesafe's library for accessing databases

my name is brandy mcadams i work for type tape and professional services group um also spending part of my time now working on slick which is our database access layer it grew out of an old project called skull aquarium this is the fonzai group created he now leads our slick team i want to talk about what slick is some of what is coming in the future but also sort of how we use it how it looks under the covers in some ways at all give you guys a taste of a different way of doing things and hopefully an idea of why we might want to do things the way that slick allows us to so slick stands for scala language integrated connection kit however at some point not so long ago we decided to make it not all capitals it's capital s and the rest is lowercase even though it's an acronym so just keep that in mind so this is the idea is this is meant to be an elegant functional database library for a more civilized age this is the one and only time i've ever put an audio sound in a slide and i sort of regret it now it was cheesy when it started now it's just silly but this is the idea and i'm going to talk about some of why we want to do this but the reality is you know think about the concepts behind relational databases you've got cod's 12 rules from what the early 70s late six and they were meant to solve a very particular problem but we were also working with very different types of tools and programming languages at the time and i think we've we've hit a more civilized age we're starting to take these concepts of functional programming from the research world the academic world move them into production programming and there's a lot of changes happening but we for the most part apart from this nosql movement going on that i've been part of so i should be careful about how i described that um we haven't really changed the way that we access our databases through code and in some ways it works so why we're here and why i want you to sit here for the next hour and hopefully nobody runs out i want to talk about some key concepts so first of all the functional data concepts which is how we work with collections in scholarly how many of you consider yourselves to be scholar experts with a show of hands i don't see anybody's hand up except sanjay here in the front um so i want to review a little bit about how you manipulate collections in scholar and the reason why is this if you know how to manipulate a seek in scholar you know how to manipulate a database with slick so we're going to review how we do that so that you guys are comfortable with that and then i can unleash you on just playing with slick because you know everything you need to know a little bit about the concept of an evidence mismatch and this is where code and data fail to mean that's that part i'm talking about of this more civilized age of finding a better way to work with our information and how we map scholars functional collections because that is a key concept of scala that really makes it stand out is that the con that the collections are functional we can pass functions in and manipulate that information with functions so what do we aim to solve with slick and the idea partly is that we want our code and our data to be in harmony so slick is a database query and access library for scala the idea is that slik is supposed to allow us to work with the database and we can write native stall of syntax to do it which is a little bit novel this is a different way of doing things we let scala generate our data access for us under the covers and this means no more sql this is without us needing to go over to nosql because one of the things that's driven nosql over the years has been people who are tired of writing sql tired of data models that don't match up with their code tired of using hibernate for example which as far as i'm concerned is a good enough reason to get rid of every sql database on earth using hibernate you can still write sql if you need to i'm not going to talk about it today or show it but there is a layer in slick that allows you to write sql directly if you need to and to access the jdbc layers and we are working on adding no sql support that's actually one of my projects and so in the next few months you're going to start to see us take all these concepts and apply them to more than just sql databases slik 1.0 is out now this requires scholar 210 plus 2.0 is currently in a milestone cycle so i think m2 was released a few weeks ago and it's still like pre-beta it's not really production ready there's still some initial concepts and ideas and other things that we're playing with but we are evolving fairly quickly so we went from 100 to 200 because 1.0 got out we had a lot of new ideas and other things that changed rapidly and we went over to 2o the work that i'm doing on nosql support is targeted against the 2o release what i'm going to show you today are going to be examples from 1 0 because 1 0 is currently the production release and the one that you're most likely going to be working against and this is from the same people who've brought us skal it's the team at typesafe and a number of people at epfl who are working on this so the two main developers on slick are stefan zeiger who originally creates scholar query he works for us at typesafe and christopher vote and i apologize that it probably mispronounced christopher's last name christopher works for epfl where he does research and other things and the two of them together have driven a lot of the work on slick forward and i'm starting to come on the team and pretend like i'm actually an equal contributor and i feel a little overwhelmed at times so what do we support we are focused right now on relational databases because that's the core area that slick was originally built for but like i said we're going to expand that so we support postgres mysql we support microsoft sql server there's two things with that i in my distant past did a lot of work with sybase and anybody who remembers the history for a long time microsoft licensed the cybase source code and repackaged it as sql server so they actually have the same protocol even when microsoft bought the rights just go off on their own and there's actually a couple open source libraries that can connect both sideways and sql server so i'm curious to play with it at some point but i bet we probably could make cybase work given that we have sql server support um we support sql lite we support h2 derby slash javad hyper sql which is usually known as hsqldb microsoft access for some reason and in 2.0 there's actually a really cool in memory driver and i say it's really cool because it's actually been a really great model for me adding mongodb support the in-memory model drove a lot of moving away from everything being hardwired for sql and so i actually finally started looking at the in-memory driver last week and realizing that i've been banging my head against the wall looking at the jdbc stuff for examples because the in-memory driver doesn't use sql it just maps your scala code directly to any data structure now there is something that i should note the items that are on the left for 1.0 are all part of the open source package of slick and they're completely free for 1.0 oracle and db2 require a license for typesafe for usage with slick 2.0 anything that's considered a proprietary database so that would be sql server moving into the big red box is going to be moved over to the concept of being a proprietary driver that you'll license from us so if you're using an open source database and connecting slick to it there's no license or anything else if you're connecting to a proprietary database like oracle db2 sql server then you would get a license from us to use those extensions for slick i'm not actually sure up top my head how those are distributed whether they're freely available for development but i can find that out if anybody needs that info now what's coming real soon now what i'm working on the first thing we're looking at is mongodb we're also looking at a couple of other node sql databases because we don't want to just be manga even though people know me as the person who did a lot of work on scala and i do have interest outside of that now that i no longer work for the vendor behind i can play with other nosql databases and that includes things like riot cassandra neo4jnc how these map and if we can make these things work so as far as examples go stefan has done a bunch of demos in the past and uses coffee as some examples and one thing i want to not feel like i'm just cutting and pasting stefan's slides even when i am um i also change it up a little bit because i tend to drink more tea than coffee these days coffee eats a hole through my stomach the curse of 15 years as a programmer um also have found some really good tea around the world so there's that so let's look at how we might track data about tea and scholars so what i've done is i've created a bunch of tea vendors um from around the world so some are very expensive and some teas that they sell what i typically did was pick the most expensive tea in the vendor sold pop them in there we're going to work entirely with an in-memory collection of data now i should separate that out we're not using the new in-memory stuff in 2.0 we're using i believe h2 or hsqldb so it's using a java jdbc driver that behind the scenes in memory now i want to start before we look at the sequel and look at some scholar i should clarify this before we go forward the examples i'm about to show you are pure scala imagine if we're just building our t data in scala data structures and manipulating to review how we work with this information so i've created an enum in scala called t and there are several types there's black white long and green interestingly enough there is at least one large tea vendor in france i don't know if all of them do but mariage frere sells oolong as purple they're very consistent on everything has a color and for a long time i had no idea what purple tea was and i got blank stares from the guys at the shop until somebody finally explained that they called it along so i kept it a zoo long because everybody knows move along we have a case class which is aspire does everybody know what a case class is does anybody want to tell me what the properties that i automatically get from the case class are okay we get setters and getters what else do we get equal i heard equals i heard hashcode anything else two string and we get constructors we get pattern matching so there's value to using case classes so i've created a case class here i've got a supplier name i've got the country that they're based in and i've got a url which is a java net uri i've also then got t and t represents some item of t for sale and that's the supplier the name of the team the color of the t the size 100 grams a pound whatever else the currency that it's sold in and the price which is a double everybody okay so far so this is enumeration values of color for t a container class for t vendors and a container class for t types from specific vendors now let's put together some lists of t let's actually build some data that we can work with see how we manipulate this information and so here i've got a bunch of suppliers and some of these most of these are places that i've actually had tea from and they're all fantastic stash is based i think in oregon but um there's a couple of really fantastic ones in here i'm a big fan of mariage prayer except it's expensive so you know when usually i drink it when someone buys it for me as a gift i was not soliciting for gifts for the record i just pointing out more like usually someone gives me a box of it and it's very very good but expensive as we will see so each of these is the supplier we've got the name their location and their url now we know what they're doing we know who's selling but what are they selling and so i've got a whole bunch of teas here now each is broken up by comments and as i mentioned you will see there are some expensive ones mariage freyr sells a white tee that's 105 euros for 100 grams it's a little bit pricey it's worth it but it's very pricey um stash is relatively cheaper you're looking at 100 grams for about seven bucks and each of these is a construction of our case classes and a few other random choices from various vendors if you want to take a look i have put a copy of the code that i used to build this at github and it's bw mcadams and it's slick dash talk and there's a there's a copy of all the code that i use here and you can do sbt run and it will actually run it and print out all the output that i'm going to show you that actually is the example with the running slick as well if you want to play with that so we've also got you know a really nice darjeeling here from palaists which is 56 euros or 56 400 grams because when you go to their website from the us they show you their prices and dollars so how do we connect and manipulate this data in scala hopefully is anybody here not sure what we're going to do next have no idea how i might filter and manipulate data okay well i'm going to show you anywhere so i've created a sequence here i've collected a bunch of instances of my t i've created a sequence and i've saved it in the variable t now scala's type inference figures out it's a sequence of t hopefully everyone's okay with that i'm not going to go into all the different choices we could use for a seek i probably could have used a vector instead but this is a collection it holds a list of t's now it would be the same if we explicitly declared the type what exactly we can do with the sequence is where it gets interesting so first and foremost with working with scala collections we're talking about functional programming i'm hoping by the end of today where at the end of the day everyone's comfortable with this concept i hope that everyone guessed that functions are involved you've also probably heard the term lambda thrown around java 8 is adding them for example lambda usually refers to an anonymous function a function that's not specifically addressed with a value or attached to a method methods are named in concrete scala does have the ability to lift these methods into functions as needed so real quick this is a method it's well defined it's a method cost dollars it takes an argument of t and returns a boolean which checks whether the currency is in dollars or not that's all that it does and i also have a function this is an anonymous function or a lambda i just happen to have captured into a value it still will take an argument of type t and return the into me representing what it does so this is our core unit of work we're using slick and everything else for the manipulations we're going to do and the rocket indicates a function or a lambda the left side declares the arguments to the function and the right side is inferred here as boolean so we can use this to define functions and methods that take functions this is one of the two things that defines a higher order function does anybody remember what the other definition of i order function is so higher order function is either a function that takes a function as an argument or returns a function as a return value and so that's important because this is something that makes it really easy for us to do cool things in scala and with slick so here i've got filter which is something that's attached to that sequence that we created is this method filter that takes a function which takes an argument of t and returns a boolean so i can do that to evaluate finally all the items that have currency of euros and filter is built into these file collections so if we look at this really quickly it's a predicate which is evaluated against each entry for truth for each item in the collection we run that predicate function and we say do you match yes or no and we only return the items that match that predicate and we return a new sequence of t with only the true items so whatever the input function is is what the f well sorry whatever the input type is is what the output type is going to be and then we can pass the function we write in line even so i say valid euros which gives me a sequence of all of the items where the currency is in euros so now i find all the t that's for sale in euros and i could do the same with other currencies as well so this is a new sequence of t we also can lift our method so if any time that scala needs a function and is given a method which has the same argument signature it can lift that into a function so here i've defined cost dollars as taking t and returning if the currency is in dollars and that's lifted up automatically in hopefully everybody's seen this before is at least somewhat comfortable with what i'm showing you now will be the time to run out of the room if you're really confused or at least let me know if you want me to explain further i would prefer the latter so we've got a new seek of t now exists is another one this is again fundamentally part of what we have which is i'm looking at any elements that match the predicate existing in the sequence or not and so are there any items that are sold in rupees as far as i know none of my vendors sell in rubik's you know most a lot of tea comes from india so maybe there's one in there and so i say t dot exists t dot currency is equal to the rupee symbol and that will return me true or false and then finally we've got group by now this is the part where we start to really feel like sql because grouping is a big part of what we do in a sql database and scala has a function called group by that's designed to do just this so with group by we take in a type of k which is used for the grouping it can be any type we want it's what the type that's coming out is going to be for each entry we want to transform it into k so really what we're doing is we're looking at the item and we're turning a key that represents its grouping and then we get back a map that's grouped by the key and then a sequence of all the items that were attached to that key so here if i group by kind i say types of t is t dot group by t kind i'm going to get back a map by t type color and i can even easily recast that as a string this was just something legible on the screen and string string ct and so t entries dot t dot group by t kind of two string keys is gonna be a set of white oolong black and green now we can apply all of these things to doing sql and to having slick take these functions because these are all the fundamental operators that we need in order to work with data can be translated down to sql forms there's one last transformation function i want to mention which hopefully you guys have seen which is map and map is going to give us back a new sequence of b it's a transformation it takes in an item and transforms it into something else so if i wanted to format all of my items i could make the value of prices be a map where i format it so that i get the currency symbol the price and the size so i'm saying it's a hundred dollars per gram or whatever else sorry that's let's create our tables let's actually apply this and really look at slick now there are two main ways of doing work in slick the one that we most often work with right now is called lifted embedding and lifted embedding is very clear that we're writing some special code for the database there's another type called direct embedding which is experimental which when finished will allow you to basically annotate a case class with scala annotations and use macros under the covers to convert that down to code the lifted embedding is fully functional and that's what i want to focus on for today so i've defined a table called vendors and you'll notice that we extend the class table and then we provide a tuple with the type so we have to have all the types that go into the table now the one thing to keep in mind here obviously is this limits our table our number of items in the table to 22 which is the arity limit but it's in string string string and there's another argument to the constructor which is the name of the table in the database to use because there's one of two possibilities the table may already exist in which case i'm going to use my code to read from that table or i have the option of creating that table in which case we'll create a vendor's table everybody okay with that so far so they will already exist we're just snapping to it or not we're trying to happen to it i mean so if you call the ddl create command it would give you an error because the table already exists so it would attempt to map what we've defined here onto the table existing from it so this worked on an error on a table that already exists if you try to call create on a table that already exists you would get an error no this is just so this is a definition in my code okay that says how to map between scala and sql we have the option to create a table from this definition so this can be converted into a create table statement but we don't have to yes this is actually the direct embedding yeah yes so wait no i'm sorry this is the lyft embedding the brain is not working this is the lifted this is the lifted embedding everything's being lifted up the direct embedding using macros so if i were to do a create statement then i would need to go through an evolution go through like evolutions correct now i don't really think we have support for true evolutions so there's not really support for i've evolved my code and with the next version but you do have create drop and there are ddl interaction commands built in that work here and you know and evolution may be something that we add in later yes can you talk unless you're going to get to it can you talk about the difference between extended table of string string string and string and string string so it's the order that the um the order the columns are defined in and i don't know whether it's going to make a difference i think it's going to depend on the database some databases are fairly strict about the tastes what's that i feel like yeah i'm actually not sure on that the best bet would be probably to email me that question and i'll get stephon to get back to you i i don't know actually um i would do it in the order that's defined in the database table just to be safe that's usually how i do things to exactly you're describing which is some level of unreliability now each of my you'll note here that i haven't defined vowels but instead i've got depth so i'm defining methods and this is that part where we talk about this being lifted embeddings these methods will be lifted as needed into functions so i've defined id and to define that i actually call a function called column with a type argument so i'm creating an int column on the field or the column name vendor id and i'm also giving it an attribute called primary key so this tells slick that this is the primary key that also means that if i'm going to generate a ddl from this it's created as a primary key we also have name which is a simple column string vendor name we have country and we have url and in this case i simplified things versus my original scholar example url is just a string now because not every database has a uri type and slick doesn't fully map that i didn't want to get into custom types but the last thing that needs to happen and if you played with scala query you may have seen this before is that in order to tell scala because there's no macros in use here what fields are available we define a special projection method called star that's an asterisk symbol and that's going to be each column combined together with a tilde operator and that's that final step that tells scala if i was to basically do the equivalent of select star what columns are included in select star and that's where that mapping is if you think in terms of select star that's what that is star has to represent every column that's been defined inside of this table not with the lift embedding because there's no macros or anything else in place to figure that out with the direct embedding that's coming down the road in the next release there will be some ability for that to happen automatically so basically based on this definition to understand what the table structure to generate required sql query design is correct we have to tell slick a lot more than maybe down the road you might have to as we evolve the product does this also cover foreign on the next slide yes so this code uh basically creates a table right because we specified there that the first field is the primary key you can you can use this to create a table if the table already exists yeah you can use this to just read from that table okay but i think we're repeating ourselves when we are specifying the types in the uh in the table in the brackets and then below we enumerate all these columns again because if we need to add one more column we'll have to do to make two changes that's true and that's because with generics and scholar we are we don't have um in java you can ignore the generics and in scala we are required to specify the generics and so table has there's really table one through table 22 where they take the type arguments and it's it's essentially a requirement of the type system and again these are things that as the product evolves we're looking at the direct embedding there will be solutions for making it easier to do that but right now you do have to declare that in two places three three three so let's look at our t's table and again we have object t's that extend this table with our types and we have our table name now obviously everything else is fairly similar here we've got our primary key we've got our star but here's what's interesting i've defined vendor id so vendor id is a column event called vendor id and that's going to hold the actual id from the vendor table but there's an additional method that i've defined because when i'm working in my code i don't want to have to manually resolve vendor id into a vendor object and so i can define this method here and this is a reified foreign key relation so we can navigate this to create a join automatically so what i say is that basically defining the field vendor and that is a foreign key that in the database if i was created is going to be called vendor underscore fk that relates to the vendor id column and the vendors object so the vendor id column matches to the vendors table and in order to resolve a vendor i use the id field from the vendors object so underscore dot id says from vendors get the id for you and so now if i call vendor i will automatically resolve the vendor behind the scenes and get back an objective vendor is there a question in the back there yes you don't have to have that actually behind you real quick oh no i was just wondering we don't have to write this do you have a tool that automatically generates this from the database metadata um there is some work being done on that i think brandon hudgens did a blog post recently that he's been working on a tool to generate slick ddls from existing tables was your question you don't have to have that vendor fk object actually name that in your database or no this is how i named it okay it's been a while since i did a lot of work with sql so i'm a little rusty with my you know names and i mean i like making dbas unhappy so i probably did that deliberately but that would be if you were to create the table from here that would be what it would be called i it doesn't need that in order to resolve it if the foreign key already exists really the important part is saying that the vendor id column relates to id on the vendor's object it can be used to pull things up so let's take a look at sessions and ddls here and i think the code's a little small i apologize so there is an implicit called thread local session which keeps our session in a thread local and scoped to the current thread which we're importing at the top now database.4 url allows us to construct a driver and connect out to a server and that's what i'm doing here so i'm using jdbc i'm using h2's memory driver and the test one database and then specifying the drivers org h2 driver all the fun of jdbc is still slightly exposed here and then with session actually is a loan pattern that everything happens within that session so inside with session we have an implicit session that gives us certain objects that let us work with the database but outside of the with session method you don't have direct access to that data so the session is never dated explicitly it's bound to the current thread using the thread local session that we imported and we can create the tables and that includes the primary and foreign keys and you notice here that i've done two things i've said vendors.ddl plus plus teas.ddl and that will return me ddl will return me the constructor statements and i can combine those together and then call dot create and that just says go out and create the sql tables for these two items and then i insert some suppliers so all those suppliers i showed you before stash mariage frayer etc have all been created with vendors.insert and in this case it looks like an insert statement vendors.insert and each column is defined so one comma stash comma usa comma stasht.com and we can also insert some t now here we can use the jbc batch insert feature if it's supported by the database otherwise you would have to do individual insert statements so at the bottom here i've actually done an insert all and i apologize it's a little small on the screen insert all and then it's just a bunch of tuples to define the columns and these are all bulk inserted directly into the database forming and you'll note um in this case i've got their jealous state golden tip and then one which is the vendor that sells it so this is sold by stash black dollars 100 grams and yo now all of our data is in you yes so you do have to point at it you couldn't somehow load the object you could if you wanted to i mean if you had an instance of the object you could easily do that as long as it resolved to an end but for quick and dirty creation here i whipped it up with how i would probably write a sql statement by hand if i was to create all the data now basic data iteration looks and feels quite a lot like scallop we use the object query and pass it to the table that we want to query again so in this case query teas is going to give us the ability to query against the teas table and now i can actually do it for each iteration in there i say in the case that the item you get is name comma vendor id comma kind currency price size so i extract all of that and i'm just doing a print here i'm doing a println with some formatting to make it look pretty on screen and it actually looks a little uglier on the console but i formatted it so it's readable so essentially i'm this this data came cut and paste from actually running the sample code that's up on my github and it spit right back out onto the screen and so i've got each item i've got the name tab formatted vendor id the kind the currency and price and the size so this all comes from a simple iteration that all i really had to do was put a query call around my table and then i work with it almost as if it's a normal scholar collection so why not let the data is do the string conversion and concatenation for us that is possible if we want to if the database supports it and so this is a little more complex but i basically save in val q1 for tt's and then i yield const column so const column is a shortcut in slick for anything that's declared as a constant so if i say 5.0 that's a constant column of 5.0 that's a constant statement that's passed into the database in whatever way is necessary so the const column open quote space close quote of me adding a space in and i'm just tacking each field in so t dot name plus plus tab plus plus t dot vendor id as column of string now that lets me convert so t dot vendor id would come back as an int normally but you can't do a string format in the database within and string so i convert that over to a string this is using a standard sql cast and then ultimately i do a four h and print line my results and i get back the same thing relatively and that comes right from the database now resolving will give me relationships exception of something if i would say currency is now i think it depends on the database um how the database would handle that because this is the same as doing a formatting in the data and i think mostly i don't remember if databases will throw an error on that being null i think they'll just put them in as null or as blank it's going to handle so under the covers we're using the jbc drivers so it's going to handle things just as if you're connecting directly to jdbc at least as far as an exception being thrown isn't going to be changed pretty much when you apply function or something wouldn't it be like an option there is an option and i'll show you how to do that as option later um i don't know that i would recommend doing formatting because exactly what sanjay is pointing out null it could throw an error or something else but you know the more important thing here is to look at things like the ability to as call above the fact that you can drop this stuff down to the database if you want to but you also can do it from scalp i mean these are casts though right they're sql that's the most important thing the casting is happening here in the sql database not in scholars so the sql database is going to determine how to handle asking to cast say a null item to string that should affect your decision as to whether or not you really want to use this style of pulling data from the database or if you want to format it in skull it may be easier in scala to determine whether a column is null and formatted appropriately so to resolve relationships so for example i want to perform a joint i want to get my t names back and my supplier names for all the really good stuff regardless of currency so i'm looking for anything that's more than 25. no matter what currency it is i'm pretty sure none of the stash stuff will show up here so i do val q2 and then i have a four comprehension or four expression where i get t and i'm on the t's table because remember in a four comprehension if you produce an errorable in it'll pull out each item one by one so t's if t dot price is greater than 25 so whatever currency it is we're only allowing the next item in the iteration to happen if this is past 25 in which case then we look at the vendor so we get each vendor out if the vendor id and notice the triple equals here for the same for those of you who sat in on bill vendor's talk he talked early on about the fact that there's problems with using single the single double equals not single double equals with using the double equals it's not a single double equals it's just double equals uh because some of it is deferred to scala and it does simple comparisons and so in a lot of cases including slick you will see a triple equals which allows the developer to more closely define how things work my understanding is with the upcoming direct embedding which is macro based equals equals will work because we've got control over how that's evaluated but there is a triple equals here so i'm asking if v i e is triple equals to the t the t's vendor id so i'm matching up i'm doing a join here under the covers and then i'm yielding t.name and vendor name and then for each item in the query i print line and i get back a result like this white t from beyond the sky is supplied by mariah freyr etc and so these joins it doesn't look very much like sql does it it looks like we're manipulating some scala data objects and that's generally the idea is that convert to an efficient simple statement it does and i believe you can call dot ddl from it or there's a dot sql that will print out the sql statement that's going to use how does that work each driver actually has its own compiler and with that so that allows each implementation for each database to generate efficient sql from the slick ist so you could do the profiling on can you see the value of passing through it what i'm getting you can so there is a dot sql call i believe that will spit out the sql statement and then you know everything could be tuned in the individual driver so does price define greater than well price is so i mean the way this must work is tees has a flat map oh then there's a filter right it's a filter but it's going to be filter must be past something that's not just a function something that can be compiled to have my console open look at this think it's spot simple i'm saying he's a little cute he looks like sande has been working with the actual stuff more let's take a look because the easiest way to answer the questions let's take a look at what it generates now i will show you some of the diagrams of what's happening under the covers and and specifically each driver has its own compiler that's responsible for turning that slick ast into sql so here's my sql statement select x2.tname x3.ender name from ts as x2 vendors is x3 or x2's price is greater than 25 and vendor id is equal to vendor id that's it that's profile so all that's all you have to do is call select statement or whatever else is under the covers and you can see exactly what it's doing like i said because each driver implements its own compiler the other thing you don't get is i think there's a track that a lot of orms fall into which is that they generate one sql statement for every databases you end up with inefficiencies that usually drive dbas a little nuts so the idea here is that each database should be able to choose for whatever it's doing this is what i get for dropping back the console there we go sorry about that so we could do the same thing just using the navigable foreign key so notice the difference here here i said if v dot id is equal to the vendor id here i'm just saying if t.price is greater than 25 and i just iterate to t.vender and under the cover slink is going to do the same thing it's going to figure out based on what i'm saying that i want the vendor to match so only pull the vendor out if there's a matching vendor this will compile almost i think it compiled down to the same sql statement this is an efficiency to make it easier for us to do this kind of work this time i want to read the results into a list instead of just printing them and so i can just i can say q3.list and i'll get back a list of whatever the result set type is which in this case is a tuple of string and string and my print line will look the same oh of course my next slide reminded me of exactly how to print the select query sorry about that so it is dot select statement we'll print out whatever the sql that's been generated is that sql is generated by your individual database driver and so they have opportunities to tune for whatever that database may be so in this case it's going to look exactly of saving myself the trouble of having dropped back off the console um we're going to get the select statement here and it's the same statement here as it was in the last one because saying vendor is the same is actually manually resolving it a little later in this um i will show you some examples of doing left joining our joints and everything else so real quick there's a couple other pieces of computation we can do i can compute the number of action t's in this case i meant to update that the number of t's offered by each supplier so i say i just iterate over each t and then i do then the vendor match and i yield a group by statement so that's a scholar group by similar to what i showed you before that under the covers becomes a grouping in this in sql that then groups for each type now note that there's a dot get here and that's because slick can't enforce statically that the supplier is always available because it's a non-nullable foreign key so we wrap it in an option so you do have to call get because the foreign key doesn't necessarily have the foreign entry in the other table it's always an option and so here we do our printout and we have four t's from stash one three from mariah etc now why do we want to do all this i guess that should be part of the question and i wanted to show you what slick looks like before i try to convince you that you need slick and you know one of the things is ted neward has said that object relational mapping is the vietnam computer science and there's right up there it's bit.ly slash orm-vietnam if you want to read more about what his thoughts were um they were encoding having worked i spent the last three years prior to this december working on mongodb um part of the nosql movement and spending a lot of time moving away from relational databases so i can sort of see it and i think the reasoning behind why things are happening like people are moving to is that our databases and our code have come into conflict they don't really play nice anymore and that move is changing and it's not just for scalability but for data interaction one of the reasons people love longo is it's a bunch of json and they can work with that really easily in python and scala and java and whatever else they need to do they don't have to learn sql or anything complex and part of it also is we want the way that we work with our data to match up with how our data is stored and that gives us a really big mismatch between sql and object-oriented programming and even with functional programming although functional programming in some ways is closer to sql something as simple as a field projection give me a small set of fields doesn't match up well when you've got an object that says these are the fields that are part of the object say a case class you can't project only part of those think about now why in the lift and embedding i define everything as a def it's not a vowel because i want to be able to pull out each item as a projection however i need to and so we usually end up building fun new ridiculous dsls to give us some way of querying up from our old world so if you adopted hibernate you got away from sql and instead you had to learn the hybrid query language it wasn't really an improvement and it sort of showed me that somebody was either having a really big joke on us or missed the point for both i mean that's the thing we should have to learn a whole new language the idea with slick generally is you should write scala code and you should move forward with scholar code so the functional language that we use to work with scala collections maps cleanly to how we manipulate these collections we have a couple extra operators that we need to do sql stuff so i don't know why there's a button that turns off my screen on my clicker but there is so i have a relation here supplier is a relation it's another object that i want to point to currency is an attribute or a field and anybody want to take a guess at what that seek is okay it's a table because each entry is a row is the equivalence of a rub that's all that our table really is is it's a collection of rows in this case it's a collection of well-structured rows so this is a seek of t which means that there's a type requirement that's enforced by the database that actually is logically fairly close to a generic system you have to meet this requirement of typing except we don't have we don't have uh inheritance and other things in most sql databases so the biggest problem is that sql doesn't compose and there's that word it explains most of it because what's great about what i'm showing you is i can compose functional statements but we can't really compose sql we can't easily add manipulations onto an existing query you can't chain operations in a sql string you would have to rewrite the sequel and if you're lucky you don't create a cartesian product in the process so the other problem is that generating sql via string manipulation which is traditionally how they've done it and i'm going to bet everyone in this room at least once has generated sql and probably written at the library that they released some thing i think there's probably at least one library on the pearl cpan that i wrote that i was probably very proud of at the time one of the things that happened and what's funny is actually there was a spelling mistake in this slide that i fixed earlier today spelling mistakes and type errors are not caught at compile time when we generate sql so if we misspell select we don't find out about that until runtime we may as well be running python or ruby at that point because we're having the same problem okay part of our code is compiled but our sql isn't type checked and we lose that benefit and so that's something that something like slick can help with which is that we can do this generation in checking in a way that guarantees to us that we're generating something that's valid and the sql injection is generally the pain of every developer either you spend a lot of time up front trying to prevent sql injection or you spend a lot of time on vacation logged in remotely fixing whatever somebody did in your database because you didn't protect against sql injection and so generally one of the ways you can fix that as well hopefully you trust someone else to build a library where you have the benefit of it being an open source library that everyone else finds mistakes and contributes them back to one source we're not all reinventing the wheel and i can't talk about sql injection without little body cables um and i'm pretty sure that i don't think you can actually put sql on a birth certificate but um you know if you could it would be a fun experiment to try i do know uh the pearl mongers which is the user group for pearl originally tried when they originally registered in new york state they were supposed to be pearl mongers with a regex it was mongers or mungers and new york state refused their corporation application saying that there were invalid characters in it which is how it became just prolongers so i bet they probably have the same rules for uh birth certificates so let's look quickly at the types of sql interaction we've been looking at the course like api which is the lifted embedding it is possible to do other things we can invoke stored procedures we can call raw sql but that is a topic for another time i think if you guys have the fundamental concepts of slick you can run off and play with the documentation and figure things out and the docs are up there at slic.typestage.com but i want to take a quick look at how the lifted embedding works and actually changed out this slide because there's a new diagram that stephon put together that also shows how direct embedding works so this is both with the direct embedding and the lifted embedding so the lifted embedding converts to a slick ast that's an abstract syntax tree it's sort of like a raw baby programming language before it gets compiled down into bytecode and the query compiler converts that into an even more refined version of the slight asd so this is things like refining types checking for errors simplifying potentially complex statements it's multiple phases just like the scala compiler does if you ever look at the output of the skull compiler from phase to phase you have some pretty hairy coat at the beginning but by the time you're done it's scraped off a lot of the mold and other things to get you down a cloud state and that runs in the executor which goes to the database and returns us our result now there is also the direct embedding which is experimental and still being worked on and that runs at compile time in scholar rather than the run time that we get with the lift and embedding and that uses slick's macros to create a scholar ast which is then run through the query compiler refined executed and given as a result generally a bunch of the things that you guys ask me questions say wait i really have to write that code myself we'll be handled in the direct embedding so the left in embedding let's look real quick again at a statement in lifted embedding where we say t's if price is less than nine and then the supplier we yield the name and the uh of both and then we're on that because i think the original example is coffee and i didn't swap out the variable um sorry about that that should be t if t dot price um but we get a table query we get a tease object we have a column double there's column extension methods less than column double and there's a whole bunch you can sort of see the picture that i'm building here which is that these all compile down to things under the covers in scala which represent objects that eventually can be converted into sql by your individual driver and eventually this will work with as well by converting down to a longer query which doesn't use sql and ultimately we get a query of column string column of string which returns a string string so what is slick doing to compile queries first of all we have immutable asts these are types that can't be mutated until they're observed i'm sorry they can be mutated until they're observed so you have the ability to manipulate them until you try to use them there's an immutable compiler state and this means that the ast and the phase output state can't be changed and the phases just like in the scala compiler transform the compiler statement so we go from step by step we mutate locally that state to create a more efficient query and the drivers provide their own compilers and like i said this is really important because this means that we can write efficient queries for a given database if there's some dirty oracle trick that one of you guys knows and you look at slick and go hey slick queries are really inefficient you can fix the oracle driver without having to change all of the other ones or putting a big if block in and so that's broken down to a level where these things are separated and as a rough glance you can see that there's a series of compiler phases they generally break down into clean up flattened columns shape the sql and generate code so so if you want to give a hint to that to that database say you want to modify an existing compiler of what's the api for providing that hint that you want to perform particular optimization there are some raw sql um things i don't know off the top of my head i would have to look back at the docs but there is ui there can be you can either update the code for the driver there is some ability to generate raw sql so you may for example choose in a particular place to replace an ast call with actually just passing raw sql in until you get an optimization place i was just wondering if you could take a drive that exists and modify a sense that you can add it here and say um you can manipulate the code for the individual driver because that is the compiler that knows how to take that refined state down and see so in your scala how would you express that i believe you'd have to swap out the actual driver code as the thing stands right now so you might have to provide an additional driver or not modify the exercise yes what's the relationship between sleep and anal i don't know that i'm entirely familiar they norm it sounds familiar but the enorm is a kind of standard database based access layer of play which is basically based on sql and from sql you generate objects so i know long term slick is part of the type save stack and so what i would expect to see is that we're going to eventually have play migrate over to slick the database layer um so whether a norm will go away or not i would be the wrong person to ask james before the right person to ask that question he knows the the product direction right now yeah so i mean i don't think there's any plans for a norm to go away but i do know that as as slick evolves and as the 2.0 release comes out you're going to see more of it demoed with play and integrating with playing available for you it would be interesting to know why you know the direction of the decision to replace and eventually well like i said i don't know that we're looking to replace it i think it will probably augment and sit alongside of it but i would speak to james about that to get a good idea of what direction is it no not jv but you might know what's the plan for a norm in the long term or were you replacing slick with it in play or they just sit side by side no i think he'll be plugged i think you both i don't think i can be wrong jamie's higher on the food chain than me so you can blame him for the wrong answer yes and i have a question at what point do you think you'd be able to use um asynchronous so that's that's something we talked about a little near i'm almost done but um that is something that's on my roadmap after i'm done with the nosql stuff um we have been talking about this idea of pure asynchronous data access i would say probably in the fall would be when we're looking at it's almost the fall actually i think probably late fall or start to play with that but i mean the road map is set by someone else what i necessarily see in the next few months may not be how it is but it is on our plans for the next couple of cycles that we do want to have potential jvc level access like that for us there is can we hold that off until i'm done i just want to go through a couple more slides i just want to real quick show you guys a couple more examples of using slick so you have something to take away with um here i'm doing a sort so i'm calling persons.sort by name i'm dropping five and taking ten these translate down the sql queries i can group by the number of people per age so mapping that grouping by the age field again this looks a lot like ross scala code that will compile the sequel i can pull out the first entry if i need to so find me the persons who have the id3 first one we do support excuse me unions so in this case i'm creating a union of all of the persons whose age is less than 18 or greater than 65. and we do support null as well and so if you look at this we've created option columns an option is a translation under the covers for creating a nullable column so in sql terms that's a nullable column but we want you to map a column that's nullable with option because that's good safe scholar that gives you something that you're not going to get null back from the database and so if i was to do a ddl create here what i would create would be something with nullable field but it would be translated as an option in my scholarship now i'd have to check that finally you guys were asking about outer about outer joins so this is an example of outer joins left right and full now i've got a tasks column and a person's sorry a task table on a person's table and i'm doing my join and i'm just specifying past outer joined persons on so it looks a lot like the sequel now these question marks defined here are because it's possible with certain joins like the right hand side of a left outer join or the left hand side on a right outer join or both sides for a full outer join for us to have null values if the join didn't match it's possible that title and name could be no because there is no left hand side or right hand side or anything else if you've worked with these kinds of join you've seen that and so dot question mark tells slick i want you to return me an option of the type rather than the raw type just in case it's not and there are a number of column operators that are additional for doing all sorts of fun sql things a couple of other quick features there is auto increment there are sub queries case statements prepared statements custom data types are possible for each iteration which we talked about and a lot more well cursors are implemented as fundamentally they're part of slick they are you know when you get back an iterator from aquarius you are using a cursor on the covers and then depending on the driver implementation whether it uses the native cursors of the of the database would be really up to the jdc driver so there are some new back-end architectures that are available in the 2-0 milestones this includes type providers using tight macros this is going to greatly improve some of the stuff that seems a little overly expressive like declaring our table types and other things will be possible to automatically infer as tight macros come around and i think macro paradise is due for 211. there is distributed query and if you're going to be a java 1 i do highly recommend stefan's talk i've actually walked through his slides for java 1. he's been talking about the direct querying system i'm sorry the distributed querying system what that actually lets you do is join against multiple data sources so you can have an oracle database and a sql server database and you could do a join between the two and we'll emulate that join in code and fetch the data from both databases and that's something that's going to be available in 2o and ultimately once we get longo in place we'll probably implement joins in emulated using the distributed aquarium code also coming soon as i said equal support and we're talking about this reactive and asynchronous database support this is something these are two things that i'm working on that i'm excited about that i'm hoping to have enough time to really put together um and so you know it's something that we also we're open source so we actively encourage contributors from the outside if you have ideas or thoughts please jump in get on the mailing list tell us what you think and thank you very much there's also uh the stick website is slic.typesafe.com our website is typesafe.com as you may have guessed and i know we jumped a bunch of questions in here we probably have a couple minutes since there's no other talker coming on after me does anybody have any other questions yes there are no there are no plans to introduce passion like that i don't know to be honest i'm not aware of any that doesn't necessarily move me out um i haven't heard anything about them i know like sort of the immediate future of the things that i just mentioned which is the tight macros the distributed aquarium system a lot of that is driven a lot of this is driven by user requests and customers that are working with other things so hashing is something that's interesting i i was just posting something in the mailing list and let's get a discussion started and see if that's something that makes sense to get in there i think usually caching is something that's separated out but it might make sense because it's part of the nosql stuff it's also possible if you look at more cache like things like them cathodes and use digital querying or something else multiple databases is handled by the distributed query system that i just mentioned so multiple databases with distributed aquarium week which will be in 2o we have the ability to pull in multiple you're talking about joining multiplayer that's that's just a simple that's just um when you declare your session you specify what host to connect to and what database etc um what was the other one before connection pool would be handled by the individual driver i know we do support the c3p and bone cp under covers because that's a change to see things you can specify the jdbc connection string use those and you can use those the same as you would in java yes transactions yes there are transactions board actually sanjay's been playing with them lately uh there's just a with transaction function right instead of with session so you just say with transaction also all of the jdbc uh functionality applies so you can set scoped at all you know thread level and other things transaction stuff just like you can at jdbc but there's that loan pattern method with transaction available and you have all the support that you would in jdbc for transactions available on stick yes you know support plan for a little bit of these store procedures for which for i have to use storage procedures to get this because there is support for calling store procedures i don't know how hairy it is so i don't know whether you could i and i've i worked in a shop like that where all data access was written by the dba and you had to call functions and i'm sort of picturing exactly what you're asking which is you'd like that nice table definition to map to a sql storage hall i'm actually not sure um i will ask about that because that's actually interesting and i've like i said i've worked in a shop like that where we had that as a problem it was one of the things that made it really hard when we moved over to ejb was that we had no ability to customize any of the sql tape and dda got very mad when he was told look we're moving to ejb whether you like it or not you've got to get rid of the stored procedures because he saw them as his way of auditing and making sure people didn't make mistakes any uh one more question here anybody all right thank you very much guys you