Prisma•2mo ago

TypedSQL: Passing null values into raw SQL?

I am currently using an aggregate query that returns a boolean that is either true or false if some user (current user) has reacted to a given post. Here is the query (thanks Jon Harrell--if the code has issues it's likely because I've been modifying it):

-- @param {String} $1:Post ID
-- @param {String} $2:User ID
SELECT
    p."id" AS "postId",
    reaction_types."id" AS "reactionTypeId",
    CAST(COUNT(reactions."id") AS INTEGER) AS "reactionCount",
    reaction_types."emoji" AS "emoji",
    reaction_types."name" AS "emojiName",
    CASE
        WHEN COUNT(CASE WHEN reactions."user_id" = $2 THEN 1 END) > 0 THEN TRUE
        ELSE FALSE
    END AS "reacted"
FROM posts p
LEFT JOIN reactions ON p."id" = reactions."post_id"
LEFT JOIN reaction_types ON reaction_types."id" = reactions."reaction_type_id"
WHERE p."id" = $1::uuid
GROUP BY p."id", p."user_id", reaction_types."id", reaction_types."name", reaction_types."emoji"
ORDER BY p."id", reaction_types."id";

-- @param {String} $1:Post ID
-- @param {String} $2:User ID
SELECT
    p."id" AS "postId",
    reaction_types."id" AS "reactionTypeId",
    CAST(COUNT(reactions."id") AS INTEGER) AS "reactionCount",
    reaction_types."emoji" AS "emoji",
    reaction_types."name" AS "emojiName",
    CASE
        WHEN COUNT(CASE WHEN reactions."user_id" = $2 THEN 1 END) > 0 THEN TRUE
        ELSE FALSE
    END AS "reacted"
FROM posts p
LEFT JOIN reactions ON p."id" = reactions."post_id"
LEFT JOIN reaction_types ON reaction_types."id" = reactions."reaction_type_id"
WHERE p."id" = $1::uuid
GROUP BY p."id", p."user_id", reaction_types."id", reaction_types."name", reaction_types."emoji"
ORDER BY p."id", reaction_types."id";

And I want to be able to pass a null userId value if there is no current user into

prisma.$queryRawTyped(getPostReactionsSql(postId, userId))

prisma.$queryRawTyped(getPostReactionsSql(postId, userId))

but unfortunately this does not work with the type error

Argument of type 'string | null | undefined' is not assignable to parameter of type 'string'. Type 'undefined' is not assignable to type 'string'.ts(2345)

Up until now, I've been passing two functions conditionally when userId is not known:

  const postReactions = (await prisma.$queryRawTyped(
    userId
      ? getPostReactionsSql(postId, userId)
      : getPostReactionsNoUserSql(postId),
  )) as getPostReactionsSql.Result[] | getPostReactionsNoUserSql.Result[];

  const postReactions = (await prisma.$queryRawTyped(
    userId
      ? getPostReactionsSql(postId, userId)
      : getPostReactionsNoUserSql(postId),
  )) as getPostReactionsSql.Result[] | getPostReactionsNoUserSql.Result[];

But this doesn't play well with the type system, hence the explicit type casting since the only accepted types don't include null or undefined. Is there a way to type this userId value as nullable/nullish, and execute this query without it? Any help is greatly appreciated!

1 Reply

Yetzederixx•2mo ago

it seems like userId isn't actually set to null prior to passing in, give it some initialization to null and see if that helps

Gaming

Programming

TypedSQL: Passing null values into raw SQL?