Complex Schema Design with Drizzle ORM | Common Patterns

over the past decade of working with backend JavaScript to talk to databases I have used many different ORS including things like SQ eyes and also waterline JS and also query Builders like connect JS and then the omm is based on top of that like bookshelf JS and objection JS but eventually in the world of JavaScript there was a push towards type safety with typescript and so libraries like typm popped up and also keyley which is a query Builder similar to connect but it has Type safe queries for like your table names and column names um and then eventually a lot all of these libraries also adopted uh type script so you could get some level of type safety including connect so you can actually create interfaces for your tables and get more type saf queries and uh I have apps running in production that have used all of these libraries including Prisma which is the more modern choice for working with databases uh in typescript or JavaScript and for good reason Prisma has a very nice way of defining your schema it's fully type safe also the query language is nice to work with most of the time um and it's it's a great library but there are certain scenarios where it starts to break down especially when trying to run Prisma in a serverless environment where you might have memory or size constraints and then sometimes Prisma generates multiple queries in situations where maybe a single query would be better so it does have its issues inter drizzle now there's been a lot of hype around drizzle but I have been working with Drizzle over the past week to implement a fairly complex schema and uh I like it I really like it a lot and uh ultimately I see it as kind of the culmination of all of these ideas in in working with databases in in back in JavaScript and uh it it does everything really nice so the type safety is awesome it's tiny so it has no dependencies it works really well in serverless environments also the query syntax is very interesting so they have two different kinds of syntax you can work with they have a more Prisma orm like syntax but they also have a query Builder syntax and finally it's performance so I'll show you some of the queries that it generates and it's kind of fascinating how it can take your really complex omm like code and just generate a single query from that so it's awesome to work with so in this video I'm going to show you how I implemented this complex database for an application called bite Dash so essentially this is like a food delivery app it has users it has restaurants restaurants have menu items users can make orders and then they add menu items to it so that there's a lot of complex and nested relationships going on here so I really put drizzle to the test and in this video I'm going to kind of show you some patterns that emerged in a more complex drizzle code base including defining your tables in separate files when you have a lot of different tables in your schema also how to create a single database Connection in a single drizzle instance that can be used whether you're migrating or seeding or running your uh code in an actual application also I'll show you how I created seeds for nested and related data because that gets uh pretty tricky to do once you have this many tables to work with uh and of course I'll show you all of the schemas that that I have created and what it looks like when you have more complex schemas as well so like the order table has a restaurant a user a driver an address so it it gets pretty interesting and finally I'll show you the query syntax as well so like I mentioned you can do things that are more om Prisma like but there also is a query Builder syntax so for instance this query here written as a query Builder with joins would look like this so I'll show you this I'll show you the queries that get generated and I had a lot of fun coming up with some much more complex queries uh a lot of them that kind of combine the OM syntax with the query Builder syntax so it's good stuff if this interests you stick around uh and let's do it so my name is CJ welcome to syntax [Music] now when you visit the drizzle docks you'll see that they support three different kinds of databases postgress databases MySQL and then also sqlite but they take a very different approach than I've seen with other orms and that for instance if you're connecting to a postgress database it's going to vary the type of postgress database in this modern world of of like hosted services and things like that so depending on the type of postgress database that you're connecting to you will choose a specific drizzle driver for that database and so they've created these drizzle drivers for all of these different kinds and also if you for instance choose MySQL they've created drivers for like Planet skill and also like a direct my SQL database and then also sqlite so with things like turo or when you're running sqlite in a specific type of environment so this is a very different approach than I've seen with other ORS But ultimately I think it allows them to create more performant queries and do things in a specific way depending on the type of database and service that you're connecting to now for my application I'm just doing postgress so I chose the postgress JS driver I have a a postgress database running locally in a Docker container and then when I deploy the app it's just postgress running in the [Music] cloud now all this code that I will show you is completely open source so the link is in the description you can peruse this code you can dive into it and kind of uh learn and and steal some of the patterns that I that I've come up with here uh first of all we're connecting to a postgress database so if you're not using Docker to run postgress locally you should so here's a simple little file that you can use you literally just copy paste this into your app and you can get a postgress database running locally all you have to do at this point is just say Docker compose up and now we have a postgress running on my machine which is great so uh steal that and and use this in your own application from there uh we have environment variables right so I have a EnV that has all of the information for how to connect to my database uh but this is a typescript application so I want a type Safe Way of working with those environment variables so I'm using Zod for that so if we take a look at my env. ts file we're using Zod to create a schema that essentially makes working with my our EnV types safe and uh it it basically pulls in all of those environment variables and then makes sure that they're valid and the application will not start if your EnV is not correct so this is another pattern that you can still there are some libraries out there that uh help with this but just using Zod directly is is not too bad so you can take a look at this file but basically anywhere I need these environment variables I'm using this file which first parses it from the EnV file from there we create our database connection now I talked about this but you will bring in the specific drizzle connector based on the type of database that you're connecting to so for me I'm using a postgress database so I have drizzle postgress DJs so this is how I will create my drizzle instance and then that corresponds to the actual postgress library and so that's how I'm creating my database connection now uh in the documentation it can be a little bit confusing because they actually show you so let's say you choose postgress and then you go in here to post crjs they have some code that you can copy paste here that has a specific connection for migrating and a specific connection for when you're just querying the database I didn't want to have that because that would be like code duplication so essentially what I've done is I have this single entry point so I have a DB folder inside of that I have my index.ts and this is where I create both of those things and then I have some environment variables that set the options depending on where we're running so if we are migrating or we're seating we set the max connections to one and so this is specific to postgress but you might have other things that you want to set specifically when you're migrating or seating but in this case uh we don't want to have multiple connections because we want to create the tables in the correct order and also seed the data in the correct order so when we're migrating or seeding single connection otherwise as many as you want use a connection pool so that way uh your queries across your app are more performant uh and then specifically when we're seaing the database I'm doing some truncation of tables so I wanted to kind of hide those uh warnings and notices that pop up so I just I disable those here and then from there we just pass pass that connection into the drizzle instance and then we can use this drizzle instance anywhere we need to query our database now for the schema you can see I'm importing everything from this DB schema file so let's take a look at that so we look in the source DB folder and then in schema we have an index.ts file and this Imports every single uh table and every single relation associated with that [Music] table so before we dive into my schema files let's take a highle overview of this overall database structure so it all starts with users we have users of our application now a user can also be a driver and so the driver has a user ID which is a foreign key and then users can have many different addresses so if you think about it maybe sometimes you want to deliver the food to your house maybe sometimes you want to deliver it when you're traveling or maybe you want to deliver it to work or something like that so a user can have many addresses now an address has a city ID and so there's a lookup table for cities and then a city has a state ID and so for this application it's just us-based things get much more complicated if you want to support addresses for different countries but we're we're imagining that this application only works in the United States uh from there we have restaurants now A restaurant uh we're actually just embedding the address in the restaurant table in a more complex app you might actually uh have a restaurant table that has a maybe a join table for addresses let's say you have multiple locations in a single City you probably don't want to duplicate that restaurant information information so there's other ways to do this I simplified it by just storing the address directly on the restaurant but we do have that City ID so that way when someone is searching for restaurants the user we can use their specific delivery address City ID to look up those specific restaurants from there a restaurant can have many menu items then each menu item can have a category now this is actually where I'll show you drizzle studio so we can actually take a look at this lookup table here now drizzle Studio runs in the web browser and it lets you inspect your tables insert data that kind of thing and so to use it you're going to need a config file and so uh in the docs they have a way that you can copy paste that in but let me show you how I set mine up so if we take a look it's at the root here it's called drizzle. config and uh you can see that we're importing our schema from that same common location that we imported it when we were setting up the drizzle connection also I'm putting my migrations folder specifically in the DB folder so uh you can have them at the root but I like to consolidate all of my DB stuff into this folder itself so specifically I want my migration to exist in this folder here now we specify the driver as postgress and then we can specify our connection URL now again I don't want data duplication so we're pulling in that environment variable from that that type safe EnV from here because this file lives in the root of my codebase here if from the command line I do an MPX drizzle Das kit Studio that will launch drizzle studio and so drizzle studio will let you uh dive into all of your tables you can also like add records it's a nice way of interacting with your database when you're in development but for instance we have this category lookup table so a menu item can be an appetizer a lunch a dinner a salad Etc but then we also have a lookup table for like our states right so these are the states that we support and then we have a lookup table for cities so these are the cities that we support as well from there we have orders so a user can place an order and that order is going to have a delivery address so there's a specific address that it's going to be delivered to we have the user that placed the order and then once the order is in progress we have a driver that's going to get assigned to that order now a driver just relates to the user table but it has specific information about like the car that they're driving and whether or not they're online and available and then an order has many menu items on it so for any given item that gets added to the order we insert a row in this table here then we actually take a snapshot of the price and the quantity because it's possible that later on a restaurant owner might change the price of a menu item but we want historical information about how much a user paid for that specific menu item when they place the order so that's why we're we're duplicating that data here but basically an order is going to have many of those menu items and then uh an order has an order status so if we look back in the database the status catalog is kind of the lookup table here so an order can be in any one of these states so it can be new confirmed preparing ready for delivery out for delivery delivered or canceled and so an order goes through many different statuses so it's going to have an entry in this join table here this order status for every time that status changes and so that way you could Implement something like a delivery tracker that shows exactly when these various events happened and that's our database uh as you can see some things are normalized some things aren't it could be more complex but I think this was sufficiently complex to really put drizzle to the test and so let's see how some of this stuff was [Music] implemented now let's take a look at the user schema so in Source DB schema I have user. TS and this is where I Define the user table now you'll notice that I am importing from drizzle orm PG core and so when you look in the drizal documentation under column types you're going to want to make sure that you choose the specific kind of database that you're connecting to because if you're uh defining a schema for a mySQL database you're going to want to import from that specific library and also they have one for sqlite so because I'm using postgress I import from PG core now uh you can see when I'm defining these columns we can have these this kind of like chained method to Define our constraints so for one when you're working in postgress if you want to Define an ID column they have this serial type that makes it auto incrementing and they also have the primary key constraint for all of my other columns you can see most of these have the not null constraint and then for some of them they need to be unique like phone number should be unique and email should be unique so those have the unique constraint and under the hood that's actually going to create those constraints on the database itself now another thing you'll see is for any given uh column type like varchar you might have some options that you can pass in so in this case all of these varchars have a max length set to 255 and then for example when we're creating timestamps we can also set the mode and so this is actually one of the nice things uh about working with dates in drizzle is if you've worked with other OMS a lot of times they'll par the date from the database that comes back as a string into like the date object in JavaScript and that's not always ideal so if you set the mode to string you're always going to have it as strings and then you can use your preferred date parsing library to turn it into a date if you really need to now another thing you see here on the time stamp table is this default Now function and so this is just built into timestamp and it actually will use the database method for setting a date if we insert this row and we we didn't specify a created at or an updated at uh value now from there you can see I have some relations down here we're just going to ignore those for now because technically if you're just using the query Builder syntax you actually don't even need to set up these relations these relations are only needed whenever you're using the orm style syntax so we'll talk about that later next let's take a look at defining relationships with a foreign key constraint and so you'll see on a column you have this references method so in our database schema here let's actually take a look at city and state so you can see that a city uh references the state via state ID so if we look at our codes we have the state table which is pretty straightforward but then if you look at the city table it actually references that state table right so we have a state ID it's an integer and we say it can't be null which means you know it must have a reference and it references the state table ID column and so this is how we set up that foreign key constraint the other thing we have going on on this table is a composite key right so for any given City it must be unique within that state so you can't have two cities with the same name so we have set up a composite key and so if we look in the docs here under uh indexes and constraints you can see how they set up this composite key now it's possible to just create it without a name and then you just pass in the columns and it'll create that composite key but you can see that you can also pass in a custom name so if for whatever reason uh maybe if you're creating this from an existing database or something like that you might actually want to set a custom name but then we just pass in the columns and we have our composite key let's also take a look at how to set up indexes so indexes are also constraints so they appear in that uh function that you pass in but specifically for our app we set up an index on the restaurant so uh we want to be able to query for restaurants by name and we want that to be fast so we set up an index on the the name column and so here you can see we just call it whatever we'd like and then use the index function that comes from PG core and then specify the column that you want to index and that's really the extent of defining your tables now I haven't talked about this yet but what I really like is that it's actually just done with code and then the thing that is returned is an actual typescript type that we can use everywhere else in our codebase so this is really a really nice code first way of doing this where we Define our tables in this way but the literal thing that we're exporting here is also the type that we can use everywhere else in our application so that's a beautiful thing uh let me just show you some of the other tables but beyond what I showed that's that's pretty much it you know we have some unique constraints not no constraints we have some indices and then we also have like foreign key constraints and so I guess I'll show you that order table again CU this one probably has the most relationships you can see there are several different uh I foreign key IDs that are set set up here pointing to different tables and for these I'm actually importing that specific table into this [Music] file next let's talk about migration so now that we have these schemas defined we need to actually run them against our database and this is where migrations come in so uh you can see in their docs here they say Define your schemas and then you need a connection file so this is the same connection file that we're using for drizzle Studio but when you're migrating it's going to use this file as well so I'll remind you at the root of our code base we have our config set up here and the most important thing is pointing it at the schema and then also telling it where the migrations should go so uh specifically in our schema folder we have an index file that Imports all of our tables and so because this is where we've declared our schema this is how it's going to know to create all of these uh specific tables in the migration itself now to generate the migration you need to run the drizzle kit generate command and then pass in the specific type of database and so I have added a script to my package.json that does just this so you can see I have this DB generate command that runs that specific command specifically for postgress and so whenever we run this it'll look at those schema files any changes that have happened will get added to a a new migration file now to actually run the migrations you have you just do it with typescript code so there's there's nothing built into drizzle kit that runs them you need to specifically create a migration file and then just run that with something like TSX so let's take a look at how I did that so if you look in the codes in the DB folder I have have a migrate file now you can see that I set up this environment variable here DB migrating and I talked about this earlier but when we're running our migrations the connection to our database should only use a maximum of one connection so that's why I set up these environment variables so when migrating is true this connection here is only going to use uh one a Max of one connection to connect to the database so that way we're not trying to create tables in in multiple processes so uh this script right here basically just throws an error if you try to run migrations and you haven't set this it's not going to let you do it because it wants to make sure that it's using a single connection from there it just Imports the DB and the connection that you're going to use everywhere else in your application so it's nice and easy you don't have any duplicate code here and then the other thing is we want to reuse that configuration for the migrations folder so instead of duplicating that we specifically bring in the dri drizzle config and then just pass in that out value so that way it's using the same migrations folder and uh from there we just run the file so if you look in our package.json we have a DB migrate and uh specifically we're setting the DB migrating value to true I'm using cross EnV so this works crossplatform but then it literally just runs that file to generate the [Music] migration now let's see migrations in action so right now we have our our base table set up and and I highly recommend that you actually don't create any migrations until you have a standardized stable database structure because when you're in development you're going to be changing things a lot and uh you don't want to have a bunch of migrations that are just doing development things where it's kind of like changing the base structure of your database so I highly recommend figure out your structure and then you have a base migration that defines That Base structure but from there maybe you have new feature requests or new things that you're adding to the app and that's when migrations are useful so let's see an example of that if you look at our existing schema uh restaurants have no way of being managed like unless we have some sort of like super admin or like God mode uh there's no way for users to have the Privileges to edit a restaurant so what I want is like a join table right I want an owner's table where I can have a user ID and a restaurant ID and if a user is in there they have the privilege to edit that restaurant and also edit that Restaurants menu items so let's do just that in my schema folder I'm going to create a new file for that table let's call it restaurant owner table from there our base table setup will look like this we have the restaurant owner which is a PG table we export it and it has an ID which is a primary key uh then I'm going to set up the restaurant ID and so uh we'll call this restaurant uncore ID and it's going to be not null and it's going to reference the restaurant table so in order to do this I'm going to need to import that restaurant schema and then we also need the owner ID which is going to come from the uh user table so I'm going to call this owner ID and the name here will be owner ID but this is going to reference the user table so this is another thing that I'll need to import in so this looks good I have a basically a join table here that references both of those tables the other thing I want to do though is set up a composite key right because uh you should only have uh one owner and ID like an owner can't be an owner twice on here so let's just set that up here and we'll just call this uh the uh unique owner and this is going to be uh unique and so we'll pull that in from PG core I want to point out that this is something you need to be careful of whenever you're uh working in your schemas uh because there's these other libraries we could potentially Import in from so don't make that mistake make sure that you're importing from the correct Library uh whenever you do this so should you be unique on and then I'm going to pass in both of those columns so I'll say table. restaurant ID and table. owner ID great this looks good so I've defined my table now I need to pull it into my schema so we'll go back over to our index here and I'm going to import it in so here I have uh export the default export from restaurant owner from that spe specific restaurant owner schema now from there we can actually generate our migrations right because we've added a new table so we're going to expect that the migration that gets generated adds that new table so let's see it in action and so from the command line here I'm going to do an mpm run DB generate and so that's going to look at the existing schema look at the changes that I've made and then generate a migration file so if we take a look in that migrations folder you can see that it creates the table if it doesn't exist and then it adds all of our constraints uh this is also one of the the beautiful things about drizzle as well these migration files are extremely readable and so it's also a great way to learn uh SQL ddl syntax if you're not even familiar with that because it's very straightforward right it's creating the table adding the constraints good to go but now that that migration exists we need to run it against the database to actually create that table so we need to run the migrate command so so far we generated the migration now we need to actually run migrate which is going to create that table on the on the uh database itself so if I do an PM run DB migrate that then uh looks at that migration file and runs it on the database now the reason we're seeing all the SQL output is because in my drizzle instance I have logger set to true so uh you could set up another environment variable for this but for now I just have it so we can see the the stuff that's happening behind the scenes as [Music] well now let's take a look at seeding the database now just like migrations are literally just a types script file that needs to be run seeds are the exact same thing so seeds aren't built into drizzle kit you just literally write a typescript file that seeds your database so I created this uh Source DB seed. TS file and then in my package.json when I do a DB seed we set our environment variable DB seing to true and then we just run that file so let's take a look at how I actually run these seeds now just like migrating I check to make sure that we're in seeding mode and otherwise I throw an error and the seeds don't run so seeding needs to be set to True specifically so we have some connection options that get set whenever seeding is set to true so if it's true we have a single connection because we need to create these seeds in the correct order and then also I'm hiding any notices that pop up because I'm doing some table truncation so let me show you that so whenever you see the database you basically want to start from scratch now technically you you don't need to do this I am doing some SQL raw here to do a truncate and restarting the identity uh technically you could also just do a delete all the rows in the table but that won't reset the primary key on the table and uh there's reasons why you may or may not want that but specifically because we're in postgress I can run this trunk aate command so it specifically wipes out the table and it restarts the ID so every single table is always going to start with an ID of one after these these seeds are run so I run over all of my tables from the schema and reset them now the order here uh is important now technically because we're doing Cascade uh you won't R run into as many issues because it actually will delete the dependent rows and the other tables uh but if you didn't have a casc the order is important because you need to delete in the order of things that are not dependent on each other so that's another thing to take into account from there we also need to seed our data in a specific order so the way that I have seeds set up is in the seeds folder I have a separate file for each of the the tables that I want to seed and so let's take a look at the category seeds it's pretty simple uh I export a function that takes in the database connection and then we just do some some inserting so uh the way uh insertion works with the query syntax here is you do db. insert insert you pass in the schema and then you pass in some values and you'll notice here I'm just using Json files so if we take a look in the data folder I have categories. Json and these are just the basic categories that get inserted in and so for simple lookup tables like this it's it's super easy right you literally just uh insert the data itself but when things are related it gets a little bit more complex so for instance uh let's take a look at the city table so all three of these table category status catalog and state those are all lookup tables so they're not dependent on any tables but the city table is dependent on the state table so for these purposes the state table needs to be inserted in seeded first so that when we're inserting cities we can relate them back to the state table and so let me show you how I do that if you look in my data folder I've got the cities. Json and you can see that I'm just referencing the state name now state name doesn't exist on the city table during my seed I need to actually look up the ID of the state based on its name uh but by structuring my seeds in this way I don't have to know the ID ahead of time so even if I wasn't uh restarting the identity I would still be able to se this table so uh let's take a look at the codes for that and it it's literally just some SQL code so iterate over that array and then for each one of them I need to find the state with that specific name right so we uh grab the cities from that Json file for each one find the state that has that specific name now in this example example you actually can see me using the more om syntax so I'm using like find first uh and you're seeing a first look at how we actually do uh wear statements so equal actually comes from jizzel orm and it's how we do these these comparisons so I'm saying the state. name uh column should be equal to the state name String that comes from that Json file so uh we look up the state in the database and it should exist because we seated the state table first and then from there if we didn't find it I just completely exit and and stop seating because there an issue with the the data that we're seing but if we did find it we then return it we we set that state ID so that way when we're putting it into the database it has the actual ID of the corresponding State uh from there we just have an array of insertable values and then we just use the query Builder syntax to insert all of those cities now let's take a look at restaurants because it things get even more complex so first I'll show you the data if we look in the data folder and then restaurants. Json you can see that for each restaurant it has its basic info but it also needs to reference the city table right so a restaurant actually has a city ID so it's going to be a similar idea here for every restaurant we're going to need to look up that City by name first to grab its ID from there I want to insert menu items for this specific restaurant and so I actually have these embedded in this Json file here because a menu item needs a restaurant ID and so my seeds are basically going to have to find the city insert the restaurant get back the restaurant ID and then insert menu items with that specific restaurant ID so if we look at the restaurant seed file you can see just that so we pull in the list of restaurants and then for each of those we first need to find the city if we didn't find it we we just stopped seating because there was an error in our in our Json data we then now have the city ID and we can actually insert that restaurant into our database from there we use returning so that way we have access to the inserted restaurant and then we can use the corresponding ID whenever we're inserting the specific uh menu items now you'll notice this gets even more complex because a men menu item has a category and so we need to look up the category ID so I have a little helper function here that looks up the category ID by name returns that category ID and then we can use that when inserting the menu item and also the restaurant ID now similar ID with users users have a an address so if we look at the data for users here we've got some user info and then a list of addresses so we need to insert that user and then get back the user ID then insert the address so let me show you that and also we need to Hash the passwords when we're inserting them into our database so let's look at the user seed for every user we insert them into the database now we're giving some default values here like yes the email is verified yes the phone is verified but we need to Hash these passwords before they go into our database now the way you hash the password is going to be dependent on what you're using for your your off provider but in this case we're just assuming that the actual application code that's going to be verifying these hashes is also using argon 2 so I'm pulling in the Argon 2 library to Hash that password before it goes into the database from there we get back the user ID and then we can use that user ID when inserting into the address table but again there's another nested relationship because an address also needs a city ID so we do a quick look up on the city name to grab the city ID and that's our seeds it's a lot I highly recommend look at the link in the description you can peruse through this code and kind of like dive a bit deeper uh but the beautiful thing about this is I can start my database from scratch no matter what so let's do just that I am going to kill this uh Docker container and then remove the uh DB data which is all where all of the docker container data is stored and then do a Docker compose up now I have a completely fresh post grass database there's no data in it there's no tables in it nothing at all and so we've started from scratch but we can still get back to where we were because we have migrations and we also have our seeds so from here let's do an mpm run DB migrate and so that's going to take all of our migrations and run them against our database over here and then we can run the seeds so if I do an mpm run DB seed that's going to look at all of our seed files and then insert all of that data into the table uh and we still have drizzle Studio running so if we take a look at drizzle Studio you can see that all of the the data got inserted here and and we're good to go and the nice thing about the way I've set up these seeds is we could run these as many times as we want and it's going to work every time because it's literally emptying out the tables before it happens so I could run the seeds again and uh everything's going to be just fine here because of the way that I'm I'm resetting those tables and actually let me show you uh how what would happen if we didn't do the truncation so right now for instance you can see categories start with ID one so 1 2 3 4 5 six and similar for like cities and states because we're restarting that identity their their IDs start over but if we didn't truncate the table we actually could just do a delete from on that row so if I do a db. delete we pass in the table name so now this is just going to do a delete are from table uh but because this isn't doing truncation that's where the order actually matters here but it's it's still going to work because of the way that I set this up so now if I change that to delete instead of truncate watch what happens with the IDS we'll run these seeds again runs just fine but if we look back in drel studio uh the IDS have not been reset right so like our category IDs are now starting at 7 and our city IDs are starting at 7 so depending on your application you may or may not want those IDs to always reset but because of the way I've set up these things where each of the individual seeds are doing lookups instead of just having a hard-coded ID value it still works and we can Reed our database no matter what and and it all connects [Music] together now let's see what it looks like to actually query the database and for this I am using a library called typed RPC so I can have a a very simple setup for a server and client so again super simple literally all I have is a little Express API that has this service here the service just exports a bunch of methods where I can actually query the database and then the client side code just gets a nice little typed client based on that API where I can call those methods and then get back like typed data from those methods so let's take a look the first query here is just getting all the categories from the database so this is using the more omm like syntax so whenever you uh import your database connection you're going to have access to this query thing and then you have access to all of the tables that you set up on your schema and so you can access any one of these and then there are two methods there's find mini and find first and so the omm syntax is only for querying the database there is no orm Syntax for actually inserting into the database uh but it works just like this so uh query table name find mini that's going to return everything from the database so if I run this codes I can do a uh Dev for my API that will start up my API server and then if I want to run the client I can do an mpm run Dev client that'll just run it and then spin back down so that queried all of the categories from the database now to see this same thing with the uh querybuilder syntax we can see that we do a db. select and so db. select will specifically do a a select star from table you can also pass pass in like column names but in this case we just pass in the table name and so here you just do db. select from and then pass in the specific table and you can see that I'm importing that table from my schema so so it's the the same typed exported value and then we just can query from it like that and so in the client side codes uh it'll look the same but we just say get category select and the data that we get back is going to be exactly the same but it's just using that query Builder syntax instead now let's take a look at related queries and so in in this case I want to get a list of all restaurants but I also want to include the state information and the City information because a restaurant has a city ID but we want to actually have the city name so we need to query that table and then a city has a state ID but we actually want that state name so we're going to query that table as well so because I've set up these relationships we can query it in this way so we can say select from the restaurant table but include the city's relations and then also include the states relations so let's take a look at what this looks like so when I run this query it's going to give me back all of the restaurants but then you can see that they have a city property and then that has the City information and then from there that has a state property with all of the state information as well now to set this up you do need to set up relations so let's take a look at that if you look in our schema first of all on the restaurant table you can see that I have restaurant relations that I'm exporting as well and I set up City and then also menu items which we'll talk about later and also orders now this city relationship is a a onetoone relationship so a restaurant has one city and you can see that I I do need to be explicit here to specify well uh what's the column that it's coming from in this case it is the city ID column and then what table does it reference in this case it references the ID column in in the in the city table so that makes it so that we can query to get the city information whenever we're specifically querying a restaurant but if we want to get the state information that happens inside of the city table so if we look on city city has a relation to the state table and uh it uh references one state similar idea here and so because I've set up those two relations and I've exported them so if you look at my main schema export I'm also exporting the relations so that way when the schema gets loaded in all of those relations get added in as well which allows us to query in this way um and so I'll actually show you this if we comment out those specific relations queries you're going to get an error from drizzle that says it doesn't have enough information so if we remove the city column uh then drizzle no longer knows about that relationship now it still technically has the city ID column but we we do need to be explicit about how that relationship appears and you can see the moment I've done this we actually get a type error and so now whenever we're trying to find here it's it's saying uh may only specify known properties and so because we haven't set up that relation it doesn't know about querying that that nested relation there so these relations absolutely need to be set up if you are doing this om like syntax now the other thing I want to show you is the actual SQL query that gets generated when we run this and so I showed this earlier but because we set logger to true in our drizzle instance you can see that it actually spits out the SQL query and the beautiful thing is this is a single query to do all of those nested relations so you can see here that drizzle has actually created a nice single succinct query to get all of that related data so we're getting all of the the restaurant columns but then we're doing a left joint and then because we're using postgress it actually can do a a Json build uh statement so this is actually specific to postgress and that's how it's getting those those nested properties so honestly I think this is a great way to even learn about SQL if you look at the the actual queries that were executed against the database you can you can learn a lot about how to query in this way now let's look at an example of a wear Clause so in this example I want to get a restaurant from my database with a specific restaurant ID and so I have a wear clause and I want to look at that specific ID so I showed a few examples earlier when we were looking at the seeds but this equal function here comes from jizel omm we basically say on the left- hand side what is the column we're looking at and so we specify the schema. column name and then the value that we're looking for and so that's going to set up the correct wear claw under the hood and then again we have uh some some related queries here to to pull in all of the related data so let's say I want to grab the restaurant with id7 I'll run that codes here spits it out uh but you'll notice because of that uh related query we're actually including menu items but then we have the restaurant info here now let's take a look at this same query but instead of using the RM syntax we're going to use the query Builder syntax with just left joins right right so in this case we're going to select from the restaurant table we have the exact same uh wear clause and so that's the nice thing about both the omm syntax and the query Builder syntax is the way that you specify wees are are almost exactly the same and then we can do a left join so because I want to grab all the menu items for this specific restaurant we do a left join where the menu items restaurant ID is equal to the specific restaurant ID that gets returned and similarly we want to return the category of these items so we're going to return where the category ID of the menu item is equal to that specific category ID and so if we run this query instead we get back the data but you'll notice it's in a slightly different format and um really this is just the nature of querying with joins against your database because when you do a left join uh and also like a left joint on multiple tables it returns a row with duplicate data for each item in that result so because we queried this way we actually have a duplicate restaurant for every single uh result that came back but we have not only the restaurant then we have the menu item and then we have the category for that specific menu item and so when you're doing left joins uh you get back all the data you wanted but you are getting back some duplicate data as well which is why those uh omm queries are are are much nicer but if you look at the SQL that was output from this uh it's fairly straightforward because it's literally just like left join stuffs and so in this case we're just selecting a bunch of columns doing two left joins and then we're good to go so be because we're not doing any sort of like Json aggregation or anything like that um the the SQL query is is fairly straightforward but again you are going to have some some duplicate data in the output that you'd have to iterate over so in this example we're grabbing a restaurant by ID but we're only including menu items that match a specific category ID so you would potentially need to look up the category IDs and then pass in like the ID for like desserts or something like that uh but we can get more complex than that so this query says uh give me the restaurant with this ID but only show me the menu items with this specific category name and so because of this we have to do a lookup to find the category ID but we can do that within a nested statement so right here we say grab the menu items where the category ID is equal to the ID that is returned by looking up that category name so this is a nice uh nested select statement that actually gets compared here and this whole thing gets run as a a single SQL statement now another interesting query we could do is let's say I want to find all customers that have ordered from a specific restaurant so here's what I do so I want to find all users where their ID matches the ID from a specific order so we're quing on the order table where that order belongs to a specific restaurant the one that we're looking for from there we're going to select the user ID and then uh any user that matches we're going to return them uh and from there we're going to get back also the orders that correspond to that user so basically uh in a single statement we have all the users in our database that have ordered from a specific restaurant and we have the order that they made at that restaurant and then the last really complex one I have here is I want to know all menu items in a given City that are of a specific type so like give me all the dessert items in Denver or all the dinner items in New York and so this one gets fairly complex first of all give me all menu items where the category ID matches the category name that we're looking for right so I have this nested SQL statement here we're looking up the category ID we want to match against that so this will be like the ID for dinner or the ID for dessert or something like that okay so there's that uh we're combining this with and so match the specific category ID and we want to find a restaurant that exists in this specific City so we'll start in this nested query here so we're trying to find the uh City ID based on the specific city name that got passed in so that's going to give us the city ID then we're finding all restaurants that exist within that City getting back their ID and then we're saying make sure that the restaurant ID of the menu item matches against that um and then we're including all the nested data there so this is a fairly complex query with like deeply nested uh sequel statements as well uh but let's actually just run this one because it it generates some a single sequel statement for all of this even with all of the embedded stuff so here I'm saying give me all desserts that exist in New York uh and let's run the codes and it's done so you can see we have a chocolate brownie from the Central Park Cafe it's in that desserts category we're also including like the city information and I think also the State information if we look at the generated query it's a bit of a beast but it is a single query which is fascinating so let's actually take a look at this as well um but again honestly like to me this is like a learning opportunity like what are these queries that drizzle is is generating and they're actually like extremely readable too right you have this like Json build array you have these nested select statements so honestly this was fun for me to even just see the very readable sequel queries that drizzle drizzle [Music] generates now that's all I have to show you but I highly recommend take a look at the code dissect it learn from it and then also let me know if there are issues with it or things that you would change about it um but yeah like I said this code is open source you can see how I did everything how I did the seeds the migrations how I set up the schema and uh if you have any questions let me know so throw it down in the comments and also you could open an issue on this GitHub repo that's linked in case you have a question or you find it issue uh I do want to mention there's a lot more you can do with Drizzle as well so they have a whole section on performance uh you can also do like prepared statements and they also have transactions there's more advanced stuff you can do as well and there's also a built-in drizzle Zod Library so this is something I didn't get to demonstrate but uh for any of your tables they have a way of generating a Zod schema for both inserting and selecting and so this is something that's so common that you do when you're dealing with a database you have a specific uh data access layer and and data access object for like querying the database because when you the data you get back from a database for instance is going to have an ID on it so that's one specific schema but when you're inserting into the database you typically don't insert with an ID so that's a a completely separate schema uh but this is just built into drizzle odod so you can actually generate these schemas Define them in the same file and now you have all of your types that have a single source of Truth like all of your your types for querying and inserting and the schema for the table itself just has that single source of truth right there which is is also a beautiful thing all right that's it for this video hope you enjoyed it hope you learned something from it and I'll see you in the next one [Music]

Channel: Syntax

Views: 15,382

Keywords: tutorials, cms, html, css, html5, css3, education, free, lessons, tuts, tutorial, learn, software, web development, web developer, developer, web, website, drizzle, orm, javascript orm, typescript orm, object relational mapper, how to drizzle, how to seed drizzle, how to migrate drizzle, how to add relations drizzle, database, databases, postgres, project setup, syntax-related-721

Id: vLze97zZKsU

Length: 45min 54sec (2754 seconds)

Published: Sat May 04 2024