Learn MySQL with Nodejs in one Video

Key Takeaways

are doing are doing and welcome to a brand new video tutorial from my opinion so today we're gonna have a really awesome topic talk about which is in the case or in the field of databases so we're gonna take my sequel database just give you a quick introduction where's my sequel database and most importantly we're gonna use it with the node.js ecosystem or even OGS developments in environments so I've been like using my co I don't really know how to use it with no GS you actually in the right spot to migrate from whatever you actually language you were using for example PHP or Java or something this you are using my sequel on you can just go ahead take this video through like a portal to how to know how actually my c1 no J's groups together like like a friends and how they can community communicate together to do cool things on the other hand if you're a newbie to my C wanna know nothing about my sequel well this is also a good tutorial for you to start and getting started with my sequel because I'm gonna have give you like the fundamentals you need in order to get it started in one single video for my sequel developer in order to be able to create your own databases add your own tables and create your own tea rows and columns and stuff without any headaches or problems just straight to the points so you can take this as your first video tutorial on my c1 so sign off this is go ahead and try to the first thing you think when when it comes to my sequel trying a new thing you've got to first Joe install the actual software in your machine in order to start development on it so my sequel has like like a community server which is you know available for community I'm also professional usage policies readjust community I'm gonna just develop some some simple applications on it so I'm just gonna use this really quick so it's my secret community you can go and just church that you can find the link in in the actual tutorial written story on openness calm or let me you can't description below optionally you can take you there so after you didn't though you can just go ahead and selected much evil community server and afterwards you'd you actually choose your operating system which is in this case on windows so you can do seelix windows a buncha whatever Linux Mac OS X whatever things in plenty of them available but there and you can just go and like click download wait for you to download then you can fully install it there's the MSI in here which is the Microsoft installer or you can just download a zip then in zip the the file one whatever file system or whatever diversity you watch on your own you're actually operating system but it's better to just download the actual install it gonna do the job for you perfectly just install this in normal application and there you go so afterwards it's just simple as you know installing any other program this is how you basically install the my sequel community or what is called a demon server so this is what is actually call it afterwards this is simpler we've got a problem you need to do now we just need to move on into the real project in a work on which is the node.js project so here are already set up like our projects pretty simple projects on node.js which is basically what does it have is just a couple of luck directories with a public that issue which holds an index.html let's say we have like an already reaching or Ryan application on Express or something like this just just hypothetically and we can have like an associate which has the database is the file we're gonna work on in here and we've got the outdoor genius in here which requires the database that obviously the opto G is gonna be our main entry point therefore we actually requiring the database torch a yes and for the packet or JSON we've got one require e we need to install which is on the my sequence of Lamar sequel driver or the my sequel package we need to like install an analogous it ought to be able to communicate with our server that we have just installed but still we haven't executed that server yet it's not running yet so we've gotta do to do this from you just go and do and be amongst all I need to install my see also and save it make sure to say with other real dependency that is simple as that just install it and there you go I advanced and everything now we need to move on into actually seeing the the actual my sequel server running on visual studio code in here and open up the terminal you can open up CMD for Windows or to your terminal on Mac or Linux and you can run the actual my seat server demon if you know how to do that if you don't you simply just go ahead and like you know whenever you installed your my sequel so make sure to know the diadochi where you have actually installed the my sequel server I for example for me I installed inside Zam my sequel been inside of the V directory or the D local Drive then just go and change it like I wrote you into there just do a CD and make sure to run there afterwards you're already just need to run make sure to do like four slash or dot for the special number to make sure like to run you to the current directory and run my sequel D with a pair boss and there you go so this is how simply you need to do in order to make the server she they running and start it up you can just put this command if you sure like the your path system has the my secret diary to sign up but this is the actual simplest and the easiest and the safest way to go through in order to run the actual my secret demon simple enough now we just need to jump back in here and we have our projects is actually running and everything is working too much fine now let's just go ahead and try to dive into the actual wine sequel now let's go to see actually how you can start with a my sequel with the basic thing is firstly how we can connect to the my sequel service it's actually run it so this is the first thing you need to think about actually how you can connect to the my sequel daemon your demon that you have just executed a couple of seconds ago so for that we first must go ahead and like do Const my sequel which is the actual driver you'll probably need and you'll have actually installed right now so we're gonna go with my sequel this is the driver that you really actually need in order to get everything that you you might need actually for your connection to the database and now afterwards we're gonna probably do is just we have to first like specify like a connection object like a configuration to this particular thing so I'm gonna do a cast like conflict for example and it's gonna be an object for this objects we're gonna have that a user which is this a user by default when you install the community server with default settings without changing anything at all you're gonna have like an a user which is the root user the main root user you're also gonna have a password but the banner is not gonna be valid so just like an empty password and most importantly you're gonna have like yeah the house and for the house it's gonna be simply on the local host because we are actually currently running in some place in the the our machine on the bulk of the host simple enough now we need to create another one of it just name it connection so it's gonna leave the main connection linked between the actual node.js application and the actual my sequel database that's currently running which is the actual server for my sequel and now we're gonna probably need to do just you know go in and create this connection so I'm gonna do my sequel don't create connection it has a method inside of that and instead of this we need to plan see the actual configuration objects and probably see takes a configuration objects and there we go so this is simply a problem need to do just you know holding them and put inside of the connection and there we go now after you just you know creating the connection and linking it now we need to connect or tell it to connect to the actual database so I'm gonna do this like they do a connect and I'm then I'm gonna do basically of connection don't connect so instead of this objects that is a method called connect and allow us the node.js application your basis of no genius gonna allow it to actually connect and you know send all link together with the community service you are running with or partially my sequel now this takes takes a callback function that the first argument of this one is gonna be actual error so if there's any errors of something that this objects the objects and clearly seen here is gonna be about it otherwise if there's any errors gonna be known so this is how basically no juice ecosystem works it always provides an error objects and if there is any errors the arabs you gonna find it valid otherwise it's gonna be justin also you need to know about always you've gotta check in for this particular error object so i'm just gonna do that and if anything I'm just gonna do console.log dot error and I'm gonna do like a connection so I'm gonna just give it the actual error message so to make sure that everything actually working pretty much fine and everything is running as expected to be and well just do is to close that and let me just go ahead and do now this is the first thing so simply now for this callback what it does so once the actual my sequel connection once we do that connection dock connect my sequel or an odious application in here try to connect the actual server if it does connect without any errors or something it's gonna like you know later only calls this callback so after the connection goes it calls the callback if there's any errors or something passing the earth otherwise the connection is successful and everything is working pretty much fine but on this side of this callback since it's asynchronous so you can't actually run the order of my sequel commands out of this callback function show so you have to run run it inside of this callback function in order to ensure that the connection is actually valid and it's already links and everything so there's nothing out of the boundary is happening or any other errors so need to make sure that all of your queries all of your my sequel queries and commands needs to run inside of this callback because it's very important since this callback means when he actually runs means the X the actual connection has been made and everything is working pretty much fine as expected to be now moving on to the next part that we need which is initializing a database which basically creating a database and you know having a couple of tables and rows to this particular database or selecting this database in order to make it our DVD but database to work on it so therefore in here I'm just gonna go in and create a function gonna name this function in DB which is gonna be responsible for like initializing the database and like selecting database as a default one we're gonna work on for the current application so I'm gonna do a constant you're gonna do like create two Deuce DB so gonna be a query and everything or pretty much anything on my in my sequel would work with my sequel everything is about or is actually something like a query so everything you run every command you run is this actually a query so for this query I has like this ability specific syntax you can check out the syntax a they're using a spurt cheese or like a sheet you saw something in this or the official documentation but it's very straight for this conspiracy it's plain English you can easily understand and go through this commands in queries so you can easily understand that was pretty really simply now here we're gonna tell it just go and create a database I'm just gonna tell it to create a database really quick it's not exist so what I'm telling you here to create a brand new database for us holy if and only if that's this database doesn't exist already so if it doesn't exist already the lip is gonna be created otherwise it's just like not gonna create the database is just gonna ignore this commanding you know keep executing or keep running normally without many problems and we're gonna hear give us the actual database name I'm just gonna name our database as up DB you know you can choose one of your database intern aiming here lastly this this one is actually optional so you can just go in and remove it if you want to create a database each time this query gonna be executed but this is really really bad because what you want is since this this commands gonna be initialized or like you know called a couple of times wanna see your application or every time an application can execute or start this this command is gonna be actually executed in a sense to the actual my sequel server so you need to make sure that it creates the database only once because this is really bad if you have like really sensational data out there so you might actually override it or you might actually duplicated database and have some issues in errors so this is a really really important thing to care of when when creating my sequel databases now after doing that we'll have to tell the actual connection object that is actually linking us with the my sequel server to execute this this query inside of this particular server and Grandpa Cynthia in this case we only want to just create a database so I'm gonna use the query method the creative method allows us to execute and run any query right and I'm gonna do create two Deuce DD query and there we go so this is simply well I don't know what I'm spelling here so to do is just there we go just just made that really clear and obviously after running the query and everything now what we're gonna need to do it's still the actual connection we want to change the database to or change the default database to our current database that we have just you know created in order to make sure that we will work on it or whenever we execute the next command the next command is gonna be actually executed inside of this new database which is the up DB now therefore I'm gonna do connection change a user and the change user takes a couple options you have like a plethora of options in here but we only care about the database option in here and we have got a given slot which they are based even wanna change on which is the alto DB and there we go simple enough this is this is all you're gonna probably interview and make sure as I've told you before everything all of the actual commands and queries needs to be run after you make sure that everything's working with what fine and the connection has been made successfully with the actual my sequel server so we need to do it inside of this callback of course now we're gonna need to do is just call this amid DB and there we go simple enough this is all you kind of problem in to do so after writing this we're gonna probably get is actually the database then a run and connects without any errors and hopefully we can be able to create and Lockheed a database called up top DB and everything with no problems pretty much so this is the actual code not to run the code we simply not me to do just note and up to genius so simple just just running the actual application here and make sure you're actually in sort of the nerdiest project or your root directory in order to actually be able to create that now we're not really getting any response because we have no no response in here so what we need to do in here we can just go ahead and actually run the ripple or the command-line interface of my sequel sound that let us actually interacts with the actual server is currently running rather than all of my secretly provided with the actual you know server a command-line interface that allows us to interact with this so you can own this my sequel and also make sure you actually inside of the that virtually where your my sequel server and such being installed I'm just gonna run this one and it has a couple of like arguments you need to pass in the first one you probably need to give it the actual user which is gonna be routes and obviously you have no passwords or just specify that it tells you into your password since we have no password just click enter and there we go you are inside of the database ripple now we need to make sure that there is a database that we can use the use command and we give it database name in order to make sure that we are actually going to use this particular database so use dB and there we go now the DB has been actually you know created successfully there we go it tells us database changed and it has been changed into the DB database name that we have just created using the the app store owner Jesus instance of our application here pretty good now let's gonna jump back into here and try it now since we created the database and everything and also we made sure that the database is gonna be only created once now we need to create actually set up the database what I mean by self the database or setting up the database is actually creating the tables and rows inside of the database and as you know that my sequel is actually original database running my relational database is all the tables and rows inside of that particular database has a relation to each other so all of them has a reason to each others that's why it's called a relational database this older things called no sequel she's no relational database like MongoDB for example but we only care about this point this particular moment and therefore we each created tables and each table has like a set of rows that holds the actual dinner so each row represents a specific type of year like an integer or string or character and actually a databases is like a set of tables and each table is a set of rows simple enough nothing really complicated just everything is pretty pretty damned forward straight for Russian now we need to go and create another query that allows us to create actually in tables and rows add all the different things together into that from database that we have just created so I'm gonna do create to-do query so this is gonna be responsible this particular one so we need doing here like creates table if no exists and as I've told it before always use that because your application get up one like you know a couple of times not only get our one once you only need to describe this if you're sure enough that's your application only gonna run once because you don't want to duplicate the tables and they're gonna throw you an error or something so make sure to like to do if not exist or add this constraint to wait to make sure that's not gonna be added or duplicated tables so here we're gonna create a table and we give it the name actually so the name of the table which is gonna be two dues and we open up a parentheses inside parentheses we give you the actual rows of this particular table a row has it unlike a couple of specifications like what is the type of row the type of data that this Rose can hold had the size of the row and obviously a couple photos say like if it's the default or a normal ball that's you know if you can actually put no value on it or you can actually it's a primary key which is you know a key that is user to access database a couple of things actually we can actually cover all the different things because a little bit more advanced thing to go into my secret database you can just go any Google this out but you can you got the points of actually things work so here we need to add a new roman just give it like the row name then you give us the actual real child it going to be like an integer so this ID rule always presents inside of any table if either on my seat or just most of them like 99% you find this ID because it is e use it for indexing or for actually being able to access the particular to do since this to do is table got howled like a couple of - dues so it's not gonna be actually simple to find that we have two got a key that allows us to a unique key actually for this particular to do that allows us to access be particularly to do we want using a it's key we give the integer time and we're gonna give it a primary so this what we tell is in here that's gonna be used as the key of this particular table and it's gonna be I'm gonna have also give it I get a primary key is I've told it before so and also we need to tell it that it's gonna be all to increment which is you know increasing point people once we add a new Balaji where it's gonna be increasing by default so this is simply what it is and we also need to like a title of this particular to do we're gonna be a variable characters so a type of fair char which is a variable character in here and between parenthesis you can give it the size of this particular time so you from this in this case I'm gonna use 255 which is you know 255 bytes that do be held in the inside of this particular title row and we're gonna just tell it not Norwich which tell is that this this actual row cannot be no so you can actually hold or store the null value inside of this particular role simple enough and we need to actually to separate the rows each row and another is to be separated by a comma Swiss furious that we need to provide a comma and the last I'm gonna need is a completed row which can hold a tiny integer so a tiny ins of one so tiny into one basically means the boolean so this is how you specify a boolean you can use a boolean but this is a much much better way to specify a bully I'm gonna tell it not to know and by default we're gonna have a zero value zero this was one minster so that's why I'm specifying actually only one value or one bar is actually you know needed to store for this particular because it's only gonna have either 0 or 1 to all fours there we go so this actually came in it might look a little bit confusing or I don't know a little bit complicated but it's straight to the floor I stirred to the point it's very simple and since plain English that you can easily read through and easily industrial is actually going on the side of this command without having that much of you know thing or knowledge about note or my sea voyage itself like not a problem I see well you can easily understand relationally going on behind the scenes side of here so now we also need to do a connection or query and for this we're gonna give it's like the create to-do query this one and for the query thing if you seen here it takes another argument or another parameter that is the callback so for this particular arguments gonna be the callback and as I've told you know Jesus ecosystem always takes an heiress first so we need to check if there is an error or something I think the table creating the table and the router side of it I'm gonna just do a console dot log simply like cannot create growth or create table and roads and we can just give it like much more information by doing the error message in here so it provides us and make sure just go on until I return and this one you can replace souveneir nothing really special of all this so this is simply what you need to do in here just to make sure that everything's pretty much working to be much fun and everything and afterwards after the initializing of theater base you can just go in and do a setup DB thing that we'll be able actually to set up the BTB and everything we won't be able to see anything in here in this case so I'm just gonna try and you hear I'm gonna do another function that allows us to show or grab the error phone database and showed actually on the actual screen or on ODS altercation so how we can fetch data from the database into our application in here so instead of inserting now we need to fetch it inside of here so I'm gonna use I'm gonna call in here insert today's to deuce or Roberto saying here we all actually is setting some data inside of this and then we can actually fetch it to us because here we only create the actual structure of the my sequel database then afterwards what we probably need to do is actually insert real data like insert a real to do into it and reserve like completed false or completely true or something like this so this is what actually is about and we can we can do that actually with the new command so I'm gonna mean like insureds today's search with use and it's gonna be another one so in this case we're not gonna use the create command so I'm gonna use the insert command that allows us to insert data into a specific table and a row of this particular table and I'm gonna use into so we're gonna give us the actual a table we want the inside of the table we give you the actual rows we want to insert data into so just give it here in this case we only give it at first the rows need so each row name here is gonna be first I'm gonna give it like the title and I'm gonna give it a completed and some of you might ask why we haven't actually provided the idea here why we don't say I'll give you remember that's out of the ID can clearly remember we told it that auto increments and it's gonna be a primary key so once we add a new field or a new instance of to do to the actual table it's gonna be automatically incremented the actual ID so my sequel is one enough to know ball is it's gonna do the job for us by Inc remaining it's like adding one and this is gonna be an integer so it's gonna have 0 1 2 3 4 and so on and so forth plus many to do this you have the IDS cannot be there so it gets you back with no problem so you don't really have to - and make sure to do not because this is really crucial to make the IDS unique and identical to very specific to do now here we need to give it the actual values so as value as you can take it like a function and give it a parenthesis and here we give it the actual values and for the values I'm just going to take a parameter in here like an argument and we can actually use that so yes sex actually helps us a lot and provides us with or know Jesus as well provides us with these trendy rules which is like you know using this quotation inside of the quotation mark you use the string literals mark and here we can actually give it like um you know two quotations in some of the cretaceous we can grab the variable theme so using this specific syntax you can go ahead and google string clitoris to know more about this i'm gonna give an actual title in here oka values and the second thing here we can just give it our for example just gonna be false simple as that without providing any themes i'm just going to give it like a false or something you can use this parameter but still just keep it simple always gonna be false and everything so this is the actual first thing he needs to do now we need to do the actual query you can hold a query or something but we're gonna do the connection doc career you can and here assert today's to do is obviously you're gonna have a callback which is basically the same as we do have in here so you're just gonna copy this really quick yeah so we're gonna have like an another one you believe or something well okay that's a little bit confusing to be honest okay there we go so we should have put that in here and we can we can do less you know cannot serve something in here so to make sure that i received much working fine as you expect it to be and this one will allows to insert into the offshore database and everything now we need a lot of function that allows us to see what we have or he inserted so it grabs and he shows all of the Vienna regarding to a specific table or a specific row in specific conditions of course so you can add your own constraint like with an ID equals to 1 or only grab me the actual to use that are not completed yet or with a specific title or something there's a plenty of plenty of things you can do a plenty of options you can probably call covering this particular video tutorial but you can check out the documentation there's a plenty of tutorials out there detailed one like recent story detailed one with how you can do get different things and different options available with the query system on my sequel so I'm gonna do here show today or today to deuce okay and here like today's do's and I'm gonna just go ahead and do another query I'm just gonna do it like fetch today's to do is simple enough in here and we're gonna create another one in this case we're gonna use the Select command the Select command you know takes like whatever rows or what's all the rows game you want to grab in this case we're gonna grab all the rows record into a specific table which is gonna be the to do this table so I'm gonna do a star and abstract star which tells it we want to everything all the rows to ground from the actual table and get it right over here so we tell it from so the family you have to tell it I'll show you from what table so therefore I'm gonna tell it to use the deuce table in here and there we go and we can tell it like for example where so you can specify where which is in this case you give it like a condition that only matches for a specific set up to do is that already stored in the database so you can just give it like worth completed equals to 0 sure remember you can just give us your run in here I just like a boolean variable and completed but what do you what it is actually it's its particular row and when we use the ashes we tell it which rows we want you can just go in lucky the completed rows what we want all the rows like the title the ID and all that you see the completed row simple enough in here and obviously we need to use the connection objects to curry dance so there we go I'm gonna do it in French today's to duce and it takes off callback so you can just go in and past in the actual callback we need the right over there well our no it's actually happening in her thoughts okay there we go so if there's any errors or something we do that but in this case it's a little bit different you know so we wants to fish the actual deal with not really serving anything or something like this we're not creating databases or tables we want to fetch data from the database so instead of this callback we can overseed another thing beside the actual error I'm gonna receive the result also you have the fields or something which is a little bit in advance you're actually needed so this little actual results this objects or array of objects cause the actual deal you need to actually the data actually tried to fetch all you told my sequel to grab you at the data you to let to fetch this is where the data is actually is so inside of this result you will find the offshore DNS or gonna probably need to do just go and console log Deanna and I'm just gonna do results so there we go simple enough and this just save that and now we need to go ahead up on this so we set up we initialize the database we set up the database then we simply what we need to do is insert two days to deuce so we're gonna just gonna try to insert a to do is I'm gonna me go and a name is boy on buy eggs or something there's something really simple and I'm gonna do fetch thing when students to deuce well did I need this I mean in this show you these do's and there we go so this should actually working much as expected to be so we need to just go ahead and do like a node and we're on the actual application or Randi with this particular database so I'm gonna do output Jason here and should be able actually to see everything is happening and there we go we've got back and raised so ever did run this once so that's why I'm getting to actual the other names of getting kids ID with one ID to buy eggs my eggs and it completes both are zero since we told them that's where Q equals to zero and we already inserted them in here with a false value so everything me in this chair is gonna be false and everything's gonna be actually zero so there we go you've got the point actually how are the new works and how my seek will pretty much interacts with with the actual data and how arranges and how constraints and conduct conditions pretty much works with the word completely procedure and everything working as expected to be so you clearly seen here and super nice and super simple to work all different things you can add more methods in here for example we can add a method to actually delete some Vienna phone database so deletes to do's and we can add that we click in here so for this one gonna have another query so the leaves career in the limit and for this delete query we're gonna have is actually police from and you type which table you want residues and you actually toilets were or what's actually the specific you give it like an actual condition you can take an axe dealing with an infestation so you have to give it like a condition to delete a specific rows that meets this particular condition so you can give it like verb ID equals to I don't know you can give it like an ID equals to one or something like this so it's a it deletes the particular row that has an ID one or you can delete like within title equals eggs or buy eggs so all that everything all the rows that has a title or by tax are going to be deleted from the actual database or even much simpler you can delete all the rows or the actual records that has a zero which are false and everything so you can they can be actually deleted from the database so you can easily see us actually happening here you can execute the same thing and here so I'm just gonna this would be quick we can we can delete the query in here and you can get the actual result and see it so it will actually be you know false we can we can run that in here let me just go into the least before the show to do so you can still see the I'm sure to do is I'm gonna do the lead to Do's and this will lead everything that has a completely with equal 0 we can get rid of that really quick and we can run the know digit application and there we go we've got the data to actually n MC array don't take care of this because this results shows some information about the actual database of all the table and what does it have a side of this so we and it's not really important that much and we've got the database in here with with an array just an empty erases we deleted everything so this query actually does a really good job of doing through this particular one you can also have an update query that allows you to update a particular to dues or something you can do like an update crew in here for example and this object really what it does it takes the table name that you wanna update and a set command this kept the set command it tells it what to set what are the actual fields or rows you wanna set its value to what you want to update the volume so for example gonna do like completed we're gonna update it to one and what's what are the actual rows we want to update like you give it the condition the word condition and we can tell it where ID equals one or ID equals zero or we can tell even completed equals to zero so all the completed it was a zero gonna change their values from from 0 to 1 which means makes it like a completely to do or something this there's a plenty of things actually to use while this how basically the update structure or the update command pretty much looks like and it's very straight forest to the point in the problems some clear is in here with a career command on my sequel it helps a lot and use it in OGS it makes even much much simpler working with these all different things that stuff you can use like graphical user interface some software that allows you to see your database in action interacts with in and manage it different things but you're still actually you can use it that way and manage all different things and use it on no GS in here so that was actually for this particular tutorial that was a really long video story I'm pretty sure about this but hopefully you've got everything that you need about my secret in order to get started because this is not the actual end this is only the beginning if you want to dive and dig deep into my sequin know more and more about and create like advance application this is just the first step now I've got a go ahead like you know see more tutorials if you want for me to give you like an advanced tutorials just put them in comments or people have I'm very happy to do more stories of all this I think goes watch it I really do hope you guys have enjoyed I will see you all hopefully in the next one