# TypedSQL: Type-Safe Raw SQL Queries in Prisma ORM

URL: https://www.prisma.io/blog/announcing-typedsql-make-your-raw-sql-queries-type-safe-with-prisma-orm
Published: 2024-08-27
Updated: 2026-07-09
Authors: Nikolas Burk
Tags: orm, announcement
Description: TypedSQL adds type-safe raw SQL to Prisma ORM: write queries in .sql files, generate typed functions, and run them with $queryRawTyped. Verified on Prisma 7.

[TypedSQL](https://www.prisma.io/docs/orm/v7/prisma-client/using-raw-sql/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](https://github.com/prisma/orm/releases/tag/5.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](https://www.prisma.io/docs/postgres) database started with `npx prisma dev`.

## How TypedSQL works [#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 [#1-enable-the-typedsql-preview-feature]

Add `typedSql` to the `previewFeatures` of your [generator](https://www.prisma.io/docs/orm/v7/prisma-schema/overview/generators) 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
// 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`](https://www.prisma.io/docs/orm/v7/reference/prisma-config-reference), and you load environment variables yourself, for example with `dotenv`:

```typescript
// 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:

```bash
npx prisma db push
```

### 2. Write a SQL query in the `prisma/sql` directory [#2-write-a-sql-query-in-the-prismasql-directory]

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

```sql
-- 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:

```sql
-- 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 [#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:

```bash
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):

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

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

### 4. Import the function and run it with `$queryRawTyped` [#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`:

```typescript
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:

```typescript
// 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.

[Watch the video on YouTube](https://www.youtube.com/watch?v=ZwYcCti6CEs)

## Why raw SQL needs type safety [#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:

```typescript
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](https://www.prisma.io/docs/orm/v7/prisma-client/using-raw-sql/raw-queries).

## TypedSQL and the Prisma Client API [#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.

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

[Post on X](https://x.com/i/status/1815664017781735475)

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 [#frequently-asked-questions]

### Is TypedSQL still a Preview feature?

Yes. As of Prisma ORM 7.8, TypedSQL requires `previewFeatures = ["typedSql"]` in the generator block and the `--sql` flag on `prisma generate`. It has been available in Preview since Prisma ORM 5.19.0 (August 2024). Preview status means the API may still change before general availability, so pin your Prisma version and read release notes when upgrading.

### Which databases does TypedSQL support?

TypedSQL infers types automatically on PostgreSQL (all supported versions) and MySQL 8.0 and later. On MySQL versions older than 8.0 and on SQLite, you must annotate argument types manually with `@param` comments. MongoDB is not supported. Generation also requires an active database connection, because the database performs the type inference.

### Can TypedSQL run dynamically constructed queries?

No. TypedSQL types each query at generate time, so the SQL text must be static. Arguments are supported through typed placeholders (`$1` on PostgreSQL, `?` on MySQL), but dynamic column lists or table names are not. For queries built at runtime, use `$queryRaw` with tagged templates, which still protects against SQL injection through parameterization.

## Summary [#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](https://www.prisma.io/docs/postgres). The [TypedSQL documentation](https://www.prisma.io/docs/orm/v7/prisma-client/using-raw-sql/typedsql) covers the full reference, including argument annotations and database support.

Looking ahead: [Prisma 8](https://www.prisma.io/docs/orm) is a TypeScript-native rewrite of Prisma ORM, built for AI coding agents, and it is now the current release. Prisma 7 remains fully supported; its docs live at [prisma.io/docs/orm/v7](https://www.prisma.io/docs/orm/v7). To start with Prisma 8, run `npm create prisma@latest` or read the [Prisma 8 docs](https://www.prisma.io/docs/getting-started).