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

[innocces]

The original blog info

subject	content
title	Announcing TypedSQL： Make your raw SQL queries type-safe with Prisma ORM
url	blog url
author	Nikolas Burk

[innocces]

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

Nikolas Burk@nikolasburk

With today’s v5.19.0 release, Prisma ORM introduces a new way to write raw SQL queries in a type-safe way! You now get the best of both worlds with Prisma ORM: A convenient high-level abstraction for the majority of queries and a flexible, type-safe escape hatch for raw SQL.

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:

Create new user with postQuery users with posts

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!

[innocces]

宣布 TypedSQL：使用 Prisma ORM 使原始 SQL 查询类型安全

尼古拉斯·伯克@nikolasburk

在今天的 v5.19.0 版本中，Prisma ORM 引入了一种以类型安全的方式编写原始 SQL 查询的新方法！现在，您可以使用 Prisma ORM 获得两全其美的好处：为大多数查询提供方便的高级抽象和为原始 SQL 提供灵活、类型安全的逃生口。

TL;DR: 我们使原始 SQL 完全类型安全

借助 Prisma ORM，我们设计了我们认为最好的 API 来编写常规 CRUD 查询，这些查询构成了大多数应用程序的 95%！

对于其余 5%（无法使用 Prisma Client API 表达或需要最高性能的复杂查询），我们提供了较低级别的 API 来编写原始 SQL。然而，这个逃生舱口不提供类型安全，开发人员错过了他们习惯的 Prisma ORM 的出色 DX，因此我们寻找更好的方法！

随着今天的 Prisma ORM v5.19.0 版本的发布，我们很高兴地宣布 TypedSQL：编写复杂且高性能查询的最佳方式。 TypedSQL 只是 SQL，但更好。 它完全类型安全，提供自动完成功能，并在您需要制作原始数据时为您提供出色的 DX SQL 查询。它的工作原理如下：

1. 在 .sql 文件中编写 SQL 查询并将其放入 prisma/sql 目录中：

   原始 SQLPrisma 架构

   您还可以使用参数创建 SQL 查询！

   原始 SQLPrisma 架构

2. 使用 prismagenerate 上的 --sql 标志生成查询函数：

3.从@prisma/client/sql导入查询函数...

...并在新的 $queryRawTyped 函数中调用它以获得完全类型化的结果😎

如果您的 SQL 查询有参数，它们将提供给传递给“$queryRawTyped”的查询函数

Prisma Client API 与 TypedSQL 一起为 CRUD 操作和高度复杂的查询提供最佳体验。有了这个补充，我们希望您再也不用接触 SQL 查询生成器了！

高级抽象以实现高生产力

原始 SQL 仍然提供了最强大、最灵活的方式来查询关系数据库中的数据。但它确实有一些缺点。

原始 SQL 的缺点

如果您之前在 TypeScript 项目中编写过原始 SQL，您可能知道它并不能完全提供最好的 DX：

• 编写 SQL 查询时没有自动完成功能。
• 查询结果没有类型安全。
• 编写和调试复杂 SQL 查询的复杂性。
• 开发团队通常拥有不同程度的 SQL 经验，并且并非团队中的每个人都精通 SQL 编写。
• 与需要从一个对象映射到另一个对象的 TypeScript（对象）相比，SQL 使用不同的数据模型（关系）；当涉及到模型之间的关系时，这种情况尤其普遍，这些模型在 SQL 中通过“外键”表示，但在 TypeScript 中作为“嵌套对象”表示。

应用程序开发人员应该关心数据 – 而不是 SQL

在 Prisma，我们坚信应用程序开发人员应该关心数据，而不是 SQL。

典型应用程序开发人员编写的大多数查询都使用相当有限的功能集，通常与常见的 CRUD 操作相关，例如分页、过滤器或嵌套查询。

我们的主要目标是确保应用程序开发人员能够快速获取所需的数据，而无需过多考虑查询以及数据库中的行到代码中的对象的映射。

使用 Prisma ORM 快速发货

这就是我们构建 Prisma ORM 的原因，为开发人员提供一个抽象，使他们高效并快速交付！以下是使用 Prisma ORM 的典型工作流程的概述。

首先，您以人类可读的模式定义数据模型：

然后，您可以使用 Prisma CLI 生成（可自定义）SQL 迁移并针对数据库运行迁移。将架构映射到数据库后，您可以使用 Prisma 客户端对其进行查询：

使用 postQuery 创建新用户 包含帖子的用户

逃逸舱口：下降到原始 SQL

虽然我们相信这种更高级别的抽象可以提高开发人员的工作效率，但我们已经看到许多项目需要编写原始 SQL 的选项。这通常发生在以下情况：

• Prisma 客户端 API 不够灵活，无法表达特定查询。
• 需要优化查询速度。

在这些情况下，Prisma ORM 通过使用 Prisma Client 的“$queryRaw”方法为原始 SQL 提供了逃生口：

这种方法的主要问题是该查询不是类型安全的。如果开发人员想要享受从标准 Prisma 客户端 API 中获得的类型安全优势，他们需要手动编写此查询的返回类型，这可能既麻烦又耗时。另一个问题是这些手动定义的类型不会随着架构更改而自动更新，这引入了另一种出错的可能性。

虽然有一些方法可以使用 Prisma ORM 的原始查询来改进 DX，例如通过使用 Prisma Cient 的 Kysely 查询构建器扩展 或 SafeQL ，我们想以原生方式解决这个问题。

Prisma ORM 中的新功能：TypedSQL 🎉

这就是为什么我们很高兴推出 TypedSQL，这是 Prisma ORM 中的一个新工作流程，可为您提供类型安全性用于原始 SQL 查询。 TypedSQL 的灵感来自于 PgTyped 和 sqlx 等基于类似想法的项目。

借助 TypedSQL，Prisma ORM 现在为您提供两全其美的优势：

• 更高级别的抽象，可以提高开发人员的工作效率，并可以满足项目中的大多数查询。
• 当您需要直接编写 SQL 时，这是一个令人愉快且类型安全的逃生口。

它还为开发团队（其中各个开发人员有不同的偏好）提供了选择他们最喜欢的方法的选项：团队中是否有一位工程师是铁杆 SQL 粉丝，但也有一些不会用十英尺接触 SQL极？

Prisma ORM 现在可以为两个群体提供他们想要的东西，而无需牺牲 DX 或灵活性！

尝试一下并分享您的反馈

每当您过去使用“$queryRaw”时，TypedSQL 都是您的新伴侣。

我们将 TypedSQL 视为 SQL 查询构建器的演变，它为开发人员的数据库查询提供了更大的灵活性，因为它消除了所有抽象。

尝试类型化 SQL

我们希望您尝试 TypedSQL，并在 X 和 [Discord](https://pris.ly/ 上告诉我们您对它的看法不和谐）！