Drizzle ORM, NextAuth and Supabase

miljan — Tue, 03 Oct 2023 12:02:49 +0000

In my last Drizzle article, I wrote about some performance problems related to Prisma and explained why Drizzle ORM might be a better solution for you. In that article, I also provided a step-by-step guide on how to set up Drizzle ORM with NextAuth and the PlanetScale database.

At that time, we didn't have an official Drizzle adapter for NextAuth, so things were a little bit tricky. However, since we now have an official Drizzle adapter for NextAuth, there's no need to create it manually.

In this article I will show you how to set up Drizzle ORM, NextAuth, and Supabase (also tRPC if you want it) with the next-kickstart CLI in just a couple of seconds!

Next Kickstart

Next-Kickstart is an opinionated Next.js 13 (App Router) CLI toolkit that contains everything you need for developing full-stack applications.

With just one CLI command, you can add libraries like NextAuth, Drizzle, or tRPC to your Next.js project in seconds, with zero configuration. And yes, it's also ready for the edge!

It includes:

Next.js
TypeScript
tRPC
Drizzle ORM
NextAuth.js
Supabase
T3 Env
Tailwind CSS
ShadCN/ui
Zod
ESLint
Prettier

Init new app

To initialize a new next-kickstart application, run any of the following commands and respond to the command prompts:

npm



npx next-kickstart <folder name>

pnpm



pnpm dlx next-kickstart <folder name>

Add library

There is also an 'add' command. Let's say you have initialized a new next-kickstart project and initially selected to install Drizzle and NextAuth libraries. However, later on, you realize that you also need tRPC. There's no need for manual installation; all you have to do is run the following command:

npm



# npx next-kickstart add <library name>
# libraries available in add command are 
# shadcn, trpc, drizzle, nextauth

npx next-kickstart add trpc

pnpm



# pnpm dlx next-kickstart add <library name>
# libraries available in add command are
# shadcn, trpc, drizzle, nextauth

pnpm dlx next-kickstart add trpc

The documentation will be available soon. This project is being developed in public and is still in beta, so there may be some bugs. Feel free to test it if you like, and perhaps give it a star on GitHub. Contributions and pull requests are also welcome.

Happy hacking!

Drizzle ORM, Next Auth and PlanetScale

miljan — Tue, 20 Jun 2023 15:39:40 +0000

The so-called "Prisma Killer," has arrived, and that ORM's name is Drizzle. Even though Prisma provides a really great developer experience, and I personally love to use it, it also has its weaknesses.

Prisma seems not to be very performant, as said on the codedamn blog.

There is no concept of SQL-level joins in Prisma. This was one of the most shocking revelations to us. In some queries we inspected that supposedly should have used SQL Joins or subqueries, we discovered that at a low level, Prisma was fetching data from both tables and then combining the result in its “Rust” engine. This was a path for an absolute trash performance.

This has been very nice explained by Mehul Mohan in the following video on codedamn YouTube channel.

So, maybe you want to give a try to a new hot ORM, Drizzle.

What is Drizzle ORM

Drizzle is a TypeScript ORM for SQL databases. Drizzle's main philosophy is "If you know SQL, you know Drizzle ORM," as said on the project's GitHub page.

Drizzle ORM is a TypeScript ORM for SQL databases designed with maximum type safety in mind. It comes with a drizzle-kit CLI companion for automatic SQL migrations generation. Drizzle ORM is meant to be a library, not a framework.

It stays as an opt-in solution all the time at any levels. The ORM's main philosophy is "If you know SQL, you know Drizzle ORM". We follow the SQL-like syntax whenever possible, are strongly typed ground up, and fail at compile time, not in runtime.

How to use Drizzle ORM with PlanetScale and NextAuth

I've seen that many developers are struggling with using Drizzle and NextAuth together. So, let's now see how to use Drizzle along with NextJS 13, PlanetScale, and NextAuth.

Note: Drizzle, NextAuth and Supabase

If you want to use Drizzle and NextAuth with Supabase you can use next-kickstart cli. Next-Kickstart is all-in-one toolkit for developing full-stack, typesafe applications.

Cloning a Drizzle NextAuth repo

You can simple clone drizzle-next-auth repo (and maybe give it a star) using the following command

git clone https://github.com/miljan-code/drizzle-next-auth.git

After that you just need to supply .env file with database url and GitHub and/or Google client ID and secret and you are ready to go.

DATABASE_URL='mysql://username:passowrd@0.0.0.0/db' #PlanetScale

NEXTAUTH_URL='http://localhost:3000'
NEXTAUTH_SECRET=

