You should learn Drizzle, the TypeScript SQL ORM

You're listening to Syntax, the podcast with the tastiest web development treats out there. Strap yourself in and get ready. Here is Scott Tolinski and Wes Bos.

Welcome to Syntax, the podcast with the tastiest web development treats out there. Today, we've got a show for you on Two things.

Drizzle and databases.

So Drizzle is an ORM that allows you to interact and and have queries and insert data Two different databases.

So we we thought we would explain what Drizzle is and and how it works alongside, Explaining a bunch of stuff about MySQL, Postgres, and SQLite. So those are the 3 databases that rid Drizzle supports and being able to explain their features in context of Drizzle, we thought, would be super helpful. So rid. Buckle up for a database filled episode.

Yeah. Databases.

If you're working in databases and ORMs, rid Chances are you're gonna mess up your code. You're gonna have some slow queries.

You know, one of the my, favorite new features on on Sentry is rid The, the query performance tracker.

You know, when we were building our syntax site, Some of our queries ended up being a little bit slow, and I don't know if I would have noticed that I was using the wrong ORM method to query, when you're trying to find 1, I wasn't doing I I believe it was find unique. I think I was doing find first instead of find unique or something.

And because of that, we had a bunch of slow queries, and I was able to see that because of Century's slow query monitoring. So if you sign up for Century at rid century.i o. That's Wes e n t r y.i o. Use the coupon code tasty treat. You get 2 months for free.

And then the show is presented by Century. So Let's get into, 1st and foremost, Drizzle.

Just a little bit of what is Drizzle.

Drizzle JS an ORM. Basically, an ORM is a tool that makes typically working with databases easier.

It's a a way that you can rid Map your database to some code. That way, you can query off of your database and have it be just a a nicer way of working in it. Sometimes they give you types. Sometimes they give you, rid validation. Sometimes they give you just things that just straight up working raw with the database want. And so Drizzle is a newer ORM for JavaScript.

I've used plenty of ORMs myself in in all different context, rid. And Node Drizzle is certainly a little bit more of a a modern outlook on the ORM, and the ORM itself prides itself in being a little bit more SQL like, as in you can write chain methods that are very SQL like. However, there is a new query syntax, which is a little bit more ORM like, both of which are really nice.

Overall, the library is a joy to work in, so it's a you know, no no surprise that people are really liking Drizzle. Rid There's also something called DrizzleKit, which gives you more control over the ORM and your database. So Drizzle Kit can do things like, running migrations for you. There's a config file where you can connect to your database.

Rid. And those types of things just become a little bit easier, a little bit more handholdy, which JS somebody like me, I'm not, you know, Not typically a database person.

Yeah. Drizzle kit is something that I really, really wanted within Drizzle, and that was one of the reasons I hadn't tried it yet because it's like, I don't wanna be writing that stuff myself. I don't feel like doing all of those things. Therefore, Drizzle Kit is, once that kind of dropped, it definitely opened up Drizzle a little bit for me, whether that is just working with your database itself more via migrations or

the migration stuff is super cool. We're going to talk about of migrations are in just a second. But being able to see the progress of how things changed over time and rid. Programmatically apply them is super handy, as well as the ability to pull schemas down from an existing database. So if you have somebody on your team that has been working. They just you just have a database. You don't have the schema for it. It will, like, backwards pull it down, which is cool.

Rid. We'll talk a little bit more about that in a sec. We're getting ahead of ourselves, but I thought, oh, that is super nifty. And then they have Drizzle Studio as well, which is I always want this.

Just a UI for visualizing all of your data.

Usually when You work with your data.

You'll have to go and grab a database client.

So what are the popular database clients out there? SQL Pro JS one Tables plus. Table plus is is another one. Table plus is the one that I've been using a lot lately.

MongoDB Compass is another really nice one. And Sometimes, especially Wes you're first 3 t. Oh, yeah. That's a that's a big one.

PHP my admin is probably the the OG one doji. And you just want some sort of UI for being able to straight up edit your data. It's the original admin interface.

Node a lot of the ORMs, rid.

Prisma has this and Drizzle has this. A couple of other ones have it.

They will give you just a table view of all of your data. You can filter, you can Scott really quickly.

And it's really nice to have that when you haven't totally built out a full admin UI.

Sometimes you just gotta double click a row and type in the actual data.

Totally. I really greatly prefer having something like this. It's it's funny, Wes. I don't know about you, but for a long time, I just thought Pnpm MyAdmin, like, was MySQL. Like, I in my brain, because they were always so connected when I was working on whether it was Drupal or WordPress, it was like, alright. Now you have rid Now you're into PHPMyAdmin.

I I Wes just, like, really connected those 2 software to be, like, the exact same thing. Oh, I'm working in MySQL now. Kidding.

Rid. Yeah. It's true because you can you can use it for everything. And and back in the WordPress days, I would use that quite a bit, to take database dumps to be able to change. You could change a password in there because they just empty 5 the string in there. It it was pretty cool product. It's funny that it's still very much the same. It's been updated slightly, but it's been around for absolutely forever.

Yeah. I wonder what it would look like with a Drizzle Studio Node of paint. You know? Because Drizzle Studio, one of the things that it has going forward is the UI is super nice.

