Write your own SQL

The Prisma Client API covers most queries, but there are situations where raw SQL is the right tool.

This can happen for various reasons, such as the need to optimize the performance of a specific query or because your data requirements can't be fully expressed by Prisma Client's query API.

In most cases, TypedSQL allows you to express your query in SQL while keeping typed inputs and outputs. However, since TypedSQL is statically typed, it may not handle certain scenarios, such as dynamically generated WHERE clauses. In these cases, you will need to use $queryRaw or $executeRaw, or their unsafe counterparts.

Writing type-safe queries with Prisma Client and TypedSQL

What is TypedSQL?

TypedSQL is a feature of Prisma ORM that lets you write your queries in .sql files and import them into your TypeScript code as functions with fully typed inputs and outputs.

With TypedSQL, you can:

1. Write SQL queries in .sql files, with your editor's SQL support and syntax highlighting
2. Import those queries as fully typed functions in your TypeScript code
3. Pass typed arguments and receive typed results

TypedSQL is particularly useful for:

• Complex reporting queries that are difficult to express using Prisma's query API
• Performance-critical operations that require fine-tuned SQL
• Using database-specific features not yet supported in Prisma's API

For a detailed guide on how to get started with TypedSQL, including setup instructions and usage examples, please refer to our TypedSQL documentation.

Raw queries

Prior to version 5.19.0, Prisma Client only supported raw SQL queries that were not type-safe and required manual mapping of the query result to the desired type.

While not as ergonomic as TypedSQL, these queries are still supported and are useful when TypedSQL queries are not possible either due to features not yet supported in TypedSQL or when the query is dynamically generated.

Alternative approaches to raw SQL queries in relational databases

Prisma ORM supports four methods to execute raw SQL queries in relational databases:

• $queryRaw
• $executeRaw
• $queryRawUnsafe
• $executeRawUnsafe

These commands are similar to using TypedSQL, but they are not type-safe and are written as strings in your code rather than in dedicated .sql files.

Alternative approaches to raw queries in document databases

For MongoDB, Prisma ORM supports three methods to execute raw queries:

• $runCommandRaw
• <model>.findRaw
• <model>.aggregateRaw

These methods allow you to execute raw MongoDB commands and queries, providing flexibility when you need to use MongoDB-specific features or optimizations.

$runCommandRaw is used to execute database commands, <model>.findRaw is used to find documents that match a filter, and <model>.aggregateRaw is used for aggregation operations. All three methods are available from Prisma version 3.9.0 and later.

Similar to raw queries in relational databases, these methods are not type-safe and require manual handling of the query results.