Using Prisma Client as query builder only and get the SQL queries like prisma.user.findOne().getRawQuery()

[Jolg42]

Problem

Adding this feature opens the possibility to use Prisma Client as a query builder only.
Meaning getting the SQL queries without actually hitting the database.

What Lucas wanted is to be able to visualize the query that is going to execute in some sort of playground editor.

Suggested solution

prisma.user.findOne().getRawQuery() would return the SQL query to be executed.

Additional context

See Public Slack Community https://prisma.slack.com/archives/CCWDULGUW/p1591855689031600

[Jolg42]

Mentioned on Twitter TypeORM has a getSql() method

https://github.com/typeorm/typeorm/blob/51b2a63d/src/query-builder/QueryBuilder.ts#L377

    /**
     * Gets generated sql that will be executed.
     * Parameters in the query are escaped for the currently used driver.
     */
    getSql(): string {
        return this.getQueryAndParameters()[0];
    }

[onderonur]

This would be useful!
As a tiny example, I use dataloader for paginated queries in some cases. I actually explained it here with a small example:
graphql/dataloader#231 (comment)
There might be better solutions or alternative methods, but for now I create multiple SQL queries and merge them with UNION ALL.
I use prisma's sql utility function, and it makes a lot of things easier. It prevents SQL injection etc and you can create a conditional and readable query.
But if we could get the generated query from prisma itself, I would create a bunch of queries with prisma.post.findMany(...).getRawQuery() in a for loop and stitch them together later.
TypeORM has this feature but I couldn't find a cleaner way to create parameterized queries with it. You can find an example here:
https://github.com/onderonur/post-gallery/blob/master/api/src/db/entity/Comment.ts#L118
But it's not a clean solution and it becomes overwhelming if you have a lot of similar queries/connections/paginations etc.
Mine might be an edge case or there might be better solutions for GraphQL APIs. But this feature might be very useful for some scenarios.

[supermacro]

Here's another use case:

[brookmg]

Anything new with this functionality? If there is a way to intercept queries to get the raw SQL might work as well.

[Jolg42]

@brookmg The client has a query log feature, see docs at https://www.prisma.io/docs/reference/api-reference/prisma-client-reference#log
Curious to know if that works for you.

[pantharshit00]

I don't think would work exactly for your case as we run multiple statement many time based on the result of previous statement that we have sent but following is possible:

const prisma = new PrismaClient({
  log: [
    {
      emit: "event",
      level: "query",
    },
  ],
});

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

[brookmg]

@pantharshit00 I was just implementing that to see if I could get the actual query. It technically works but it's a horrible method to follow in production as there will be a lot of queries happening at any given time. If there is a way to tag a given query before it happens might ease the problem. Does something like that exist?

[brookmg]

So I think I found a workaround to tag the query. I hope you guys make a decent API to fetch the actual query tho because this is not an optimal solution.

Steps:

1. Define the Prisma client as an exportable object

const { PrismaClient } = require('@prisma/client')
export const mainClient= new PrismaClient({
    log: ['query', 'info', 'warn', 'error'],
})

2. Define a custom map and set up the listener to process each entry if necessary

const queryListeners = new Map()

export function registerQueryListener(tag , fun = (q) => {} ) {
    if (!tag) throw Error("Tag is required")
    queryListeners.set(tag, fun)
    return true   
}

export function unregisterQueryListener(tag) {
    if (!tag) throw Error("Tag is required")
    if (!queryListeners.has(tag)) return false

    queryListeners.delete(tag)
}

mainClient.$on('query' , async e => {    
    for (let item of queryListeners) {
        
        // If the tag was not mentioned in the query 
        // in any way then we shouldn't even bother
        if (!e['params'].includes(item[0])) continue;   

        e['custom-tag'] = item[0]
        await item[1](e)
    }
}) 

3. Call the above methods and run the query from the controller

    let first_query = ""
    let first_params = []

    const queryTag = `list_for_user_${await uuidV4()}`
    await registerQueryListener(queryTag , async (q) => { 
        first_query = q['query']
        first_params = q['params']
        await unregisterQueryListener(queryTag)
        console.log(`Processed ${queryTag}`)
    })

    // This is a waste as we are not going to use this query, we are only running it to intercept the raw sql
    await prisma.contentinterestgraph.findMany({ 
        take: 10,
        where: {
            ...whereConditions,
            NOT: {
                id: queryTag
            }
        }
    })

What do you guys think?

[mubaidr]

^ This seems very in-elegant solution.

I think its best that prisma query engine should expose interface to generate queries (similar to getSql()), this will help write complicated queries (or in my case using two queries, which is in-efficient both on db server running multiple queries and in terms of memory requirement to hold intermediate results).

[brookmg]

I agree @mubaidr. This is a workaround I used for now because no such method exist.

[mubaidr]

Or if at least there could be before execution event which can provide query and allowing us to cancel query execution (alternate to on query event which returns query after/during execution)

[sawirricardo]

any progress on this?

[nova974]

People still need this

[ivosabev]

Complex queries are quite difficult to achieve in the Prisma and prone to errors, so this functionality could be very helpful for verifying the output SQL.

Using the generic query listener is not helpful when you have hundreds of queries executed in a single request and you only want to see the SQL of a specific one.