And I I you Node, rid That that shouldn't go overlooked.

Anytime you're working in any of this stuff, a really nice UI goes a long way. Mhmm. There's also a product called Benchmarks, which I I haven't really looked at Benchmarks. It's a newer thing. It's actually listed in their nav as new, But it's a way of really determining what's slow and fast. Is it your query? Where where's any sort of slowdown you're having? Rid. It's interesting. I would wanna dive more into benchmarks. So we're probably not gonna be covering that too much in this, but it exists. So it's important to know. Yeah. I'm curious

How this works because JS it like a proxy for all of your queries? I haven't done it because generally I lean on using a database hosting provider. Usually Wes I pick somewhere to host my database, I'm not just spinning up a Linux server and launching MySQL on there. I'm going to go for some sort of pass platform as a service, And that's going to allow you And why are you doing Yeah. And they're going to email No. No. Sorry. I'm asking you I'm asking you why why would you do that? Because they provide a lot of, like databases JS scary, especially for people like us who are just Node day we're writing buttons and putting rectangles on a page, and the next day we're We're querying databases and backing up, and databases are extremely complex, and tools like Drizzle and Prisma make Working with databases so much more accessible.

However, there's still a lot that can go very wrong. And probably the biggest thing that can go wrong is that you can have Slow database queries, right? And something like my MongoDB does this, MyPlanetScale does this, JS they'll email you when there is A extremely slow query or something that has run 10,000 times and is extremely slow and say, hey, maybe it's worth putting an index on this field because rid. You've been filtering by this field

quite often. Word. Yeah. I for me, database is as scary as you mentioned, so, rid I don't want anybody to get ESLint my database. That that's just straight up you know, any sort of rid Anything I can do to mitigate potential data leak or anything like that, I'm gonna be as cautious as you can possibly be. So rid. That's just that's where I'm at there. Let's talk about the different types of databases,

specifically the ones that Drizzle support is Postgres, MySQL and SQLite. So Postgres and MySQL are probably the 2 big databases. They've been around forever. They're sort of battle tested.

They've fallen in and out of love over the Yarn.

And it seems like right now everybody is in love with Postgres and MySQL, especially because we're starting to see a lot of services built on top of them. So Postgres is a relational database And that is the database behind things like Neon, which is a service Supabase.

Supabase does a lot more than just a database, but their database part is rid. Postgres. Vercel Postgres is I call this the Trader Joe's Neon database because Vercel just Partners with Neon to resell their database. That's the same thing for the,

Upstash, which is what we used for the syntax Oh, yeah. Right? And Cloudstash.

The Trader Joe's KV key value store.

Yeah.

Maybe we should can you explain Trader Joe's for the The Brits and Australians and, I guess, the Canadians.

Yeah. Well, I I don't know what you necessarily mean by Trader Joe's here. Trader Joe's is is just a grocery store.

It's like a it's it's owned by Aldi's, I believe.

And it's, I don't know. They they kind of it JS kind of, like, expensive, oddly packaged, unique items.

Yeah. And so all of their items Yarn, like, white labeled or relabeled, meaning that it's well known that A lot of Trader Joe's stuff is built by or sorry, not built JS created by Kellogg's or like their cereal is probably made in the same factory as a lot of the big brands of cereal, but they'll just like rebranded as like Trader Joe's, You know, when they're they're just Cheerios in the same box at the end of the day, you know, upcharged a little bit. Yeah, exactly.

We don't have Trader Joe's in Canada, so a little sad, but we always go there. We always cross the border. You you I tend to not go to Trader Joe's very much because there's a whole it's going down in The Trader Joe's parking lot YouTube video about, like, how crazy the Trader Joe's parking lots Yarn, and it's it's seriously like That's the one of the most accurate videos of all time. Whoever designed the Trader Joe's parking lot said, how can we make this as frightening as possible? We're gonna put in, like, Ten parking spaces, and you can all just, like, fight over them. You it's so accurate. And it was, like, the big reason why it's It's like, alright. If we go to Trader Joe's, you have to calculate out the time where nobody's going to be there because it's

insane. We went to the Trader Joe's in Brooklyn once.

And it was wild. Like it was like wrapped around the entire store. The line was almost going out the door just to pay.

Yeah, people people working there are just like, next, next, like, just champs. Anyways. Postgres. What else? Zeta uses Postgres. Railway is another popular host. They have Postgres.

And then we also had The folks from Payload CMS on go back and listen to Node 700 with the folks from Payload. They use Postgres and results, so both of those things.

But next up, we have the MySQL database. MySQL has been around forever. I don't think we need to explain a whole lot about What that JS.

The big name in MySQL right now for hosted is PlanetScale. That's what we're using on the Syntax website rid. Via Prisma ORM, and I'm a big fan of it. It allows you to, like, fork databases, which is kinda cool. So, like, If Scott wants to work on a feature, he can just fork the database and he gets all the, like, fresh data, rather than, like, Some old data. So big fan of PlanetScale. It's kind of interesting if you search like PlanetScale Vercel or PlanetScale alternatives, You often find people recommending Postgres, which is like people think PlanetScale has done such a good job that They think that Sanity scale is tech, which it kind of is because they do connection pooling and a lot of that stuff. But rid. People use the word PlanetScale as if it were another database, even though it hits MySQL.