GITHUB_ID=
GITHUB_SECRET=

GOOGLE_CLIENT_ID=
GOOGLE_CLIENT_SECRET=

Connecting Drizzle, PlanetScale, and NextAuth manually

Install necessary dependencies

npm i drizzle-orm @planetscale/database
npm i -D drizzle-kit

Create Drizzle configuration file drizzle.config.ts inside root directory of your project

To complete this step, we need to install one more dependency, and that is dotenv. Importing dotenv/config inside our drizzle configuration assures that the connection string is in the environment variable file.

npm i dotenv

// drizzle.config.ts

import type { Config } from 'drizzle-kit';
import 'dotenv/config';

if (!process.env.DATABASE_URL) {
  throw new Error('DATABASE_URL is missing');
}

export default {
  schema: './db/schema.ts',
  out: './db/migrations',
  driver: 'mysql2',
  dbCredentials: {
    connectionString: process.env.DATABASE_URL,
  },
} satisfies Config;

Add database url inside environment variable file

DATABASE_URL=your url

Create a db folder inside root directory of your project. Inside db folder create index.ts and schema.ts files. In index.ts file we are going to create a db connection. Inside schema.ts file we will obviously, create our database schema needed for NextAuth.

// index.ts

import { drizzle } from 'drizzle-orm/planetscale-serverless';
import { connect } from '@planetscale/database';

const connection = connect({
  url: process.env.DATABASE_URL,
});

export const db = drizzle(connection);

// schema.ts

import {
  datetime,
  index,
  int,
  mysqlTable,
  text,
  timestamp,
  uniqueIndex,
  varchar,
} from 'drizzle-orm/mysql-core';

export const accounts = mysqlTable(
  'accounts',
  {
    id: varchar('id', { length: 191 }).primaryKey().notNull(),
    userId: varchar('userId', { length: 191 }).notNull(),
    type: varchar('type', { length: 191 }).notNull(),
    provider: varchar('provider', { length: 191 }).notNull(),
    providerAccountId: varchar('providerAccountId', { length: 191 }).notNull(),
    access_token: text('access_token'),
    expires_in: int('expires_in'),
    id_token: text('id_token'),
    refresh_token: text('refresh_token'),
    refresh_token_expires_in: int('refresh_token_expires_in'),
    scope: varchar('scope', { length: 191 }),
    token_type: varchar('token_type', { length: 191 }),
    createdAt: timestamp('createdAt').defaultNow().notNull(),
    updatedAt: timestamp('updatedAt').defaultNow().onUpdateNow().notNull(),
  },
  account => ({
    providerProviderAccountIdIndex: uniqueIndex(
      'accounts__provider__providerAccountId__idx'
    ).on(account.provider, account.providerAccountId),
    userIdIndex: index('accounts__userId__idx').on(account.userId),
  })
);

export const sessions = mysqlTable(
  'sessions',
  {
    id: varchar('id', { length: 191 }).primaryKey().notNull(),
    sessionToken: varchar('sessionToken', { length: 191 }).notNull(),
    userId: varchar('userId', { length: 191 }).notNull(),
    expires: datetime('expires').notNull(),
    created_at: timestamp('created_at').notNull().defaultNow(),
    updated_at: timestamp('updated_at').notNull().defaultNow().onUpdateNow(),
  },
  session => ({
    sessionTokenIndex: uniqueIndex('sessions__sessionToken__idx').on(
      session.sessionToken
    ),
    userIdIndex: index('sessions__userId__idx').on(session.userId),
  })
);

export const users = mysqlTable(
  'users',
  {
    id: varchar('id', { length: 191 }).primaryKey().notNull(),
    name: varchar('name', { length: 191 }),
    email: varchar('email', { length: 191 }).notNull(),
    emailVerified: timestamp('emailVerified'),
    image: varchar('image', { length: 191 }),
    created_at: timestamp('created_at').notNull().defaultNow(),
    updated_at: timestamp('updated_at').notNull().defaultNow().onUpdateNow(),
  },
  user => ({
    emailIndex: uniqueIndex('users__email__idx').on(user.email),
  })
);

export const verificationTokens = mysqlTable(
  'verification_tokens',
  {
    identifier: varchar('identifier', { length: 191 }).primaryKey().notNull(),
    token: varchar('token', { length: 191 }).notNull(),
    expires: datetime('expires').notNull(),
    created_at: timestamp('created_at').notNull().defaultNow(),
    updated_at: timestamp('updated_at').notNull().defaultNow().onUpdateNow(),
  },
  verificationToken => ({
    tokenIndex: uniqueIndex('verification_tokens__token__idx').on(
      verificationToken.token
    ),
  })
);

