Skip to main content
721

January 24th, 2024 × #javascript#webdev#databases

You should learn Drizzle, the TypeScript SQL ORM

Podcast explaining the Drizzle ORM for interacting with databases like MySQL, Postgres and SQLite.

or
Topic 0 00:00

Transcript

Announcer

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.

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.

Wes Bos

Drizzle and databases.

Wes Bos

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

Topic 1 00:29

Explaining Drizzle, an ORM, and MySQL, Postgres & SQLite databases

Wes Bos

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.

Scott Tolinski

Yeah. Databases.

Scott Tolinski

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.

Topic 2 01:04

Century query performance tracker helps find slow queries

Scott Tolinski

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

Scott Tolinski

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.

Scott Tolinski

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.

Scott Tolinski

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

Topic 3 01:57

What is Drizzle?

Scott Tolinski

Just a little bit of what is Drizzle.

Scott Tolinski

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

Scott Tolinski

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.

Topic 4 02:29

Drizzle is SQL-like with query and schema syntaxes

Scott Tolinski

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.

Scott Tolinski

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.

Scott Tolinski

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.

Scott Tolinski

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

Topic 5 03:32

Drizzle Studio provides UI for visualizing database

Wes Bos

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.

Wes Bos

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.

Wes Bos

Just a UI for visualizing all of your data.

Popular database hosting services

Wes Bos

Usually when You work with your data.

Wes Bos

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

Wes Bos

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.

Wes Bos

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.

Topic 7 05:08

phpMyAdmin is the original database admin UI

Wes Bos

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.

Wes Bos

Node a lot of the ORMs, rid.

Wes Bos

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

Wes Bos

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

Wes Bos

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

Wes Bos

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

Scott Tolinski

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.

Scott Tolinski

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.

Wes Bos

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.

Scott Tolinski

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.

Scott Tolinski

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

Topic 8 06:53

SQLite databases are used widely in native apps

Scott Tolinski

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

Wes Bos

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.

Wes Bos

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

Scott Tolinski

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,

Wes Bos

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.

Topic 9 09:15

Overview of Postgres, MySQL and SQLite databases

Wes Bos

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

Wes Bos

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.

Wes Bos

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,

Scott Tolinski

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

Scott Tolinski

The Trader Joe's KV key value store.

Scott Tolinski

Yeah.

Wes Bos

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

Scott Tolinski

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.

Scott Tolinski

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

Scott Tolinski

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

Wes Bos

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.

Scott Tolinski

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

Wes Bos

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

Wes Bos

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

Wes Bos

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.

Wes Bos

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.

Wes Bos

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.

Wes Bos

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.

Topic 10 13:34

PlanetScale and Neon provide hosted MySQL and Postgres

Scott Tolinski

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.

Scott Tolinski

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

Wes Bos

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.

Scott Tolinski

Yeah. Totally.

Topic 11 14:52

Can use prefixes to host multiple apps on one database

Scott Tolinski

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.

Topic 12 15:15

SQLite is simple local database, no server needed

Scott Tolinski

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

Scott Tolinski

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

Wes Bos

DB and Node. DB and gin.

Wes Bos

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.

Wes Bos

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.

Wes Bos

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

Wes Bos

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.

Wes Bos

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.

Wes Bos

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.

Scott Tolinski

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

Topic 13 18:00

Cloudflare Workers provides SQLite hosting

Scott Tolinski

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

Wes Bos

extremely simple to use.

Wes Bos

It does serverless super Wes.

Wes Bos

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.

Wes Bos

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.

Wes Bos

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

Wes Bos

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.

Wes Bos

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.

Wes Bos

Big fan. Yeah.

Scott Tolinski

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.

Scott Tolinski

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

Wes Bos

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.

Scott Tolinski

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

Scott Tolinski

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

Scott Tolinski

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.

Scott Tolinski

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.

Topic 14 20:43

Schemas define database structure in JavaScript

Scott Tolinski

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

Scott Tolinski

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.

Scott Tolinski

And that's cool.

Scott Tolinski

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.

Scott Tolinski

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.

Topic 15 22:17

GraphQL and Prisma use custom schema syntaxes

Scott Tolinski

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.

Scott Tolinski

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

Wes Bos

first. Right? Very explicit.

Scott Tolinski

