PrismaP
Prisma•15mo ago•
5 replies
Hornster

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}`

Checking the query log, the following query is executed:
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]


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.

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 🤔
Jump to solution
Was this page helpful?