TypedSQL is a powerful feature in Prisma that allows developers to write raw SQL queries while maintaining type safety and full integration with Prisma’s TypeScript client. This feature is especially useful for scenarios where complex SQL queries are necessary, but you still want to benefit from Prisma’s type safety, code generation, and developer experience (DX). In this blog, we'll walk through the steps to get started with TypedSQL, how to pass arguments to queries, define argument types, and some best practices.
Setting Up TypedSQL in Prisma
To start using TypedSQL in your Prisma project, follow these steps:
1. Install the Required Packages
Ensure that you have @prisma/client and prisma installed and updated to at least version 5.19.0. You can do this by running the following commands:
npm install @prisma/client@latest
npm install -D prisma@latest
2. Enable TypedSQL in Your Schema
Add the typedSql preview feature flag to your schema.prisma file:
generator client {
provider = "prisma-client-js"
previewFeatures = ["typedSql"]
}
3. Create a Directory for SQL Files
Inside your prisma directory, create a sql directory where you'll write your SQL queries:
mkdir -p prisma/sql
4. Write Your SQL Queries
Create a new .sql file in the prisma/sql directory. The file name must be a valid JavaScript identifier and cannot start with a $. For example, let's create a getUsersWithPosts.sql file:
-- prisma/sql/getUsersWithPosts.sql
SELECT u.id, u.name, COUNT(p.id) as "postCount"
FROM "User" u
LEFT JOIN "Post" p ON u.id = p."authorId"
GROUP BY u.id, u.name
5. Generate the Prisma Client
To generate TypeScript functions and types for your SQL queries, run the following command:
prisma generate --sql
If you want Prisma to regenerate the client automatically after every change, you can use the --watch flag:
prisma generate --sql --watch
6. Use TypedSQL Queries in Your Code
You can now import and use your SQL queries in your TypeScript code:
import { PrismaClient } from '@prisma/client'
import { getUsersWithPosts } from '@prisma/client/sql'
const prisma = new PrismaClient()
const usersWithPostCounts = await prisma.$queryRawTyped(getUsersWithPosts())
console.log(usersWithPostCounts)
Passing Arguments to TypedSQL Queries
To pass arguments to your TypedSQL queries, you can use parameterized queries. This allows you to write flexible and reusable SQL statements while maintaining type safety.
Example: Passing Parameters
In your SQL file, use placeholders for the parameters you want to pass. The syntax for placeholders depends on your database engine. For PostgreSQL, use the positional placeholders $1, $2, etc.:
-- prisma/sql/getUsersByAge.sql
SELECT id, name, age
FROM users
WHERE age > $1 AND age < $2
When using the generated function in your TypeScript code, pass the arguments as additional parameters to $queryRawTyped:
import { PrismaClient } from '@prisma/client'
import { getUsersByAge } from '@prisma/client/sql'
const prisma = new PrismaClient()
const minAge = 18
const maxAge = 30
const users = await prisma.$queryRawTyped(getUsersByAge(minAge, maxAge))
console.log(users)
Example: Passing Array Arguments
TypedSQL supports passing arrays as arguments for PostgreSQL. Use PostgreSQL's ANY operator with an array parameter:
-- prisma/sql/getUsersByIds.sql
SELECT id, name, email
FROM users
WHERE id = ANY($1)
In your TypeScript code:
import { PrismaClient } from '@prisma/client'
import { getUsersByIds } from '@prisma/client/sql'
const prisma = new PrismaClient()
const userIds = [1, 2, 3]
const users = await prisma.$queryRawTyped(getUsersByIds(userIds))
console.log(users)
Defining Argument Types in SQL Files
You can define the types of arguments directly in your SQL files using specific comments. This enhances type safety and ensures that the TypeScript client is fully aware of the types being passed.
Example: Typing Arguments
Add the following comments to define the types:
-- @param {Int} $1:minAge The minimum age of the user
-- @param {Int} $2:maxAge The maximum age of the user
SELECT id, name, age
FROM users
WHERE age > $1 AND age < $2
The format is consistent across all supported database engines and ensures that your TypeScript code accurately reflects the SQL query structure.
Pros and Cons of Using TypedSQL
Pros
Type Safety: TypedSQL provides strong type safety for raw SQL queries, reducing the risk of runtime errors and ensuring consistent data handling.
Integrated DX: By integrating with Prisma, TypedSQL allows you to write complex SQL queries while still benefiting from Prisma’s developer tools, like autocomplete and type inference.
Reusable Queries: TypedSQL encourages the reuse of SQL queries across your application, making it easier to maintain and update your codebase.
Protection Against SQL Injection: By using parameterized queries, TypedSQL helps protect your application against SQL injection attacks, which are a common security vulnerability.
Better Performance for Complex Queries: TypedSQL allows you to execute more complex and efficient SQL queries directly within Prisma, bypassing some of the limitations of Prisma’s query builder.
Cons
Limited Dynamic SQL Support: TypedSQL does not natively support constructing SQL queries with dynamically added columns. For such cases, you need to fall back to
$queryRawor$executeRaw, which means losing type safety.Increased Complexity: Using raw SQL, even with type safety, adds a layer of complexity to your codebase, especially if your team is more familiar with Prisma’s query builder syntax.
Learning Curve: Developers need to be comfortable with both SQL and Prisma’s TypeScript client to use TypedSQL effectively. This might require additional learning for some team members.
Dependency on Preview Features: TypedSQL is currently a preview feature, meaning it’s not yet fully stable and might undergo breaking changes in future releases.
Lack of Full ORM Benefits: When using TypedSQL, you miss out on some of the abstractions and conveniences that Prisma’s ORM provides, such as automatic relation handling and query optimization.
Conclusion
TypedSQL is a powerful tool in Prisma’s ecosystem that bridges the gap between raw SQL and type-safe query construction. It offers the flexibility of raw SQL with the benefits of Prisma’s TypeScript client, making it ideal for scenarios where you need fine-grained control over your database queries.
However, it’s important to weigh the pros and cons before adopting TypedSQL in your project. While it offers many advantages, particularly in terms of type safety and query performance, it also introduces complexity and requires a good understanding of SQL.
By following the steps outlined in this guide, you can start using TypedSQL in your Prisma projects, writing more complex queries while maintaining the benefits of Prisma’s type-safe client.
Top comments (0)