How I implemented Drizzle ORM with Nextauth
Tags: drizzle, nextjs, authjs, react
Published: 2023-07-10 08:51:47

Drizzle ORM has become an ever increasly popular with it's speed and type safety out of the box. 
However the community are still working on a Nextauth Adapter as you can see [here](https://github.com/nextauthjs/next-auth/pull/7165) The solution i found is from [create-t3-app](https://github.com/t3-oss/create-t3-app) 

> Disclaimer: i didn't come up with the full solution i am just bringing more awareness to the topic 

### Table of contens
- [The problem](#the-problem)
- [The solution i found](#solution)
    - [The adapter](#the-adapter)
    - [Schema declaration](#schema-declaration)
    - [Defining the config](#defining-the-config)
    - [Defining the connection](#defining-the-connection)



## The problem 
The problem with using Drizzle with Nextauth that this is no native adapter, this is still in the works but for now you have to create your own, which in some sense can be beneficial to your project as now you have a little more control 


## Solution 
So I ended up modifying existing solution from the [create-t3-app](https://github.com/t3-oss/create-t3-app) check out the [pull request](https://github.com/t3-oss/create-t3-app/pull/1461) for drizzle implementation in the stack.
This solution is MySQL with a Planetscale database connection 

> Disclaimer: I do not take credit for this, I am just sharing what I found, this solution will also need to be modified per project to work with your stack 


### The adapter
`next-auth-drizzle-adapter.ts`
```ts 
import { and, eq } from 'drizzle-orm';
import { type GetServerSidePropsContext } from 'next';
import {
  getServerSession,
  type NextAuthOptions,
  type DefaultSession,
  type AuthOptions,
} from 'next-auth';
import { type Adapter } from 'next-auth/adapters';

import { env } from '@/env.mjs';
import { db } from '.';
import * as schema from './schemas';

/**
 * Module augmentation for `next-auth` types. Allows us to add custom properties to the `session`
 * object and keep type safety.
 *
 * @see https://next-auth.js.org/getting-started/typescript#module-augmentation
 */
declare module 'next-auth' {
  interface Session extends DefaultSession {
    user: {
      id: string;
      // ...other properties
      // role: UserRole;
    } & DefaultSession['user'];
  }

  // interface User {
  //   // ...other properties
  //   // role: UserRole;
  // }
}

/**
 * Options for NextAuth.js used to configure adapters, providers, callbacks, etc.
 *
 * @see https://next-auth.js.org/configuration/options
 */
export const authOptions: NextAuthOptions = {
  callbacks: {
    session: ({ session, user }) => ({
      ...session,
      user: {
        ...session.user,
        id: user.id,
      },
    }),
  },
  adapter: DrizzleAdapter(),
  providers: [
    
};

/**
 * Wrapper for `getServerSession` so that you don't need to import the `authOptions` in every file.
 *
 * @see https://next-auth.js.org/configuration/nextjs
 */
export const getServerAuthSession = (ctx: {
  req: GetServerSidePropsContext['req'];
  res: GetServerSidePropsContext['res'];
}) => {
  return getServerSession(ctx.req, ctx.res, authOptions);
};

/**
 * Adapter for Drizzle ORM. This is not yet available in NextAuth directly, so we inhouse our own.
 * When the official one is out, we will switch to that.
 *
 * @see
 * https://github.com/nextauthjs/next-auth/pull/7165/files#diff-142e7d6584eed63a73316fbc041fb93a0564a1cbb0da71200b92628ca66024b5
 */

export function DrizzleAdapter(): Adapter {
  const { users, sessions, accounts, verificationTokens } = schema;
  return {
    createUser: async (data) => {
      const id = crypto.randomUUID();

      await db.insert(users).values({ ...data, id });

      const user = await db
        .select()
        .from(users)
        .where(eq(users.id, id))
        .then((res) => res[0]);
      // eslint-disable-next-line @typescript-eslint/no-non-null-assertion
      return user!;
    },
    getUser: async (data) => {
      const user = await db.select().from(users).where(eq(users.id, data));
      return user[0] ?? null;
    },
    getUserByEmail: async (data) => {
      const user = await db.select().from(users).where(eq(users.email, data));
      return user[0] ?? null;
    },
    createSession: async (data) => {
      await db.insert(sessions).values(data);

      const session = await db
        .select()
        .from(sessions)
        .where(eq(sessions.sessionToken, data.sessionToken));
      // eslint-disable-next-line @typescript-eslint/no-non-null-assertion
      return session[0]!;
    },
    getSessionAndUser: async (data) => {
      const sessionAndUser = await db
        .select({
          session: sessions,
          user: users,
        })
        .from(sessions)
        .where(eq(sessions.sessionToken, data))
        .innerJoin(users, eq(users.id, sessions.userId));

      return sessionAndUser[0] ?? null;
    },
    updateUser: async (data) => {
      if (!data.id) {
        throw new Error('No user id.');
      }

      await db.update(users).set(data).where(eq(users.id, data.id));

      const user = await db.select().from(users).where(eq(users.id, data.id));
      // eslint-disable-next-line @typescript-eslint/no-non-null-assertion
      return user[0]!;
    },
    updateSession: async (data) => {
      await db
        .update(sessions)
        .set(data)
        .where(eq(sessions.sessionToken, data.sessionToken));

      return db
        .select()
        .from(sessions)
        .where(eq(sessions.sessionToken, data.sessionToken))
        .then((res) => res[0]);
    },
    linkAccount: async (rawAccount) => {
      await db
        .insert(accounts)
        .values(rawAccount)
        .then((res) => res.rows[0]);
    },
    getUserByAccount: async (account) => {
      const dbAccount = await db
        .select()
        .from(accounts)
        .where(
          and(
            eq(accounts.providerAccountId, account.providerAccountId),
            eq(accounts.provider, account.provider)
          )
        )
        .leftJoin(users, eq(accounts.userId, users.id))
        .then((res) => res[0]);

      return dbAccount?.users ?? null;
    },
    deleteSession: async (sessionToken) => {
      await db.delete(sessions).where(eq(sessions.sessionToken, sessionToken));
    },
    createVerificationToken: async (token) => {
      await db.insert(verificationTokens).values(token);

      return db
        .select()
        .from(verificationTokens)
        .where(eq(verificationTokens.identifier, token.identifier))
        .then((res) => res[0]);
    },
    useVerificationToken: async (token) => {
      try {
        const deletedToken =
          (await db
            .select()
            .from(verificationTokens)
            .where(
              and(
                eq(verificationTokens.identifier, token.identifier),
                eq(verificationTokens.token, token.token)
              )
            )
            .then((res) => res[0])) ?? null;

        await db
          .delete(verificationTokens)
          .where(
            and(
              eq(verificationTokens.identifier, token.identifier),
              eq(verificationTokens.token, token.token)
            )
          );

        return deletedToken;
      } catch (err) {
        throw new Error('No verification token found.');
      }
    },
    deleteUser: async (id) => {
      await Promise.all([
        db.delete(users).where(eq(users.id, id)),
        db.delete(sessions).where(eq(sessions.userId, id)),
        db.delete(accounts).where(eq(accounts.userId, id)),
      ]);

      return null;
    },
    unlinkAccount: async (account) => {
      await db
        .delete(accounts)
        .where(
          and(
            eq(accounts.providerAccountId, account.providerAccountId),
            eq(accounts.provider, account.provider)
          )
        );

      return undefined;
    },
  };
}
```

### schema declaration
`auth.ts`
```ts
 import { relations } from 'drizzle-orm';
import {
  int,
  timestamp,
  varchar,
  primaryKey,
  mysqlTable,
} from 'drizzle-orm/mysql-core';
import { type AdapterAccount } from 'next-auth/adapters';

export const users = mysqlTable('users', {
  id: varchar('id', { length: 191 }).notNull().primaryKey(),
  name: varchar('name', { length: 191 }),
  email: varchar('email', { length: 191 }).notNull(),
  emailVerified: timestamp('emailVerified', { mode: 'date' }),
  image: varchar('image', { length: 191 }),
  created_at: timestamp('created_at').notNull().defaultNow(),
  updated_at: timestamp('updated_at').notNull().defaultNow().onUpdateNow(),
});

export const usersRelations = relations(users, ({ many }) => ({
  accounts: many(accounts),
}));

export const accounts = mysqlTable(
  'accounts',
  {
    userId: varchar('userId', { length: 191 }).notNull(),
    type: varchar('type', { length: 191 })
      .$type<AdapterAccount['type']>()
      .notNull(),
    provider: varchar('provider', { length: 191 }).notNull(),
    providerAccountId: varchar('providerAccountId', { length: 191 }).notNull(),
    refresh_token: varchar('refresh_token', { length: 191 }),
    access_token: varchar('access_token', { length: 191 }),
    expires_at: int('expires_at'),
    token_type: varchar('token_type', { length: 191 }),
    scope: varchar('scope', { length: 191 }),
    id_token: varchar('id_token', { length: 191 }),
    session_state: varchar('session_state', { length: 191 }),
    created_at: timestamp('created_at').notNull().defaultNow(),
    updated_at: timestamp('updated_at').notNull().defaultNow().onUpdateNow(),
  },
  (account) => ({
    compoundKey: primaryKey(account.provider, account.providerAccountId),
  })
);

export const accountsRelations = relations(accounts, ({ one }) => ({
  user: one(users, { fields: [accounts.userId], references: [users.id] }),
}));

export const sessions = mysqlTable('sessions', {
  sessionToken: varchar('sessionToken', { length: 191 }).notNull().primaryKey(),
  userId: varchar('userId', { length: 191 }).notNull(),
  expires: timestamp('expires', { mode: 'date' }).notNull(),
});

export const sessionsRelations = relations(sessions, ({ one }) => ({
  user: one(users, { fields: [sessions.userId], references: [users.id] }),
}));

export const verificationTokens = mysqlTable(
  'verificationToken',
  {
    identifier: varchar('identifier', { length: 191 }).notNull(),
    token: varchar('token', { length: 191 }).notNull(),
    expires: timestamp('expires', { mode: 'date' }).notNull(),
  },
  (vt) => ({
    compoundKey: primaryKey(vt.identifier, vt.token),
  })
);

```


### Defining the config
something to note here is that when connecting to a Planetscale database you need to change this prama at the end of your string from `?sslaccept=strict` to `?ssl={"rejectUnauthorized":true}` so that you can actually connect to your database
`drizzle.config.ts`
```ts
import 'dotenv/config';
import type { Config } from 'drizzle-kit';
import { env } from './env.mjs';

const config: Config = {
  schema: './src/db/schemas/',
  out: './src/db/migrations',
  driver: 'mysql2',
  dbCredentials: {
    connectionString: env.DATABASE_URL,
  },

};

export default config;
```

### defining the connection 
`index.ts` 
```ts
import * as schema from './schemas';
import { connect } from '@planetscale/database';
import { drizzle } from 'drizzle-orm/planetscale-serverless';
import { env } from '@/env.mjs';

// create the connection
const connection = connect({
  url: env.DATABASE_URL,
});

export const db = drizzle(connection, { schema });
```


## Conclusion 
If you've made it this far thank you for reading I hope this has made intergrating Nextauth with Drizzle ORM a little easier