Now let's improve our authentication system by adding a database. So first things first, let's actually create a database and get a connection string. So I want you to Google Neon Tech and create an account on this website. Once you log in, you're gonna get prompted to create your project. You have one project and one database completely free.
So let's go ahead and create image.ai project and database name image.ai. I'm gonna go ahead and click create project and all you need to obtain from here is the connection string. So that's right here what we are seeing in front of us. So I'm going to click copy snippet right here and then I want you to go inside of your .environment.local and at the end here simply add database URL and paste your connection string. Make sure you don't misspell the database URL part.
Great! Now I want you to set up Drizzle ORM which is the ORM or query builder which we are going to use to handle Postgres. Let's go ahead and visit the Drizzle ORM documentation here. So I'm going to go ahead and click on Postgres and we are using Neon so this is the chapter we are interested in Neon Postgres I'm gonna select BUN as my package manager and first I'm gonna add drizzle-orm and that Neon database serverless so let me show you these commands right here there we go so bun add or npm install drizzle-orm and add the neon database slash serverless let's wait a second for these to install and after that we have to run a dev dependency called drizzle kit like this And let's also add one more thing. So again, bun add inside of DevDependencies here .environment.
Like this. And now let's go ahead and run ban run dev here and let's follow the instructions further so now what I want you to do is I want you to go inside of source here and I want you to create a new folder called database and inside create a drizzle.ts file and you can just copy this and paste it inside. So we're going to be using the newly installed neon database serverless package here and drizzle.orm package and we can remove this. We don't need that. And what we have to do here is change the database string.
So inside of your .environment.local, copy the name of the variable here and then paste it here. So it should be database URL right here. And now I also want you to ensure that you do an export const for the database variable because this is what we are going to use to actually query stuff inside of our schema. Great! So before we move on there is a thing I want to do.
I want to go inside of package.json and I want to add some important scripts which we are going to use to control our drizzle methods and you know various actions which we are going to do. So first one will be database generate. Let's leave it empty. Then we're going to have database migrate and last one will be database studio. Now depending on whether you're using npm or bun you're gonna write the following command.
So if you're using npm you're gonna write npx drizzle kit generate npx drizzle kit migrate and npx drizzle kit studio. If you're using bun you can replace npx with bunx but npx will work just fine great so now we have these methods so when we add something to our schema we're going to generate the migration file and then we're going to migrate and we can use this to open the studio. Now let's go ahead and before we try these methods here let's go back inside of our Auth.js documentation and in here I want to go inside... Is it in getting started? Yes, it is.
It's in getting started. Let's click on database right here and in here we have the official adapters. Let's select Drizzle because that's what we are using. So now we already have Drizzle ORM installed and we have a Drizzle kit. But what we are missing is the adapter for Drizzle from the Auth package.
So this is the one we need. Let's go inside of our terminal here. And let's do npm install or bun add at out slash drizzle dash adapter bun run dev again I think we can do safely and we already have so in here they use health drizzle URL you know in the documentation of drizzle they use drizzle database URL I think it's simply easier to just use database URL so keep it like that and we already have it so you know you can also of course use local Postgres as they are doing here but you know if you want to deploy it it needs to be hosted somewhere. Great so now what we're going to have to do is also create a config file yes that's something that I have forgotten and I think that I can just click on here and it will lead me to the documentation. Perfect.
So let's actually create the drizzle.config.ts before we move forward. And yes, the scripts which I've created actually won't work if we don't have that configuration file so you will create the configuration file in the root of your app. So not in the source folder, but in the root of your apps on the same level as the other config files. So let's create the drizzle.config.ts, like this. And then inside of here, you're going to import config from .environment, which we've installed.
And you're going to import define config from drizzle kit. Let's go ahead and precise where we are going to read the database string from, so the path for that will be .environment.local like this and then let's export the default define config with our schema pointing to ./.source/.database/.schema.ts which we are going to create in a moment. And our dialect will be Postgres because that's where we are using. And our database credentials will work on the URL and read process.environment database URL. And let's add verbose true and strict true and just add exclamation point here at the end.
You actually don't need these two but I always add them in my project to get some more information in the logs. So as always just confirm that you didn't misspell the database URL. It can happen and it's gonna cause cryptic bugs so it's just easier for yourself to copy and paste it here. Great! So just make sure you have defined the schema here properly.
So source, database, schema. So source, database and now we're going to create the schema file in a moment. I just wanted us to have the config file so that we can properly do migrations and read from that schema file. So what do we put inside of our schema file? Well, for that, we can go back inside of the ALT JS documentation here and you can actually skip these instructions, right?
Because usually you have to do this, right? You have to create your own migrate methods, you had to create your own push methods, but now you don't have to do that. Drizzle has updated and they have this simple DrizzleKit generate and DrizzleKit migrate methods. So it's much easier to do it now. Great, Now we have schemas here to follow.
So you're gonna have to visit this website so let me go ahead and show you where it is. So inside of Out.js, inside of Getting Started, Connections, Adapters, Drizzle. That's where you want to click. So we handled this, we have the environment variable, we added the DrizzleConfig file and now we click on Schemas PostgreSQL and you need to copy this entire schema here. So let's go ahead and do this.
We're also going to have to modify it slightly go inside of source database and inside create schema.ts and paste the entire thing inside and now we're going to have to remove the import for Postgres here and you're also going to have to remove the connection string and remove this. Right, so we don't need this. You also don't need the database from here at all. So you can go ahead and remove drizzle. Import as well.
This one. You don't need it. So just leave. Let's see where the adapter account type is used. All right.
It's important that we only leave things that are used here. Great. That's because we handle the connection string inside of a separate file right here, right? So no point in doing that twice. In the schema, I just want to hold my schema.
That's it. So this schema is necessary for NextAuth to be able to insert users in your database whenever they log in. Be that using OAuth so it's also going to create a relation with the account or using credential login. So for credential login we're gonna have to extend the user table. Great!
So once we've added our schema as written here what we have to do is we actually have to push this to our database. Right now if you go inside of your neon console here and click on the tables you're gonna see that they are empty right so I have not pushed anything inside of here and let's go ahead and do the following so again let's just confirm that inside of your where is it Your drizzle.config.ts. You're pointing correctly at schema, you're using the Postgres SQL dialect, and you have the correct database URL. If all of that is correct, you're gonna want to do the following. Shut down your app and then simply run bun run database and first of all you're gonna use generate.
There we go. So five tables account, authenticator, session, user and verification token and you can see your SQL migration file. So if you go ahead and go inside of your new Drizzle folder, you now have the migration file right here. And now that we have the migration file, we can run the second command which we've added. So, von run database migrate.
And let's just wait a second. And there we go. So it looks like I have some warning here but it looks like it applied successfully. So let's go ahead and check it out now. So I'm gonna go ahead and refresh my tables now here.
And now I should still have no data, but I should at least have the tables ready. And I do. You can see that all of my tables are inside. Perfect. And here's another thing I want to do.
So inside of terminal here, I wanna try and run bun run database studio because I should also see those tables in my studio but if I try and run it looks like there is no error happening now it seems so let me go ahead and try and open this if I click open looks like it opens without any problem but in my previous versions of Drizzle I had a slight error so if you have the same error my error was that the PG was missing so if you have that here's how to fix it right looks like right now it seems to be working just fine. But in case you got an error after running this command go ahead and simply do bun add in dev dependency postgres pg like this. So this was the previous fix, right? Or npm install the pg, right? One of those.
And then try bun run database studio again. And if you get this message, if you see the link, go ahead and open the link and you should see all the tables which we have just you know migrated to neon. There we go. So that works just fine. Now we have to configure our adapter setup.
So let's go ahead and let's go back inside of auth.ts right here. So now we're gonna have to extend this a bit. So besides having the providers, we're also gonna have to add our Drizzle adapter. So we installed this package previously if you don't remember where is the instructions right here in the beginning we added this package. So now that we have the drizzle adapter the drizzle adapter will be able to insert users on our behalf because we're going to provide drizzle adapter with our database constant which is connected to our SQL string.
So let's go ahead and do that now. Let's also prepare the import for database. So database from at slash database drizzle like this. And then above providers, we can add adapter to be DrizzleAdapter and pass in database. And in my original code this is the only thing I did.
So I just left it like this. But as you can see, you can be very specific with your schema here. So sessions table is optional and only required if you're using the database strategy, verification tokens is optional and only required if you're using a Magic-Link provider. So you can decide which ones you want and which ones you don't. It looks like they've done some improvements since I've done this because I did not have these options to remap tables.
So it looks like you can extend this and you can manually define the users table to be your users which you can import directly from database schema, right? Like this. But it looks like if you manually define like this you also have to manually define the accounts table to be accounts. So I just want to try this out together with you. So yeah looks like you at least need the users and the accounts table.
Those are the minimum requirements it seems. But let's try and not modify anything in the beginning. Right So I'm not going to import anything from the schema and I will assume that the way they instructed us to create the schema which we've pasted here and already migrated. Where is it? My DB schema here.
I will assume that these names, user, account, and session, all of those things are the things that the Drizzle adapter will read by default. I'm going to assume that. In here they have your migration tutorial, right? But we no longer need this script because as I said, Drizzle has updated and now they have these really cool, you know, DrizzleKit methods such as generate and migrate. So yes, usually you have to write your own migration script but now you just run, you know, drizzleKitMigrate and we even map it to an even shorter method here.
There we go. So let's try it out. So how do we try it out? Well, we need two things running now. In your first terminal, go ahead and run bunrun dev.
And in your second, run bunrun database studio. So you need to have your database opened here in one instance like this and let me close the other one and now go back and visit your localhost 3000 here. So just refresh your page, ensure that everything is running smoothly. You know, you can switch between these terminals and I recommend you actually keep inside of the terminal where you run your localhost, because in here you can see any errors if they happen on the server. So what we're going to do now is attempt to create an account and after that we should have one user and one account connected to that user.
Let's try it out. Inside of here I'm going to go ahead and go to first of all if you are already logged in go to slash out slash API slash out sign out. First of all, go here and sign out. That's what you want to do. And then go ahead and go to API out sign in.
And let's go ahead and let's sign in with GitHub and now we're gonna see whether this works or not. So I am definitely logged in. I am already authorized. So I didn't get that. If you got that GitHub screen, you know, that's completely fine.
I didn't get it. And now let's go ahead and let's check our Drizzle Studio and let's see what happens. I will refresh and there we go. We have one user here, we have one account from GitHub which is connected to that user right here and we even have a session because we did not define how we're going to handle our sessions, right? We have the database strategy and we have the JSON Web Token strategy.
So because we didn't define it automatically created the session for us. Amazing! That's exactly what we wanted to achieve here. And one last thing I'm going to show you in case you were not able to do this for whatever reason, it might be you know you can see that these things change every day, they add some new updates. So if you want to use the same versions that I do, Here are my dependencies.
So the Auth Drizzle adapter is 1.4.1. The Neon Database Serverless is 0.9.4. My Drizzle ORM is 0.31.4. And inside of my Dev Dependencies, my drizzle kit is 0.22.8. So this is just in case you know you can't get it to work no matter what you do.
It might be that they've messed up something in the new versions but I highly doubt that. This seems to be very stable for me at the moment. Great! So we now improved our authentication in a way that this user is actually stored in the database. So let's just confirm one more thing.
If I go ahead and sign out now, of course what we want to confirm is that no two users are created, right? So if I log in with the exact same user, all it should do, it should read from my existing account. Which means if I go inside of Drizzle Studio and refresh here, I still only have one account, one user and one session because the exact same user logged in. Perfect. So this officially works.
Great, great job.