Prisma•16mo ago•

5 replies

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

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.

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

prismaClientTenant()

, and then on subsequent calls it always returns the same instance stored on globalThis.

as for the helper functions

Prisma.raw

Prisma.raw

etc - storing a reference to them on globalThis also seems to fix my issue

Jump to solution

Prisma•16mo ago•

5 replies

Hornster

nextjs + prisma: rawQuery breaking if using globalThis

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

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]

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

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

prismaClientTenant()

, and then on subsequent calls it always returns the same instance stored on globalThis.

as for the helper functions

Prisma.raw

Prisma.raw

etc - storing a reference to them on globalThis also seems to fix my issue

Jump to solution

nextjs + prisma: rawQuery breaking if using globalThis

nextjs + prisma: rawQuery breaking if using globalThis

Similar Threads

Similar Threads

Similar Threads