In this chapter I want us to set up our database and finally connect to NEON using Drizzle ORM. So we can close what we have opened in our editor and I want you to focus on NEON website. So head to neon.tech, you can use the link in the description or simply Google neon database and go ahead and create a new project. So I'm going to go ahead and give my project a name of Lingo and I'm going to give my database the same name of lingo. And I'm simply going to click create project.
So now let's go ahead and copy this string. So I'm just going to go ahead and click reveal here and then you can drag the entire thing and copy or you can simply use the copy option here. And now I want to go ahead and add that to my environment keys. So let's go inside of .environment and after our clerk keys, let's add database and let's actually use the full name, database underscore URL, and let's paste the Postgres URL. So I'm gonna add it in quotes.
You don't have to, you can add it like this as well, but you can see that this weird syntax is enabled for me. So I'm just gonna wrap it in quotes. Great, So we have the database URL ready here and that is pretty much it for Neon. So it was that simple to create a database with Neon and we are going to keep this open just so we can later confirm in these tables that we have some new records added meaning that our Drizzle is successfully connected to our database. So that's it for NEON that was very simple and yes it's completely free at least at the time of me recording this so this was a completely free database and now let's go ahead and set up Neon.
So we're going to be using sorry let's set up Drizzle using Neon. So we're going to be using Drizzle ORM and in here I'm following their Drizzle ORM documentation for PostgreSQL. So let's go ahead and install Drizzle ORM and add neon database slash serverless. So that is the exact provider which we need to use Neon with Drizzle. So I can actually shut down my app for this part and install drizzle-orm and add neon database slash serverless.
And after that we're gonna have to install the drizzle kit but inside of our dev dependencies because it's not going to be used for production it's only going to be used for our development purposes so that we can easily set up and see our studio and also do things like push to the database right so great after we have Drizzle ORM and Neon Database Serverless let's install Drizzle kit in the dev dependencies and once we have that let's go ahead and do the following. So we're gonna have to run some commands like npx drizzle-kit studio and drizzle-kit push Postgres, but this is a longer command to write every time so I recommend that we do the following let's go inside of our package.json here and let's add some scripts which will save us some time in the future. So I want to add database studio here, which is going to be npx drizzlekit studio. And then let's also add database push, which is going to be npx drizzlekit push posgres. So first things first, let's try out the database studio and see if it's working.
So I'm gonna go ahead and run npm run db studio and there we go you can see how that runs the ntx drizzle kit studio but we did not configure our path and this file does not exist yet so we have to configure that first before we can actually run some commands here. Let's go ahead and create a folder called database. So I'm going to go ahead and I'm just going to create in the root of my application a new folder called database. Inside of that folder I'm going to create a new file drizzle.ds. In here I'm going to import Neon from Neon database serverless package, which we've just installed.
And I'm going to import Drizzle from drizzle.orm.neon.http. And then let's import... Well, we cannot do that yet. We have to import schema now, but we don't have the schema yet. So for now, let's simply do this.
Const SQL is neon. And then let's use process.environment.database.URL. And let's put an exclamation point at the end so we don't have any typescript errors. And whenever you're writing this just double check that you didn't misspell the database URL here and here. So just copy and paste it and confirm.
And then let's do const database to be drizzle and passing SQL inside. And let's export default database. Like this. And now we have this little TypeScript error here and this is what I'm going to do so for now I'm gonna add a TS ignore here just for this line and we're gonna see if this will actually break the code or not because this did not happen to me in the previous versions of Drizzle and Neon database serverless so I don't know which package exactly is it which causes this little TypeScript bug but I'm pretty sure it will not cause any issues in actual function of this database of course. So I'm just gonna add this for now and then later we're gonna see and actually try and debug what version causes this and can we fix this by manually adding some types perhaps.
But during the initial development of my Lingo app, this did not happen, this TypeScript error. So for now, I'm just gonna add this. You, for example, in the future might not even get this error. Great, so now that we have our Drizzle set up, let's go ahead and create a new folder in the database folder called schema.ts. Inside of this schema folder, I wanna create my courses table.
So let's export const courses and let's add pgtable from drizzleorm.pgcore and let's call this courses. Let's give it an ID of serial and make sure you import it from pgCore as well. So our ID is going to be named ID, it's going to be a type of serial and primary key. Type of serial means that it will auto increment on every new entity of course. You could also add an integer from pgCore like this, but then it will not auto increment.
So Serial will auto increment, so that's why I'm using that. Yes, there also exists UUID, so you can use that if you want as well. You can explore other things which exist in the NEON documentation. But I'm going to use Serial for this tutorial. Besides the ID, we're going to have a title, which is a very simple text from PGCore as well.
And it's gonna be called title in the database as well. And we're gonna add a not null rule here, meaning that it is required. And let's also add an image source which is also going to be a text and this is the cool thing about defining drizzle tables so it allows us to use camel case here in JavaScript so when I write this in JavaScript I'm going to access course.imageSource using the capital S like this but in the database I want this to be saved like this. So I can just as easily do that. Great.
So that's going to be not null. And there we go. Now we have our first table here. So let's head back inside of our drizzle.ts here and let's do what I wanted to do, which is import everything as schema, rom.schema. And then let's extend our database here by passing in the schema as the second argument inside of an object.
This will allow us to use the Drizzle query API, which is similar to Prismas, which is gonna make it easier for us to get introduced from Neon because if you watched my previous tutorials you know that we use Prisma a lot so this is going to be just a stepping stone for us here so we don't have to go immediately into raw SQL let's call it that of course it's not raw SQL but to us coming from Prisma it might look like that so using the queries API, which Drizzle team has developed is gonna help us a lot in this tutorial. And for that, we need to add this SQL schema here. You can of course find all of this in this documentation which I'm using here right now so let me quickly try and find the queries here and there we go you can see how they've added the schema here and then you can use the query API like find many for example. Great! Now that we have this let's create the last file we need which is the drizzle config TS.
So I'm going to close everything here and in the root of my application I'm gonna create a drizzle.config.ts and in here I want to go ahead and install a package called .env let's actually just install .env like this Now let's go ahead and import .environment.config and let's import type config from drizzle kit. Now let's export default schema to go to .slash database schema.ts out is going to be .slash drizzle driver is going to be Postgres and db credentials are going to be connection string process.environment.database underscore url and you can see that I have a little typo here so let's just finish wrapping this so let's add satisfies.config so I have a little typo here So make sure you don't do that as I did. So I have database URL, it should be database URL. So as always, just double check that it is exactly what's written in your environment file and paste it here. Great, and Now that we have this, let's go ahead and do the following.
Let's open our terminal here and let's try and push our new table inside the NEON database. So we're gonna use our npm run database push here to see if this is working. So there we go! It says changes applied. So it read from our drizzle config here.
Let's go ahead and open it. It knows where our schema is. So it read from our database schema here and it noticed that this courses is a new table. Then it gave us a message changes applied. So let's confirm that by going inside of our Neon console and clicking on the tables and there we go.
We have the courses table in our Neon database with an ID of integer, image source of text and title of text. If you did not get this result, here's a little thing you can confirm just in case if you think it's not your code. So if you're certain you wrote the exact same code, you can go ahead and look at my dependencies or go to the source code and take a look at the exact dependencies that I have for Drizzle ORM, for Neon Database Serverless and for Drizzle Kit. And you can install those if you think it's a versioning issue. Great!
So now that we have that, let's just confirm one more thing inside of here. And that is that our npm run database studio is working. So it says us to please install the required packages pg. So let's go ahead and do that. So npm install and I'm going to install this in the dev dependencies.
So let me try again Database Studio and there we go. So I needed to install the pg package and I've added it inside of dev dependencies because this is using the Drizzle kit which is already installed in our dev dependencies. So I assumed that the right place to install this pg would be in the dev dependencies as well. And there we go, we now have the Drizzle Studio is up and running on this domain. So let's go ahead and open it right here and there we go.
We have our courses, we can even choose between different schemas here. So let's go ahead and try and add a record here so I'm going to go ahead and give this an id of one I'm going to give it a title of Spanish and I'm going to use the image source of slash es.svg and I'm going to click save and there we go. We officially have a record inside of our database. Perfect. So this is how we are going to fill our database now and then we're going to go ahead and create the courses page so when people click on this page or when they click on the back arrow I don't have the app running right now so it's giving this connection refused error so that's the next page we have to do the page where users will choose which course they want to learn and that page is going to load this table, so this exact table.
Great great job, so that's what we're going to do in the next chapter.