DT
Drizzle Team•13mo ago
jakeleventhal

How to use sql template strings

await tx.update(inventoryItems).set({
        masterProductId: sql`
        CASE
          ${chunk
            .map((update) => `WHEN id in (${update.ids.map((id) => `'${id}'`)}) THEN '${update.masterProductId}'`)
            .join('\n')}
        END
        `
      });
await tx.update(inventoryItems).set({
        masterProductId: sql`
        CASE
          ${chunk
            .map((update) => `WHEN id in (${update.ids.map((id) => `'${id}'`)}) THEN '${update.masterProductId}'`)
            .join('\n')}
        END
        `
      });
Query: update "InventoryItem" set "masterProductId" = 
        CASE
          $1
        END
         -- params: ["WHEN id in ('450a5c19-af32-4ee6-a565-2ba6b4664549','742a257d-23da-4092-bfa6-ba7d8ba6a4f8','450a5c19-af32-4ee6-a565-2ba6b4664549','742a257d-23da-4092-bfa6-ba7d8ba6a4f8','ea8ecac1-c73a-4590-8cda-7ce4344a95ca','03dca948-f970-4a42-83c8-999e1a61e2b6') THEN '1232a182-035c-4534-bc4b-402f9a00bced'"]
Jan 11, 12:09:45PM debug: req.url /api/master-products/bulk/map/save 
Jan 11, 12:09:45PM error: PostgresError: syntax error at or near "END"
Query: update "InventoryItem" set "masterProductId" = 
        CASE
          $1
        END
         -- params: ["WHEN id in ('450a5c19-af32-4ee6-a565-2ba6b4664549','742a257d-23da-4092-bfa6-ba7d8ba6a4f8','450a5c19-af32-4ee6-a565-2ba6b4664549','742a257d-23da-4092-bfa6-ba7d8ba6a4f8','ea8ecac1-c73a-4590-8cda-7ce4344a95ca','03dca948-f970-4a42-83c8-999e1a61e2b6') THEN '1232a182-035c-4534-bc4b-402f9a00bced'"]
Jan 11, 12:09:45PM debug: req.url /api/master-products/bulk/map/save 
Jan 11, 12:09:45PM error: PostgresError: syntax error at or near "END"
this should be resolving to valid sql
24 Replies
jakeleventhal
jakeleventhalOP•13mo ago
await tx
        .update(inventoryItems)
        .set({
          masterProductId: sql`
        CASE
            WHEN id in ('asdf','fff','asdf','asdf','23f','1ijf') THEN '1232a182-035c-4534-bc4b-402f9a00bced'
            WHEN id in ('asdf','fff','asdf','asdf','23f','1ijf') THEN '1232a182-035c-4534-bc4b-402f9a00bced'
            WHEN id in ('asdf','fff','asdf','asdf','23f','1ijf') THEN '1232a182-035c-4534-bc4b-402f9a00bced'
            WHEN id in ('asdf','fff','asdf','asdf','23f','1ijf') THEN '1232a182-035c-4534-bc4b-402f9a00bced'
            WHEN id in ('asdf','fff','asdf','asdf','23f','1ijf') THEN '1232a182-035c-4534-bc4b-402f9a00bced'
            ELSE ${inventoryItems.masterProductId}
        END
        `
        })
await tx
        .update(inventoryItems)
        .set({
          masterProductId: sql`
        CASE
            WHEN id in ('asdf','fff','asdf','asdf','23f','1ijf') THEN '1232a182-035c-4534-bc4b-402f9a00bced'
            WHEN id in ('asdf','fff','asdf','asdf','23f','1ijf') THEN '1232a182-035c-4534-bc4b-402f9a00bced'
            WHEN id in ('asdf','fff','asdf','asdf','23f','1ijf') THEN '1232a182-035c-4534-bc4b-402f9a00bced'
            WHEN id in ('asdf','fff','asdf','asdf','23f','1ijf') THEN '1232a182-035c-4534-bc4b-402f9a00bced'
            WHEN id in ('asdf','fff','asdf','asdf','23f','1ijf') THEN '1232a182-035c-4534-bc4b-402f9a00bced'
            ELSE ${inventoryItems.masterProductId}
        END
        `
        })
