What I want to do next is establish a connection between our application and our database. And for that I'm going to be using MySQL on PlanetScale. That being said, you don't have to use PlanetScale and you don't even have to use MySQL for this project. I do recommend that you choose a relational database rather than a non-relational database, but if for any reason you want to, you can use MongoDB, you can use PostgreSQL from Superbase or NeonDB. But I'm going to show you how to create a MySQL database from PlanetScale.
PlanetScale offers one free forever database but it does require you to add a credit card in order to access that. So if you don't have a credit card, you have a couple of options. The best and the simplest option for you is to learn how to spin up MySQL locally. The reason why I'm not showing that in this tutorial is because there are different machines that people are watching this tutorial on. I specifically have a MacBook so I have one set of instructions on how to do that.
If you have a Windows machine you're gonna have to Google how to set up MySQL locally on Windows. If you have Linux you're gonna Google it for Linux, right? One alternative that I've heard of of PlanetScale is CockroachDB which I'm not sure if it's exactly MySQL, I'm not that familiar with it. It could be some other relational database like SQLite or something like that but nevertheless I'm going to show you how to do it on PlanetScale. If you have a credit card then you can follow these exact instructions and it's going to be completely free forever.
If you don't have a credit card go ahead and Google how to set up MySQL locally. All that matters is that you have a database URL which you can connect to. So let's go ahead to planetscale.com and go ahead and log in and then in here you're gonna see a dashboard similar to this. So I already have a database here, so I'm going to go ahead and create a new one. So click create new database here, and make sure you select the hobby option, which is free forever.
And go ahead and confirm that this says monthly cost free and let's give our database a name in my case it's going to be game hub this region can stay the same and go ahead and click create a database. So in here I'm gonna select Prisma so I get the instructions for Prisma. And I'm gonna go ahead and create my password. So click create password. And you can go ahead and copy this and save it on your machine.
And what we're gonna do next is we're gonna install a couple of packages. So let's go ahead and do the following. I'm going to go ahead and open my terminal here. I'm going to shut down my app and first I'm going to write npm install dash d prisma like this and then what I'm gonna do is run npm install at prisma slash client so what of these packages are important for you to have inside of your project so we did npm install d prisma and npm install prisma client great And now that we've done that we have to run npx Prisma init. So let's go ahead and copy this command and do npx Prisma init.
And there we go! Let's take a look at everything that was created here now. So as you can see we now have a prisma folder with schema.prisma inside and I have a couple of settings here. And I also in my .environment file where we added our clerk keys we now have this big comment here and we have a dummy database URL. So first thing I want to explain is inside of this schema Prisma if you don't have a nice syntax like I do go ahead and install an extension called Prisma and that's gonna prettify this look right here.
Now let's go ahead and select the optimized. If you're on PlanetScale go ahead and select optimized here and then you just have to go ahead and find your database URL. So if you're not using PlanetScale just find a URL similar to this right and you have to add that to your environment file. So go inside of .environment and replace this database URL with your proper database URL and it needs to be stored inside of this key right here database URL because that's what we are reading here in our Prisma schema environment file database URL. Great and now let's go ahead if you're using MySQL you have to modify your schema prisma a bit.
So I'm just going to go ahead and copy this and you can pause the screen and see what I've changed. So we have data source DB here and we changed the provider to use MySQL. The URL stayed the same, so it's database URL, but we also added a relation mode Prisma. I believe you only need this if you're using MySQL. I don't think you will need this if you're using something else but you can still add it here and we also have the generator client here which I believe stayed the same so save this file ensure that you have the database URL here and what we're gonna do next is we're gonna go ahead and run npx prisma db push.
So let's go ahead here inside of our terminal and let's run npx prisma db push. And I believe we're actually gonna get an error here because we don't have any models but that's completely okay all that matters is that we have this the database is already in sync with the Prisma schema. Great and I think that is it what we have to do yes that seems to be it You can now close this and you can go back inside of your application. So one more thing that we have to do here is we have to create our database util. So let's go ahead inside of the lib folder and create a new file db.ts.
Let's go ahead and let's import the Prisma client from at Prisma slash client which we installed a couple of minutes ago and let's do export const database to be global this dot Prisma or new Prisma client. And then let's write if process.environment node environment is not production global this.prisma is assigned to the db constant. And now let's fix this error. So let's add declare global var prisma to be prisma client or undefined. Like this.
So why are we doing this? That is because when using Next.js every time you save a file something called hot reload happens Which is what you see every time you modify a file and save it, you instantly see the changes inside of your screen. So that's called hot reload. And what that does with this little lib which we created here is creates a bunch of new Prisma clients. So what we do is if we are not in production we store that inside of global this and global this is not affected by hot reload so that way we prevent the hot reload from creating a bunch of unnecessary Prisma clients here and in production we don't do that because in production we don't use hot reload.
Great! So now what I want to do is go inside of our Prisma schema here and create our user model. So let's go ahead and write model user. Let's give it an id of type of id. That's going to be our primary key here and let's give it the default value of UUID.
Then let's also go ahead and give it a username, which is going to be a required string, which is unique. And let's also give it an image URL, which is also going to be a string and db.text, so we can store more characters inside than your usual string. And let's also add external user ID, which is going to be a string. And it's going to be unique. And let's also add bio, which is going to be an optional string and also db text so we can store more characters inside and lastly let's add created at which is date time and the default value is now and let's add updated at which is also date time with the value at updatedAt like this.
So we have an id which will be created every time this user model is created. We have a username field and the image URL field which will be populated from our Clerk webhook. So when user signs up using Clerk we're gonna fire a webhook which is gonna connect to our database and apply the username and the image URL from there. And same is true for external user id. So inside of external user id we're gonna store the id which clerk uses for that user and bio is gonna be something of our own.
So clerk is not gonna know about the bio that's gonna be our thing. Perfect! So now that we have this we are ready to push this to the database. So go inside of your terminal here and run npx prisma db push like this and that's going to connect to your database and it's going to synchronize this new user model as you can see your database is now in sync with your Prisma schema. And one more thing we have to run is npx prisma generate.
So this is going to add that to our local environment so that we can actually access this user model. And one more command I want to show you is mtxprisma studio like this. And that's going to open on localhost 5555. So let me go ahead here and paste that here and you should see something like this. So you can see that it recognizes that we have a user model.
It is of course empty, there are no rows in this table but you can see the fields, ID, username, image URL, external user ID and more. Great! So you've successfully connected to the database. You can now shut this down. What we're going to do next is build our webhook.