Drizzle Team•2y ago

TypeError when adding subquery to `drizzle.insert.values`

Hey all, I'm a bit new to drizzle so not sure if this is even possible or if I'm just doing something wrong. I'm getting type errors when using a subquery to select for companyId based on a company name:

    return await db.insert(widgetItem).values({
    name: "New Widget",
    companyId: db  // using subquery
      .select({ id: company.id })
      .from(company)
      .where(eq(
        company.name,
        userCompany
        )),
    typeId: widgetTypeId,
    slug: generateSlug(),
  });

    return await db.insert(widgetItem).values({
    name: "New Widget",
    companyId: db  // using subquery
      .select({ id: company.id })
      .from(company)
      .where(eq(
        company.name,
        userCompany
        )),
    typeId: widgetTypeId,
    slug: generateSlug(),
  });

but this checks out ok:

  return await db.insert(widgetItem).values({
    name: "New Widget",
    companyId: BigInt(2),  // hardcoded
    typeId: widgetTypeId,
    slug: generateSlug(),
  });

  return await db.insert(widgetItem).values({
    name: "New Widget",
    companyId: BigInt(2),  // hardcoded
    typeId: widgetTypeId,
    slug: generateSlug(),
  });

wondering if this is possible or will I have to send multiple queries? Cheers

5 Replies

JimOP•2y ago

My current work around is calling the db twice:

  const id = await db
    .select({ id: company.id })
    .from(company)
    .where(eq(company.name, userCompany));

  return await db.insert(widgetItem).values({
    name: "New Widget",
    companyId: id[0].id,
    typeId: widgetTypeId,
    slug: generateSlug(),
  });

  const id = await db
    .select({ id: company.id })
    .from(company)
    .where(eq(company.name, userCompany));

  return await db.insert(widgetItem).values({
    name: "New Widget",
    companyId: id[0].id,
    typeId: widgetTypeId,
    slug: generateSlug(),
  });

AlcaponeYou•2y ago

https://orm.drizzle.team/docs/crud#select-from-subquery

Quering with SQL-like syntax [CRUD] – DrizzleORM

Drizzle ORM | %s

JimOP•2y ago

Yeah I've used subqueries just fine in select queries, but when I use them in an insert typescript throws

AlcaponeYou•2y ago

might be a bug w/ drizzle. I haven't tried subquery on insert yet

JimOP•2y ago

Nice one, I looked on github and found this: https://github.com/drizzle-team/drizzle-orm/issues/343 Dan explains we can wrap the subquery using the sql template

  return await db.insert(widgetItem).values({
    name: "New Widget",
    companyId: sql`${db
      .select({ id: company.id })
      .from(company)
      .where(eq(company.name, userCompany))}`,
    typeId: widgetTypeId,
    slug: generateSlug(),
  });

  return await db.insert(widgetItem).values({
    name: "New Widget",
    companyId: sql`${db
      .select({ id: company.id })
      .from(company)
      .where(eq(company.name, userCompany))}`,
    typeId: widgetTypeId,
    slug: generateSlug(),
  });

GitHub

[BUG]: SQLite doesn't support subqueries for UPDATE ... RETURNING...

What version of drizzle-orm are you using? 0.23.2 Describe the Bug SQLite's INSERT ... RETURNING and UPDATE ... RETURNING support arbitrary expressions as returned values. Attempting to use thi...

Gaming

Programming

TypeError when adding subquery to `drizzle.insert.values`