Prisma's new typed raw SQL feature

DatabaseSoftware Development

Typed Raw Glossary · In briefSQLSQL is a language for querying and managing data in a relational database and defining that database's structure.Read more⁠ is in the latest Prisma Glossary · In briefreleaseA release is an identifiable software version prepared to be made available to users. It brings together one or more checked changes.Read more⁠, and that feature lifts the robustness of our code another solid step.

What exactly was added? The ability to write typed raw SQL Glossary · In briefqueryA query is a targeted instruction to a system to retrieve, filter, combine or modify data.Read more⁠, and that makes Prisma not only more flexible but also a lot safer to use. More details are in the documentation.

What is Prisma?

Prisma itself is an Glossary · In briefORMAn ORM is software that maps objects in program code to tables and relationships in a relational database. ORM stands for Object-Relational Mapping.Read more⁠ (Object-Relational Mapping) tool that makes it easy for developers to work with Glossary · In briefdatabaseA database is a structured collection of data that software can store, retrieve and modify. A database management system controls access to that data.Read more⁠ through a Glossary · In brieftype safetyType safety is the extent to which a programming language prevents values from being used in ways incompatible with their types. Checks can identify such errors during coding, compilation or execution.Read more⁠ Glossary · In briefAPIAn API is a defined way for software to exchange data or call functions in other software without needing to know how that software works internally.Read more⁠. Fetching, adding, updating, or deleting data can all happen without you having to worry about the SQL under the hood. The built-in query builder ensures that both input and output are typed, so while you code you already see whether a query is correct and what kind of data you get back. That saves a lot of guessing.

Okay, and what is Typed Raw SQL?

You could always fall back on raw SQL in Prisma for cases where the query builder fell short, and that remains useful for complex queries or database-specific functions that Prisma does not support. But the big downside was that the input and output of those raw SQL queries were not typed, so you only found out while running your software whether everything was correct. Let a few days or weeks of programming pass and then you have to debug with untyped returns. That is no fun.

You get the full power of SQL, but with guaranteed type safety.

The new "Typed Raw SQL" feature lets you write raw SQL in a dedicated file. You work with named parameters and template literals for dynamic queries. When your Prisma client is generated (simply with the prisma generate command), that SQL is analysed and checked against your Prisma schema. That yields a clean typing for both the input and output of your query. Exactly what we were looking for: flexible like SQL, safe like Prisma.

Why is this useful?

This feature combines the best of both worlds. Why did our team cheer right away? Here is why:

Type Safety
You write a raw SQL query and you simply know that input and output are correct, which firmly swallows runtime errors.
Flexibility
For complex queries, or things outside the standard Prisma query builder, you simply reach for SQL without losing type safety.
Dynamic SQL
With template literals you write queries that can be fully adapted while the types still line up.
Maintainability
Those types also make everything more maintainable straight away. Your code refactors without fuss, because Prisma immediately sees where a change goes wrong.

Who is this relevant for?

With Typed Raw SQL you get the combination developers crave in Prisma: the raw flexibility of writing your own SQL together with the reliability of solid type checking under the hood. Working with your database becomes not only a lot more efficient, it is noticeably less error-prone and a big difference in confidence with complex data operations. And since we get hit with those spicy nested queries every day here, we can say this really is a valuable addition to Prisma.