jeremy Comments - Answer Overflow

jeremy

Posts Comments

DTDrizzle Team

•Created by jeremy on 6/8/2024 in #help

(Next)Auth.js Drizzle Schema

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

18 replies

DTDrizzle Team

•Created by jeremy on 6/8/2024 in #help

(Next)Auth.js Drizzle Schema

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")

18 replies

DTDrizzle Team

•Created by jeremy on 6/8/2024 in #help

(Next)Auth.js Drizzle Schema

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.

18 replies

DTDrizzle Team

•Created by jeremy on 6/8/2024 in #help

(Next)Auth.js Drizzle Schema

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?

18 replies

DTDrizzle Team

•Created by jeremy on 6/8/2024 in #help

(Next)Auth.js Drizzle Schema

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

18 replies

DTDrizzle Team

•Created by jeremy on 6/8/2024 in #help

(Next)Auth.js Drizzle Schema

// 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

18 replies

DTDrizzle Team

•Created by jeremy on 6/8/2024 in #help

(Next)Auth.js Drizzle Schema

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/[email protected]/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/[email protected]/node_modules/drizzle-kit/bin.cjs:77694:15