this code works just fine
Angelelz
Angelelz•13mo ago
I think the inArray function from drizzle does that for you 
masterProductId: sql`CASE WHEN ${inArray(inventoryItems.id, update.ids)} THEN ${inventoryItems.masterProductId} <Rest of the query>`
masterProductId: sql`CASE WHEN ${inArray(inventoryItems.id, update.ids)} THEN ${inventoryItems.masterProductId} <Rest of the query>`
jakeleventhal
jakeleventhalOP•13mo ago
same result 
      await tx
        .update(inventoryItems)
        .set({
          masterProductId: sql`
            CASE
              ${chunk
                .map((update) => sql`WHEN ${inArray(inventoryItems.id, update.ids)} THEN '${update.masterProductId}'`)
                .join('\n')}
              ELSE ${inventoryItems.masterProductId}
            END`
        })
      await tx
        .update(inventoryItems)
        .set({
          masterProductId: sql`
            CASE
              ${chunk
                .map((update) => sql`WHEN ${inArray(inventoryItems.id, update.ids)} THEN '${update.masterProductId}'`)
                .join('\n')}
              ELSE ${inventoryItems.masterProductId}
            END`
        })
Jan 11, 12:50:21PM error: PostgresError: syntax error at or near "ELSE" seems to be an issue with the template string
Angelelz
Angelelz•13mo ago
Call toSQL() at the end and lets check what's producing
jakeleventhal
jakeleventhalOP•13mo ago
{
  sql: 'update "InventoryItem" set "masterProductId" = \n' +
    '            CASE\n' +
    '              $1\n' +
    '              ELSE "InventoryItem"."masterProductId"\n' +
    '            END',
  params: [ '[object Object]\n[object Object]' ]
}
{
  sql: 'update "InventoryItem" set "masterProductId" = \n' +
    '            CASE\n' +
    '              $1\n' +
    '              ELSE "InventoryItem"."masterProductId"\n' +
    '            END',
  params: [ '[object Object]\n[object Object]' ]
}
if i remove the sql and and the inArray to put it how it is in the OP, then i get this 
{
  sql: 'update "InventoryItem" set "masterProductId" = \n' +
    '            CASE\n' +
    '              $1\n' +
    '              ELSE "InventoryItem"."masterProductId"\n' +
    '            END',
  params: [
    "WHEN id in ('450a5c19-af32-4ee6-a565-2ba6b4664549','742a257d-23da-4092-bfa6-ba7d8ba6a4f8','450a5c19-af32-4ee6-a565-2ba6b4664549','742a257d-23da-4092-bfa6-ba7d8ba6a4f8','ea8ecac1-c73a-4590-8cda-7ce4344a95ca','03dca948-f970-4a42-83c8-999e1a61e2b6') THEN 59c62270-32bb-4cbd-9d25-0d856ba4965b\n" +
      "WHEN id in ('a6a75464-6848-4291-af06-8d95457f5ed9','a6a75464-6848-4291-af06-8d95457f5ed9','855f99cc-e10b-487e-842a-582341b5e866','00b078fc-c560-4334-936e-babca166652b','e203dfea-d837-43b5-92c5-d7d33d2f866d') THEN d7eb975c-6787-41b6-81ec-561027e49627"
  ]
}
{
  sql: 'update "InventoryItem" set "masterProductId" = \n' +
    '            CASE\n' +
    '              $1\n' +
    '              ELSE "InventoryItem"."masterProductId"\n' +
    '            END',
  params: [
    "WHEN id in ('450a5c19-af32-4ee6-a565-2ba6b4664549','742a257d-23da-4092-bfa6-ba7d8ba6a4f8','450a5c19-af32-4ee6-a565-2ba6b4664549','742a257d-23da-4092-bfa6-ba7d8ba6a4f8','ea8ecac1-c73a-4590-8cda-7ce4344a95ca','03dca948-f970-4a42-83c8-999e1a61e2b6') THEN 59c62270-32bb-4cbd-9d25-0d856ba4965b\n" +
      "WHEN id in ('a6a75464-6848-4291-af06-8d95457f5ed9','a6a75464-6848-4291-af06-8d95457f5ed9','855f99cc-e10b-487e-842a-582341b5e866','00b078fc-c560-4334-936e-babca166652b','e203dfea-d837-43b5-92c5-d7d33d2f866d') THEN d7eb975c-6787-41b6-81ec-561027e49627"
  ]
}
which looks correct missing quotes after the Id after "THEN" but same result when i add back 
{
  sql: 'update "InventoryItem" set "masterProductId" = \n' +
    '            CASE\n' +
    '              $1\n' +
    '              ELSE "InventoryItem"."masterProductId"\n' +
    '            END',
  params: [
    "WHEN id in ('450a5c19-af32-4ee6-a565-2ba6b4664549','742a257d-23da-4092-bfa6-ba7d8ba6a4f8','450a5c19-af32-4ee6-a565-2ba6b4664549','742a257d-23da-4092-bfa6-ba7d8ba6a4f8','ea8ecac1-c73a-4590-8cda-7ce4344a95ca','03dca948-f970-4a42-83c8-999e1a61e2b6') THEN '218cdc08-3896-4690-accc-e327b2436ea5'\n" +
      "WHEN id in ('a6a75464-6848-4291-af06-8d95457f5ed9','a6a75464-6848-4291-af06-8d95457f5ed9','855f99cc-e10b-487e-842a-582341b5e866','00b078fc-c560-4334-936e-babca166652b','e203dfea-d837-43b5-92c5-d7d33d2f866d') THEN 'c3a67ded-e827-474b-8814-100c8997718b'"
  ]
}
{
  sql: 'update "InventoryItem" set "masterProductId" = \n' +
    '            CASE\n' +
    '              $1\n' +
    '              ELSE "InventoryItem"."masterProductId"\n' +
    '            END',
  params: [
    "WHEN id in ('450a5c19-af32-4ee6-a565-2ba6b4664549','742a257d-23da-4092-bfa6-ba7d8ba6a4f8','450a5c19-af32-4ee6-a565-2ba6b4664549','742a257d-23da-4092-bfa6-ba7d8ba6a4f8','ea8ecac1-c73a-4590-8cda-7ce4344a95ca','03dca948-f970-4a42-83c8-999e1a61e2b6') THEN '218cdc08-3896-4690-accc-e327b2436ea5'\n" +
      "WHEN id in ('a6a75464-6848-4291-af06-8d95457f5ed9','a6a75464-6848-4291-af06-8d95457f5ed9','855f99cc-e10b-487e-842a-582341b5e866','00b078fc-c560-4334-936e-babca166652b','e203dfea-d837-43b5-92c5-d7d33d2f866d') THEN 'c3a67ded-e827-474b-8814-100c8997718b'"
  ]
}
if i copy in the params it works just fine its gotta be something to do with the ending of the last part bcause if i remove the else clause, it says the syntax error is at or near "END"
jakeleventhal
jakeleventhalOP•13mo ago
this also works
No description
jakeleventhal
jakeleventhalOP•13mo ago
but only for one record narrowed down the problem to being that it fails when i do the mapping of multiple rows joined with .join(', ') 
// DOES NOT WORK

        .set({
          masterProductId: sql`
            CASE
              ${[...Array(3)]
                .map(
                  () =>
                    sql`WHEN ${inventoryItems.id} in (${chunk[0].ids.map((id) => `'${id}'`).join(', ')}) THEN ${
                      chunk[0].masterProductId
                    }`
                )
                .join('\n')}
              ELSE ${inventoryItems.masterProductId}
            END`
        })