Yeah. In in in that same regard, We mentioned Neon. Neon and PlanetScale are kind of analogous. Right? Like, Neon is to, Postgres as, rid PlanetScale is to MySQL. So they're they're very similar Wes they're both do connection pulling that type of thing. So if you do want that type of database, You wanna do Postgres? Go NEON. You wanna do MySQL? Go PlanetScale.

And, both have fairly generous rid Plans too. You Node? I'm I'm rocking a free plan for my NeonDB that I'm using right now. And, hey, I got A decent amount of people using this thing, and I'm not even close to approaching the the free limit. Now granted, it's not

that's not a a scalable thing to use the free plan. But, hey, for a little side project. It's not bad, right? Totally. That that's the hope with a lot of these things is you just get your side project on and up and running. And then before you know it. You're you're $100 in a month on these different services, but it's it's well worth it. A lot of people also have recommended Just using 1 database and then using like database prefixes, we talked about this on a past show, is just pay for 1 database and then you can use prefixes rid. To host multiple apps on it. Still makes me a little bit uneasy, but certainly something you could do.

Yeah. Totally.

There's also SQLite, With Sanity, you know, I I think it gets overlooked a Scott, but SQLite, I think, is used very frequently in native app development, rid to store data on a, you know, on an iPhone or an Android device or something like that. But it's basically just a straight up it's a file on your computer. It's a local Database.

And it's super useful to just get going because it's SQL.

I mean, you can Pin up a SQLite DB in no time, not pay anything if you're just hacking away on something, and that's it. You you Scott you got yourself a the database cooking, and you don't have to be running a, like a what's the, what's the Node database service that you use, Wes, To to get up and running, like, with my secret

DB and Node. DB and gin.

What's that? D b begin. D b engine. Oh, d b engine. I thought it was I always thought it was d begin, but it is d b engine. Yes. D b engine. And so d b engine is A little tool that you can run on your computer. And then what that will allow you to do is to start and stop your database servers so you don't have to remember the, like, rid. Crazy CLI Yeah. No thanks.

Arguments that you need to pass, and and you have to sudo it. And it also You don't have to run Warp, ZAMP, or MAMP. Rid. Yeah. Big fan. Big fan of that to be able to to run it locally. But SQLite is amazing because it's just a file on your computer. You don't need a server.

You're just up and running and it's amazing.

On the SQLite website, they say there are over a couple 1,000,000,000,000 Vercel light databases in the world. And that's because there's sequel light databases probably in your thermostat. There's certainly lots of them rid on your phone. I was looking into how the Raycast extension for Imessage works. So there's an IMS extension that will allow you to do the two factor authentication codes that'll it'll just implement them into your browser. Rid.

And I was like, how do they do that? And it turns out that all of the Imessages on your computer, like the Imessage app on your desktop JS just a SQLite file and you can open it up and read it. I thought it would be encrypted, but It's not. And then also, like, your Ifotos has a SQLite database. And inside of the SQLite database is all the information about I once saw somebody crack it open, and it will tell you who it thinks is in each photo.

What is in like, if you search dog on Imessage, it will Bring up photos of actual dogs. So all of that metadata about your photos needs to be stored somewhere. Right? And You can't store that in ESLint because that would if you have 10,000 files, you would have to parse every single file before you execute the search. So Apple just sticks all that metadata in a SQLite database on your device, which I thought was really cool. Rid Yeah.

Yeah. And and SQLite can be hosted if you want.

Why why would you pick a a SQLite host, Wes? What would be a reason for that? Yeah, it's

extremely simple to use.

It does serverless super Wes.

And there's lots of really great hosting options out there as well. So Cloudflare D1 is SQLite API so you can use Drizzle with Cloudflare D1.

It's extremely cheap, extremely fast to be able to do these types of things. I'm sure there's some pros and cons to the differences between the 2, but Cloudflare uses it for a lot of their own stuff, so I would feel pretty confident in that type of thing.

Fly IO uses as well. Bun. So BUN, which is another JavaScript runtime. We've had Jared on the show in the past.

He just teased the fact that you can import. So BUN also comes with a SQLite adapter built right in. And I love that because you can just get up and running with SQLite very, very quickly.

But they also now teased being able to import Wes SQLite database in your JavaScript files with JavaScript import assertions. So import assertions is something new to JavaScript Wes You import a file and you can say as text, as JSON, and they're building an import assertion for a sequel light. So you can see as sequel light and then it'll just return the whole database connection.

Big fan. Yeah.

Yeah. You know what? I, I don't know how I feel about that, but I'm sure. You know? One thing about BUND is that's interesting is is they do a lot of things that are very, like, user focused in terms of, like, stuff you might want to do.

Rid. But I do have concerns about, man, like, the amount of stuff they're adding to that. Yeah.

On one hand, there's standards, rid. And on the other hand, there's the Node stuff that's added, but they're doing it in. It's not like a like a Vite, you know, like Vite does a lot of non standard stuff. You just import a CSS file and boom, you have the CSS, right? Like, that's not a JavaScript thing, But Bun is doing it in a way that is using import assertion. So theoretically, you could write a loader that handles that some maybe, like, maybe that's the, the end game there.

