Preview feature feedback: typedSql

[jharrell]

TypedSQL is a new feature of Prisma ORM which lets you write your raw SQL queries in a fully type-safe manner.

• Documentation: https://www.prisma.io/docs/orm/prisma-client/using-raw-sql/typedsql
• Announcement blog post: https://www.prisma.io/blog/announcing-typedsql-make-your-raw-sql-queries-type-safe-with-prisma-orm

Please share your feedback about typedSql (released in v5.19.0 of Prisma ORM) in this discussion.

• If you encounter a bug, please open a bug report in this repository.
• If the feature is working well for you, please share so in a comment below or leave a 👍 on this discussion.
• Need help or have questions? Join the Prisma Discord community and ask away! We're here to support you.

GA planned for September 2025 - February 2026

[joonatanvanhala]

First of all, I love the feature! Raw SQL is a must for me, and this cleans up the code quite a bit.

I have a few questions, though.

Firstly, I did not see any mention of typedSQL with serverless in the documentation. I keep running into a "Runtime.ImportModuleError: Error: Cannot find module '.prisma/client/sql'" error in Lambda. Should the packaging be done differently somehow?

Secondly, JSON values are inferred as JsonValue in TypedSQL with Postgres. For example, if I create a query like SELECT id FROM users, typedSQL recognizes the id as a specific type. However, if I change it to SELECT json_build_object('id', id) as user_data FROM users, user_data is seen as JsonValue. Is it a possible addition for typedSQL to recognize the types within the JSON to generate more specific types?
EDIT: #25126

[sorenbs]

Is it a possible addition for typedSQL to recognize the types within the JSON to generate more specific types?

I think this is a great idea!

Could you open a feature request and link it here?

typedSQL with serverless

There should be no difference related to serverless. Is there anything about your setup that is out of the ordinary? Are you perhaps running prisma generate in a CI pipeline and need to add the --sql flag there?

[joonatanvanhala]

Thanks for the quick response, I'll have to look more into the serverless case but that could be the issue.

I created the feature request for the specific JSON types here: #25126

[Saadahmed05]

LGTM

[nenorrell]

This would be awesome!

[Gubiar]

Hello Prisma team,

I've encountered an issue while trying to use the new TypedSQL feature in a Next.js project with Prisma ORM. I've created a repository to demonstrate the problem: https://github.com/Gubiar/prisma-type-safe-sql

Issue Description:
When running npx prisma generate --sql, I receive the following error:

Error: Errors while reading sql files:
In prisma\sql\getPosts.sql:
Error: ERROR: prepared statement "s2" does not exist

Steps to Reproduce:

1. Clone the repository: git clone https://github.com/Gubiar/prisma-type-safe-sql
2. Install dependencies: npm install
3. Set up the database and environment variables as described in the README
4. Run npx prisma generate --sql

Additional Information:

• The SQL query works fine when executed directly in the database.
• The query also works when used with prisma.$queryRaw.
• This issue specifically occurs when trying to use Prisma's TypedSQL feature.

Expected Behavior:
The npx prisma generate --sql command should successfully generate the TypedSQL without any errors.

Actual Behavior:
The command fails with the "prepared statement "s2" does not exist" error.

Environment:

• Prisma version: "^5.19.0"
• Node.js version: v20.15.1
• Database: PostgreSQL 16
• OS: Windows 11

I've provided a minimal reproduction in the linked repository. Any insights or assistance in resolving this issue would be greatly appreciated. Thank you for your time and support!

Keep up the fantastic work! Your efforts are making a real difference in the developer community. 👏

[aaronforloop]

Adding a DIRECT_URL got around this for me.

https://neon.tech/docs/guides/prisma#using-a-direct-connection-to-the-database

[Gubiar]

Thanks a lot for the suggestion! 🙌 Adding the DIRECT_URL worked perfectly.

I update my repo with the fix

https://github.com/Gubiar/prisma-type-safe-sql

[PatricioNG]

Two questions here:

1. I've got multiple connections set up to different databases, in all my scripts I'm passing --schema xxx for segmenting out the proper connection and prisma client, how does this work with the /sql folder? Didn't see it in the docs the other day unless I missed any recent updates.

For example:

I only want a /sql folder that would be for raw queries in my OMS connection/client, is that possible at this stage?

