Drizzle Team•4mo ago

Updating multiple rows causes type error

I'm following the guide posted here https://orm.drizzle.team/learn/guides/update-many-with-different-value regarding updating multiple rows at once. My data is similar, but not the same:

const inputs = [
  {
    id: 9,
    order: 2,
  },
  {
    id: 10,
    order: 1,
  },
];

const inputs = [
  {
    id: 9,
    order: 2,
  },
  {
    id: 10,
    order: 1,
  },
];

The raw output is:

{
  sql: '(case when "routes"."id" = $1 then $2 when "routes"."id" = $3 then $4 end)',
  params: [ 9, 2, 10, 1 ]
}

{
  sql: '(case when "routes"."id" = $1 then $2 when "routes"."id" = $3 then $4 end)',
  params: [ 9, 2, 10, 1 ]
}

Unfortunately, this causes a type error when being submitted:

Database Error: error: column "order" is of type integer but expression is of type text

Database Error: error: column "order" is of type integer but expression is of type text

I assume this is because the sql is being converted to something like this:

case when "routes"."id" = 9 then "2"

case when "routes"."id" = 9 then "2"

therefore it's being recognized as a string whenever it's submitted to the database. But I could be wrong. Is it possible to cast this value somehow or otherwise inform Drizzle that it's supposed to be an integer and not a string?

Drizzle ORM - Update many with different values for each row

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

7 Replies

Mario564•4mo ago

What does the full update query look like? In Typescript (raw SQL would be nice too

dsf_2OP•4mo ago

How do I get the raw SQL?

Mario564•4mo ago

Sorry, I meant something like the "raw output" you provided above, but for the full query (or something close to the full query)

dsf_2OP•4mo ago

const sqlChunks: SQL[] = [];
const ids: number[] = [];

sqlChunks.push(sql`(case`);

for (const route of updateInfo) {
  sqlChunks.push(sql`when ${routes.id} = ${route.id} then ${route.order}`);
  ids.push(route.id);
}

sqlChunks.push(sql`end)`);

const finalSql: SQL = sql.join(sqlChunks, sql.raw(" "));

await db
  .update(routes)
  .set({ order: finalSql })
  .where(inArray(routes.id, ids));

const sqlChunks: SQL[] = [];
const ids: number[] = [];

sqlChunks.push(sql`(case`);

for (const route of updateInfo) {
  sqlChunks.push(sql`when ${routes.id} = ${route.id} then ${route.order}`);
  ids.push(route.id);
}

sqlChunks.push(sql`end)`);

const finalSql: SQL = sql.join(sqlChunks, sql.raw(" "));

await db
  .update(routes)
  .set({ order: finalSql })
  .where(inArray(routes.id, ids));

This is the code It's the same as what is in the linked guide working on the raw, one min

Mario564•4mo ago

Try casting the whole case statement to integer, like so:

sqlChunks.push(sql`end)::integer`);

sqlChunks.push(sql`end)::integer`);

dsf_2OP•4mo ago

It worked!! You are a legend thank you so much!

Mario564•4mo ago

You're welcome!

Gaming

Programming

Updating multiple rows causes type error