How to change PrismaClient database connection at runtime?

Question

I have .env file like

DATABASE_URL="sqlserver://srv:50119;initial catalog=mydb;user=aaa;password=bbb;"

and then schema.prisma like

datasource db {
  provider = "sqlserver"
  url      = env("DATABASE_URL")
}

generator client {
  provider = "prisma-client-js"
  previewFeatures = ["microsoftSqlServer"]
}

I generate a client using:

npx prisma generate

and then Prisma works great in my express app using:

const prisma = new PrismaClient();

Say I wanted to use a different db for user for multi-tenancy, how can I achieve this? Ideally I'd want to switch the db connection at runtime but it seems that DATABASE_URL is only read during prisma generate and not at runtime so the generated client ends up with a hardcoded db url.

Answer 1

You can use the datasource property to create a new PrismaClient instance and pass a dynamic URL.

datasources

Programmatically overrides properties of the datasource block in the schema.prisma file - for example, as part of an integration test. See also: Data sources