For some reason, it feels weird to me rid Mhmm.

As a runtime and not as a library. I don't know. A bundler.

You Node, I don't know why that feels weird to me. I I I like the niceties. I should say Alright. Totally. Let's get into the next part, which is, you know, defining your data structures.

And in Drizzle, Just like many ORMs, one of the ways that you do that is through what's called a schema and or or as Wes would say, a schema. Schema. Yeah.

Rid And a schema is basically a an outline of how your data is, rid laid out within the database itself.

This includes data types, property names, And in Drizzle's case, column names, you you have those as kind of, like, separate concepts, where sometimes it's like the column name is always going to be directly matched of the property name. In Drizzle, you have Wes, specify the property name and the column name itself when you declare.

And that's cool.

But This is a a basic way that you can, again, define the structure of your your database overall as a whole, and that includes things like relations, default values, things that are non null, primary keys for things that need to be indexed or indexes in general, relations themselves, And maybe perhaps, like, the length of field and options for various fields. In some other ORMs, this is done through, domain specific languages. So DSLs. We DSLs. Yes. We use a, Prisma on the Syntax website, and that's rid Deno through a specific Prisma schema file, which is a dot Prisma file, and that's kind of its own syntax. Right? In GraphQL, you have a schema file. That's a GraphQL syntax.

In Drizzle, the schema is a straight up JavaScript Function. It's a function you do. In our case or my case, in my, Postgres drizzle, you do a PG table function.

You give it the table name, and then the 2nd parameter in that function is an object with all of the values in that table.

And that's typically that's it. You know? And and that's actually kind of one of the things that makes Drizzle really interesting is that everything is very JavaScript

first. Right? Very explicit.

Very explicit.

Yes. Yeah. I'm a big fan of doing the schemas Directly in JavaScript.

So the huge benefit to that is something we'll get into in just a sec, but it's rid. It's typed. It's fully typed. It's all in JavaScript. So the changes to your schema immediately ripple their way throughout your entire codebase. You still do need Drizzlekit to generate your schema because at the end of the day, Drizzle is generating rid SQL, right? It's generating the queries that need to be sent to your SQL database, and it's generating the a sequel files that will modify what your schema looks like and things like that is like, oh, I added a revealed to the podcast called Release Date, and that is the name of release date. It's required every single time. It has a default value if we want to of next Tuesday and

etcetera. Right. And I should say to interrupt there, You only need to use DrizzleKit to connect your schema if you're use if you're having Drizzle handle your migrations in your table creation.

Because you could do all of this by hand if you were sequentially enough, and that wouldn't be a problem. Because in your JavaScript code, You're just importing the schema itself because it is JavaScript. You're not importing anything generated necessarily. But if you do have Drizzle manager migrations and of stuff. That's when you want

that connection there. Yes. Yeah. That's a really good point.

Oh, one thing we didn't say is, like, Can we just quickly talk about the 2 things that we built,

using Drizzle and and what database we're using? Go you go ahead first. I think yours is more interesting. Okay. Cool. Yeah. Mine is more interesting, and it is open source, so you can check it out. I'll I'll have a link to that in the show notes. Basically, I'm working on a habit rid Tracker. I mentioned this in our productivity episode, and it's called the it's at habitpath.io if you wanna check it out. But it's basically like a simple habit tracker, and that's a really good project to build because it's more interesting than a to do list. Right? Because, rid. You have relations.

It it essentially is kind of to do list like. You have a thing you're adding to the database, and then you check If it it has existing and we have some relations there. And I also, have user accounts.

So if you wanna see how authentication, And I rolled my own authentication. If you wanna see how that's put together, you can check out the the repo for that. So I I do. I have several tables in mind I have. So a habit is a table. It has a, a relationship where 1 habit has many checks. A check has 1 habit.

Check is just, hey. On this day, you checked it off. There's also a user table.

A user table has, in relation to a session, there's a user sessions. There's also a wait ESLint for users.

In addition to that, there is, rid. Users have checks and habits, so there's also many to many relationships there. So if you wanna see how, relationships and

user accounts and those things are done. It's a pretty good example of that. So what I built was something to track where syntax is on the podcast charts. So we Wes want to be able to see where we are, especially in relation to other podcasts that are within the Scott of coding spectrum. This one. I kind of keep tabs on where we're at as we go up and down the charts and we pay for this thing called charitable and it works pretty good, but it doesn't give us rid All of the data that we wanted. So I was like, well, you know what? I love doing I love writing scrapers.

So I built a quick little scraper That, it scraped the Itunes top 250 and the Spotify top 50. That's all that's available for both of those.

And then usually when I'm building this type of stuff, I'm just like, Oh, Okay, I need to save this data. So like my go tos when I'm building something really quickly like that is A, JSON stringify it and throw it in a file.

Or B, I've used a couple different like FileDB Node adapters in the past, which will give you really quick schema Wes, being able to just add and remove it. The downside of that is you don't get rid like full typing.

So I was like, you know what? I'm going to use Drizzle for that this this time because you can get up and running with the Drizzle database, especially SQLite. You don't have to go set something up and Port forward all this silliness. You can just make a SQLite file on your Local file system and boom, you have a database and then probably in 15 lines of Node, you have your schema up and running and you have full blown query syntax for it. So we have scrapes, and then we have scrape items, which is basically every single podcast that is on that chart. And then each item has things like the rank it is, the name of it, thumbnail, etcetera. So pretty nice little example that I was able to just quickly throw stuff in the database and be able to query it.

