How to Use Knex js in an Express Backend App

[BLANK_AUDIO] Using Next in the Backend app, previously we created a Next file that contained configuration for Next Migrations, and that file is used to connect to our database. We can use the same Next file in our Express app to allow it to work with the database. We'll start by adding a directory to the root of the project called Database, and then we'll create a JavaScript file that initializes Next. So let's go ahead and create a directory called database and a file called client.js. So over in VS code, under the Backend, choose New Folder, create a directory called database. And then inside that directory, new file called client.js. So this will be our database client. And then inside that client.js, we'll import Next using the node require syntax. And then we'll use our Next file to instantiate Next with the connection information that it needs to connect to the Postgres database. So paste that information in and save that. And next, we're going to look at the git lunchweek endpoint. So as we've discussed, we're returning that hard coded list, and we're going to change it to return data from the database. So first of all, we're going to import our Next client, the file that we just created. So in lunchweek.js, where our backend lunchweek routes are set up. So import Next. Then we're going to create a helper function to select all the rows from the lunchweek table. So this is going to kind of replace this hard coded array. We'll deal with that in a second. So here's our helper function. And the next syntax is pretty easy. We just say next dot select from the table name. And then we'll add an order by clause to order by the week of that way. We'll have the weeks in chronological order. And then inside our git endpoint, we just need to call that helper function and return the result from it. So whereas before we were returning our lunchweek list, there, the hard coded list, we're going to change it to fetching this list list from our helper function. Now, Next is asynchronous. And so we need to add the word async here. Otherwise, the await syntax won't work and we'll return an empty list. So this is a big gotcha there. Make sure that you have async there before function so that the await syntax will await this asynchronous method from Next. And then we'll send the list back. So we can delete this constant list now and save that. And let's go see if our back ends running. So go ahead and start the back end using npm run nodemon. Let's see here, we've got an error on line 20. Okay, so that's because I already deleted that array. And so the linter is telling me that this endpoint is no good. So let me put that array back in just temporarily. So we'll leave that back in there. Run this npm run nodemon again. So that's a good example of how es lint can catch bugs in your code. So our back end is running on localhost 3000. So we can test this change in the browser just by navigating right to localhost 3000 API lint suite. And so now if we hit that endpoint using that URL, we'll be able to see the data that our back end is pulling out of the database and then serving up to the caller. So you might have noticed that the JSON properties in our API response are snake case just like in the database. So we have these underscores. We can do a little bit of extra configuration and convert those to camel case, which would be the JavaScript standard. And then that'll make it a little bit more understandable for JavaScript developers to work with the code. So this is kind of a nice to have. But if you really prefer camel case and for the rest of this course, we're going to use camel case in our JavaScript. So I'd suggest to go ahead and do this part of the project as well. So we're going to install next string case back on the command line, stop the back end, do npm install next string case. So we'll update our client.js file to use next string case. So think these three lines are what we need. So copy that. Come back to the client.js file. And so we'll do instead of const DB equals next config, we'll instantiate next with the command. So we'll do the command. And then we'll do the command. And then we'll do the command. And then we'll do the command. And then we'll do the command. And then we'll do the command. And then we'll do the command. And then we'll do the command. And then give this another try just from the browser. So now if we refresh, the response from our endpoint now has camel case properties, which will be better from a JavaScript perspective and make it a little bit more understandable and kind of follow the JavaScript best practice. Okay so we still have another endpoint in our back end to deal with. So let's convert the git lunch week ID route to use the database as well. And so this time we'll pass in the ID and we'll return the matching row using a where clause function from next. So by default next will return a list. So we'll also use dot first to return a single object. So we can start by creating this helper function here. Back in our routes file and this time we can get rid of this list here. So here's another helper function get lunch week by ID. And then in our ID endpoint now we'll call the helper function. So before we were using lunchweeklist dot find. So we'll get rid of that. And now we can use lunch week equals await get lunch week by ID and we'll pass in our ID. So let's give that a try. Oh so another good catch by ES lint. So telling me that await is only valid in an async function. So we need to add async here. Now our back end will compile. And so our convention for this endpoint is just to add the ID so slash three. So now we get lunchweek ID three. We can try to try a couple of examples there. So now we're getting that via a where clause from our database. Okay next let's look at error handling. So our endpoints are working in dev but we're dealing with an external database . And that means that there is things that can fail. And the network can fail. The database itself could fail. And so it's a good idea to handle errors in our API. There's a bunch of different ways to handle errors in express including error handling middleware. But in our course we're dealing with a small number of endpoints. So we're going to use the explicit way of handling errors. If you want to look at some other options this is a pretty good article that describes some other ways you can do it. So the explicit way goes like this. We'll wrap our code that could fail in a tri-block. And then we'll return an appropriate HTTP status code with an error message in the catch block. Okay so on our get lunchweek endpoint we'll change it to a tri-catch. And so if there's an error we'll return a status 500. And we'll return a little object with a message and then the actual error. So let's update that endpoint to this. So that's going to be this endpoint. We didn't do any error handling yet. So we're going to change it to that. So this was the part we had before. So if everything works well we'll follow that part. Otherwise we'll catch errors and then let the caller know that something bad happened. Okay. And then likewise we need to do the same thing on the lunchweek ID route. Let's see what we have here so far. So we already have an if-else statement where we're sending the 404 not found. So we can keep that part. We're going to update it a little bit though to use this message. So that'll improve this a little bit. So we'll still send a 404 but this time we'll send a message explaining what happened. And then we're going to wrap the whole thing in a try catch. So we'll go try. Catch E. And then under this block. Now we're not exactly sure what error may happen. This will be kind of a generic catcher. So we'll just say error getting lunchweek by ID. And then we'll bubble that whatever the error is we'll send it back to the caller through this E.2 string. So we'll put this in our catch block. Okay. So let's see here. Let's give that a try and just make sure that everything's still working. Okay so that's working. Now let's try like seven or something that doesn't exist. So under this case we do get the message back. And if we look at the network tab here and refresh that we get the 404. So that's telling the caller that there was a not found. Okay. Let me just show one more example here of an error that can happen. And then the way this handling will help the caller. So imagine in our next file that we put in the wrong password. Okay. So I think that automatically recycled. And so now let's try this here. Yeah so now I'm trying the lunchweek endpoint and I'm getting a 500 status. So an internal server error status. And I'm getting a message that says error getting lunchweek list password authentication failed for user postgres. So now the caller can see that something went wrong and get an idea of what went wrong. Alright so let's fix this. Put that back to where it's correct. And that's going to be it for this lesson. So we covered a lot of ground we added next to the back end and we updated our endpoints to use the database. So now our app has persistence and it's really becoming a full stack app where we've got a front end with spelt. We've got our back end with express and then we've got our underlying persistence layer with postgres and they're all kind of working together. So far we've only dealt with git requests and in the next lesson we'll add post and put endpoints so that we can create and update entities in the database and then we'll serve them back with git request. So we'll look forward to seeing you next time.