Prisma•3w ago

nextjs + prisma: rawQuery breaking if using globalThis

Hi! I'm currently working on a nextjs (14.x) project, where I also want to use prisma (5.22). I have encountered an issue with nextjs always generating new clients in dev mode, so I followed the documentation and only generate the client, if it does not yet exist on globalThis. This seems to work when I use the client directly, but rawQueries only work on the first rendered route. As soon as a route has to be re-rendered (which happens very often in dev mode), the parameters no longer get properly resolved. Here is a simple example, queried with a freshly created client:

const fields = Prisma.raw(['id','name'].join(','))
const items = await prismaClient.$queryRaw`SELECT ${fields} FROM project WHERE id = ${projectId}`

const fields = Prisma.raw(['id','name'].join(','))
const items = await prismaClient.$queryRaw`SELECT ${fields} FROM project WHERE id = ${projectId}`

Checking the query log, the following query is executed:

query: SELECT id,name FROM project WHERE id = $1
params: [2]

query: SELECT id,name FROM project WHERE id = $1
params: [2]

So it seems the fields are put directly into the statement, and a placeholder is used for projectId. Now if I use a client assigned to globalThis, then on the first request this will work as well - but as soon as a prisma route needs to be re-compiled, the executed query changes:

query: SELECT $1 FROM project WHERE id = $2
params: [{"values":[],"strings":["id,name"]},2]

query: SELECT $1 FROM project WHERE id = $2
params: [{"values":[],"strings":["id,name"]},2]

This query will no longer work. Seems like as soon as nextjs compiles a new route, then certain parts of the Prisma namespace also get instantiated again, which in turn makes them no longer work with an older instance of the client? Not exactly sure what I can do to remedy this, other than just letting nextjs create new clients whenever it feels like it. Help would be greatly appreciated!

Solution:

its a bit different, as I need multiple client instances due to a multi tenant setup. ```js import { PrismaClient as PrismaClientTenant } from './client' ...

Jump to solution

3 Replies

Nurul•3w ago

Hi @Hornster 👋 How are you instantiating your PrismaClient? Can you share the code snippet of it? To confirm, are you instantiating PrismaClient like this? https://www.prisma.io/docs/orm/more/help-and-troubleshooting/help-articles/nextjs-prisma-client-dev-practices#solution

Best practice for instantiating Prisma Client with Next.js | Prisma...

Best practice for instantiating Prisma Client with Next.js

Solution

Hornster•3w ago

its a bit different, as I need multiple client instances due to a multi tenant setup.

import { PrismaClient as PrismaClientTenant } from './client'

function tenantSchemaUrl(tenantId: number) {
  const dbUrlBase = process.env.DB_URL_BASE
  return `${dbUrlBase}?schema=tenant_${tenantId}`
}

declare const globalThis: {
  [K in string]: PrismaClientTenant
} & typeof global;

function generateClient(tenantId: number) {
  const databaseUrl = tenantSchemaUrl(tenantId)
  const client = new PrismaClientTenant({
    datasources: {
      db: {
        url: databaseUrl
      }
    }
  })
  return client
}

export function prismaClientTenant(tenantId: number) {
  const key = `prisma_tenant_${tenantId}`
  if (!globalThis[key]) {
    const client = generateClient(tenantId)
    globalThis[key] = client
  }

  return globalThis[key]
}

import { PrismaClient as PrismaClientTenant } from './client'

function tenantSchemaUrl(tenantId: number) {
  const dbUrlBase = process.env.DB_URL_BASE
  return `${dbUrlBase}?schema=tenant_${tenantId}`
}

declare const globalThis: {
  [K in string]: PrismaClientTenant
} & typeof global;

function generateClient(tenantId: number) {
  const databaseUrl = tenantSchemaUrl(tenantId)
  const client = new PrismaClientTenant({
    datasources: {
      db: {
        url: databaseUrl
      }
    }
  })
  return client
}

export function prismaClientTenant(tenantId: number) {
  const key = `prisma_tenant_${tenantId}`
  if (!globalThis[key]) {
    const client = generateClient(tenantId)
    globalThis[key] = client
  }

  return globalThis[key]
}

As every tenant needs a different default schema in the client's database url, I ended up going with this. It seems to work - the client is created when I first call prismaClientTenant(), and then on subsequent calls it always returns the same instance stored on globalThis. as for the helper functions Prisma.raw etc - storing a reference to them on globalThis also seems to fix my issue 🤔

HornsterOP•2w ago

I think I can mark this as solved for now. as mentioned above, storing references to the helper functions on globalThis seems to work - but there is no mention of that in the documentation. so my suggestion: the troubleshooting page for nextjs should also mention this.

Gaming

Programming

nextjs + prisma: rawQuery breaking if using globalThis