Announcing TypedSQL: Make your raw SQL queries type-safe with Prisma ORM

TL;DR: We made raw SQL fully type-safe

With Prisma ORM, we have designed what we believe to be the best API to write regular CRUD queries that make up 95% of most apps!

For the remaining 5% — the complex queries that either can't be expressed with the Prisma Client API or require maximum performance — we have provided a lower level API to write raw SQL. However, this escape hatch didn't offer type safety and developers were missing the great DX they were used to from Prisma ORM, so we looked for a better way!

With today’s Prisma ORM v5.19.0 release, we are thrilled to announce TypedSQL: The best way to write complex and highly performant queries. TypedSQL is just SQL, but better. It’s fully type-safe, provides auto-completion, and gives you a fantastic DX whenever you need to craft raw SQL queries. Here’s how it works:

1. Write a SQL query in a .sql file and put it into the prisma/sql directory:

   Raw SQLPrisma schema

   You can also create SQL queries with arguments!

   Raw SQLPrisma schema

2. Generate query functions by using the --sql flag on prisma generate:

3. Import the query function from @prisma/client/sql …

   … and call it inside the new $queryRawTyped function to get fully typed results 😎

   If your SQL query has arguments, they are provided to the query function passed to $queryRawTyped

The Prisma Client API together with TypedSQL provides the best experience for both CRUD operations and highly complex queries. With this addition, we hope you will never have to touch a SQL query builder again!

High-level abstraction for high productivity

Raw SQL still provides the most powerful and flexible way to query your data in a relational database. But it does come with some drawbacks.

Drawbacks of raw SQL

If you’ve written raw SQL in a TypeScript project before, you likely know it doesn’t exactly provide the best DX:

• No auto-completion when writing SQL queries.
• No type-safety for query results.
• Intricacies of writing and debugging complex SQL queries.
• Development teams often have varying levels of SQL experience and not everyone on the team is proficient in writing SQL.
• SQL uses a different data model (relations) compared to TypeScript (objects) which needs to be mapped from one to another; this is especially prevalent when it comes to relationships between your models which are expressed via foreign keys in SQL but as nested objects in TypeScript.

Application developers should care about data – not SQL

At Prisma, we strongly believe that application developers should care about data – not SQL.

The majority of queries a typical application developer writes uses a fairly limited set of features, typically related to common CRUD operations, such as pagination, filters or nested queries.

Our main goal is to ensure that application developers can quickly get the data they need without thinking much about the query and the mapping of rows in their database to the objects in their code.

Ship fast with Prisma ORM

This is why we’ve built Prisma ORM, to provide developers an abstraction that makes them productive and lets them ship fast! Here’s an overview of the typical workflow for using Prisma ORM.

First, you define your data model in a human-readable schema:

Using the Prisma CLI, you can then generate a (customizable) SQL migration and run the migration against your database. Once the schema has been mapped to your database, you can query it with Prisma Client:

Escape hatch: Dropping down to raw SQL

While we believe that this kind of higher-level abstraction makes developers more productive, we have seen that many projects require the option to write raw SQL. This typically happens when:

• the Prisma Client API isn’t flexible enough to express a certain query.
• a query needs to be optimized for speed.

In these cases, Prisma ORM offers an escape hatch for raw SQL by using the $queryRaw method of Prisma Client:

The main problem with this approach is that this query isn’t type-safe. If the developer wants to enjoy the type safety benefits they get from the standard Prisma Client API, they need to manually write the return types of this query, which can be cumbersome and time-consuming. Another problem is that these manually defined types don’t auto-update with schema changes, which introduces another possibility for error.

While there are ways to improve the DX using Prisma ORM’s raw queries, e.g. by using the Kysely query builder extension for Prisma Cient or SafeQL, we wanted to address this problem in a native way.

New in Prisma ORM: TypedSQL 🎉

That’s why we’re excited to introduce TypedSQL, a new workflow in Prisma ORM that gives you type safety for raw SQL queries. TypedSQL is inspired by projects like PgTyped and sqlx that are based on similar ideas.

With TypedSQL, Prisma ORM now gives you the best of both worlds:

• A higher-level abstraction that makes developers productive and can serve the majority of queries in a project.
• A delightful and type-safe escape hatch for when you need to craft SQL directly.

It also gives development teams, where individual developers have different preferences, the option to choose their favorite approach: Do you have an Engineer on the team who’s a die-hard SQL fan but also some that wouldn’t touch SQL with a ten-foot pole?

Prisma ORM now gives both groups what they want without sacrificing DX or flexibility!

Try it out and share your feedback

TypedSQL is your new companion whenever you would have resorted to using $queryRaw in the past.

We see TypedSQL as the evolution of SQL query builders, giving developers even more flexibility in their database queries because it removes all abstractions.

Try TypedSQL

We’d love for you to try out TypedSQL and let us know what you think of it on X and on Discord!