Next.js, Auth.js, Drizzle ORM, SQLite, Credentials Provider Tutorial

GitHub: https://github.com/fullstackbook/next-auth-drizzle-sqlite-tutorial

Learn how to set up Authentication with Next.js, Auth.js, Drizzle ORM, SQLite, and Credentials Provider.

This setup with Credentials Provider is intended for rapid prototyping and proof of concepts. For production apps, it is recommended to use OAuth or Email authentication.

Step 1: Initialize Next.js project

npx create-next-app@latest

Step 2: Install dependencies

npm install next-auth@beta drizzle-orm @auth/drizzle-adapter better-sqlite3 bcrypt
npm install drizzle-kit @types/better-sqlite3 @types/bcrypt --save-dev

Step 3: Load environment variables

/lib/config.ts

import { loadEnvConfig } from "@next/env";

const projectDir = process.cwd();
loadEnvConfig(projectDir);

const config = {
  DATABASE_URL: process.env.DATABASE_URL!,
};

export default config;

Next.js automatically loads environment variables from .env.local, however, Drizzle Kit does not. So, we'll need to call loadEnvConfig manually.

Step 4: Define schema

/lib/schema.ts

import {
  integer,
  sqliteTable,
  text,
  primaryKey,
} from "drizzle-orm/sqlite-core";
import type { AdapterAccount } from "@auth/core/adapters";

export const users = sqliteTable("user", {
  id: text("id").notNull().primaryKey(),
  name: text("name"),
  email: text("email"),
  emailVerified: integer("emailVerified", { mode: "timestamp_ms" }),
  image: text("image"),
  password: text("password"),
});

export const accounts = sqliteTable(
  "account",
  {
    userId: text("userId")
      .notNull()
      .references(() => users.id, { onDelete: "cascade" }),
    type: text("type").$type<AdapterAccount["type"]>().notNull(),
    provider: text("provider").notNull(),
    providerAccountId: text("providerAccountId").notNull(),
    refresh_token: text("refresh_token"),
    access_token: text("access_token"),
    expires_at: integer("expires_at"),
    token_type: text("token_type"),
    scope: text("scope"),
    id_token: text("id_token"),
    session_state: text("session_state"),
  },
  (account) => ({
    compoundKey: primaryKey({
      columns: [account.provider, account.providerAccountId],
    }),
  })
);

export const sessions = sqliteTable("session", {
  sessionToken: text("sessionToken").notNull().primaryKey(),
  userId: text("userId")
    .notNull()
    .references(() => users.id, { onDelete: "cascade" }),
  expires: integer("expires", { mode: "timestamp_ms" }).notNull(),
});

export const verificationTokens = sqliteTable(
  "verificationToken",
  {
    identifier: text("identifier").notNull(),
    token: text("token").notNull(),
    expires: integer("expires", { mode: "timestamp_ms" }).notNull(),
  },
  (vt) => ({
    compoundKey: primaryKey({ columns: [vt.identifier, vt.token] }),
  })
);

The schema was copied and pasted from the Auth.js docs. We remove the notNull constraint from email, as we will only use the username for prototyping.

Step 5: Create the Drizzle DB instance

/lib/db.ts

import { drizzle } from "drizzle-orm/better-sqlite3";
import Database from "better-sqlite3";
import * as schema from "@/lib/schema";
import config from "./config";

const sqlite = new Database(config.DATABASE_URL);
export const db = drizzle(sqlite, { schema, logger: true });

Step 6: Configure Auth.js

/lib/auth.ts

import { DrizzleAdapter } from "@auth/drizzle-adapter";
import NextAuth from "next-auth";
import CredentialsProvider from "next-auth/providers/credentials";
import { db } from "./db";
import bcrypt from "bcrypt";
import { eq } from "drizzle-orm";
import { users } from "./schema";

function passwordToSalt(password: string) {
  const saltRounds = 10;
  const hash = bcrypt.hashSync(password, saltRounds);
  return hash;
}

async function getUserFromDb(username: string) {
  const user = await db.query.users.findFirst({
    where: eq(users.name, username),
  });
  return user;
}

async function addUserToDb(username: string, saltedPassword: string) {
  const user = await db
    .insert(users)
    .values({
      id: crypto.randomUUID(),
      name: username,
      password: saltedPassword,
    })
    .returning();
  return user.pop();
}

export const {
  handlers: { GET, POST },
  auth,
} = NextAuth({
  adapter: DrizzleAdapter(db),
  providers: [
    CredentialsProvider({
      name: "Credentials",
      credentials: {
        username: { label: "Username", type: "text", placeholder: "jsmith" },
        password: { label: "Password", type: "password" },
      },
      async authorize(credentials, req) {
        let user = null;
        const username = credentials.username as string;
        const password = credentials.password as string;

        if (!username || !password) {
          return null;
        }

        user = await getUserFromDb(username);

        if (user) {
          if (!user.password) {
            return null;
          }

          const isAuthenciated = await bcrypt.compare(password, user.password);
          if (isAuthenciated) {
            return user;
          } else {
            return null;
          }
        }

        if (!user) {
          const saltedPassword = passwordToSalt(password);
          user = await addUserToDb(username, saltedPassword);
        }

        if (!user) {
          throw new Error("User was not found and could not be created.");
        }

        return user;
      },
    }),
  ],
  callbacks: {
    async session({ session, user, token }: any) {
      return session;
    },
  },
  session: {
    strategy: "jwt",
  },
});

