TypedSQL: Type-Safe Raw SQL Queries in Prisma ORM

TypedSQL is a Prisma ORM feature that turns raw SQL queries written in .sql files into fully typed TypeScript functions. You write plain SQL, run prisma generate --sql, and Prisma ORM generates a function with typed arguments and a typed result that you execute with $queryRawTyped. This guide walks through the complete workflow on Prisma ORM 7.

Updated (July 2026): This post originally announced TypedSQL in Prisma ORM v5.19.0. It is now maintained as an evergreen guide. TypedSQL remains a Preview feature in Prisma ORM 7. Every command and code block below was run end-to-end against prisma@7.8 and @prisma/client@7.8, using the prisma-client generator and a local Prisma Postgres database started with npx prisma dev.

How TypedSQL works

TypedSQL is a four-step workflow: enable the Preview feature, write a query in a .sql file, generate the typed function, and call it through Prisma Client.

1. Enable the typedSql Preview feature

Add typedSql to the previewFeatures of your generator block. On Prisma ORM 7, the default generator is prisma-client, which emits the client into your own source tree instead of node_modules:

// prisma/schema.prisma

generator client {
  provider        = "prisma-client"
  output          = "../src/generated/prisma"
  previewFeatures = ["typedSql"]
}

datasource db {
  provider = "postgresql"
}

model User {
  id             String          @id @default(uuid())
  email          String          @unique
  trackingEvents TrackingEvent[]
}

model TrackingEvent {
  id        String   @id @default(uuid())
  timestamp DateTime @default(now())
  userId    String
  type      String
  variant   String
  version   Int
  user      User     @relation(fields: [userId], references: [id])
}

On Prisma ORM 7, the connection URL no longer lives in the datasource block. It belongs in prisma.config.ts, and you load environment variables yourself, for example with dotenv:

// prisma.config.ts

import 'dotenv/config'
import { defineConfig } from 'prisma/config'

export default defineConfig({
  schema: 'prisma/schema.prisma',
  datasource: {
    url: process.env.DATABASE_URL!,
  },
})

Sync the schema to your database before generating:

npx prisma db push

2. Write a SQL query in the prisma/sql directory

Put each query into its own .sql file inside prisma/sql. The file name becomes the name of the generated function:

-- prisma/sql/conversionByVariant.sql

SELECT "variant", CAST("checked_out" AS FLOAT) / CAST("opened" AS FLOAT) AS "conversion"
FROM (
  SELECT
    "variant",
    COUNT(*) FILTER (WHERE "type" = 'PageOpened') AS "opened",
    COUNT(*) FILTER (WHERE "type" = 'CheckedOut') AS "checked_out"
  FROM "TrackingEvent"
  GROUP BY "variant"
) AS "counts"
ORDER BY "conversion" DESC

Queries can take arguments. On PostgreSQL you reference them as $1, $2, and so on (MySQL uses ?), and an optional @param comment names the argument and pins its type:

-- prisma/sql/conversionByVariantByVersion.sql

-- @param {Int} $1:version The tracking event version to filter by
SELECT "variant", CAST("checked_out" AS FLOAT) / CAST("opened" AS FLOAT) AS "conversion"
FROM (
  SELECT
    "variant",
    COUNT(*) FILTER (WHERE "type" = 'PageOpened') AS "opened",
    COUNT(*) FILTER (WHERE "type" = 'CheckedOut') AS "checked_out"
  FROM "TrackingEvent"
  WHERE "version" = $1
  GROUP BY "variant"
) AS "counts"
ORDER BY "conversion" DESC

3. Generate the typed query functions

Run prisma generate with the --sql flag. TypedSQL needs a live database connection at this point, because Prisma ORM asks the database to infer the types of each query:

npx prisma generate --sql

✔ Generated Prisma Client (7.8.0) to ./src/generated/prisma in 28ms

Two Prisma 7 workflow notes:

• prisma migrate dev and prisma db push no longer regenerate the client automatically, so run npx prisma generate --sql explicitly after schema or query changes.
• During development you can keep the functions in sync with npx prisma generate --sql --watch.

For each .sql file, Prisma ORM emits a module into the sql folder of your generator output. This is the result type it inferred for the query above (shown without its generated namespace wrapper):

