Drizzle Team•8mo ago

Updating multiple rows with single SQL query doesn't work.

await db.execute(
      sql`UPDATE users SET approved = true WHERE id IN (${ids
        .map((id) => `'${id}'`)
        .join(', ')})`
    );

await db.execute(
      sql`UPDATE users SET approved = true WHERE id IN (${ids
        .map((id) => `'${id}'`)
        .join(', ')})`
    );

With the above code, I'm setting the approved column to be true. ids is an array of strings, that I convert to the form of 'id1', 'id2'. Passing a single value into ids works, but when more than one value is passed, nothing happens. There is no error returned. Bizarrely, when I console log the generated sql query (in the case of multiple IDs) and execute it manually on my database, everything works fine. I suspect it's something to do with the sql function, but I don't understand why it works when there's only one ID.

3 Replies

Torbaz•8mo ago

Is there any reason that you would not just use this?

await db
    .update(users)
    .set({
        approved: true,
    })
    .where(inArray(users.id, ids));

await db
    .update(users)
    .set({
        approved: true,
    })
    .where(inArray(users.id, ids));

Sillvva•8mo ago

The difference between these two approaches is like this:

const pgDialect = new PgDialect();
console.log(
    pgDialect.sqlToQuery(sql`UPDATE users SET approved = true WHERE id IN (${["1", "2", "3"].map((id) => `'${id}'`).join(", ")})`)
);
// {
//   sql: 'UPDATE users SET approved = true WHERE id IN ($1)',
//   params: [ "'1', '2', '3'" ]
// }

console.log(
    db
        .update(users)
        .set({ approved: true })
        .where(inArray(users.id, ["1", "2", "3"]))
        .toSQL()
);
// {
//   sql: 'update "users" set "approved" = $1 where "users"."id" in ($2, $3, $4)',
//   params: [ true, '1', '2', '3' ]
// }

const pgDialect = new PgDialect();
console.log(
    pgDialect.sqlToQuery(sql`UPDATE users SET approved = true WHERE id IN (${["1", "2", "3"].map((id) => `'${id}'`).join(", ")})`)
);
// {
//   sql: 'UPDATE users SET approved = true WHERE id IN ($1)',
//   params: [ "'1', '2', '3'" ]
// }

console.log(
    db
        .update(users)
        .set({ approved: true })
        .where(inArray(users.id, ["1", "2", "3"]))
        .toSQL()
);
// {
//   sql: 'update "users" set "approved" = $1 where "users"."id" in ($2, $3, $4)',
//   params: [ true, '1', '2', '3' ]
// }

The way it binds the values in the query is the issue here. There is some SQL injection magic and possibly other transformations happening during the binding that breaks the first approach. If there is some reason the 2nd approach doesn't work, you'll likely need to build your SQL step by step like this: https://orm.drizzle.team/docs/sql#sqlempty

Drizzle ORM - Magic sql`` operator

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

gfungOP•8mo ago

Ahh okay, I understand, thank you! I wasn't aware of the .inArray() function 😭

Gaming

Programming

Updating multiple rows with single SQL query doesn't work.