The session callback is required to add the user id to the user object.

Setting the session strategy to jwt is important for credentials provider, otherwise the user object will not be included in the session object.

Step 7: Create Auth API route

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

export { GET, POST } from "@/lib/auth";

Step 8: Extend Next Auth Type With Module Augmentation

/types/next-auth.d.ts

import NextAuth from "next-auth";

declare module "next-auth" {
  /**
   * Returned by `useSession`, `getSession` and received as a prop on the `SessionProvider` React Context
   */
  interface Session {
    user: {
      id: string;
      name: string;
    };
  }
}

It is important to import NextAuth here to fix a TypeScript error in auth.ts.

Step 9: Sign In Button

/components/signin-btn-auto.tsx

"use client";
import { useSession, signIn, signOut } from "next-auth/react";

export default function SignInBtnAuto() {
  const { data: session } = useSession();
  if (session) {
    return (
      <>
        Signed in as {session.user?.name} <br />
        <button onClick={() => signOut()}>Sign out</button>
      </>
    );
  }
  return (
    <>
      Not signed in <br />
      <button onClick={() => signIn()}>Sign in</button>
    </>
  );
}

This button uses the prebuilt sign in page from Auth.js.

Step 10: Session Provider

/components/session-provider.tsx

"use client";
import { SessionProvider } from "next-auth/react";
export default SessionProvider;

Use this session provider on any layout or component that needs to check user session.

Step 11: Use the Session Provider

/app/(dynamic)/layout.tsx

import { ReactNode } from "react";
import SessionProvider from "@/components/session-provider";
import { auth } from "@/lib/auth";

export default async function Layout({ children }: { children: ReactNode }) {
  const session = await auth();
  return (
    <SessionProvider session={session}>
      <div>{children}</div>
    </SessionProvider>
  );
}

We are using a route group to avoid putting the session provider on the root layout so that we don't force static pages to become dynamic. For example, static marketing pages. It's good to keep the option to create static pages in the future.

Step 12: Add the sign in button to the sign in page

/app/(dynamic)/signin/page.tsx

import SignInBtn from "@/components/signin-btn";

export default function Page() {
  return (
    <div>
      <SignInBtn />
    </div>
  );
}

Step 13: Create env local file

The Auth Secret can be any random string. Auth.js has a tool to generate a secret.

npx auth secret

Copy and paste the secret into .env.local.

DATABASE_URL="db.sqlite"
AUTH_SECRET="exOpw3cJVq70C8bnoJjSmW76wFRJU5Ou/pmLmN3ypxc="

Create a .env.example file for future reference.

DATABASE_URL=
AUTH_SECRET=

Step 14: Configure Drizzle

/drizzle.config.ts

import config from "@/lib/config";
import { defineConfig } from "drizzle-kit";

export default defineConfig({
  schema: "./lib/schema.ts",
  out: "./drizzle",
  driver: "better-sqlite",
  dbCredentials: {
    url: config.DATABASE_URL,
  },
  verbose: true,
  strict: true,
});

Step 15: Run the push command

npx drizzle-kit push:sqlite

This command will sync the schema to the database.

Step 16: Test the sign in page

Test the login by going to the sign in page and signing in. http://localhost:3000/signin

Step 17: Create a protected page

/app/(dynamic)/protected/page.tsx

import { auth } from "@/lib/auth";
import { redirect } from "next/navigation";

export default async function Page() {
  const session = await auth();
  if (!session) {
    redirect("/signin");
  }
  return <div>{session.user.name}</div>;
}

Test by visiting http://localhost:3000/protected.

Step 18: Create a protected API Route

/app/api/protected/route.ts

import { auth } from "@/lib/auth";

export async function GET() {
  const session = await auth();
  if (!session) {
    return Response.json({ message: "unauthenticated" }, { status: 401 });
  }
  return Response.json({ name: session?.user.name });
}

Test by sending a GET request to http://localhost:3000/api/protected.

Step 19: Add sqlite files to the gitignore

We don't want to check sqlite databases into the Git repo.

.gitignore

*.sqlite

Step 20: Ready for production?

This SQLite boilerplate is designed for rapid prototyping. Here are a few recommendations if launching to production.

  • Switch to using a production grade database like PostgreSQL.
  • Switch to using migrations instead of the push command.
  • Switch to using OAuth or Email Provider instead of Credentials Provider.
  • If using Credentials Provider, implement an email verification and password reset process.
  • Add notNull constraints to email and username.
  • Create a custom sign in page instead of using the prebuilt one by Auth.js.

References