DrizzleAdapter: Request to Improve/Fix Certain Column Types

[ktmouk]

Goals

• According to the next-auth docs, the email column should be unique, but the current schema does not seem to have a unique constraint. It may cause duplication of email addresses.

  Email address is optional, but if one is specified for a User, then it must be unique.

• The current Postgres schema uses timestamp to store the datetime. However, timestamptz is preferred because it can also store the timezone info, which helps prevent bugs related to timezone. more detail: Don't_Do_This
• As Postgres has a uuid column type, we can use uuid column type to store the user's id instead of using text column type.

Non-Goals

No response

Background

While the current schema works fine, I believe we can improve (or fix) some column types for the drizzle adapter.
https://authjs.dev/reference/adapter/drizzle

Proposal

We could implement by changing the current schema like this:
This is potentially a breaking change, but since we are under v1, I believe this is worth doing.

const users = pgTable("user", {
    id: uuid("id").notNull().primaryKey().defaultRandom(), // Change to `uuid` and use `defaultRandom` to generate uuid automatically.
    name: text("name"),
    email: text("email").notNull().unique(), // Add `unique()`
    emailVerified: timestamp("emailVerified", { mode: "date", withTimezone: true }),  // Add `withTimezone` option.
    image: text("image"),
  })

  const accounts = pgTable(
    "account",
    {
      userId: uuid("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) => ({
      pk: primaryKey({ columns: [account.provider, account.providerAccountId] }),
    })
  )

  const sessions = pgTable("session", {
    sessionToken: text("sessionToken").notNull().primaryKey(),
    userId: uuid("userId")
      .notNull()
      .references(() => users.id, { onDelete: "cascade" }),
    expires: timestamp("expires", { mode: "date", withTimezone: true }).notNull(),  // Add `withTimezone` option.
  })

  const verificationTokens = pgTable(
    "verificationToken",
    {
      identifier: text("identifier").notNull(),
      token: text("token").notNull(),
      expires: timestamp("expires", { mode: "date", withTimezone: true }).notNull(),  // Add `withTimezone` option.
    },
    (vt) => ({
      pk: primaryKey({ columns: [vt.identifier, vt.token] }),
    })
  )

[nahtnam]

Email address is optional

Shouldn't notNull be removed from the email address field?

[stumbata]

Why there is no PR about this? specially the uuid change is very important for speed. Also it will be good account.userId and session.userId to be indexed.

[mjn81]

Hi did this got a PR or something? it's a nice change and should absolutely be added