Mastering Database Queries in NestJS with Knex Query Builder: A Complete Tutorial #19

[Music] foreign [Music] and welcome back welcome to my another video and in this video we are going to talk about next query Builder so as you as you already know I'm talking about Nest shares with the different orms in the module 2 of this next year's Advanced series so in this playlist we are talking about module 2 which is all about how we are doing Integrations with different orms to work with the different database like I have a nest.js wants to connect wants to use this next query Builder and wants to use MySQL postgres or different SQL database or I have an sjs application where I want to use I want to use sqlise and I wanted to connect to the MySQL uh postgres or any other SQL database or I do have mongodb how do I work with an SGS so all those Concepts we are covering in end-to-end applications let's try to First explore the next is really nice query Builder and I have used next with the express app I mean it's very lightweight you don't need to do a lot to set up and even when you are doing a different operations joints and all the next provide a really nice query Builder so I will just try to go through it and we'll then we will just talk about nest.js within X so this is an X next is actually a query Builder not a full-blown orm what I mean to say is like when you talk about type RM SQL ice Prisma they are pure orm based libraries but it's more about uh query Builder and you can work with the postgres sqlite MySQL my sequel to Oracle DB and there are cockroach DB and some other database also so you can run this so this is how you will initialize the client because like you have Prisma client you have a type orm uh repository similarly there is a next client with the help of that you will be able to access these tables so next also provides the migrations I mean you can write a simple migration files and you can populate those tables into the actual uh database instance but this is how you are connecting the client name may be a postgres and the connection will be the details you can have just one single URL like the URL so this is the connection string right so you don't need uh the big all these variables because every database also provides a connection URL which contains the host Port username password and the database name so you can pass that connection string also so this is how you will actually initialize your database so if you want to use a sqlite then that's a simplest way because sqlite is a database file based database and this is how you just specify the path client is equalized and if you want to just use in memory then you can just use a column memory okay and how you are going to Define the migrations and all that's very simple uh we will talk about it so this is how you are initializing the connections so this is for postgres right and for Aurora DB next Aurora DB which is AWS Arora that is also there so we are just specifying the resource crnl security error and that is simply will work like this now the important part is let's say I have already the database connections right now I'm more interested in knowing what uh this next is providing me right like how we are doing a transactions how I'm doing a simple queries and all that's important and it's very simplified I mean you can just you can just see the syntax it's uh next dot select these many columns from books so it's like really nice query Builder it is selecting all the columns from the books selecting the ID from the user so selecting so this is how I mean you can also use it with the typescript you just need to Define the types interface I mean you just create interface classes interface for your database entities and then you can specify it like this okay so I mean you can write a complex queries also not just a simple one like these are the columns you are selecting from the books these are the columns so what we are doing here is give me all the columns from the users but then there are other queries also which you can write I will talk about that so this this is really a nice query Builder which contains lots of things like there is simple where Clause what it is doing is from the users table uh give me the data with only ID where first name is this and last name is this right so it's like a simple query give me the data from the users where ID equal to this or well not in so it's all about how uh we are writing a queries so these are the where Clause that has been added with and so if I look into this left column I can also see the joints right that is important because we uh do need these joins sometimes right so I'm just doing a join with the user with the contacts and this is the condition that user.id should be able to should be equal to the contact dot user ID right and select the user.id and contact.phone so I mean I will not uh dive much into the documentation I mean migrations are also important and how we do the migrations so it also uses the the migration CLI okay so if I talk about you need to have a first environment configurations and then you just Define your migrations which is somewhat like this up and down it contains two sections schema dot creatable so in the up sections you will just do the create operations like I need to create a table I need to create a alter a table column and in the down sections you will just drop sentence drop the column or drop the table right and this is how you will manage the transactions you will just write these files and when you apply these migrations the database changes will be applied to the database okay I mean if you want to talk about transactions this also we can do so transactions means you can you will initialize the transactions and then based on the transactions you will start doing the Performing the operations like this is a transaction transitions.com transactions.org so in in between that whatever the operations you are doing that will be considered a part of transaction okay let's see this in the in the in the respective to Nest GS okay now let's take a look into the code and try to understand this so this is our next GS project okay I just I wanted to talk about a simple example uh not very complex stuff so here I do have an X5 like orm config.ts you have similarly there is a a simple file next file and next file contains the configurations for different environment like for testing you can use this for the local you can use this I mean the configuration is kind of similar we are using client postgres connection URL migration directory and C directory but this is how you will write next file and from this you can get this object based on your environment variable so what I will do is I will set this environment variables node EnV local and also I can set this database URL in the export so that I can access that using process.env okay and then I can just simply do is npm run migration make so in the migration there are different steps you need to create a empty migrations then you need to apply the migration all those things so here I have a script that is somewhat uh simple node modules next we are using and here we are running migration migrate make migrate latest migrate rollback seed make seed run right so if I talk about DB migrate make means I want to write a new migrations can you create a new migration for me so this is how I will just execute a script and it will create this migration file now you need to fill the this migration file with your data and you can see the migration file definition is inside source migration and this is a new migration and here you can specify do you want to create a new table let's say I want to create a new table so I can just 2 is something like this and then I can use this same thing drop table because in the down statement if any anything fails then I will drop this table so let's say I'm just creating this new table with these set of columns so if you can see table dot string table dot tags table dot Boolean all these things are available so in the table API I think because table we are not using as a next table it should be so we we need to take care of the types which we are using here so that we can get all the things stable dot dates all these types are there you can see table dot Bool Boolean column I can introduce okay let's say I want to have a Boolean table is deleted dot not nullable because this is the only non-nullable and and here you can say default 2 is deleted false right so now this is the migration I have created now I want to apply the migration so I can just choose another command which is DB migrate run so that will be executed against database and you can see batch one three migrations has been applied right so it's like a simple steps you create a tables you write a migrations and then you apply the migrations through the pipeline or here we are playing to the terminal so this is how we are applying the migrations and you can also apply the seeds let's say I have the seed which is updating putting things inside a user's table right so I can run the seeds go to packages and you can also do seed make and Seed run TB what it will do it will just look into this users.ts and it runs the seed file so it has populated the data inside your user table that's all about like how we are creating tables how we are creating a empty migrations and how we are running the migrations now how actually we will do the integration so you already remember that in the next CS you need to have a database module to access the database and here we do have database URL and I'm using postgres here so if you see this this is my environment variable local is the environment and this is my uh you can say database that I am using here so for that what I can use is I will just update my DB module so this is like a same folder structure and same code I haven't changed anything here I'm just using this DB module so the only thing which I'm changing here is this so type is postgres database URL key connection alive here I'm just changing how I am getting the database configurations so we already have this next file I will import it and I will pass the environment variable like process.node.env because next file is providing the configurations for different environment environments like for testing for local for production so we just need to pass this object this object to our next client so if you see DB module is all about passing these config options inside config and then finally what we are doing is because here also we are using this uh module next module with the nest CS next next GS so this is actually a module you can use if you want to if you want to play around with this next this is an npm module uh which is there and you can just do a simple testing for with this so next next yes and how we are initializing the connection is so that it is providing both the methods for root and for router sync so here inside a config you are providing the client connections and all connection is memory based and here full router sync that means we are dependent on the some config service populating all these values and this is how you will be accessing the database tables right uh here like this dot next dot table user dot insert update delete so we are using this version of it next module Dot 4 root async and if you see in my code we are doing the same thing next module.4 root async and we are getting things from the config module means the dynamic module options and this is the get connection options and this is returning with this object this is what I need what is the connection name what is the config object config object is this thing which contains the next file configuration right so if you see config options are based on the normal variable it will give you the whole object that becomes your config which contains your connection URL and migrations and see directories and all which you can pass so now this whole object becomes the next module connection options which we are passing here and that's it it is giving us whatever we need so this is the database module once you initialize it you should start consuming you can inject the next service inside your components inside your controllers and you can start accessing these data tables so after that rest part is easy because now because we are using a next module so if you look into the the main module here we are using domain module inside domain module we are adding okay dbmodule.4 root I mean we I I have designed my own custom database module where I'm calling for root and inside if you see the four root 4 root is nothing but a method which is initializing this next module with the factory pattern I mean it we are injecting config module and config service from config service we are getting uh the environment variables and database URLs and all that's it so here now if I wanted to access the data so controller is simple I didn't do any rocket science it's just like a create user update user delete user some credit operation just for the Showcase and here I'm doing create so what create will do create needs to create a user and it needs to have an access to the database so this inject model like in the type RM we have a repository pattern and all we do repositories and get the repository instance and then this dot user report dot save and all we were doing similarly here we have inject model which will give us access to the next and from this next we can get the instance of any table user table user test table uh any table whatever the tables you have in the system so I mean that's nice you don't need to create a separate repositories for each and every table it's like you once you get the next it's a global object and you can get the access to any tables existing the system and I got the user so these are just a simple query operations like insert find here I'm doing a table dot where close give me this Theta I mean where this equal to this and update this parameters remove is simple this dot next dot table where ID equal to this dot delete so I mean I'm not talking about the query Builder and all these operations but it's just like a simple crud operation delete fine I mean get delete update insert update delete so this is how we are creating dot insert this is how we are getting it taught update based on where close and this is how we are deleting it so next is overall simplified ice I will say we don't need to deal with uh complex stuff you just need to be nice at the query Builder and you can do join you can do anything uh with the next rest I mean how to use next queries because once you get the access to this this dot next dot table you can write anything after it based on whatever the query you have you have created uh from the query Builder okay so this is all about I can just try to run this application and show you how it really works so here I'm running my project there are some small build errors which I fixed and then I'm able to run this application and actually you need to look into the code like on which endpoint it is exposing the documents so here I can see this is the base path API V1 swaggermodule dot setup serger module is set up on this API V1 so I go to the API V1 and this is what it is next year's template and here I can just check the health looks good and here I can just try to fetch all the list of users which I did seed because we already seeded this data and this is the users now I don't know like what is in the request body I need to check in the payload like what we are passing I can just copy and paste these parameters and see if it works I can remove the ID because that is auto generated internal server errors we always do something wrong okay so there is something unique unique duplicate while it constraint user primary key so I think there is something unique Let's uh make them unique okay so API users what is this duplicate key value while it's Unique key constraint I think it's trying to generate a ID and ID is already there as a one that may be the possibility so what I can do is we can just generate because here is the migrations and this is the user okay this is Auto incremented ID okay so while doing the insert operation so we need to make sure that we are not inserting the same ID so that's why I always prefer having uuids or cuids in the database because we can generate those uuids and cuids and can insert okay so this is auto incrementing the IDS I can also check in the database I think maybe I didn't restart the application that's why it was behaving like this because increasing the IDS is the responsibility of the database orm right because we are doing insert and if the ID is auto incremented it should keep appending one two three four five six so here this is how I'm inserting that so just like we need to make sure that we are not duplicating the email and you will get the the values out of it so this is how we are creating a users and then we are fetching the list of the users here which will give me the big list so it's just like a simple crud operations which I was talking about and this is how you will play with the next migration Cedars and the database connections with the nest GS okay I mean it's like a really Plains and simple apis you can play around with this I will share this particular code and this is how we are initializing the database connections and putting all the migrations okay so that's it about uh next now next we are going to talk about sqlise and that's it after that but after that I'm going to build end-to-end application with mongodb so in mongodb we are going to use a type goose and how we are going to interface with the mongodb with multiple collections so in the in the next case I haven't worked on any project where I have multiple Collections and associating the relationships in the nest GS project so let's say I have a user blog post comments reviews and associating their relationships and doing a read and write operations from that so that's the the final Target and with that also we will cover the module 3 which is all about covering the test cases how we are writing the test cases for the services controllers the middlewares auth guards whatever we are writing in the next GS um

Channel: Code with tkssharma

Views: 1,599

Keywords: javascript, nestjs, nodejs, nestjs microservices, nestjs mongoose, nestjs swagger, nestjs tutorials, nestjs crash course, nestjs training, nestjs apis, nestjs sequelize, nestjs logger, nestjs js, nestjs typeorm, nestjs services, nestjs query param, nestjs tutorial, nestjs graphql, nestjs typeorm postgres, nestjs typeorm mysql, nestjs postgresql, nest js, nestjs knex, next js knex, knex tutorials, knex js typescript, knex js

Id: Idu7XNFi31c

Length: 27min 3sec (1623 seconds)

Published: Wed Jul 19 2023