Create a Drizzle Schema

[00:00:00]
>> Brian Holt: So, what I want you to do now, open your project and please run these two statements. We're going to install Drizzle ORM. We're going to do the Neon serverless driver. This is, you could use like the PG package as well. This is just one way that you can query a database. I like, obviously Neon database serverless. It's done, it's SQL over HTTP as opposed to TCP.

[00:00:27]
Most of you probably don't care, but it's very conducive to a serverless environment. We're going to end up in Vercel, which is mostly kind of a serverless environment, so all the pieces all kind of fit together here. I will say it comes at the cost of some performance, right? The HTTP is a little bit going to be slower, but you don't have to worry about connection pooling or anything like that.

[00:00:46]
It all kind of gets absorbed in the complexity here. Okay, so you'll install those. And then you'll also install these dev dependencies, Drizzle Kit. This is the part that actually like creates and manages migrations for you. And then we're also going to use Drizzle Seed, which is going to allow us to generate seed data, which is kind of nice as well. Okay, so now that we have both of those.

[00:01:17]
Yeah, and again, every time you install, you're going to see this loose side React warning. I wish I could fix it for you, but I cannot. Okay. Did I cover all of this? Drizzle Kit is the CLI commands. You are definitely welcome to use the PG or the Postgres.js packages. Those are like ways of querying Postgres. They will technically run faster, or like inevitably, but you have to worry about things like connection pooling and like for serverless environments of like.

[00:01:50]
Creating a Postgres connection is expensive, but like maintaining one is not. That's what the serverless versus these two packages differ. If you have like one server that's just like constantly connected to a Postgres server, use one of the PG or the Postgres packages. But if you're in like Lambda or Vercel or something like that, the Neon serverless server works really well for that.

[00:02:15]
I think I explained some of that over there. Yeah, that's perfectly suited for Vercel's serverless architecture. Okay. So, normally you'd go sign up for Neon, create a project, and you'd go get a .env file, you have already done that, right? You got that when you got your Stack Auth credentials. So you should already have a database URL. Let's look at my projects and just make sure that I'm not lying to you.

[00:02:46]
You should have, you should already have this in there, right? If not, just go grab it from Neon. It's here. You always click just connect up here, and there it's just right here. And there's a bunch of different ways you can grab it as well. So that all works just fine. Next.js right there. There you go. This will give you actually like Next.js code for generating that, and it'll give you the .env file as well.

[00:03:31]
Okay. So let's go create our Drizzle config. Okay, root of your project, new file. Drizzle.config.ts. Import dotenv/config. I do get asked somewhat frequently of like, hey, why don't you use like Node's like built-in .env loader? Totally valid way of doing that as well. You just have to make sure that you write that into your npm scripts. Whereas if you do this, it just kind of works across every environment and you never have to worry about it.

[00:04:18]
So I kind of go back and forth on which one I use. I'm going to import defineConfig from Drizzle Kit. Okay, and we're going to export default defineConfig. And we're going to say out: ./drizzle. Schema: ./source/db/schema.ts, which is a file we're about to write. Dialect is going to be, of course, Postgres. And dbCredentials is going to be URL colon process.env.DATABASE_URL.

[00:05:13]
I have this null assertion, you can actually just drop that all together, I think. Oh yeah, you know what? Because it's going to say like, well, this could be null and so TypeScript is going to get mad at you, which is why I think this was originally there, but then like it doesn't like these in Biome. So, here's how you get around it. You say assert from Node assert.

[00:05:53]
Process.env.DATABASE_URL, and you say you need a database URL and that solves everything. Just kind of like a little pro tip here. What assert does is like if this is undefined, it throws, right? And it says like you don't have a database URL, so like this won't start without a database URL so it's fine that we would throw in that particular case, right?

[00:06:11]
And then what's nice about this is like, it takes care of all of your TypeScript errors after that, right? So this is generally what I do. Some people don't like that, they say it's an anti-pattern, and I say that they're wrong. So, this is what we're going to do. Makes sense? Any questions? All right. Let's head into source, and we're going to make a new folder here inside of source.

[00:06:50]
And we're going to call it db. And we're going to create a new file in here called schema.ts. So some people will put like a schema per file, some people will put all the schemas in one file. I'm generally a put all the schemas in one file until you have too many and then split it into multiple files. Okay, import pgTable, serial, text, timestamp, boolean from drizzle-orm/pg-core.

[00:07:31]
Okay, and then we're going to import usersSync from drizzle-orm/neon. So this is one of like the specific features that we're able to ship. We always have the same schema for all users' tables, so we just kind of give you that schema for free. So, we're going to make one table. Obviously normally a bigger app would have multiple tables. For us, we are just going to have the one.

[00:08:01]
Export const articles equals pgTable. So here we're going to describe our articles table. And here it's just like, this is like a create table statement, just written as a Drizzle. So id is going to be a serial called id and it's going to be a primary key. Title is going to be text, title and we're going to say it's never null. Slug, you know, I actually ended up not using this in this course, but we're going to leave it in there because as like one of your like take home assignments, I'm going to tell you instead of using IDs for URLs, you should go use slugs, which would be cool.