I used and also I used Cloudflare Pnpm there, which was a little bit tricky because it's not like a standard.

It doesn't just give you a Vercel lite Connection string. It's a little bit more funky, but I was able to sort of work my way around it. Yeah. Totally. And and I use Neon, by the way, for mine. So if you wanna see Neon,

You can check out mine.

Cool. Let's talk about relational data. Relational data, I found to be pretty easy. I did have some, like I had some bang my head against the wall moments because I had a a string out of place.

You know, it was one of those things where I had oh, what did I have? I had In my habits relation, my relation was on Sanity. And I said that habits have many checks rather than checks have many habits. And it was Giving me some kind of obtuse error, and I was just like, what is wrong with this? I've used this over and over again. I've compared it to the docs. What's going on? And it's just like one of those things where you're just When you look at it visually, you're swapping those 2 words and, the worst kinda bug. Right? Because you you really think I was really convinced, that I had my code right. I I don't know why I was so convinced because I was getting an error, and I was following the Bos, but I was so convinced for some reason that it wasn't my fault. Of course, it's my fault.

Rid. That's one thing that I found that I really like with Prisma is Prisma has a I don't know if it's an ESLint plug in or or some sort of ESLint, and it will immediately tell you when you goof something up.

Rid. And I found that. And I only saw later that there also is a drizzle ESLint plug in. So maybe this does the same thing. But I found the same thing where I was just trying stuff and it didn't work or I spelled this thing wrong and I feel like maybe that could be improved a little bit or maybe I was not using the rid. ESLint plug in correctly or at all. Yeah. That's one thing with the domain specific language too. Right? You have to have that tooling for that domain specific language

to be effective.

In TypeScript, Wes. TypeScript will save you from all the things that it can, but there's some things it can't save you from. Right? Can't save you from the things that is, like, intentional. Rid. Right. I I did this intentionally. Yeah. It passes all the TypeScript checks, but it's still incorrect.

So, that's you can define many to many relations, one to 1 relations, one to many Node to 1 relations? Node to many relations? Any of the relation types you want, rid. You can define those inside of your schema.

And then what's nice about relations inside of Drizzle, which I found the docs to be kind of, rid Nondescriptive about this, which is kind of annoying.

When you're doing a query with something that has a valid relation, You really straight up just have to say with the thing. So for instance, if I have a habit that has checks, rid. Right? I can say, hey. Give me all of this user's habits.

And then in that, in that selection, in that query, all I have to say is With colon checks colon true. That's it. And it's gonna give me the checks. Now granted, If you want to limit that further, you just have to have a object instead of true Wes you have, Again, a query selection thing Let let me see if you want instead of the whole thing. You want. Yeah. Or perhaps a filtering of rid Based on, oh, I only want checks from, this date range. For me, that was the thing. So I could have a date range then as being that that parameter in there.

But for the most part, I found that syntax to be lovely. I found it to be really nice to work with.

The documentation for that, I found to be a little Wes lovely. I had to, like, rid. Guess on something else and figure it out. Yeah. Little sparse.

I wanna see more examples. Yeah. Yeah. I found it really helpful to just search Common Drizzle queries on GitHub just to see how other people are specifically doing it. Yes. Rid. That's the other thing. There's this website called Answer Overflow. Have you seen this starting to pop up? No.

On your Google searches? No, I have not because a lot of the Q and A has moved into Discord, and that kind of sucks because it's not globally available.

So I don't know who answer overflow is or what this company is, but search all of this.

Somebody and I've been finding a lot of Cloudflare and Drizzle Wes and answers on Answer Overflow because somebody had asked it in discord, and now they're surfacing it, which is genius.

I always find that really frustrating about Discord is that questions go to die or chat in general. Just questions there go there to die unless you explicitly go to the chat room and search for it. If Discord really wanted to step up their game,

here's what you do. You index rid All of the forum type posts, you make them publicly available there. Thank you. That would be great, because you're right. It is obnoxious to not be able to search that stuff, to find that stuff when you want it. I've been, one thing I did, You know, I liked a lot about Prisma was that chat g p t specifically Yeah. Like, has a lot more answers for Prisma. Totally. So if I could say, give me a rid that does this in chat g p t. It always spit it out. And then Drizzle, it would just hallucinate something, which was not helpful. But I did find that the Drizzle AI have you you Drizzle's docs have their own, like, AI in the rid Documentation and then the menu bar, there's an ask AI button.

I found you can paste in rid Whole ass snippets into this thing and then say, hey. What's what's wrong with this, or what what am I doing wrong here or whatever? And it was very good Using Drizzle's own documentation or whatever it's using as their data source to be able to answer that stuff. I I found it was much better, obviously, than Going to chat gpt for that. Yeah, it's they say it's powered by this thing called InKeep.

And I'd be curious to see How much additional data past the docs have they supplied it? Yeah, right. Is the whole code base? What is it? Because I did find way better answers that way. One other cool thing about schema is they have this introspection and pull. I mentioned this earlier is that you can rid. Pull down a schema from an existing database and it will then translate that to the actual TypeScript files that are needed.