// src/generated/prisma/sql/conversionByVariant.ts (generated)

export type Result = {
  variant: string
  conversion: number | null
}

4. Import the function and run it with $queryRawTyped

With the prisma-client generator, you import Prisma Client and the query functions from your generator output directory rather than from @prisma/client:

import 'dotenv/config'
import { PrismaPg } from '@prisma/adapter-pg'
import { PrismaClient } from './generated/prisma/client'
import {
  conversionByVariant,
  conversionByVariantByVersion,
} from './generated/prisma/sql'

const adapter = new PrismaPg({ connectionString: process.env.DATABASE_URL! })
const prisma = new PrismaClient({ adapter })

// `result` is fully typed: { variant: string; conversion: number | null }[]
const result = await prisma.$queryRawTyped(conversionByVariant())
console.log(result)

Running this against a seeded database prints:

[
  { variant: 'A', conversion: 0.6666666666666666 },
  { variant: 'B', conversion: 0.2 }
]

If the SQL query has arguments, you pass them to the query function, with full type checking:

// Only conversion results from TrackingEvent version 1
const v1 = await prisma.$queryRawTyped(conversionByVariantByVersion(1))
console.log(v1)

[
  { variant: 'A', conversion: 0.5 },
  { variant: 'B', conversion: 0.2 }
]

That is the whole workflow. Rename a column in the schema without updating the query, and prisma generate --sql fails; change the query's result shape, and TypeScript flags every call site that relied on the old type.

Why raw SQL needs type safety

Raw SQL remains the most flexible way to query a relational database, but writing it inside a TypeScript project has long meant giving up the guarantees the rest of your code enjoys:

• No auto-completion while writing the query.
• No type safety for query results.
• Manually written result types that drift out of date as the schema evolves.
• A mapping gap between the relational model (rows, foreign keys) and TypeScript (objects, nested references).

Prisma Client has always offered $queryRaw as an escape hatch for the queries its high-level API cannot express, or that need hand-tuning for performance:

const result = await prisma.$queryRaw`
SELECT "variant", CAST("checked_out" AS FLOAT) / CAST("opened" AS FLOAT) AS "conversion"
FROM (
  SELECT
    "variant",
    COUNT(*) FILTER (WHERE "type" = 'PageOpened') AS "opened",
    COUNT(*) FILTER (WHERE "type" = 'CheckedOut') AS "checked_out"
  FROM "TrackingEvent"
  GROUP BY "variant"
) AS "counts"
ORDER BY "conversion" DESC
`

The problem: this query returns unknown. To get typed results you have to write the result type by hand, and nothing keeps that type honest when the schema changes. TypedSQL closes that gap by generating the types from the database itself. It is inspired by projects like PgTyped and sqlx that are built on the same idea, and the details are covered in the raw queries documentation.

TypedSQL and the Prisma Client API

The Prisma Client API is designed for the common queries that make up the bulk of most applications: CRUD operations, pagination, filters, and nested reads and writes.

// Create a new user with a post
await prisma.user.create({
  data: {
    name: 'Alice',
    email: 'alice@prisma.io',
    posts: {
      create: { title: 'Hello World' },
    },
  },
})

TypedSQL covers the remainder: analytics-style aggregations, window functions, database-specific features, and queries you want to hand-optimize. Together they give a team both options without a trade-off in type safety. Developers who prefer the high-level API keep it, and developers who think in SQL get the same generated types and editor support.

Frequently asked questions

Summary

TypedSQL gives you raw SQL with the type safety of the Prisma Client API: write a query in prisma/sql, run npx prisma generate --sql against a live database, and call the generated function with $queryRawTyped for fully typed results. On Prisma ORM 7 it stays behind the typedSql Preview flag, works with the prisma-client generator's custom output directory, and fits any PostgreSQL or modern MySQL setup, including Prisma Postgres. The TypedSQL documentation covers the full reference, including argument annotations and database support.

Looking ahead: Prisma Next is a TypeScript-native rewrite of Prisma ORM, built for AI coding agents and currently in early access. It becomes Prisma 8 at general availability; until then, Prisma 7 stays the production choice. To try it, run npm create prisma@next or read the early access docs.