How to set up Drizzle ORM with Auth.js in a fresh project

Drizzle ORM is n object relationship mapper – basically it maps your relational databases and all it’s entitites to objects that you can interact with to make queries without necessaarily knowing SQL (but it definitely helps).

The first thing I typically do when setting up a project is to get Auth.js working, which involves setting up a database and a basic schema (your database blueprint)

The documentation was confusing so I wrote this to help guide you (and myself in the future):

Prerequisites: I’m assuming you have Next.js project setup and next-auth@beta installed and set up, if not – go here

Now for setting up the database.

1. Install everything you need:
npm i drizzle-orm pg dotenv
npm i -D drizzle-kit tsx @types/pg
npm install @auth/drizzle-adapter
   
2. Add your connection URL to your env variables:
   DATABASE_URL=
   
3. Set up your project structure:
   📦
   ├ 📂 drizzle
   ├ 📂 src
   │ ├ 📂 db
   │ │ └ 📜 schema.ts
   │ └ 📜 index.ts
   ├ 📜 .env
   ├ 📜 drizzle.config.ts
   ├ 📜 package.json
   └ 📜 tsconfig.json
   
4. Add your schema to schema.ts (this is just what’s required for authentication) – see bottom of post for schema
5. run npx drizzle-kit generate to generate migrations
6. run npx drizzle-kit migrate to apply them to tthe database
7. make sure your auth.ts file is set up with the DrizzleAdapter(db) where db is from schema.ts
8. That’s it!

/src/db/schema.ts
import {
  boolean,
  timestamp,
  pgTable,
  text,
  primaryKey,
  integer,
} from "drizzle-orm/pg-core";
import { drizzle } from "drizzle-orm/node-postgres";
import type { AdapterAccountType } from "@auth/core/adapters";
import { Pool } from "pg";

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
});
const db = drizzle({ client: pool });

export const users = pgTable("user", {
  id: text("id")
    .primaryKey()
    .$defaultFn(() => crypto.randomUUID()),
  name: text("name"),
  email: text("email").unique(),
  emailVerified: timestamp("emailVerified", { mode: "date" }),
  image: text("image"),
});

export const accounts = pgTable(
  "account",
  {
    userId: text("userId")
      .notNull()
      .references(() => users.id, { onDelete: "cascade" }),
    type: text("type").$type<AdapterAccountType>().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 = pgTable("session", {
  sessionToken: text("sessionToken").primaryKey(),
  userId: text("userId")
    .notNull()
    .references(() => users.id, { onDelete: "cascade" }),
  expires: timestamp("expires", { mode: "date" }).notNull(),
});

export const verificationTokens = pgTable(
  "verificationToken",
  {
    identifier: text("identifier").notNull(),
    token: text("token").notNull(),
    expires: timestamp("expires", { mode: "date" }).notNull(),
  },
  (verificationToken) => [
    {
      compositePk: primaryKey({
        columns: [verificationToken.identifier, verificationToken.token],
      }),
    },
  ]
);

export const authenticators = pgTable(
  "authenticator",
  {
    credentialID: text("credentialID").notNull().unique(),
    userId: text("userId")
      .notNull()
      .references(() => users.id, { onDelete: "cascade" }),
    providerAccountId: text("providerAccountId").notNull(),
    credentialPublicKey: text("credentialPublicKey").notNull(),
    counter: integer("counter").notNull(),
    credentialDeviceType: text("credentialDeviceType").notNull(),
    credentialBackedUp: boolean("credentialBackedUp").notNull(),
    transports: text("transports"),
  },
  (authenticator) => [
    {
      compositePK: primaryKey({
        columns: [authenticator.userId, authenticator.credentialID],
      }),
    },
  ]
);