Drizzle ORM - cannot push schema due to FK type mismatch

Question

I am trying to learn Drizzle ORM and integrate it with next/auth.

I am trying to push an updated schema to my DB, (postgres through neon), but am running into the following error:

Error: foreign key constraint "job-tracker-t3_session_userId_job-tracker-t3_user_id_fk" cannot be implemented

Detail: 'Key columns "userId" and "id" are of incompatible types: text and uuid.',

When I first tried to push the schema, there was a type mismatch, userId was type text while id was uuid. I then changed userId to uuid as well, and got the same problem. I also dropped all the tables and tried to repush, and got the same error.

Here is my schema:

export const users = createTable("user", {
  id: uuid("id")
    .default(sql`gen_random_uuid()`)
    .primaryKey(),
  username: varchar("name", { length: 255 }).unique(),
  email: varchar("email", { length: 255 }).notNull().unique(),
  avatar: varchar("image", { length: 255 }),
  sub: varchar("sub", { length: 255 }),
  displayName: varchar("displayName", { length: 255 }),
  date: timestamp("date").default(sql`CURRENT_TIMESTAMP`),
  autoArchive: boolean("autoArchive").default(true),
  passwordHash: text("passwordHash").notNull(),
});

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

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

When I look at my DB, I see that the tables were all created and userId is of type uuid. Should I disregard the error since the tables were created, or will I get more problems from this in the future?

Thanks!

Answer 1

• Mismatched data types: The data types of the referencing and referenced columns must be the same.
• Dangling foreign keys: A foreign key that links to a nonexistent table or column.
• Referencing a nonexistent table or column: The table that the foreign key references must already exist before you can define a foreign key that references it.

Please ensure that your schema doesn’t have these issues. If you’re still facing problems, could you please provide more details about the error message? That would help me assist you better.

Answer 2

My error was actually in the session table, where it still refers to the user.Id from accounts as text instead of uuid:

userId: text("userId")
    .notNull()
    .references(() => users.id, { onDelete: "cascade" }),