Drizzle Team•4mo ago

(Next)Auth.js Drizzle Schema

See https://authjs.dev/getting-started/adapters/drizzle#schemas for the Drizzle Schema provided I haven't been able to figure out how to get Auth.js schema working with drizzle. I believe the issue is handling the composite primary key In the example, the accounts, verificationTokens, authenticators primaryKeys are respectively

  (account) => ({
    compoundKey: primaryKey({
      columns: [account.provider, account.providerAccountId],
    }),
  })

  (verificationToken) => ({
    compositePk: primaryKey({
      columns: [verificationToken.identifier, verificationToken.token],
    }),
  })

 (authenticator) => ({
    compositePK: primaryKey({
      columns: [authenticator.userId, authenticator.credentialID],
    }),
  })

  (account) => ({
    compoundKey: primaryKey({
      columns: [account.provider, account.providerAccountId],
    }),
  })

  (verificationToken) => ({
    compositePk: primaryKey({
      columns: [verificationToken.identifier, verificationToken.token],
    }),
  })

 (authenticator) => ({
    compositePK: primaryKey({
      columns: [authenticator.userId, authenticator.credentialID],
    }),
  })

Does the property name of the primary key matter? In my old drizzle schema, it looked like it was named compoundKey, while the current documentation uses either pk or pkWithCustomName and the auth.js example is compoundKey, compositePk, and compositePK (is this one a typo?). I can run drizzle-kit generate and drizzle-kit migrate, while when running drizzle-kit push gives me this error:

[✓] Pulling schema from database...error: constraint "authenticator_userid_credentialid_pk" of relation "authenticator" does not exist
    at node_modules/.pnpm/drizzle-kit@0.22.5/node_modules/drizzle-kit/bin.cjs:77694:15 {
  length: 162,
  severity: 'ERROR',
  code: '42704',
  detail: undefined,
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'tablecmds.c',
  line: '11976',
  routine: 'ATExecDropConstraint'
}

[✓] Pulling schema from database...error: constraint "authenticator_userid_credentialid_pk" of relation "authenticator" does not exist
    at node_modules/.pnpm/drizzle-kit@0.22.5/node_modules/drizzle-kit/bin.cjs:77694:15 {
  length: 162,
  severity: 'ERROR',
  code: '42704',
  detail: undefined,
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'tablecmds.c',
  line: '11976',
  routine: 'ATExecDropConstraint'
}

Sorry if this doesn't make any sense, let me know if I can provide more information Using node-postgress and pg driver, not sure if that matters

Auth.js | Drizzle

Authentication for the Web

8 Replies

jeremy•4mo ago

drizzle-kit check gets me Everything's fine 🐶🔥 and this is what drizzle-kit introspect gets me (images above) i love u guys ur twt once qrtd me also fyi the next authjs documentation still uses the old migrator script for migration instead of the drizzle-kit migrate command

jeremy•4mo ago

https://pastebin.com/cYHwZAyH

Pastebin

import { boolean, integer, pgTable, primaryKey, text, timesta...

Pastebin.com is the number one paste tool since 2002. Pastebin is a website where you can store text online for a set period of time.

jeremy•4mo ago

my schema.ts also if that is helpful tho its essentially a carbon copy of what auth.js recommends except i just changed all the composite primary keys property names to pk i figured it out (deleted the table and migrated again and pushed again and it worked :love:) it appears i have spoken too soon

jeremy•4mo ago

i am still not certain why it is doing this: [✓] Pulling schema from database...error: constraint "authenticator_userid_credentialid_pk" of relation "authenticator" does not exist at /Users/jeremy/Github/defraud/node_modules/.pnpm/drizzle-kit@0.22.5/node_modules/drizzle-kit/bin.cjs:77694:15

jeremy•4mo ago

fyi the verbose output:

jeremy•4mo ago

Anyway, I think I have actually figured it out, the following code works and is able to be pushed fine:

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) => ({
    pk: primaryKey({
      name: "authenticator_userId_credentialID_pk",
      columns: [authenticator.credentialID, authenticator.userId],
    }),
  }),
);

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) => ({
    pk: primaryKey({
      name: "authenticator_userId_credentialID_pk",
      columns: [authenticator.credentialID, authenticator.userId],
    }),
  }),
);

