Node Objection.js tutorial with knex and Postgres | Node SQL ORM

now what is going on everyone today we are going to take a look at objection js which is a sequel friendly or m4 node and yeah we are going to explore this library and i can tell you it's pretty cool so objection.js itself is built on top of next.js which is a sql query builder so a pretty popular project actually and in this tutorial we're going to talk about how to do migrations we're going to talk about how to have seed data so example data in your database and we're going to talk about how to build like a rest api with express that accesses your database with postgres so we're going to do all of that and before we get started i just want to show you what we will build so since we are on youtube i thought it makes sense to make an example that relates to youtube so let's say we have users and each user can have like maximum one channel so either you have zero channels or you have one channel okay i think this is also how it is on youtube you cannot have multiple channels you need like multiple users for this okay so even if someone has two channels he created like two users okay so this is like how the setup is and each channel can contain an arbitrary amount of videos so in that case i just drew four of them here so to sum this up um we're going to have three tables a user table a channel table and a video table and a user can have or can have like an association or with channel so you can have either zero channels or one channel and every channel belongs to exactly one user so this is like the other way around okay and with channels um you one channel can have an arbitrary amount of videos that's why i wrote here n and uh every video belongs to exactly one channel yeah so if you know like entity relationship uh like models these or the annotations i did are kind of done with this in mind okay so i just wanted to show you what we want to build out so it's a little bit easier to understand as we are actually doing it cool so i would say let's just go to your directory where you want to build or where you want to create this project i'm just going to make a directory and i'm going to call it objection tutorial and then i'm going to change inside of this directory and i'm going to run npm init yes which is going to create a package.json file and maybe we can already install a couple of things so if you click on this get started button you immediately uh it brings into the installation page and you can see that you will need objection like the library itself then next because like this thing because objection is using this under the hood and a database driver which is in our case postgres um yeah so these are the things that we will need and we will also need uh express probably so i'm just going to install express i'm going to install objection i'm going to install next and i'm going to install the pulse postgres driver oops do something wrong here oh i forgot the npm install okay i like this cool and while this installs i can show you or you should have postgres up and running locally so i have it running um at the moment here on my machine so i created this database here objection tutorial and by the way if you're wondering what this thing is it's pg admin it's like a ui for managing your postgres database you can also use the command line with pcq psql for it but i thought it's just a little bit easier if i use like the ui so if you want to create a new database you can just right click here and then click on create and then database and you can give it whatever name you want so in my case i picked objection tutorial and since it already exists i'm not going to create it again and if i scroll down here to schemas and public and then to tables you can see that i don't have any tables in here so this database is completely empty and yeah that is good and by the way i will include the code or a link to the repo in the description down below and in the readme you will also find instructions on how to set all of this up okay yeah so let's go back to our uh little installation over here okay this is done then what else do we need we need um but this time it's the dev dependency not one simply because i don't want to start and stop the server all the time so this package is so incredibly useful um yeah okay so we will install nodemon and finally we can open this thing up inside of our editor so i'm just going to say code and while this thing opens i can actually already create the index.js file okay so here it is nice so empty index.js file and in all packets json we have everything we need express next objection and our postgres drive okay that's good and the first thing i will do is i'm going to make a dev script and i'm going to say node 1 index js so this is going to be our hot reload kind of thing so this is just good because it's much easier for development cool so let's start with setting up our database shall we and to do this i typically or let's create a folder for it right okay i'm going to create a folder i'm going to call it db for database and inside of this folder i'm going to create two more folders i'm going to create migrations and seeds yeah just that we have it okay here so here you can see it and if you have seen my previous tutorial about next.js then maybe you remember that this query builder here it pulls its cons configuration from something called a next file which is basically just a file that exports a object that contains the configuration for your database and this is what we want to create uh next i would say okay so maybe let's get out of this and let's create this next file and by the way if you're wondering how to do this um just search in the documentation for next file and here they have like a pretty nice navigation so you can click here and here it like you can see what the next file is and how you actually uh how you actually create it so this is quite cool and the way you can create one is you can just say npx like npx not npm nex init the reason is i don't have nex installed globally if you do then npm will also work but since i don't have it installed globally i always need to run npx okay so i'm just going to hit enter and we end up like with this next file js and there we have it so as you can see it creates configurations for development staging and for production now these days you don't really need like these separate uh configs in here because you pull everything in with environment variables or kubernetes anyway but yeah so i'm just going to delete the cq light um local file because i don't want it it doesn't make sense right why would i why would i try to [Music] develop like with cq lite and then in production run on postgres that doesn't make sense like i want to run in production on the exact same database that i ran locally otherwise i'm not confident that it works right tests like to fly and fly like your test kind of thing cool so i already have this database so in case your database is or you create a different database then you will need the appropriate name and this is the username for postgres and then you also need the password so by default the password is just empty password that's why we can just pass null and the user is your system user uh at least that's what happens if you install it locally and if you don't know what your system username is you can just run who am i and then you can see here okay this is like my system user and this is what i put here so that will do overall and yeah that's it pretty much i would say uh what else i want to do something else so let's include like the case mappers first okay so the thing is that in there's various approaches on how database tables can be named and some databases like their case sensitives others are case insensitive and that always creates problems so in general i would recommend to always use underscores or like snake case for your database names and um objection has like a pretty nice um yeah extension pseudo or configuration snake case mappers that you can include inside of your configuration so that means say you have um i don't know in javascript you try to insert a field with this like this first name and what this mapper is going to do it's going to convert it to first underscore name and that's like a best practice so for databases you always want to have this underscore but i don't want to you know i don't want to write this all the time so i don't want to write first name colon first name no this is just super annoying so that's why these mappers are like so handy because you can just import this next snake case mapper and then all you got to do is you just got to destructure it over here yeah so this is like a setting that i would always recommend so make sure that your databases columns uh you or yeah the database also the name and everything uses like snare case so everything was uh with underscore you can also see it here i used it in the database as well okay so that's it pretty much we already have our migration specified and the only other thing i want to specify is the seeds so a seed is the initial data or directory where we store files that create initial data and this is nice for local development because as your project grows you don't want to manually insert like that stuff all the time or you know maybe you mess around with a database and then you need to create and drop it again and then you just want to take some example data and push it inside of the database and this is what this seeds setting here is for and that's also why we created this folder here because this is where our seed data is going to go into okay so but before we do the seed data um let's talk about how to actually uh yeah how to actually migrate or how to actually create the database schema that we want so in order to get or to create our database schema we need to create our first migration so there's some people who just directly execute create table statements like on the database but i would really not recommend this because your database schema is going to evolve all the time and you need like a tool to properly manage this okay so we have this tool with next.js and you can find it if you search for migrations and then here in the sidebar you will find it you can just click over here and here you can see that you basically only need um where is it yeah this one here next migrate make and then you give it a name okay so let's just do that so i'm in the root of the project i'm just going to say uh npx because we don't have next installed next and then migrate make and i'm just going to call it init okay cool so here it is and you also see that since we are at the root of the project it just like created it inside of this migrations directory so we don't want this so i'm just going to take this file and grab it over here and then i'm going to delete this folder cool so now we have our initial migration and what we now need is we need to create our database and the way this works is we have two methods up and down up is to apply the changes and down is to roll back all the changes and what i'm just going to do is i'm going to say next.schema dot create table and the first table i want to create is the channel table and basically you give it a name so here's channel and then you can give it a function over here and now you can specify the columns so i'm going to say table.increments and what this does is it's going to add an id column as an integer and it's going to make it auto-increment now maybe you actually want to use your ids but like for this tutorial and it's just much easier to see if it's integers so that's why i'm just going to pick integers on production or in general you probably want to have uuid simply because you can generate the uuid before you insert it inside of the database this is important if you have like a micro service based system with like some eventbus kafka or something like that okay and so i'm just going to add a name here and i'm going to add timestamps so this is going to to add a created ad and update it at and basically what this does is it creates this channel table and after we create this channel table we create another table which is the user table and it goes like this yeah okay and inside of our user table we do the same we just say table dot increments to get like um id as an integer which with auto increment and i'm going to like put a couple of properties here so for example i'm going to give it a name and i'm going to say it must not be null i'm going to give it a yeah i'm going to give it a email maybe okay i'm going to give it an email and i'm also going to make this not nullable and i'm also going to make this unique okay and the next thing is i'm going to add the timestamps true true for created add and updated ad and now this is going to be interesting we're going to add our first foreign key and we're just going to say okay there's like an integer and i'm going to call this channel id and this id oops is going to reference the id column in the table channel right so basically this means in the user table we have a column and this column points to a channel or maybe not because it's notable right because remember you can either have exactly one channel or you can have zero channels so that's why we just leave it as nullable okay so uh i think that should do it and what else do we need i think the only thing we need is we need uh the video table right so same thing video and i'm going to say table i'm going to add a function here and yeah let's see let's go do it like this and i'm going to say here we can copy this i want to copy this table increments i want to have that i want to have the timestamps okay and what else i want to have maybe a title i think title and not nullable because every video needs to like every video needs to have a title after all and i'm going to add the next foreign key so i'm going to say okay this is the channel id and the channel id is also not nullable because every video must belong to one channel and it references the id column in table channel yeah so that's it i would say yeah okay so now we have our three tables and yeah we added a couple of properties but they don't matter so much what's probably more interesting is like how you do these foreign key relationships uh yeah and now if we need to roll this back what do we do well it's pretty easy we can actually just say uh return next dot schema dot drop table if exists table if exists video and actually you know what i think i'm i'm just going to copy this so do this i'm going to do it like this and then like this and i don't want to have this semi-colon here obviously and the next thing i'm going to delete is the user and then i'm going to delete the channel okay so that's it why is this yelling here yeah i think that should be it cool so in i think it looks okay we can actually try this out right now so yeah maybe let's make another script okay let's make a migrate script migrate because i don't want to type this out all the time and what we can do is we can just go over here and i think it's yeah here it's next migrate uh latest so this command is going to like run the run all migrations and as you can see like we got this timestamp in the file so it knows basically which migrations to do okay and yeah let's just do this and let's go back and i'm just going to say npm next migrate latest so the same thing as we had before and the only thing that we need to specify is we need to specify this uh next file right because our next file is in db and then in next file oh it actually isn't why is it not there that explains a lot i forgot to move it sorry i thought i already moved it so you want to have this next file inside of your database oh yeah like this yeah i didn't see it because of folder structure okay so the next file is now inside of the database that is fine um yeah i think that's okay let's try to run this yeah let's see what happens so we are in here and we're just going to say npm run migrate oops and you can see oh there's already a an error and it says oh it failed it's not a function okay let's go to the migration over here let's see what's wrong here ah i found it sorry here is like uh this is missing okay so let's clear this and let's try again let's see oops and we get another error i say table.email so we got oh string yeah like this sometimes i make typos when i talk too much okay so hopefully this is the last one you know because it's hard to talk and write the code okay what else is there there's like table integer dot references i think it's references like this yeah that should be okay yeah that migration stuff can be annoying you know it can take a while until like your migration actually runs yeah here it is okay so now we got our migration and that means technically speaking we should have a couple of tables over here so i'm just going to go in here and i'm going to say refresh yeah and i can already see we have tables and the interesting thing is if you look at the uh migrations you can see ah okay it stores what migrations have been run so you can see here um yeah there's like a first migration it was like with this name and then it was like when it was run okay and you can see all you can see as well down here that we actually have the three tables that we just created so like this yeah so you can see id name email channel id created at and updated that yeah so here if i scroll then you see the updated ad as well it's just because my view is zoomed in okay cool so that means our stuff is working nice and then the only thing i would say we still need is we need to create some seed data right because the moment is a little bit empty in our little database so uh let's let's create like a seed and the interesting thing is that this seed or the next.js they also have here this seed file section there you can see it's pretty simple so it's pretty consistent naming you just say next seed make you create you give like the name and then it's going to create like a seed file and by the way i think um let's go to our next file yeah we already did it over here right maybe the same thing as before uh let's let's make another uh make seed i'm just this also for your reference by the way because in case you're not sure okay how again how again do we do this you can just take that copy it over here add a comma and then yeah i don't know uh what kind of name do i give i'm just going to give it def because on develop like you probably want to have some initial data when you run it and again the other thing we need to specify is the next file so next file and i'm going to say database next file.js and that is because we assume that we run this script at the root of the project right it's just a little bit more user-friendly so let's try this let's say npm run make seat yeah okay there we have it and you can see bam we got like a new seat table and this is good so yeah i'm just going to add like a couple of things in here i think i'm probably going to make this a little bit faster i don't know because otherwise it's boring the only other thing that we need to do or what i want to show you here is before we do anything we need to truncate all existing tables because otherwise we try to insert it twice the way you can do that or the way you have to do this in postgres is you need to run a raw statement and the reason for this is because there are foreign keys in the in our table so you you can't just say truncate this table because otherwise like the database is going to tell you hey but you know what there is there are foreign key constraints here so i can't just remove this yeah so that's why you want to use this cascade feature over here and that's why you need this raw that's more like postgres specific i think it could be different in other databases okay so this basically says yeah just delete everything and yeah the next thing we can do is let's add a few channels um let's do it from scratch here okay i will say next channel and then i'm going to say insert and i'm going to insert an array and in here you can specify the objects you want to insert so i'm going to say id 1 name is channel 1. okay save this copy this and a comma here okay so now we have two channels let's also add this maybe over here this over here so it looks a little bit nicer okay so that should be it and uh yeah what else we need we need some user data i think so these are our channels now that we have our channels we can add our users does this look right here i'm not quite sure this is close the array this closes this yeah it should be okay let's see maybe later we see that there's an error okay and then for user i'm just going to do the same so i'm going to add maybe three users oh and the id should be two here right so three users this guy this guy and this guy and i'm going to call user 1 user 2 user 3 and i think email we still need to put email so user 1 at test.com take this thing copy it so i'm just going to insert like a few records over here and like oh no it was user three right okay and let's say the first two have a channel so we're going to say channel id 1 i'm going to add a comma here and this is going to be channel id 2. and let's say the second guy like he does not have a channel okay so i think that makes sense cool and then i'm just wondering all the time why this thing is not auto formatting it i think there's some issue like but we will see it and yeah the only other thing we need is we need uh now we can return because it's the last thing we're going to do so we're going to insert like some videos and yeah it's first one title video one by user one channel id one so i'm just going to make this like super dummy data so it's easy to see what we have okay so let's say the first guy uploaded two videos and the second guy uploaded one video and this is using two and this is video two that's an all missing here yeah i think that should be it yeah okay uh let's try this out i think there is some uh there's some kind of problem here but because it's not doing the auto format but we will see it soon okay and i'm going to make another script just also to document all of this and the way you can do that you can run like these seed files you just say next seed run so pretty simple let's go over here and i just say npx next seed run and again i have to specify the next file and i'm just going to copy paste this okay so let's see and i'm going to run npm run seed yeah oh yeah i need to make this async like this ah now the auto format is wrong that's why it had problems okay just didn't see it okay um try to run this yeah yes nice so it ran uh let's go to our database and let's see how we are doing over here so i want to see all rows and i don't know can you guys actually see this yeah but here you can see okay we got two channels and we also got a couple of videos anyway i mean you can see we now have like the data running this is good and yeah that's it pretty much for this things yeah maybe one more thing what we could do is we could add another script and i'm just going to say npx next migrate down and the reason i'm doing that is sometimes if you mess something up with your database you just want to undo the current migration and that's why this command here is so useful because you can say down which says undo the last migration which effectively executes this which drops all your tables at least for this migration okay so that means you can easily clear like your database yeah so now we got this ready we got the migration we got the seed data the only thing we need to do is we need to create our model classes and in case you're wondering how that works you can check out the section here of a model so basically the idea is you create you create a class and this class is going to [Music] like contains like a couple of properties and then you can directly interact with this class and you don't need to go like so low level that's the advantage that this thing provides okay so let's go over here and i want to create a new directory and i'm going to call it models and inside of this directory i'm going to create three new files for our three models so that is the channel then we have a user and then we also have video oops well not like this what is it doing over here video js yeah and the idea here is that you go in here and you import like this model class from objection and then you create a class and then you extend from this model and then you give it like the table name and there's a couple of other things you can do so i'm going to show that in a second but i'm just going to copy paste like all of this and here it's going to it's going to be the user uh why is it not because i don't have an export okay so i need to add this as well exports equals user okay so let's create this copy paste this [Music] channel channel and for video i'm going to paste this so as you can see this is like that database stuff it's not so easy like you can you need to set up quite a lot of things and uh yeah so now you got this up and running and the only thing you now need to get like your models up to speed is to actually wire up your uh your next instance with your database and that is why or that is pretty simple actually we just need a another file here and i'm just going to call this database setup js and the idea is that you basically say okay i have this next and i import like my next instance and i also have a next file next file and i also need this model because the thing is how should this library know like how to access the database and this is basically what we need to set up here so that's why i'm just going to make a real quick function and this is also in the the documentation over here right so i think it's in getting started or in the minimal example yeah so here you can see okay give next instance to objection this is basically what we need to do cool um so here and i'm just going to say const database equals next and i'm going to pass the configuration for next file.development and i'm going to say model.next database so this means that like you you globally install like this database inside of all models and now objection.js knows how to actually access your database yeah let's maybe say con setupdb will require dave setup setup all right it's db setup right like this and then i should also name it like this right then i can say database setup i'm just going to create an express server even though this video is already quite long but you know so if you make it until this point like nice one you really want to figure out like how it works right okay so here's like express to json and then app.listen port 8080 and then console log server running on port i'm just going to create it in here okay simply because otherwise it takes too long but i'm going to add a comment in prod uh put this in separate files so you know that you need like a controller and a service right i've already shown this a couple of times but the thing is it just takes too long like now so i'm just going to do it like this and i'm going to expect this and i'm going to say id equals request dot params. yeah actually we can destructure it const id and by the way this is um like we're not doing any request body validation or anything inside of this so yeah this is maybe also something we should take into account later on okay and basically the way we can finally access our database is we can just uh load this model so i'm going to load the user model and i'm going to do like users no it's user right user yeah and then you can just say user equals await user.query and then dot find by id id yeah and then you can say i'm just going to return this user and this needs to be async okay and in case there's like some sort of error like what do we do then i don't know i'm just going to add a try catch jason error okay so i'm just going to like put the error message in the response and that's it let's run this so i think it was nvm run dev right uh okay there is some models and then user ah because it's this one right should be let's remove this yeah so now it's starting and technically speaking it should work now so if you now go to a localhost 8080 to user and to two yeah so as you can see we now get the user back now you do know right that we actually need to build a service layer and that error handling and request body validation these kind of things but i'm not going to do this this is just for the tutorial uh in prod put this in to avoid cold smells right this is a cold smell but it just takes too long otherwise cool so the last thing i want to show you is how to actually load the user with the channel object and the thing is right you remember we have these two tables user and channel and we can we have like a join column which was like this channel id in the user object and you can actually specify um a method in here like in this model class that allows you to eagerly fetch other objects so that means not only do you fetch the content of this table but you can also fetch the content of like another table together with it this is what i want to show you so what you need to do is you need to [Music] go where is it here and here you can see okay there's something called relation mappings like a method and this is what we are going to need so i'm just going to copy this and this is like what we will need and the way this works is and you can just copy like from the documentation you can just copy this so we're not doing paths here we're doing like channels and the idea here is that we say okay every user has zero or one channel so it's not has many relation but it has it is many too many i think it has has one relation right it's somewhere here in this dock yeah this one has one relation so that makes sense right and let's go back to our code and let's just add this here and the relation class is going to be channel and now you might be wondering why are are we importing or why do we have like a require statements inside of like a method and the reason is we don't want we want to avoid circular input circular import paths because then we have a problem right then the system doesn't know what to do and the only thing or like one of the ways on how you can do this you just put it inside of the method and then it's evaluated in the lazy way and yeah so it says has one relation and now let's think okay it was person no it was user and it was channel id right remember we had this column channel id and then we want to join on channel dot id yeah that's it and i think then the only thing we need to do is we need to go to our method and i already have it here because there was some error but basically the idea is there's a method it's called with graph fetched and then you can say okay in addition to this user i want to load this property as well and this is called eagle loading you should not always do this by the way because it's expensive right you always need an additional join but you can see here that you can not only like load the table itself but also like this channel property here right so here you see like the channel information yeah so this is pretty nice and this is something that objection.js allows you to do so i can only recommend this library i would probably prefer it over using next.js because next.js is just like bare metal so this one is just better like it builds on next.js nexus is solid it has migrations it has seed data that is all cool um but this one like is really nice because it allows you to have these model classes and you just have to specify uh the relations and then you can just uh load things so that's pretty cool yeah so uh thank you so much for watching i think i'm going to move that stuff into like a service and data access object layer but i'm not going to show this because i think this would be boring so thank you so much and leave a like and subscribe to the channel and if you have any question just write me a comment or uh just send me tweet my twitter handle is at productioncoder and yeah i hope you liked the video and see you in the next one bye

Channel: Jan Goebel

Views: 19,451

Keywords: objection node js, objection orm node js, knex objection js, objection tutorial, objection js tutorial, node objection, node objection knex, objection javascript, objection js knex, node objection postgres, objection js, node orm objection, ojection js, objection js promise, objection js array, objection js examples, node objection examples, objection js download, objection node express, objection js date, objection node app, objection orm example

Id: zbIl2kuP7tE

Length: 44min 23sec (2663 seconds)

Published: Mon Feb 08 2021