So now let's go ahead and create our database connection with Drizzle ORM and Neon Postgres. So visit neon.tech or use the link in the description to obtain a free database. I'm gonna go ahead and log in and create my first project. And I'm gonna use the same name for the project and for my database here. And I'm going to press create project.
After my database has been created, I need to find my connection string. And you can find that down here. So inside of here you need this connection string. So we are gonna be using NeonDriver but it might be easier for you to copy from here. So just select the simplest Postgres option and just copy the connection string or you can use the copy button right here.
Go inside of your dot environment variables and separate this from the clerk variables so it's easier for you to see and name this database underscore URL and simply paste the Postgres connection string. So it needs to start with PostgreSQL and it needs to use this kind of format. Great. Save this file and make sure that you have no typos in your database URL. So now we're going to follow Drizzle ORM instructions to connect to NEON.
So I'm visiting the documentation here for Drizzle and I pressed on PostgreSQL right here. And the first option is actually NEON, but you can also take a look at all the other options like they have like Superbase, Node, Postgres and all the other options. So I'm gonna be using BUN so I will select BUN. If you're using NPM, feel free to leave it at NPM. So let's go ahead and first add the Drizzle ORM and the Neon Database slash serverless.
So I'm going to go here inside of my terminal and I will leave. So this is me in post-production here just giving you a quick tip. So during the recording of this course a new version of Drizzle Kit came out. It is a great update but unfortunately it doesn't work with the scripts that I have prepared for this tutorial. So because of that, I want to teach you exactly which version of Drizzle kit, Drizzle ORM and the NEON database to install and how to do that.
So looking at my package JSON right here let's go ahead and search for Drizzle. So this is the most important thing for this project. Your Drizzle kit needs to be this version. If your Drizzle kit is a version 21 that is the newer version And the newer version is great and if you want to you can learn it on your own. It has some great adjustments.
The problem is the migration scripts, the running of the database studio and all the other things are not going to work if you are using 21. So, when installing DrizzleKit, go ahead and install it like the following. If you are using npm for you it would be npm install-Drizzle-Kit and then you would add an add sign and you would specify the version so 0.20.17 like this. This is how you would install a specific version right here. If you were using bun you would do bun add-d drizzle-kit and then add a specific version 0.20.17 like this.
And lastly, I also want to show you my Drizzle ORM package and my Neon Database Serverless package. So I don't think any big changes are in those two packages but still in case you want to do the exact same version. There we go. So for my Drizzle ORM I'm using 0.30.10 and for Drizzle Zod I'm using 0.5.1 and for my Neon Database serverless I'm using 0.9.1. So I recommend that when you install this in this chapter you do the exact version installation so npm install drizzle-orm and then go ahead and type out the exact version that I have so 0.30.10 or bun add drizzle-orm point 10 or bun add drizzle or m 0.30.10 right or yarn whatever you're using just use these versions but again as I said the most important part is this.
The Drizzle kit needs to be this version otherwise none of the scripts are going to work and the config file which we are later going to create so you don't have this yet but this config file also changes. It introduces something called the dialect, which as you can see in here does not exist. But if I look at the Drizzle documentation here in the Drizzle kit and look at the configuration, they have a dialect, right? So a lot of things has changed. We can do that in the next course or in additional chapters, but I already recorded a good portion of the tutorial, so please use this version of DrizzleKit in your dev dependencies.
I also have my app running, so I will shut that down for now and instead I'm just going to add bun add drizzle orm and neon database serverless or npm install two of those packages and then we also need to add inside of our development dependencies drizzle kit so for me that's bun add dash capital D drizzle kit. For you, it might be npm install dash capital D drizzle kit. There we go. So now let's go ahead and let's follow the further instructions. So now what we have to do is we have to create our, well, let's call that root or entry point for our Drizzle connection.
So I'm going to go ahead and create a new folder and I'm going to call this... Let's see... Let's call it database. And inside of database I'm going to create a drizzle.ds endpoint. And now inside of here we can copy and paste this snippet right here so I'm going to close this and paste it here there we go I'm going to remove this because this is just the example but this is what we need so first of all I don't like single quotes so I will replace them to use double quotes and the other thing that we have to do is we have to change this environment variable so for us we don't have the drizzle prefix so what you can do is go inside of your dot environment local and directly copy the name of this variable that's the safest thing to do And you can leave the exclamation point because otherwise you get an error.
There we go. Now let's go ahead and make sure that both of these constants are exported like this. There we go. What I want to do Next is I want to start creating our schema. So let's go ahead and see how we can create schemas here.
So let's go ahead and select the schema or maybe the overview would be better. There we go. So you have a lot of options about how you can create your schemas. So you can just use everything in one file, you can have separate files inside of a schema folder or you can even have separate folders if that's what you prefer. I'm gonna keep it simple and just use the one file because it's gonna be easier for me to export everything from there using the asterisk option for my imports and then it's gonna smoothly go and align with my migration script.
So I recommend that you do the same. So let's go ahead and do that. So this is how you basically do that. We don't have to copy it from here. Instead what I want us to do is I want us to go inside of the database folder and create a new file schema.ts right here and let's go ahead and create the very first account which we can call for example I told you to create the very first account I meant to say the very first schema which is going to be the account schema.
So this is how you do it. You write export const accounts pgtable and you can import pgtable from drizzle-orm-pgcore. Pg represents Postgres So obviously if you were using MySQL, you would have different imports and you can always check that out in the documentation here. So as you can see here, we are using the PG core imports but in MySQL, you can see that it has mysql core. Great!
So make sure you work with Postgres if that's the database you chose. So let's open up pgtable and now what we do here is we also give this table a name. So I'm going to call this accounts and then we open an object as the second argument and inside of here we now write our elements. So I'm going to create an id here to be a type of text and you can also import text from Drizzle ORM PG Core. So make sure you have text imported from here.
And now we give this field a name, ID. And let's also chain primary key. That's it. Now we have an account with ID. Perfect.
So let's go ahead and add the following fields. I'm also going to add name to be text name and let's make it required by adding a not now. There's no need to add not now to the ID because primary key takes care of that. And let's also add the user ID, which is going to be text user underscore ID dot not now. So what's important to understand with Drizzle here is that this is just javascript, right?
But what's inside of strings is what's actually being written to your database here, right? So just because we have camel case here doesn't mean that that's how it's going to be stored as a table. Instead it's going to look at this value right here. Same for this, for this, and for this. Perfect.
So now that we have this I want to go ahead and I want to create a migration script and I want to add a couple of scripts inside of our package.json so that we can safely, well, push, well, generate and migrate to our Neon database. So I want to start by adding a package inside of our project called .environment and I'm going to add it as a dev dependency here. So npm install-D.environment if you're using npm. And now I found a guide on creating migration scripts right here from Neon so it uses schema migration with Neon Postgres and Drizzle ORM and we're going to go ahead and skip to the bottom here and let's go ahead and first create our migration script right here. So this is what I want to do first.
So I'm going to zoom in right and let's go ahead and go inside of our project structure and how about we create a new folder called scripts and inside let's create migrate.ts like that and now inside of here I want to import config from .environment which we've just installed and let's go ahead and run config path dot environment dot local like this so just ensure that this dot environment dot local is the actual dot environment dot local that has your database url right here So the reason I'm doing this is because this is going to be run by outside of Next.js. So Next.js is not going to run this project. We are going to run that using either Node or Bund, right? Something like that. So now let's go ahead and let's define SQL again using NEON from NEON database serverless here and let's go and call process.environment.database.URL and then let's define the database which will be drizzle from drizzle ORM neon HTTP and let's paste in the SQL.
So I just like to align this like that and then lastly let's also add the migrate script from drizzle ORM slash neon HTTP slash migrator. Like that. And then let's write const main to be an asynchronous method with a try and catch block. Let's resolve the catch first. So I want to console error and I want to write error during migration.
And let's simply print out the error and let's exit this process. And then in the try block, we're going to do await, migrate, passing the database as the first options and let's go ahead and write migrations folder to be drizzle Like that. So this will be created every time we create a migration. So let's go ahead and run this script right here. So that's important.
Perfect. So now that we have this script, what we can do is we can go inside of our package.json here and we can add some new scripts here. So I'm going to start by actually creating db.generate here and then below that we're going to have db.migrate and below that we're going to have db.studio So we're going to add three scripts here. So first of all, let's create the generate one and you can actually take a peek at this script right here. So let me go ahead and go up there.
There we go. So we're gonna copy this script and we can actually write it together, right? So let's see what we are going to need. We're going to need to call drizzle kit, which we've installed as a dev dependency. And we're gonna use generate, colon Postgres, because we're using Postgres here.
And then we're going to locate our schema in db slash schema dot ts. So that's why I told you that I recommend that you use the single file method for schema here, right? So this whole file will hold our schemas. Let's go back inside of package.json here. So we located our schema file here and now let's write the dash dash out dot slash drizzle like that so that's our out folder so let me zoom out so you can see this in one line like this there we go now let's go ahead and let's create our migrate script So for migrate script what we need is to run this folder right here scripts migrate.ts and here's the thing you cannot run this with node right because node doesn't support imports by default and it also I think doesn't support maybe some other things here well yeah I think it doesn't support TypeScript as well so here's what you can do you can either simply write bun.script-migrate.ts so that's one option or you can write TSX if you're not using BUN, right?
I think I actually give you that tip here so let's see after we created this migration script here there we go they use TSX, right? So TSX will allow you to run this script safely if you don't wanna use BUN for any reason. Right here on npm, TypeScript execute, the easiest way to run TypeScript in node.js, right? So you can just go ahead and install that using npm install tsx, or you can install it globally. Great, so instead of tsx, I'm gonna be using bun because I'm using bun for this project.
And for db studio, we're simply gonna call drizzle-kit, DB, my apologies, drizzle-kit-studio, like that. Perfect, so now that we have this three scripts, right? We have generate, migrate, and studio, we can run them in that order. So First things first, we have to generate from our schema some new migrations, and then we have to actually migrate them through the database. So let's try this out.
I'm gonna do bun run. So if you're using NPM, it's npm run for you. So bun run, database generate. And there we go. So it created one account, one table called account with three columns right here.
And you can already see that migration script here. So there we go. We have the drizzle folder and in here we have the migration script. Perfect. Now what we have to do is we have to push that table so it's visible right here in our neon console.
So right now if I go inside of tables here nothing is visible. So let's go ahead and run our second script. Let's see what it's called. So it's called dbmigrate. So let's go ahead and run that now.
Bund run database migrate and let's see what I did wrong. So script is the wrong folder. It's called scripts multiple. Let's run that again. Make sure you don't have the same mistake as I do.
And there we go. No error has been thrown which would mean that this was successfully pushed. So let's refresh the tables here. And there we go. We have accounts with id, name and user id.
And now I want to try out the last script which is to run our database studio locally. So let's try and run that. Bun run database studio and we have an error here. So first of all, we are missing the drizzle.config.json. And then we also need to do one more thing.
So first of all, let's go ahead and let's create the drizzle.config file. So I'm gonna write drizzle.config.json here. My apologies, .ts. That's what we are going to use. So drizzle.config.ts in the root of your project.
And let's import config from .environment And let's do the thing first. So Let's ensure that everything we use here comes from .environment.local. And then from here, let's import define-config from drizzle-kit. And in here, let's export default define-config. Let's target our schema to be .slash database schema.ds file driver is going to be Postgres.
Database credentials are gonna use connection string to be process.environment database underscore url verbose can be true and strict can be true as well there we go so now we have our config file so let's try running the bun script again and let's see what happens. There we go. So we have a different error now which is the missing package pg. So you can easily add that pg-D. So add it to dependencies.
There we go. And let's do bun run database studio again. And there we go. We can now finally see the studio so I'm going to close this I'm going to paste that here and there we go, we should have our studio load any minute now and we have the accounts with id, name and user id and we can add records from here if we want to. Perfect!
So let's go ahead and confirm everything here. So I just want to show you my package json because we added a couple of stuff. So I have added independencies, neon database serverless. I've added the drizzle ORM. In my dev dependencies, I've added dot environment and drizzle kit and Postgres itself.
There we go. Perfect. And if you're wondering where did I get the information about building the config file from, you can find that in Drizzle documentation here under DrizzleKit, I believe, configuration, I think. There we go, right here in the configuration. So it can be drizzle.config.ts.js or JSON.
So we are using the TS file that's where you can find that perfect, so we now have a working schema and working scripts So there's just a couple of more things that I want to do here. So first of all, I wanna go inside of here, I wanna show you something. So right now, if you want to fetch accounts, for example, you would use database.select from, and then you would import accounts, right? And if you wanted, for example, to use, let's call this accounts2, there we go. So it conflicted with this variable, That's why I renamed this.
So now let's say if you wanted to, I don't know, connect the relation to the accounts, right? You would need to add some inner join or something like that. So you would basically write SQL like query. But something that Drizzle also offers you if you wanna use that instead, is let's go ahead and find it. Is it queries right here?
Or is it, let's see. There we go. So find many, find first. So if you want to, you can use drizzle like this. So this is querying similar to Prisma.
There we go. So find many with comments. So this is how you would include a relation with the comments, right? But we're not going to do that in this project. Instead, what we're going to do is we're going to go ahead and we're going to use the SQL like querying.
So we're going to use select from users and stuff, but in case you don't like this and you yourself want to build it like this, you have to create one small modification here, and that is to import everything as schema from schema that's inside of this drizzle file here. And then you would simply add the schema here. Schema, there we go. And then what you are able to do is you would write database.query.accounts.find first, and then you can write where, right? So this is Prisma like query, right?
But we're not gonna do that in this project. We are going to use the select and then from accounts, this would be schema.accounts, right? And then we were gonna do .where and then inside of here, we would use the equals and stuff like that, right? So that's what we are going to do. So I just wanted to give you a quick tip if you wanna use it differently.
Great, so now that that is done, let's go ahead and practice the migration one more time. So inside of database schema how about we add another field here which we're actually going to need later called played ID. So let's define this as text and played underscore ID like that. So what are the steps to add this to our database? We would go inside of the terminal here, we would do bandrun database generate and then bandrun database migrate and there we go.
Bandrun Database Studio should now open up and have the plate ID here. And it does, it's right here, plate ID. Perfect. Let's refresh the Neon console. And inside of here, we have plate ID as well.
And if you take a look at your Drizzle here folder, you now have the change for that as well. If you ever want to, you can completely remove the Drizzle folder and then your migrations will start over again. Great, so I hope that kind of gave you a quick intro into Drizzle and Neon and how to do migrations and how to do the basic changes. What we're going to do next is we're going to go ahead and fully write our schema so that we can finally create some accounts and transactions. Great, great job!