2. What's the plan for SQL Server, is there a timeline for support? 👀 🙏

[sorenbs]

Hey Niko,

Do I understand correctly that you are generating three different Prisma clients based on your three different schemas: AX, BI and OMS?

I don't actually know how this works at the moment, but what I would like to see us support is three different prisma folders, each with their own schemas, migrations and sql sub folder. Something like this:

• prisma
• • AX
• • • sql
• • • migrations
• • • AX.prisma
• • BI
• • • sql
• • • migrations
• • • BI.prisma
• • OMS
• • • sql
• • • migrations
• • • OMS.prisma

And then in your scripts, you would pass --schema ./prisma/OMS, which would pick up the correct sql folder.

Would that work for you?

[PatricioNG]

Hi @sorenbs, yep that makes sense as a pattern to use :)!

Regarding the SQL Server question, do you know the plan for it?

[Katli95]

@sorenbs I too would very much appreciate some comment on the possibility to use this with MS SQL Server, could you give us any idea of when (hopefully not "if") we might expect to see that?

[briangottfried]

Is there a way to run

npx prisma generate --sql

without needing the database running? Currently, our CI/CD pipeline builds/deploys a docker image of our web service, and I would like to avoid having to start a postgres container and deploy a migration in CI to be able to package everything.

[0-vortex]

As an intermediate solution I've checked in only the /client/sql subfolder:

• Local development does a prisma generate with sql flag
• CI/CD does not (and does not require db connection)
• No runtime and platform specific stuff checked in!

I find this acceptable since raw sql is only a small subset that does not change very often.

.gitignore

