Build an SQL Query from Variables with a Prisma Raw Query (prisma.$queryRaw)

On a recent project, I needed to break out Prisma’s query builder to write a Postgres-specific query. Using prisma.$queryRaw, I was able to write a basic query that returned the data I needed. However, the problem was that I also needed the query to filter and order the data in different ways.

My First Go at a Sorting Query

My first take at the SQL statement that sorted the data was to build up a query based on the sorting options available. I’d then execute it with prisma.$queryRaw. My first go at it looked something like this:

// 🛑 Produces a Prisma Error
const sortingClause =
    sortOption === SortOptions.LowHigh
      ? `ORDER BY "price" ASC`
      : `ORDER BY "price" DESC`;
const assets = await prisma.$queryRaw`
 SELECT
   "id",
   "name",
   "description",
   "date",
   "price",
 FROM
   "Asset"
 ORDER BY
   ${sortingClause}`;

I ran the test for the sorting requirement and received a Prisma error:

Invalid `prisma.$queryRaw()` invocation: Raw query failed. Code: `42601`. Message: `db error: ERROR: syntax error at or near "$1"`

I expected the query to return the sorted results and wondered if I had written my SQL query correctly. To verify my query, I ran it in TablePlus, and, sure enough, I got back the sorted results.

Using Prisma.sql to Compose a Sql Query

Next, I looked into how prisma.$queryRaw works. I discovered that Prisma prevents the accidental introduction of SQL injection vulnerabilities by ensuring the use of templated strings. So, you can use string variables only for data values, not for identifying database schema or SQL keywords. I knew I didn’t want to jump over to the less safe version of prisma.$queryRaw, prisma.$queryRawUnsafe. So, I had to look into other options. It turns out there is a fantastic Prisma helper that can help compose SQL queries.

Enter the Prisma.sql helper, this helper allows you to build up queries that can be executed with prisma.$queryRaw. After I implemented my sorting query using this new helper the code looked similar to my first attempt, let’s take a look:

// ✅ Properly composes a SQL statement with prisma.$queryRaw
const sortingClause =
   sort === SortOptions.LowHigh ?
   Prisma.sql`ORDER BY "price" ASC` :
   Prisma.sql`ORDER BY "price" DESC`;
const assets = await prisma.$queryRaw`
 SELECT
   "id",
   "name",
   "description",
   "date",
   "price",
 FROM
   "Asset"
 ORDER BY
   ${sortingClause}`;

Success! Using the Prisma helper, I could compose a query based on the sorting option that the user had selected with no need to use the unsafe query raw function.

Bonus Tip!

While writing the sorting query, I found another fun Prisma helper. Prisma.empty returns an empty templated string, a great way to provide no SQL statement, say when the ordering of the results does not matter.

This is an updated version of a post originally published in August 2022.