Raw SQL

Although the Query API covers most use cases and the Query Builder API provides a type-safe escape hatch for complex queries, sometimes the most direct approach is to write raw SQL. ZenStack provides four methods on the ORM client for this purpose.

Access Control

Raw SQL queries bypass ZenStack's access control enforcement. If you have the policy plugin installed, raw SQL methods are rejected by default. You must opt in with the dangerouslyAllowRawSql option to use them.

$queryRaw

Executes a raw SQL query and returns the result rows. Uses a tagged template literal for safe parameterization — values are automatically escaped to prevent SQL injection.

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

$queryRawUnsafe

Same as $queryRaw but accepts a plain string instead of a template literal.

SQL Injection Risks

This method is susceptible to SQL injection if you interpolate unsanitized user input directly into the query string.

// query all users
const users = await db.$queryRawUnsafe<{ id: number; email: string }[]>(
  'SELECT id, email FROM "User"'
);

// query users of a specific role, use parameterized query to avoid SQL injection (PostgreSQL syntax shown here)
const filteredUsers = await db.$queryRawUnsafe<{ id: number; email: string }[]>(
  'SELECT id, email FROM "User" WHERE role = $1', role
);

$executeRaw

Executes a raw SQL statement (e.g. UPDATE, DELETE, INSERT) and returns the number of affected rows. Uses a tagged template literal for safe parameterization.

const count = await db.$executeRaw`
  UPDATE "User" SET name = ${newName} WHERE id = ${userId}
`;
console.log(`${count} row(s) affected`);

$executeRawUnsafe

Same as $executeRaw but accepts a plain string. The caller is responsible for SQL injection safety.

SQL Injection Risks

This method is susceptible to SQL injection if you interpolate unsanitized user input directly into the query string.

// use parameterized query to avoid SQL injection (PostgreSQL syntax shown here)
const count = await db.$executeRawUnsafe(
  'UPDATE "User" SET name = $1 WHERE id = $2',
  newName,
  userId
);
console.log(`${count} row(s) affected`);