Now, since we are using MySQL as our database, Drizzle Kit allows us to alter our database schema with a simple db:push command. So, we are going to add it as our script inside the package.json file.

"db:push": "drizzle-kit push:mysql --config=drizzle.config.ts"

Let's run the db:push script

npm run db:push

Ok, we've got our schema and database connection, so let's create a Drizzle Adapter for NextAuth. Inside the root directory of your project, create a lib folder, and inside the lib folder, let's create an auth folder where we are going to store our drizzle adapter (drizzle-adapter.ts) and auth options (index.ts).

In order to create a drizzle adapter we need to install one more dependency for generating unique IDs.

npm i @paralleldrive/cuid2

// lib/auth/drizzle-adapter.ts

import { createId } from '@paralleldrive/cuid2';
import { and, eq } from 'drizzle-orm';
import { accounts, sessions, users, verificationTokens } from '@/db/schema';
import type { Adapter } from 'next-auth/adapters';
import type { PlanetScaleDatabase } from 'drizzle-orm/planetscale-serverless';

export function DrizzleAdapter(db: PlanetScaleDatabase): Adapter {
  return {
    async createUser(userData) {
      await db.insert(users).values({
        id: createId(),
        email: userData.email,
        emailVerified: userData.emailVerified,
        name: userData.name,
        image: userData.image,
      });
      const rows = await db
        .select()
        .from(users)
        .where(eq(users.email, userData.email))
        .limit(1);
      const row = rows[0];
      if (!row) throw new Error('User not found');
      return row;
    },
    async getUser(id) {
      const rows = await db
        .select()
        .from(users)
        .where(eq(users.id, id))
        .limit(1);
      const row = rows[0];
      return row ?? null;
    },
    async getUserByEmail(email) {
      const rows = await db
        .select()
        .from(users)
        .where(eq(users.email, email))
        .limit(1);
      const row = rows[0];
      return row ?? null;
    },
    async getUserByAccount({ providerAccountId, provider }) {
      const rows = await db
        .select()
        .from(users)
        .innerJoin(accounts, eq(users.id, accounts.userId))
        .where(
          and(
            eq(accounts.providerAccountId, providerAccountId),
            eq(accounts.provider, provider)
          )
        )
        .limit(1);
      const row = rows[0];
      return row?.users ?? null;
    },
    async updateUser({ id, ...userData }) {
      if (!id) throw new Error('User not found');
      await db.update(users).set(userData).where(eq(users.id, id));
      const rows = await db
        .select()
        .from(users)
        .where(eq(users.id, id))
        .limit(1);
      const row = rows[0];
      if (!row) throw new Error('User not found');
      return row;
    },
    async deleteUser(userId) {
      await db.delete(users).where(eq(users.id, userId));
    },
    async linkAccount(account) {
      await db.insert(accounts).values({
        id: createId(),
        userId: account.userId,
        type: account.type,
        provider: account.provider,
        providerAccountId: account.providerAccountId,
        access_token: account.access_token,
        expires_in: account.expires_in as number,
        id_token: account.id_token,
        refresh_token: account.refresh_token,
        refresh_token_expires_in: account.refresh_token_expires_in as number,
        scope: account.scope,
        token_type: account.token_type,
      });
    },
    async unlinkAccount({ providerAccountId, provider }) {
      await db
        .delete(accounts)
        .where(
          and(
            eq(accounts.providerAccountId, providerAccountId),
            eq(accounts.provider, provider)
          )
        );
    },
    async createSession(data) {
      await db.insert(sessions).values({
        id: createId(),
        expires: data.expires,
        sessionToken: data.sessionToken,
        userId: data.userId,
      });
      const rows = await db
        .select()
        .from(sessions)
        .where(eq(sessions.sessionToken, data.sessionToken))
        .limit(1);
      const row = rows[0];
      if (!row) throw new Error('User not found');
      return row;
    },
    async getSessionAndUser(sessionToken) {
      const rows = await db
        .select({
          user: users,
          session: {
            id: sessions.id,
            userId: sessions.userId,
            sessionToken: sessions.sessionToken,
            expires: sessions.expires,
          },
        })
        .from(sessions)
        .innerJoin(users, eq(users.id, sessions.userId))
        .where(eq(sessions.sessionToken, sessionToken))
        .limit(1);
      const row = rows[0];
      if (!row) return null;
      const { user, session } = row;
      return {
        user,
        session: {
          id: session.id,
          userId: session.userId,
          sessionToken: session.sessionToken,
          expires: session.expires,
        },
      };
    },
    async updateSession(session) {
      await db
        .update(sessions)
        .set(session)
        .where(eq(sessions.sessionToken, session.sessionToken));
      const rows = await db
        .select()
        .from(sessions)
        .where(eq(sessions.sessionToken, session.sessionToken))
        .limit(1);
      const row = rows[0];
      if (!row) throw new Error('Coding bug: updated session not found');
      return row;
    },
    async deleteSession(sessionToken) {
      await db.delete(sessions).where(eq(sessions.sessionToken, sessionToken));
    },
    async createVerificationToken(verificationToken) {
      await db.insert(verificationTokens).values({
        expires: verificationToken.expires,
        identifier: verificationToken.identifier,
        token: verificationToken.token,
      });
      const rows = await db
        .select()
        .from(verificationTokens)
        .where(eq(verificationTokens.token, verificationToken.token))
        .limit(1);
      const row = rows[0];
      if (!row)
        throw new Error('Coding bug: inserted verification token not found');
      return row;
    },
    async useVerificationToken({ identifier, token }) {
      const rows = await db
        .select()
        .from(verificationTokens)
        .where(eq(verificationTokens.token, token))
        .limit(1);
      const row = rows[0];
      if (!row) return null;
      await db
        .delete(verificationTokens)
        .where(
          and(
            eq(verificationTokens.token, token),
            eq(verificationTokens.identifier, identifier)
          )
        );
      return row;
    },
  };
}

