Anyone having issues with adding extra columns to the user model and then getting drizzle to save/retrieve that data?

[robertocrespond]

Anyone having issues with adding extra columns to the user model and then getting drizzle to save/retrieve that data?

According to the docs it should be as simple as adding a new column to the schema and return the data from the profile() option.

https://authjs.dev/guides/basics/role-based-access-control#with-database

It should then be available in the user object in the session callback.

Yet nothing happens when you return something from the profile function, nor is the property on the user in the session callback.

Works fine with prisma.

Originally posted by @ramoneres in #7005 (comment)

[SteveMelons]

I have this exact problem and cannot find a solution to it. I just want to extend the next-auth session with a userRole but it is always undefined..

[SteveMelons]

I figured out what the problem is.

Take a look at the mysql adapter for example https://github.com/nextauthjs/next-auth/blob/5c2b0b6e517a4aed9d4df7dd8bbdf70531174065/packages/adapter-drizzle/src/lib/mysql.ts#L84C5-L84C5

The createTables function is creating an entirely new table using the standard values. Even though you pass in your tableFn, the tables get overwritten with the defaults. This causes the queries to only include the default values and not any additional columns you added. This is an issue not a discussion. I dont know why the issue was closed..

[SteveMelons]

I've tried to open 2 issues for this now but they keep getting closed because of the reproduction link..

[hexcowboy]

the issue is because the drizzle adapter was poorly written to only accept the example schema, which will not yield your custom columns when the adapter does .returning() with drizzle.

the solution would be to create a custom adapter that uses your own schema:

adapter.ts

import type { Adapter } from "@auth/core/adapters";
import * as schema from "./schema"; // this is where your custom tables are defined
import { and, eq } from "drizzle-orm";
import { PgDatabase } from "drizzle-orm/pg-core";

export function pgDrizzleAdapter(
  client: InstanceType<typeof PgDatabase>
): Adapter {
  const { users, accounts, sessions, verificationTokens } = schema;

  return {
    async createUser(data) {
      return await client
        .insert(users)
        .values({ ...data, id: crypto.randomUUID() })
        .returning()
        .then((res) => res[0] ?? null);
    },
    async getUser(data) {
      return await client
        .select()
        .from(users)
        .where(eq(users.id, data))
        .then((res) => res[0] ?? null);
    },
    async getUserByEmail(data) {
      return await client
        .select()
        .from(users)
        .where(eq(users.email, data))
        .then((res) => res[0] ?? null);
    },
    async createSession(data) {
      return await client
        .insert(sessions)
        .values(data)
        .returning()
        .then((res) => res[0]);
    },
    async getSessionAndUser(data) {
      return await client
        .select({
          session: sessions,
          user: users,
        })
        .from(sessions)
        .where(eq(sessions.sessionToken, data))
        .innerJoin(users, eq(users.id, sessions.userId))
        .then((res) => res[0] ?? null);
    },
    async updateUser(data) {
      if (!data.id) {
        throw new Error("No user id.");
      }

      return await client
        .update(users)
        .set(data)
        .where(eq(users.id, data.id))
        .returning()
        .then((res) => res[0]);
    },
    async updateSession(data) {
      return await client
        .update(sessions)
        .set(data)
        .where(eq(sessions.sessionToken, data.sessionToken))
        .returning()
        .then((res) => res[0]);
    },
    async linkAccount(rawAccount) {
      const updatedAccount = await client
        .insert(accounts)
        .values(rawAccount)
        .returning()
        .then((res) => res[0]);

      // Drizzle will return `null` for fields that are not defined.
      // However, the return type is expecting `undefined`.
      const account = {
        ...updatedAccount,
        access_token: updatedAccount.accessToken ?? undefined,
        token_type: updatedAccount.tokenType ?? undefined,
        id_token: updatedAccount.idToken ?? undefined,
        refresh_token: updatedAccount.refreshToken ?? undefined,
        scope: updatedAccount.scope ?? undefined,
        expires_at: updatedAccount.expiresAt ?? undefined,
        session_state: updatedAccount.sessionState ?? undefined,
      };

      return account;
    },
    async getUserByAccount(account) {
      const dbAccount =
        (await client
          .select()
          .from(accounts)
          .where(
            and(
              eq(accounts.providerAccountId, account.providerAccountId),
              eq(accounts.provider, account.provider)
            )
          )
          .leftJoin(users, eq(accounts.userId, users.id))
          .then((res) => res[0])) ?? null;

      if (!dbAccount) {
        return null;
      }

      return dbAccount.users;
    },
    async deleteSession(sessionToken) {
      const session = await client
        .delete(sessions)
        .where(eq(sessions.sessionToken, sessionToken))
        .returning()
        .then((res) => res[0] ?? null);

      return session;
    },
    async createVerificationToken(token) {
      return await client
        .insert(verificationTokens)
        .values(token)
        .returning()
        .then((res) => res[0]);
    },
    async useVerificationToken(token) {
      try {
        return await client
          .delete(verificationTokens)
          .where(
            and(
              eq(verificationTokens.identifier, token.identifier),
              eq(verificationTokens.token, token.token)
            )
          )
          .returning()
          .then((res) => res[0] ?? null);
      } catch (err) {
        throw new Error("No verification token found.");
      }
    },
    async deleteUser(id) {
      await client
        .delete(users)
        .where(eq(users.id, id))
        .returning()
        .then((res) => res[0] ?? null);
    },
    async unlinkAccount(account) {
      const { type, provider, providerAccountId, userId } = await client
        .delete(accounts)
        .where(
          and(
            eq(accounts.providerAccountId, account.providerAccountId),
            eq(accounts.provider, account.provider)
          )
        )
        .returning()
        .then((res) => res[0] ?? null);

      return { provider, type, providerAccountId, userId };
    },
  };
}

