Do we need a unique constraint on User.email?

[keitakn]

Question 💬

I am trying to persist Auth.js data to RDB using the following table structure.

https://authjs.dev/reference/adapters#models

I use PlanetScale.

I am thinking of using @auth/drizzle-adapter.

The MySQL section of the following document shows no unique constraint on email.

https://authjs.dev/reference/adapter/drizzle

But in @auth/prisma-adapter, email is set to unique.

https://authjs.dev/reference/adapter/prisma

My understanding is that Auth.js does not allow sign-in with the same email address.

If it is correct to make the email unique, I would appreciate it if you could correct the following document🙏

https://authjs.dev/reference/adapter/drizzle

How to reproduce ☕️

import {
  int,
  timestamp,
  mysqlTable,
  primaryKey,
  varchar,
} from "drizzle-orm/mysql-core";
import type { AdapterAccount } from "@auth/core/adapters";

export const users = mysqlTable("user", {
  id: varchar("id", { length: 255 }).notNull().primaryKey(),
  name: varchar("name", { length: 255 }),
  // I think this email needs to be UNIQUE
  email: varchar("email", { length: 255 }).notNull(),
  emailVerified: timestamp("emailVerified", {
    mode: "date",
    fsp: 3,
  }).defaultNow(),
  image: varchar("image", { length: 255 }),
});

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

export const sessions = mysqlTable("session", {
  sessionToken: varchar("sessionToken", { length: 255 }).notNull().primaryKey(),
  userId: varchar("userId", { length: 255 })
    .notNull()
    .references(() => users.id, { onDelete: "cascade" }),
  expires: timestamp("expires", { mode: "date" }).notNull(),
});

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

Contributing 🙌🏽

Yes, I am willing to help answer this question in a PR

[andrewdoro]

I was also thinking about this now, I also think that we should add unique

[ktmouk]

I also thought the same thing and created the discussion (https://github.com/nextauthjs/next-auth/discussions/9166). I think we need to fix this issue.