How to Store Users in NextAuth with Prisma and PostgreSQL

Once OAuth is configured in our Next.js application, the natural next step is to learn how to store user data (accounts, sessions, etc.). We will be using a database adapater, specifically a Prisma adapter, with NextAuth to persist user information inside a PostgreSQL database.

Our PostgreSQL setup can vary widely depending on how we want to develop locally (i.e. hosted on our machine, in a Docker container, in the cloud etc.). For that reason, I won’t go over how to set up a Postgres database for development.

However, once we have a database up and running, we can connect to our database using psql.

Create the Postgres Database with `psql`

Once we’re in the psql command line, we can create our database.

CREATE DATABASE db_nextauth;

This should return CREATE DATABASE. Let’s double check that we did this successfully by running \list or \l.

Let’s connect to this database using \c db_nextauth.

You are now connected to database "db_nextauth" as user "postgres".

Configure Postgres Schema

Next, we’ll create 4 tables in our database: accounts, sessions, users, and verification_requests

This is the schema that NextAuth recommends in their documentation.

users: stores info about each user (name, email address, OAuth profile, etc.)
accounts: stores OAuth account info (one user can have multiple accounts if multiple OAuth providers exist for our app)
sessions: stores user session information, unused if jwt is enabled (one user can have multiple sessions)
verification_requests: stores tokens for passwordless sign in emails (one user can have multiple open verification_requests if signing into multiple devices)

If we plan on using JSON Web Tokens (jwt) for maintaining session state, then we won’t need to create the session table in our database. With JWTs, we’ll be doing all of this session management on the client-side instead of on the server-side.

Now, we can copy these commands into the postgres-# command line.

CREATE TABLE accounts
  (
    id                   SERIAL,
    compound_id          VARCHAR(255) NOT NULL,
    user_id              INTEGER NOT NULL,
    provider_type        VARCHAR(255) NOT NULL,
    provider_id          VARCHAR(255) NOT NULL,
    provider_account_id  VARCHAR(255) NOT NULL,
    refresh_token        TEXT,
    access_token         TEXT,
    access_token_expires TIMESTAMPTZ,
    created_at           TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at           TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
  );
CREATE TABLE sessions
  (
    id            SERIAL,
    user_id       INTEGER NOT NULL,
    expires       TIMESTAMPTZ NOT NULL,
    session_token VARCHAR(255) NOT NULL,
    access_token  VARCHAR(255) NOT NULL,
    created_at    TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at    TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
  );
CREATE TABLE users
  (
    id             SERIAL,
    name           VARCHAR(255),
    email          VARCHAR(255),
    email_verified TIMESTAMPTZ,
    image          TEXT,
    created_at     TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at     TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
  );
CREATE TABLE verification_requests
  (
    id         SERIAL,
    identifier VARCHAR(255) NOT NULL,
    token      VARCHAR(255) NOT NULL,
    expires    TIMESTAMPTZ NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
  );
CREATE UNIQUE INDEX compound_id
  ON accounts(compound_id);
CREATE INDEX provider_account_id
  ON accounts(provider_account_id);
CREATE INDEX provider_id
  ON accounts(provider_id);
CREATE INDEX user_id
  ON accounts(user_id);
CREATE UNIQUE INDEX session_token
  ON sessions(session_token);
CREATE UNIQUE INDEX access_token
  ON sessions(access_token);
CREATE UNIQUE INDEX email
  ON users(email);
CREATE UNIQUE INDEX token
  ON verification_requests(token);

Install Prisma and Connect Database

We need 2 packages: @prisma/client and prisma.

npm install @prisma/client --save
npm install prisma --save-dev

We can now set up our project to use Prisma.

npx prisma init

This creates a prisma folder that will hold a schema that follows the database schema we created above.

If it doesn’t exist yet, Prisma will have added a DATABASE_URL into our .env. We’ll want to replace this with a link to our local Postgres database.

# .env
DATABASE_URL=postgresql://username:password@localhost:5432/db_name?schema=public

Configure Prisma Schema

Now, we want to update our Prisma schema in prisma/schema.prisma, which is a model that mirrors the database configuration above.

// prisma/schema.prisma
generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model Account {
  id                 Int       @default(autoincrement()) @id
  compoundId         String    @unique @map(name: "compound_id")
  userId             Int       @map(name: "user_id")
  providerType       String    @map(name: "provider_type")
  providerId         String    @map(name: "provider_id")
  providerAccountId  String    @map(name: "provider_account_id")
  refreshToken       String?   @map(name: "refresh_token")
  accessToken        String?   @map(name: "access_token")
  accessTokenExpires DateTime? @map(name: "access_token_expires")
  createdAt          DateTime  @default(now()) @map(name: "created_at")
  updatedAt          DateTime  @default(now()) @map(name: "updated_at")

  @@index([providerAccountId], name: "providerAccountId")
  @@index([providerId], name: "providerId")
  @@index([userId], name: "userId")

  @@map(name: "accounts")
}

model Session {
  id           Int      @default(autoincrement()) @id
  userId       Int      @map(name: "user_id")
  expires      DateTime
  sessionToken String   @unique @map(name: "session_token")
  accessToken  String   @unique @map(name: "access_token")
  createdAt    DateTime @default(now()) @map(name: "created_at")
  updatedAt    DateTime @default(now()) @map(name: "updated_at")

  @@map(name: "sessions")
}

model User {
  id            Int       @default(autoincrement()) @id
  name          String?
  email         String?   @unique
  emailVerified DateTime? @map(name: "email_verified")
  image         String?
  createdAt     DateTime  @default(now()) @map(name: "created_at")
  updatedAt     DateTime  @default(now()) @map(name: "updated_at")

  @@map(name: "users")
}

model VerificationRequest {
  id         Int      @default(autoincrement()) @id
  identifier String
  token      String   @unique
  expires    DateTime
  createdAt  DateTime  @default(now()) @map(name: "created_at")
  updatedAt  DateTime  @default(now()) @map(name: "updated_at")

  @@map(name: "verification_requests")
}

Generate Prisma Client

Finally, we can generate our Prisma client, which is what we will use in next-auth.

This client will be aware of all the accessible tables and columns specified in prisma/schema.prisma.

npx prisma generate

Use Prisma Client in NextAuth as Adapter

With the client installed, we can import PrismaClient in pages/api/auth/[...nextauth].js.

We’ll create an instance of the Prisma client and then set it up as an adapter.

The code snippets here require NextAuth.js v4. Check out how to upgrade to version 4.

// pages/api/auth/[...nextauth].js
import NextAuth from "next-auth";
import GoogleProvider from "next-auth/providers/google";
import { PrismaClient } from "@prisma/client";

const prisma = new PrismaClient();

export default NextAuth({
  providers: [
    GoogleProvider({
      clientId: process.env.GOOGLE_ID,
      clientSecret: process.env.GOOGLE_SECRET,
    }),
    ...
  ]
  ...
});

We can now try logging in using an OAuth provider, and we should see our tables populate with user information.