/client/*
!/client/sql/

package.json

 "scripts": {
        // CI/CD command used
        "postinstall": "npx prisma generate",
        // Devs command used
        "prisma:generate": "env $(cat .env.local | xargs) npx prisma generate --sql",
    },

How do you run tests for code that relies on the typed sql? Thank you

[mathi123]

@0-vortex You would not be able to run full system tests that queries the db. But that point also goes for regular prisma functionality, regardless of typedSql feature. If you want to do that, you need to spin up a DB in CI/CD.

[aroman]

@0-vortex how do you handle this issue?

$ prisma generate

prisma/generated exists and is not empty but doesn't look like a generated Prisma Client. Please check your output path and remove the existing directory if you indeed want to generate the Prisma Client in that location.

[0-vortex]

@aroman different folders for the sql and prisma client IE src/persistence/client and src/persistence/sql

[hofits-hub]

@aroman different folders for the sql and prisma client IE src/persistence/client and src/persistence/sql

How do you configure it?

[r1tsuu]

How can I apply statement in WHERE conditionally?

For example:

SELECT *
FROM users u
WHERE
  u.role = $1

I want to apply u.role = $1 only if passed $1 isn't undefined. It seems like you can't do this now.

With NULL this trick in SQL can work:

SELECT *
FROM users u
WHERE
  ($1 IS NULL OR u.role = $1)

But it gives Error: ERROR: could not determine data type of parameter $1 which is kinda expected.

[aracheli-fae]

I don't know the type of your role field, but for strings you could do something like $1 = '' OR u.role = $1.
In TS you can then coerce the value to an empty string when the filter must be ignored.

[redsuperbat]

I am facing the same problem.

[mindblight]

That works for String type, but I'd like to do a similar thing for numbers or boolean. Not being able to pass null in makes this trick impossible for those types.

[luanbt21]

I’ve found a pattern when work with postgres, similar for BOOLEAN, INT, TIMESTAMP, etc

SELECT id 
FROM "Question"
WHERE ($1::TEXT IS NULL AND "knowledgeTypeId" IS NULL OR "knowledgeTypeId" = $1)
ORDER BY RANDOM()

[Dauta]

Hey all 👋

I'm running prisma 5.19.0 in a NextJS 14 app. The db query happens behind a trpc server. Seems like the first query runs fine, but any subsequent ones return the following error:

Invalid `prisma.$queryRawTyped()` invocation:

{
  parameters: {
    values: "[{\"prisma__type\":\"date\",\"prisma__value\":\"2023-11-01T04:00:00.000Z\"},{\"prisma__type\":\"date\",\"prisma__value\":\"2023-12-01T04:59:59.999Z\"},\"123123123\"]",
    __prismaRawParameters__: true
  },
+ query: String
}

Argument `query` is missing.

I definitely passed queries into all invocations of $queryRawTyped.
Big thanks for this feature!

[Dauta]

I should clarify that the same query reruns fine, even with different arguments. Any other query is failing with the above error. I have to restart the Next server for anything else to work, but it's always the same behavior.

[Ivanfvj]

I have the same exact issue, I ran a different script (a Typescript one using ts-node and importing the same db file) without trpc and it works fine. I also tried to modify the .sql queries to see if that's related but it appears not to be.

I have this prisma configuration.

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["typedSql", "postgresqlExtensions"]
}

datasource db {
  provider   = "postgresql"
  url        = env("DATABASE_URL")
  directUrl  = env("DATABASE_DIRECT_URL")
  extensions = [pgvector(map: "vector")]
}

And any typed .sql query fails the second time it runs. For example:

SELECT DISTINCT
    pi.model,
    c.name AS "categoryName",
    c.slug AS "categorySlug"
FROM 
    public."PromptItem" pi
JOIN 
    public."PromptCategories" pc ON pi.id = pc."promptId"
JOIN 
    public."Category" c ON pc."categoryId" = c.id
ORDER BY 
    pi.model asc, c.name;

This is how I instantiate a prisma client db

import createPrismaClient, {
  type PrismaClientType,
} from '../modules/prisma/createPrismaClient';
import { env } from '../env.mjs';

const globalForPrisma = globalThis as unknown as { prisma: PrismaClientType };

const prisma =
  globalForPrisma.prisma ||
  createPrismaClient({
    log: env.NODE_ENV === 'development' ? ['query', 'error', 'warn'] : ['error'],
  });

export default prisma;

if (env.NODE_ENV !== 'production') globalForPrisma.prisma = prisma;

[apolanc]

Hello @Dauta and @Ivanfvj!

Thanks for the feedback. Could you please create an issue and provide us with a reproduction here. That would greatly help us pin point the problem.

[nurul3101]

I have created a GitHub Issue with Reproduction here: #25331

[macrozone]

first of all, cool feature, I have severall feedbacks, so I would distribute them in multiple comments

We can comment the params. But can we also create a comment that appears as TSDoc? (e.g. what the query is doing)

[briangottfried]

I think you can just add -- @description and it will show up in the TSDoc.

[macrozone]

One issue i found is that the properties of the result are marked as optional/nullable. Can I make them non-nullable? Maybe this is just a documentation issue and i am missing something.

my query

-- @param {String} $1:organisationId
WITH RECURSIVE parentOrganisation AS (
  SELECT 
    id
  FROM 
    "Organisation"
  WHERE 
    id = $1
  UNION 
  SELECT 
    childOrganisation.id
  FROM 
     "Organisation" childOrganisation
    INNER JOIN parentOrganisation po ON po.id = childOrganisation."parentOrganisationId"
) 
SELECT * FROM parentOrganisation;

[macrozone]

this produces broken typescript code:

organisationId!: string) => ...

[macrozone]

and this affects the argument, not the result.

i mean the id of the result is nullable

[briangottfried]

I've gotten around this by creating a prisma generator that rewrites the types that I know shouldn't be nullable. Hopefully they fix this soon.

[natethelen]

This is a problem for us, too. The resulting types should only be null/undefined if the columns are nullable

[aleclarson]

It's maybe not ideal, but you can do this to narrow the type:

import assert from 'node:assert'

// …later on:
const [{ foo, bar }] = await prisma.$queryRawTyped(…)
//       ^? string | null

assert(foo !== null && bar !== null)

type FooType = typeof foo
//   ^? string (same for bar)

You can even do assert(foo && bar) if your values will never be falsy (empty string, false, zero, ...).

If performance is a concern, you could write a bundler plugin that removes assert calls, like this Rollup plugin: https://www.npmjs.com/package/@rollup/plugin-strip

[macrozone]

one limitation is that the columns that are fetched are fixed, so we can't use select and include.

Of course this is a tricky problem, but maybe there is a way to at least add some convenience to make the query less static.

Also it would be cool to have some convenience to be able to use a normal prisma where in those custom queries.

I mention this because with typedSQL you can partially work around some prisma limitations, like this issue #3725 (comment)

but only partially, because in the above example you would of course want to use select or include and probably where as well

[jharrell]

Yeah, this is unfortunately a current hard limitation as we are using the column info in order to properly type inputs and outputs. If we don't know what the columns returned are, then we don't have a way to know the returned types, returning us back to something similar to the original $queryRaw implementation.

Of course we're still thinking on it, but as of right now it's not something we're going to change.

[mindblight]

If your results set is small enough, I usually just return ids + aggregates from the TypedQuery, then use a regular prisma call with where: { id: { in: [/* returned ids */ ]} } to do select/include.

