Build a Node JS SQLite API tutorial

hey everyone how's it going today we're going to be making an api using node.js and sqlite this is going to be a simple movie quote api the first thing that we're going to want to do here is open up our project in visual studio code then we're going to want to press control and tilde at the same time if you're on mac or linux that's going to be command until day it's going to open up the terminal once inside the terminal what we're going to do here is type npmi body dash parser space express space sqlite3 and then hit enter these are the three packages that we're going to be using next we're going to make a new file we're going to call this file app.js we're gonna make another file call this table.js and we're gonna start inside of table.js we're gonna say const sqlite equals require sqlite3 dot verbose which is a method then we're going to say constdb equals new sqlite dot database which is a method and we're going to have to make a new file here we're going to call this quote.db this new file is going to be our database so we're going to want to link that by saying period forward slash quote.db then we're going to put a comma and we're going to say sqlite dot open read write and lastly a callback function taking in an error and we're just going to say if there's an error return console.error what we're doing here is we're establishing the database connection super important that's the first step next we're going to make a variable called sql and this is going to be what actually creates the table here so we're going to say create table quote we're going to make the first column an id of type integer it's going to be a primary key second column is going to be movie and the third column is going to be quote fourth column is going to be character then we're going to say db.run sql i'm going to run this twice if it worked the first time we should get an error the second time perfect we got that error the error is just telling us that it's already been created now we can copy this and go into app.js and paste it now we're going to require a few more things here we can say const express equals require express const body parser equals require body parser const app equals express as a function okay now we're going to say app.use body parser dot json parentheses and then app.listen we're going to do port 3000 and this is just the port that our server is going to be running on so first thing we're going to do is handle the post request for api we can do that by saying app.post forward slash quote that's going to be the route we're going to take a callback function with request and response inside of this callback function we're going to run a try catch now if we catch an error what we're going to do is return response.json and basically we're going to want to do here is just make something human that says hey this is bad so we're going to say status of 400 and success is false now inside the try we're just going to console.log request dot body dot movie and that's all we're going to do for now and then we're going to send it back as json by saying res.json we're going to give this one a status of 200 and we're going to say success true i forgot to put parentheses after body parser.json in app.use so i'm going to add those right now and i'm going to say node app.js to get our server up and running then i'm going to go into postman first thing that you want to make sure you do is that you have your thing set to post and you have the correct url for me that's localhost 3000 dash quote next thing to do is go into your headers what you're going to want to do is type in an additional header called content type make it of application json and you're going to want to make sure that's checked instead of the text one now as you can see i already have my data typed out i am typing this out in raw format raw json format that's how i'm going to be sending the data so i have a movie a quote and a character all filled in we obviously don't want to send in the id because that's going to be automatically populated so this should be all that you need and as long as you've set your headers and you have the right url and you're typing in like this you should be all set so i'm going to hit send and you'll see we got back a status of 200 and it was successful and if we go to the console look at that it console log star wars episode 3 revenge of the sith that's exactly what we wanted so for now i'm going to remove this console log and i'm going to say movie quote and character i'm going to destructure all of these from request.body at the very top i'm also going to say let sql i'm going to declare that early on then i'm going to say sql equals insert into quote and i'm going to say movie quote and character the three columns that we previously had of course and i'm going to pass in some values the values here are just going to be question marks for now i'll pass those in as variables later on so just three question marks then i'm going to say db run pass in the sql pass in some brackets now this is where i'm going to put the variable is inside of this array so i'm going to say movie quote and character just like that and then it's going to take in a callback function which is going to have an error here what we're going to say is if there's an error instead of just comps logging the error we're going to return res.json what we're going to say here is let's say a status of 300 success is false and the error here is going to be that database error we'll pass that now if it's good and we're not returning that we're going to console.log successful input and i'm also going to pass the movie the quote in the character i'm going to restart my server now and i'm going to run it again and there we go we got a successful input console log awesome that's exactly what we want here the next thing that i'm going to do is i am going to go off screen and i'm going to manually enter in probably three or four more things into the database all right i'm back what we're going to do now is at the very top say const url equals require and what we're going to require here is url going back down we're going to set up our get request so what we're going to have to say here is app.get and we're going to use the same url so forward slash quote again we're going to need a response and a request we're going to say in here sql equals select all from quote now for a try catch we're going to do the same thing we did before with the try catch so we're going to return that status of 400 with the success false if we catch an issue and we're going to try to say dp.all sql passing some empty brackets and this time we're going to take an error and rows in our callback function again if there's an error we're going to want to return that error as json so you'll see here i'm just going to copy and paste it to make it super simple if there's not an error what we're going to do this time is say if rows dot length is less than 1 we're going to want to return another error this error is going to be a little different we're just going to return a string for it what we're going to say is no match and that just means that there was no match so that will make more sense a little bit later right now it might not make a lot of sense then what we're going to say is return res.json status 200. right now we're assuming everything's good for the data we're going to pass in the rows and the success of true all right let's restart our app load it up and let's make sure that we're in a git request this time same url and when we hit send you can see we have all of the movie quotes that i entered here i entered four of them the id is auto populating which is exactly what we want this is working perfectly so far this is a nice looking api but what if we wanted to make it unique what if we wanted to essentially have a way to say well i only want to grab ones that are by this character or from this movie something like that what we can do here is we can set up a little bit of extra code to differentiate that on the api's end so that way we don't have to loop through it on the front end it's way better to do this on the back end so what we can say here is const query object equals url dot parse request url true and then at the very end we're going to say dot query what does that mean well that's basically just going to grab our query parameters which if you're not familiar with query parameters are i'll explain them in more depth when i show you me using them in postman next we can say if query object dot field which is going to be one of the parameters and query object dot type which is the second parameter so if we have both the field and the type we're going to say sql plus equals space where now we're going to use a where clause to differentiate this so where query object dot field is like double percent sign query object dot type so what is this saying what this is saying right here is we're appending it to our let's pretend we had something right we'd be saying select all from quote where let's pretend the query object dot field is movie where movie now like double percent sign just means contains so what we're saying here is where movie contains some string maybe it's star wars it's going to return every single thing in the database where the field movies contain star wars so maybe you'd have star wars 1 star wars 2 3 4 5 6 all the star wars would come back now let's set up our fields and our get requests so you'll see here we're doing a movie and we're doing the type of star wars boom just like that we get our one star wars movie which is exactly what we want but let's change it let's make it a little more complicated let's instead say character and the type here will be dorothy and there you go we got our wizard of oz quote so as you can see it's fully functional and it's working perfectly and that is going to be it for this simple api hopefully you learned something and you enjoyed it if you did please leave a thumbs up it means a lot if you want to see more content just like this hit that subscribe button i hope you have a wonderful day take it easy

Channel: Byte Myke

Views: 16,084

Keywords: Node JS SQLite API, Nodejs sqlite api, javascript api, node js api, sqlite api, sqlite3 api, node js sqlite3api, build an api in node js, api node js, api node js sqlite, node.js, nude.js, nad.js, javaScript, java, java script, node javascript, javascript backend, server side, sqlite 3, sql

Id: mnH_1YGR2PM

Length: 13min 38sec (818 seconds)

Published: Fri Apr 22 2022