// DOES NOT WORK

        .set({
          masterProductId: sql`
            CASE
              ${[...Array(3)]
                .map(
                  () =>
                    sql`WHEN ${inventoryItems.id} in (${chunk[0].ids.map((id) => `'${id}'`).join(', ')}) THEN ${
                      chunk[0].masterProductId
                    }`
                )
                .join('\n')}
              ELSE ${inventoryItems.masterProductId}
            END`
        })
// DOES WORK
        .set({
          masterProductId: sql`
            CASE
              ${sql`WHEN ${inventoryItems.id} in (${chunk[0].ids.map((id) => `'${id}'`).join(', ')}) THEN ${
                chunk[0].masterProductId
              }`}
              ELSE ${inventoryItems.masterProductId}
            END`
        })
// DOES WORK
        .set({
          masterProductId: sql`
            CASE
              ${sql`WHEN ${inventoryItems.id} in (${chunk[0].ids.map((id) => `'${id}'`).join(', ')}) THEN ${
                chunk[0].masterProductId
              }`}
              ELSE ${inventoryItems.masterProductId}
            END`
        })
i think it might be a bug or something?
Angelelz
Angelelz•13mo ago
Man, one sec, I'm confused. What is chunk? This is wrong This is wrong too
jakeleventhal
jakeleventhalOP•13mo ago
chunk is a just an array of {ids: string[]; masterProductId: string} maybe its becuase im trying to do ".join" on a SQL object so it stringifies it how else would you plug in multiple dynamically though why wrong? when you hardocde in those parameters it works 
        .set({
          masterProductId: sql`
            CASE
              ${chunk
                .map((update) => {
                  console.log(`WHEN id in (${update.ids.map((id) => `'${id}'`)}) THEN '${update.masterProductId}'`);
                  return `WHEN id in (${update.ids.map((id) => `'${id}'`)}) THEN '${update.masterProductId}'`;
                })
                .join('\n')}
              ELSE ${inventoryItems.masterProductId}
            END`
        })
        .set({
          masterProductId: sql`
            CASE
              ${chunk
                .map((update) => {
                  console.log(`WHEN id in (${update.ids.map((id) => `'${id}'`)}) THEN '${update.masterProductId}'`);
                  return `WHEN id in (${update.ids.map((id) => `'${id}'`)}) THEN '${update.masterProductId}'`;
                })
                .join('\n')}
              ELSE ${inventoryItems.masterProductId}
            END`
        })
