Drizzle Team•6mo ago

I'm getting the following error when trying to fetch a data with relations.

I have invoice table with a id of type PgSerial, and invoiceAmenities table has invoiceId as foreign key which is of type integer. The select query from invoice table with invoiceRelations fail stating "operator does not exist: text = integer" at the following point of the query: "invoiceId" = "invoice"."id". The generated query with the error log is attached. The drizzle query that I'm running is as follows:

  const invoiceData = await db.query.invoice.findFirst({
      where: eq(invoice.id, invoiceId as number),
      with: {
        amenities: {
          columns: {
            name: true,
            quantity: true,
            rate: true,
          },
        },
      },
    });

  const invoiceData = await db.query.invoice.findFirst({
      where: eq(invoice.id, invoiceId as number),
      with: {
        amenities: {
          columns: {
            name: true,
            quantity: true,
            rate: true,
          },
        },
      },
    });

The relevant schemas:

export const invoice = pgTable("Invoice", {
  id: serial("id").primaryKey().notNull(),
  guestName: text("guestName").notNull(),
  invoiceDate: timestamp("invoiceDate").notNull(),
  checkinDate: timestamp("checkinDate").notNull(),
  checkoutDate: timestamp("checkoutDate").notNull(),
});

export const invoiceAmenities = pgTable(
  "InvoiceAmenities",
  {
    id: serial("id").primaryKey().notNull(),
    name: text("name").notNull(),
    quantity: integer("quantity").notNull(),
    rate: integer("rate").notNull(),
    invoiceId: integer("invoiceId")
      .notNull()
      .references(() => invoice.id, {
        onDelete: "cascade",
        onUpdate: "cascade",
      }),
  },
  (table) => {
    return {
      invoiceIdIdx: index("InvoiceAmenities_invoiceId_idx").on(table.invoiceId),
    };
  }
);

// invoiceAccomodation, invoiceFood schemas are skipped for being concise
export const invoiceRelations = relations(invoice, ({ many }) => ({
  accomodation: many(invoiceAccomodation),
  food: many(invoiceFood),
  amenities: many(invoiceAmenities),
}));

export const amenitiesRelations = relations(invoiceAmenities, ({ one }) => ({
  invoice: one(invoice, {
    fields: [invoiceAmenities.invoiceId],
    references: [invoice.id],
  }),
}));

export const invoice = pgTable("Invoice", {
  id: serial("id").primaryKey().notNull(),
  guestName: text("guestName").notNull(),
  invoiceDate: timestamp("invoiceDate").notNull(),
  checkinDate: timestamp("checkinDate").notNull(),
  checkoutDate: timestamp("checkoutDate").notNull(),
});

export const invoiceAmenities = pgTable(
  "InvoiceAmenities",
  {
    id: serial("id").primaryKey().notNull(),
    name: text("name").notNull(),
    quantity: integer("quantity").notNull(),
    rate: integer("rate").notNull(),
    invoiceId: integer("invoiceId")
      .notNull()
      .references(() => invoice.id, {
        onDelete: "cascade",
        onUpdate: "cascade",
      }),
  },
  (table) => {
    return {
      invoiceIdIdx: index("InvoiceAmenities_invoiceId_idx").on(table.invoiceId),
    };
  }
);

// invoiceAccomodation, invoiceFood schemas are skipped for being concise
export const invoiceRelations = relations(invoice, ({ many }) => ({
  accomodation: many(invoiceAccomodation),
  food: many(invoiceFood),
  amenities: many(invoiceAmenities),
}));

export const amenitiesRelations = relations(invoiceAmenities, ({ one }) => ({
  invoice: one(invoice, {
    fields: [invoiceAmenities.invoiceId],
    references: [invoice.id],
  }),
}));

5 Replies

WebDevSayantan•6mo ago

I tried deleting the tables and recreating them with same schemas and this is what I get(attached). My guess is that Postgres isn't able to convert serial to integer automatically. What should I do to fix this?

Mykhailo•6mo ago

Hello, @bhoboghurey! It should work. Could you please tell me what driver are you using & provide data for this query:

  const invoiceData = await db.query.invoice.findFirst({
      where: eq(invoice.id, invoiceId as number), // I need invoiceId vlaue
      with: {
        amenities: {
          columns: {
            name: true,
            quantity: true,
            rate: true,
          },
        },
      },
    });

  const invoiceData = await db.query.invoice.findFirst({
      where: eq(invoice.id, invoiceId as number), // I need invoiceId vlaue
      with: {
        amenities: {
          columns: {
            name: true,
            quantity: true,
            rate: true,
          },
        },
      },
    });

WebDevSayantan•6mo ago

Hi @Mykhailo , I'm using postgres as driver and invoiceId in this particular scenario was 3. While debugging, I deleted the table data and then ran db:push to get the following error, it seems like a foreign key as integer type is not matching with the primary key which is of serial type.

Mykhailo•6mo ago

Strange, I could not reproduce the issue. Could you drop invoiceAmenities table and its constraints and then run drizzle-kit push again? Or is it possible to provide reproduction repo? @bhoboghurey You likely had a non-integer type initially but then changed it to an integer, which is why you are encountering this issue.

WebDevSayantan•6mo ago

Thanks, dropping and recreating the tables solved the issue! Not sure, how it was caused in the first place though as I created the tables using drizzle's db push only. But anyway, can proceed now. Thanks a lot.

Gaming

Programming

I'm getting the following error when trying to fetch a data with relations.