Notice the order of the columns [credentiald, userId]. I imagine the property name of the object (in this case, pk, but in the Auth.js schema compositePK) doesn't matter, but for some reason, the order of the columns makes a difference. Here are the results for different variations of the above after deleting the table authenticators and then running drizzle-kit push:

export const authenticators = pgTable(
  "authenticator",
  schema,
  (authenticator) => ({
    pk: primaryKey({
      columns: [authenticator.userId, authenticator.credentialID],
    }),
  }),
);

export const authenticators = pgTable(
  "authenticator",
  schema,
  (authenticator) => ({
    pk: primaryKey({
      columns: [authenticator.userId, authenticator.credentialID],
    }),
  }),
);

[✓] Pulling schema from database...
 Warning  You are about to execute current statements:

ALTER TABLE "authenticator" DROP CONSTRAINT authenticator_userId_credentialID_pk;
--> statement-breakpoint
ALTER TABLE "authenticator" ADD CONSTRAINT authenticator_userId_credentialID_pk PRIMARY KEY(userId,credentialID);

error: constraint "authenticator_userid_credentialid_pk" of relation "authenticator" does not exist
    at /Users/jeremy/Github/defraud/node_modules/.pnpm/drizzle-kit@0.22.5/node_modules/drizzle-kit/bin.cjs:77694:15

[✓] Pulling schema from database...
 Warning  You are about to execute current statements:

ALTER TABLE "authenticator" DROP CONSTRAINT authenticator_userId_credentialID_pk;
--> statement-breakpoint
ALTER TABLE "authenticator" ADD CONSTRAINT authenticator_userId_credentialID_pk PRIMARY KEY(userId,credentialID);

error: constraint "authenticator_userid_credentialid_pk" of relation "authenticator" does not exist
    at /Users/jeremy/Github/defraud/node_modules/.pnpm/drizzle-kit@0.22.5/node_modules/drizzle-kit/bin.cjs:77694:15

// With name property
export const authenticators = pgTable(
  "authenticator",
  schema,
  (authenticator) => ({
    pk: primaryKey({
      name: "authenticator_userId_credentialID_pk",
      columns: [authenticator.userId, authenticator.credentialID],
    }),
  }),
);

// With name property
export const authenticators = pgTable(
  "authenticator",
  schema,
  (authenticator) => ({
    pk: primaryKey({
      name: "authenticator_userId_credentialID_pk",
      columns: [authenticator.userId, authenticator.credentialID],
    }),
  }),
);

[✓] Pulling schema from database...
 Warning  You are about to execute current statements:

ALTER TABLE "authenticator" DROP CONSTRAINT authenticator_userId_credentialID_pk;
--> statement-breakpoint
ALTER TABLE "authenticator" ADD CONSTRAINT authenticator_userId_credentialID_pk PRIMARY KEY(userId,credentialID);

error: constraint "authenticator_userid_credentialid_pk" of relation "authenticator" does not exist

[✓] Pulling schema from database...
 Warning  You are about to execute current statements:

ALTER TABLE "authenticator" DROP CONSTRAINT authenticator_userId_credentialID_pk;
--> statement-breakpoint
ALTER TABLE "authenticator" ADD CONSTRAINT authenticator_userId_credentialID_pk PRIMARY KEY(userId,credentialID);

error: constraint "authenticator_userid_credentialid_pk" of relation "authenticator" does not exist