schema.ts

import type { AdapterAccount } from "@auth/core/adapters";
import {
  integer,
  pgSchema,
  primaryKey,
  text,
  timestamp,
  uuid,
} from "drizzle-orm/pg-core";

export const authSchema = pgSchema("auth");

export const users = authSchema.table("users", {
  id: uuid("id").notNull().primaryKey(),
  name: text("name"),
  email: text("email").notNull(),
  emailVerified: timestamp("email_verified", { mode: "date" }),
  image: text("image"),
  customField: text("custom_field"),
});

export const accounts = authSchema.table(
  "accounts",
  {
    userId: uuid("user_id")
      .notNull()
      .references(() => users.id, { onDelete: "cascade" }),
    type: text("type").$type<AdapterAccount["type"]>().notNull(),
    provider: text("provider").notNull(),
    providerAccountId: text("provider_account_id").notNull(),
    refreshToken: text("refresh_token"),
    accessToken: text("access_token"),
    expiresAt: integer("expires_at"),
    tokenType: text("token_type"),
    scope: text("scope"),
    idToken: text("id_token"),
    sessionState: text("session_state"),
  },
  (account) => ({
    compoundKey: primaryKey(account.provider, account.providerAccountId),
  })
);

export const sessions = authSchema.table("sessions", {
  sessionToken: text("session_token").notNull().primaryKey(),
  userId: uuid("user_id")
    .notNull()
    .references(() => users.id, { onDelete: "cascade" }),
  expires: timestamp("expires", { mode: "date" }).notNull(),
});

export const verificationTokens = authSchema.table(
  "verification_tokens",
  {
    identifier: text("identifier").notNull(),
    token: text("token").notNull(),
    expires: timestamp("expires", { mode: "date" }).notNull(),
  },
  (vt) => ({
    compoundKey: primaryKey(vt.identifier, vt.token),
  })
);

[SteveMelons]

Yes, that’s what I said above. But writing your own adapter, that makes a lot of sense until it gets fixed! Good workaround.

[thomasmol]

Ah thanks, this works as a work around, great!

[thomasmol]

Interesting bug. For me the problem is that I want to have plural names of the columns -> e.g. users instead of user. This adapter is giving me the same problem. Any ideas how we can update the adapter so it accepts a custom schema?

[ofi-inc]

Copy the existing Drizzle Adapter. Then just import your table definitions in "mysql.ts" (or whichever db you use) for at least the user table or just all of the requried tables instead of letting the table function create them. You can remove a lot of the code in the adapter.

So you replace
const { users, accounts, sessions, verificationTokens } = createTables(tableFn)
with the imported tables from your schema.

[thomasmol]

Thanks, i know! I actually meant how can we help @balazsorban44 and create a pull request that fixes this problem.

I think the problem is related to the fact that the drizzle adapter itself adapts to 3 different adapters (mysql, pg and sqlite):

