How to change PrismaClient database connection at runtime?

user_1221269 0
Viewed 2565

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.

prisma
2 Answers

You can use this to create a new PrismaClient instance and pass a dynamic URL.

user_88743510

So just to confirm, to use this with express I should do this:

app.use(async function(req, res, next) {

  const prisma = new PrismaClient({
    datasources: {
      db: {
        url: "new db url goes here",
      },
    },
  });

  req.prisma = prisma;
  next();
});

Instead of this?:

const prisma = new PrismaClient();

app.use(async function(req, res, next) {
  req.prisma = prisma;
  next();
});
user_12212690
Related