I think that would be I didn't use that myself. But if I'm having if I have an existing SQL file And I want to interface with that database via Drizzle.

You don't really want to rebuild the entire schema and hopefully match it One to 1 with what your MySQL database Bos. You want to do the opposite, right? So what I think would be really cool is if we took the Syntax website, Pull the schema down from the Syntax website, which is just an SQL schema. Right? It's generated by Prisma, puts it into SQL schemas.

And then if we were to pull it down and translate it to drizzle, that's pretty wild that you could do that.

I have not seen or tried that. But no, me neither. But

I'm going to try it after the show because I think that would be really nifty just to see how well it does.

And theoretically, it should work because that's the thing with Drizzle as Wes. It's just you can only do stuff in Drizzle that converts to SQL, right? You can't it's not adding extra layers of validation or whatever to you. I guess that's that's maybe not true because you can set functions to generate default values that runs in JavaScript, But it tries to push you in the way of like, no, don't use JavaScript for default values. Use the SQL template tag that will rid. Generate your default Volley. Try to keep everything in the database. Sometimes people forget this is like a database Has its own language. Right? And you can generate defaults and generate time stamps

in SQL rather than in JavaScript. Word. Alright. We got to get moving here a little bit. So, let's talk about TypeScript really quick. This thing is fully typed, which means it is really nice to work in TypeScript in general, like we mentioned. Whether you're doing schemas or queries or any of that stuff, it's going to yell at you.

You know, one thing I really like about the query Syntax, which we'll talk a little bit about, is that you do d b dot, query, and then it auto populates your Your schema options, and you have that all there. And it tells you if you're trying to do something that does not make sense, it tells you Just about every step of the way. So

Scott love that that integration there. I don't know how we ever lived with databases.

Oh, my gosh. Not things type or even like I'd be screwed. My database for my course platform right now is rid. What I did is I generated all the types from MongoDB, but they weren't perfect. So then I went in and, like, tweaked them a little bit manually.

But this is making me want to switch entirely to a fully typed ORM because what a thing of beauty for having literally every property, every column that is returned. Every type just rippled throughout your entire application. Then if you're using like a meta framework, your your schema is then transferred to your queries. Your queries return data that is fully typed on the back end. You can pass that data to your front end And then you have full typings all the way through from your schema to your front end, and you never have to worry about mistyping or The big one for me, I always use, like, .title instead of .name.

You shouldn't have to fuss about that word.

Next up is migrations.

You know, migration is basically anytime you make a modification to your database, whether that is Adding new columns or changing, default values of columns or or creating tables, any of that stuff that anytime the database itself has changed, you need to write SQL code to change that database.

Rid. And a migration is essentially a file that has some commands for the database.

And so a migration file could just be, say, Create table if not exists, the table, the properties. And then when that file is run, it will create that table if it doesn't exist.

Now the thing about migrations are is that over time, our database evolves typically. You're adding fields. Sometimes you're changing fields. In a migration file JS basically just a file with instructions.

And the way Drizzle does JS is it puts them all into a folder. It has here's the here's Step 1, then step 2, then step 3, step 4, whatever.

And then that way, when you run the drizzle kit migration script, rid What it does is it just kinda goes through those step by step to make sure that your database is set up to be the exact way that the ORM rid expects it to be.

That way, everything is in connection. It is all related very specifically.

Migrations could be a giant pain in the butt if things kinda get rid Out of sync, these files are generated. You typically aren't wanting to go in there and delete them. That would be a disaster. That would make me very that would make me go nuts.

But, you know, the these things also the for migrations within, Drizzle, Unlike other things, which I don't necessarily understand why this JS. Maybe, yeah, I'm just being a dumb guy here. You have to write your own migration Script your own TypeScript file, which is really just simply, hey. Connect to your database and then run await migrate and then pass it in a folder. Like, why Well, I gotta write that file. It it's fine. Whatever. I did. I wrote it. It's fine, but you gotta do that. So first, you run drizzle kit generate.

And what generate does is it goes ahead and it looks and sees what's changed in your schema from the way that the database rid is to the way that your schema is, and it generates a migration file that's sequential.

And once that file is generated, You can then run the migration script to then step by step go through all of those files. Yeah. I'm a big fan of this migration since you can put those in your version control and you can see the history. So, like, if Scott drops table and adds 3 new columns to it And then he pushes that up to version control. And then I sell off my local database, which has not been updated, but the code has pnpm, Then I'm able to see, okay, I need to run these migrations in order to update my local database. So the schema looks exactly like what This application is expecting word. Let's talk about querying and selecting data. There's 2 ways to pull data out of rid. Drizzle.

There's queries and there is select. So I'm curious. Which one did you use, Scott?

I started using select because a lot of the docs are written in select. Yeah. Yeah. The moment I found out you could use query, I used query because I prefer that style of syntax.

I'm not a SQL guy.

I prefer a nice ORM syntax. And with the fact that you have TypeScript support, inquiry could help you out along the way. It was nice for me to do that.

So, you know, it wasn't necessarily a value add for me to use the select style SQL control syntax.

Yeah.