logged output when i make chunk of length 1: 
WHEN id in ('450a5c19-af32-4ee6-a565-2ba6b4664549','742a257d-23da-4092-bfa6-ba7d8ba6a4f8','450a5c19-af32-4ee6-a565-2ba6b4664549','742a257d-23da-4092-bfa6-ba7d8ba6a4f8','ea8ecac1-c73a-4590-8cda-7ce4344a95ca','03dca948-f970-4a42-83c8-999e1a61e2b6') THEN '290d0d78-1816-4537-8d34-333bdc4fed85'
WHEN id in ('450a5c19-af32-4ee6-a565-2ba6b4664549','742a257d-23da-4092-bfa6-ba7d8ba6a4f8','450a5c19-af32-4ee6-a565-2ba6b4664549','742a257d-23da-4092-bfa6-ba7d8ba6a4f8','ea8ecac1-c73a-4590-8cda-7ce4344a95ca','03dca948-f970-4a42-83c8-999e1a61e2b6') THEN '290d0d78-1816-4537-8d34-333bdc4fed85'
looks correct and plugging in that value instead of the ${chunk....} code makes the query work and again, works without the mapping, even without the sql operator
Angelelz
Angelelz•13mo ago
Remember that drizzle is trying to pass you input as parameters and it makes wrong syntax if you pass it strings with objects like this
jakeleventhal
jakeleventhalOP•13mo ago
more succinctly: DOES NOT WORK 
case
  ${`WHEN id in ('a', 'b', 'c') THEN 'd'`}
  ELSE ${inventoryItems.masterProductId}
