In this chapter, we're going to set up our database. We're going to be using PostgreSQL, which we are going to obtain through NEON database. After we establish our database and have a proper connection string, we're going to integrate Drizzle ORM. After that, we're going to create our first database schema which will be the schema for our users. We are then going to learn how to migrate those changes to our database on Neon and we're also going to learn how to use Drizzle kit which is very useful because it has a Drizzle studio which will allow you to locally browse your database.
So before we move on Why Drizzle ORM? So in previous tutorials of mine, I've used Prisma because I really, really like its querying, right? So this picture right here is not Prisma. This is also Drizzle. But I wanted to show you this because this is a very nice type of querying.
It reminds me of Mongoose, which is very simple, right? You database query users find many and include posts, right? Very simple. But there are a couple of problems with that. First of all, why Drizzle ORM?
It is the only ORM with both relational, which is this, and SQL-like query APIs. This is, it's SQL-like querying. It is also serverless by default, which is something Prisma is not. And this is what I really like about Drizzle, especially when it comes to making tutorials. It is forcing us to understand our queries.
What do I mean by that? So if you want to, you can use Drizzle like this. They have both relational API, which is this one. So this is an API similar to Prismas, and it's great. But to be honest, when you write this, you have no idea what you're doing, right?
You have no idea, is this a subquery? Is this a common table expression? Or is this a join? If it's a join, is it an inner join, a left join, a right join? You have no idea.
But if you opt to use SQL-like querying, you're going to have to force yourself to understand what you're doing. So just by using this left join here, I feel like we've gained more understanding of our database and our query than we would with a thousand queries of Prisma. So that's why I'm gonna be using Drizzle ORM and I'm gonna be using exclusively SQL-like query APIs, which means all of our queries are going to be like this, almost as if we were writing SQL. So let's start by creating and obtaining our connection URL from neon.tech. So go ahead and visit this website here, and go ahead and create an account.
Once you've created an account, go ahead and create your project. So I'm going to call this a new tube and I'm going to call the database the exact same name and I'm just going to click create project. I'm not going to modify any auto scaling here. I will just click confirm. And then what I'm going to do here is I'm just going to copy this right here.
I see they have some options for Next.js but we are not going to be using this. So you can just copy the normal Postgres connection string. So you can click copy snippet like this. And now let's go ahead inside of our .environment.local here and I will add a database URL. And I will just paste that here.
This is probably the most crucial environment variable key you will have. So please do not share it with anyone. As always, this is my test account. I don't care about these keys. I'm gonna remove this account afterwards.
So that's why I'm showing them to you, but please do not share this with anyone. Keep it for yourself. Great, and we're pretty much done with the Neon dashboard, but I do wanna keep it here just, you know, so we can see once we actually push our users, we can also explore the tables here. So I just want to teach you how to do that. But for now, let's go ahead and let's focus on adding Drizzle ORM.
So you can Google Drizzle ORM to get to its documentation page here. And instead of the get started here, make sure you've selected the new database guide and go ahead and select the neon right here. Great. Here you can also see the basic file structure. So this is our project root and we're going to have a database folder in which we're going to have our schema.
And we're also going to need to have a drizzle config file as well as the index of the actual drizzle. You're going to see all of that in a second. But let's start by installing the necessary packages here. So I'm gonna select the bun here just so we have the proper bun commands. We're gonna have to add Drizzle ORM, Neon Database Serverless and .environment.
And then in our dev dependencies we're gonna add DrizzleKit and TSX. Technically since we're using bun I'm pretty sure we don't need the TSX, but I'm going to follow the guide just in case we mess it up. Right. So I have prepared all of these things inside of NPM so we can see the versions. One thing I want to bring to your attention is that you might see this.
In the top here you can see that version 1.0 is currently 75% done. What does that mean? So they have a public roadmap in which they are going to publish Drizzle Kit version 1, which is a very major version. I have contacted the authors behind Drizzle to see how can I ensure longevity for this tutorial, How can I ensure that the moment I upload this video, there won't be any new breaking changes? There is one thing that's happening at the moment, which is the relational queries version two API.
And I asked the author whether I should wait for this to be released or should I use the current version. And it turns out that this is only going to be out in a couple of weeks after I published this video. So I got a green light to tell you to install this version 0.39.0. Right. So in this case, I can't really teach you the new relational queries version 2 API, but I'm pretty sure this version will be supported for long enough.
So yes, it is a bit important that you install this version if you want to follow the tutorial exactly as I do, or if you want to, you know, explore the latest version yourself. If in the future you see this being a 1.0 or something, a major change, You can explore it yourself if you want to, but if you're watching this a bit closer, I prefer you to install the same version as I do. Let's go ahead and install this one by one. I'm going to go ahead and shut down my app. So let's do bun add drizzle ORM at 0.39.0.
As you can see that was published 12 hours ago. And let's go ahead and see what else do we need to install. So we need Neon Database Serverless. So let's add that. And let me see the version for that is 0.10.4.
There we go. And then we need the dot environment. So let's add that. The latest version for that is 16.4.7. Great.
So let's just ensure we have all the necessary ones and so those three great let's add them perfect so we have those three and now let's do bun add dash development and we're gonna add drizzle kit and tsx So the drizzle kit version is 0.30.3 also published 13 hours ago. And we also need TSX, which I'm not exactly sure if we need or not, but let's go ahead and install it. So TSX at 4.19.2. Great! Let me go ahead and install this inside of my dev dependencies here.
I think that should be enough. So let me go ahead and do bun run dev now. And now let's go ahead and follow the guide further. So we have already set up our database URL right here. So we don't need to do this step.
Now what we have to do is we have to use that environment variable to create our database util, right? So let's go ahead and do that. I'm going to go inside of source and I'm going to create a new folder called database and inside an index.ts let's import drizzle from drizzle or neon http And let's export cons database Drizzle process environment database URL and put an exclamation point at the end to ensure, well, to kind of override the fact that TypeScript doesn't know what this is. We know that it's right here. Great.
And of course, you can see that if you need a synchronous connection, you can use our additional connection API where you can specify a driver connection and pass it to the Drizzle instance, right? So that's if you want to use serverless specifically you can do that as well in our case Neon HTTP will do just fine Great. Now what we have to do is we have to create a table and a schema that we can push. In our case, that's gonna be users. Let's go ahead and do that.
I'm gonna go inside of database and I'm gonna create schema.ts like this. I will export const users to be pgTable from drizzle.orm.pgCore, meaning Postgres, right? And let's define the users. So I will always define my table names in multiple entity, right? So not user, it's gonna be users like this.
Let's go ahead and let's add ID. I'm going to use the UUID from PGCore. I know there's a lot of opinion around UUIDs, to its speed, to its long length, right? But I kind of find them to always be a good standard. Of course, if you know better, you can use NanoID, you can use CUID.
I believe there are some other newer versions of UUID. The thing is, I've always built my projects with UUIDs without any problems. And I like to only teach you things that I'm certain that work, right? We can experiment with NanoID some other time. If you want to, you can do that.
But I'm certain that relations and everything that I have imagined for this project work perfectly with UUIDs. So that's my only choice for that, no other reason. So it's pure preference for me. If you know any other, if you have any other preference, feel free to use that type of ID. Great, so let's go ahead and actually build our user ID here.
So we are gonna have our primary key, right? But we are almost, well, we are certainly never going to create this via any registration form. This user model will only be created through a webhook which Clerk will fire. Because of that, we are always gonna have a Clerk ID. So this Clerk ID will be what we have here for the user.
So for example, I have one user here and inside of here somewhere, there we go, I have this user ID. So that's what's going to be here, right? That's what we need so we can connect the two. So I'm going to use the text option from Drizzle ORMPG core. Make sure you don't accidentally import it from MySQL or something else.
I'm gonna call this clerk underscore ID. It's always going to be unique and it's always going to be required. Then we're gonna have a name, which is also going to be required. And then we're gonna have banner URL. Actually, how about this?
You don't know what the banner is at the moment, So I'm not going to add it yet. I think it will be a good exercise for us to learn how to modify the schema later on. How about we just add a to-do, add banner fields. Let's continue. Let's add image URL.
This is something that each user will get when they create an account with Clerk. So that is going to be text image URL, not now as well, meaning required. And then we're going to have our handy created at and updated at timestamps. So import the timestamp from PG core, created at, default it to now and set it as required. And we're gonna do the same thing as updated at, just modify this to be updated at, like this.
There we go. So one thing I want to do now is I want to add an index for the clerk ID because we are often going to search for the user in our database by the current clerk ID that we have because we will be able to use Clerks handy server utils and then we're gonna have to query the database to check, okay, this logged in user, we are a clerk, what user in the database does this entity belong to? So we're gonna do a lot of querying by clerk ID, but clerk ID at the moment is not our primary key. So technically, yeah, maybe we could have put the clerk ID to be our primary key, but I don't know. I'm not really sure about that.
So this is what I'm going to do. I'm going to go ahead and create an index on that using unique index from drizzle rmpg core and I'm going to call it the clerk id idx short for index dot on t dot clerk id like this there we go So now I have created an index on this clerk ID so I can query faster like that. Great. And I believe that we are ready to push this now. But before we can do that, let's continue learning about setting up drizzle.
So we have created a table. That's done, right? As you can see, you can also use like bar charts. If you want to specify the exact length of something, I use text, which is, I'm not sure what is the correct definition. You'll have to forgive me for that, but I use it whenever I'm not sure what I should limit the field to.
For example, I have no idea how long this image URL is going to be, so I don't have the confidence to set a length to it. I don't want my insertions to start failing all of a sudden. If you are more experienced in this part of the project, feel free to change this to Varchar and put the exact length that you are more familiar with. Great! Now we have to set up our drizzle config file.
So create a drizzle.config.ts file in the root of your project and add the following content. Let's go ahead and do that. So in the root of our project, so outside of the source folder, let's add drizzle.config.ts like this. And you can go ahead and copy this entire thing and add it here so make sure you have installed dot environment as I have in the package.json right here and one thing we're going to do is we're going to specify which environment file we are using because by default I don't think it knows that we are using .environment.local it's probably searching for .environment so what we can do is .environment.config my apologies we have to do this we have to import .environment from .environment like this and then .environment.config path .environment.local So now it will be able to read this and find the database url here. What's important here is that the out is .slash drizzle that the schema is correct path so .slash source database schema let's see slash source database schema right here perfect the dialect is PostgreSQL that's correct and this is my database url perfect that is it That's all we need for our drizzleconfig.ds.
And now what we have to do is we have to run drizzlekit push. Now, about this. This is one of many ways you can migrate your changes or apply your changes to the database. This is the most convenient method for fast iteration for solo developers or for tutorials. If you're more familiar with SQL, you might know that there are things called migration files, right?
Migration schemas. That is also possible with Drizzle. You can go ahead and read some tips here. For example, alternatively, you can generate migrations using DrizzleKit generate command and then you can apply them using drizzle kit migrate command and you can learn more about that by clicking here. So now you're gonna see how all of that works.
In this tutorial we're gonna use the most convenient method drizzle kit push. Basically anytime we make a change to our schema, we're just going to do drizzle kit push. Let's go ahead and do that now. I'm going to go ahead and do bonnex drizzle-kit push, like this. And let's see if we get any errors.
Looks like everything went fine. Link schema from the database and changes applied. It means that it correctly found our schema and it correctly pushed to this database URL which we specified right here. So if I now go ahead inside of my Neon console, You can see that I have zero tables in my public schema. But if I refresh, I now have my users and you can see all the fields which we created previously.
The ID, the clerk ID, name, image URL, and the timestamps right here. Perfect. So that works just fine. One cool thing about Drizzle, if you don't know, is that they have very powerful branching feature. If you go ahead with this project and create proper staging environments, development environments, preview environments, and production environments, this will come in very handy to you, especially because you will be able to replicate data from the production database.
Now, I know that's a bit controversial, but in fast iteration, at least in my case, that has proven to be insanely useful to have real data that you can test on without actually modifying the production database. So I just want to bring that to your attention. I think that's super cool. They also have the restore feature. They have very cool monitoring.
So you can see if something is going on with your queries. If you need to have some focus on a slow running query, they have integrations, they have these authorizations, a lot of things that you can explore here. Great. So let's go ahead and see what else do we have here. They are teaching us how to seed and query the database here.
We are going to learn how to do that in a moment but here's what I want to do. What I want to do is learn how to use the Drizzle Kit Studio. So let's go ahead and do this. Bonix Drizzle Kit Studio. Right here it's going to say that Drizzle Studio is up and running on https://local.drizzle.studio.
So if you go here, you should be able to see your studio right here and your users. And if you go ahead, you can go ahead and add a record. So this is not something that we're going to do now, but if you want to, you know, bootstrap, if you want to quickly add something, you can do that from here. Great. So that's what I wanted us to learn.
Let's go ahead and see, did we cover everything we need here? We created our Postgres database. We've set up Drizzle ORM. We have created the user schema. We've learned how to migrate the changes and we've learned how to use Drizzle Git.
And we explained why we're using the Drizzle ORM. And later on you're gonna see why I really want to use this instead of this right here. Great, so I think that wraps up this chapter. What we are going to do next is we are going to configure a local tunnel which will be able to communicate with our Clerk, Webhook right here, our application and our database. So every time a new user is created in Clerk, what we are going to do is we're going to go ahead and actually populate this table right here and then inside of our Drizzle Studio we're gonna be able to finally see some users here.
Great, great job!