TTC
Theo's Typesafe Cult•2y ago
max14

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),
  })
);
11 Replies
max14
max14OP•2y ago
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],
    }),
  })
);
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
Kasper•2y ago
@maxlc14 serial is just an anlias for int not-null autoincrement. So you can only have it one place in each table 
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],
        }),
    })
);
I also fixed your many to many relation In the code quantity is changed from 
 serial 
 serial
to 
 int 
 int
max14
max14OP•2y ago
ah thank you that worked although I can't add multiple engineer Id's
Kasper
Kasper•2y ago
What do you mean by that?
max14
max14OP•2y ago
as in I would like multiple (or one) engineers to be able to own a timesheet
Kasper
Kasper•2y ago
You can do that. You need to add a new record to the timesheetsToEngineers table for each engineer
max14
max14OP•2y ago
ahhhh sorry im a bit of a noob with db relations thanks
Kasper
Kasper•2y ago
No worries! Don't be afraid to ask more
barry
barry•17mo ago
I highly recommend postgres over mysql or vitess every day neon.tech is great
Kasper
Kasper•17mo ago
@thatbarryguy Why?
barry
barry•17mo ago
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
Want results from more Discord servers?
Add your server