Next.js Tutorial - Part 4 | API Routes using SQL Database

hello, YouTube welcome back to our Next.js series!!! Today we are going to create the API endpoints for those two screens. On the Left we have a user profile screen and on the right we have vehicles owned by a specific user, so focusing on the Left screen we can see that when the application loads we will need to have an endpoint to get that data so we will have something like /API/person/:id then after a while, we can change the information in the form and click on Save Changes. Doing that we will need a PUT endpoint because we are updating that user if you don't know what those GET POST PUT DELETE verbs are I will leave a link in the description for you. Lastly, we can use an endpoint to get all the users that we have in the database and if we want to do that we will need to have an endpoint like /API/people and it's also a get then on the second page we will need to have an endpoint to get all the vehicles after that if we click in one of the vehicles we need to get the vehicles by ID and lastly if we want to get only the vehicles from a specific person we can opt for two different variations the first one we can use the endpoint that we created in the previous screen /person/:id and one of the properties will be the array of vehicles but imagine that that array of vehicles is really big let's say 200 vehicles and you never use that array except on this specific screen so you are sending back and forward a lot of data between your server and your client that is never used so in order to optimize data bit it will be nice that our user and pointer slash person endpoint only sends the information that we really need for the user like the name the email etc but we don't send vehicles when we need to get the vehicles we can have /API/person /:id/vehicles and in that moment yes we send all the vehicles from that user this is just a slight optimisation okay then how are we going to achieve this well initially we will create all the end part the end points so all the API routes will be created initially secondly we will create a database using sequel Lite and the last point is to make those two work together our route and our sequel Lite and you may be asking why are we going to use sequel Lite well one of the requirements I have when I created this channel is that everybody should be able to follow along with only two comments git clone and then PM install and they shouldn't need to install any external dependencies and if we go to use MongoDB or sequel server or Oracle I will force people to install a dependency which is something that I would like to avoid then most people also understand sequel even if you are using MongoDB most probably you know a bit about sequel and the same is not true about people that know sequel not everybody will know MongoDB so it's a way to keep everybody on the same page we will use normal sequel okay so let's not jump into the S code now that we are inside vs code we just need to follow three rules the first one is that our API is need to be inside /pages / - after that the second rule is that every single file that will be live inside a pea is it will become an API route so one of the ones we need is slash vehicles so let's create a file called vehicles dot TS and now we need to export a function and this default here is important if we don't export it as default next J's will not be able to recognize this as a function in order to be called so we just name now this function as get all vehicles for example and because we are exporting it as default this name really doesn't matter it's just for us to read it and understand what's going on now this function has two parameters one is the request and the other one is the response if you ever did no js' for example you are probably used to those two and next Jase provides us with some Taipings okay so we can come here and do next API requests and next API response and using those two API response over here okay we can see that one of them the methods that exists for the response is of type JSON so let's just first start to say hello world for example okay and let me just add one more thing to give you an example in a second so let's put this method and this method is nothing more nothing less than the HTTP verbs we spoke five minutes ago so you will have to get the post the put the delete etc okay so saving this one we can go to our browser and in our browser let's open slash API slash vehicles and opening this one you can see that we received our hello world as we were expecting and we have a method get' so let me just show you something here so if we have this one oh wait fetch API vehicles and I create it as opposed okay let's see what happens our dot JSON so now this API endpoint also replies to a post if you don't want this to happen we can just come here and do the following if rec dot method different then get okay then we can do res dot status dot oops let's say the status is a 500 internal server error we can use the better one if we want to and let's say that the JSON that we are going to return will be of type message and we say instead of hello world let's put a proper message sorry we only accept get requests okay so if we go back to our browser after we save we can just say r2 because we are using costs and if we do this now we should receive a 500 and we did receive a 500 so coming here to our network and opening this one we can see that we received a sorry we only accept get requests okay so if we come here and we do an r3 inside the method is just I get it should just work as before so doing a wait are three dot Jason should return the message that we expect so this one is good so which other endpoints do we need apart from vehicles we will need one endpoint called slash vehicle /ib so let's create a folder over here called vehicle oops vehicle and inside this vehicle let's put an ID but yes and if you don't remember from the first video from this series those square brackets in ID are telling next chance that we will have a dynamic routing parameter so the same thing applies here so I can just copy paste this one and from now on I will not validate for the methods just to be faster to show to you okay so in this one we have a get vehicle by ID okay and we can say by ID and say that the ID that we received it's wrecked wery dot IB and this ID over here matches the ID that we have in that file name so this seems okay we can remove this method now and just say a message like oops a message like the name of the function for example okay so if we go back to our browser we should be able to see slash vehicle 1 2 3 4 5 or 1 2 3 4 6 and we received exactly what we were expecting so our second endpoint is also done let's go now to the people and we can have a file called people dot PS okay and we can just copy from our vehicles let's just remove this validation because we don't need that one for now get all people or get people get people and in this one we can just send an array of two names example name Bruno and name another person named Jack Jake I don't even know if that name exists good enough for now right so if we do people over here you see that we are starting to receive those two people that you expected so that one is also good the last one well not the last one we will need two more so let's create a folder called slash person okay and we will do exactly the same thing we did for vehicle the first one that we need is person /id and the second one is person /id slash vehicles to get all the vehicles of that user so you have two ways to do this one or you can create here a file called IV dot es ok which is okay and you just have the person /id over here or you can just have a folder called ID okay and then inside that folder that ID you have an index dot d s file and a vehicle's file oops my bad and the vehicles file okay so for now let's delete this one okay oops delete delete this one and now we will have a slash person /id which is our index dot t yes you can think of it like if you do my website comm by default you will go to slash index dot HTML is the same thing over here ok but in this case is a type script file so let's just copy this one put it over there and call it get person by ID we can copy these funky herbs I copied the wrong thing put it over there ok and the last one we need to do is copy paste this one and we can say get all vehicles by person ID or something like that person ID some name so let's just make this one and we can use the exact same thing so let's test these two endpoints and if these two white wines are working we can jump directly to the database so person /ib which is five five five for example okay so we have the get person by ID and if we do five five five slash vehicles okay we should have a get all vehicles person ID we can change this ID so for example seven seven seven and as you can see we have that one over there right so the next step we need to do is to npm install and let's install sequel light so we will do a sequel light ok and let's also install NPM install - - save dev type slash sequel light I will come back to you when these finishes so the NPM install is now finished and I have great news we don't need to install ad types slash sequel light because sequel light itself already comes with typing's out of the box so going to the documentation of sequel light we can see that they provide us with some migrations that we can use so we will use something similar to this where we provide the tear up and the tear down when we want to migrate to a next version of the database so let's start by oops not this one this one let's start by collapsing everything create a new folder called migrations okay and now let's create a file 0 0 1 hello world dot sequel ok and now what we need to do is this up and this down ok and start to say create table person for example and we will have an ID which is an integer this is a primary key Auto increment okay and why Auto increment because we want every time that we create a new person that the ID just increases automatically without we having to do anything the next thing is the person usually as a name and the type of the name is usually a text after the name we can say that the user has an email and we can put more and more property over here but for the sake of the demo probably we don't need to put any more properties the next type we are going to create is the vehicle table and we can copy exactly the same idea from here okay and the next thing is that a car usually as a brand which is of type text it has a model which is of type text and in our case it has an owner ID and that owner ID is an integer and it's a foreign key okay that references oops I have a typo references person I be okay and so having this now it's probably enough for us to create the the person and the vehicle so let's just drop this table person on the teardown and drop the type of vehicle on the trier the on the teardown now we need to test that we are doing something proper right so let's say that we create a file called database test dot J yes it's a good name so we will have sequel Lite equals to require sequel Lite okay and now because we are going to work a lot with the synchronous code it's just easier if we call a function asynchronous okay and we just do this because now I can play with a wait and they think inside this function so we will have a DB and we will do sequel Lite and we need a no wait over here and which name do we want to give to our database let's call it my DB dot sequel Lite okay I would say it's a good name so now we need to migrate our database so we will do migrate and force and last I'm not sure if it's last or latest lets me just check in the documentation they have here force last okay first last that's the way we force it to migrate to the latest versions okay if you have an application in production probably don't want to do this line but in development it's nice to force your application to just migrate to the latest version of your database and now that we have this we can probably run this file and see if we get any errors so let's just run this file so database test yes and running that oh we don't need the foreign probably oops open so we don't need these bit which is great we just need probably the references to let's just check if that's the case yes that's the case so we just need the reference and it will assume automatically that's a foreign key so now we can insert some values insert into person values oops we need to say name and email and let's say the names are Bruno and by the way these email doesn't exist don't send them out there so let's say that I have that user and I have another person which can be called I don't know jack jack at well my name is good okay so let's say let that we do now insert somebody over here so now we create a model and we can have an owner ID we say values and let's say that the brand is an Audi r8 and we will assign it to me so now I have an Audi r8 so let's just assign a vehicle so immerse these bands to these second person Jack okay so let's just see if the application still runs okay okay it still runs so now we can just go here to our database test and do something like DB dot get or in our case we want all okay so we can do select star oops from person okay and let's just do cost people equals to a weight of bad okay and let's say that we do a console log of all people and we will do people like that okay let's even do this look nicer string if I and let's do the trick we do all the time over here in the channel so let's see how it looks like if it works then we can do the same for the vehicle and yes we can see all the people that exist in the database so let's just do a copy-paste of this one and do the same thing for vehicle and vehicle and vehicles we can copy this one and say all vehicles right so now we can even have this running before our next GS application so if we have this running before our next year's application we can even make sure that everything from our setup let's say worked and that we have all the people and all the vehicles and you can see that one vehicle is how they are ate the other one is a Mercedes Benz and the first one is assigned to me the second one is assigned to Jack okay so let's just make this script run before our next J application so come here and now the slash dot and how did I call it database test dot J yes we can even call it beta by setup probably is better but it's already called database test so let's keep it as is and now that we have that every time that we run our application let's say that we do a clear of the console and do NPM run dev okay we should run that comment first and now oops I think I put it in the wrong place did I yes I did it in the wrong place my bad I'm sorry so let's now do an NPM run Dev and we should run our setup yes we run our setup and now we are starting to run next ok so what is the next step the next step is for us to go inside our application ok and start to go to simple places like for example slash vehicles and replace the code we have over there which is just art coded response dot Jason and start to change it to have response dot Jason of our DB dot all select star from vehicles for example the same for people then we can start to put where clauses and do everything so the first end point that we can start to migrate is for example the people endpoint because it's a simple one so let's just copy this bit over here open people's endpoint and start by importing sequel Lite from sequel Lite oops another type of Renault good job and we will make this function I think ok and now that we made that function I think we can say DB equals to dead and cost people equals to V B dot all off select star from person ok and having these people over here we just need to pass it to the JSON over there so everything seems to be fine let's just go to our localhost Rita oops I think is not running yes it is not running so let's make it run and hopefully we will see our setup initially so we saw our setup everything seems to be fine so we can do API slash people it's our first endpoint and now oops what happened to do we need a no wait over here ok because our all returns the promise as well so we need to await for that one so refreshing that one we get the two people in our database so our first endpoint is done let's go to the vehicles one which is really similar to this one let's just copy this because it's really really simple if we don't care about this anymore I will just delete this but in a real application keep this if over there because it's probably important you don't want to give the false impression to someone that they can post over your API and in reality it was just to get right so in this one let's just say very calm okay and make this vehicle so copy paste and get all vehicles okay when we save this one and we go back to our browser we can now say vehicles and now we receive all the vehicles in our database so the second endpoint is migrated let's go bye-bye ID so where do we have our vehicles by ID we have our vehicles by ID over here let's just copy the internals because the internals is the nicest thing and the hardest thing to do as well so we have that one so let's import sequel light oops I have one L too much from sequel light okay make this function a sink as well and now we can delete this one and what do we want from this one this one we just want to do where the vehicle ID is our parameter so let's do where ID equals to a parameter okay so we can direct that query dot ID okay and so with this let's do a paste so everything seems to be nice we are doing a select we are doing where and we are passing a parameter over there and that parameter will be our request query that ID so going back to our browser and doing slash vehicle slash one for example we should receive our Audi r8 so this is good the only thing is we are receiving in an array so we can just change this from all to yet and if I'm not mistaken now we should receive just a normal object and yes we do now we receive just the object which is great going back to our person we can just again copy these go to our person and on the person / ID we can do something really similar and so let's delete this one let's say person and let's say that over here is person where ID equals to the ID let's make this function asynchronous as well does visible to do import now we need to import it manually but it's okay so ctrl V save and we will pass a person over there so we can save this one and now if we do person slash one you should receive Bruno so it's okay it's working as well and the next one that we can do is burn off slash vehicles and we will get all the vehicles that are from Bruno so let's do again the same thing we can just copy paste this one okay and probably this bit over here if this was a real application we should extract this bit of logic because if the name of our database changes you are seeing how many places we already need to change that one but for the demo is probably okay to do this copy/paste everywhere okay so import sequel ID from sequel Lite now let's delete this one and what we want to do in this one is we want to go to the vehicle and we need to change this to all where ID and in this case is not the ID I think we called it owner ID will be that thing let me just check that we call it an owner ID or not over there where are our migrations they are here okay let me just check that we called it owner ID yes it was owner ID so we are doing it correctly oops let's go here so we are doing that correctly is on that idea okay control-c control-v to be so we can call it all vehicles and all vehicles from that user not all vehicles of all vehicles right so just to make this a bit more interesting we can go to our migration and in our migration we can just add one more vehicle to be part of Bruno so let's say that Bruno now as and now they are I don't even know if this car exists r6 probably doesn't exist but it's fine for now so let's just kill our server first the application to run again and now we have two cars for Bruno okay which is good and alright and an r6 so going back to our browser and doing slash person / one which is me slash vehicles we should now receive two vehicles and we do we received the r8 and br6 if we go to the second person we should receive only one vehicle so the second person which is jack we receive only one one one vehicle so everything seems to be working correctly at the moment so the only thing that you may want to do I don't even know if you really want to do but that you may want to do is probably in the vehicle you can do some kind of group by sorry not group by inner join just to show the username and the user ID for example instead of showing only the ID you show the name and the ID for now let's keep it out of the way and the last thing we need to do let's just close all of these is to go to our people slash ID and we will need to create the put for this method we already have the get but when we save changes we need to execute the put to our server so in order to do that we already saw that we have request dot method so we can have request dot method if it is a put now we are going to do the following create a statement and to create a statement we do debbie dot preparer and in our case we have tried the table person we set the name equals to the first parameter we set the email equals to the and parameter wear ID equals to the third parameter now oops we need to put any weight over here now we can say Const result equals to statement dot run and we will start to pass the parameters and because this is a put the name and the email will come from our body so we can do request dot body dot email request that body dot name and request that other dot I be okay not ever I'm sorry query query dot ID and actually I exchanged the order we have name email first okay and so every single one of those will match to the first question mark over here this one will match the second question mark and this one will match the third question mark the last thing we need to do is result that finalize and I missed another weight over here okay so now we don't need that if we format we have this code and the funny thing now is we need to return to our user the newly updated result but because we already have the get in the same exact method we don't even need to change anything we will just get again from the database that specific user and because it's already updated we just returned the updated user to our front end so that's looking good let's see first if the application still works with the get this one is running so let's go to our browser and let's see if the application is still working with be person / - for example and we should have Jack and okay yeah we still have Jack so let me just go here and do fetch post okay and let's just copy this from mdn is probably the easiest way because we need to set the content and we need to set the body so let me just copy this and change it slightly so opening here so we don't need any of those we can change these one to be a boot okay the URL is this URL okay so now we can delete all of these we can delete these we can delete all of these and let's say that our new values for name will be our new value and the email will be new at whoops we need new at PT dot PT I don't know that thing probably doesn't even exist so let's say that we have this we are doing a put we need to specify when we use fetch what is the type of our body in this case it will be a content type of application JSON and then the body will be that object so running this one what happened a weight is only valid in an async function oops I forgot to put this thing over there to close the string so now doing this and we need to call it something else okay we now get our RESP so we can do RESPA dot Jason okay and the reps dot Jason we can even see that it updated so if i refresh you will see that here as well we'll have our new values so let me refresh and as you can see the name is our new value and the email is also new at Pt dot P T so this finalizes our tutorial we learned how to do get we learned how to do put and a put and the post will be really similar so it's ok we connected our routes to a database we also did some bridge between our first video ever in this series about routing the routing that time was 4 pages now it's the routing for API but as you saw they work exactly the same way so I really hope that you enjoyed this one subscribe to the channel and I see you next week again bye bye

Channel: Bruno Antunes

Views: 50,954

Keywords: javascript, react, next.js, ReactJS, ReactJS Tutorial, Next.js Tutorial, Nextjs Tutorial, Nextjs for beginners, React next, React Next.js, React nextjs, react next js, ssr, react ssr, next ssr, javascript for beginners, typescript, next.js typescript, next typescript, nextjs typescript, next.js sql, next js sql, next.js database, next.js api routes, next js api routes, api routes, next.js rest, database, restful services, nextjs api routes, nextjs services, nextjs webservices

Id: PxiQDo0CmDE

Length: 33min 12sec (1992 seconds)

Published: Mon Mar 09 2020

Reddit Comments

[u/originalcr7]

Nice tutorial.

[u/DancingInTheReign]

Hi Bruno, I'm following this series and I love it, thanks for making these.

[u/vinnsubs]

This is awesome!

I’m a little new to the whole API/back end part of things - pardon if this makes you palm your face - but is it possible to make session based authentication with Next API Routes? Can I build an entirely user protected system with querying to a MySQL database as long as you’re authenticated?

If not, maybe you could guide me to some articles?

Thanks!

[u/bmvantunes]

Part 5 is now available - https://www.youtube.com/watch?v=k7bKDpw-JGk