Complex Schema Design with Drizzle ORM | Common Patterns

In this video CJ shows how he used drizzle to implement a complex DB structure to represent a food delivery service called bytedash. He shows how to setup the project, how to create schemas, how to seed the DB with related data and how to query the DB with deeply nested relationships.
View the DB diagram here: dbdocs.io/w3cj/bytedash?schem...
View the code here: github.com/w3cj/bytedash
Read the drizzle docs: orm.drizzle.team/
Listen to Wes and Scott talk about how they use Drizzle in their apps: syntax.fm/show/721/you-should...
00:00 Intro
03:30 Drizzle: A Different Kind of ORM
04:37 Codebase Intro / Setup
07:51 Database Structure High Level Overview
09:20 Drizzle Studio Setup
12:06 Creating the Schema with Drizzle
14:10 Drizzle Foreign Key Constraints
14:45 Drizzle Composite Key Constraints
15:19 Drizzle Index Constraints
16:38 Drizzle Migration Setup
23:18 Seeding the Database with Drizzle
32:38 Querying the Database with Drizzle
34:45 Related Queries with Drizzle
35:39 Creating Table Relations
37:18 Drizzle Generated SQL Queries
38:51 Drizzle Join Query
40:34 Nested Where Queries with Drizzle
44:00 Final Thoughts on Drizzle
------------------------------------------------------------------------------
Hit us up on Socials!
www.syntax.fm/links
Brought to you by Sentry - Use code "tastytreats" to get 2 months free - sentry.io/syntax
#database #typescript #webdevelopment

[@syntaxfm]

03:30 Drizzle: A Different Kind of ORM 04:37 Codebase Intro / Setup 07:51 Database Structure High Level Overview 09:20 Drizzle Studio Setup 12:06 Creating the Schema with Drizzle 14:10 Drizzle Foreign Key Constraints 14:45 Drizzle Composite Key Constraints 15:19 Drizzle Index Constraints 16:38 Drizzle Migration Setup 23:18 Seeding the Database with Drizzle 32:38 Querying the Database with Drizzle 34:45 Related Queries with Drizzle 35:39 Creating Table Relations 37:18 Drizzle Generated SQL Queries 38:51 Drizzle Join Query 40:34 Nested Where Queries with Drizzle 44:00 Final Thoughts on Drizzle Listen to Wes and Scott talk about how they use Drizzle in their apps: syntax.fm/show/721/you-should-learn-drizzle-the-typescript-sql-orm

[@Plaswin]

Couldn't follow him before in streams because I get distracted by that format. But this edited presentation are absolutely awesome

[@ofeenee]

I could not agree more!

[@Anbaraen]

Stream vids felt second-best (this is pretty much always the way with stream vods unless you use Theo or CJs old approach of heavily editing them down). This is perfect.

[@czhablog9559]

Bro this video is pure gold! Thanks for sharing your amazing technical knowledge to the world.

[@NicholasMaietta]

I'm totally stealing the use of the Zod for getting the env vars checked before doing anything else. This actually would have been nice a few days ago when I couldn't figure out why my DB connections were not working in production. I wasn't getting any errors in logs! This is such a great idea.

[@iukeay]

Legit, I never have never seen that done until this video! Clever. Clever

[@ddrweb_]

I started learning SQL, feels like this is exactly what I will need once fully understand what I am doing. Nice, clean patterns btw. Thanks for this you did an amazing job! This new role at syntax really suits you, everything you do has so much value to it, keep being amazing.

[@IsakFilms]

Great video! I've been learning Drizzle/SQL the past months and this was a great refresher. It really helped me understand relationships better

[@Stoney_Eagle]

Nice job! I have watched many videos to make sense of the chaos in de docs and all of them combined missed a lot of information that I needed and is shown here. I wish I had this video when I started using it.

[@MrAbuYT]

Awesome! Thank you for this very helpful explanation. Good job CJ.

[@joelkuijper]

This is awesome, thanks for the hard work your putting into these videos CJ!

[@paulclarke4099]

Your videos are fantastic and always so well explained, thanks so much CJ 💯😃👍

[@talensjr]

Yet another one of this amazing tutorials!! Keep this going, super helpful from a fullstack developer point of view. Thanks! 🙏

