Drizzle ORM schema not working

Hey all, I have a drizzle orm schema and I can't really understand the error message, could someone please help? its connected to a planetscale db

scheme:

export const timesheets = mysqlTable("timesheets", {
  id: serial("id").primaryKey().autoincrement(),
  work_provider: varchar("work_provider", { length: 128 }),
  date_of_work: timestamp("date_of_work").defaultNow(),
  order_num: varchar("order_num", { length: 128 }).notNull(),
  work_item: varchar("work_item", { length: 128 }).notNull(),
  quantity: serial("quantity").notNull(),
  notes: varchar("notes", { length: 1024 }).notNull(),
  gang_price_split: varchar("gang_price_split", { length: 128 }).notNull(),
  created_at: timestamp("created_at").defaultNow(),
});

export type Timesheet = InferModel<typeof timesheets>;

export const timesheetsRelations = relations(timesheets, ({ many }) => ({
  engineers: many(engineers),
  timesheetsToEngineers: many(timesheetsToEngineers),
}));

export const engineers = mysqlTable("engineers", {
  id: serial("id").primaryKey().autoincrement(),
  firstName: varchar("firstName", { length: 128 }).notNull(),
  lastName: varchar("lastName", { length: 256 }).notNull(),
  birthDate: varchar("birthDate", { length: 32 }).notNull(),
});

export type Engineer = InferModel<typeof engineers>;

export const engineersRelations = relations(engineers, ({ many }) => ({
  timesheets: many(timesheets),
  timesheetsToEngineers: many(timesheetsToEngineers),
}));

export const timesheetsToEngineers = mysqlTable(
  "timesheets_to_engineers",
  {
    engineerId: int("engineer_id")
      .notNull()
      .references(() => engineers.id),
    timesheetId: int("timesheet_id")
      .notNull()
      .references(() => timesheets.id),
  },
  (t) => ({
    pk: primaryKey(t.engineerId, t.timesheetId),
  })
);

export const timesheets = mysqlTable("timesheets", {
  id: serial("id").primaryKey().autoincrement(),
  work_provider: varchar("work_provider", { length: 128 }),
  date_of_work: timestamp("date_of_work").defaultNow(),
  order_num: varchar("order_num", { length: 128 }).notNull(),
  work_item: varchar("work_item", { length: 128 }).notNull(),
  quantity: serial("quantity").notNull(),
  notes: varchar("notes", { length: 1024 }).notNull(),
  gang_price_split: varchar("gang_price_split", { length: 128 }).notNull(),
  created_at: timestamp("created_at").defaultNow(),
});

export type Timesheet = InferModel<typeof timesheets>;

export const timesheetsRelations = relations(timesheets, ({ many }) => ({
  engineers: many(engineers),
  timesheetsToEngineers: many(timesheetsToEngineers),
}));

export const engineers = mysqlTable("engineers", {
  id: serial("id").primaryKey().autoincrement(),
  firstName: varchar("firstName", { length: 128 }).notNull(),
  lastName: varchar("lastName", { length: 256 }).notNull(),
  birthDate: varchar("birthDate", { length: 32 }).notNull(),
});

export type Engineer = InferModel<typeof engineers>;

export const engineersRelations = relations(engineers, ({ many }) => ({
  timesheets: many(timesheets),
  timesheetsToEngineers: many(timesheetsToEngineers),
}));

export const timesheetsToEngineers = mysqlTable(
  "timesheets_to_engineers",
  {
    engineerId: int("engineer_id")
      .notNull()
      .references(() => engineers.id),
    timesheetId: int("timesheet_id")
      .notNull()
      .references(() => timesheets.id),
  },
  (t) => ({
    pk: primaryKey(t.engineerId, t.timesheetId),
  })
);