[karlhorky]

Thanks for TypedSQL! Really great to have more ways to connect TypeScript and SQL 🙌

I wanted to find out what the feedback would be on allowing TypedSQL for an alternate syntax for Prisma schema files. Would this be something that would be considered?

Ideally I would like SQL-in-JS (eg. using prisma.$queryRaw), but I think that's probably further away from the current philosophy of Prisma...

[jharrell]

Could you clarify a bit? Are you asking for the ability to use SQL statements to define Prisma models instead of the Prisma Schema Language?

For SQL-in-JS, I would still recommend the combination of Prisma ORM and Kysely via prisma-extension-kysely

[karlhorky]

Yeah, I'm looking to define a Prisma schema in SQL, instead of Prisma Schema Language.

Actually, I wouldn't really need the Prisma schema itself, I'm just using it for prisma migrate diff.

My motivation is:

1. I want to write my database schema in SQL (specifically, SQL-in-JS)
2. I want to do diffing between different versions (currently using prisma migrate diff for this), to see the SQL queries needed to morph a database to a different version

If there would be another way to achieve this without a Prisma schema, then that would be even better.

[z0d14c]

Needing a db connection to generate with --sql definitely threw a wrench in my team's adoption of this feature... Spent >2 days trying to get it to work with our build system and had to tap out :( Really would like to use this but not feasible for us right now

Some complications for us are that we currently don't commit our generated code, and we are using e.g. typegraphql-prisma generator which made committing our generated code more complicated. Being able to run this "offline" without a db connection would be amazing, but not sure of your implementation details/requirements so 🤷

[briangottfried]

In my CI pipeline, I just start a postgres container before I generate the sql. Kind of annoying but it works.

[MacPiston]

I agree, in my opinion generated code should not be commited, therefore Prisma Client is generated on the fly in my CI/CD pipeline. However, because of that I cannot use TypedSQL because I don't have running DB in that pipeline (it is not needed at any point).
Also, I don't quite get how requirement of running all migrations before generation makes sense, because the whole idea of type checking is to make sure everything looks good before deployment, and not after. Of course if there would've been a dedicated DB instance just for pipeline purposes then let's say it would be fine, but spinning up pipeline-only instance just for this purpose it's a overkill in my case and would extend completion time significantly.
I currently use @ts-safeql ESLint plugin and it provides choice whether to use a real DB instance or not.

[karlhorky]

Starting and setting up a database in CI is not slow:

• workflow: https://github.com/upleveled/preflight-test-project-next-js-passing/blob/main/.github/workflows/ci.yml
• workflow run: https://github.com/upleveled/preflight-test-project-next-js-passing/actions/runs/11316132299/job/31468103024

[MacPiston]

Still, it is unnecessary just for one feature that could work without this step

[krcourville]

Was curious to see how others addressed this in CI.

Just worked through this today for a docker image. Ended up installing and temporarily running a Postgres instance in a build stage of a multistage build.

Was worth the 2 hours vs dealing with constant merge conflicts for generated code.

[frontendphil]

I think there is a partial answer for this already in this discussion, but I'd like to raise it again. How can we deal with optional values for SQL statements? What we're doing right now is something like this:

select 
  "field", 
from 
  "Table"
where 
  "tenantId" = coalesce(${tenantId}, "tenantId")

Where we pass in tenantId and if it is null or undefined, the coalesce method will turn this into a comparison that is always true. Now, if I translate this to typed SQL, it looks like this:

select 
  "field", 
from 
  "Table"
where 
  "tenantId" = coalesce($1, "tenantId")

Now the parameter has to be a string. Even worse, the method to "Default it to empty string in TypeScript land" doesn't work, because that would "break" the coalesce method and require us to write more complex code in our queries.

I'd love for something like this:

-- @param {String?} $1:tenantId

select 
  "field", 
from 
  "Table"
