You're listening to some tracks, the podcast with the TCS web and the treats out there, grab yourself it and get ready, here's not the list, eat and Westmoss. Welcome to syntax, so I guess with TCS 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 have queries and insert data to different databases. So we thought we would explain what Drizzle is and how it works alongside explaining a bunch of stuff about MySQL, Postgres, and SQLite.
So those are the three databases that Drizzle supports and being able to explain the features and contacts of Drizzle, we thought we would be super helpful. So buckle up for a database field episode. Yeah, databases, huh? You're working on databases, ORMs, chances are, you're going to have to be code and have to slow queries.
One of my favorite new features on Century is the query performance tracker. When we were building our syntax sites, 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 one. I wasn't doing it, I believe it was fine, you need to think I was doing fine first since it was finding something.
And because of that, we had a bunch of slow queries. And I was able to see that because of centuries, slow query monitoring. So if you send it for Century at Century.io, that's the NPRY.io, use the Google code to the two month free and it shows for the device Century. So let's get into first and foremost, drizzle just a little bit of what is drizzle.
Now, drizzle's an ORM, basically an ORM is a tool that makes it typically working with databases easier. It's a way that you can map your database to some code. That way, you can query off your database and have it be just a nicer way of working and it sometimes give you types, sometimes they give you validations, sometimes they give you just things that just straight up working raw with the database one. And so, drizzle is a newer ORM for JavaScript.
They use plenty of ORMs myself in all different contexts. And drizzle is certainly a little bit more of a modern outlook on the ORM. And the ORM itself, probably itself is 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, you know, no surprise that people are really liking drizzle. There's also something called drizzle kit, which gives you more control over the ORM in 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. And those types of things just become a little bit easier, a little bit more hand-holdy, which, as somebody like me, I'm not, you know, not typically a database person. Yeah, it's a thing that I really, really wanted with a drizzle.
And that was one of the reasons I hadn't tried it yet because it's like, I don't want to be writing that stuff myself. I don't feel like doing all of those things. Therefore, drizzle kit is, when's that kind of drop? It definitely opens up.
There's a little bit for me, whether that is just working with your database itself more via migration. The migration stuff is super cool. We're going to talk about what migrations are in just a second, but being able to see the progress of how things change over time and 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's been working, they just use have a database. You don't have the schema for it. It will like backwards pull it down, which is cool. We'll talk a little bit more about that and it's like we're getting a head of ourselves, and we're going to try 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? A SQL Pro is one. Table classes is another one.
Table classes are the one that I've been using a lot lately. MongoDB Compass is another really nice one. Sometimes it's especially your first. Oh yeah, that's a big one.
PHP Myven is probably the OG one. You just want some sort of UI for being able to straight up at your data. It's the original admin interface. And a lot of the ORMs, Prisma has this, and Drizzle has this.
Other ones have it. They will give you just a table of view of all of your data. You can filter it. You can sort really quickly.
It's really nice to have that when you haven't totally built out a full admin UI. Sometimes you've got to double click a row and type in the actual data. Totally. I really greatly prefer having something like this.
It's funny, but I don't know about you, but for a long time, I just thought PHP Myven was MySQL. Like in my brain, because they were always so connected when I was working on whether it was Drupal or WordPress. It was like, all right, now you have, now you're in a PHP Myven. I was just like really connected those two software to be the exact same thing.
Oh, I'm working on MySQL now. Yeah. Yeah, it's true. You can use it for everything.
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 your password in there, because they just MD5 string in there. It was pretty cool. Probably gets to tell you 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 code of paint. Because drizzle studio, one of the things that it has been going for is the UI super nice. Hey, you know, that's a pretty cool overlook.
Anytime you're working on any of this stuff, it's a nice UI goes a long way. There's also a product called benchmarks, which I haven't really looked at benchmarks. It's a newer thing. It's actually listed in an app as new.
But at the way of really determining what's slow and fast is that you work where it's sort of slow down. It's interesting. I would want to dive more into benchmarks. So we're probably not going to be covering that too much.
But it exists. So it's important to know. Yeah, I'm curious how this works, because like, is it like a proxy for all of your queries? I haven't got it because generally, I lean on using a database hosting provider.
Usually when I pick somewhere to host my database, I'm not just spinning up a Linux server and launching my SQL. I'm going to go for some sort of past platform as a service. And that's going to allow you to do it. Yeah.
And they're going to email. And that's going to be news. I'm not seeing why we do that. Because they provide a lot of databases to scary, especially for people like us who are just one day where write buttons and put rectangles on a page.
And the next day, we're querying databases and backing up. And databases are extremely complex. And tools like Trisma make, working with databases, so much more accessible. However, there's still a lot that can go very wrong.
And the part of the big thing that can go wrong is that you can have a slow database queries. Something like my model, does this. My scale this is the email. When there is a extremely slow query or something that has run 10,000 times and is extremely slow.
And it's a hey, maybe it's worth putting an index on this field because you've been filtering by this field quite often. Word. Yeah. I mean, databases is scary as you mentioned.
So I don't want anybody to get in my database. That's just straight up. Any sort of, anything I can do to mitigate potential data leak or anything like that is going to be as cautious as you could possibly be. So that's just what's right, I'm out there.
Let's talk about the different types of databases. Specifically, the ones that Trisma support is Postgres and MySQL and SQL and SQL. So Postgres and MySQL are probably the two big databases they've been around forever. They're sort of battle tested.
They fall in and out of love over the years. 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 superbase.
Superbase does a lot more than just a database. But their database part is Postgres. The first cell postgres is, I call this the Trader Joe's neon database because the first cell partners with neon to resell their database. That's the same thing for the upstairs, which is what we use for these.
Oh yeah. And the Trader Joe's KV key value store. Yeah. Maybe we should have game-slaying Trader Joe's for the Brits and Australians and I guess the Canadians.
Yeah. Well, I don't know what you necessarily mean by Trader Joe's here. Joe's is just a grocery store. It's like it's owned by all these, I believe.
And I don't know. It's kind of expensive oddly packaged unique items. Yeah. So all of their items are like white label or really meaning that it's well known that a lot of Trader Joe's stuff is built by or started built.
It's created by Kellogg's or like they're certainly probably made in the same factory as a lot of the big brands as they're built. They'll just like rebrand it as like Trader Joe's. You know, they're just curious in the same box at the end of the day. You know, a partial little bit.
Yeah, exactly. We don't know if Trader Joe's is a little sad, but it goes, go there. We're a processor. I can't imagine that the Trader Joe's very much because there's a hole.
It's going down in the Trader Joe's parking lot YouTube video about like how crazy the Trader Joe's parking lot's are. And it's seriously like that's the one the most accurate video is all the time. Whoever designed the Trader Joe's parking lot's and how can we make this as frightening as possible for them like 10 parking spaces and you can all just like fight over them. It's so accurate.
It's like the big reason why it's like, all right, if we got a 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. It was like wrapped around the entire store. The line was almost going out the door.
Just to pay. People working there are just like next, next, like just champs. Anyway, what else? What else?
They don't get to close dress real ways. Another popular host they have postgres. And then we also had the folks from payload CMS on. Go back and listen to episode 700 with folks from payload.
They use postgres and also both of those things. A whole lot about what that is. The big name in MySQL right now for hosted is PlanetScale. That's what we're using on this in tax website, the Prisma ORM.
And I'm a big fan of it. It allows you to like fork databases, which is kind of cool. So like, it's not one sort of feature. You can just fork the database and you can all the fresh data rather than like some old data.
So big fan of PlanetScale. It's kind of interesting. If you search like PlanetScale first or PlanetScale alternatives, you often find people recommending postgres. Which is like people think PlanetScale is a much good job that they think that PlanetScale is tech, which kind of it.
Because they do connect to pulling in a lot of that stuff. But people use the word PlanetScale as if it were another database, you know. It's MySQL. Yeah.
In that same regard, we mentioned neon. Neon and PlanetScale are kind of analogous. Like neon is to postgres as PlanetScale is to MySQL. So they're very similar.
We're both do connection pulling that type of thing. So if you do want that type of database, you want to do postgres going neon. You want to do MySQL. Go PlanetScale.
And both fairly generous plans too. I'm not going to free plan for my neon DB that I'm using right now. And hey, I got a decent amount of people using this thing. I don't even close to approaching the free limit.
No, no, no, no. It's not a scalable thing to use the free plane. But hey, it was a project. It's not bad, right?
Totally. That's the hope of a lot of these things. You just get your site project on up and running. And then before you know it, you're $100 in a month on these different services.
But it's well worth it. And a lot of people also have recommended just using one database. And then using like database prefixes, we have to put this on a hash show. It's just for one database.
And then you can use prefixes to post multiple apps on it. It still makes me a little bit uneasy, but certainly something you could do. Yeah, totally. It's also SQLite, which SQLite, you know, I think it overlooked a lot.
But SQLite, I think it's used very frequently in native app development, the store data on an iPhone or an Android device or something like that. But it's basically just a store data. On your computer, it's like local database. And it's super useful to just be going because it's SQL.
I mean, you can spend up a SQLite DB in no time. Not pay anything if you just hack it away and something. And that's it. You got yourself a database cooking.
And you don't have to be running a YAM. OK, what's the one database service that you use to get running without my TV? TV and GIN. TV and GIN.
What's the GIN? TV and GIN. TV and GIN. I always thought it was D begin.
But it is DB and GIN. So DB and GIN is a little tool that you can run on. Your computer and what that will allow you to do is to start and stop your database servers. So you don't have to remember the crazy CLI arguments that you need to pass and you have to see it on it.
And it also runs like a Zamper MAM. Yeah, big fan, big fan of that to be able to run it. 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 trillion SQLite databases in the world. And that's because their SQLite databases probably in your thermostat.
There's certainly lots of them on your phone. I was looking into how the Raycast extension for iMessage works. So there's an iMessage extension that will allow you to do that to factor authentication codes. That'll implement them in tier browser.
And I was like, how do they do that? And it turns out that all of the iMessage is on your computer. And you can open it up and read it. I thought it would be encrypted.
And then also your i Photos has SQLite database. And inside of SQLite databases, all the information about, I want somebody to crack it open. And it will tell you who it thinks is in each photo. What is it?
Like a search dog on iMessage. It will bring up photos of the actual dogs. So all of that metadata about your photos needs to be stored somewhere. And you can't store that in XF because that would be of 10,000 files.
That would have to parse every single file before you execute the search. So it Apple just sticks all that metadata in a SQLite database on your device, which I thought was really cool. Yeah. Yeah.
And SQLite can be hosted if you want. Why would you pick a SQLite host? What would be a reason for that? Yeah.
It's extremely simple to use. It does serverless super well. And there's lots of really great hosting options that there's 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 two, but Cloudflare uses it for a lot of their own stuff. So I would feel pretty confident in that type of thing.
FlyIO uses as well. Fun. So fun, 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 fun also comes with a SQLite adapter built right in. And I love that. I can just get up and running with SQLite very, very quickly.
But they also now tease being able to import SQLite database in JavaScript files with JavaScript. In port assertion, so in port assertion, it's something new to JavaScript where you import a file and you can say as text, as JSON. And they're building an import assertion for SQLite. So you can say as SQLite.
And then I'll just return the whole database connection. Big fan. Yeah. Yeah.
I don't know how you feel about that, but I'm sure. You know? The only thing about Bundit, that's interesting, is they do a lot of things that are very user-focused in terms of stuff you might want to do. But I do have concerns about them.
Like the amount of stuff they're adding to that. Yeah. On one hand, there's standards. And on the other hand, there's the nice stuff that's added.
But they're doing it in. It's not like a, you know, like a feed is not an on-stander. So if you just import a CSS file and boom, you have a CSS, right? Like that's not a JavaScript thing.
But Bundit's doing it in a way that is using import assertion. So theoretically, you could write a loader that handles that. So maybe like maybe that's the end game there. It's some reason it feels weird to me as a runtime and not as a library.
I don't know. I don't know why the field weird to me. I like the nice things I just like that. All right.
So let's get into the next part, which is defining your data structures. And in terms of just like many ORMs, one of the ways that you do that is through what's called a schema. And or as well as what's a schema. Yeah.
And a schema is basically an outline of how your data is out late out within the database itself. This includes data types, property names, and in Drizzle's case, column names, you have this kind of like separate concepts. Where sometimes it's like the column name is always going to be directly matched to the property name. In Drizzle, you have to specify the property name and the column name itself when you declare.
And that's cool. But this is a basic way that you can again define the structure of your database overall as a whole. And then includes things like relations, default values, things that are non-no, 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 use a Prisma Honda syntax website, and that's done through a specific Prisma schema file, which is a dot Prisma file. And that's kind of a done syntax. In GraphQL, you have a schema file. That's a GraphQL syntax.
In Drizzle, the schema is a JavaScript function. With a function, you do, in our case, or in my post-grest result, you do a PG table function. You give it to the table name. And then at the second parameter, in that function, is an object all of the values in that table.
And that's typically, that's actually kind of one of the things that makes Drizzle really interesting, is that everything is very JavaScript first. Right? Very explicit. Yeah, but again, of doing the schema directly in JavaScript, so the huge benefit to that is slightly getting into the second, but it's tight.
It's fully tight, so it's all in JavaScript. So the changes to your schema immediately ripple their way throughout your entire code base. You still do need Drizzle kit to generate your schema, because at the end of the day, Drizzle is generating SQL. It's generating the queries that need to be sent to your SQL database.
And it's generating the SQL files that will modify what your schema looks like. Things like that is like, oh, I added a field 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 et cetera.
And I should say to interrupt there, you only need to use Drizzle kit to connect your schema if you're having Drizzle handle with your migrations in your table creation. Because you could do all of this by hand if you were a SQL-enough. And that would 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 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 two things that we built using Drizzle?
And in the database, we just go ahead for a second. OK, cool. Yeah, mine is more interesting. And it is open source.
So you can check it out. I'll have it linked to that in the show notes. Basically, I'm working on a habit tracker. I mentioned this in our productivity episode.
And it's called it. It's a habit path.io if you want to check it out. But it's basically a simple habit tracker. And that's a really good project to do.
Because it's more interesting than a to-do list because you have relations. 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 has existing.
And we have some relations there. And I also have user accounts. So if you want to see how authentication and I roll my own authentication, there's a relationship where one habit has many checks. A check has one habit.
Check is just paid on the state. You checked off. There's also a user table. A user table has a relation to a session.
There's a user session. There's also a wait list for users. And addition to that, there is users have checks and habits. So there's also many too many relationships there.
So if you want to see how relationships and user accounts are those things are done, it's pretty good. So what I built was something to track where syntax is on the podcast charts. So we want to be able to see where we are. Especially in relation to other podcasts that are within the coding spectrum.
These are kind of key tabs on where we're at. We go up and down the charts. And we pay for the single chartable. And it works pretty good.
And give us all of the data that we want to say. Well, you know what? I love doing. I love writing scrapers.
So I know the book looks great for that. It scrapes 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, I need to save the status.
So my go to when I'm building something really quickly like that is A, JSON stringify it and throw it in a file. Or B, I use a couple of different like file DB noted adapters in the past, which will give you really quick schema less being able to just add and remove it. The downside of that is you don't get like full typing. So I was like, you know what?
I'm users over this time because you can get up running with a database. Especially SQLite. You 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 months ago, you have your team up and running and you have full blown queries and tax 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, et cetera.
So pretty nice little example that I was able to just quickly throw stuff into database and be able to query it. I used, and also I used Cloudflare D1 there, which was a little bit tricky because it's not like a standard. It doesn't just give you a SQLite connection to the string, but a little bit more funky, but I was able to sort of work my way around it. Yeah, totally.
And I used neon, by the way, for mine. So if you want to see neon, you can try mine. Cool. Let's have relational data.
Relational data. I found it pretty easy. I did have some big, my head gets a while moment because I had a string out of place. It was one of those things where I had my habits relation.
My relation was on habits. 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'm just over and over again. I compared it to the docs. What's going on? And it's just like one of those things where you look at it visually, you're swapping those two words.
And we're just kind of about it, because you really think I was really convinced that I had my code right. I don't know why it was so convinced because I was getting an error and I was following the docs. But I was so convinced for some reason, that wasn't my fault. 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 yes-linked plugin or some sort of linter.
And it will immediately tell you when you go something up. And I found that, and I only saw later that there also is a result. It's like maybe this does the same thing. But I found the same thing where I was just trying stuff and nothing work or I saw this thing wrong.
I feel like maybe that could be improved a little bit. Or maybe I was not using the e-linked plugin correctly or at all. That's one thing with the main specific language, you have to have that tool. For that domain specific language to be effective.
In TypeScript, yeah, TypeScript will save you from all the things that you can't put. There's something you can't save you from. Can't save you from the thing that is intentional. I did this intentionally.
It passes all the types of checks, but it's still incorrect. So that's even defined many of the relations. One relations, one relations, one in relation, one in relations. One in many relations.
Any of the relations types you want, you can define those inside your schema. And then what's nice about relations inside of a result, which I call the docs to be kind of non-descriptive with the thing. So for instance, if I have a habit that has checks, right, I could say, hey, give me all of this user's habits. And then in that selection, in that query, all I have to say is with colon checks, colon true.
That's it. And it's going to give me the checks. Now granted, if you want to limit that further, you just have to have a object instead of true where you have, again, a query selection thing in the real new one instead of the whole thing. You want to do one or perhaps a filtering of based on, oh, I only want checks from this date range for me.
That was the thing. So I could have a date range that's being that parameter in there. But for the most part, I found that it's actually to be lovely. I found it to be really nice to work with.
The documentation for that, I found it to be a little less lovely. I had to like, yes, I'm a little bit bigger out. Yeah, luisbars. I want to see more examples.
Yeah, I found it really helpful to just search common result queries on just to see how other people are specifically doing it. Yeah, 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'm not. Because a lot of the Q&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 it's on the disk of somebody. And I've been finding a lot of cloud-floured and drizzle question to answer on answer overflow, because somebody I asked it in Discord and now they're surfacing it, which is genius.
I was finally frustrated by Discord. That's the worst part of the time. I don't know. Just questions there.
Go there to die unless you explicitly go to the chat room and search for it. It Discord really wanted to step up their game. Here's what you do. You index all of the four on tight posts.
You make it publicly available there. Thank you. That would be great. Because I'm not just not able to search that stuff.
To find this stuff when you want it. I like to talk about Prismo that. Chat GPT specifically. Yeah.
I had a lot more answers for Prismo. So if I could say, give me a schema that does this. And Chat GPT. I'd always spit it out.
And then Drizzle it would just lose anything, which was not helpful. But I did find that the Drizzle AI, how you, Drizzle's docs have their own AI in the documentation. And then then you are, there's an Ask AI button. I found you can taste them whole ass snippets into this thing and then say, hey, what's wrong with this?
Or what am I doing wrong here or whatever? And it was very good at using Drizzle's own documentation or whatever it's using as their data source to be able to answer that stuff. I thought it was much better. I'd always been going to Chat GPT for that.
Yeah. This is 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 based? What is it? Because I had to find way better answers that way.
One other cool thing about Steam is they have this introspection and full image in this earlier is that you can pull down a schema from an existing database. And it will then translate that to the actual text profiles that are needed. I think that would be, I didn't use that myself. But if I'm having, if I'm 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 one with what you're might see well databases.
You want to do the opposite. So what I think would be really cool is if we put syntax website, pull the schema down from the syntax website, which is just a SQL schema, right? It's generated by Prisma. Put 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'm not seeing or try that, but no, any other. But I'm going to try it after the show, because I think that would be really nifty because to see how well it does. And theoretically, it should work.
Because that's the thing which Drizzle as well. It's just you can only do stuff in Drizzle that converts to SQL. You can't. It's not adding extra layers of validation or whatever.
Do you? I guess that's maybe not true, because you can set functions to generate default values. And that runs in JavaScript. But it tries to push you in the way that, no, don't use JavaScript for default values.
Use the SQL template tag. That will generate your default value. I keep everything in the database. Sometimes people forget that it's like, the database has a language.
And you can generate default and generate time stamps in SQL rather than in JavaScript. Work. All right, we got to get moving here a little bit. So let's talk about types really quick.
This thing is fully typed, which means it's 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 what I really like about the query syntax, which we'll talk a little bit about is that you do DB query and then auto populates 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 got to love that integration there. I don't know how we ever lived with databases. Oh my gosh.
I don't think Skype or even like these queries. My database from my course platform right now is I did it as I generated all the types from MongoDB, but they weren't perfect. So then I went in and 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 is ripple throughout your type application.
And then if you're using that, I'm everywhere. 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 typeings all the way through from your schema to your front end. And you never have to worry about mistyping or the big one from the iOS use that title. Instead of .name, you shouldn't have to fuss about that. Word.
Next up is migrations. You know, migration is basically any time you make a modification to your database. What about is adding new columns or changing default values of columns or creating tables. Any of that stuff that any time the database itself has changed, you need to write SQL code to change that database.
And a migration is essentially a file that has some commands for the database. And so a migration file could just be say, great table, if not exist, 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 is that over time our database evolves typically or adding fields, sometimes you're changing fields.
And a migration file is basically just a file with instructions. And the way the drill does it is it puts a module full of awareness. Here's a step one. Then step three, step four, whatever.
And then that way when you run the drizzle kit, migration scripts, what it does is it just kind of goes through those steps. I step to make sure that your database is set up to be the exact way that the ORM expects it to be. That way everything is in connection. It is all related very specifically.
Migration could be a giant pain in the butt. If things kind of get out of sync, these files are generated. You typically aren't wanting to go in there and they'll leave down that would be a disaster. I don't make any go nuts.
But these things also, the formagations within drizzle, unlike other things, which I don't necessarily understand why this is maybe I'm just being a dumb guy here. You have to write your own migration script. Your own types of file, which is really just simply you take an active database and then run oh wait, my great, and then pass it in and folder like what? What do I got to write that file?
And then if I'm whatever, I did that at the time. But you got to do that. So first you run drizzle kit generate. And what generate does is it goes ahead and it looks at sees what's changed in your schema from the way that the database is, to the way that your schema is.
And it generates a migration file that's a sequential. And once that file is generated, you can then run it. The migration script to then step by step go through all of those files. Yeah, I'm going to get into those migrations.
You can put those in your version control. You can see the history. So like if Scott drops table and adds three new columns to it. And then he pushes that off to version control.
And I sell my local database, which has not been updated. But the code has been then I'm able to see, OK, I need to run these migrations in order to update my local database. So the schema looks exactly like what this application is expecting. It's about query and selecting data.
There's two ways to pull data out of drizzle. There's queries and there's select. And here's which one did you use Scott? I started using select because a lot of the docs are written in select.
Yeah, the moment I found out you could use query. I used query because I prefer that's that I was syntax. I'm not a SQL guy. I prefer a nice alarm syntax.
And with the fact that you have TypeScript support and query can help you out along the way, it was nice for me to do that. So it wasn't necessarily value add for me to use the select style SQL controls syntax. So I used the queries and types where I could. That's it.
I think this is a part where the docs really fail. I agree. The docs should have every single example in the docs should be like, you know, sometimes like TypeScript and JavaScript tabs or whatever for the docs. They need to have a tab that shows the queries and text and the selects and text side by side in a tab where you can see both of them.
Maybe I'll just go ahead and throw the difference with them. So 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 schema values. So you just say select from, you've got to import the user schema and pass it in. So I did it the select way because all of the examples for SQLite were intellect. And I couldn't.
I thought, oh, if you're using SQLite, you can't use the queries. But that's wrong. You can use the queries. The queries is much nicer.
And you're probably, if you're listening to it, that's you probably want the queries syntax. Unless you're some sort of die hard SQL writer. And it says much nicer. So I would probably go towards query.
But you're right. I thought, for the longest time, select is only for SQLite. But it's just with all the examples of it. And select one thing I'm not going to do much time on is just views.
The views is just a database in general. It's sort of like a virtual database that can contain data from multiple tables. So at a database level, you can create sort of problem aggregations in MongoDB, but kind of similar as new aggregations too. OK.
So if you have like, all right, I want a user's view. And it has all the users tasks. And if they completed them or not. Like, that might be two or three tables for you.
So 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, oh, never query the password field because this view should never be able to access to it. So there's a little work for that in result. Yeah, it's pretty cool. I will say I don't necessarily relate those to aggregation.
So I know. OK. Yeah. Because aggregations, even in MongoDB, or in this kind of like, you're doing lots of steps.
So you're running them with a query. You can reduce them, right? Yeah, you're doing whatever you do. So a combination or a group I kind of thing.
Yeah. So I'll tell you the example of when I've used a view before. And that was, yeah, you're right. That was, I was trying to calculate how many course I had sold in the dollar value for that.
And when I was getting, what I was doing, I was just growing all of those. And then running a JavaScript reduce over them. So yeah, I guess it'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 going to be one line in the column. And I can just use that as if it were a virtual table. Work cool. Yeah.
So next up is limitations. We're going to tell you some limitations that you ran into. Because I didn't hit too many limitations myself in terms of where Drizzle was the issue. Yeah, this is not really a Drizzle issue, but you often need to insert or query in multiple values.
So I hit this both with Prisma and Planet Scale. And then I also hit it with Cloud Cloud D1. And that is you were sending too much data in a single query to insert. So I was looping over 150 rankings.
And each one has 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.
You don't have like an employee data being inserted. Both in the syntax website when I was inserting the words that are associated with transcripts, two large. I think it's more of a limitation of timeouts on serverless functions. But I just immediately threw up an error too much data.
You're sending, so I had to break it down into smaller incremental right sport. Yeah, I didn't do anything weird or anything like that. Another thing here is transactions. Transactions are something you can do, which is all words you're running multiple steps in one logical unit basically.
You're saying if I have a multi-step thing, I need to do this, then this. And I want to treat it as one functional unit of steps. Right? Then that's when you use a transaction because these things always need to happen in this specific, not only order, but we need to have nothing interrupting these.
Right? We need this to happen in a chunk. And so that's when you reach for a transaction. Again, this is way more complex operations.
Not necessarily something you need to do for most basic usage, but typically if you need a transaction kind of knowing it. So one last thing before we get out of here, I do want to say I made a syntax a drizzle studio theme for fun. Just because I was procrastinating. So I had a link to it here.
Drizzle that studio theme is whatever it's a long key. but there's a syntax podcast theme. If you're using a drizzle studio, you can go search for it. Just use a yellow black color.
It's pretty cool. But yeah, that's it. So you want to check out one link to the drizzle or M. Or M.
That's the same. They have the depth behind it. They used to have a pricing page. Did they still have it?
They had a pricing page. It just said like, it's not a paid product at all. They took that off. Which makes me wonder if they are going to be offered.
You got to make money on the somehow. You know, the testimonial thing was kind of turning off because I was like, they had testimonials where people think that things about drizzle, which like, OK, yeah, I get it. It's funny. I'm not trying to do too much of a comment about stuff like that.
Awesome. Let's move into sick fix and shameless plugs. Hey, I have a sick pick. I don't know if you've sick picked it this before.
I vaguely remember people talking about this on the show. The blackberry movie. Have you seen the blackberry movie? We talked about a four-time show.
That's what I'm saying. I have a memory of it. But I don't have a memory of it. And we just watched it last night.
So I just watched the blackberry movie last night. If you haven't seen it, I think it was awesome in case you haven't done it in a professional already from watching the show. But yeah, I thought it was really super good. And I didn't necessarily know any of the story.
But I think it was a big fan of Glenn Howard's and gosh, he is unbridled rage. So we have a very good answer. Michael Ironside and Michael Ironside and Michael Ironside fan told recall and stuff. So yeah, the blackberry movie, if you haven't seen it.
Jim was a little bit too bright to buy the Bulldog for penguin. But no, he first tried to buy. Oh, no, he tried to bring an NHL team to Hamilton. He didn't try to buy our local penguins.
Yeah, he was going to bring it to the penguins. They were going for a huge drought at that time. They were really bad. It was pretty at Sydney Crossley.
And they were a lot of people were trying to buy them. The team was in dire straits. They were trying to move in Kansas City. They were all kinds of places.
And they were going to buy them. And definitely the dessert. Oh, yeah, that was a fantastic tour. I would almost watch that again.
I'm going to ever watch them with me. When I'm on the one side from, with Hot American Summer. Oh, yeah, that was great. I'm going to take a candle warmer.
I got this from my wife for Christmas. And we freaking love it. So what this 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. And I'll have to worry about your house burning down or turning off. It has a timer on it.
And it makes your candles last so much longer. Like one of the bathroom body works candles. The big three-wick ones. And we probably got 30, 35 hours out of it.
And it's probably 20% left on the candle. So it looks so nice. It smells great. They don't have to be burning.
There's also stuff around like you should be burning candles indoors all day long. Because it makes your lungs black. Which is about everything is bad for you these days. But everything is bad.
But it's true. It's just because we're getting smarter. It's not because we're getting softer. I kind of like being a little in the know about these things.
But the next thing else is to be super in candles obviously. But this is really, really nice. I'll link up the one that we got. I'm a little bit worried about buying bulbs for it though, because in Canada, for a long time, all the bulbs have been LED.
I think you can still buy halogen bulbs. It has to be hot enough. When my daughter's easy big oven broke, I had a hell if I'm buying an oven bulb for it. Like a hot enough to bake a cake.
Oh, yeah. I finally found a special one that is for like a rough service. It's like 100 watts and it's for lighting up construction sites. Which is something to spend money on because LEDs are way better for construction sites.
I have one of those twisty light bulbs. Oh, you see a foul. So I have for my lighting set in my studio. I had this giant CFL like larger than a football, like just NASA 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 oven them in bulk. And I started using them in parts of my basement because they're super in the furniture room. I put one of them in the furniture room and it's massive.