export const timesheets = mysqlTable("timesheets", {
  id: serial("id").primaryKey().autoincrement(),
  work_provider: varchar("work_provider", { length: 128 }),
  date_of_work: timestamp("date_of_work").defaultNow(),
  order_num: varchar("order_num", { length: 128 }).notNull(),
  work_item: varchar("work_item", { length: 128 }).notNull(),
  quantity: serial("quantity").notNull(),
  notes: varchar("notes", { length: 1024 }).notNull(),
  gang_price_split: varchar("gang_price_split", { length: 128 }).notNull(),
  created_at: timestamp("created_at").defaultNow(),
});

export type Timesheet = InferModel<typeof timesheets>;

export const timesheetsRelations = relations(timesheets, ({ many }) => ({
  engineers: many(engineers),
  timesheetsToEngineers: many(timesheetsToEngineers),
}));

export const engineers = mysqlTable("engineers", {
  id: serial("id").primaryKey().autoincrement(),
  firstName: varchar("firstName", { length: 128 }).notNull(),
  lastName: varchar("lastName", { length: 256 }).notNull(),
  birthDate: varchar("birthDate", { length: 32 }).notNull(),
});

export type Engineer = InferModel<typeof engineers>;

export const engineersRelations = relations(engineers, ({ many }) => ({
  timesheets: many(timesheets),
  timesheetsToEngineers: many(timesheetsToEngineers),
}));

export const timesheetsToEngineers = mysqlTable(
  "timesheets_to_engineers",
  {
    engineerId: int("engineer_id")
      .notNull()
      .references(() => engineers.id),
    timesheetId: int("timesheet_id")
      .notNull()
      .references(() => timesheets.id),
  },
  (t) => ({
    pk: primaryKey(t.engineerId, t.timesheetId),
  })
);

export const timesheets = mysqlTable("timesheets", {
  id: serial("id").primaryKey().autoincrement(),
  work_provider: varchar("work_provider", { length: 128 }),
  date_of_work: timestamp("date_of_work").defaultNow(),
  order_num: varchar("order_num", { length: 128 }).notNull(),
  work_item: varchar("work_item", { length: 128 }).notNull(),
  quantity: serial("quantity").notNull(),
  notes: varchar("notes", { length: 1024 }).notNull(),
  gang_price_split: varchar("gang_price_split", { length: 128 }).notNull(),
  created_at: timestamp("created_at").defaultNow(),
});

export type Timesheet = InferModel<typeof timesheets>;

export const timesheetsRelations = relations(timesheets, ({ many }) => ({
  engineers: many(engineers),
  timesheetsToEngineers: many(timesheetsToEngineers),
}));

export const engineers = mysqlTable("engineers", {
  id: serial("id").primaryKey().autoincrement(),
  firstName: varchar("firstName", { length: 128 }).notNull(),
  lastName: varchar("lastName", { length: 256 }).notNull(),
  birthDate: varchar("birthDate", { length: 32 }).notNull(),
});

export type Engineer = InferModel<typeof engineers>;

export const engineersRelations = relations(engineers, ({ many }) => ({
  timesheets: many(timesheets),
  timesheetsToEngineers: many(timesheetsToEngineers),
}));

export const timesheetsToEngineers = mysqlTable(
  "timesheets_to_engineers",
  {
    engineerId: int("engineer_id")
      .notNull()
      .references(() => engineers.id),
    timesheetId: int("timesheet_id")
      .notNull()
      .references(() => timesheets.id),
  },
  (t) => ({
    pk: primaryKey(t.engineerId, t.timesheetId),
  })
);

max14OP•8/7/23, 5:00 PM

next bit:

export const timesheetsToEngineersRelations = relations(
  timesheetsToEngineers,
  ({ one }) => ({
    group: one(timesheets, {
      fields: [timesheetsToEngineers.timesheetId],
      references: [timesheets.id],
    }),
    user: one(engineers, {
      fields: [timesheetsToEngineers.engineerId],
      references: [engineers.id],
    }),
  })
);