// lib/auth/index.ts

import GithubProvider from 'next-auth/providers/github';
import GoogleProvider from 'next-auth/providers/google';
import { eq } from 'drizzle-orm';
import { DrizzleAdapter } from '@/lib/auth/drizzle-adapter';
import { db } from '@/db';
import { users } from '@/db/schema';
import type { NextAuthOptions } from 'next-auth';

export const authOptions: NextAuthOptions = {
  adapter: DrizzleAdapter(db),
  session: {
    strategy: 'jwt',
  },
  secret: process.env.NEXTAUTH_SECRET,
  pages: {
    signIn: '/',
  },
  providers: [
    GithubProvider({
      clientId: process.env.GITHUB_ID as string,
      clientSecret: process.env.GITHUB_SECRET as string,
    }),
    GoogleProvider({
      clientId: process.env.GOOGLE_CLIENT_ID as string,
      clientSecret: process.env.GOOGLE_CLIENT_SECRET as string,
    }),
  ],
  callbacks: {
    async session({ token, session }) {
      if (token) {
        session.user.id = token.id;
        session.user.name = token.name;
        session.user.email = token.email;
        session.user.image = token.picture;
      }

      return session;
    },
    async jwt({ token, user }) {
      const [dbUser] = await db
        .select()
        .from(users)
        .where(eq(users.email, token.email || ''))
        .limit(1);

      if (!dbUser) {
        if (user) {
          token.id = user?.id;
        }
        return token;
      }

      return {
        id: dbUser.id,
        name: dbUser.name,
        email: dbUser.email,
        picture: dbUser.image,
      };
    },
  },
};

We also need to declare the next-auth and next-auth/jwt modules. Create a types folder in the root directory of your project, and inside the types folder, create the next-auth.d.ts file. By doing so, we can now access the token id and assign it to a session.user object inside auth callbacks.

// types/next-auth.d.ts

import type { JWT } from 'next-auth/jwt';
import type { Session, User } from 'next-auth';

type UserId = string;

declare module 'next-auth/jwt' {
  interface JWT {
    id: UserId;
  }
}

declare module 'next-auth' {
  interface Session {
    user: User & {
      id: UserId;
    };
  }
}

The next step is to add the GitHub and/or Google Client IDs and secrets inside the environment variable file, along with the NextAuth URL and NextAuth Secret. The NextAuth url is where your application is hosted, and the NextAuth Secret is any string of your choice. If you use it in production, make sure it's a strong one.

NEXTAUTH_URL='http://localhost:3000'
NEXTAUTH_SECRET=

GITHUB_ID=
GITHUB_SECRET=

GOOGLE_CLIENT_ID=
GOOGLE_CLIENT_SECRET=

If you don't know how to get GitHub or Google Client ID and secret, you should read this post.

The last step is to create an API endpoint. I'll assume that you are using an app router. Inside the app folder of your NextJS app, create a few nested folders and a route file like this: api/auth/[...nextauth]/route.ts.

// api/auth/[...nextauth]/route.ts

import NextAuth from 'next-auth';
import { authOptions } from '@/lib/auth';

const handler = NextAuth(authOptions);

export { handler as GET, handler as POST };