toSQL() would a good name similar to knex's implementation.

[hariria]

any progress on this?

wanted to bump this 👀

[Jolg42]

I think there is a fundamental issue that makes things difficult here, it was mentioned earlier but to put it in other words:

Prisma Client will send queries "dynamically", so the exact sequence of SQL queries is not easily predictable.
As an example, for some Prisma Client queries, Prisma's Query Engine will first send 1 query statement and then depending on the result of that, do additional queries, or not.

[hariria]

I think even the 1st query statement would be great.

[revero-doug]

I think there is a fundamental issue that makes things difficult here, it was mentioned earlier but to put it in other words:

Prisma Client will send queries "dynamically", so the exact sequence of SQL queries is not easily predictable. As an example, for some Prisma Client queries, Prisma's Query Engine will first send 1 query statement and then depending on the result of that, do additional queries, or not.

certain operations like create should be able to yield SQL deterministically, regardless of context. this feature would be very useful for preparing manual migrations (e.g. drop these records, create these records)

[jscul]

Prisma Client will send queries "dynamically", so the exact sequence of SQL queries is not easily predictable. As an example, for some Prisma Client queries, Prisma's Query Engine will first send 1 query statement and then depending on the result of that, do additional queries, or not.

I appreciate the challenge here. Even find statements with includes aren't using LEFT JOIN under the hood. My use case is creating scripts that will roll the DB back. So I'd need the CUD of CRUD.

Couldn't you just return all the SQL queries? Return an array rather than a single query. Let the user pick out what they need and create their own tests.

[casey-chow]

certain operations like create should be able to yield SQL deterministically, regardless of context. this feature would be very useful for preparing manual migrations (e.g. drop these records, create these records)

Even then it's difficult because it's possible to link records not by their primary key, so a query may need to look up the linked record before inserting the foreign key into the database.

[janpio]

We could only do this when we limit the types of queries that Prisma can execute. Many nested operations would just not be possible any more. Similar to why we currently often do multiple queries where technically 1 would be possible, that just has not been a focus in the last few years, so we can not "just" do this. This will be a major initiative.

[YaakovR]

mainClient.$on('query' , async e => {    
    for (let item of queryListeners) {
        
        // If the tag was not mentioned in the query 
        // in any way then we shouldn't even bother
        if (!e['params'].includes(item[0])) continue;   

        e['custom-tag'] = item[0]
        await item[1](e)
    }
})

@brookmg This is a nice hack! I'm just wondering what your intention was when doing e['custom-tag'] = item[0]. How would you then use e['custom-tag']?

[Kaszanas]

I think there is a fundamental issue that makes things difficult here, it was mentioned earlier but to put it in other words:

Prisma Client will send queries "dynamically", so the exact sequence of SQL queries is not easily predictable. As an example, for some Prisma Client queries, Prisma's Query Engine will first send 1 query statement and then depending on the result of that, do additional queries, or not.

Would it then be possible to record a query as it is happening? This should not be a problem for non-destructive queries?

[RuchitPatel14]

I have the same requirement, I want to do groupBy on a specific table but based on month only, and another use case is prisma group doesn't support the grouping on relation and we can't select/include to fetch relation model data
#16243

So if you guys create one method like getRawQuery() or getSQL() then we can use this SQL where clause with left join, if we have filters then merge in our SQL group query so we can execute like below

because it is quite complex to create a where clause and join query when we have to filter on 50+ columns ( on the relation model too)

select count(id) from table_name where (getSQL where) group by year(date)

[altinthaqi]

This has been opened since 2020 with a great amount of people requesting the feature but no valid solution to date -

I need Prisma to return the SQL executed so I can use the generated SQL WHERE condition. This is currently the only thing holding me back from Prisma.

[kristijanvlahov]

Would be great if it's implemented, this would really save developers a lot of time.

[rinorbardiqi]

This is must needed for my start-up company, i'm hoping this will be done in near future otherwise i would even consider to leave Prisma and go with other ORM maybe like Drizzle

[Kaszanas]

This is must needed for my start-up company, i'm hoping this will be done in near future otherwise i would even consider to leave Prisma and go with other ORM maybe like Drizzle

It is possible to use SQLAlchemy to get raw queries. You may want to look into that if this is a critical issue

[arily]

This is must needed for my start-up company, i'm hoping this will be done in near future otherwise i would even consider to leave Prisma and go with other ORM maybe like Drizzle

I ended up use both.

• Use Drizzle relational query when possible, Drizzle still lacks a lot of features so don't expect it can do too much.
• Use Drizzle to do very easy CURD (for example DELETE FROM posts WHERE id = 1)
• use Prisma when I can't achieve with drizzle(more advanced CURD, nested filtering etc.)
• use Drizzle query builder for anything else (this kind of queries are very hard for ORM anyways, you don't lose much DX. you don't have type checks for queryRaw in Prisma )

[eliliam]

Is this being worked on by anyone? It feels like we've had very little correspondence from Prisma themselves around this. There is a big need for this, especially when debugging individual queries where you don't want to enable logging at the root Prisma client level via the currently supported logging method.

[vladinator1000]

I started working on a package to solve this using the D1 adapter as inspiration, but I got stuck on this error.

double free or corruption

Repo: https://github.com/vladinator1000/prisma-printer