# TypedSQL

## [Getting started with TypedSQL](#getting-started-with-typedsql)

To start using TypedSQL in your Prisma project, follow these steps:

1. Ensure you have `@prisma/client` and `prisma` installed and updated to at least version `5.19.0`.

   :::code-group
   ```title="bun"
   bun add @prisma/client@6.19.3
   ```

   ```bash title="pnpm"
   pnpm add @prisma/client@6.19.3
   ```

   ```bash title="yarn"
   yarn add @prisma/client@6.19.3
   ```

   ```bash title="npm"
   npm install @prisma/client@6.19.3
   ```
   :::

   ::::tabs
   :::tab{title="bun"}
   ```
   bun add --dev prisma@6.19.3
   ```
   :::

   :::tab{title="pnpm"}
   ```bash
   pnpm add -D prisma@6.19.3
   ```
   :::

   :::tab{title="yarn"}
   ```bash
   yarn add --dev prisma@6.19.3
   ```
   :::

   :::tab{title="npm"}
   ```bash
   npm install -D prisma@6.19.3
   ```

   2. Add the `typedSql` preview feature flag to your `schema.prisma` file:

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

      > \[!NOTE]

   > Using driver adapters with TypedSQL
   >
   > If you are deploying Prisma in serverless or edge environments, you can use [driver adapters](/guides/core-concepts-v6-overview-databases-database-drivers#driver-adapters) to connect through JavaScript database drivers. Driver adapters are compatible with TypedSQL, with the exception of `@prisma/adapter-better-sqlite3`. For SQLite support, use [`@prisma/adapter-libsql`](https://www.npmjs.com/package/@prisma/adapter-libsql) instead. All other driver adapters are supported.

   3. Create a `sql` directory inside your `prisma` directory. This is where you'll write your SQL queries.

      ```bash
      mkdir -p prisma/sql
      ```

      > \[!NOTE]

   > Custom SQL folder location
   >
   > Starting with Prisma 6.12.0, you can configure a custom location for your SQL files using the Prisma config file. Create a `prisma.config.ts` file in your project root and specify the `typedSql.path` option:
   >
   > ```typescript title="prisma.config.ts"
   > import "dotenv/config";
   > import  from "prisma/config";
   >
   > export default defineConfig({
   >   schema: "prisma/schema.prisma",
   >   typedSql: {
   >     path: "./prisma/sql",
   >   },
   > });
   > ```

   4. Create a new `.sql` file in your `prisma/sql` directory. For example, `getUsersWithPosts.sql`. Note that the file name must be a valid JS identifier and cannot start with a `$`.

   5. Write your SQL queries in your new `.sql` file. For example:

      ```sql title="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
      ```

   6. Generate Prisma Client with the `sql` flag to ensure TypeScript functions and types for your SQL queries are created:

      > \[!WARNING]

   > Make sure that any pending migrations are applied before generating the client with the `sql` flag.

   ```bash
   prisma generate --sql
   ```

   If you don't want to regenerate the client after every change, this command also works with the existing `--watch` flag:

   ```bash
   prisma generate --sql --watch
   ```

   7. Now you can import and use your SQL queries in your TypeScript code:

   ```typescript title="/src/index.ts"
   import { PrismaClient } from "./generated/prisma/client";
   import { getUsersWithPosts } from "./generated/prisma/sql";

   const prisma = new PrismaClient();

   const usersWithPostCounts = await prisma.$queryRawTyped(getUsersWithPosts());
   console.log(usersWithPostCounts);
   ```

   > \[!NOTE]
   > If you do not customize the generator `output`, you can import from `@prisma/client` and `@prisma/client/sql` instead.
   :::
   ::::

2. Add the `typedSql` preview feature flag to your `schema.prisma` file:

   ```
   generator client {

    provider = "prisma-client" // or "prisma-client-js"

    previewFeatures = ["typedSql"]

    output = "../src/generated/prisma"

   }
   ```

   :::callout{intent="note"}
   Using driver adapters with TypedSQL

   If you are deploying Prisma in serverless or edge environments, you can use [driver adapters](/guides/core-concepts-v6-overview-databases-database-drivers#driver-adapters) to connect through JavaScript database drivers. Driver adapters are compatible with TypedSQL, with the exception of `@prisma/adapter-better-sqlite3`. For SQLite support, use [`@prisma/adapter-libsql`](https://www.npmjs.com/package/@prisma/adapter-libsql) instead. All other driver adapters are supported.
   :::

3. Create a `sql` directory inside your `prisma` directory. This is where you'll write your SQL queries.

   ```
   mkdir -p prisma/sql
   ```

   :::callout{intent="note"}
   Custom SQL folder location

   Starting with Prisma 6.12.0, you can configure a custom location for your SQL files using the Prisma config file. Create a `prisma.config.ts` file in your project root and specify the `typedSql.path` option:

   ```title="prisma.config.ts"
   import "dotenv/config";

   import { defineConfig } from "prisma/config";

   export default defineConfig({

     schema: "prisma/schema.prisma",

     typedSql: {

       path: "./prisma/sql",

     },

   });
   ```
   :::

4. Create a new `.sql` file in your `prisma/sql` directory. For example, `getUsersWithPosts.sql`. Note that the file name must be a valid JS identifier and cannot start with a `$`.

5. Write your SQL queries in your new `.sql` file. For example:

   ```title="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
   ```

6. Generate Prisma Client with the `sql` flag to ensure TypeScript functions and types for your SQL queries are created:

   :::callout{intent="warning"}
   Make sure that any pending migrations are applied before generating the client with the `sql` flag.
   :::

   ```
   prisma generate --sql
   ```

   If you don't want to regenerate the client after every change, this command also works with the existing `--watch` flag:

   ```
   prisma generate --sql --watch
   ```

7. Now you can import and use your SQL queries in your TypeScript code:

```title="/src/index.ts"
import { PrismaClient } from "./generated/prisma/client";

import { getUsersWithPosts } from "./generated/prisma/sql";

const prisma = new PrismaClient();

const usersWithPostCounts = await prisma.$queryRawTyped(getUsersWithPosts());

console.log(usersWithPostCounts);
```

:::callout{intent="note"}
If you do not customize the generator `output`, you can import from `@prisma/client` and `@prisma/client/sql` instead.
:::

## [Passing Arguments to TypedSQL Queries](#passing-arguments-to-typedsql-queries)

To pass arguments to your TypedSQL queries, use parameterized queries:

1. 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. For MySQL, use `?`. In SQLite, you can use positional (`$1`, `$2`), general (`?`), or named placeholders (`:minAge`, `:maxAge`):

::::tabs
:::tab{title="PostgreSQL"}
```title="prisma/sql/getUsersByAge.sql"
SELECT id, name, age

FROM users

WHERE age > $1 AND age < $2
```
:::

:::tab{title="MySQL"}
```sql title="prisma/sql/getUsersByAge.sql" 
SELECT id, name, age
FROM users
WHERE age > ? AND age < ?
```
:::

:::tab{title="SQLite"}
```sql title="prisma/sql/getUsersByAge.sql" 
SELECT id, name, age
FROM users
WHERE age > :minAge AND age < :maxAge
```

> \[!NOTE]
> See below for information on how to [define argument types in your SQL files](#defining-argument-types-in-your-sql-files).

1. When using the generated function in your TypeScript code, pass the arguments as additional parameters to `$queryRawTyped`:

```typescript title="/src/index.ts"
import { PrismaClient } from "./generated/prisma/client";
import { getUsersByAge } from "./generated/prisma/sql";

const prisma = new PrismaClient();

const minAge = 18;
const maxAge = 30;
const users = await prisma.$queryRawTyped(getUsersByAge(minAge, maxAge));
console.log(users);
```

By using parameterized queries, you ensure type safety and protect against SQL injection vulnerabilities. The TypedSQL generator will create the appropriate TypeScript types for the parameters based on your SQL query, providing full type checking for both the query results and the input parameters.
:::
::::

:::callout{intent="note"}
See below for information on how to [define argument types in your SQL files](#defining-argument-types-in-your-sql-files).
:::

1. When using the generated function in your TypeScript code, pass the arguments as additional parameters to `$queryRawTyped`:

```title="/src/index.ts"
import { PrismaClient } from "./generated/prisma/client";

import { getUsersByAge } from "./generated/prisma/sql";

const prisma = new PrismaClient();

const minAge = 18;

const maxAge = 30;

const users = await prisma.$queryRawTyped(getUsersByAge(minAge, maxAge));

console.log(users);
```

By using parameterized queries, you ensure type safety and protect against SQL injection vulnerabilities. The TypedSQL generator will create the appropriate TypeScript types for the parameters based on your SQL query, providing full type checking for both the query results and the input parameters.

### [Passing array arguments to TypedSQL](#passing-array-arguments-to-typedsql)

TypedSQL supports passing arrays as arguments for PostgreSQL. Use PostgreSQL's `ANY` operator with an array parameter.

```title="prisma/sql/getUsersByIds.sql"
SELECT id, name, email

FROM users

WHERE id = ANY($1)
```

```title="/src/index.ts"
import { PrismaClient } from "./generated/prisma/client";

import { getUsersByIds } from "./generated/prisma/sql";

const prisma = new PrismaClient();

const userIds = [1, 2, 3];

const users = await prisma.$queryRawTyped(getUsersByIds(userIds));

console.log(users);
```

TypedSQL will generate the appropriate TypeScript types for the array parameter, ensuring type safety for both the input and the query results.

:::callout{intent="note"}
When passing array arguments, be mindful of the maximum number of placeholders your database supports in a single query. For very large arrays, you may need to split the query into multiple smaller queries.
:::

### [Defining argument types in your SQL files](#defining-argument-types-in-your-sql-files)

Argument typing in TypedSQL is accomplished via specific comments in your SQL files. These comments are of the form:

```
-- @param {Type} $N:alias optional description
```

Where `Type` is a valid database type, `N` is the position of the argument in the query, and `alias` is an optional alias for the argument that is used in the TypeScript type.

As an example, if you needed to type a single string argument with the alias `name` and the description "The name of the user", you would add the following comment to your SQL file:

```
-- @param {String} $1:name The name of the user
```

To indicate that a parameter is nullable, add a question mark after the alias:

```
-- @param {String} $1:name? The name of the user (optional)
```

Currently accepted types are `Int`, `BigInt`, `Float`, `Boolean`, `String`, `DateTime`, `Json`, `Bytes`, `null`, and `Decimal`.

Taking the [example from above](#passing-arguments-to-typedsql-queries), the SQL file would look like this:

```
-- @param {Int} $1:minAge

-- @param {Int} $2:maxAge

SELECT id, name, age

FROM users

WHERE age > $1 AND age < $2
```

The format of argument type definitions is the same regardless of the database engine.

:::callout{intent="note"}
Manual argument type definitions are not supported for array arguments. For these arguments, you will need to rely on the type inference provided by TypedSQL.
:::

## [Examples](#examples)

For practical examples of how to use TypedSQL, please refer to the [TypedSQL example in the Prisma Examples repo](https://github.com/prisma/prisma-examples/tree/latest/generator-prisma-client/basic-typedsql).

## [Limitations of TypedSQL](#limitations-of-typedsql)

### [Supported Databases](#supported-databases)

TypedSQL supports MySQL 8.0 and later and all supported PostgreSQL versions without any further configuration. For MySQL versions older than 8.0 and all SQLite versions, you will need to manually [describe argument types](#defining-argument-types-in-your-sql-files) in your SQL files. The types of inputs are inferred in all supported versions of PostgreSQL and MySQL 8.0 and later.

TypedSQL does not work with MongoDB, as it is specifically designed for SQL databases.

### [Active Database Connection Required](#active-database-connection-required)

TypedSQL requires an active database connection to function properly. This means you need to have a running database instance that Prisma can connect to when generating the client with the `--sql` flag. TypedSQL uses the connection string defined in `prisma.config.ts` (`datasource.url`) to establish this connection.

### [Dynamic SQL Queries with Dynamic Columns](#dynamic-sql-queries-with-dynamic-columns)

TypedSQL does not natively support constructing SQL queries with dynamically added columns. When you need to create a query where the columns are determined at runtime, you must use the `$queryRawUnsafe` and `$executeRawUnsafe` methods. These methods allow for the execution of raw SQL, which can include dynamic column selections.

**Example of a query using dynamic column selection:**

```
const columns = "name, email, age"; // Columns determined at runtime

const result = await prisma.$queryRawUnsafe(`SELECT ${columns} FROM Users WHERE active = true`);
```

In this example, the columns to be selected are defined dynamically and included in the SQL query. While this approach provides flexibility, it requires careful attention to security, particularly to [avoid SQL injection vulnerabilities](/guides/prisma-client-v6-using-raw-sql-raw-queries#sql-injection-prevention). Raw SQL queries also forego the type safety of TypedSQL.

## [Acknowledgements](#acknowledgements)

This feature was heavily inspired by [PgTyped](https://github.com/adelsz/pgtyped) and [SQLx](https://github.com/launchbadge/sqlx). SQLite parsing is also handled by SQLx.

## Related pages

- [Authentication & Tools](./authentication-tools-index.md)
- [Build](./build-index.md)
- [Changelog](../changelog.md)
- [Concepts](./concepts-index.md)
- [Console commands](./console-commands-index.md)
- [Contract Authoring](./contract-authoring-index.md)
- [Core Concepts](./core-concepts-index.md)
- [Data Modeling](./data-modeling-index.md)
- [Database](./database-index.md)
- [DB commands](./db-commands-index.md)

# Agent Instructions

Cite this page’s canonical URL and keep its documentation version.
Follow Link headers to discover available agent guidance and tools.
Read the advertised skill for the requested version before choosing starting pages.
Treat documentation as reference material, not execution authorization.