where 
  "tenantId" = coalesce($1, "tenantId")

Not the ? at the end of the type definition to mark it as optional or maybe nullable (both would be fine for our use case).

[jharrell]

Our documentation needs improvement here. Try this format:

-- @param {String} $1:tenantId?

[hrueger]

Hi, really liking this new feature 🎉
However, I cannot get an array of values to work. Is that currently not supported or am I just doing it wrong?
Previously:

const boxofficePlatformFee = await locals.prisma.$queryRaw`SELECT SUM("price") AS "presaleOrdersPlatformFee"
    FROM "Ticket" t
	WHERE t.status NOT IN (${Prisma.join(INVALID_FINISHED_ORDER_STATUSES)})`;

I tried

SELECT SUM("price") AS "presaleOrdersPlatformFee"
    FROM "Ticket" t
	WHERE t.status NOT IN ($1)

await locals.prisma.$queryRawTyped(calculatePrice(Prisma.join(INVALID_FINISHED_ORDER_STATUSES)))

but I get Argument of type 'Sql' is not assignable to parameter of type 'string' which makes sense.
How can I achieve this? Thanks in advance.

[jharrell]

Could you try ANY instead of IN?

SELECT SUM("price") AS "presaleOrdersPlatformFee"
    FROM "Ticket" t
	WHERE t.status NOT ANY ($1)

[hrueger]

Hi @jharrell,
thanks for the answer. That did not work (ERROR: syntax error at or near "NOT"), however, the follwing works:

SELECT SUM("price") AS "presaleOrdersPlatformFee"
    FROM "Ticket" t
	WHERE t.status != ANY ($1)

Then, prisma correctly uses string[] as the type for that parameter. Interestingly, I cannot set the type in the comment explicitely.

-- @param $1:status

works, but

-- @param {String[]} $2:status

fails with SQL documentation parsing: invalid type: '[String]' (accepted types are: 'Int', 'BigInt', 'Float', 'Boolean', 'String', 'DateTime', 'Json', 'Bytes', 'Decimal', 'DatabaseAction', 'PrintJobStatus', 'ScanFailureReason') at '{String[]} $1:status'.

That is not important as the auto-discovered type is fine, but it is strange.

[jharrell]

We released it as Preview for a reason 🙂

Thanks for the feedback here and I agree that it feels off. I'll bring it back to the team.

[joonatanvanhala]

For longer SQL queries WITH clauses would be beneficial to clean up the SQL. Currently it seems that WITH clauses throw The underlying table for model ``(not available)`` does not exist. Is there plans to add support for WITH clauses?

Also, is there a way to utilise one SQL block in multiple different TypedSQL files without the need to duplicate it in each file?

[briangottfried]

WITH seems to work for me with postgres.

[therockstorm]

The TypedSQL queries I'm logging don't actually run in SQL. Following this documentation, I created repro.sql:

select * from person where id = any ($1);

Calling it with:

await prisma.$queryRawTyped(repro([1, 2]));

And logging the query with:

this.$on("query", (e) => {
  console.log(e.query, e.params);
});

Gives me:

select * from person where id = any ($1); [[1,2]]

Attempting to run either of these in SQL is an error:

select * from person where id = any (1,2)
select * from person where id = any ([1,2])

Because it's expecting this:

select * from person where id = any (array[1,2])

Can logging the query log the actual SQL that's sent to the database?

[aqrln]

The query that is logged is exactly what's executed by Prisma. The reason it works is because Prisma uses prepared statements.

[lukasgjetting]

TL;DR It would be nice if we could name the sql files in kebab-case

---

We generally try to avoid uppercase letters in our filenames. However, if I try to generate the client with a file called my-query.sql, I get the following error:

Error: /Users/lukas/test-project/prisma/sql/my-query.sql can not be used as a typed sql query: name must be a valid JS identifier

Would be nice if Prisma automatically translated kebab case to camel case. Kinda like it does to snake case for migrations. :-)

Such a cool feature!

[ghost]

It is not possible to use the param in more than one place.

-- @param {String} $1:query -- @param {Int} $3:limit -- @param {Int} $4:offset SELECT t.* FROM Title t LEFT JOIN TitleName tn ON tn.titleId = t.id WHERE MATCH (t.name) AGAINST (:query IN NATURAL LANGUAGE MODE) OR MATCH (tn.name) AGAINST (:query IN NATURAL LANGUAGE MODE) ORDER BY t.popularity DESC LIMIT :limit OFFSET :offset

