Learn knex.js with Postgres and express in 35 minutes

now what is going on guys today we are going to take a look at how to connect to a database in node.js with the next.js query builder and for the database we're going to use postgres so make sure that you have postgres installed and running so i already have it running so i can show it to you here i can say rule services ls right so you can see i got like postgres running over here so that is cool and the library that we'll use to connect is this one here it's called next.js it's like a pretty popular library for connecting to a database so i would say let's just get started and let's see how we can connect to a real database so open up your terminal wherever you want to create the project and i'm just going to make a directory and i'm going to say next.js tutorial and i'm going to cd into this tutorial and i'm going to say npm knit dash yes and this will create a package.json file and let's just open this fold up okay over here let's make this a little bit bigger yeah like this exactly and here we go so there's a couple of things we need um when you have a look at this next js library you will see that oops at the very west the top yeah at the very top here at installation you see that you will need the next npm package and you need like a specific driver for your database so since we are going to use um postgres we are going to install this postgres package and this next package okay and the idea is that you have this query builder and then you can basically swap out like which database you use so that's that's kind of the idea and they support like different databases like cqlite oracle and even ms sql okay so let's install a couple of things so i'm going to install i'm going to install um express i'm going to install next and i'm going to install like the postgres driver and maybe we should also install like nodemon so we get like a hot reload because i don't want to restart like the server all the time that might also be a good idea right so let's say npm install safe dev nodemon okay here we go and now let's go to our code editor where is it over here yeah okay cool so that's it we should have it in here you see express next postgres and normal so first thing we can do is uh let's create like a dev script so we're just going to say uh nodemon index.js okay and like so we get like a hot reload and what else do we want we also want an index.js file oops touch okay so now we have an index.js file and i'm just going to set up like the boilerplate okay so this is pretty like straightforward so i'm just going to run quickly through it so we're just going to import express and then we're going to create an app and then we're going to use like some json parsing middleware and then we're going to say okay we're just going to listen port 8080 and we're going to say console.log server listening on port 8080 cool so that's it pretty much we also need like some routes i guess how do we do that i'm just going to make a directory called routes because i don't want to put it in this main file here right because it's just not a good practice so i'm just going to copy this and i'm going to copy this in here and i'm going to create a router router okay and then i'm going to module export exports router so right now this router does not have like any routes but we're soon going to add them and uh i'm going to import routes and then i'm going to plug this router in and by the way i already have i already have a tutorial on like how to compose like these routes and yeah you can take a look at that if you're not sure like how this setup here works okay cool um that's it pretty much i think now we can actually start with the database stuff um the first thing we need is we need to make like some directory so i'm going to make a database directory and inside of here i am going to make a file called database.js so the thing is we first need to specify like the configuration with which we are going to connect to our database and the thing is that this next.js it has something called or it typically works with something called nextfile.js so instead of this next file.js you have a you you kind of have the details on how to connect your database right hostname port password and so on and this is also what you we are going to use for migration so that's why i'm just going to create this next file and i'm going to do so by running npm next init and this is going to create like a next file but i don't want to have this next file over here because it's database related so i'm just going to put oops i'm just going to put this inside of this directory here perfect so now we have a next file and you can see that in here it basically assumes we have a development database a staging database and a production database and per default the development is cq-lite so we don't want that we want postgres so i'm just going to delete this and for develop man like this yeah so i'm just going to get rid of the original one i'm also going to remove the production thing because right now it's just a tutorial right so this is what it generates so it says okay we use postgres and we want to connect to the following database and the thing is i actually already have a database oops where is it so i got pg admin running and you can see i have a database over here called next.js tutorial and this is the one i want to connect to and like your details will be different obviously like maybe not the database name but probably the user okay so this is like my uh the user which i used to log into like my computer this is also this is also probably the one you have but of course in your case it's going to be your name okay and then i also have like password and i have just said i don't want password pool is fine so minimum two maximum 10 connections that's okay and the interesting part over here is actually demigrations so migrations are actually the most impor one of the most important features when it comes to these libraries because the thing is your schema is going to continually evolve and you need to have a way to kind of you know systematically evolve your schema and with systematically evolving i don't mean creating like a sql file and then you know uh putting like all the sequel statements in there that's that's like not really the way how you should do it because it's going to be really messy and that's why um it or like next year s supports or one of the advantages advantages of next.js is that it comes with a command line tool uh that supports migrations so if you search for migrations you can see uh okay it's you can somehow or you have this command line utility and with this command line utility you can actually like run migrations uh here's the cli yeah so you can actually run like migrations and you can say okay go one migration forward go on migration backwards and so on so it's kind of like pretty handy okay and this is what we're going to do so even for your initial setup you should always use like a migration okay but but first before we do that uh let me just do it like this so i'm going to uh create like this database so first i'm going to import next and then i'm going to input my next file so remember this next file is basically my database configuration yeah in in the age of like docker and kubernetes it's might not be like the best thing to to do it but or to to actually do it like that but let's just see okay cool um we got this next file and what we can just do is we can say const database equals next and the thing is that this next file that the cli generated it basically already contains like the configuration on how to connect to the database so all we got to do is we just got to export it and of course i just want to add like an additional comment here i want to say okay in prod you will probably use depends injection and an additional thing is that you would probably not import like this hard code this hard coded config here so in pro don't access next file dot development directly but this site with nfl's which config to use now the thing is since you're going to inject like these things so like the database name the user and the password with nfs anyway you might argue here but why do i have like these different configurations here in the first place and that's actually a very valid point so you might not really need that because for every environment you just have different environment variables so you don't need these three objects that you had before and of course you should not hard code this data here at all unless it's like development that's why we have it but in order to run these migrations we need this next file and that's why i just created it okay so this is our database and what we can now do is uh i want to uh oops why is the r here i want to go to this folder here and i want to create another directory called migrations and this is actually where we are going to set up like our first schema and the way we can or the way we can do this is we can use like this command line utility tool and we can do this by npm next or npx next right because we haven't installed it globally and we can say migrate and then make and then i just say init so i'm just going to call my first migration in it simply because yeah it's the first thing that we do and what you can do is you can specify like the directory where the migration should be stored and this is important because our next file is not at the root of the project because we want to have it in our database directory over here okay and here you go so you basically get like this file and as you can see it auto generates like the name and it's like the current date so as you can see it's 2021 it's january and it's the 24th right and then it says basically like the time okay and what you have here is you get like two functions so up basically means uh the new changes you want to do and down means basically roll back okay and like so you can basically go back and forth right so you can either say up which kind of applies to migration and down which kind of undos the migration and that is like the main idea and yeah i'm just going to make like a really simple example over here okay so i'm just going to have one table and i'm going to say next dot schema dot create table so what this does it's going to say okay i want to have a new table and this table is supposed to be called person and then inside of this table increments id so basically what we are saying is hey this table has like a column called id and it has like auto increment now typically or like probably you might want to replace like this uh integer as a primary key with like a uuid there's like a couple of reasons why you won't want to do that so long term it's like better to have uuid says keys in my opinion it has like a couple of advantages but also a few downsides obviously uh regarding indexing and i'm just going to add like a new a few fields okay and the thing is or you might be wondering what is this um and if you go like to the documentation over here you can just search for next.schema there you can see like how you can create these tables so the thing is that the goal here is to not you know use plain sql to create the tables but to actually use this uh these methods and like so you can be sure that no matter what database you use under the hood so for example cq lite or ms sql or i don't know postgres it's going to work because the databases they are like slightly different and that's why you have this uh syntax here which might also be a downside right because you're relying on this uh function thing to kind of create like the database yeah so yeah okay so i'm just going as we have like here we say okay table increments id and i think i'm just going to add like i don't know maybe like a first name and a last name and as you can also see you also have um the ability to add like non-nullable and unique so for example the id must the email must not be null and it must be unique that's just what we're going to say and here's already the first caveat when you are using databases always use underscores in your table name like don't never do something like this why because there are some databases who are like case sensitive or case insensitive and this might lead to a hell of a lot of issues and that's why the convention is to always use like underscores for everything so snake case basically for table names and for column names i would highly encourage you to to do that because it's if you don't do that you might run into like problems later down the road and it's also kind of like a convention so everyone does it like this i haven't seen like databases where um where people use like these pascal or camel case anyway another thing you can do is you can add oops you can add time stamps and this is interesting because you can say okay i want to have created ad and i want to have like an updated ad and every time you basically update create or update the record it's going to auto update the updated ad which is kind of cool you should have that in every table by the way so every table should have like a created ad and updated at a column like that's the minimum okay typically you also want to save who actually updated the uh the file or the the record okay and here basically what do we need to do to undo all of this we kind of added this uh table over here what do we need to do to undo this well we just got to drop this table right so i'm just going to say drop table person okay so that's it pretty much i would say and actually we could try this out so i just want to show you that this table here that i have in my nice pg admin panel like i don't have any let me refresh i don't have any i can't see it yeah i don't have any tables over here okay and what i just want to show you like how you would run like such a migration and the way you would run like such a migration is you would again use this uh this command line utility and what you can do is you can run np let's go over here let's go the root of project and let's run npm next and then migrate and then latest so what this is going to do is it's going to take a look at the migrations it's going to check the database which migrations have already been run and then if it's not there it's going to run it and already see we got some uh issue over here so let me just check what this is ah you have to specify yeah you have to specify the next file that's a good point i remember so instead of doing it like this you just have to say next file and then you can say database next file.js okay so hopefully this is going to run and uh yeah as you can see it just ran like one migration and the the environment per default if you don't specify anything else is development okay so that means technically speaking we should have like an additional table or additional tables over here so i'm just going to do i need to refresh this and is it then auto refreshing things yeah so the interesting thing over here is that you have three tables um you have the person table which is the one we actually created so if you go to all rows you can see that okay we don't have anything but we have like all the columns right we got created and updated that and so on and the interesting thing is there's two more tables and pretty much every migration tool does the exact same because what you have in here or this one is actually a mutex which stands for mutual exclusion so the thing is when you run this migrations uh migration you need to be sure that no one is modifying like the table and that's why or that no one is running another migration at the same time right because you might be in a kubernetes cluster you might have like multiple pods and this is like dangerous and that's why you need like this lock table so basically when you enter the command to run like this migration like next.js is going to grab the lock from the database so it's going to set this lock to one and then everyone else knows oh okay someone is already running something so i have to wait i can't access the table and inside of here of next migrations you can actually see what migrations have already been run so as you can see here okay this is like the name of the migration init like you remember that's the one we created and the migration time is basically the current date yeah so that's pretty much uh the thing with all migrations now what else do we need okay so now we understand how to run like these migrations i am just going to copy this and i'm going to yeah maybe make a script okay so that you guys have the command and yeah that's pretty good okay um the only thing we now need is what we might need is how can we actually like use this so we already have like one route so i always suggest we just add like a post request i'm just going to create like one end point here okay yeah and we need something that kind of like processes these requests so typically what you see in tutorials they do something like this right into everything in line and then import the database that is very ugly like don't do that it's it's a very big code smell you so you don't want to have your processing logic specified in line over here so what you we do instead is we're going to make a new directory and we call it controller and inside of this controller i'm going to add a person controller right so you already know the notion of a controller so controller is something that can handle http requests and i'm just going to say create person okay and as you can see this guy is going to have this express request and response object okay and typically i use dependency injection that's why i'm just going to export like an instance of this here you don't really need it so we're not going to implement dependency injection right now but i just like to create like a class typically okay so that's it pretty much and now that we have like this controller what should we do so a lot of people now say oh but now i can actually access the database right database dot do something like no this guy is not supposed to know like the data layer okay for this you have an additional layer which is called the service layer and the service layer is the one which is actually like which actually has the logic and which actually knows like what to do this guy is just responsible for getting the request delegating it to the service and then if there's any form of error it communicates it back so that's why you um have like a service directory as well and you also have a day dow also data access object directory as well and i'm just going to create these three files and the data access object is or this dell layer is basically there to abstract away like the database access right because our service doesn't care like what underlying database we use it just wants to you know delegate it to this uh data access object and in case we change something later on so we switch the database typically that never happens but if we were to switch the database then we only have to like modify this layer here okay so the same thing i'm just going to create a [Music] person service and again this might might be a little bit repetitive it might also look a little bit overkill for all of this and it kind of is but i just want to show you how you would organize that because normally you have more than one like endpoint where you access the database and if you have a structure like this it's pretty easy to manage so i'm just going to say okay great person and maybe i want to have i don't know first name last name email okay and then inside of here i'm going to yeah let's maybe do the data access object first so i'm going to say const database require and this is actually where we're going to use our database right so oops um database and uh is it database yeah and then slash db right yeah this is the one and then i have um so this class is basically supposed to abstract away um everything so the creation of the person right because we don't care like on the service and on the controller level what the table name for first name last name and email uh is we just don't care about it we just tell this data access object hey man uh like i want you to create like this um this object for me database and i don't care how you do it so it's like up to you okay and it's actually pretty simple so there's uh you can just say database the person dot insert and by the way this is all like this next js syntax so i'm not making this up so if you go through the documentation and that one is actually pretty good i think it's maybe more at the top right so let me scroll somewhere here and then insert yeah you can see here right next table insert and then you can just pass the javascript object and then you kind of get something back this is basically what we use right in our case it's just called a database because our module like next or because we already have the next module we just called it like database okay so back to our little topic so i'm going to insert like this and i'm going to say okay email and i'm going to say first name first name and last name last name and notice that we are using the underscores here so i think there's also libraries where you can convert that stuff to snake case might make sense if your thing is getting bigger like for now i'm just going to leave it like this and this returning id means i want to get back like the the primary key so the number for the person and the thing is this is actually a promise so yeah i want to get like the id i need to destructure it because you might return like multiple things so that's why you kind of get the primary key first and then you can return like the id and now we are basic now we basically have uh something like this i'm just going to do like in a weight and uh yeah now i can make this async right yeah and note that we don't have like a try catch here so if something throws it's going to throw up to the service layer um yeah so let's go to the service layer and in here like everything we need to do is we just need to import this person data access object right so i'm going to say data access object person i'm just going to um to do something like um dot create person first name last name email yeah and here it might already make sense to think about okay what are we actually going to do uh maybe maybe let's um let's do it like this okay so we just expect that we get like one object so this is going to be our request body and uh yeah this is going to be the person detail so something like this and this is also going to throw right so technically speaking if something goes wrong here we could have like an exception and this exception somehow needs to be handled so you can either handle it like on the controller level or maybe you have like some additional logic in the the service level for now i'm just going to handle it in the controller level obviously if your controller gets bigger or there's like additional logic in the service you might you might want to you might want to handle it over here okay and there's one more thing i think i forgot it i need to return this because this is actually a promise right if you look at this guy or this person data access object um then you can see that okay it's like async and uh yeah okay here's like a promise it awaits it and then it returns it okay so technically it could create like an arrow over here yeah i think it's fine i think it's fine let's just do it like this and i'm just going to use a try catch so eventually eventually i need the person service first eventually you are going to need to have a try catch right because every time you have async await you know that it could be that it doesn't work so that means we somehow need to handle this okay and here we get the id and what i'm just going to do is i'm going to return it 201 and then i return the id as a response now oftentimes or according to http or to rest typically you also have like a header special header that you could return where you put the id which is like the location i'm not going to do this right now because it's yeah not many some people do it some people don't um that's just how it is yeah so as you can see we're just awaiting like the creation of the person we're passing the request body in our service layer we're destructing these three things and then we are pushing it to our data access object and by the way at the moment we don't have any validation so that means if any of these things is missing then obviously all of this is not going to work and by the way i have like another tutorial on this so it's called uh request body validation and maybe check that one out so this is also something that you should do or was like one of the first things that you should put inside of your project to validate what you get from the people okay so i'm just going to say personalcontroller.createperson i think that should be it um yeah we plugged everything in maybe we can try to run it let's see if it starts up maybe we got some typo or something no doesn't look like it this is good so i think we can probably give this thing a shot right um let's see i'm just going to open postman and i got like a collection over here so as you can see oh wow it's not there so i have to do it manually i'm going to say localhost 8080 person right post request and inside of the body i want to have a json and i'm going to say first name yeah john last name smith and email is oops yeah and by the way what we also haven't done here is we haven't put in like error handling um so that means what happens for example if the email is already taken or something like that okay i think there's some kind of sort of issue okay let's see what did we forget persondel.createperson is not a function okay this oh we forgot to export it and the person tao yeah that's the thing about like node you know it's very easy to make mistakes so here let's try again ah nice bam so as you can see apparently this thing like it got the id one and if you look at the database so let's go over here and let's check like this person and you can just right click here and this pga admin by the way so it's a very nice and handy tool and uh i think you can go to all rows yeah there you have it okay wait how can i do this i think you're not really seeing it do you i know you see okay so here you see you got the id you got the email you got the first name and you got to create it at an updated timestamp so this is working this is basically how you connect to a database there's a lot of things that we still need to do as i said uh what about request body validation what about constraint validation right because this thing is going to throw if i attempt to insert wait actually i think we haven't handled this right so yeah we're just sending like the arrow and i think we would need to something went wrong okay and by the way guys in prod don't use console error because it's not async yeah so in that case you know if we were to send this request again yeah then we would get like a something went wrong arrow and you can see in the in the logs over here that basically you got this constraint validation so this is all stuff that you actually need to handle in your in your service layer actually that's what you have the service life for so you attempt to create the person and if it fails for whatever reason then your service layer needs to figure out like why and then return like the proper error message that would be like a good behavior and that's also why you need this service layer so i'm not going to implement this right now because otherwise it's going to take forever i think you now have like a solid grasp on on how to connect to a database as i said next.js kind of nice tool uh use the migrations always use lowercase letters for the tables and for the column names and make sure to go to have like proper error handling yeah so yeah that's it pretty much uh thank you so much for watching give the video a like if you enjoyed it uh also if you have a comment please post it down below and you can also reach out to me on twitter my twitter handle is at productioncoder so thank you so much for watching and i'll see you next video bye

Channel: productioncoder

Views: 11,664

Rating: 4.8567166 out of 5

Keywords: knex js, knex js postgresql, node knex tutorial, node knex migrations, node knex postgres, knex node js tutorial, postgres knex tutorial, knex express example, knex express tutorial, node express knex, knex postgresql node js, knex javascript tutorial, knex javascript, node postgres tutorial, postgres knex, node database tutorial, node database access, knex with postgres, node js knex, knex migrations, knex migrate latest, knex migrate rollback

Id: wfrn21E2NaU

Length: 36min 11sec (2171 seconds)

Published: Mon Feb 01 2021