How do I add a conditional set clause to a slonik sql query?

Question

I'm building a postgres query using slonik to update a user table. The passwordHash column should be changed only if the passwordHash variable is not null, otherwise it should be left untouched.

I've tried with a nested rule:

import { createPool, sql } from 'slonik'

const dbPool = createPool()

const updateUser = await dbPool.connect(async (conn) => {
  return conn.one(sql`
    UPDATE users 
    SET "name" = ${name},
      "email" = ${email},
      ${passwordHash ? sql`"passwordHash" = ${passwordHash},` : ''}
    WHERE "id" = ${id} 
    RETURNING *
  `)
})

But I'm getting the error: syntax error at or near "$3"

What's the correct way to do that?

score 0 · Answer 1 · answered Jun 09 '22 at 15:48

probably a bit late, but you could use coalesce.

const updateUser = await dbPool.connect(async (conn) => {
  return conn.one(sql`
    UPDATE users 
    SET "name" = ${name},
      "email" = ${email},
      "passwordHash" = coalesce(${passwordHash}, passwordHash)
    WHERE "id" = ${id} 
    RETURNING *
  `)
})

if passwordHash can also be undefined, go coalesce(${passwordHash ?? null}, passwordHash)

How do I add a conditional set clause to a slonik sql query?

1 Answers1