Write your own SQL

The Prisma Client API covers most queries, but some situations call for raw SQL.

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 lets you write 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 complex SQL queries using familiar syntax
2. Benefit from full IDE support and syntax highlighting for SQL
3. Import your SQL queries as fully typed functions in your TypeScript code
4. Maintain the flexibility of raw SQL with the safety of Prisma's type system

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
• Leveraging 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

While not as ergonomic as TypedSQL, raw queries are still supported and useful when TypedSQL queries are not possible 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.

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