Using prisma with two different databases.

[whole canopy]

I have a NextJS project using Prisma ORM with Microsoft SQL Server. I am trying to setup Prisma to work with two different SQL Server databases in the same project. I cannot seem to get it working. I keep getting an error: TypeError: The "path" argument must be of type string. Recieved undefined. Anyone have any idea?

[thorny swallow]

Skipping coffee for instant knowledge! Let's brew that AI answer now.

[whole canopy]

generator client {
  provider = "prisma-client-js"
  output = "../prisma/generated"
}

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

... Existing models below

generator client2 {
  provider = "prisma-client-js"
  output   = "../prisma2/generated"
}

datasource db2 {
  provider = "sqlserver"
  url      = env("DATABASE_URL2")
}

... Existing models below

import { PrismaClient as PrismaClient1 } from "../prisma/generated";
import { PrismaClient as PrismaClient2 } from "../prisma2/generated/";

const globalForPrisma1 = global as unknown as { prisma?: PrismaClient1 };
const globalForPrisma2 = global as unknown as { prisma?: PrismaClient2 };

export const prisma = globalForPrisma1.prisma || new PrismaClient1();
export const prisma2 = globalForPrisma2.prisma || new PrismaClient2();

if (process.env.NODE_ENV !== "production") {
  globalForPrisma1.prisma = prisma;
  globalForPrisma2.prisma = prisma2;
}

Currently I'm using two different prisma folders, I've also tried under the same prisma folder. Always getting the same error.

[thorny swallow]

I can help you with setting up Prisma to work with two different SQL Server databases in your NextJS project.

The error you're encountering ("The 'path' argument must be of type string. Received undefined") is a common issue when working with multiple Prisma schemas. This typically happens when Prisma can't find the schema file path correctly.

Here's how you can set up multiple databases with Prisma:

1. Create separate schema files

Create two separate schema files for each database:

prisma/
  db-1/
    schema.prisma
  db-2/
    schema.prisma

In each schema file, define the datasource and specify a custom output path for the generated client:

For prisma/db-1/schema.prisma:

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

generator client {
  provider = "prisma-client-js"
  output   = "../../node_modules/@prisma-db-1/client"
}

// Your models for database 1

For prisma/db-2/schema.prisma:

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

generator client {
  provider = "prisma-client-js"
  output   = "../../node_modules/@prisma-db-2/client"
}

// Your models for database 2

2. Set up environment variables

In your .env file:

DATABASE_URL_DB_1="Server=database1;Database=tesla1;User Id=sa;Password=mysecret;TrustServerCertificate=true;"
DATABASE_URL_DB_2="Server=database2;Database=tesla2;User Id=sa;Password=mysecret;TrustServerCertificate=true;"

3. Generate clients and run migrations

For the error you're encountering, make sure to use the full path to your schema files when running Prisma commands:

npx prisma generate --schema=./prisma/db-1/schema.prisma
npx prisma generate --schema=./prisma/db-2/schema.prisma

npx prisma migrate dev --schema=./prisma/db-1/schema.prisma --name initial
npx prisma migrate dev --schema=./prisma/db-2/schema.prisma --name initial

If you're using a package manager like pnpm, make sure to use the pac

[thorny swallow]

kage manager's command:

pnpm prisma generate --schema=./prisma/db-1/schema.prisma

4. Use the clients in your code

import { PrismaClient as PrismaClient1 } from '@prisma-db-1/client';
import { PrismaClient as PrismaClient2 } from '@prisma-db-2/client';

const db1 = new PrismaClient1();
const db2 = new PrismaClient2();

// Use db1 and db2 to query your databases

5. Add helper scripts

To simplify your workflow, add helper scripts to your package.json:

"scripts": {
  "postinstall": "npx prisma generate --schema ./prisma/db-1/schema.prisma && npx prisma generate --schema ./prisma/db-2/schema.prisma",
  "generate": "npx prisma generate --schema ./prisma/db-1/schema.prisma && npx prisma generate --schema ./prisma/db-2/schema.prisma",
  "migrate": "npx prisma migrate dev --schema ./prisma/db-1/schema.prisma && npx prisma migrate dev --schema ./prisma/db-2/schema.prisma",
  "studio": "npx prisma studio --schema ./prisma/db-1/schema.prisma --port 5555 & npx prisma studio --schema ./prisma/db-2/schema.prisma --port 5556"
}

This approach is based on the official Prisma documentation and community solutions for working with multiple databases Multiple Databases Guide and GitHub Discussion #24247.

[whole canopy]

How should I properly export my clients to use the prisma clients throughout my application

[thorny swallow]

🤔 Alright, thanks for letting us know that there's still some help needed. A team member will reply to you shortly.

In the meantime, please let us know any additional info, questions, or which parts of the answer may need clarification.

[whole canopy]

I've tried implementing and still getting same error

import { ipManamgement } from "@/lib/prisma";

export default async function IPManagementPage() {
  const test = await ipManamgement.iPAddresses.create({
    data: {
      IPAddress: "12345",
    },
  });
  return (
    <div>
      <h1>IP Management</h1>
    </div>
  );
}

I've tried both:

import { PrismaClient as PrismaClient1 } from "@prisma-repair/client";
import { PrismaClient as PrismaClient2 } from "@prisma-ip-management/client";

export const prisma = new PrismaClient1();
export const ipManamgement = new PrismaClient2();

and

import { PrismaClient as PrismaClient1 } from "@prisma-repair/client";
import { PrismaClient as PrismaClient2 } from "@prisma-ip-management/client";

const globalForPrisma1 = global as unknown as { prisma?: PrismaClient1 };
const globalForPrisma2 = global as unknown as { prisma?: PrismaClient2 };

export const prisma = globalForPrisma1.prisma || new PrismaClient1();
export const ipManagement = globalForPrisma2.prisma || new PrismaClient2();

if (process.env.NODE_ENV !== "production") {
  globalForPrisma1.prisma = prisma;
  globalForPrisma2.prisma = ipManagement;
}