export const timesheetsToEngineersRelations = relations(
  timesheetsToEngineers,
  ({ one }) => ({
    group: one(timesheets, {
      fields: [timesheetsToEngineers.timesheetId],
      references: [timesheets.id],
    }),
    user: one(engineers, {
      fields: [timesheetsToEngineers.engineerId],
      references: [engineers.id],
    }),
  })
);

max14OP•8/7/23, 5:00 PM

and the error:

Error: target: [dbname].-.primary: vttablet: rpc error: code = InvalidArgument desc = Incorrect table definition; there can be only one auto column and it must be defined as a key (errno 1075) (sqlstate 42000) (CallerID: q894nv2d7jsuvrpa4xam): Sql: "CREATE TABLE `timesheets` (\n\t`id` serial AUTO_INCREMENT,\n\t`work_provider` varchar(128),\n\t`date_of_work` timestamp DEFAULT (now()),\n\t`order_num` varchar(128) NOT NULL,\n\t`work_item` varchar(128) NOT NULL,\n\t`quantity` serial AUTO_INCREMENT NOT NULL,\n\t`notes` varchar(1024) NOT NULL,\n\t`gang_price_split` varchar(128) NOT NULL,\n\t`created_at` timestamp DEFAULT (now())\n)", BindVars: {REDACTED}

Error: target: [dbname].-.primary: vttablet: rpc error: code = InvalidArgument desc = Incorrect table definition; there can be only one auto column and it must be defined as a key (errno 1075) (sqlstate 42000) (CallerID: q894nv2d7jsuvrpa4xam): Sql: "CREATE TABLE `timesheets` (\n\t`id` serial AUTO_INCREMENT,\n\t`work_provider` varchar(128),\n\t`date_of_work` timestamp DEFAULT (now()),\n\t`order_num` varchar(128) NOT NULL,\n\t`work_item` varchar(128) NOT NULL,\n\t`quantity` serial AUTO_INCREMENT NOT NULL,\n\t`notes` varchar(1024) NOT NULL,\n\t`gang_price_split` varchar(128) NOT NULL,\n\t`created_at` timestamp DEFAULT (now())\n)", BindVars: {REDACTED}

Kasper•8/7/23, 5:29 PM

@maxlc14 serial is just an anlias for int not-null autoincrement. So you can only have it one place in each table

Kasper•8/7/23, 5:30 PM

export const timesheets = mysqlTable('timesheets', {
    id: serial('id').primaryKey().autoincrement(),
    work_provider: varchar('work_provider', { length: 128 }),
    date_of_work: timestamp('date_of_work').defaultNow(),
    order_num: varchar('order_num', { length: 128 }).notNull(),
    work_item: varchar('work_item', { length: 128 }).notNull(),
    quantity: int('quantity').notNull(),
    notes: varchar('notes', { length: 1024 }).notNull(),
    gang_price_split: varchar('gang_price_split', { length: 128 }).notNull(),
    created_at: timestamp('created_at').defaultNow(),
});

export type Timesheet = InferModel<typeof timesheets>;

export const timesheetsRelations = relations(timesheets, ({ many }) => ({
    timesheetsToEngineers: many(timesheetsToEngineers),
}));

export const engineers = mysqlTable('engineers', {
    id: serial('id').primaryKey().autoincrement(),
    firstName: varchar('firstName', { length: 128 }).notNull(),
    lastName: varchar('lastName', { length: 256 }).notNull(),
    birthDate: varchar('birthDate', { length: 32 }).notNull(),
});

export type Engineer = InferModel<typeof engineers>;

export const engineersRelations = relations(engineers, ({ many }) => ({
    timesheetsToEngineers: many(timesheetsToEngineers),
}));

export const timesheetsToEngineers = mysqlTable(
    'timesheets_to_engineers',
    {
        engineerId: int('engineer_id').notNull(),
        timesheetId: int('timesheet_id').notNull(),
    },
    (t) => ({
        pk: primaryKey(t.engineerId, t.timesheetId),
    })
);