So I used I used the query syntax where I could. That said, I think this is a a part where the docs really fail. I I think the way the docs should have is every single example in the docs should be like you know, some have, like, TypeScript and JavaScript tabs or whatever for the docs. They need to have a tab that shows the query syntax and the select syntax side by side in a tab version where you can see both of them. Maybe I'll explain real quick the difference between them. Rid. The select is full SQL control.

It looks a lot more like writing SQL, and you often have to import Different methods like equals, not equals, greater than.

You have to import those methods and use them to build your query, and you also have to import your your schema values. So you just say select from you got to import the user schema and pass it in. So I did it the select way because all of the examples for SQL Lite. We're in select, and I couldn't I thought, oh, if you're using SQL Lite, you can't use the queries, but that's wrong. You can use the queries and rid. The query is much nicer, in my opinion.

If you're listening to this, you probably want the query syntax unless you're some sort of diehard SQL writer, And it's much nicer. So I would probably go towards query. But you're right. Like I thought for the longest time, Select is only for SQL Lite, but it's just because all the examples are written in Vercel warp. One thing I'm not going to spend too much time on is just views.

Views JS just a Database thing in general, it's sort of like a virtual database that can contain data from multiple tables. So at a rid. Database level, you can create sort of I we call them aggregations in MongoDB, but kinda similar They call them aggregations too. Rid. Okay. So if you have, like, alright, I want a user's view and it has all the users tasks and if they've completed them or Scott. Like, that might be 2 or 3 tables for of you, Scott, and you want to pull them all in so you can create a database view which has all of that data in and you can specify what people have access to generate secure things or I'll never query the password field because this view should never be able to have access to it. So rid. There's full support for that in Drizzle. Yeah. It's pretty cool.

I will say I don't necessarily relate those to aggregation, though. No? Okay. Yeah. Because aggregations, even in, in Mongo or in this Yarn kind of like you're doing lots of steps. So you're running a Yeah. You could really reduce them. Right. Yeah. You're doing like a reduce or a combination

or a group by kind of thing. Yeah. So I'll tell you an example of when I've used a view before. And that was.

Yeah, you're right. That was because I was trying to calculate how many courses I had sold and the dollar value for that. And Wes I was getting what I was doing is I was just querying all of those and then running a JavaScript produce over them and then adding them all up. That got really slow once I got past several 1,000 courses. Right. So I had to move that logic to the database layer because it's way faster to do it in the database.

So, yeah, I guess that's not necessarily a view, though, because if you would be like, All right. Let me grab a list of users, but also join their courses in and also select the progress from all of their videos.

And each user is gonna be 1 line in the column, and I can just use that as if it were a virtual table word.

Cool.

Yeah. Totally.

Ready. Next up is limitations. What are some limitations that you ran into? Because I didn't hit too many limitations myself in terms of where Drizzle was the issue.

Rid. Yeah.

This is not really a drizzle issue, but you often need to insert or query in multiple value. So I hit this both with Prisma and PlanetScale, and then I also hit it with Cloudflare D1. And that is ready. I was sending too much data in a single query to insert.

So I was looping over 150 rankings.

And each one has so that's 150 inserts, right? I tried to do it in a single go, which was a transaction.

And transactions are nice because if one of those inserts goofs up for whatever reason, the whole thing will roll itself back and you don't have incomplete data being inserted rid both in the syntax website. When I was inserting the words that are associated with transcripts too large. I think it's more of a limitation of timeouts on serverless functions, but it just immediately threw up an error. Too much data you're sending. So I had to rid. Break it down into smaller, incremental

rights word.

Yeah. I didn't do anything weird or anything like that.

Rid. Another thing here is transactions.

Transactions are something you can do with Drizzle, Wes you're running multiple steps in one One logical Sanity, basically, you're saying if I have a multistep thing, I need to do this, then this, then this, and I wanna treat it as Node functional unit of Steps. Right? Then that's when you would use a transaction, because these things always need to happen in this specific, Not only order, but we we need to have nothing interrupting these. Right? We need this to happen in a a chunk. And so that's That's when you'd reach for a transaction. Again, this is way more for complex operations, not necessarily something you need to do, for most basic usage. But, rid. Typically, if if you need a transaction, you'll kind of know it. So one last thing before we get out of here. I do wanna say I made a syntax drizzle, studio theme For fun, just because I was, you know, procrastinating.

So I have a link to it here, Drizzle dot studio themes, whatever. It's a long key. But there's a syntax podcast theme. If you're using Drizzle Studio, you can go search for it. It just uses our yellow and black color, so it's it's pretty cool. But, yeah,

that's That's it. So if you want to check it out, we'll have a link to the Drizzle ORM. It's ormdrizzle.

Team. They have sort of the devs behind it are pretty hilarious.

So rid. They used to have a pricing page.

Do they still have it? They they had a tracing page that just said, like, psych.

You know, like, it's not a paid product at all. They took that off, though, which makes me wonder if they rid. Are going to be offering you gotta think they got they got to make money on this somehow.

Somehow. You know, the the testimonials thing always kinda turned me off Because I was like they had the testimonials where it's just people saying bad things about Drizzle, which, like Oh, yeah. Yeah. I get it. It's funny. But it did kinda turn me off a little bit initially. That and the fact that I I didn't wanna write my own migrations. But, you know, I I think I I've come around. I'm not I'm trying not to be too much of a curmudgeon about stuff like that. It doesn't matter.

