#20 | CRUD Basics - AdonisJS 7

Now that we have our models defined, we can actually use them throughout our code to perform create, read, update, and delete operations against our database. If you'll recall within our challenges controller, we were mimicking these create, read, and update operations using an in-memory based array that we had right up here housed at the top of our file. So, in this lesson, we're going to work on transitioning those to actually utilize the database instead. And so that we actually have some records to work with, let's go ahead and start with creating records. So if we go down to our store method, which is in charge of creating those records, at the moment, we're just pushing into that array. What we're going to want to do instead to actually utilize our database, and I'll just keep this up above it for the time being, is to use our challenge model. And if you'll recall back to the last lesson, a model instance represents a single row inside of our table. So by instantiating our challenge model and I'm just going to import that from our models directory. We're creating a representation of a new row inside of our challenges table. However, just instantiating a new instance of this model won't automatically populate it with our data, nor will it create or persist it inside of our table itself. So we need to do those as well. And again, we have a couple of options. First, we can be very verbose and populate each individual column or property on our challenge instance one by one. So we could do something like text equals data.ext and challenge.points equals data.points just like so. Then once we have the data populated inside of the model instance, we can call await challenge.save. And this is where it will take those current values held on our challenge instance and actually persist it as a row inside of our database. An alternative option to explicitly setting each individual property on our model is to instead merge data into the model. And this actually pairs fantastically with the validation flow of Adon.js. So for example, instead of this, we can merely just do challenge merge and then pass our data object in. So long as that data object matches the structure of our models type, TypeScript will be happy there and allow us to do exactly this. Now, this might not be a huge difference whenever it comes to the small model that we're working with here, but it comes very handy with larger models. And even better yet, we can condense the entire creation into a single line using static properties provided by the base model for our challenge. So we could do con challenge equals await and then reach for the static create for our challenge and pass that data in that way allowing us to get rid of all of those. This single static create method is going to do pretty much the exact same as what we had just had just in this nice smaller package. So since we're now properly creating our challenge inside of the database, we can get rid of our push into our in-memory array. And further yet, this challenge.create does return back the created challenge record. But we don't need to actually make use of that anywhere inside of this controller method. So we can go ahead and get rid of that and just trust that create is making it inside of our database. Now you might have seen this, but there is also create many, which allows us to create many records at once by providing in an array of data structures. We only need to create a single one here. So, I'm just going to go ahead and put that back to create. Now, we do have a problem with our current code, but let's go ahead and run into it so that we can actually see it. So, I'm going to boot our server back up here. Okay, let's jump back into our browser and actually attempt to create a new challenge. So, we'll jump into here and I'm going to call this testing 01. Go to point value of 10. Hit create and we're going to get an error as we see here. So insert into challenges and then it has the properties that have it tried to populate values that have tried to populate those columns with not null constraint failed challenges.creator ID. At the moment we are not populating the creator ID value on our challenge record. And since we have that defined in our table as a notnull column, the insert statement is failing because we're inserting null into it and the database isn't allowing it. Additionally, we can't just plop any old value in as the creator ID. So we can't just do creator ID 1 and then have our data because we also have a foreign key constraint with that relationship as well. And that foreign key is going to require whatever ID we provide here to actually exist in the related model which is our users table. So if we were to give this a save and jump back into our code and give this one more go. So I'm going to go back into new challenge testing 01. Give it a point value of 10. Hit enter. You'll see that we get a similar looking error but the end of it differs. the actual error that we're getting this time. We're getting a foreign key constraint failed. So, we're setting a value in it. So, where it's no longer null, but it's saying that the foreign key that we tried to set this to doesn't actually exist. And so, it's holding us to that constraint and throwing an error. Now, the starter kit did come with working authentication. So, we could just sign up a user to quickly create a user record inside of our database. However, we're going to run into the exact same issue here because we don't have any roles to assign this user to and we have a foreign key constraint there as well. In the next lesson, we're going to see exactly how we can fix this situation that we've got ourselves in using cedars and factories. But for now, we can actually jump into a ripple instance, which is a read evaluate print loop instance to quickly create a new role and or user for ourselves to work with. So, I'm going to go ahead and expand our terminal out here to full screen by just clicking on that little icon right there. And let's stop our server and clear this out. To enter our ripple session, all that we need to do is do node ace ripple and hit enter. As it notes, we can type ls to see a list of available context methods and properties at our disposal. So if we were to do that, we're going to see the following. By default, nothing is going to be loaded inside of this ripple session. We can see what's loaded right down here, and it's just an empty object. So if we want to work with any models, we need to load those models into our session. So within here we can do await load models and call that as a function. Hit enter. And now if we do ls we can see that we have our models loaded in just like so. Okay. I'm going to give myself a little bit of room here to work with by doing console.clear which is a method directly from Node.js's ripple session to clear out our console and get us back up to the top of my terminal here. So now that we have our models loaded, we can do await and then we have access to all of our models by doing models dot and then whatever that model name is. So we want to first create a RO. So we'll do models.roll dot and then we can use its static create method to create a new role and we'll give this a name of user. I'm also going to go ahead and just plop that into a variable. So I'm going to hit command left arrow to jump to the start of the line and do const ro equals to take the created return ro that we get and populate it inside of the ro variable there. All right, I'm going to hit enter and we can see that it has now run a SQL statement to insert into roles and it used SQL parameters to insert our values just like so. Furthermore, we can do ro ID to see that we now have a role with an ID of one. We can do ro.name to see that its name is properly user. Now, all that we want to do is do the exact same thing for our users. So, we'll do models do user.create, and we can give this user a full name, some properties, and the Node.js rirepple session is going to be smart enough to know that we opened a bracket here, but did not close it. So, if we just hit enter, it's going to give us a new line and won't actually execute our line until we close that bracket. So, we can do full name to give this user a name and an email just like so. And finally, a password. And I'm just going to put something there. And now, although we have access to our RO ID and we can set it just by doing ROID, we also have inside of our migrations a default value of one for our ROS as a whole. And now since that role actually exists, we can just rely on that default being set by our migration to set the default role for this user to one as a whole. So if we hit enter there, it's going to create our user. And since I didn't plop it into a variable, it's just going to go ahead and log out the entire user that we've created. Now, also note that the password that I entered and created this user with was just something. But what was persisted into our database is this script line right here as our actual password value. This is done for us automatically via a before save hook added onto our user model by Adonis.js's default authentication system. We're going to discuss this more later on in the series, but I just want to note that that's happening here as it's quite an important step that's occurring. Additionally, since we're relying on the default value set inside of our database, we aren't going to see a RO ID yet on the model that has been logged out, although it is actually populated inside of the database. So, at this point, if we were to scroll back down to our DBSQL 3, remember we have that Visual Studio Code extension installed that will allow us to take a look at this. If we jump into our users table now, we're going to see that we have our user right in here with our default role of one. Fantastic. All right, I'm going to go ahead and close that back out. Furthermore, we can go ahead and exit our ripple session by doing dotexit and hitting enter and clear out our terminal and boot our server back up. So, npm rundev. Now, our creator ID of one actually exists in our database. So we're no longer going to get a foreign key constraint if we just merely attempt to resubmit our previous form. So if we go ahead and jump back into our create a new challenge here and we call this something like testing 01, give it a point value of 10, hit enter, it's actually going to be created successfully inside of our database. We aren't going to see it quite yet though within our available list of challenges because we're still reading from that in-memory array though. So let's fix that next. So, jumping back into our controller here, we want to scroll back up to our index view. And there's a number of ways that we can actually go about querying data from our database because getting data is never a one-sizefits-all problem. In this particular lesson, we're only going to be looking at these static method options available for querying data. But do note our models also have a query builder added on them that allow us to perform complex queries. We're going to introduce that a little bit later on though. So starting relatively simple here, we can grab just everything inside of a table by doing const list equals await and then using our static challenge models all method. This is just going to query everything and return it back into our list variable. I'm calling this variable here list because challenges already exists in this scope. So I'm just going to merely change the value of our challenges being passed into our render state to instead be our list so that it's now coming from our database. With that change set, if we give this a save and jump back into our browser, give it a refresh, it's now going to reququery that data from our database this time, allowing us to see our testing 01 challenge. If we needed to perform any other filtering beyond this, that's where we would then want to reach for that query builder that we're going to talk about in a few lessons. Whenever it comes to querying for a single record though, we also have a few other static options at our disposal. So let's jump down to our show next because at the current point in time if we were to actually jump into our testing page it's not actually our testing 01 it's still reading from an in-memory array of challenges printing out learnonjs. So we need to update this as well. The simplest approach that we can do since we have an actual ID from our route parameters that we're after is to just do const challenge equals await and then off of our challenge model is a find method that accepts in that ID. So we can do params id just like so. And that replaces our find method that we're using on our in-memory array. With that saved, if we jump back into our browser, give it a refresh. Voila. Now we're getting our testing 01 challenge from our database. Now, importantly, the find method will attempt to find a record in our database matching the ID that's been provided. But if it cannot find one, then it's just going to merely return back null. In our case, the rendered page expects a challenge to be found. So we're going to get an error at the render state instead of the query level. So we can fix that by switching this find to instead be a find or fail. So now this will attempt to find the ID that we're providing. And if it can't, it's going to throw a 404 error, stopping the user at this level and giving us a more meaningful and easier to discern whether or not it's an actual bug or not error to work with. So if we refresh here, now we're getting a row not found error which is much more meaningful. You might have seen it in the dropdown, but there is also a find by and find by or fail option as well. And this accepts two arguments. So the first one is going to be the column that we want to search against. And since we have our ID here, that's going to be our ID column followed by the value that we want to search with. So, if you're searching for something like an alias, a slug, or even a user's username, that's where this would be super handy to do. Rolling through a few of the other options, there's also find many. If you need an array of challenges based off of a list of IDs that we can do, just like so. And then there's also find many by, which again allows us to define which column we want to search our values against. For our use case here though, all that we want for this one is find or fail. So find or fail just like so. Whenever it comes to updating data, it's relatively similar to creating. So if we scroll on down to our update method. Now the primary difference between creating and updating is that we're going to want to query for the record instead of instantiating a new instance. So again, we'll leave what we currently have down there so that we can compare it for reference. But what we're first going to want to do is query for the specific challenge that we're trying to update. And again, this is where we're going to want to do challenge.find find or fail and pass it in the params ID. So if it can't find it, we'll get a 404 error. Otherwise, we're going to get back our challenge instance. Again, as we saw in our create method, there is a merge method that we can utilize to merge in our validated data as long as it matches the structure of our model. And then once that's merged in, all that we need to do is persist the change by calling save. And again, similar to the create method, there's also a shorthand for updating called update or create. noting that if it cannot find a record to update, it's going to go ahead and create a new record if it match cannot be found. So only use this method if you're okay with that possibility. But if I comment this section out here, we can actually go ahead and get rid of our in-memory section as well. Uh we can do this by doing await challenge dot update or create. And you'll see there's also a many variant of this as well that allows us to specify a search payload. So this is going to be our ID of params ID followed by the data that we want to create with. Now, this actually isn't applicable in our specific use case because we're using an ID to perform the search. This is more applicable if we're doing something like an alias-based search or a username based search, for example, because the properties that we're searching with will ultimately be merged into the data whenever it's persisted into the database. And in most cases, you're not going to want to manually specify an ID if that ID cannot be found. So, take this example here with a grain of salt. Additionally, if you're trying to pass in an undefined, you'll run into issues as well. But this is a great option if you're using something like aliases, usernames, and things like that to allow you to search against values and default to creating the value if it cannot be found. For our use case, this query, merge, and save approach is much better for an update based operation that we're doing here. Finally, let's go ahead and fill out our destroy, which is in charge of deleting challenges. So, this is actually relatively similar to updating. We're going to want our response and then we can also get our session as well if we want to provide a message back to our user. The first thing that we're going to want to do is query for the challenge that we're trying to delete. So we'll go ahead and reach through our challenge find or fail and pass in the params ID off of our challenge instance. Then is actually a delete method that we can call that will delete this instance inside of our database. So with that deleted, we can now do session and then use our flash to state a success message saying that uh let's do back tick challenge.ext has been deleted. And then we can go ahead and return response redirect to our route challenges index just like so. And again, since we're using a resource to define these routes, this route definition is already there and ready to go for us. Meaning all that we need to do is jump down into our resources, views, pages, and let's put this inside of our challenges show page. So just underneath our edit challenge, we can add in a button of type submit and point it to a form with an ID of destroy. We'll add that next. Give this a class of button and destructive and the text of delete. Finally, down here, we can add in our at form. Make it self-closing because we don't need to add any fields into it. Give it an id of destroy so that it's able to map to our button. Okay. Point it to the route challenges. And then we need to provide it the route params of ID with the value of our challenge ID. And then finally we'll give it the method of delete. And that got a little long. So I'm going to go ahead and collapse that down onto new lines so it's a little bit more readable there. Okay, great. So, with that saved, if we jump back into our browser now, and we go into our challenge with 90 of one. Whoops, I did challenge, not challenge. Let me uh fix that really quick. There we go. Let's give that a refresh. All right, there we go. So, now if we click on delete, we'll get our success message, and we no longer see our testing 01 challenge within our available list. Furthermore, our if statement took hold, and we now see no challenges available. And now there is one very important thing to note here when deleting records and that's that our foreign keys and their constraints need to be adhered to otherwise the delete's going to fail in our database structure. The most applicable relationship that we're going to run into this with is the pivot table relationship between our challenge and our user. And there's two different ways that we can handle this. We can jump back into our migrations for that pivot table of our challenge user and switch these foreign keys to cascade on delete. So that if we delete a challenge or if we delete a user, the pivot table reference to either of those will just automatically cascade it as deletion. And that can be easily done by chaining off of either of these and on delete cascade just like so. And we'll do that for the user ID here. So that the deletion automatically occurs for us at the database level. The database will have like a trigger system that says okay the challenge is trying to be deleted. It has this foreign key reference inside of challenge user. It's marked as cascade on delete. So, we're going to go ahead and delete this row from the challenge user table as well. Or we could manually do that inside of our controllers. So, if we jump back up to our challenges controller prior to deleting the challenge, we can reach through our challenge instance. So, challenge dot to its relationship via the related method. With this, we're able to point to a specific relationship that we want to go through. So we'll go through our participants relationship here which is the many to many relationship that we have to say that we want to detach all items in this pivot table from this particular challenge. In essence deleting the record out of our many to many pivot table doing the same thing that that cascade would have done. In most databases the foreign key references must be deleted before the record itself can be deleted. So by detaching here we're allowing ourselves to then go ahead and delete the challenge by first deleting its foreign key references. We're going to be discussing and working with relationships in a dedicated lesson. So, more about all of that later on. But of course, also remember that if you do want to go through with the cascade ondee method, in order for that to be picked up inside of our database, we would then need to refresh our migration. So, node ace migration refresh. And since we just have one batch, that has now updated and picked up that