Harness-engineering prisma-raw-queries

Prisma Raw Queries

install

source · Clone the upstream repo

git clone https://github.com/Intense-Visions/harness-engineering

Claude Code · Install into ~/.claude/skills/

T=$(mktemp -d) && git clone --depth=1 https://github.com/Intense-Visions/harness-engineering "$T" && mkdir -p ~/.claude/skills && cp -r "$T/agents/skills/claude-code/prisma-raw-queries" ~/.claude/skills/intense-visions-harness-engineering-prisma-raw-queries && rm -rf "$T"

manifest: agents/skills/claude-code/prisma-raw-queries/SKILL.md

source content

Prisma Raw Queries

Execute type-safe raw SQL with $queryRaw, $executeRaw, and Prisma.sql template tag

When to Use

Running SQL queries that Prisma Client cannot express (CTEs, window functions, complex joins)
Performing bulk operations more efficiently than Prisma's generated queries
Calling database-specific features (full-text search, advisory locks, lateral joins)
Executing data migrations or one-off scripts

Instructions

Read query — use
```
$queryRaw
```
with a tagged template literal. Always parameterize inputs:

const users = await prisma.$queryRaw<User[]>`
  SELECT id, email, name FROM "User" WHERE role = ${role}
`;

The template tag automatically parameterizes

${role}

as a prepared statement parameter — never string-interpolated.

Write query — use
```
$executeRaw
```
for INSERT, UPDATE, DELETE. Returns the affected row count:

const count = await prisma.$executeRaw`
  UPDATE "Post" SET "viewCount" = "viewCount" + 1 WHERE id = ${postId}
`;

Dynamic table or column names — use
```
Prisma.raw()
```
for identifiers that cannot be parameterized:

import { Prisma } from '@prisma/client';

const column = Prisma.raw(`"${columnName}"`);
const result = await prisma.$queryRaw`
  SELECT ${column} FROM "User" WHERE id = ${userId}
`;

Validate

columnName

against an allowlist before using

Prisma.raw()

to prevent SQL injection.

Build dynamic queries with
```
Prisma.sql
```
and
```
Prisma.join
```
:

const ids = [1, 2, 3];
const result = await prisma.$queryRaw`
  SELECT * FROM "User" WHERE id IN (${Prisma.join(ids)})
`;

Compose query fragments with
```
Prisma.sql
```
:

const where = searchTerm ? Prisma.sql`WHERE name ILIKE ${'%' + searchTerm + '%'}` : Prisma.empty;

const users = await prisma.$queryRaw`SELECT * FROM "User" ${where}`;

Type the result —
```
$queryRaw
```
returns
```
unknown[]
```
by default. Pass a generic type or validate with Zod:

import { z } from 'zod';

const UserRow = z.object({ id: z.string(), email: z.string() });
const raw = await prisma.$queryRaw`SELECT id, email FROM "User"`;
const users = z.array(UserRow).parse(raw);

Use raw queries inside transactions:

await prisma.$transaction(async (tx) => {
  await tx.$executeRaw`LOCK TABLE "Inventory" IN EXCLUSIVE MODE`;
  const [item] = await tx.$queryRaw<Inventory[]>`
    SELECT * FROM "Inventory" WHERE sku = ${sku} FOR UPDATE
  `;
  await tx.$executeRaw`
    UPDATE "Inventory" SET quantity = quantity - ${qty} WHERE sku = ${sku}
  `;
});

Details

Raw queries bypass Prisma's query engine and type generation. The SQL runs directly against the database connection, but parameter binding still uses prepared statements for safety.

Table and column naming: Prisma maps model names to table names via

@@map

and field names to column names via

@map

. In raw SQL, you must use the actual database names, not the Prisma model names. Check with

prisma migrate diff --from-empty --to-schema-datamodel prisma/schema.prisma --script

to see the real table names.

Return type caveats:

```
$queryRaw
```
returns rows with exact database column names (not Prisma field names)
```
BigInt
```
columns return JavaScript
```
BigInt
```
, which is not JSON-serializable — convert to
```
Number
```
or
```
String
```
before sending to clients
```
Decimal
```
columns return
```
Prisma.Decimal
```
objects, not
```
number
```
```
DateTime
```
columns return
```
Date
```
objects

$queryRawUnsafe

/
$executeRawUnsafe
: Accept a plain string instead of a tagged template. These do NOT parameterize — you are responsible for preventing SQL injection. Avoid unless you are building a query builder that handles parameterization itself.

Performance advantages of raw SQL:

CTEs and window functions are impossible with Prisma Client but often the most efficient solution
```
INSERT ... ON CONFLICT
```
(upsert) on multiple rows is significantly faster than looping
```
prisma.model.upsert()
```
Bulk updates with CASE expressions are faster than individual
```
update
```
calls

Source

https://prisma.io/docs/orm/prisma-client/queries/raw-database-access

Process

Read the instructions and examples in this document.
Apply the patterns to your implementation, adapting to your specific context.
Verify your implementation against the details and edge cases listed above.

Harness Integration

Type: knowledge — this skill is a reference document, not a procedural workflow.
No tools or state — consumed as context by other skills and agents.

Success Criteria

The patterns described in this document are applied correctly in the implementation.
Edge cases and anti-patterns listed in this document are avoided.