export function DrizzleAdapter<SqlFlavor extends SqlFlavorOptions>(
  db: SqlFlavor,
  table?: TableFn<SqlFlavor>
): Adapter {
  if (is(db, MySqlDatabase)) {
    return mySqlDrizzleAdapter(db, table as MySqlTableFn)
  } else if (is(db, PgDatabase)) {
    return pgDrizzleAdapter(db, table as PgTableFn)
  } else if (is(db, BaseSQLiteDatabase)) {
    return SQLiteDrizzleAdapter(db, table as SQLiteTableFn)
  }

  throw new Error(
    `Unsupported database type (${typeof db}) in Auth.js Drizzle adapter.`
  )
}

And in each adapter it cannot access the table schemes, so it has to create new ones.
A solution could be I think to pass the users, accounts, sessions and verificationTokens schema objects in the DrizzleAdapter function as well. But this might be a bit cumbersome.

[ofi-inc]

That was the only solution I could think of as well. I agree, seems a bit tedious.

[thomasmol]

I just saw you can import the whole schema like so: import * as schema from './schema'; where schema is pointed to the schema.ts file. So you could pass on the entire schema.
Also, the drizzle client exposes some sort of schema like so: db._.schema. And with that you can do this, but I can't get it to work tbh: db._.schema?.users.columns

[rajatsandeepsen]

I solved it by custom building an adapter. My requirement was an additional column named username (from Github provider) in pgDrizzleAdapter. Below code has some type sacrifices, but works. Also default Schema is replaced with mine.

server/db/adapter.ts reference full code @auth/drizzle-adapter

import { PgDatabase } from "drizzle-orm/pg-core"
import { Adapter, AdapterUser } from "next-auth/adapters"
import { accounts, sessions, users, verificationTokens } from "./schema"
import { and, eq } from "drizzle-orm"
import { Awaitable } from "next-auth"

+ type CustomAdapter = Omit<Adapter, "createUser"> & {
+    createUser: (user: Omit<AdapterUser & { username: string }, "id">) => Awaitable<AdapterUser & { username: string }>
+ }


export function CustomPgDrizzleAdapter(client: InstanceType<typeof PgDatabase>): CustomAdapter {

    return {
        async createUser(data) {
            return await client
                .insert(users)
-                .values({ ...data, id: crypto.randomUUID() })
+                .values({ ...data, id: crypto.randomUUID(), username: data.username })
                .returning()
                .then((res) => res[0] ?? null)
        },
        async getUser(data) {
            return await client
                .select()
                .from(users)
                .where(eq(users.id, data))
                .then((res) => res[0] ?? null)
        },

....

and server/auth.ts

export const authOptions: NextAuthOptions = {
-  adapter: DrizzleAdapter(db),
+  adapter: CustomPgDrizzleAdapter(db) as unknown as Adapter,

  providers: [
    GithubProvider({
      clientId: process.env.GITHUB_CLIENT_ID,
      clientSecret: process.env.GITHUB_CLIENT_SECRET,

      profile: (profile) => {
        console.warn("profile:", profile)
        return {
          id: profile.id.toString(),
          name: profile.name || profile.login,
          email: profile.email,
          image: profile.avatar_url,
+          username: profile.login,
        };
      },
    }),
  ],
  callbacks: {
    session: ({ session, user }) => {
      return {
        ...session,
        user: {
          ...session.user,
+          username: user.username,
          id: user.id,
        },
      }
    },
  },
};

[madsrode]

This pull request might be interesting for others finding this.

[paupenin]

For anyone having a similar issue, you can use you own table definitions like this:

  // @ts-expect-error DrizzleAdapter expects a TableFn<SqlFlavor> but we're using a table defined in our schema
  adapter: DrizzleAdapter(db, (name: string) => {
    return {
      user: users,
      account: accounts,
      session: sessions,
      verificationToken: verificationTokens,
    }[name];
  }),

I'm sorry for the @ts-expect-error but I don't have time to create a PR for this now in the open-source project.

[pranavp10]

To resolve this issue, you should provide the schema to the adapter when initialising it.

import * as auth from "../db/schema/auth";

// remaining code 

adapter: DrizzleAdapter(db, {
    accountsTable: auth.accounts,
    // @ts-expect-error
    usersTable: auth.users,
    // @ts-expect-error
    sessionsTable: auth.sessions,
    verificationTokensTable: auth.verificationTokens,
  }) as Adapter,

// remaining code