Get Started With Drizzle ORM - Migrations, Queries & more!

there's lots of ORS out there but when it comes to typescript and postgress my go-to has been Prisma but this new one popped onto my radar recently called drizzle and I want to check it out so that's what we'll do on this video obviously we need a goal for the video not just reading through the documentation so we'll build a small API that handles a few crud operations for a blogging application this is basically what we did in the bun videos but instead of Prisma we'll just use drizzle digging through the documentation it looks like their main selling point is being lightweight and performant but what I found really interesting is the approach that they've taken with the queries if you've never used an omm before or you know SQL then you wouldn't find it too bad picking up drizzle because of how it imitates SQL in this case if you just want to select all rows from a table you just do a DB select from and the table which is just like SQL it obviously has a bit more of a modern touch to it using models and preventing things like SQL injections for you but one of the reasons orms can be intimidating at first to pick up is because of the new querying structure that you have to learn and get used to if you're already familiar with that structure then it looks like drizzle also supports that with a queries API this can simplify complex queries a lot especially when you have lots of joints happening so in this case for example on the users table we're doing a fine Min and also including all the posts to get started with this video we'll actually use a template that I created a few days ago this template just provides us with a postgress database and an Express server I'll leave the link to this template in the description if you want to follow along but if we take a quick look at what's happening in this repo we'll have a Docker file this Docker file is just strictly responsible for creating our server and running it in the container and then we also have a Docker compos in here we just create our database it's a postris database using the latest image and then our server which gets built from the docker file next we have our package Json and here we just install cores. m Express and Morgan if you've never used Morgan before it basically just outputs all the requests that API receives in the console for us just to have that visibility into what's going on and yeah lastly we just have our index.ts in which we just create our our Express server and for now we just have one endpoint which just returns hello world to get started make sure you run npm install this we'll just make sure that all of our packages are installed and now we can go ahead and install our drizzle packages and the rest of the packages that we'll need so we'll just do npm install drizzle orm this is the orm itself then we'll do drizzle Zod and PG drizzle Zod will just be for validation on our requests coming in and PG will just allow us to connect to the database once those packages are installed we can go ahead and install a few other Dev packages these are just specific for our development environment so we'll do npmi D capital D and we'll install drizzle kit and we'll look at what that does in a second and then TSX we'll just use this to run some scripts and then at types slpg now that we have all of our packages installed let's close our terminal and go into our package.json and here we'll have to make some changes so for our Dev environment since our index file is in the source folder we'll have to do uh source s/ index start and build can be left alone but we'll need a few more commands so we'll do introspect the goal of introspect is if we already have a database it will pull all of our tables into a migration so then we have that tracked into a file we won't need this for the video but it's still good to know about it and to call this we'll just use drizzle kit introspect PG and we'll pass it a config which will just be drizzle. config.sys we can do the same thing for the generate command but instead of introspect here we'll just pass generate what generate does is it actually creates the migrations for us so it'll look at our models and check for changes and then create our migrations for us next we actually need our migration script so we'll call this migrate and in order to call this we'll just do TSX D R.V SLC config and then we need our migration script so we'll put this in Source DB migrate dots and lastly one cool feature of drizzle is that it provides us with a studio this is just so we can see our database and we'll just call this studio and this is also from drizzle kit so we'll call drizzle kit studio now that we're done with our package Json you notice that in here we're calling n so let's just create a quick EnV file so we'll all call ITV and there's a few values that we need in here so I'll just copy and paste them it's pretty straight forward we need a port for our server the name for the database the host for the database our user password and putting all of that together into a URL but that's basically ourv file next in our package.json we also specified a drizzle. config so we'll just create that file next so I'll just copy and paste this and create a new file and here we need to import EnV so we'll do EnV SLC config next we need to import a type so we'll do import type config which comes from drizzle kit and we need to export a default object from here so we'll do export default an object and we'll also type this as satisfies config we'll export a few properties on this object such as schema we don't have a schema file just yet but we'll still provide the path so we'll put this in Source SL DB schema index.ts we need a folder in which drizzle will export all of our migrations and all the files that it generates so we'll do out and the same thing do/ Source DB and we'll call this drizzle for the driver since we're using postgress we'll just do PG and we can also have DB credentials and in here we'll pass a connection string and we'll make sure this is a string which comes from process. env. dbor URL also I want to make sure this is verbose so we'll pass this as true and also strict which is also true so we're done with our drizzle config now let's actually work on the schema files so we'll go ahead and create those folders in here we need a new folder called DB and another folder called schema and which we just create a index.ts the schema just like Prisma if you're familiar with that this is where all of our models are going to live so we can export const since this is a blogging application we'll have a post table and in order to create this we need to call PG table this comes from the drizzle orm PG core so we'll import that and the name of the table which will be posts and in the second parameter we can provide an object with all the fields that we want on this table so we'll say our table will have an ID and I want this to be a uu ID so I'll just type U ID and this also comes from PG core we'll call this ID and the default for this will just be default random and this is the primary key I want our posts to also have a title so we'll do title this is a varar also from PG core give it the name title I will actually cap this to a length of 256 so so we'll do length 256 and we don't want this column to be null so we'll just do not null next we have our content which just like the title will be a VAR chart with the length of 256 and we cannot make this null so all we have to do is just rename this to content and lastly for this table I just want a create at which is a timestamp and also from PG core created at which defaults to now and it is also not null the one thing I've notice about drizzle is they don't yet support updated at so we'll have to leave that column for now next let's just export some types for a model so we'll do export type post and we can infert the select model type which also comes from drizzle orm and in here we just provide posts actually we have to type of posts and now we have our model which is just a post containing all the fields that we specified in our table we can do the same thing for a new model so I'll do export type new post and this uses infer insert model which is also from the drizzle orm and type of posts and you would just simply use this whenever you create a new post and this in most other scenarios next let's actually create our database Connection by just creating an index file in the DB folder so we'll just do index.ts in order to do that we'll just have to import pool and this comes from PG and we'll also have to import node PG database this comes from node postgress and we also need to import drizzle next let's also import our schema so we'll do star as schema from. schema we'll throw a check in here just to make sure we have all of our values for connecting to the database if not throwing an error but otherwise we can create our pool so we'll do con pool and this equals to new pool and in here for the port we'll just do 5432 which is the default port Port usually and the rest are just the process. EMV values that we saved in ourv file next we actually have to export our database so we'll do export con DB and this is a node postgress database with the type of schema and this is equal to drizzle where we pass in the pool and the schema and that's it now we have our database connection set up we just have to import this wherever we want to use our database but next let's actually look at how to create our migration SC script so we'll also create this in here so we'll do new file migrate TTS we'll do the same thing in here so we'll import pool and we'll also import migrate you'll see here a lot of suggestions I'll just type it out so we'll do from drizzle o/ node postgress slm grator next we also want to import node PG database and drizzle we'll create a function in here and it needs to be async so we'll do async function Main and underneath it we'll just call Main this is because we can't await things inside of the file without actually calling them inside of an async function so we'll have to wrap everything inside of this function and in here we can create our pool so we'll do pool is equal to new pool but this time let's just pass the connection string so we'll do connection string process. env. DB Euro for the database it's the same thing cons DB node postp database is equal to drizzle and pass in the pool next we can actually log out that our migrations are running and then we can do an await for migrate which is actually going to run our migrations provided the database connection and next we just need to provide the migrations folder which as we specified before is just Source DB drizzle next we can also console log that the migrations have ran and after this we can await pool. end and this will exit the function for us I think our app is at a good point right now so we can actually test some things I have a configuration set up for for running my Docker compose but we'll do this simply from the terminal for now so if I clear this we should be able to just do Docker compose up and we'll also build the images you don't have to specify that flag but I think I've built them before so I just want to make sure everything is brand new now that everything is running you should be seeing the message server listening on Port and to quickly test this I created a tester. HTTP and we can run this once we run this we can actually see the message hello world which just means that our server is running properly I can also see in here that my Docker is running with the containers so I can see that my database and my server are running which just means we should be able to run our generate and migrate script so I'll create another terminal in here and we can do npm run generate as you can see this created a migration file for us and we can even see it in here so Source database and we now have the drizzle folder with the SQL migration that it created so you can see it's created the table if it doesn't exist with the columns that we specified next in here we actually want to apply this migration to our database so we'll just do npm run migrate we see our console logs and it's saying that all migrations have been ran and if we do npm run Studio this will run that drizzle Studio that I was talking about and if we go to this URL that it provided for us now we're inside of the studio and I can select all the tables that I want to show up in my tabs so I'll select post and then we can even click into post and this is the cool thing about drizzle we get this UI that we can see that we don't actually have any records as of now but everything everything seems to be working fine we have all of the columns that we created and no records now since our database is working as expected we can start working on the routes and in order to do that I'll just create a new folder in here and I'll call this routes and inside our routes since we have our posts I'll just create another one called post and setting up all the routes I'll just do this in an index.ts so I'll do file index.ts this is pretty straightforward I'll do import router from Express we don't have the hand handlers set up but I'll still import them so I'll do import the handlers from our handlers file we'll work on this file next but for now let's just set up our router I'll do export const router is equal to router and we'll have about five routes in here we'll do router. getet and this is on the slash path and it'll be handled by get post I'll just duplicate this five times so we have five here this will be another get but instead now this will be passing in an ID and it'll get an individ ual post so we'll do get post we'll have a post and this creates the post we'll have a patch and this updates the post and we'll have a delete which deletes the post and for the update we actually also have to provide the ID so I'll pass that in there next we can work on the handlers file so I'll do file handlers. TS let's import some of the things I will use so we'll do import request from Express and we also want to import response down here let's also import DB and this is the one one that we created and let's also import some things from our schema so we'll do new post we'll also import post and the posts these two are just types and this is actually for using the table in our queries for getting a post we'll do export async function get post this gets the request which is just a request type and then the response which is a response and it returns a promise which is resolving to a response and here we'll do this for all of them I'll have a try catch so we'll do try and then for the catch we just cons toog the error and then if anything we just return a status of 500 with the message that an error was encountered this error message can definitely be improved on but I'll leave that to you guys now we can grab the ID from the parameters so we'll do request. params now there's a couple ways of doing this next part using drizzle the first way is doing the SQL like way of doing things as I showed you at the beginning of the video so we'll do a const post is uh an array of posts and we'll do await db. select so we'll do a select from post where and in order to do a comparison and drizzle we actually have to import EQ and this basically just does an equality comparison so we'll import this from drizzle orm and in here we'll just do posts. ID and compare this to ID oh you'll notice that we have an error here this is just because we call this post so I'll do instead I'll do all post the tricky thing about using this way of doing things is that no matter if one record is found or multiple records it will always return an array so next we actually have to do if all posts. length is less than one so if we didn't find any posts then we'll just return a response with the status of 404 and we'll send a message saying that the post is not found after this we just have to return a response and send the post which is just all posts and index zero so this is the first way of doing things when it comes to finding just one post and it's just a simple comparison by ID I actually prefer doing this using the query's API so let's look at how to do that first we'll just do const post and in here we'll do await called DB again and instead use Query this time and we'll do posts and we'll do a fine first all we have to do in here is just pass the condition so we'll do where EQ so we'll use EQ again post. ID and the ID this is where I was saying if you're more familiar with something like Prisma this structure of doing things is actually a lot more familiar now in here we'll do if not post we'll do the same thing so we'll do return response that status with a 404 and then send the message of post not found and if the post was found we'll just return response. send post but yeah when it actually comes to finding things I prefer this method I find it a lot cleaner and easier to understand but there's nothing wrong with using the other method now let's look at getting all the posts so we'll do export async function get posts and the parameters are the same so I'll just copy and paste that and so is the response I'm a bit lazy so we'll do the same try catch in here so try catch I just copy and pasted that so now all we have to do here is just const all posts and this is an array of posts so we'll do Post array and await db. query. poost and we'll do a fine menu this time if you want wanted to use the other method all you would have to do in here so you would delete this part and do select and from Posts and that's it these two lines are basically the same thing but now since we have all of our posts we can just do return response. send and return the posts we can now look at creating a post so I'll just copy and paste this this is just a function of creating a post same parameters same response and in here the same try catch and we'll take the parameters from the body so we'll do const title and content and this is equal request. body we'll worry about the validation on these later but for now we'll just do con results and results is actually going to be an array of new posts so we'll do new post array and in order to create a post using drizzle all we have to do is just await db. insert and we'll do an insert into the post table with the values of title and content and the other thing you want to make sure you do with uh drizzle is that at the end you want to do Dot returning otherwise doing this part we'll just insert it into the table without actually returning it to us so since we want that we'll just put it in there next I'll just add a check for the results so if not results or the length is less than one we'll just return a message saying that the post could not be created otherwise we just want to do return response. send and the results at index zero now that we have our create let's look at how to do an update so I'll put it here we'll do an update and in here it's the same thing with the try and catch and we'll just grab our values which we have ID from the request parameters and the title and the content from the request. body an update is also pretty straightforward we'll do con updated post and this also returns an array so we'll do Post array is equal to await DB and I'll actually put this on a new line so it's more readable and we'll do update we'll update the post table and we'll set the values of the title and the content to the ones that were provided in the request and we also need a wear clause in here so we'll do a wear and again EQ post. ID is equal to ID and we also need the returning now we can just throw a condition in here to check for if the post is updated if the post wasn't updated we can just post could not be found this looks wrong so we'll just do Post could not be updated lastly we just do return response. send updated post and at index zero the last Handler that we have is a delete post post so in here we'll get the ID and I put the ID in the request body this is up to you if you want to have it in the URL or the or the request body but I just put it in the body so we'll do request. body and we'll do con deleted posts is equal to await DB and we'll do a delete on the post table where we have the ID of the post so we'll do post. ID is equal to ID and in this scenario I actually want to be more specific with what's being returned so we'll do do returning and we can actually provide the value that we want returns so I'll do ID is posts. ID in this case when we get the list of posts that were deleted all we have for each post is the ID of the post that were deleted and just like the other ones we'll throw a condition in here for if the post could not be found otherwise we just return the deleted post so that's it for all of our handlers we can actually go in this index.ts file and see that all the errors are now gone all we have left to do is just connecting this router to the rest of the application so we'll go into our application index.ts we'll delete this route since we don't need it anymore and at the top of the file I'll just do another import so we'll import router as post router and this is coming from that router that we created so routes slost now in here I want to prefix my API by doing SL API so I'll do const API prefix is equal to/ API and now we can do app.use and for the route I'll do a string so I'll do po API prefix slost and this is where all of our posts are going to live and just passing in the post router and that's it for all of our posts we can now actually hit the routes for our posts but before we do that since this is a video on the orm itself and not how to build an API let's continue with adding relationships to our tables and to do that we'll go back to our schema so we'll go to DB schema index.ts and the goal here is to have our posts and each post needs to have an author and for each author we can grab all the posts for that author in order to do that we'll just do another table so we'll do export con user users is equal to PG table and passing in here the name users I won't make this table too complicated we'll just have an ID which is a uu ID for the column ID it will have a default random and it's the primary key and all we have for each user is just a name which is a varar for the column name also a length of 256 and we want to make sure this is not null and that's it for our users now we just need the types I'll just do the same thing that we did for the post so we'll have a user and a new user now in order to make the connection between these two we'll have to add another import so we'll import relations and we can start with the post relation and in order to do that we'll just do export cost post relations is equal to relations and this is for the Post table the goal here is to have a on to many relationship where one user can have many posts but one post can only belong to one user so we'll do a function here where we get one and in here we'll return an object where the author is now mapped to one and this is for the users table and the fields that we need from here are the posts. author ID and we'll add that column in a second but now we just need references and this will reference the users. ID column let's also add that column to the post so we'll have author ID and this author ID is just a uid column for author ID and it cannot be null so the relationship is now set up on the post side we also need to to set up the relationship on the user side this one's a lot simpler so we'll do export con users relations is equal to relations and this is for the users table where instead of getting one now we get many since this is a one to many relationship and this Returns the object where posts is mapped to many posts and that's all we have to do in order to set up a on to many relationship between our posts and our users in order to see this an action we can actually create more routes and this time it's for the users so we'll go in here and create another folder and this is for users and this users folder will do the same thing where we have an index index.ts and in this file we just set up the router the router is pretty straightforward we have our handlers again and this time we have a route where provided the user ID we get all the posts for that user and in order to create a user we also have a post route where we just create the user now we can focus on the handlers so I'll do new file handlers. TS we'll have very similar Imports to the post handlers and in here the create user is very similar to creating a post so we'll just copy and paste this and as you can see here we have results insert into users where the value is the name that was provided in the body and we have the same air handling on this side as well where it gets a bit more fun is getting the posts for a user so we'll do a get users posts it's the same air handling but instead in here we'll grab the ID so we'll do const ID is equal to request. prams and as we saw saw at the beginning of the video we can use find first so we'll do const user is equal to await db. query. users and we'll do a fine first where we have a where and this is equal to users. ID and the ID p in and in order to get the post for this user all we have to do is just with and in here pass posts is equal true find first we'll handle all the joins for us so it'll join the post on the users table now the same airor handling and if not just just send the user back as a response you'll notice that the errors are now gone in here since we have all the handlers now created all we have left to do is just go back to the app just like we imported the post router now we do users router from users and just like we set this up we'll do the same thing for users and passing in the users router great so now we have all of our routes created and we have a new table the one thing that we cannot forget to do though is going in here and running npm run generate this will look at at our schema file look at what changed and then create our migrations based on that so we'll run this file we'll see that it created this migration file and we can actually even look at it this will be in the drizzle folder and it's this new migration we'll see that it creates a new users table and it add the author ID column on the post table the one thing that I don't really like about drizzle is that you can't provide your own custom name for the migrations I tried looking through the documentation but I couldn't find a way of doing that hopefully they add it sometime in the future but now all we have left to do is just run mpm run migrate in order to apply these changes to our database oh and you'll see that I got an error here and this is because I stopped my Docker containers we can run the same thing I'll run it from here but you can just do the same command of Docker compose up now that my containers are running I can go back to my terminal and run that command again so mpm run migrate and all my migrations Ran So if I go in here and I'll actually restart my drizzle studio so I'll do npm run drizzle studio and now we have all of our tables I can just select all of them I'll even go into the post table and now we can see the author column here now I don't know why the users tab didn't save but I'll go back in here select all of them again and go into the users table we can see that now they have all the posts associated with that user this is a nice feature of the studio where you can see for each individual user the posts now let's go back to our tester. HTTP and just replace this with all of these new routes you can find all of these in the description Down Below in the repo that I provided or you can set all of these up using Postman whichever one you prefer but for now let's just test the getting all of the posts so if I hit this we actually get an error and if we look at our server we'll see that we have an error in our post handlers and if we take a look at it we'll go into Source routes post we can even see by the underline here so we go into handlers we have an error in our create post and that's because in our insert we now need to provide an author ID and we'll get that from the body so we'll do author ID and also passing that in here so that should resolve all of our errors I can restart my Docker container and if I go back to my tester I can hit this again and we'll see that we actually get an empty array now in order to create a post we have to create an author so let's go down to the post for users and just create a user with the name code Brew looks like that was successful and it provided us with the ID for the user we can now go up and create a new post so we'll do a new post with the title subscribe to code brew and the content will be more content like this coming and just providing the ID in here and you'll see that the post was created now if I take this ID for the Post we can throw this in the get so we'll do get posts ID and we'll get the post that we just created we can even go down to the patch now and provide the ID for that post in here and we'll update the title to like the video and content helps the channel so if we do this we'll see that our post was updated so now it says like the video helps the channel going back up to the get if we do a get on that same post we'll see that we get the updated post let's also take a look at this inside of the drizzle studio so if we refresh this now we see the new user code Brew with the posts so if I click on this we can see all the posts that code Brew created we can even go down to the posts we'll see that updated post and if I click on the author I can see the author down here I was also looking through the documentation and it looks like drizzle also supports Zod so I'll import this package and then we can use it to validate our routes for the data coming in so to do that I'll to my schema so we'll go database schema index for the post I can go down here since we already installed this package at the beginning of the video we can just do export con insert post schema and we can create a new insert schema this is from drizal Zod and we can provide the table post we can now copy this go down here create the same thing for users we'll do insert user schema and provide the users table now if we go down to the handlers so we'll go down to the post handlers for the create post we can add one more step in here so we'll do con new post is equal to the insert post schema we'll do a parse on here by providing the title the content and the author ID this will just ensure that the values passed in match the schema of the table in the database and now we can take this new post and just provide it in here instead of the values we can do the same thing for updating a post so in here we'll do const post is equal to insert post schema the difference here being that since we don't take an author ID in here to update the post we actually want to do a partial here and the only values that we want to take are the title so we'll do title is equal to true and then content is also equal to true I'll put this on a new line so these are the only two values that we want to verify for and then we'll do a parse on those same values so title and content and now we can take this post and pass it in here for the setter we'll just do the the same thing for the users handlers so when we create a new user we'll go in here so when we get the name we'll just do con new user is equal to insert user schema and I'm not sure why it's not importing it I'll just manually do it we just go in here insert user schema and for the schema we'll just do a parse and provide the name of that user now with the new user we just grab it and pass it in here and that's all we have to do in order to use Zod for our schemas and I can even test this by going to the patch and for this time I'll just exclude the title if I run this we'll see an error encountered we can obviously improve on these error messages but for now this will do we have our validation and we were able to use drizzle for migrations creating models creating relationships overall I think drizzle is great and if you see anything in this video that could be improved when using drizzle let me know down in the comments and make sure to subscribe

Channel: CodeBrew

Views: 7,069

Keywords: drizzle orm, drizzle orm tutorial, drizzle kit, drizzle orm course, drizzle orm crash course, prisma vs drizzle, typescript orm, drizzle orm sql, drizzle orm migrations, drizzle orm node js, drizzle introspect, sql drizzle, drizzle orm comprehensive course, nextjs drizzle, drizzle tutorial, drizzle schema, drizzle orm zod, zod validation drizzle, express js, drizzle orm postgres, drizzle orm typescript, express rest api, drizzle queries api, drizzle vs prisma, drizzle

Id: Eljdg5_EgOI

Length: 31min 51sec (1911 seconds)

Published: Fri Jan 12 2024