Skip to main content

Write your own SQL

While the Prisma Client API aims to make all your database queries intuitive, type-safe, and convenient, there may still be situations where raw SQL is the best tool for the job.

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 still benefiting from Prisma Client's excellent user experience. 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​

info

TypedSQL is available in Prisma ORM 5.19.0 and later. For raw database access in previous versions, see our raw queries documentation.

What is TypedSQL?​

TypedSQL is a new feature of Prisma ORM that allows you to write your queries in .sql files while still enjoying the great developer experience of Prisma Client. You can write the code you're comfortable with and benefit from 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

By using TypedSQL, you can write efficient, type-safe database queries without sacrificing the power and flexibility of raw SQL. This feature allows you to seamlessly integrate custom SQL queries into your Prisma-powered applications, ensuring type safety and improving developer productivity.

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:

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:

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.