 Like you said, I'm talking today about using Ecto outside of Phoenix. And I know that a lot of people, when they get started with Elixir, they kind of hear from it by Phoenix or maybe hear it from other way. But I know I heard about Phoenix early on when I was learning about Elixir. But I thought I wanted to not learn Phoenix quite yet until I was pretty proficient in Elixir. So, I went out and looked for ways to do that. And one of the ways I like to learn things is to use a database. A lot of the things I do are backed by data. A lot of the things you guys do are backed by data. And if I could code up something as I'm learning to interact with data, then that helps me in my learning process. And so that's why I decided to go ahead and start learning Ecto, even though I hadn't learned Phoenix yet, in order to start doing that kind of data manipulation and storing things and retrieving things to the database. Even ones that have already been created. So interacting with databases that maybe are legacy databases. So that's kind of what we're gonna talk about today. First of all, I work for a company called Planning Center. I realize today I'm kind of a walking, you can't see it because I'm short. But I'm kind of a walking billboard for Planning Center. I even got socks on from Planning Center. That's not necessarily on purpose. It's just they happen to provide like three quarters of my wardrobe at this point, which I'm very thankful for. Anyway, we make apps for churches. It's a very fun place to work. We love what we do. We love our churches. We love our apps. And we love the people who work with us and our customers. So, check us out. It's just a planning dot center. And they're based in Carlsbad, California, which is just north of San Diego. And for the past nine years, up until a year ago, I lived in Arkansas. My second stint in Little Rock in my life and decided to move. I was remote there and I decided to move out to San Diego to be close. And personal information, but we, my family and I in Little Rock. One of the things we'd like to do is go out to eat and we'd always go get fajitas of this, one or two specific places. We love fajitas. So we thought, okay, San Diego, right next to the border, it's gonna have awesome Mexican food and great fajitas. And we've had the hardest time finding fajitas. We cannot find fajitas. And people are always, I was like, where's the good fajitas? Where's the good fajitas? And they're like, well, we don't have fajitas. This is, that's like Tex-Mex. I was like, oh, really? Okay, well, you say so. So last night I had fajitas. I was very excited to come to Texas and have fajitas again. Yeah, so, first of all, what is Ecto? Ecto is a domain-specific language for writing queries and interacting with databases in Elixir. And that's one of two times I'm gonna directly quote documentation, so this is the most exciting part of the talk. I'm just gonna read straight for docs. Basically, it helps you write queries that interact with your database. Crazy how that's actually the case. So it provides a few different things. First of all, it provides powerful change sets, and we're gonna get into that in a little bit. Another thing it provides is composable queries. You can actually build up your query over a number of different functions if you wanted to based on either user input or some other determinations. It's got connection pooling built in. So if something goes down, I'll make it ring back up the connection to the database. It's got database migrations so you can track the schema of your database over time, so you can put that into Git or whatever you're using for version control and see how that changes over time. And if you need to, you can roll that back or bring that back up in different environments. It also has some built-in security, because it does some escaping and things like that that you may not think of writing straight SQL. And also it has flexibility. So if you need to write straight SQL, it has that ability. It's got something called Fragment. You can write SQL Fragments if you need to and just pipe that straight into the database. So this is actually a, this is like the end of the slides. I've got maybe two more. This is mostly live coding, so it's gonna be very interesting. Hopefully everything works okay. I will tell you I'm gonna make a few mistakes on purpose and I'll probably make a few mistakes not on purpose. I'll leave you to guess which ones are which. And our goal today is gonna be a setup Ecto in a brand new application and start using it a little bit. So we'll set it up. We'll create a to-do application. That seems to be the thing to do, to do. And we're not gonna have like a command line interface. We're not gonna have a web GUI. It's just basically all in IEX. So nothing crazy, beautiful. It's just super simple, ready to just get you guys interested in Ecto. So the goal is to get you kind of peak your interest in Ecto and say, well, if he can do that quickly, maybe that's something I wanna look into for things we're working on and go from there. One thing I did forget to mention is there are different adapters for Ecto that you can use depending on your database. So the default is Postgres. They've also got a MySQL one. They recently released version two. Ecto has recently upgraded to version two and version one actually still has support for MS SQL, MongoDB and SQLite three. So that hasn't been updated yet to version two, I don't think, but those may be coming. All right, so let's do some mix new, right? Let's exit this and let me know if you guys cannot see things, but I'm gonna go from here. All right, so we go mix new. One of the things I like about Elixir is that it provides you with good help documentation. Documentation is like a first class citizen in Elixir, which is really cool. So if you make a mistake, it usually tells you good examples of how to correct that mistake. So in this case, it says, hey, I expected a path to be given. Please use mix new path. So another thing you can do is ask it for help. So we can say mix help new. And it's a little small here. I'm gonna back it out just a little bit for now. But you can see it provides you with examples of how to use mix new. It provides you with the different options you can use. And I'm gonna point out one option that we're gonna use today. And that's the dash dash sub option. It says it can be given to generate an OTP application skeleton, including a supervision tree. So we're gonna be using Ecto and it's gonna have that connection pooling. We wanna make sure that we provide that option so we have that skeleton. We can write it all ourselves if we wanted to, but it's just nice to have that skeleton already built for us. So I'm gonna say mix new to do and then pass that dash dash sub. All right, so we generated our files for our Elixir application. It even says go CD test and then mix test. So CD to do, mix test, let's do that. So CD to do, mix test. We got two tests already. We're already on our way, right? That's sweet. We didn't even write anything. Those are basically just testing that true is true. So we know that everything is working correctly on our basic application. Next we're gonna start a teamwork session here. Pardon the horribly looking status bar there. I usually do it in black and other colors. So from here, we need to edit some files. The first thing we need to do is go into, let me go to Elixir Conf to do and we need to open up mix.exe. And in here, we can define our dependencies. So if we go down here to depths, we need to add two dependencies for application. One is Ecto itself. So I'll type in Ecto here and we're gonna make sure that it's around 2.1. And then we're also gonna bring in something called postgres. And this is basically the database adapter for postgres. It's the default adapter for Ecto. It's what I have installed running right now locally. And so it's easy to use. If you're using MySQL, the dependency package for that is MariaX. And of course there's other ones as well as you can look at the Ecto site to find those dependencies. But we need to do that. We no longer, in Elixir 1.4, we need to define it in our application because it infers that based on our dependencies. So it'll spin up those applications when it spins up ours, which is really cool. So now that we have those defined, we can see go back to our command line mix depths.get and it'll go out and get those dependencies for us. Cool, we're done. No, I'm just kidding. What I wanna do now is mix compile. So one of the dependencies it got was Ecto, of course. And one of the things that Ecto provides are some helper tasks for mix that we can use to get our application running. So if you had looked in just straight mix help before we ran this, you would've noticed there were no Ecto options. But as it's compiling this, we're now gonna get those Ecto options. I know the first time I tried this, I did mix depths.get and I looked in the mix help and there were no Ecto options. So I was like, I added it to my dependencies, why isn't it there? Well that's because I haven't compiled it yet. So make sure you compile before you do this so you can see these. So if you say mix help and I'm just gonna grep out our Ecto options here. Now we can see that we have a few different helpers, which is really cool. The first thing we need to do is create something called a repository. And that is basically what interacts with your database in your application. And it actually provides something, mixecto.gen.repo generates a new repository. So let's try that, mixecto.gen.repo. And again, we have a good error message here. It says it couldn't find repositories for application, but we could do that on the command line by passing it the dash r option. And what dash r expects is a module name for our repository. So let's say mixecto.gen.repo through dash r and we're just gonna call our repo to do.repo. All right, it generated a few files for us and we'll go take a look at those in a second. But it actually again provides us with some helpful tips. It says don't forget to add your new repo to your supervision tree, typically in lib slash to do.exe. So I'm just gonna copy this supervisor and I'm gonna go into my editor here. Go to lib to do.exe and what, it actually changed. I don't know if ecto hasn't updated this quite yet because it looks at 1.4 change where this lives by default, but that's a little misleading. So if it still says that when you're watching this or you're trying this on your laptop and it's not there, it's actually gonna be in to do slash application. So in here, we want to add our supervisor under our children, okay? So when our application starts up, it'll start supervising our repo. Perfect, so that step is done. If we go back, it also says, and to add it to the list of ecto repositories in your configuration files. So ecto tasks work as expected. I would like for them to work as expected, so let's config that. I'll copy that and go into config, config.exe and I'll say we also want to config or to do application with what it provided me. Perfect. Now in here, you'll also see that it has provided some default, some initial things to connect to the database that you told it you wanted to use in this case, Postgres. It says we're using a Postgres adapter. If we were using Maria X, this would go to the MySQL adapter, has the database name and our username and password. So these are some things you're probably gonna need to change. So I'm gonna change those for my local thing. Everybody close your eyes. All right, open them. I don't want you to know my password. Okay, so that's done. We're done with setting up our database. That's all we needed to do. So as long as Postgres is running, if we do mixecto.create, we should create our database successfully. So it's gonna compile all those files we changed. It's gonna give us a saying, it says generated to do app and the database for to do.repo has been created. So we got hooked up correctly, right? So the Postgres was running. It now has a database named, well, whatever I gave it in the config, I forgot to change that. It's to do repo, perfect. So the next thing, we need to actually add a module. So we need to actually add the things that we wanna store in this database. So since this is a to do application, one of the first things we need to do is add a to do item, right? So let's create an item module. So we go to lib to do and we'll create a file item.ex. So we're gonna def module to do.item and in there we're going to use ecto.schema. Now what that'll do is it'll bring in some functions, some macros that will allow us to define how we want item to look like in our database or to our database. So to use that to define that, we use schema and it expects the table name of where it can find these items. So in this case, I'm just gonna say it's in items. And then within that, we're gonna define a few things. First, we're gonna find a field and our first field is gonna be a title and it's gonna be a string and they're also gonna have a completed and that's gonna be a boolean. Ecto also provides this handy function timestamps that basically automatically adds insert or dat and update or dat to your schema. So you don't have to type those out because it realizes that most of the time you're gonna want those. Also I wanna point out that if we had not specified string there in that field, it would have defaulted to string but I like to be explicit and specify that. So now that we have our schema defined, the database still doesn't know about items. We haven't told it to create an items table, we haven't added any fields or columns to an items table and to do that we need what's called a migration. So let's go back into our terminal here and we have another task that helps you create migration. So mix ectogen migration and it expects the name of the migration. So in this case, since we're creating our items table, I'm gonna say create items. It's gonna create, I'm sorry, it's gonna create a default file here for us with a little bit of boilerplate that we can use to use it in our migration and that's in our privrepo migrations folder. So let's go check that out and edit that. I'm gonna back out here, so we've got a privrepo migrations and it time stamps it nicely too so you know exactly how your database is built up over time. Let's open that up. So in here you can see that it uses ecto.migration which provides a lot of the functionality we're gonna use and it has this change method and ecto is really cool in that it knows how to go back and forth on your migration. So if you're going one direction, it'll make changes and if you go the other direction, if you need to roll those back for some reason, it'll actually be smart enough to know how to reverse those most of the time. Now if you have some crazy migrations that you can't really predict what's gonna happen up and down, you can write separate up and down functions. So if we wanted to do that, we can say def up and def down. In this case, we're not gonna write anything crazy so we're just gonna let ecto do its thing and just do everything inside the def change function here. So I'm actually gonna bring up on the side here, actually let's do it below because of size. Let's bring up our items. So let's look at our schema as we create our table here. So inside the change we're gonna say our change is going to be create a table named items. And within there, we're going to add a field called title and it's gonna be a string and we're also gonna add completed which is a boolean and we're gonna add those timestamps. So in this case, our schema in our item module and our migration are like exactly the same almost. That's because this is the first time we're messing with that items. In later migrations, which we'll see here in a minute, it won't match this exactly. It'll just start building it up over time. I'm gonna close this side and we will run that. So we say mix ecto migrates and it'll run that migration. So you can see it's running our create items migration of the change function and it's going forward and it created the table items and it did that in a whopping 0.0 seconds. So glad we had to wait for that. All right, so the next thing we wanna do is let's try it out, right? Let's see if we can interact with our database in the items table. So I'm gonna bring up an IEX session here, which is gonna be our best friend for a while. I'm gonna alias a couple things here to make my typing a little less stressful. So we've alias repo and item. So first thing I wanna do is do this. Now, if you are familiar with Elixir, you're like, wait a minute, he didn't define a struct, but if I press enter here, we have a struct defined for us. And that's another one of the cool things that Ecto does for us. It actually provides us with a struct automatically that we can use in our application. So it knows that we've got a completed field, we've got it inserted at, title updated at, but what the heck is this ID field here? We didn't tell anything about ID. Well, thankfully Ecto is also smart enough to know that usually you're gonna want something like an ID field. Now, if you wanna avoid that, you can tell it, skip the ID field completely. You can say, you know what, I want my items field, my primary key to be called to do ID for whatever reason. Then you can do that. You can override those settings. By default, it's gonna create an ID field for you, which is pretty cool. Now, to insert an item, which sounds like something you wanna do, we will say repo, actually let's see all the items in our database right now. So we can say repo.all and then pass in item. We do that, a couple cool things. First of all, it shows us some debug information, this case, the source of this query is the items table and it's 1.2 milliseconds in the database and then it actually prints out the SQL that it generated based on what I told it that it sent to Postgres to actually get these items. And finally, we have the result of that, which in this case is an empty list, which is what we'd expect because we haven't put anything in there yet. So let's put something in. So let's say repo.insert and we're gonna create an item and I'll say the title is my first item. All right, so again, we have the debug output. This is the SQL it generated and it returns back a tuple of okay in this case because it was okay and then also the record that it provided. In this case, we have our to do item. So if you wanted to, you can pattern match on that. So let's pretend like I did that and we'll say, in case you don't know, v is actually the return of the last statement in IEX. So it's super helpful if you got, oh man, I forgot to pattern match that. I wanted to capture that item. We can do that and now we have item in our capture. Okay, so now we have our item. Perfect, it's got an ID of one. It's got our insert at, it's got my title but the completed is nil. Now if you have a to do application, nil doesn't really mean much to you, right? You either, it's either true or false, really. You can kinda say, well, if it's false or nil or falsi but we wanna make sure that it's either true or false. So let's add a default to that, okay? So we go back into our item module and on this field, on our completed field here with Boolean, we can say default is gonna be false. Let's save that, go back into our IEX, let's recompile our item module and let's insert our new item. Let's say item two equals repo.insert and I'm gonna use this bang method which actually returns not a tuple of okay and then the item but just the item that it inserted itself. So, don't have to pattern match on it. We're gonna insert an item and the title is gonna be my second item. Okay, so now we can see that completed is false even though we didn't provide it with any information as to what to do with completed. And since we didn't do that, it defaulted to false which is exactly what we wanted. But now we have this to-do out there that's still nil, right? We forgot to do that on our first creation, maybe you put this out on the web and you've got a hundred to-dos now that have completed as false and that's not cool, you wanna fix that. So let's modify that, let's update that particular record so that the completed that is false. So there's a couple different ways we can get something from the database. The first is get by and so we can say we want to get an item and we're gonna search for it by the title and I think I called it my first item. So let's just do that. So it's gonna search the database, select, you can see here in the database items where the title is my first item. And so we get back this item here and again I forgot to capture it so I'm gonna go ahead and do that. Let me see, this is the one that has completed nil. Now if you are familiar with Elixir you realize that to update a map, one cool way to do it is to kinda open up the maps and text here, put in your item and then a pipe and then what you wanna update. So let's say completed is false. So if I do that, we can see we get back one where completed is in fact false. So you think perfect, let's use that and update our repo with that information. So we'll say repo.update, feeling all confident and go here item and we're gonna pipe in, completed is actually false. Uh oh, here's an error. Thankfully this is what I was expecting. It says giving a struct to do dot repo update is not supported. Ecto is unable to properly track changes when a struct is given and Ecto change set must be given instead. So this is one of those things I mentioned that's really cool about Ecto is that you have these things called change sets. And this is the second most exciting part where I'm gonna read from documentation so hold on your seats. Change sets allow filtering, casting, validation and definition of constraints when manipulating structs. Ecto change sets provide both validations and constraints which are ultimately turned into errors in case something goes wrong. Now that's pretty cool, that's pretty powerful. You can see things like in the programming Phoenix book one of the things that they do is in a change set they'll actually take a plain text password and hash that password before it actually puts it into the database and that's done inside a change set. You can do other things like validate certain fields or a certain length or match a certain thing. You can call your own custom functions that will validate that true or false. So let's create a change set because we do wanna update that item. So back in our items here, we're going to import Ecto.change set and we're gonna create a function called change set here. Now it's gonna expect an item struct and it's also gonna expect some params but we're gonna give it an empty map as a default here. And within there we're gonna take that item and pipe that directly into a function called cast. And it's gonna say within the params I'm gonna allow two fields here. I'm gonna allow title and completed to actually pass through my check. So if you had some field that you didn't want to go through on your change sets, let's say you had something you never wanted to have change then you can exclude that from your allowed list here and that would never get through to your database as long as you use that change set. So let's use that. So let's recompile our item and let's get our item again. Get by, actually let's do it a different way. So I can say item, it's another way to get something for your repo, repo.get item with the ID of one. And that'll give us that first item with our ID of one, the one that is completed no. So if we look at item.change set and initially for illustration purposes I'm just gonna pass it an empty item and I'm gonna say my changes are gonna be title something. Let's look at what it gives us back. First of all it gives us a struct Ecto.change set and it has this changes attribute which title is something. So it knows that we're trying to change what we passed it which was initially empty. We're trying to change title into something. So far it has no errors because we didn't provide any in the change so that we weren't doing any validations. It provides this the data which actually is what we passed it and it checks if it's valid, it returns if it's valid and you can pattern match on that to see if the things that you're trying to do are valid. It'll actually keep it from going into the database if it's invalid which is really cool but we can use this and let's use this to update that first item where our completed that is no. So we can say item.change set and that was not change set. Change set item and we want to change completed to false and we're gonna pipe that directly into repo.update. So we can see here it did update items. It said completed to true, I'm sorry false and it gave us back this this tuple again which again I forgot to pattern match so let me do that, item equals v. Okay so now we have our updated item with completed false. Perfect, that's what we wanted. So now we've got that. Let's go ahead and start adding users and tying these items to users. So first you need to go back and create a user module here. So it's a user.ex whoop not uex user.ex and def module to do man.user do. And we're also gonna use ecto.schema in here and our schema here is gonna be super simple. It's gonna be users do. We're gonna have a field called name and that's gonna be a string and let's go ahead and add our tab stamps because they're fun. Now a user in our database and our application is going to have many items. So the items are going to belong to a user and that's something we can also define in our schema here. So we can say has many items and it's gonna expect on the second thing where it can find the definition of an item. So in this case it's in our item module. We'll save that. Let's go back over to our item because it's now going to belong to a user. So it's a belongs to a user and you can find that defined in to do.user. So we have user defined as a schema. We have our item updated to know that it belongs to a user but we don't have those changes in the database yet. So what do we need? Everybody, yes, migration, right? So let me split this real quick because I don't wanna get rid of it. So I'm gonna say mix ecto.gen.migration. We're gonna create users. We have that now, that file. So let's go edit that. I'm gonna wanna go back to my user and split my screen here so you can kind of see how we're doing this and go to priv repo migrations and then our create users migration. And again, we're going to create a table user. Table users. And we're gonna add a field called name. It's gonna be a string and we're going to say it has... No, it doesn't have many. We're gonna do our timestamps because the database doesn't care about the has many. It cares about the belongs to. If you know, databases usually have the belongs to, the foreign ID is gonna be on the belongs to field side. So we want to say that the items table should have a user ID field, right? A user ID column. So within that same migration, we can actually alter a table. So we're gonna alter table items and we're going to add a user ID column and it references users. Perfect. You can add as many different things in one migration as you want. Sometimes it's better to split them up. Sometimes it's better to put them together. So it's kind of up to you as to how you wanna split those up. In this case, I'm just gonna keep them together. So we'll go back and if I do mixecto.migrations, it'll list out all the different migrations we've created and their status. So we can see here, we've got create items. It's been migrated. They also have create users now and it's down right now. So we want to migrate that up. So we can say mixecto.migrates. All right, so we've created our table users and we've altered our table items. Perfect. I'm gonna close that because I don't need it anymore. I'm gonna recompile item and I'm going to alias2do.user. And let's create a user. So repo.insert. I'm gonna do a bang. I'm gonna say user with the name of, let's just do me, geo. All right, so now we've got a user in our database. And you can see here it's got an ID, it's got insert that and it has items now. So it knows that we have a group of items that it's expecting in the database to be based off of this particular user, which is really cool. At this point, I'm gonna go in and show you how to delete something because there's not really a better time. So let's say we messed that up somehow and we wanted to delete that. So I'm gonna say repo.get, the first user, and let's pipe that directly into repo.delete. So that's how you delete something. Super simple, just put in what you wanted to delete and it'll delete it from the database. It doesn't need to change that. You can do it that way. So, but we actually do want a user in our database so let's add a user back. So I'm gonna say geo equals repo.insert. Bang, user with the name of geo. All right, cool, so there I am. I'm in the database. Now, if you're familiar with Rails, I come from Rails world and I liked ActiveRecord because I could go user.items and it would load up all the items for me, which is really cool. So maps are kind of the same way in Elixir. So if I say geo.id, we get the id of two, perfect. So if I say geo.insert.assistuneName, faster type. We get geo, that's what we wanted. So let's try geo.items. Items is not loaded. So this is one of the cool things about Ecto that I like as well in that it makes you be kind of explicit about what you actually want loaded from the database at the time you're doing those database loads. You can load it later if you wanted to, but I like that it makes you load it explicitly because there are a lot of times in Rails where you get into N plus one queries and other slowness because of so many database calls. Well this makes it where you have to think about that from up front and actually specify that in your code, which is really cool. So if we wanted to, we can say, we're gonna preload it. So I can say geo equals repo.preload me, I'm gonna preload items. So now if I say geo.items, I get an empty list. So it's preloaded that from the database for us, which is kind of what we wanted, perfect. So let's make the second, or the first item, let's associate that with me, right? Let's update the item id to me. So we have our item and you can see that it has a, well it doesn't right now, so item equals repo, get our first item again. So now we can see that an item has a user id field and it's no right now. So let's update that to user id of two, which is gonna be me. So again we can say repo.update bang and we're gonna pass in our item and our changes that we want. We wanna say user id is two, that messed up. Let me change that real quick. Updates item user, oh, because I don't have to change that. Item.changeset, that's gonna go out here, item.changeset, this is totally one I planned for, right? Let's try that. Okay, so we have a successful database back and forth and it returns the item. But wait a minute, our user id is still nil. Why is that? Well remember in our change set we had to explicitly whitelist the things that we wanted to allow changed in our database. And since we didn't have that in the database we couldn't make the mistake of updating this without being explicit. Another cool thing about Ecto and Elixir in my opinion that he had to be explicit a lot of times. When I first got into it I was like, ah, I gotta write so much. It's so annoying, gotta be so explicit about everything. But now I'm like, it saved my butts many times. So let's go in here and allow user id to be changed. And let's go try that again. So let's recompile our item and now let's try that update. Now we have a user id of two, perfect. So we have to allow those changes through in our whitelist and our change set. Okay cool, so we got about 15 minutes left. Two things I wanna do. One is let's create a new item with the association already created. And then I'm just gonna go through some different queries you can do in Ecto and to try and show some of the power of the querying. So the first thing I'm gonna do is create a new item. Ecto has this thing called build a sock that basically builds an association between two things that are already associated but it'll build the association for you. So in this case, let's build an associated item. So for me, I want to build an association of items and the title is, let's say, speak at Lone Star Elixir Comp. So we can see here it's built something for us. It already has a user id of two which is gonna associate with me and we can pipe that directly into our repo.insert and now we've got an associated item in the database with that one call, which is pretty fun. All right, now some querying. Let's get into some querying. First, before we do that, I'm gonna go a little quick here and create a new column just to add some fun data that we can query. I'm going to add in our items a new field called estimated minutes. And this is just a track, how long we think something's gonna take and it's gonna be an integer. And we need to create a migration for that. So let's bring back up, let's put this again. Mix Ecto.gen.migration. Add estimated minutes to items. All right, let's go back over here. Load that file up. It's gonna be in priv, repo, migrations. And we're going to alter table items and not odd, do. We're gonna add a field of estimated minutes and it's gonna be an integer. I'm gonna close that. That's all we're doing in that migration. So let's migrate that. Mix Ecto.migrate. Cool, so now we have estimated minutes in our items table. Next thing we're gonna do is just add a bunch of random data. So bear with me for a second while I do this. Let's say we're gonna add 75 things in there. So let's pipe that into enum.each and to get the index of that. For that function, we're going to create an item with the title of to do number i and it's gonna be estimated minutes. We're just gonna make a random number here, random between, I'm sorry, uniform. Say between one and 120 or zero and 120 and a user ID is gonna be also random between, yeah, let's do it this way, two and three and you should notice that we don't have a user ID of three which will come into effect here in a second. User ID and then completed, it's also gonna be random between true and false. And we're gonna close that and then pipe that into repo.insert, the bang and in that. So you can see here it gives us this long thing but what we wanted to see is that the key, did I misspell it, estimated minutes, not found, estimated minutes, no I did. Well I didn't do it, oh recompile, thank you. All right, well I'll just tell you real quick what the user ID of three would have done. It would have given us an error for that because it realized in the database that we don't have a user ID of three which is one of those cool constraints because we've sold it that that column is gonna be referencing the user's table on the ID column which is pretty neat but I'm gonna pass that for now. So ID, actually I'll just go back and give you up. That, tap that into repo.insert and that. And now we can't do that. Oh, put that three in there again. So this is the thing I was just telling you was gonna happen, I forgot to add the user. So let's add the user repo.insert, I'm gonna add another user. In this case I don't really care, let's just say repo.insert, user with the name of max. Okay, now we've got user ID of three, let's try that one more time. Pipe that into the each and then go to the item and then pipe that into insert and then we'll end that. You're kidding, let's see. Princes are required, there you go, thank you. My up game was wrong, title. I'm just gonna do that into repo.insert, thank you. Okay, so now I've got random data in our database, finally. So let's ask it how many are in there? So earlier we were using repo.all, now it's just give us everything back. It's expecting one or more results, or zero or more results. Now repo.one is expecting at most one result. So we can say repo.one and say from i in items, or item, we want to select the count of i.id. I don't do this a lot. I'm gonna need to import our ecto.query. That'll give us those functions. So we can see we have 79 items in our database, 75 from those tests we did and a few from our initial ones. So we can say, well, how many belong to the user max? So we can do that, so repo.one from i in item, and this is just setting up our variables saying we were looking for something in the items table and we're gonna say that that's gonna, we're gonna reference that as i, and we're gonna join that with u in user on i.userid equals u.id. And we're gonna say where the u.name equals max. And we're gonna select the count of his IDs. So he has 38 items in the database. You can see here that with that it provided this SQL, which is kinda cool. So I didn't write any SQL. I kinda have to know a little bit about database transactions to write this, but I didn't have to write any SQL. So if at some point we needed to switch from Postgres to MySQL or to MSSQL or whatever, we just plug in a different adapter and Ecto would do that for us, which is really neat. Let's do something else. Let's say how many belong to each one of us? So we can say repo.all from i in item, join u in user on i.userid equals u.id where, let's say I'm sorry, group by. And we're gonna group by the user id and we're gonna select. And this is something cool about Ecto too. You can actually tell it how you want the data back. So you can do that in select. So if I didn't tell it specifically in select what I wanted, I'm gonna provide all the different full structs of the records that we were looking for. In this case, all I want is, let's say in a map, I want the count and i.id. And I also want something in name, so u.name. And I want them in maps. So you can see it returns a list of maps for us with the count and the name of the person they belong to, which is pretty neat. So we can also ask, well, what are the easy ones? So let's say we want to search for ones that are under 10 minutes long. So you can say repo.all from i. And how many times am I gonna have to write this? Well, let's just see something easy here. Let's create a query. This is where the composable queries comes in. So if we have that, that we know we're gonna be typing in from i and item, join you and user, we can actually store that in a variable. And then from that point later on, use that variable as the starting point for any other later transactions we wanna do. So let's do that. So let's say our query is gonna be from i and item, not in an item, i and item. We're gonna join up with the u and user on i.userid equals u.id. All right, so that's all we wanted to do. So we've got that query now. You can see that it's returned a query for us. We can pass this variable around. We can add to it. We can modify it. We can do what we need to do to build up the query over time, which is where the composable part of Ecto comes in. So now we can say repo.all from i and u in our query. So here we're saying the i is gonna refer to the first thing we defined, which is the i and item. And I can name this whatever I wanted. I can name this the first thing. But for now I'm just gonna name it i to keep typing. And the u is the second thing, which is the user. In query, we were going to select the easy ones. So we can say where estimated minutes of our item is less than 10. And let's order by the estimated minutes. And let's select in a map, we'll say the to do title is gonna be i.title, our item title. Let's say the time is gonna be i.estimated minutes. And our user, let's put in our username. So there we go, we've got the things that are easy things to do. So I've got this one here, it's only one minute, let's just get it done, right? It's to do number 59, perfect, that's what I wanted to do anyway. So this kind of gives you a demonstration of different things you can do. You can do pass and order by, you can pass in the different searches here. So in the past we've just done equals, we can do less than, we can do greater than. We can specify different returns. So let's say instead of in that map, we wanted just everything in a tuple here. So let's say we just want the title and then the estimated minutes and then the user. So now we get it all back in just straight tuples, which is really cool. Let's say we wanted them in lists, well we can say let's put that in a list. And Ecto will provide that to us in a list. So Ecto is really flexible in providing you exactly the data you need in the format that you need it, which is really cool. So if you have some web view or something you're using in Phoenix or some other app, you can actually say I want these things with these names and in the map and all this kind of stuff and it'll actually provide that to you right away. So you don't have to modify it later. Pretty neat. Couple other things I want to try, let's do this. Let's say, let's do one more. So it can actually use the power of the database itself to do some calculations and do some things that the databases can sometimes do. So let's get the average minutes for each user. So we can say from repo.all, from in this case I'm just gonna type out a given user in our query. We're gonna group our query by our user ID and we're gonna select the user, user.name and let's say our to-dos. Let's say let's get our count item.id and average minutes. And here we're gonna use the average function which again, Ecto will take and pipe that directly into Postgres. So we don't have to, Ecto doesn't have to do anything about it. Postgres is really all that for us. Our item estimated minutes. Forgot a, let's, oh darn it. Does anyone know how to stop that? Let me re-do this, repo, user item, and I think that was it. So let's try that again, repo.all. We're gonna rewrite that from i and item. Join you and user on i.userid equals u.id group by user.id, we're gonna select, so I forgot earlier, for some reason, the select is the only one I always forget the colon. We're gonna select user, u.name, to-dos, our counts i.id and average minutes is going to be our average, yeah, average of our i estimated minutes. All right, let's see if that does it, it does not. Ah, thank you. Of course, import Ecto.query, because I had to back out everything. All right, let's do that. User, oh, because I didn't do, user, I did u this time, u.id. Okay, there we go. So now, you see Ecto, I'm sorry, Postgres did the averaging for us, as you can tell in our SQL here. It sent that straight to Postgres and it averaged everything for us and gave us back the maps we were expecting on that. That's really all the time we have to go into some of those things, but Ecto has a whole lot more things that are really powerful that I'd really encourage you to go check out. I've maybe submitted one or two PRs into Ecto, so this isn't my repo, it's awesome. All I did was maybe correct a couple typos, so you can thank me for that. But go check it out, it is amazing and I know that for me, as I'm learning new things, again, databases are the way to go for me, I know some of you are probably the same. Also for enterprise databases, I know someone was asking earlier about interacting with Oracle and maybe some big MSSQL databases. This can do that. Again, Ecto 1 would have to do that for MSSQL and Oracle hasn't written yet, but maybe that's on you and go from there. But anyway, I blog at jeffreylessall.com. So far, it's like one post every other month or something like that and all it's been on Elixir and most of it's been on Ecto and a lot of them I go through how to build up different sample applications to show you different things to do with Ecto, including multiple repos with multiple databases and different types, dealing with legacy databases, that kind of stuff. So check that out if you're interested. Thank you very much.