[@kuldar]

Absolutely love this new content from CJ. Thank you for making Syntax even better!

[@chill-hot-stream]

cj never stop releasing videos .Your coolify video from last week helped me manage my server effectively.Was using normal terminal but now everything is simple and easy to setup effectively

[@jbphilippi]

I agree and hope he will do more coolify videos!

[@VincentFulco]

This was an outstanding tutorial. Will watch a few times. Thanks!

[@vitalikda]

Omg! It's pure gold! Thank you

[@prashlovessamosa]

Great explanation CJ thanks Please keep uploading good stuff.

[@truthzp]

Thanks for the brilliant content! It's exactly what I need now.

[@zhanezar]

another great video thanks i really liked the idea of using Zod for Environment variables instead of adding another libray , thanks CJ

[@pavank4466]

Great video, Thank You 👏

[@petermckeever2360]

Well this was incredibly helpful

[@adamcollier1907]

Great video as always CJ! I know you touched on migrations but I’d love to know how to handle migrations for prod, e.g you already have an app running and need to run a migration without breaking anything. Also worth mentioning Drizzles proxy driver, was great for my move away from Planetscale and still allowed me to use Vercel edge functions

[@Michael291288]

Amazing! Would love to see the same implementation done with Prisma 🤩

[@bashiryousufy1]

CJ is the best, been watching him on Coding Garden

[@ArionKosturi]

Thank you CJ

[@joaquimley]

Cj you are amazing ☘️

[@brennenrocks]

How do you run migrations on your Production DB? When in a production environment typically you won't have your devDependencies which means drizzle-kit won't be there which then means the migration script will not run because the defineConfig is coming from drizzle-kit

[@yiannis_p]

Amazing video as usual! One thing I would also add here is that drizzle offers a db:push functionality so you don’t have to deal with any migrations! Also the foreign keys are optional, as you can still use their orm style query builder without foreign keys just using the relationships you defined in the schema! Works really well in an environment like planetscale which only has online ddl and has no foreign keys!

[@haziqali6024]

great video! I have just started to look into drizzle to integrate with expo-sqlite. I had a question: Is there any way to generate schema.ts file automatically from an existing sqlitedb which already has sql schema defined?

[@cpakken]

What is the vscode theme you're using?

[@paoloricciuti]

I think i actually did the same error when i used Drizzle with sqlite but from what I've researched closing the db connection is not handled by drizzle so with PG you can use the Pool to use a connection pool but you have to find ways to do the same with other drivers otherwise you will either clog up or all your connections will pile up. This is obviously less of a problem with serverless but i think is worth mentioning.

[@w3mw]

Great video as always CJ! I’m building a project right now with nest js and prisma as backend and I thinking of next js with drizzle in front end is that possible tough or should I use the same ORM in both ways? Never tried drizzle before so I’m not sure if it works good with nest js but I guess it does 😄 what do you recommend?

[@syntaxfm]

Drizzle is just typescript, so it will work just fine with nest js. It is possible to use 2 different DB libraries to connect to the DB, but if you can consolidate to a single one, that would be ideal. If you are working in a mono repo, you could also have a library specifically for the db / queries and share this between projects. -CJ

[@officialdanieldsouza]

Hey awsome video, this video really clarified organization of my files in drizzle. I was wondering what is the best field type to use for the primary key, I know in your example you have used serial but I was just wondering in terms of security, performance, running out of integers. Would love to hear your thoughts on this dude!

[@syntaxfm]

There's always a trade off in performance / usability. string ids (guids) are good for large distributed systems where you might be inserting many many rows all at once across several nodes. guids can also be generated outside of the DB, so you could generate them before insert. auto incrementing integer keys might be vulnerable to enumeration attacks if your application code does not protect against it. e.g. someone might try requesting /users/1, /users/2, /users/3 etc. but this is only vulnerable if you have not locked down the API. postgres serial / int maxes out at 2,147,483,647 but if you are worried about running out, you could use bigint / bigserial which maxes out at 9,223,372,036,854,775,807 I have a lot of apps still in production that used int / serial keys, but those apps only have a few thousand users. Worrying about running out of primary keys / insertion performance is likely only needed if your going to be working on things at a much bigger scale. -CJ

