Functional Database Strategies

scala.bythebay.io: Jason Swartz, Functional Database Strategies

Recording: scala.bythebay.io: Jason Swartz, Functional Database Strategies

you hi good morning everyone I can't believe we're all here this is great I'm not even awake yet let's see where's a good place to stand this is a good place to sand hey everyone so i'm jason schwartz i want to talk about functional data based strategies today but not really talk about the no q i want i guess ql old-fashioned databases relational databases mice mostly talk about most things on postgres i know that there's a lot that you can talk about with no sequel and making them immutable and workable functional programming i'm going to take a different tact assume that you're going to work with old-fashioned relational databases so i'm jason schwartz I I tweet probably too much now as Schwartz rock I views databases number of different companies I've been that it's only more recently that I've been looking at the functional aspect of working with databases how to make them immutable how to make sure that you don't have mutable state and that all the lessons you learn in Scala and the functional programming languages that you can apply them for databases I also like to talk about scholar which is why I'm here this is not so much of a scholar talk this time but I love talking about scala and helping out its scala by the bay so that's why this is going to be a not super scala talk but it's going to be a functional programming database talk here all right so the main strategy functions for functional databases is by yourself a functional database I'm just kidding there's no functional duties okay but databases you know they're mutable typically we think of them as you know you store your state in there and you get it back in fact you can have a nice stateless instance and have totally immutable data let the database do all the mutating that you need I mean that's typically the way to do it but well can you have a mutable data in the database I mean can you use it like that so we're going to look at these different areas we're going to see maybe you can have applied the functional programming knowledge that you're experiencing as you develop Scala to your database we're going to look at being able to lock down your rose you can't touch them at all we're going to look at the new windowing functions you have in sequel 2003 and later versions are available in Postgres and other high-end databases will look at may be storing state doesn't even the right answer then we'll look at some ways that people manage to handle their database transactions in kind of a haskell way by wrapping things and separating them out to avoid to avoid errors with shared mutable state so all these areas are are functional it may or may not be functional programming but it's an interesting way to think in terms of well you know we like immutable data we like functional programming let's make sure we don't forget about our database or you know by a functional databases wherever that is all right so amitabh ille tables well I want to start with an example so let's say I have a okay roll like developers here we love you know JIRA and bug reporting and stuff like that okay so let's say I have a nap my app is called tasks and I'm keeping track of a few different bug reports the high-end functional Scala you know type of experience I have is a big monolithic web app and a database that's my model don't tell me what about this and let's say I have some basic like this you know i'm using post gross well this is not hard to do a lot of people do these kind of will have AP is that rap databases i mean you know even as good as we are at the engineering we often end up running one of these somewhere or or many of these so in this case i'm modifying issues well what happens when i modify an issue what happens when I do the create read update well in this case can anyone see the top part and the bottom part all right great so let's say we have post coming think talking hdb now well that's great we now have this new database entry here and then we go ahead and update it and said I have assigned it to to you for example which you know people love so oh we have assignee that's nice and you know then we mark it as done and we're good you know so well this is not very hard to do is there anyone who hasn't done something like this already alright ordering we had who hasn't had enough coffee okay me neither great coffee i love the coffee so this looks this is okay right I mean it's pretty standard but how did this table get this way I mean look at this thing it's a table it's a two boats it it's a collection of it's a single row of the collection of data there of different types but how did you get there I mean how can you look at a row like this and extrapolate up to the events at the top well if I take away the events at the top how do I know just from the database how I got here I mean it's really hard isn't it you can't really say oh yeah that's how I happen to get to this fun collection of items that makes up this table row even if I show you the events that led up to it can you figure it out I mean does anyone notice anything wrong here other than really dressed too nicely a thing for scala de venta it yeah signees no fantastic good catch how to get nawl i don't know we were mutating the database we have no idea how that happened i mean that's that's kind of tough when your date when your rows are mutable you don't have any history you don't know what how you got there but if you make the table rows immutable you're going to have a whole history of everything that happens in your database you're going to actually have you're actually going to be able to see what happened and keep all the same functionality so let's say maybe we don't want to be mutable what if insect every time we do something in the database we just throw in another row well then we start out with a post and as we do a put we kind of add another row instead and then when we do our final book we look at this and say whoa well what is that I don't remember putting that in obviously someone threw this in right so now I have some kind of history here I could see what okay 1819 someone set the assignee back to nawl weird was this an accident where to come from did it come from internal job can I look in my logs either way I know that I'm not necessarily crazy because of this this actually happened something actually changed it you know I can actually tell when it's time to actually get it you know it's not very hard i can just do a DB query that says look just give me the latest one by issue one so all my you know creation and updates work fine when I want to get point back I can just get the latest it's a much better database model now each row is unique is sr taxi is immutable and untouchable i have a nice record of everything that happened now in my database all right here do you say do we have a subversive guy here all right just came off of that we're gonna keep working on that all right that's good so your tables are immutable of course you can keep inserting all you want but each row that you have every tuple should really be immutable there's no reason that you should have to change this in other words you should be able to put stuff at the end of your table but there's no reason you should do anything other than that well you should query but the only change should be append only so how do you make one well the big thing is if you want your table to be append only don't let users update and don't let users delete it's that easy this is postgres there's probably any I wait there's a way to do this in my seagull but it's a little more work in Postgres if you say I'm giving you select and insert access it means you don't have any other accesses you can't do anything else I think you can still create tables but you can't modify this table now you're a lockdown if you do this now your app won't accidentally mutate anything at all so that's it no there's not I think about the right columns to have here okay I i have this but you know it looks a little bit funny what i really should do is that thing you know consider having more than one way to look at a row again i have an ID but it's weird to see an ID with multiple entries right what I really want is I want to consider this thing to be its own entity with four different versions of it I still want to have a row ID I still want to say hey I've unique identifier but I also want something now follow this all the way down so look at this one I've added one column here issue ID saying you know ID cereal which in Postgres means it's an int and you should just generate it whenever you put it in there cool my table now has unique rose identifiers but I have an entity ID I have something called an issue ad which means based on this when I do an insert it'll pick a new number and then every time I update it I say hey I want to update I want to insert and the issue idea is this and then you know I have something that tracks all the way down you can see I have an ID here I've unique identifier but I also have something which tracks my entity all the way through so now have two different kinds of identifiers the nice part is if i want to say hey here's my issue i'm going to send an email out or I'm going to link it on this side door i'm going to show it I can use the first column because that's immutable that never changes I can make it global I can share it with anyone the issue ID is mutable I don't think you should share that with anything way anyone else that's that that's going to change so if you have to say if you're going to build someone or if you're going to do something if you're going to do some kind of reconciliation ya know that issue ID the latest version in the database it may not be the one you're working with but if you use the ID you know that's never going to change it may not be eventually consistent if I say hey I'm looking at issue with row ID three I know that that's not going to change behind me at all it may not be the latest because it's not really consistent with the database but it's safe I can refer to it I can pass around other people can come back it's never going to change anyways that's the part about row ids versus issue a tease are there any questions between the two send in more coffee you please no I'm just kidding getting the latest island then is not really tough if you have an issue 5 an entity ID I could just say look give me the latest item the most recent item with that entity ID it's very straightforward this is very basic immutability in table rows this doesn't work for everyone notice that I'm just getting a single item back if you want to then do be a little bit more advanced like you want they'll say hey I want the latest of a bunch of different items you're going to want to use something like window functions so the sequel 2003 standard makes it possible to change the regular group by and have a whole suite of functions to act on whatever you group in a database which is which which works great these are available in Postgres as of the latest my sequel they're not in there I'm sure I believe they're in Oracle and some other commercial databases as well so you have the ability to do things like well I want to aggregate i want to say hey for everything and if i'm going to go through all my if i may go through all my issues i want to kind of correlate them based on assignee and or if i have sales and want to say rank up i want to get the sum of all these per state for example so you have the ability to aggregate a large number use the over function to say here's how I want to create a window in my group buys and then you could group by different ways such as partition so these are these are sequel 2003 it's been about 13 years since and I wasn't familiar with these until recently but I find them really really useful okay so here's here's my here's my database again I can't took out the date column because it's it looked a little messy so you can see here I've to it who actually have two issues right and each issue i've modified so the table IDs are you know Roy IDs are great one two three four but I only have two entities here how would you get the most recent how would I say hey give me all the most recent issues and select the most recent one of each well then that's that's a little bit hard that's not just ordering by date this is an immutable this is a table with immutable rose I will never change any of the Rose I've just commenced your changes here so here's an example query I know it's a little bit complicated using when using sequel 2003 windowing functions to say I want to actually separate things based on the entity ID so the Corvette is saying look I have these issue IDs create little windows for each issue for age entity ID so for example there's two rows and in this case saying look for all the entities with with you with with for all the rows imagine entity ID order them by ID descending which means the most recent one is first then I want to say look I want to get the row number of it so the row number of the most recent one will be one and then the second holes will be two and then I want to limit those two issues were you know maybe only created in last week so I'm using row number which is the aggregation function just as throw in a new column that tells me the ordering i'm using over to say i'm going to create a little window and i'm using partition to say or group them by their by their different issues so what I get back from this is when I'm saying I just want the first row well here's what it looks like when I don't filter out the row numbers when I do call that function I don't say we over row number there's now a row number and it's saying look create a little window for each entity ID and order it by ID descending so I d2 is more recent than ID 1 so that gets the first row number and similarly for the other entity number roy d 4 is greater so it says that's the wrong number so then I just say we'll just give me row number one for all these which means giving the most recent entry for each item partitioned by issue ID now so that works out great I now get the most recent entry why don't really need row number because i'm going to not going to access it and now I have everything that I could do with the mutable database I now have a very easy way to say hey for each issue give me the most recent item in here I can use this for grouping I can use this for for joining I don't have to have a mutable table on how I can use these functions to create these virtual mutable looking tables that are really immutable and are safe to use all right so that partners window function any questions about this yes sir I'm not a trivial partition I'm practicing the same thing that partition by good it's it's a well it can be like for example if you you can group by two things so you can you can make that part a bit harder the base the main thing I like to do is I show the road number so I do the queries and it's like well what's the ordering and I'm like oh that's how it orders it because for example sometimes I order it and i'll i'll change the ordering you can have case statements in the ordering so you can do something similar to scholars match expressions inside the order that says hey does it have this flag set yeah okay we'll put this first if it doesn't know just use the regular order so it's really it's really interesting this is this is the functional part of the stages race change I was there another question kitchen I'm just wondering you're doing this yeah what you go to a materialized view or event sourcing makes sense I usually don't like to use because people forget them and then can't find them and then you're like where is this thing coming from which I've done with legacy systems if I i would say running examine on this make sure that you're hitting the indexes there's no indices there's no reason that you can't hit all the indices with this when you do the partition in this case i'd make sure that issue issue ID and ID are indexed separately so that you'll hit those correctly that access but I I mean you know with with both my sequin posters it's pretty easy to say tell me how the square is going to run and then you can get a good feeling for how it's actually if it's cashed in when indexes it hits or if it does have to do a full tables for example and i'm pretty sure in Postgres you can say look don't optimize for table scan assume that and just want you to try to hit the indices and then you can get a good feeling for that I can't believe I'm talking about good feelings that's a science it's a science no but when you try this out show the road numbers you know there's no reason why you can't add this in and return it you don't have to actually read it but it's a nice useful way to say oh ok that's all my queries ordering things I only want the most recent one but if you let you get it lets you experiment and try it out and see what you're actually getting back and again the great thing is so we're using this a clasp as we added the ability for people to buy bundles of classes and we said well our bundles are going to change we're going to have marketing people keep changing the prices so we have the concept of kind of a bundle but we use roi DS so when we show people a bundle to sell them we say don't get used the mutable one use the row ID so we say hey this is now available for thirty bucks and if marketing says hey that should be 25 bucks it doesn't matter we're showing the user the row ID when they buy it they buy the row ID we said look even if you change it later we're offering you the immutable one because we don't want to switch things around so this is the basics of windowing functions i find i find they're really interesting they give you a lot of you know especially with summing with averaging with the ability to say hey I don't want to do a really tough group by I don't want to do sub-queries I just want really smart functions when I go through my data alright we're halfway through how are we on time we get so we're well I'll just assume we're good all right so we talked about state so far which is great but what a lot of people have is not really state in their system do they a lot of people have events happening is it is our people will pre from now with the term event sourcing cool is anyone like really tired of it yet okay good then I'm going to talk you through alright so you've seen this right here's my here's my little HTTP actions at the top and here's the table that gets generated at the bottom we're really you know these are that happen at the top and they result in state hopefully it's mutable across the table but each row is immutable but still I'm storing state here so i'm having like hey create this issue which then gets put into a created issue and then when i do a sign and complete it gets updated no it doesn't it gets inserted because we're using immutable tables yes mutable tables are great okay but i have these events and i'm storing them estate which is nice i'm keeping track of all the states i can see all the states they've stored it is really necessary I mean if events are what creates my state maybe I should restore the events now do you really need to keep track to the state of things if you can generate them maybe the states are virtual I mean if I know what the events are I can just create the state at any point I can say hey what's issue assigned state look like well just do create a shoe and the sign is you because I'm keeping track of the events states are just a manifestation then they're just a transformation of the events against starting state so you're strong state you're storing events now okay well if I want to store vents probably my table looks a little bit different probably I want to keep track of something like an originator figure out where it came from and then the core business data I could have in some kind of payload if you're using you know if using a non-static bubbly type system I could actually have the rows here but I want to keep track of of you know where this came from so let's try this again let's say I do a post and I say hey this event here's an event with a nice event ID like actually you your ID might be better for this great i can i can store that i can say i'm going to track the event the states i can just generate a ten-point the states don't matter if i have my offense and my events are completely immutable never changed all it works great so here's my crate issue well my virtual state is that i have the starting issue well then i have an assigned as you have a second she coming along okay here's the state okay now I've completed it we can say here's the state status just generated it's very easy to do I have the event i can create the state i can create the final state i can create any part of the state i won't go into this a lot a lot of people find that if you have massive data streams it works out really well just to store the event and don't worry about updating and dealing with database but changing anything in the database i won't go into this i'd say it depends on your system if you don't have massively high performance needs it may not be necessary I really like number three and four though the idea that look now you manage states you now you manage events you can create any state that you want I mean you can undo something you can say hey you know that third state that wasn't any good let's remove it you can say great we'll just generate the state from scratch so it's kind of like right having control over the versions of your state which you can generate a taint time it's like having something which you know your data now you see every everything that led up to your data and you could change it I mean if you think about it yeah 1q say it's like having get for your data yeah well there you go you have a you know you're keeping track of everything that happened you can go back and change things it's an immutable time stream but one that you can reorder as necessary you now have full version control for all your data because you're not storing the state at a point that's just generated alright not saying it's the best there are reasons not to go with the vent sourcing yet for example yeah it's a little bit weird to you may not have an event based system you may have history you may have things which are pretty static you may have assets in your database and not really events and you know it's it's not really it's still not really standard in most companies but then hey we're scarlett developers who cares about standard we're not going there we don't care yes question well i don't know but one thing some people do is they cash it they say look if you want the most recent state we could cash it somewhere we could we could have some type of temporary table that you can't write to but you can read from and say if you need this we can generate it nice thing being if you say hey this event at this time is invalid or doesn't work you could change it regenerate your cash but yeah it is a concern you don't want to have to then read in everything but in many cases you can i think having a view or a cash at some way to say here's the current state works out great you know but make sure it's a cash it's you can't write to it you can only read from it you can invalidate it and you can represent it that way that make sense i'm glad you asked okay oh here's the flickr so yeah we're scholar developers is end gonna stop us not saying it's the best for all approaches but it's a different way to think in terms of state especially compared to mutable state alright so that's the event sourcing part of it the last thing i want to go over is pretty short i want to talk about now how you're actually working with the database we've covered we've covered making your database your tables immutable we've talking about using window functions to make the most of having immutable table rows we've talked about well maybe table that databases are not where you should actually store your state let's not about working with your database itself so again you're doing is your database and your interactions with it are still kind of mutable you still have the ability to have some type of steak and still need to worry about it for example do you have some kind of session that you're passing around it have different state do you have a you have a global or implicit session that's being passed that might change how your program works make it less you know make rusi and increase the ability sorry the increase the likelihood of side effects do you have well everyone knows where the cursor is right you do a query database says I'm ready at bro one do you want to read it yeah Oh road to oh cool i'm standing here just let me know when you want to work i usually just read everything at once but sometimes that doesn't always work but then you totally have side effects if you have cursor and you're passing things around and you're reading it in an unmanaged way you could have very odd interactions happen it's totally well you can think of as being totally and safe the other area is where you writing state are you writing it in the middle of your application are you doing side effects you're already saying no I'm going to have mostly pure functions I'm going to model everything as an update I'm going to have some type of you know maybe monadic collection that says well here's a collection of my updates at the very end I'm going to map them into actual updates in the row but i'm not going to actually change things in the middle of my program which would be side effects the last thing on here is again if you're going to change state or you're going to introduce a new state and make sure that you're not doing this in military of functions keep your functions pure make sure you have referential transparency that you can call your program over and over again you could replace a function with the actual data to return and when you do need to update state when you do need to make changes make sure that they don't offend that they don't affect your code so this is a lot to do yourself and I'm kind of lazy i'll actually want to show you how to do it yourself so i'd recommend checking out do be here is there anyone who is not familiar with do be okay great so this is one of the type local projects it's really nice it doesn't go in it doesn't say hey i'm going to handle object relational management here it says I'm going to help you work with your database in a safe manner so that you don't have so that you can reduce the likelihood of being hit by shared mutable state of your database one nice thing I like them the dooby dogs is that again it says look here's all the different things you're probably going to do when you work with jdbc or you have a system that takes you through jdbc you're gonna have a pool to a connection to a prepared statement you know these are the steps of how you get to actually working with the database from the JVM type system but maybe they're not steps maybe it's some kind of moment added data structure that you can build and you can add things to which aren't actually executed until at the edge of your program or when you return when you return control to say okay go ahead and execute all these now don't do it in the middle of my program but you don't have to take this from me folks you should probably take it from Rob Rob's here today he's the main developer of do be he has cool little hexagonal dubey stickers I don't usually like to call people out by name but I love meeting people at scallop by the bay and talking about the projects that they're on so if you like doobie or you not want to talk about other projects find people who worked on it they could be here it's a great way to get to know them and for them to get to know how people are using their software sorry Rob if you're here so that's the part I really want to talk about about databases directions to make sure that you're using the database safely now I'm just kidding that's a whole talk everyone oh yes because otherwise you have to just keep the events for subversion like you must have everything to rebuild it at least with the state when you can drop version oh yes thank you so it's a great question about the fact that as I if I'm having an immutable table I'm just gonna add rows it's really going to fill databases on more active science is that your question we haven't had that problem with class pass yet that's a good question I haven't really experienced that we aren't using it for things like logs for example if you're if you have something which is really big and grows with traffic like you have some type of event to collect your system for metrics not sure my sequel is the best place for that you'll probably want something a good system which can automatically clear out things but again I don't think it takes up a lot of room I mean these are pretty small tables in there their thing in terms of events which modify not singular events which will never change to be honest also a lot of cases your database does update the rope but often keeps the original row around if you're using something like my sequel you'll find there's massive files you probably don't want to look about which make sure that its internal format is append only that's a great question we haven't had to look on that I'm sure you can roll it up we've had we've had issues with like database migrations you know when you get 200 migration files you like hey let's stop set a key state and start over again I'm sure you could do the same at some point if you have a lot of data here as well I don't have really a better question I would probably make sure to figure out what areas you have if you have vast amounts of data which represents mutable rose yeah you may need you may need to look into some type of sharding situation or something machine grow over time does that help it's definitely a valid question to think about that yes snapshot store right or you can take a snapshot and then discard all the events or that and say here's the starting state right again it depends on what kind of events you have I mean it I would think it's still always useful to be able to keep go back and look at that but you're right if it is if you are keeping track of events and these are events which you need to turn into state to keep track of them I'm sure you can have it saying step starting from the beginning and this is something which is going to change let you you could do that wish I had a better answer these are great questions and I'm glad no one's asked about how are you going to do some kind of crazy transaction across a immutable table yes how do what's the best way to evolve the structure of your events that's a great question we've tried using Jason as the payload here because we want this to be used by a lot of static type and dynamically type ones i would recommend thinking closely about the payload again is your make sure that your payload if you could use again you could use columns in your payload or you can use some type of embedded structure I'd make sure that you're in bed instructor is always going to be that your codes always going to be backwards compatible with older versions that's the nice part of doing something with in it's some kind of embedded models that this can change is necessary I mean it's also a problem you do want it to be able to change the format of your data without actually modifying the rose if you have to add a new column or change a column that kind of changed data and change the event that's great questions probably a good reason to have some type of separate payload that's not modifiable super question yes wow these are great events or sink Russians this is wonderful how what about brace conditions when we have say multiple events coming in what some people do is they say here's an event and it's based on the you idea of a previous event so you may want to think you may want if if this is an issue think of a state transition where you say hey I have an event which where the originator is this previous event therefore if you get two different events and they have the same originator it means they're both trying to update the same thing it could be that you choose the first one and discard the second one this is often an issue with distributed systems where you can have multiple delivery if you're using queues for example if you have something else some kind of streaming data you totally might get multiple events and you want to discriminate and choose ok given that these both update that's for the same the same these are updates these are both updates to a previous event you may just have to pick one of them it's a good question it's a good reason why you may want to have something that uniquely identifies an event and say only items which update the latest event are accepted killing hey how are we on time no I forgot to check wow this is going great I think that's about it this is functional database strategies here at scala by scott skull a scout scala by the bay 2016 i'm jason schwartz thanks everyone you [Applause]