Use advanced SQL capabilities
A query requires database-specific syntax or advanced SQL features unavailable in Prisma Client's model API.
Prisma · Const
TypedSQL
TypedSQL lets you write SQL in .sql files and use generated, fully typed functions from Prisma Client. It combines SQL flexibility with typed inputs and outputs.
- Since
- 5.19.0
How it works
TypedSQL lets you author SQL in .sql files and use generated TypeScript functions with typed inputs and outputs. Enable the preview feature, generate Prisma Client with the --sql flag against a reachable database, then execute the generated function with $queryRawTyped.
Syntax
prisma generate --sql
await prisma.$queryRawTyped(generatedQuery(...args))Parameters
| Name | Type | Description |
|---|---|---|
query arguments | inferred typed parameters | Arguments defined by placeholders in the SQL query; generated with inferred types. |
Returns
A generated, typed query result inferred from the SQL and database schema.
Key points
- Queries remain ordinary SQL while working both in SQL tooling and through Prisma Client.
- The documented workflow uses a sql directory under prisma for query files.
- Run generation with the SQL flag so Prisma creates typed functions and types for the query files.
- TypedSQL supports Prisma's other driver adapters in addition to the standard connection approach.
Common mistakes
- Client generation needs a running, reachable database because Prisma infers query types from the database.
- Choose SQL filenames that are valid JavaScript identifiers and do not begin with a dollar sign.
Example: Execute a generated TypedSQL query
// prisma/sql/getUsers.sql
SELECT id, email FROM User WHERE email = $1;
// src/main.ts
import { PrismaClient } from '@prisma/client';
import { getUsers } from './generated/prisma/sql';
const prisma = new PrismaClient();
const users = await prisma.$queryRawTyped(getUsers('ada@example.com'));
console.log(users);This example places SQL in a dedicated file, generates its typed function, and executes that function through Prisma Client.
When to use it
Keep SQL with TypeScript safety
A team wants SQL files with editor support while retaining compile-time input and result types.
Integrate SQL into Prisma
Existing SQL workflows need to run through Prisma Client and its surrounding ecosystem.
Interview questions
QWhat is TypedSQL and how is it executed?
TypedSQL stores SQL in .sql files, then Prisma generates TypeScript functions and types for those queries. The generated functions are executed through Prisma Client with $queryRawTyped.
Follow-up: Why does generation need a database connection? Prisma connects to the database to infer the input and output types of each SQL query.
QHow do you enable and generate TypedSQL queries?
Add typedSql to the generator's previewFeatures, place queries in the Prisma SQL directory, and run prisma generate --sql. The generated query function can then be imported into TypeScript.
Follow-up: What should be checked if the generated function is missing? Verify the preview flag, SQL location and filename, and that generation was run with --sql.
QWhat benefits does TypedSQL provide over ordinary raw SQL?
TypedSQL keeps SQL's expressive power while providing typed arguments and results in TypeScript. It also supports editor features such as SQL syntax highlighting, error checking, and autocompletion.
Follow-up: When is TypedSQL preferable to Prisma Client's model API? Use it when a query needs SQL-specific features or control that the higher-level Client API cannot express.
Related
Prisma Client$queryRawTypedprisma generate --sqltypedSqlPrisma SchemaPrisma Migrate