Very explicit.

Wes Bos

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

Wes Bos

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

Topic 16 23:48

Schemas ensure app and database are in sync

Scott Tolinski

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.

Scott Tolinski

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

Wes Bos

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

Wes Bos

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

Scott Tolinski

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.

Scott Tolinski

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.

Scott Tolinski

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.

Scott Tolinski

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

Scott Tolinski

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

Scott Tolinski

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

Wes Bos

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.

Wes Bos

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.

Wes Bos

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.

Wes Bos

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.

Wes Bos

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.

Wes Bos

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

Wes Bos

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,

Scott Tolinski

You can check out mine.

Scott Tolinski

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.

Scott Tolinski

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.

Wes Bos

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.

Wes Bos

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

Scott Tolinski

to be effective.

Topic 17 30:06

TypeScript provides end to end typing

Scott Tolinski

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.

Scott Tolinski

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.

Scott Tolinski

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.

Scott Tolinski

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.

Scott Tolinski

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.

Scott Tolinski

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

Scott Tolinski

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.

Topic 18 31:46

Query relations using "with" syntax

Wes Bos

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.

Wes Bos

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.

Wes Bos

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

Wes Bos

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.

Wes Bos

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,

Scott Tolinski

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.

Scott Tolinski

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.

Wes Bos

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.

Wes Bos

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.

Wes Bos

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.

Wes Bos

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

Scott Tolinski

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

Wes Bos

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

Wes Bos

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

Topic 19 36:12

Prefer SQL defaults over JavaScript

Scott Tolinski

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.

Scott Tolinski

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

Wes Bos

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

Wes Bos

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.

Wes Bos

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.

Wes Bos

You shouldn't have to fuss about that word.

Scott Tolinski

Next up is migrations.

Scott Tolinski

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.

Topic 20 38:17

Migrations track database changes over time

Scott Tolinski

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

Scott Tolinski

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.

Scott Tolinski

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.

Scott Tolinski

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.

Scott Tolinski

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.

Scott Tolinski

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

Scott Tolinski

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.

Scott Tolinski

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.

Scott Tolinski

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.

Wes Bos

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.

Wes Bos

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

Scott Tolinski

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.

Topic 21 41:05

Queries vs. select for data access

Scott Tolinski

I'm not a SQL guy.

Scott Tolinski

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.

Scott Tolinski

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

Scott Tolinski

Yeah.

Scott Tolinski

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.

Wes Bos

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

Wes Bos

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.

Wes Bos

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.

Wes Bos

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.

Scott Tolinski

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

Topic 22 43:51

Views provide virtual combined tables

Wes Bos

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.

Wes Bos

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.

Wes Bos

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.

Wes Bos

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.

Scott Tolinski

Cool.

Scott Tolinski

Yeah. Totally.

Scott Tolinski

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.

Topic 23 45:18

Transaction size limitations

Scott Tolinski

Rid. Yeah.

Wes Bos

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.

Wes Bos

So I was looping over 150 rankings.

Wes Bos

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

Wes Bos

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

Scott Tolinski

rights word.

Scott Tolinski

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

Scott Tolinski

Rid. Another thing here is transactions.

Topic 24 46:29

Transactions for multistep operations

Scott Tolinski

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.

Scott Tolinski

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,

Wes Bos

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.

Wes Bos

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

Wes Bos

So rid. They used to have a pricing page.

Wes Bos

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

Wes Bos

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.

Scott Tolinski

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.

Wes Bos

Awesome.

Wes Bos

Let's move into

Scott Tolinski

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.

Scott Tolinski

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

Scott Tolinski

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

Wes Bos

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

Scott Tolinski

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

Wes Bos

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,

Scott Tolinski

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

Scott Tolinski

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

Scott Tolinski

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.

Wes Bos

Wow.

Wes Bos

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.

Wes Bos

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

Wes Bos

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

Wes Bos

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.

Wes Bos

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,

Scott Tolinski

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

Wes Bos

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.

Wes Bos

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.

Scott Tolinski

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.

Wes Bos

Awesome.

Wes Bos

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

Wes Bos

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.

Scott Tolinski

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.

Wes Bos

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

Wes Bos

Peace.

Scott Tolinski

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

Scott Tolinski

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

Wes Bos

Vercel.

Share