[@ayushgogna9732]

18:07 actually in the version @0.21.0 there is a way to handle migrations in drizzle without a migration file with "drizzle-kit migrate" No more push:pg, generate:mysql. Just use push, generate and specify dialect in config once!

[@syntaxfm]

I have updated the repo with a note for anyone that comes across this: github.com/w3cj/bytedash/#drizzle-kit-updates

[@russpalermo]

cool CJ. On seeding the db have you thought about or tried running the seeding as a single transaction in postgres? I think I did this in a prior project using knex where I set the table fk contraints to 'deferred' the ran a promise.all on the seed files where the commit did not happen until all data was stuffed in the tables.

[@syntaxfm]

Yes this should be easy enough with drizzle, just wrap the insert code into a db.transaction call: orm.drizzle.team/docs/transactions

[@brennenrocks]

What are your thoughts on using UUIDs for primary keys instead of `serial` in Postgres?

[@danfascia]

Just add another column for uuid as well as id(int) because indexing uuids is expensive

[@brennenrocks]

@@danfascia what's the point of having the uuid if you aren't going to do lookups on it or index it?

[@danfascia]

@@brennenrocks not what I said. Use integer as your primary key index and use that to carry out internal relationships as indexing integers in this context across relationships faster. No issue having a uuid index for direct item lookups

[@premdasv8]

What's the font you are using in your vscode?

[@syntaxfm]

It's called anonymous pro - www.marksimonson.com/fonts/view/anonymous-pro

[@dummy_code]

Does it support raw sql queries?

[@syntaxfm]

Yes and it parameterizes them by default: orm.drizzle.team/docs/goodies#raw-sql-queries-execution If you don't want parameterization you can use sql.raw

[@sasquatch_devs]

Would love your thoughts on Lucide ORM by the creators of AdonisJS!

[@syntaxfm]

I'm not sure how I feel about the lack of type safety 🤔 The author talks about it here: github.com/thetutlage/meta/discussions/8 I think I would rather have a single source of truth with drizzle for the table schema / inferred insert / select types, inferred validators etc. than worry about a query not having a typescript error when it should. I haven't run into the type issues during querying the author brings up in that post. With lucid you need to define your models separately from creating the tables. Migrating needs to be done manually for every column / change and then you need to update the model to match. This is very similar to my experience with sequelize. With drizzle, you update the schema type and then the migrations are auto generated from the schema change and the inferred types continue to flow through. Seems like a trade of better type safety during querying vs better type safety / single source of truth from model definitions. In my experience with other libraries, keeping the models in the code base up to date with the table schema is one of the biggest pain points, not querying. -CJ

[@polioann]

How about recursive schemas? (like the comment section)

[@syntaxfm]

Self referencing keys is possible and set up in the same way: orm.drizzle.team/docs/rqb#one-to-one

[@polioann]

@@syntaxfm Nice! Thx

[@user-tb4ig7qh9b]

For me drizzle sucks and prisma sucks sorry for that i prefer kysley

[@syntaxfm]

What do you like more about Kysley? Also what makes you say Drizzle and Prisma sucks, in my experience they are both great options

[@user-tb4ig7qh9b]

@@syntaxfm i worked with prisma and drizzle before but for very easy jobs but sometimes you pull data from alot of table and do aggregation and window function and lots of stuff okay sometimes we make a view on database but when i write new feature i need a tool to provide me most of things and drizzle already lack alot of sql features and prisma have the same problems i work on my job with ecto it is lib written in elixir and man working with so much easier. I used alot of orm like prisma awesome until you start making joins and take data from alot of tables and make aggregation you just lost the way for prisma. Drizzle just low level implementation of sql if we have versions for sql i will give drizzle like 0.1 I sometimes even work with laravel and for meduim things it is okay but after that you will write sql in your hand. so i think the only true orm i used until ecto and linq give you all the power it make the write mapping for objects and alot of stuff. Why kysley i was have a project it is scrapper and i need to feed this data to db so i tried prisma and drizzle does not fit but when i tried kysley and make inspect db i just get the completion type safety and do not need to deal with writing my geolocation types i think that a joke you have orm that does not support goelocation 😦