https://www.prisma.io/ logo
Join Slack
Powered by
Hi, is there way to reuse $rawQuery partial condit...
# orm-help
t
Hi, is there way to reuse $rawQuery partial condition replace safely? ex)
Copy code
select
  ra.company_id as company_id,
  ra.user_id as user_id,
from companies as cs
join users as us  on cs.id = us.company_id
where 
  user.score >= ${score}
  ${replacableCondition}
;
In current, my codes have many similar sql scripts for sql injection protection like above except for writing raw query in ${replacableCondition} position.
✅ 1
This is simplified than in fact my codes which uses builtin db function case, group by join etc. So raw query is comfortable to our complex queries.
https://www.prisma.io/docs/concepts/components/prisma-client/raw-database-access#parameterized-queries queryRawUnsafe and parameterized queryies suitable for me.
As with tagged templates, Prisma Client escapes all variables.
👍🏾 1
a
Hey Takeo 👋🏾 Just a clarification, have you managed to resolve the issue?
t
Yes. In fact, I didn’t still rewrite my codes and test, but I expect it would work like
Copy code
function someQuery (where: string[], ...parameters: QueryParameter[]) {
  return prisma.$queryRawUnsafe(`select
    ra.company_id as company_id,
    ra.user_id as user_id,
  from companies as cs
  join users as us  on cs.id = us.company_id
  where 
    ${where.join(' \r\n')}
  ;`, ...parameters)
}
Our input should be 
someQuery(['company.id = $1', 'and', 'üser.id = $2'], ...[varCompanyId, userId])
if not smartly in postgresql. More wisely, having combination in fixed position variable.
Copy code
function someQuery (companyId: number | string, where: string[],  ...parameters: QueryParameters[]) {
  return prisma.$queryRawUnsafe(`select
    ra.company_id as company_id,
    ra.user_id as user_id,
  from companies as cs
  join users as us  on cs.id = us.company_id
  where 
    company.id = ${parameters.length + 1}
    ${where.join(' \r\n')}
  ;`
  , ...parameters, companyId,
)
}
There may be more smart way, but code above was enough to my query grouping.
If I make it is more wiser, the string wrap in an instance, and append string method, queryInstance.where([‘comanyId’, ‘=’, companyId]) and append companyId = $1 to raw string, and paramerters.push(companyId), inner state and final result input prisma.$queryRawUnsafe like another orm but we should use prisma api if easily write query. My solution seems to be over hack.
71 Views
Open in Slack
PreviousNext
Powered by