Awesome.

Let's move into

sick picks and shameless plugs. Yeah. Hey. I have a sick pick. Yeah. I I don't I don't know if you've sick pick to this before. I vaguely remember people talking about this on the show.

The Black rid. Berry movie. Have you seen the Blackberry movie? Oh, yeah. We've talked about it, like, 4 times on the show.

That's what I'm saying. I have a memory of it, but I don't have a vivid memory of it. Rid. And, we just watched it last night. So I just watched the BlackBerry movie last night. If if you haven't seen it, I thought it was awesome, rid in case you haven't gotten that impression already from watching the show. But, yeah, I thought it was really super good, and I didn't necessarily know any of this story. And if you're a fan of, Glenn Howerton, gosh, he is his unbridled rage in

this movie is on public display. It's always Sunny in Philadelphia.

Dennis. Yeah. It's it's very good, and it's got Michael Ironside in it. I'm a big Michael Ironside Sanity, total recall and stuff. So, yeah, big big fan. Blackberry movie, if you haven't seen it. Hamilton was in it. So Jim Busselli

tried to buy, the Bulldogs. Pittsburgh Penguins, my favorite team. Oh, but, no, he first tried to buy oh, no. He tried to bring an NHL team to Hamilton. That was it. He didn't Right by our local It was the Penguins. Oh, yeah. He was gonna bring it to him. The Penguins,

they were they were going through a huge drought at that time. They, you know, they were really bad. This is pre Sydney Crosby.

And, they they were a lot of people were trying to buy them. The team was in dire straits.

They were trying to move them to Kansas City or all kinds of of places. And at the end of the day, Mario Lemieux ended up buying them and kept them in Pittsburgh.

Wow.

Oh, yeah. That was a fantastic hockey facts for you. I would Almost watched that again, and I I don't think I've ever watched a movie more than once aside from, Wet Hot American Summer.

Yeah. That's great. I love how to break it.

I'm going to sick pick a candle warmer. I got this for my wife for Christmas, and we freaking Love it.

So what this is, is it's a beautiful little lamp that has a halogen bulb in it and you put a candle underneath it And it just melts the wax and leaves your house smelling amazing. And there's no flame. You don't have to worry about your house burning down or turning it off. It has a primer on it and it makes your candles last so much longer. Like like one of the Bath and Body Works candles, the big 3 wick ones.

Node. Wes probably got 30, 35 hours out of it and we're probably 20% left on the candle. So It looks so nice. It smells great. You don't have to be burning. There's also stuff around like you shouldn't be burning candles indoors all day long because it rid Makes your lungs black,

which is a bummer. Everything is bad for you these days. But Everything is bad. But it's it's true. It's just because we're getting smarter. It's not because

rid. We're getting softer or something. Yeah. I kind of like being a little Scott in the know about these things, but we fixed the cattle situation. We still burn candles, obviously, but This is really Node. And I'll link up the one that we got bored. I'm a little bit worried about buying bulbs for it, though, because in Node, for a long time, all the bulbs have been Led. I think you can still buy halogen bulbs.

It has to get hot enough. When my daughter's Easy Bake Oven broke, rid. I had a hell of a time finding a new bulb for it that got hot enough to bake a cake. Oh, yeah. Finally, I found, like, rid. Pnpm special one that is for it's called rough service. It's like a 100 watts, and it's for lighting up construction sites, which is a dumb thing to spend money on because LEDs are way better for construction site. I I have, you know those, like what are those twisty light bulbs? Oh, yeah. CFL. CFL. So I have, rid for my lighting setup in my studio, I had this giant CFL bulb, like, larger than a football, like, just massive one. And I wasn't using those lights anymore in my office because I got LED ones.

And I have these giant bulbs because I bought a few of them in bulk, And I've started using them in parts of my basement because they're super Oh. The the furnace room. I put one of them in the furnace room, and it's massive. It looks hilarious, but it lights up the room like nothing else. I know exactly what you're talking about. Our house had those as well. It lights up the whole I took them out and replaced them with strip LED lights because I kept rid Hitting them. I smashed 1, and there's, like, poison in those things. Right? Yeah. Yeah. Yeah. I I did replace a lot of those Bos the basement, too, with those strip led lights because they are very, very cheap and super bright.

Awesome.

Shameless plugs. Check out the syntax. TikTok of TikTok Scott net.

Whatever. Just look up syntax. FM. We're on there. Hit the follow. Leave us a comment. Rid. Get the if you see somebody leaving a mean comment, just bait them. Bait them. Make them make them comment more. It really helps the algo.

Rid That is actually good. Somebody, somebody was trolling us the other day on TikTok by posting a a quote from rid Oh, man. I forget who. Somebody famous. They they posted a quote from somebody famous, because we talked about not liking quotes inside of our productivity episode, so That was really, really fun.

Oh, that's great. Alright. Thanks for tuning in. Catch you later.

Peace.

Head on over to syntax.fm for a full archive of all of our shows.

And don't forget to subscribe in your podcast player Or drop a review if you like this show.

Vercel.