[00:09:15]
So slugs are going to be not null and they're also going to be unique. Content is text and called the content and not null. ImageUrl, text, image_url. So this is one of the things I do like about Drizzle. Like, you might be looking at like id and title and slug and all of these, I'm like, why am I typing slug twice? That's very annoying, right? But the image URL is a case where like, we want the name of the value in JavaScript, or the name of the key in JavaScript rather, to be imageUrl camel case, whereas we want the name of the column to be, what is that?

[00:09:47]
Snake case, snake case, yeah. Which is like the underscore between them. And so you can call them different things, and I love that you can refer to them differently. And in my case, I'm like, I'm just applying one casing scheme versus another, but I've had it like, I've had extremely long names of columns that I'll have like be much shorter in JavaScript because they allow you to call them different things and refer to them differently in Postgres versus in Drizzle.

[00:10:15]
I think it's, well, it's annoying to have to type this twice. You do this once, then you forget about it, right? I find it nicer that it's customizable. Published, again, we're not going to be looking at published today, but one of your take home tasks is to go do published versus unpublished. Published, default false and not null. AuthorId, it's going to be text, author_id.

[00:10:56]
And this is going to be not null, because someone has to have written it. But here's my favorite part of like, where Neon works really well with this is we can say references. And you just give it a function of what it references, and it's going to be referenced to something that exists in usersSync.id. So let's talk about this for just a second. Right, this is what you call foreign key, right?

[00:11:23]
So it's going to say, let's just like look at it in Neon because I think that'll be more illustrative. If you go to the tables here. So here, we're in the neon_auth schema. Keep in mind that you have a public schema and a neon_auth schema. It refers to this id. So if Brian writes an article in the article table, it's going to have a foreign key that references something that exists in the usersSync table, right?

[00:11:51]
And I love that making foreign keys is this easy because I can never remember the syntax myself. That's generally speaking, like all the specific pgTable stuff I can never remember, but Drizzle makes it very simple. You want to make changes to the user schema, would you make those changes in Neon directly at the column and then that will cascade to this schema that is being imported here?

[00:12:16]
It's a good question. You can't modify the neon_auth schema because that's what it's driven by, Stack Auth. But to, I think to answer your question, like how do I put more data on my users, right? And that's, you can do that in Stack Auth, like it, can I look at this? Yeah, client read metadata, so you can actually like, it'll pass in like a JSON object that you can put like address and all that kind of stuff in, but that's all handled via Stack Auth, yeah.

[00:12:53]
Yeah, client metadata, I think that's where it goes, it goes in one of these metadata fields. I don't remember which one. Okay, good question. But the nice thing is like that still won't modify this, it'll just come off of like a, like usersSync. One of these raw JSON, I think it'll be in this field. Okay, so that's how you do foreign keys. CreatedAt, this will be a timestamp.

[00:14:07]
So createdAt is a timestamp. It is created_at. And the mode is string. And default now and it is not null. And then we're going to do an updatedAt, but the nice thing is we can just do updatedAt, updated_at. And that's it. So mode string here, you can ask it for if you put date here, I think is what it is. Yeah, and it'll give you back a date object. We're not using any like date comparisons in here, we're letting the database do all of the sorting of dates.

[00:14:46]
So we're only just dumbly displaying whatever string is there. So we're doing string mode. Okay, const schema equals articles, so we're just making an object out of this, because we'll export this, right? This would be like if I had another, I don't know, images table, right? It would be in here and then we could import individual schemas. And then we're going to export default schema.

[00:15:38]
And then again, something I really like about this, export type Article equals typeof articles.$inferSelect. And then the same thing we call this like NewArticle or something like that. Articles.$inferInsert. This is something that Drizzle does for you, which I find very cool. Now we can use this article, it's a type that like this is everything that we just wrote, but it's a perfect TypeScript type that reflects this.

[00:16:12]
If I change this so that this is no longer, I don't know, I just, let's just say we add something new. Now, if we go look in here, it's just, it's just populated, it's already there. One of the really nice things that Drizzle does for you is like it makes authoring the schema and maintaining the types to be the same thing, which is perfect. It's exactly what I want to do, right?

[00:16:32]
I want to write this once, I don't have to write it twice. The amount of times I've had to go and like manually, like maintain type definitions for my database is, is out of control, right? Like I never want to do that again. I'm so happy it does this for me. Perhaps the biggest reason why it's either that or migrations are the two reasons that I use Drizzle the most.

[00:16:56]
And like what I love about this is like. For some of you, I imagine many of you, this might be the first time you're looking at Drizzle, right? You can probably read this okay. If you, if you're familiar with create table syntax, if you've ever seen a create table statement before, this looks extremely familiar. Like you wouldn't even necessarily have to like look up how to do this.

[00:00:00]
You'd be like, oh okay, I guess I need to add a count, and I'm assuming I can do like an integer something like that. And this would be, I don't know, I gotta call it something, so count. Lo and behold, didn't have to look at the docs, didn't have to know anything about Drizzle, and you were just able to kind of infer how this works.