export const timesheetsToEngineersRelations = relations(
    timesheetsToEngineers,
    ({ one }) => ({
        timesheets: one(timesheets, {
            fields: [timesheetsToEngineers.timesheetId],
            references: [timesheets.id],
        }),
        engineers: one(engineers, {
            fields: [timesheetsToEngineers.engineerId],
            references: [engineers.id],
        }),
    })
);

export const timesheets = mysqlTable('timesheets', {
    id: serial('id').primaryKey().autoincrement(),
    work_provider: varchar('work_provider', { length: 128 }),
    date_of_work: timestamp('date_of_work').defaultNow(),
    order_num: varchar('order_num', { length: 128 }).notNull(),
    work_item: varchar('work_item', { length: 128 }).notNull(),
    quantity: int('quantity').notNull(),
    notes: varchar('notes', { length: 1024 }).notNull(),
    gang_price_split: varchar('gang_price_split', { length: 128 }).notNull(),
    created_at: timestamp('created_at').defaultNow(),
});

export type Timesheet = InferModel<typeof timesheets>;

export const timesheetsRelations = relations(timesheets, ({ many }) => ({
    timesheetsToEngineers: many(timesheetsToEngineers),
}));

export const engineers = mysqlTable('engineers', {
    id: serial('id').primaryKey().autoincrement(),
    firstName: varchar('firstName', { length: 128 }).notNull(),
    lastName: varchar('lastName', { length: 256 }).notNull(),
    birthDate: varchar('birthDate', { length: 32 }).notNull(),
});

export type Engineer = InferModel<typeof engineers>;

export const engineersRelations = relations(engineers, ({ many }) => ({
    timesheetsToEngineers: many(timesheetsToEngineers),
}));

export const timesheetsToEngineers = mysqlTable(
    'timesheets_to_engineers',
    {
        engineerId: int('engineer_id').notNull(),
        timesheetId: int('timesheet_id').notNull(),
    },
    (t) => ({
        pk: primaryKey(t.engineerId, t.timesheetId),
    })
);

export const timesheetsToEngineersRelations = relations(
    timesheetsToEngineers,
    ({ one }) => ({
        timesheets: one(timesheets, {
            fields: [timesheetsToEngineers.timesheetId],
            references: [timesheets.id],
        }),
        engineers: one(engineers, {
            fields: [timesheetsToEngineers.engineerId],
            references: [engineers.id],
        }),
    })
);

Kasper•8/7/23, 5:31 PM

I also fixed your many to many relation

Kasper•8/7/23, 5:31 PM

In the code quantity is changed from

 serial

 serial

int

int

max14OP•8/7/23, 5:36 PM

ah thank you that worked

max14OP•8/7/23, 5:37 PM

although I can't add multiple engineer Id's

Kasper•8/7/23, 5:37 PM

What do you mean by that?

max14OP•8/7/23, 5:38 PM

as in I would like multiple (or one) engineers to be able to own a timesheet

Kasper•8/7/23, 5:39 PM

You can do that. You need to add a new record to the timesheetsToEngineers table for each engineer

max14OP•8/7/23, 5:39 PM

ahhhh

max14OP•8/7/23, 5:40 PM

sorry im a bit of a noob with db relations

max14OP•8/7/23, 5:40 PM

thanks

Kasper•8/7/23, 5:40 PM

No worries!

Kasper•8/7/23, 5:40 PM

Don't be afraid to ask more

barry•8/10/23, 4:51 PM

I highly recommend postgres over mysql or vitess every day

barry•8/10/23, 4:51 PM

neon.tech is great

Kasper•8/17/23, 1:45 PM

@thatbarryguy Why?

KKasper @thatbarryguy Why?

barry•8/17/23, 2:14 PM

Well I like to have returning values, a proper enum and a proper boolean and not just a workaround with tinyint, and Vitess/Planetscale? Like come on, I need my foreign keys g

Drizzle ORM schema not working

Similar Threads

Similar Threads

Similar Threads