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?

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

LGTM

This would be awesome!

Adding a DIRECT_URL got around this for me.

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

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

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?

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

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

@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?

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",
    },

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

@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.

@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.

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

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.

I am facing the same problem.

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.

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()

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.

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;

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.

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

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

this produces broken typescript code:

organisationId!: string) => ...

and this affects the argument, not the result.

i mean the id of the result is nullable

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.

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

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

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.

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.

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

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.

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

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.

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

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

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.

Our documentation needs improvement here. Try this format:

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

Could you try ANY instead of IN?

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

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.

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.

WITH seems to work for me with postgres.

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

This would be great. Should be pretty straightforward.

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.

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

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.

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 😅

const email = "[email protected]";
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.