Simple API using NodeJS, Express and MariaDB

what's up everyone my name is andrea and welcome to my channel called academic um i'm trying to get started in the world of online videos as well um so the first one i'll do is just a video on node.js express and mariah db um just the basic how to connect to it to a database to a little query and present it in in a api type response you know like in json or something so what will what we'll cover is uh just adding the code to get i want to make this available to you afterwards so whatever we do in this live coding session i'm not going to edit that apart from maybe adding a logo or something i'll i'll add it to to git you can access it afterwards so we'll set up a basic express app and just get out of world displayed on screen then we'll create just a simple database we'll just create the user database with the id field email and a password maybe a created update as well then we'll connect our express app to to mariah db so we can actually query some data and we'll do two routes routes routes i'm not sure what your language or your country pronunciation is so i'll call it a route so we'll have a get route to load a user and a post route to add a new user and then towards the end just as little extra also encrypting the password otherwise it's going to be stored as clear text so assumptions that i've made in making this video is that you have some node.js experience and you've got mariah db installed you should be able to just do the same for my sequel as well as they are kind of interchangeable mirai db is open source and free version some i mean there's some fine print there but let's just say that um you've got a tool like postman available postman is a testing development testing tool where you can like do post posts or gets or all kinds of actually http type methods with data of your choice and then i expect you to know at least some javascript and sql you need to know what a on the sql side just know what a select statement is or insert statement or something like that you know the basics of running a node app um i'll show it again but i'm not going to focus on these kind of things and then you know what git is and what is useful that's primarily just to get this afterwards or if you want to follow along with me adding the stuff to get and then a very big disclaimer this is my first attempt at making a development video for youtube i'm going to go in real time i'll probably make a mistake or two and i have to fix it i might pause the video at that point to just dig around find the problem and fix it but i'll just continue afterwards at the same point where i left off and then also english is not my first language um i think i understand it and speaking well enough but i might not sound like a typical american or or european when speaking this this this interesting language without further ado let's get going so like i said the first thing we'll do is to add the code to get i've got an empty directory here i'm running ubuntu 2004 by the way but everything you see here should work fine on other platforms as well um got empty directory just the readme file that we just went through and i want to add that to git as like the initial commit so yeah let's bring this over to the side so i've created a repository on my on my github account just called node express and mariah um my connection method is ssh i've added my keys and everything i've also created the readme file so let's just follow along and say git inet so it creates a initial empty repository i'm gonna hit add the readme file and then also we need to add the remote origin and let's push oh get commit it's probably going to ask me for some credentials now um yes expecting this okay get config [Music] you are welcome to contact me um get config user dot name name um in fact let's rather make that decision monkeytab github username to avoid confusion okay let's try the comments again here we go and then we're going to push it minus you to track origin [Music] master there we go so if i refresh this side there we go so the code is there and we can start adding on so i'm gonna code on the master branch i'm not gonna branch off um it's just it's a simple little application so i'm not going to go crazy for now we can close this and this and we need to start by creating an empty project so in a minute we'll call it the sdb demo the version is an important description a simple application to demonstrate how to connect to advocate from and express entry point i'm not going to make it index.js i'm going to make it server.js this command we can skip the repository is there keywords let's not worry i am your author uh let's make the license mit and that is there we go we've got a package.json okay first things first um we need to obviously add the packages that we require so the first packages that we require is obviously express you can see it's been added to the package.json as express and i'll also add another package on here that i think is very important which is called.env e8p i'll explain why in a moment um so dot env is if you don't know it yet is to load environment variables more easily into your application um and in dev mode specifically you can do that using a file so in my case values like the database that we're connecting to or the password for that database or something like that that is typically something that you would need to provide by the command line and not hard coded so what i'm going to do is just create a dot file dot just hidden files dot env local we'll just call it that and then we can things that you would typically type an export statement for but something like db host equals localhost in my case now in my code later on i will be able to reference this dbos using this.emd package but we'll get to that for now i think that's the important stuff let's get a basic little server running so create a new file called server.js at the top of the file cons express which is going to pull express into the project and then i'm going to create a variable for port and this is the port that our application will be running on so i'm going to say process dot env dot port so that's something you can add to this file let me just start with that and add the other ones use port 8000 or a default of maybe let's make it 3000 so we can actually see the difference okay so this is the i'm going to use the the um whatever you provide as an environment variable for port or it's going to default back to 3 000. next thing we need to do is then instantiate the express app create a variable call it called app which is just express invoker function and then we're going to load some middleware and let's comment that here where uh app.use express.json we want to make this a json-based api typing restore api we need to be able to use urls that's been encoded these are just some default things you can probably set up in most of your projects from the start and last but not least we need to start listening so app.listening on the port that we defined earlier which is that one there with a callback function to say okay once you're listening let me know that you're listening all right so console.log i'm going to use string interpolation here listening on listening for requests board like that so that way the command line will tell you what port you you're listening on but this little app that we have here is not going to be doing anything at this point you're going to start it up and it's not going to do anything so let's show you maybe okay and we can't start the app yet because we don't have a startup script so yes you can technically go node server.js and that will actually run it that you can see listen to requests onboard 3000 or you could add a proper script so at this point i should probably mention a little package called nodemon npm install minus g node one minus g obviously for global so it will be available everywhere for you and nodemon is a little package that autorestarts whenever you change code so it's very handy in development environments so i'm going to add two scripts here i'm just going to start which is essentially what we just did in what you call it node server.js which will start it normally or we can say dev and that would be nodemon server.js so node 1 is simply just a node type startup script the difference is it restarts every time you change your file and we can remove test okay i've already installed norman that white just cleared that so if we do n2m run dev you can see starting node server.js so it's just starting in a normal node process and listening for request on 3000 if i now change that 3000 to 3001 and i've saved the file it restarts automatically and now we're listening report 3001. very cool okay so we if we're listening for 3001 and i navigate there ok it's going to tell me it cannot get anything at that root reason being that we haven't defined anything at that root so uh let's just do something simple um let's create a normal get root app.get and we're saying just for slash or anything from that point forward should do the following now express sends or gives you two variables to work with being a request and a response variable let's write it out fully at this point but i'm going to shorten it later on and we're not going to use request for now but we're going to say response 200 meaning it's successful dot send and um because we're working with json let's send something jsony so instead of sync let's use json and say name henry doing coding okay so if we eat this now let me bring it over to the screen sorry local host 3001 there we go we've successfully called our first get root and it brought back exactly what we told it now this is cool and all but it's not really doing anything so let's just go back to the readme and see what's next okay so we added some code to git we did a basic express app and what i just showed you was my version of hello world and now we need to create a very simple database i'm going to do it in a gui app called the beaver you can use command line text uh i'll probably add that to the to the online code as well um and we can um there's tools like table plus and what have you but tpv is quite cool um you can just go to i think it's deeper.org if i'm not mistaken nope not there let's just to be the io okay cool so there's a free version and a paid version but for most things that you do the community edition is fine um i'm running it 100 on ubuntu it integrates with eclipse if you're a java developer so yeah then also i've already set up a database on my localhost it's called demo user there's no tables in there yet like i said at the beginning i'm assuming you have some some knowledge of sql and mariah maybe or whatever otherwise you probably wouldn't be here okay so let's create a sample table um i'm going to call this table user the engine doesn't really matter for what we're doing here and create a new column first column is id i'm just going to make that a normal integer column not now auto increment get to get to the key just now okay second column is just email i'm just going to leave it to defaults but i don't want it to allow now i'll create another one called password i'll also leave it to default and not null and then last but not least let's create a created at it's just handy whenever you're creating a database server created apps and then you know when the record was created you can also go further and do updated and so on but i think that's a bit beyond the scope of this little tutorial so created just defaults to current time stamp and we need to make the date the type date time there we go and then we need to add a constraint uh for a primary key just make the id column primary key that should be it we've got an id email password and a created ad and if i just ctrl is to save it this is the actual script so let me copy that to the project create table so that will be in the git repository later on as well and you can pretty much copy it to create your own version okay i'm going to add some some data in here just for example um let's create a new sql script insert into user um we're not going to add id because that's auto increment but we will add an email sorry let me just confirm the columns email password email password values of test user.com and we'll make the password our first password no database selected ah so we've got localhost and the database needs to be demo useful there we go it doesn't exist insert into demo user email password don't have the table as part of it there we go so if we just refresh now we can see user id of one is test at user.com there's our password i'm gonna add another user just so we have more than one let's make it test run this we go okay so we've got two users we've got something to work with heading back to our little application here at this point instead of just getting a you know a random hello world style output let's rather go a different route and get stuff from the database which i suppose is why you're here so at this point i'm going to instead of loading routes by default here let's make this just a this is not by your wire head to the user and and replace id with your user id okay just see that that's working okay this is not where you get here to user call on id and replace id with your user id so what this actually means is instead of trying to launch this page please go to user and specify your id either one or two which obviously won't work because we have encoded it yet okay so what we're going to do now is create a root um so let's create a new folder called roots and in the roots folder we'll create a user file user.js and in here we're gonna like this is just separating concerns we just want the the root file to be for whatever reach we have to be somewhere else i mean there's nothing stopping you from writing all the code in your server.js file but it gets very messy very quickly so um cons express equals require express which is loading in express again and then construct router equals express.rooter so what we're doing here is essentially just creating a express router which we will pause back to the main function eventually okay so same thing we did previously we're just going to say router.get and we're just gonna test this first so it should be um id code on id and i will explain that in a minute this will be in let's make it a normal function for now but it will be asynchronous functionality um it gives us a request and a response and the code will be all it will do for now is just to do a raise dot sin race of status of 200. you might see me falling for that here but i was really interrupted by a phone call that i had to take okay so we're going to send the response with the status code of 200 and just give it the let's make a json again and provide the id that we give in just back to the user so what we do here is we say take the request object request dot params which is any parameters that we get from there dot id in this case that id okay so it's still not going to work at this point because now we've set this up but we're not linking it anyway so module what we need to do is export it from this file audio dot exports equals router or router and then import again on the server side so under these routes what we can do here is say const user equals let's call it user root or user route i'm probably going to say it wrong for whatever place in the world you're living equals require and we're just going to require that root file oops user and then we can just say app dot get again and now we say user and um just give it that truth that's actually it oh what's it complaining about now requires a callback function not the app.get to the app.use so essentially this router that we created here is a piece of middleware which we are just loading as a route in the front like facing part of this application and there's still a issue it requires the middle loop not a wave function but received an object there we go now it's happy okay let's try user one there we go so it's all not connecting to a database um what it's doing here now is you're saying listen you entered one there as a parameter so it gives you one back one back and let's see so we could make this a thousand or a hundred or in fact we could actually make it alpha numeric text it will also work i'm not going to handle like validation and stuff like that in this small course but uh yeah there you go that's now working so we obviously at this point want to load data proper data from the database based on whatever the user added yet so um i think the best thing now to do is to create our helper for databases so the the default things that you would need for a for a database is a host obviously being localose in this case a username a password and what database are your connection connected to um so let's create a folder called helpers and in the helpers folder called database players and in here this is where it gets funky now so we don't have mariah yet we need to add mariah okay package called mariah i think that's what it's called give a few seconds to install there we go and variety is now added version 2.5.3 okay so now we said const mariah db equals required still doing nothing and now create a pool so a pool is like a bunch of connections um or something that defines a bunch of connections um but you you define the settings so we're saying mariah db the object from there dot create pool and what we need here is things like host which we will get from process.emv.com db host don't worry we'll set that up now uh user processes user a password process.uv dot db boss database what's the name of the database and that will be dot emu.db name and we're going to take the connection limit to five now i'm speaking off the cuff here but i think that means there will not be more than five simultaneous connections to this pool from this specific application um but please read up on that on your own time okay so connect and check for errors is the next thing we need to do so we'll take this pool to find here and get a connection from it get connection this is a function that returns an error for a connection sorry that needs to be arrow okay so if we have an error let's try and handle it if uh that code equals now it might look like i know exactly what these are but i had to read up on them as well you'll probably need to do something at some point there are more of them i'm just using the most obvious ones so what we're saying is if we get a protocol connection lost error then we just log it to the console as database connection lost let's add another one in there and say er count error and this pretty much means database there's too many connections and one more he i think that's pretty self-explanatory a database connection for us this is probably a wrong username a password or something like that if there's not an error we won't do that and we would just say if there's a connection the second parameter which we get um then let's release it once we're done with it and return and then last but not least we export this module module dot exports equals four okay so this is just a bunch of random text but essentially what we're trying to do here is have a single place in the application where we handle our database connectivity and then we can just load it load this pool wherever we need it okay so the next thing we need to do is make sure that these environment variables are provided wherever they are needed so in back in the server.js file at the top where we added express i'm also now going to pull in const.enb which equals the record up there sorry fire dot env and i'm going to tell it to listen require that's just stupid sorry uh we're just going to say dot env dot just run it with it i'm being stupid here we need to tell it that it needs to use this evie on e and v or c dot local file as a default so dot ev dot config we'll give it a path and in this case dot en dot local env and just so we can test it let's just uh add on here what was the emv variables that we added port a thousand okay so by default in the top of our server.js file we say if upward is provided use that otherwise use 3001. so if we start this up now and whatever i did here is working correctly it should be using port 8000 so input and it's not working as expected what am i doing wrong let's rather maybe try it this way it's all not working let me just figure this out and come back to you a little stupid the file is called dot env local and not local dash env which is how i add it like that so just put it the right way around that's the one thing and the other thing is port 8000 was already on in usa on my uh box it says app crash um address already new so i just made it a thousand and one so let me just show you if we make it two and save it now this is not a file by default picked up by node mod so we'll have to just start it manually now you can see it's running 8002 um just so we don't have to refresh the whole time i am going to default it back to 3001 because uh i wanted to remain what we had in that in that window yeah see that it still works there we go uh two three four five yes still working as expected okay so back to the database problem we need to provide these things as environmental variables because if we provide them as hard-coded values it's just bad practice it's going to bite you somewhere please don't do it make it a habit to use this type of structure where you provide the variables from the command line um so the ones we need just gonna do a little bit of air here oh give yours db pass and db name right so about that i need to make this pause with something that you are not supposed to see so it's this new user create new user let's call it a demo user for this host we'll just make the password password p a h [Music] um i think that should do it schema privileges will give them access to all of them allow everything so there's a lot being done here which is probably overkill but yeah okay so demo user with a password of password if we go back to the code now the host is localhost the user is demo user the password is password see that i didn't want to give you my own password there and the database is called demo underscore user you know right so if we go back to the database now we should be getting this from the command line if we restart this because remember the node process needs to restart to pick up whatever is in the env file um now restart it now we're not gonna see anything but if we now use um we need to now use the database so let's write a query now remember all we did up to now is just take that id and write it back as part of json but what we're going to do now is to say let's do a try catch instead in case something goes wrong so if something goes wrong we say response dot status of 500 let's make it 400 dot send and we send the error dot message but if it goes right what we do is we create a sql query we say select the id the user or the id email the password and the created ad from i can't remember the user the table now it's called demo underscore user the table is just user okay from it's right now let's just say user and you say way id equals and we add a question mark question mark is the parameter that we're gonna provide so what we do now we also need to change this to asynchronous function because we might be waiting for things to happen what we do now is we get the result rows it should only be one that lets let's use the plural here which will do a pool and see it's picking it up from the helper database like just click on it and then it pulls it in properly but you might have to type this out manually if using a different editor pull.query and we're just using the sql query here and we're providing a parameter which is params sorry request doctor and stop id cool and when we have these rows we send them back to the screen okay so let's use a code of 200 and provide some json and just the rows like that apologies okay so let's see if this works if we say user one splitting a little there and it's not quite working like i was expecting ah something went wrong cannot send headers after they were set let me just figure out what i did wrong here were there for a while um to figure out what was wrong and you will not believe the idiotic mistake i made but anyway so what we did here is we've got a okay let me explain um the issue was in this envrc local that i have here i had a bit of a syntax problem and this is when you're used to working with json and javascript too much i added commas at the back there which made all of those values wrong and it wasn't able to connect to a host called localhost comma and neither were any of the other values correct but now there are um sorry so going back to the user file um we're going to use this id whatever we provide so let's say it's one then we're going to select from the user table wherever the id is one we're going to select id email password and created it and then this is the actual query being executed which is pass the string in there and then the requested perhaps of id as the actual value that's being provided to the id there and then we whatever the result is we just write this json to the to the screen so back to this you can see i actually ran it once already but you can see i used the wrong character there's no such id let's call it a b c one two three no such id but we do have a user with the id of one being test at user.com and i believe we have one for two as well here we go so that is a very simple little get request to get something from a database using a database helper i just want to get back to this database helper you can see i did some blogging now to try and figure out what was wrong um if you have a file like this with the pool defined with the settings and so on and you import it into wherever you need it like this it means it's already ready to be used whenever this file is called the first time that mariah db create pool and the get connection these handle the connections for you and make sure that things happen like they should okay so the next thing we wanted to do was let me just get back to our little github or where i've got the readme um we created a get root for loading a new user now we're going to do a post root for any new user okay so let's get back to the user file and in this case we are not going to be doing get request we will be doing a router.post and instead of providing an id we just say register and okay something else is important here okay we don't actually say user express json and refresh url in code which means the body parser things like that is loaded as well um okay so this is the async request again because we're going to be executing async query i think function that gives us a request in a response object that we can use we do the try catch same as we did above you can literally actually just copy that part put it there and okay so what we will need is and the the fields that we have in the database is an email and a password this one is auto generated this one is auto generated so let's get back to the code and say let's get the just use the structuring to get the email and the password from the request operand i request dot body sorry so i'm going to use postman to demonstrate this now but essentially whatever we put in the in the register like that so i've got henry at this.com password this is if you're using postman you go to the body make sure this is a post go to body put in your json and make sure that you select the json year as your data type if we now go back to this these are the two fields that we're going to use to insert into the database okay so now i'll just say on sql query again you don't have to do this part you can write the query in line but i like to keep it separate just to insert into user and we're going to insert an email and a password and the values will be two little question marks again and then we do a result we create a result object which is going to give us the id of the field that we inserted so we do pull.query sql query right above us and we say this is an array of values being the email that one and the parcel so make sure the order lines up so the first one in the array should match the first question mark the second one in the array should measure the second version um that's the result and then we can just do a rest.200.json and in this case i just want the user id for the record that we just created so it would be we can okay let's let's first write the entire result to the screen and then i'll show you what i mean okay so register we will provide an email and a password which we get from the body we're going to insert it into the user table as email and password and then the result i'll show you the result now but one of the fields is like the inserted id okay so let's use postman user register value that and the password of that let's send we get a user id of five oh that's the other application i'm running ha because that was right uh 3001. there we go this is so the original post has a response status let me just give postman again of 200 which means everything is okay and it's giving me the entire result object back in json format so if we go back there you'll see there's affected rows one the insert id is three so this user that we just created the 11903 and there's no warning errors that came through so what i wanted to do before now was to say just give me the user id and that would be result that insert id okay let's run this again make it epidemic there we go and that's user id of four so if you go to the database new refresh now we've got a few things here okay so i mean that actually just covers the basics of this um little tutorial that i wanted to do um i mean i said we'll connect the app to my id we'll do a get root the post route and then there's the extra encrypting the password so it's not difficult i mean this is obviously an issue if somebody gets access to this database and you've got hundreds of thousands of users now you also have their password in clipdeck so this guy can probably just use that email and password everywhere because let's be honest most people reuse the same password sorry just add some water so the last thing we're going to do now is let's let's commit at this point so i'm going to add everything once i get to that um working get plus posts okay uh yeah we can push that master so now we're gonna do some encryption and the the library we're gonna use is called bcrypt um i'm not gonna go into too much detail with it just show you how to do a basic use use case um so what we do is npm install the crypt okay it's been added so now we go back to the user and what we need to do is before we you can still send the value in as clear ticks i mean you you should have you should have your website set up to or your api or whatever setup to use ssl so it won't technically be clear text by the time it arrives at the server it will be decrypted properly and so on but what you need to do is take whatever you receive because by the time it reaches this runtime it will be clear text again um whatever you receive you need to then encrypt again before you put it in the database and then also provide a method to decrypt it afterwards okay so what we'll do is take that password and say create a encrypted password from it and all we'll be doing is to say hey wait be crypt says decrypt dot uh what's the thing again hash will create a password not really encryption okay and then we just take whatever we receive from the front end or from the client like that and salt around so the default value for salt around is thin um i'll just show you something else so assault is like a a random first section of the of the password um you can create it in two steps as well we can say salt equals a weight b group dot sort and then provide a value there let's say but i'm not going to do that i'll just provide it in line it's a single command the default value is 10 and for 99 of use cases that's good enough if you want to know a bit more um i suppose the best is to just go read up on it i'm not gonna tackle too much okay so what we do now instead of taking that password and just adding it into the database we can now gonna use the encrypted password like that okay so let's not be on apm run dev and let's create another user so let's make it a very clear password although that is this is my second one with this is the password i mean let's get text this can be and we said academic at youtube.com the user id of five now if you go there look at that good luck lacking that thing i mean that is a ridiculously random string that makes everything no sense to me and in short that's how you would uh encrypt or hash a password encrypted is probably not the right right word yet so i mean there's now a bunch of things we can do further i think the last thing maybe is let's let's compare passwords so you're probably going to write some kind of a login logic at some point and one of the things you need to do is to compare the password now the spot i did not prepare at all so let's try it on the fly i'm just going to have a supporter so what we do is another post route called logging and this is function with a request and a response the normal dry catch we're just gonna use the same catch there okay so what we do now is uh how should we tackle this we log in with the user id let's add that as a parameter now let's use the body only let's use the body so same as we did here difference being we get the password and the id from the body and then we do a query to get that user it's equal user this is just a string select password from user where id equals constructs equals i wait sequel get user so if there is rows there will probably only be one so i'm gonna have some bad coding style here but anyway um let's just do it one step at a time here and say raise dots take this of 200. json with the rows and having the first record because there probably should only be one um otherwise we do a restart status of 200 still okay request but we say send user with id use interpolation user id uh id and this comes from there was not found it's probably better to say uh invalid username and or password and be a bit more vague but yeah okay so let's see if that part works login it's a post request so we go create a new one http localhost 3001. user login and we need to provide a body raw body json and that was the email we'll probably have to create a new one let's create a new one let's register a new user of demo at faster.com and the password is just pause worldwide to think okay so that's user id number six just double check use id6 it's got this random thing here okay so now we do a login with id6 okay id f6 and a password of random password which is wrong what's it complaining about now ah missing okay so for the user login it's a not a get a post there we go so it's getting the user and it's giving us the password back it's not what we i mean it's not what we want to do in the end but it says and wet query get whatever user you find where the id matches and give us the first record it's essentially giving us the pause with that's the only thing we have in the select field um where is this now oh we were using postman so yes there's the pulse word now we want to validate that whatever we added here being random force with this case is that actually the same thing as this now i remember the code being something might compare so what we do is const is valid is decrypt dot compare and uh the data that we want to compare is whatever we entered sorry encrypted string yes so password is the one we entered let's make it clearer inter password we want to compare that to what we have in the database dot password remember that's the one field that we selected if it's valid instead of sending the actual password just say is valid and yeah whatever the venue for these better views um make it a bit more clear valid okay that's one random password is not what we used to register with it was what reports would one two three so better pause not what i was expecting what does it give us back ah remember this gives us a promise which means we should wait for it to finish data and hash arguments required what am i not doing you know what this is the problem we are pulling from the request.body and entered password and providing it to this compare method um the compare method is complaining that it's getting data and the hash arguments are required it's it's as if one of these are undefined which makes sense because we're not giving at the correct variable name there it should be entered password and not password so i think we should leave it like this and just make this password again and password just so they match up and let's try again there we go valid pause with false so the password should have been password one two three value pause with true here we go okay something is complaining here so i'm not quite sure what that oh that's that same error that we had previously i think what's happening is there's more than one thing being seen here i'm not going to worry too much about that at the moment what i did cover though is adding the code to git basic express app with hello world style thingy um we created a simple database using dpv you can use any tool you want we connected the app to mariah db in this file here we did a get root to load a user we did a post route to add a new user and then we encrypted all hashed which is the more correct term yeah the password so that it's not clear text and that is it in a nutshell i hope this made some sense to you it's my first attempt at doing something like this um so yeah let me know what you think and subscribe and give me some ideas for things you want to know in the future i'd love to hear what your thoughts are thanks bye

Channel: Techedemic

Views: 3,414

Keywords: mariadb, nodejs, javascript, express, expressjs

Id: krTbf0O-BCo

Length: 59min 47sec (3587 seconds)

Published: Mon Mar 08 2021