END
case
  ${`WHEN id in ('a', 'b', 'c') THEN 'd'`}
  ELSE ${inventoryItems.masterProductId}
END
WORKS 
case
  WHEN id in ('a', 'b', 'c') THEN 'd'
  ELSE ${inventoryItems.masterProductId}
END
case
  WHEN id in ('a', 'b', 'c') THEN 'd'
  ELSE ${inventoryItems.masterProductId}
END
not sure what the distinction is here
Angelelz
Angelelz•13mo ago
I'm kinda don't fully understand your setup or what you're trying to do But you might need to return sql`` from your map function
jakeleventhal
jakeleventhalOP•13mo ago
yeah the problem is the .join('\n') 
// THIS WORKS
${sql`WHEN id in ('a', 'b', 'c') THEN 'd'`}
// THIS WORKS
${sql`WHEN id in ('a', 'b', 'c') THEN 'd'`}
// THIS DOES NOT WORK
${[sql`WHEN id in ('a', 'b', 'c') THEN 'd'`].join('\n')}
// THIS DOES NOT WORK
${[sql`WHEN id in ('a', 'b', 'c') THEN 'd'`].join('\n')}
how can you build up the interior of the sql string without converting it to a string
Hebilicious
Hebilicious•13mo ago
fwiw i kinda struggled with something similar today https://discord.com/channels/1043890932593987624/1195077997431107728 ended-up doing stuff like 
sql`foo${sql.raw`a${stuff}b`}bar`
sql`foo${sql.raw`a${stuff}b`}bar`
which looks wrong, but appears to be safe
Angelelz
Angelelz•13mo ago
I'll help you later tonight. If you're still struggling, can you ping me in about 6 hours?
jakeleventhal
jakeleventhalOP•13mo ago
@Hebilicious wrappiong it in sql.raw worked..? using sql`` does not work
No description
Hebilicious
Hebilicious•13mo ago
did that solve your issue ?:D
jakeleventhal
jakeleventhalOP•13mo ago
yes weird behavior though: 
// doesnt work
sql`${chunk....}`

// does work
sql.raw(chunk....)
// doesnt work
sql`${chunk....}`

// does work
sql.raw(chunk....)
i thought those were equivalent? @Angelelz if theyre supposed to be, maybe this is a bug after all
Angelelz
Angelelz•13mo ago
They are not equivalent, raw inlines the arguments in the query, making it susceptible to injection attacks Let me look into it later and I'll let you know
andreasb
andreasb•13mo ago
Did you end up with a working (and perhaps non-susceptible to injections) solution to this? 🙂
jakeleventhal
jakeleventhalOP•13mo ago
no. stuck with the sql.raw answer weird bc in theory the same code simplest repro was here
andreasb
andreasb•13mo ago
Ait! Thanks for answering. Maybe Angelelz has come up with something, will check back a bit later 🙂 @jakeleventhal Could this be helpful? Some very preliminary testing from my side, but it does seem to work for my "re-arrange sort_order for images in my gallery" use case. 
const statementUpdateSortOrder = sql.join(
    array.map((item, index) =>
        sql`WHEN id = ${item.id} THEN ${index}::integer`
    ),
    sql` `
);

await db.update(TableName)
    .set({ sort_order: sql`CASE ${statementUpdateSortOrder} END` })
    .where(inArray(TableName.id, array.map(item => item.id)));
const statementUpdateSortOrder = sql.join(
    array.map((item, index) =>
        sql`WHEN id = ${item.id} THEN ${index}::integer`
    ),
    sql` `
);

await db.update(TableName)
    .set({ sort_order: sql`CASE ${statementUpdateSortOrder} END` })
    .where(inArray(TableName.id, array.map(item => item.id)));
jakeleventhal
jakeleventhalOP•13mo ago
so essentially identical code but sql.join instead of .join('\n') i can try it out
jakeleventhal
jakeleventhalOP•13mo ago
this diff worked for me
No description

Did you find this page helpful?