Drizzle many-to-many error

When running push from drizzle I am getting an error:

Error: target: db.-.primary: vttablet: rpc error: code = InvalidArgument desc = Duplicate column name 'user_id' (errno 1060) (sqlstate 42S21) (CallerID: ki0zccg3vsi73wcswazm): Sql: "create tab
le users_to_clubs (\n\tuser_id int not null,\n\tclub_id int not null,\n\tPRIMARY KEY (user_id, user_id)\n)", BindVars: {REDACTED}
    at PromiseConnection.query (/home/blue/Clones/tr-clone/node_modules/.pnpm/drizzle-kit@0.19.3/node_modules/drizzle-kit/index.cjs:34740:26)
    at Command.<anonymous> (/home/blue/Clones/tr-clone/node_modules/.pnpm/drizzle-kit@0.19.3/node_modules/drizzle-kit/index.cjs:52122:33)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5) {
  code: 'ER_DUP_FIELDNAME',
  errno: 1060,
  sql: 'CREATE TABLE `users_to_clubs` (\n' +
    '\t`user_id` int NOT NULL,\n' +
    '\t`club_id` int NOT NULL,\n' +
    '\tPRIMARY KEY(`user_id`,`user_id`)\n' +
    ');\n',
  sqlState: '42S21',
  sqlMessage: `target: db.-.primary: vttablet: rpc error: code = InvalidArgument desc = Duplicate column name 'user_id' (errno 1060) (sqlstate 42S21) (CallerID: ki0zccg3vsi73wcswazm): Sql: "cr
eate table users_to_clubs (\\n\\tuser_id int not null,\\n\\tclub_id int not null,\\n\\tPRIMARY KEY (user_id, user_id)\\n)", BindVars: {REDACTED}`
}

Error: target: db.-.primary: vttablet: rpc error: code = InvalidArgument desc = Duplicate column name 'user_id' (errno 1060) (sqlstate 42S21) (CallerID: ki0zccg3vsi73wcswazm): Sql: "create tab
le users_to_clubs (\n\tuser_id int not null,\n\tclub_id int not null,\n\tPRIMARY KEY (user_id, user_id)\n)", BindVars: {REDACTED}
    at PromiseConnection.query (/home/blue/Clones/tr-clone/node_modules/.pnpm/drizzle-kit@0.19.3/node_modules/drizzle-kit/index.cjs:34740:26)
    at Command.<anonymous> (/home/blue/Clones/tr-clone/node_modules/.pnpm/drizzle-kit@0.19.3/node_modules/drizzle-kit/index.cjs:52122:33)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5) {
  code: 'ER_DUP_FIELDNAME',
  errno: 1060,
  sql: 'CREATE TABLE `users_to_clubs` (\n' +
    '\t`user_id` int NOT NULL,\n' +
    '\t`club_id` int NOT NULL,\n' +
    '\tPRIMARY KEY(`user_id`,`user_id`)\n' +
    ');\n',
  sqlState: '42S21',
  sqlMessage: `target: db.-.primary: vttablet: rpc error: code = InvalidArgument desc = Duplicate column name 'user_id' (errno 1060) (sqlstate 42S21) (CallerID: ki0zccg3vsi73wcswazm): Sql: "cr
eate table users_to_clubs (\\n\\tuser_id int not null,\\n\\tclub_id int not null,\\n\\tPRIMARY KEY (user_id, user_id)\\n)", BindVars: {REDACTED}`
}

Solution:

Rewrote with copy past from docs, seemed tk fix it

Jump to solution

2 Replies

Ani•12mo ago

my schema relating to the models:

export const userRelations = relations(users, ({ many }) => ({
  userToClubs: many(usersToClubs),
  trackers: many(trackers),
  attendance: many(attendance),
}));

export const clubRelations = relations(clubs, ({ many }) => ({
  userToClubs: many(usersToClubs),
  trackers: many(trackers),
  attendance: many(attendance),
}));

export const usersToClubs = mysqlTable(
  "users_to_clubs",
  {
    userId: int("user_id")
      .notNull()
      .references(() => users.id),
    clubId: int("club_id")
      .notNull()
      .references(() => clubs.id),
  },
  (t) => ({
    pk: primaryKey(t.userId, t.clubId),
  })
);

export const usersToClubsRelations = relations(usersToClubs, ({ one }) => ({
  club: one(clubs, {
    fields: [usersToClubs.clubId],
    references: [clubs.id],
  }),
  user: one(users, {
    fields: [usersToClubs.userId],
    references: [users.id],
  }),
}));

export const userRelations = relations(users, ({ many }) => ({
  userToClubs: many(usersToClubs),
  trackers: many(trackers),
  attendance: many(attendance),
}));

export const clubRelations = relations(clubs, ({ many }) => ({
  userToClubs: many(usersToClubs),
  trackers: many(trackers),
  attendance: many(attendance),
}));

export const usersToClubs = mysqlTable(
  "users_to_clubs",
  {
    userId: int("user_id")
      .notNull()
      .references(() => users.id),
    clubId: int("club_id")
      .notNull()
      .references(() => clubs.id),
  },
  (t) => ({
    pk: primaryKey(t.userId, t.clubId),
  })
);

export const usersToClubsRelations = relations(usersToClubs, ({ one }) => ({
  club: one(clubs, {
    fields: [usersToClubs.clubId],
    references: [clubs.id],
  }),
  user: one(users, {
    fields: [usersToClubs.userId],
    references: [users.id],
  }),
}));

thought I followed the docs correctly must have done something wrong

Solution

Ani•12mo ago

Rewrote with copy past from docs, seemed tk fix it

Gaming

Programming

Drizzle many-to-many error