// columns switched
export const authenticators = pgTable(
  "authenticator",
  schema,
  (authenticator) => ({
    pk: primaryKey({
      columns: [authenticator.credentialID, authenticator.userId],
    }),
  }),
);

// columns switched
export const authenticators = pgTable(
  "authenticator",
  schema,
  (authenticator) => ({
    pk: primaryKey({
      columns: [authenticator.credentialID, authenticator.userId],
    }),
  }),
);

[✓] Pulling schema from database...[i] No changes detected

[✓] Pulling schema from database...[i] No changes detected

jeremy•4mo ago

I suspect this is the related issue, but not certain: https://github.com/drizzle-team/drizzle-orm/issues/2326

GitHub

[BUG]: drizzle-kit does not respect the order of columns configured...

What version of drizzle-orm are you using? 0.30.10 What version of drizzle-kit are you using? 0.21.1 Describe the Bug export const worksToCreators = sqliteTable( 'works_to_creators', { work...

jeremy•4mo ago

My guess is that the column order of the primary key when using drizzle-kit generate is as given, while when using drizzle-kit push, it is ordered alphabetically? Actually, I am not certain of the above anymore. When the table looks like this:

export const authenticators = pgTable(
  "authenticator",
  schema,
  (authenticator) => ({
    pk: primaryKey({
      columns: [authenticator.userId, authenticator.credentialID],
    }),
  }),
);

export const authenticators = pgTable(
  "authenticator",
  schema,
  (authenticator) => ({
    pk: primaryKey({
      columns: [authenticator.userId, authenticator.credentialID],
    }),
  }),
);

Running drizzle-kit generate outputs the following SQL statement,

CREATE TABLE IF NOT EXISTS "authenticator" (
    ...
    CONSTRAINT "authenticator_userId_credentialID_pk" PRIMARY KEY("userId","credentialID"),
);

CREATE TABLE IF NOT EXISTS "authenticator" (
    ...
    CONSTRAINT "authenticator_userId_credentialID_pk" PRIMARY KEY("userId","credentialID"),
);

and running drizzle-kit push executes the following statement

CREATE TABLE IF NOT EXISTS "authenticator" (
        ...
        CONSTRAINT "authenticator_userId_credentialID_pk" PRIMARY KEY("userId","credentialID"),
);

CREATE TABLE IF NOT EXISTS "authenticator" (
        ...
        CONSTRAINT "authenticator_userId_credentialID_pk" PRIMARY KEY("userId","credentialID"),
);

which is the same. Yet, running drizzle-kit push again reutrns this:

[✓] Pulling schema from database...
 Warning  You are about to execute current statements:

ALTER TABLE "authenticator" DROP CONSTRAINT authenticator_userId_credentialID_pk;
--> statement-breakpoint
ALTER TABLE "authenticator" ADD CONSTRAINT authenticator_userId_credentialID_pk PRIMARY KEY(userId,credentialID);

error: constraint "authenticator_userid_credentialid_pk" of relation "authenticator" does not exist

[✓] Pulling schema from database...
 Warning  You are about to execute current statements:

ALTER TABLE "authenticator" DROP CONSTRAINT authenticator_userId_credentialID_pk;
--> statement-breakpoint
ALTER TABLE "authenticator" ADD CONSTRAINT authenticator_userId_credentialID_pk PRIMARY KEY(userId,credentialID);

error: constraint "authenticator_userid_credentialid_pk" of relation "authenticator" does not exist

I am a bit confused at what's going on. Note on the database side the index authenticator_userId_credentialID_pk does exist with

CREATE UNIQUE INDEX "authenticator_userId_credentialID_pk" ON public.authenticator USING btree ("userId", "credentialID")

CREATE UNIQUE INDEX "authenticator_userId_credentialID_pk" ON public.authenticator USING btree ("userId", "credentialID")

Also I am on version drizzle-kit: v0.22.6 drizzle-orm: v0.31.2

Gaming

Programming

(Next)Auth.js Drizzle Schema