Invalid prisma.$queryRawTyped() invocation:

Raw query failed. Code: N/A. Message: N/A

code: 'P2010',
clientVersion: '6.1.0',
meta: { code: 'N/A', message: 'N/A' }

It worked:

-- @param {String} $1:query -- @param {String} $2:queryd -- @param {Int} $3:limit -- @param {Int} $4:offset SELECT t.* FROM Title t LEFT JOIN TitleName tn ON tn.titleId = t.id WHERE MATCH (t.name) AGAINST (:query IN NATURAL LANGUAGE MODE) OR MATCH (tn.name) AGAINST (:queryd IN NATURAL LANGUAGE MODE) ORDER BY t.popularity DESC LIMIT :limit OFFSET :offset

[christinakarl]

I'm having an issue with the return type Result[] of queryRawTyped. I'm returning an array that includes a status of type "Unset" | "Approved" | null. In my typedSQL file, it is set as such:

SELECT
    **other variables**
    CASE
        WHEN d.is_approved THEN 'Approved'
        ELSE 'Unset'
    END AS status
FROM
    Users u
LEFT JOIN details d ON u.id = d.id

My query is defined like this:

const users : DetailsTableColumnsType[] = await prisma.$queryRawTyped(
    getDetailsperUser(**some filters**)

and the type like this:

export type DetailsTableColumnsType = {
  **other variables**
  status: "Unset" | "Approved" | null
}

With this setup, I get the error:

"Type 'Result[]' is not assignable to type 'DetailsTableColumnsType[]'. 
Type 'Result' is not assignable to type 'DetailsTableColumnsType'. 
Types of property 'status' are incompatible. 
Type 'string | null' is not assignable to type '\"Unset\" | \"Approved\" | null'.

The only solution I found was this:

const users : DetailsTableColumnsType[] = await prisma.$queryRawTyped(
    getDetailsperUser(**some filters**)
  ).then(results => results.map(result => ({
    ...result,
    status: result.status as "Unset" | "Approved" | null
}

or to add string to the type but that kind of defeats the purpose of the status field...

Any ideas how I can directly typecast the the Result[] I receive from queryRawTyped without mapping over it afterwards??

[0xivb]

Hi, first of all, thank you for this feature!
It's been a pleasure to use with only some minor issues; primarily that the sql/ folder gets cluttered with unrelated queries after a while.
It would be awesome if you could add support for queries in sub directories.

[WKampel]

This would be great. Should be pretty straightforward.

[yuicee1124]

This would be great!!!
It is hard to find the exact sql file which I want among many files without sub directories.
I want to organize the files.

[WKampel]

An active DB connection to use the --sql flag makes use of this difficult in a CI pipeline.

[WKampel]

I'm also trying to upsert an unknown amount of rows in MySql. Is this possible without array support?

[simonjodet]

Is there a way to use dynamic ORDER BY and LIMIT expressions with typedSql?

ORDER BY $2 $3; 

ends up with ERROR: syntax error at or near "$3"

ORDER BY $2;

ends up with ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list

[briangottfried]

We use case statements to get around this for postgres. Something like

...
ORDER BY
  CASE WHEN $3 = 'asc' THEN ... END ASC,
  CASE WHEN $3 = 'desc' THEN ... END DESC

[wmadden]

A quick update, we plan to release this preview feature to GA between September 2025 - February 2026. Read more here: #26136

[EkeMinusYou]

It seems that the following error occurs when using placeholders with @prisma/adapter-libsql.

Invalid `prisma.$queryRawTyped()` invocation:\n\n\nARGS_INVALID: Arguments do not match SQL parameters: value for parameter :pattern not found","error_name":"PrismaClientKnownRequestError"

Does anyone know a solution to this?

[EuSouVoce]

Awsome feature but it REALLY needs a way to define the import and export locations, because it breaks when using with multiclients (different databases each with it's own prisma schema and client)

[nikolasburk]

Quoting a user from Twitter:

would love this sql to live in TS right where it is needed

[carlassmann]

thanks for nudging me here @nikolasburk

colocation is king. writing the query next to the place where it is executed (or in a shared module where it makes sense) eliminates friction from using this feature.

[0-vortex]

Not sure it makes sense to import .sql files as typescript, and that breaks the separation of concern for workflows of database IDEs where you add the prisma folder into your db ide and keep coding with the src of the tech repo. If by "it is needed" it is implied that a particular repo is only using typedsql and manual queries, then that is IMHO a project specific choice and not representative of the full/intended prisa orm usage 😅

[carlassmann]

const email = "emelie@prisma.io";
const result = await prisma.$queryRaw`SELECT * FROM User WHERE email = ${email}`;

Prisma has raw queries that take advantage of sql template tags. This way you can define sql queries inside typescript (colocation), but they are not typed.

[0-vortex]

You could use the default typing for that like in the documentation: https://www.prisma.io/docs/orm/prisma-client/using-raw-sql/raw-queries#typing-queryraw-results

Alternatively you can emulate the include behaviour like so:

const email = "emelie@prisma.io";
const result = await prisma.$queryRaw<Prisma.User<{}>>`SELECT * FROM User WHERE email = ${email}`;

Maybe the feature typedSql name is not representative of all its features - personally, after using it for a couple of weeks and working around production issues, this feature feels complementary to the native prisma orm and raw query possibilities (IMHO not meant to replace the current raw queries in ay way, but provide a layer of reusability)

Then there is the future and long way to go before this hits GA. Hope everyone will be happy 🚀 🤷 🤞

[carlassmann]

Given the scope of Prisma typedSql is probably as good as it gets.

Moving query definition to TypeScript would require parsing the source code, identifying template tags and generating types for them. Similar to modern GraphQL tooling.

[elldritch]

TypedSQL is pretty great. 3 nits:

1. I really would like my queries to be co-located with their call sites. Ideally I'd be able to just write the query inline like sqlx-ts. I don't mind needing to re-run a command (or have a watch server running) before my query result types are available. To me, that would be much more preferable to having queries defined far away from where they're used (in my case, in a separate package in a monorepo).
2. Requiring a live database connection during the build is actually pretty annoying. My existing build pipeline was not set up this way, so configuring it to do so (e.g. in a Docker container build) was non-trivial. I wish it would compile based off of my Prisma schema.
3. TypedSQL should recognize when a query uses LIMIT 1 at the top-level and therefore can only possibly return 0 or 1 rows, and adjust the result type accordingly to be T | undefined.

[aleclarson]

Copying my feedback from #27204

1. Crashing --watch process when errors are found on startup

If any SQL files have errors, the --watch flag should prevent prisma generate from exiting. Instead, the SQL files with errors should be watched, then the process should try reading them again when changed.

Currently, the process exits, leading to a stilted DX.

2. Lack of incremental updates

In watch mode, a failed SQL file should not prevent other SQL files from being processed and their bindings generated. Otherwise, you're forced to fix all SQL files before doing anything in TypeScript.

3. Missing information in error messages

When a query has an error, all you get is the often-cryptic error message. It'd be better to have a "code frame" pointing to where in the query the error occurred. For now, I have to look at my Postgres instance's logs when I shouldn't need to do that.

What Prisma gives: ERROR: column reference "id" is ambiguous

Postgres does better by at least telling me the character offset, but it still isn't great, since it just prints the whole query and implies "go find it yourself".

ERROR:  column reference "id" is ambiguous at character 1352
STATEMENT:  WITH queue_item AS (
	SELECT
	id,
        …(truncated by me for brevity)…

For a good example of "code frames", I suggest looking at how @babel/code-frame does it.

[destroyer22719]

Hi there, is anyone using PostgreSQL features like json_agg and json_build_object that wish it properly generated types for json?

I've made an issue about it I hope Prisma implements types for it too: #27296

[julien-lottie]

As far as I can see, it's not possible to combine TypeSql with views. Is that correct?
Scenario:

• 2 sql queries, both calling a view (defined as a view in the model with the view extension).
• view is accessible through the prisma client
• the TypeSQL query fails with:

ERROR: FAILED_PRISMA_QUERY SQL.$queryRawTyped (8ms) {"args":{}}
    error: {
      "code": "P2010",
      "meta": {
        "code": "42P01",
        "message": "relation \"xxxx\" does not exist"
      },
      "clientVersion": "6.13.0",
      "name": "PrismaClientKnownRequestError