Multischema configuration does not support environment variables

[aaron-henselin]

When attempting to use an environment variable to configure the schemas array in the datasource, OR the @@schema for models, the following error occurs:

Environment variables loaded from .env
Prisma schema loaded from prisma\schema.prisma
Error: Schema validation error - Error (query-engine-node-api library)
Error code: P1012
error: Expected a string value, but received functional value `env("DATABASE_SCHEMA")`.
  -->  schema.prisma:10
   | 
 9 |   url      = env("DATABASE_URL")
10 |   schemas  = [env("DATABASE_SCHEMA"), "public"]
   | 

Validation Error Count: 1
[Context: getConfig]

[janpio]

That is currently intentional and not implemented. What is your use case that would make this useful?

[aaron-henselin]

In this case, Heroku Connect, which uses the database as a formal pass-through into Salesforce. A Heroku Connect connected database can have "copies" of the same tables which direct the bidirectional replication, each in its own schema. So instead of a traditional "app1" / "app2" schema, it's a "dev" / "production" schema. Is there a workaround or better way you can think of to accommodate this?

[janpio]

Interesting. So you would use the env in the schemas list to essentially be able to switch between your dev and production data?

[aaron-henselin]

Exactly. In this use case, schema is really treated as a part of the environment/connectionstring rather than the application.

[jwwisgerhof]

We have the same issue. Our primary schema's name has a postfix that denotes the environment. Even though our production schema is in a separate DB, our development schemas (we run ~4 test instances) run in the same DB server as cost saving.

Most critically, we receive external (integration) data from other organisational units as a direct DB injection - they determine the database name (which are also namespaced by environment).

The same must then also apply for the @@schema() call of course.

[janpio]

@aaron-henselin From you responses I assume you used multiSchema with a Heroku Connect database already. Can you maybe tell us a bit about your experience in this Heroku Connect specific issue https://github.com/prisma/prisma/issues/7801? Thanks!

[ZeeshanZubair2]

I have the same kind of issue. I am implementing schema level multitenancy. Each tenant has its own schema. On the basis of request I would connect to a specific schema.

import { PrismaClient } from '@prisma/client'
const client = new PrismaClient({ datasources: { db: { url: 'postgresql://postgres:postgres@localhost:5432/db?schema=${request.tenantId}' }} })

I want to use URL schema in schemas array. Something like this(@defaultSchema will be a variable and it will get schema from datasource url)

datasource database {
  provider = "postgresql"
  url      = env("POSTGRES_DATABASE_URL")
  schemas  = ["common",@defaultSchema]
}

generator client {
  provider = "prisma-client-js"
  output   = "../node_modules/@notiz/prisma"
  previewFeatures = ["multiSchema"]
}

model test {
  userId     String   @map("user_id") @database.Uuid
  interestId String   @map("interest_id")
  @@id([userId, interestId])
  @@schema("common")
}

model user {
  id String
  name String
   @@schema(@defaultSchema)
}