How to use multiple Prisma clients in single services?

[IArnaut2]

I wanted to have two databases, a main database (PostgreSQL) and test database (sqlite). Because Prisma 7 does not support multiple datasource providers in a single schema, I had to make another schema for the test database.
prisma/main/schema.prisma and prisma/test/schema.prisma:

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

datasource db {
  provider = "postgresql"
}

model Item {
  id   BigInt @id @default(autoincrement())
  name String @unique @db.VarChar(255)

  listings Listing[] @relation("ListingItems")
}

// ... Terms, Listing, User

// same as above

datasource db {
  provider = "sqlite"
}

model Item {
  id   BigInt @id @default(autoincrement())
  name String @unique

  listings Listing[] @relation("ListingItems")
}

// ... Terms, Listing, User

I made a configuration file for each schema.
prisma/main/prisma,config.ts and prisma/test/prisma.config.ts:

import "dotenv/config";
import { defineConfig, env } from "prisma/config";

export default defineConfig({
    schema: "./schema.prisma",
    migrations: { path: "./migrations" },
    datasource: { url: env("MAIN_DATABASE_URL") }
});

// same as above

export default defineConfig({
    // same as above
    datasource: { url: env("TEST_DATABASE_URL") }
});

I generated client classes and initialized Prisma clients with respective adapters.
prisma/main/generated/client.ts and prisma/test/generated/client.ts:

export const PrismaClient = $Class.getPrismaClientClass()
export type PrismaClient<LogOpts extends Prisma.LogLevel = never, OmitOpts extends Prisma.PrismaClientOptions["omit"] = Prisma.PrismaClientOptions["omit"], ExtArgs extends runtime.Types.Extensions.InternalArgs = runtime.Types.Extensions.DefaultArgs> = $Class.PrismaClient<LogOpts, OmitOpts, ExtArgs>
export { Prisma }

prisma/main/client.ts and prisma/test/client.ts:

import { PrismaPg } from "@prisma/adapter-pg";

import { PrismaClient } from "./generated/client";
import { MAIN_DATABASE_URL } from "../../global/env";

const connectionString = MAIN_DATABASE_URL;

const adapter = new PrismaPg({ connectionString });
const client = new PrismaClient({ adapter });

export default client;

import { PrismaBetterSqlite3 } from "@prisma/adapter-better-sqlite3";

import { PrismaClient } from "./generated/client";
import { TEST_DATABASE_URL } from "../../global/env";

const connectionString = TEST_DATABASE_URL;

const adapter = new PrismaBetterSqlite3({ url: connectionString });
const client = new PrismaClient({ adapter });

export default client;

I have services ItemService, ListingService, TermsService and UserService. UserService uses the main Prisma client. To use the service for the other client, I'd have to duplicate the entire service file just for that client, which would be a huge code smell and violate DRY principle.

import { compareSync, hashSync } from "bcrypt";

import logger from "../global/logger";
import logMessages from "../global/logMessages";
import { userMapper as mapper } from "../mappers";
import { User } from "../models";
import { IRegister, IUserChangePassword, IUserUpdate } from "../interfaces";
import prisma from "../prisma/main/client";

// 4 Authentication & authorization
export default {
    // Main
    create: async (data: IRegister) => {
        delete data.password2;
        data.email = data.email.toLowerCase();
        data.password = hashSync(data.password, 10);

        // 1 Parametrized queries
        const user = await prisma.user.create({ data });

        // 5 Security logging
        logger.info(logMessages.USER_SERV.create, { userId: user.id });
        return mapper.toClass(user);
    },

    // ... other functions
}

I turned the service into a class whose constructor has prisma: any as an argument...

// same as before

// 4 Authentication & authorization
export default class UserService {
    constructor(private prisma: any) {}

    // Main
    async create(data: IRegister) {
        // same as before
    }

    // ...
}

...so that I could instantiate the service with respective clients.
controllers/auth.controller.ts:

// ... other imports
import { mainClient } from "../prisma";
import { UserService } from "../services";

const service = new UserService(mainClient);

tests/userService.test.ts:

import { testClient } from "../prisma";
import { UserService } from "../services";

const service = new UserService(client);

Because prisma was any type, its properties and functions wouldn't be auto-completed. So I tried to join MainClient and TestClient classes into one type just called Client. And changed the type of prisma into Client.
prisma/index.ts:

import mainClient from "./main/client";
import { PrismaClient as MainClient } from "./main/generated/client";
import testClient from "./test/client";
import { PrismaClient as TestClient } from "./test/generated/client";

type Client = MainClient | TestClient;

export { Client, mainClient, testClient };

services/user.service.ts:

import { Client } from "../prisma";

// 4 Authentication & authorization
export default class UserService {
    constructor(private prisma: Client) {}
    
    // ...
}

But then the functions of user, such as create, findUnique etc, became uncallable.

const user = await this.prisma.user.create({ data });

Error:

This expression is not callable.
  Each member of the union type '(<T extends UserCreateArgs>(args: SelectSubset<T, UserCreateArgs<DefaultArgs>>) => Prisma__UserClient<GetFindResult<$UserPayload<DefaultArgs>, T, { ...; }>, never, DefaultArgs, { ...; }>) | (<T extends UserCreateArgs>(args: SelectSubset<...>) => Prisma__UserClient<...>)' has signatures, but none of those signatures are compatible with each other.

How do I use service for many Prisma clients simultaneously without duplicating code?

[ysujith728]

[IArnaut2]

Please reformat your post to show code in code blocks.

[dor-sr]

Type the service against only the delegate methods it uses, taken from the SQLite client. Both generated clients are assignable to that, so you keep autocomplete without any:

import type { PrismaClient as TestClient } from "../prisma/test/generated/client";

type Methods = "create" | "findUnique" | "findFirst" | "findMany"
  | "update" | "upsert" | "delete" | "deleteMany" | "count";

export type Client = {
  user: Pick<TestClient["user"], Methods>;
  item: Pick<TestClient["item"], Methods>;
  // listing, terms: same pattern
};

export default class UserService {
  constructor(private prisma: Client) {}
}

Use the SQLite types because the Postgres client accepts extra options, such as mode: "insensitive" on string filters and skipDuplicates. A type built from the Postgres client therefore rejects the SQLite one, but not the other way round. aggregate and groupBy don't line up between the two, so leave them out.

One thing that doesn't work: using a single client generated for postgresql with the SQLite adapter. Prisma 7 throws: "The Driver Adapter @prisma/adapter-better-sqlite3, based on sqlite, is not compatible with the provider postgres specified in the Prisma schema."

Tested on Prisma 7.10.0 with both clients generated.

[DevChiniwala]

Hey! Just a quick warning about the other proposed solution: You cannot use a single Prisma Client with both PostgreSQL and SQLite driver adapters. Prisma generates database-specific SQL inside the query engine. A client generated with provider = "postgresql" will execute Postgres-specific SQL (like RETURNING) which will immediately crash when passed to the @prisma/adapter-better-sqlite3 adapter.

Because Prisma 7 still requires distinct generated clients for different providers, you have the right approach by generating two separate clients.

The TypeScript union error This expression is not callable... happens because TypeScript strictly views MainClient and TestClient as two completely distinct types, even though their .user.create() signatures look identical to us.

The cleanest fix: Type Assertion (Duck Typing)

Since both generated clients have the exact same runtime methods and schema structure, the easiest and cleanest way to fix this without duplicating your service logic is to just cast the injected client to your MainClient type inside the service.

Change your user.service.ts to this:

typescript
import { Client } from "../prisma";
import { PrismaClient as MainClient } from "../prisma/main/generated/client";
export default class UserService {
private prisma: MainClient;
constructor(prisma: Client) {
// Cast the union to a single concrete type for TypeScript.
// At runtime, both clients expose the exact same methods, so this is perfectly safe!
this.prisma = prisma as unknown as MainClient;
}

async create(data: IRegister) {
    // Full autocomplete works, no union signature errors!
    const user = await this.prisma.user.create({ data });
    return user;
}

}

By casting prisma inside the constructor, TypeScript stops trying to reconcile the union types. Your IDE autocomplete will work perfectly using the MainClient types, but at runtime, if you pass in testClient, the exact same methods will execute seamlessly against SQLite.

(If this solves your TS errors and prevents the adapter crash, please click Mark as answer so others trying to run dual-databases can find this solution!)

[Sarvagya-24-chaturvedi]

Hi @IArnaut2,

The reason you are encountering the error:

"This expression is not callable. Each member of the union type has signatures, but none of those signatures are compatible with each other."

is a TypeScript limitation with unions of generic functions. Even though MainClient['user']['create'] and TestClient['user']['create'] look identical, each generated client has distinct internal branding and generic parameter constraints (SelectSubset<T, ...>). TypeScript cannot synthesize a single callable signature across a union of two distinct generic functions.

Here is how to solve this cleanly:

---

Solution: Make the Service Class Generic (Recommended)

Instead of typing the property as a union (private prisma: MainClient | TestClient), make the class generic over T extends MainClient | TestClient:

// services/user.service.ts
import { MainClient, TestClient } from "../prisma";
import { compareSync, hashSync } from "bcrypt";
import logger from "../global/logger";
import logMessages from "../global/logMessages";
import { userMapper as mapper } from "../mappers";
import { IRegister } from "../interfaces";

type SupportedClient = MainClient | TestClient;

export default class UserService<T extends SupportedClient = MainClient> {
  constructor(private prisma: T) {}

  async create(data: IRegister) {
    delete data.password2;
    data.email = data.email.toLowerCase();
    data.password = hashSync(data.password, 10);

    // ✅ Works! Full autocomplete and type-safety restored
    const user = await this.prisma.user.create({ data });

    logger.info(logMessages.USER_SERV.create, { userId: user.id });
    return mapper.toClass(user);
  }

  async findById(id: bigint) {
    return this.prisma.user.findUnique({ where: { id } });
  }
}

How it works:

When you instantiate the service, TypeScript automatically infers the exact client type for that instance:

// controllers/auth.controller.ts
import { mainClient } from "../prisma";
import UserService from "../services/user.service";

// Inferred as UserService<MainClient>
const service = new UserService(mainClient);

// tests/userService.test.ts
import { testClient } from "../prisma";
import UserService from "../services/user.service";

// Inferred as UserService<TestClient>
const testService = new UserService(testClient);

---

Alternative: Pass Only the Model Delegate

If your service only needs prisma.user, you can inject just the model delegate instead of the whole client:

export default class UserService<T extends MainClient["user"] | TestClient["user"]> {
  constructor(private userRepo: T) {}

  async create(data: IRegister) {
    return this.userRepo.create({ data });
  }
}

// Usage:
const service = new UserService(mainClient.user);
const testService = new UserService(testClient.user);

---

💡 Architectural Tip: Testcontainers instead of Dual Schemas

Maintaining two separate schema.prisma files (PostgreSQL vs SQLite) frequently leads to subtle bugs because SQLite does not support PostgreSQL features like native enums, BigInt autoincrement nuances, @db.VarChar, case-insensitive lookups, or JSONB operations.

Rather than maintaining dual schemas, the industry standard for testing Prisma apps is spinning up a temporary PostgreSQL instance using Testcontainers:

npm install -D @testcontainers/postgresql

import { PostgreSqlContainer } from "@testcontainers/postgresql";

const container = await new PostgreSqlContainer().start();
const testDatabaseUrl = container.getConnectionUri();
// Run migrations and run tests against real PostgreSQL

This allows you to keep a single schema.prisma and a single generated client for both production and testing.

[OHURU-IAN]

I tested this with Prisma 7.10.0, TypeScript 5 and your setup (two schemas, prisma-client generator, PrismaPg and PrismaBetterSqlite3 adapters). Here's a version that type-checks with both clients and keeps full autocomplete.

Why the union and generic versions fail

MainClient["user"]["create"] and TestClient["user"]["create"] are two different generic functions, each typed against its own generated Prisma namespace. TypeScript can't merge the signatures in a union, so you get "This expression is not callable". A generic UserService<T extends MainClient | TestClient> hits the same error, because inside the class this.prisma.user.create is still that union.

What works: pick the methods you need from the SQLite client

The SQLite client's query arguments are a subset of the Postgres client's. Postgres has extras like mode: "insensitive" and more filter types. So a Postgres delegate can be used wherever a SQLite delegate is expected, but not the other way round. Type the service against the SQLite client's methods:

// db.ts
import type { PrismaClient as MainClient } from "./generated/main/client";
import type { PrismaClient as TestClient } from "./generated/test/client";

type Crud =
  | "findUnique" | "findUniqueOrThrow" | "findFirst" | "findFirstOrThrow" | "findMany"
  | "create" | "createMany" | "update" | "updateMany" | "upsert"
  | "delete" | "deleteMany" | "count";

export type Db = {
  [M in "user" | "post"]: Pick<TestClient[M], Crud>;
};

// user.service.ts
import type { Db } from "./db";

export class UserService {
  constructor(private prisma: Db) {}

  create(email: string, name?: string) {
    return this.prisma.user.create({ data: { email, name } });
  }

  listWithPosts() {
    return this.prisma.user.findMany({ include: { posts: true } });
  }
}

new UserService(mainPrisma); // ✅ PrismaClient with PrismaPg
new UserService(testPrisma); // ✅ PrismaClient with PrismaBetterSqlite3

Return types are inferred, so (await users.listWithPosts())[0].posts[0].title is a string, and autocomplete works inside the service. I also ran it against a real SQLite database with the PrismaBetterSqlite3 adapter, and the queries work.

Limits I hit

I checked each method one at a time against both clients:

Method	Picked from SQLite client	Picked from Postgres client
findUnique, findFirst, findMany (+ OrThrow), create, update, updateMany, upsert, delete, count	✅	❌
createMany, deleteMany	✅	✅
aggregate, groupBy	❌	❌
whole delegate (Pick<Client, "user">)	❌ (fails on aggregate)	❌
$transaction	❌	❌

So picking the whole user delegate, or picking from the Postgres client, doesn't work. List the methods explicitly, as above. A service that needs aggregate, groupBy or $transaction would need the concrete client type for those calls.

Worth considering

The two-provider setup exists only for tests. Running tests against Postgres too, through Testcontainers or a throwaway Postgres database, removes the problem entirely: you have one schema and one client, and your tests use the same SQL dialect as production. SQLite tests can also pass on queries that would behave differently on Postgres, for example case sensitivity or JSON filters.

[mujtbabbas]

The union is the trap. With MainClient | TestClient, TypeScript needs every method to be callable on both members, and the two generated client classes carry different generic plumbing (different model payload/result types), so the union's delegate signatures collapse — that's why user.create ends up uncallable even though it works fine on each client individually.

Since your models are identical (only the provider and @db.* attributes differ), the patterns that work in practice:

1. Type services against one client, cast once at the composition root (recommended):

// services only ever see the main client's types
constructor(private readonly prisma: MainClient) {}

// in your test wiring — the only cast in the codebase
const testDb = new TestClient(/* ... */) as unknown as MainClient
const users = new UserService(testDb)

The cast is honest here: the runtime shapes really are interchangeable for identical models, and it stays contained to wiring code while every service keeps full, precise typing.

2. Structural interface via Pick:

type AppDb = Pick<MainClient, 'user' | 'item' /* the delegates your services use */>

This works cleanly when the delegate types are compatible; if the generated delegate types clash on variance, fall back to option 1.

3. Generic services (class UserService<C extends { user: ... }>>) — fully type-safe with no cast, but the boilerplate grows fast; only worth it for a library.

What I'd avoid is trying to "fix" the union — it will keep fighting you on every delegate. Option 1 with the cast isolated in the factory that builds your test client is what most dual-client setups end up with.

[IArnaut2]

Can you NOT post AI generated answers?

[praveen-kumary]

The error happens because MainClient | TestClient is a union of two separately generated TypeScript classes. In TypeScript, calling a method on a union type requires the function call signatures to be compatible, but the internal $UserPayload and generic type arguments from two separate generated outputs are structurally and nominally incompatible.

Here are the two best solutions to solve this cleanly without duplicating code:

---

Solution 1: Parameterize the Service with a Generic Type (Fastest & Fully Type-Safe)

Instead of a union (MainClient | TestClient), make your service generic over the client type, defaulting to MainClient. This allows full autocomplete and type inference in both your application and your test suite:

import { PrismaClient as MainClient } from "../prisma/main/generated/client";

export default class UserService<TClient extends { user: any } = MainClient> {
    constructor(private prisma: TClient) {}

    async create(data: IRegister) {
        delete data.password2;
        data.email = data.email.toLowerCase();
        data.password = hashSync(data.password, 10);

        // TypeScript now infers the exact return type based on whether
        // this instance was initialized with MainClient or TestClient!
        const user = await this.prisma.user.create({ data });

        logger.info(logMessages.USER_SERV.create, { userId: user.id });
        return mapper.toClass(user);
    }
}

Now both usages work without any errors or type casting:

// In production controller:
import mainClient from "../prisma/main/client";
const service = new UserService(mainClient); // inferred as UserService<MainClient>

// In tests:
import testClient from "../prisma/test/client";
const testService = new UserService(testClient); // inferred as UserService<TestClient>

---

Solution 2: Only Depend on the Delegate (MainClient['user'])

Your service doesn't actually need the entire database engine, connection pool, or lifecycle methods ($connect, $disconnect, $transaction). It only needs user.

You can type the constructor to accept an object containing the user delegate:

import type { PrismaClient as MainClient } from "../prisma/main/generated/client";

// Extract the delegate type from your primary client
type UserDb = {
    user: MainClient["user"];
};

export default class UserService {
    constructor(private prisma: UserDb) {}

    async create(data: IRegister) {
        const user = await this.prisma.user.create({ data });
        return mapper.toClass(user);
    }
}

If the SQLite and PostgreSQL user schemas share identical fields and operations, you can pass testClient as unknown as UserDb in tests.

---

Pro-Tip: SQLite for Tests vs. PostgreSQL for Production

While running tests against SQLite is tempting for speed, SQLite and PostgreSQL behave very differently in Prisma (differences in BigInt auto-increment behavior, @db.VarChar, JSON columns, case sensitivity, and transaction locks).

The modern recommendation is to run tests against an ephemeral PostgreSQL database (e.g. using Docker, testcontainers, or a temporary test branch in Neon) sharing your single schema.prisma. That eliminates schema divergence entirely.

[IArnaut2]

DO NOT POST AI GENERATED ANSWERS!

[praveen-kumary]

The error happens because MainClient | TestClient is a union of two separately generated TypeScript classes. In TypeScript, calling a method on a union type requires the function call signatures to be compatible, but the internal $UserPayload and generic type arguments from two separate generated outputs are structurally and nominally incompatible.

Here are the two best solutions to solve this cleanly without duplicating code:

---

Solution 1: Parameterize the Service with a Generic Type (Fastest & Fully Type-Safe)

Instead of a union (MainClient | TestClient), make your service generic over the client type, defaulting to MainClient. This allows full autocomplete and type inference in both your application and your test suite:

import { PrismaClient as MainClient } from "../prisma/main/generated/client";

export default class UserService<TClient extends { user: any } = MainClient> {
    constructor(private prisma: TClient) {}

    async create(data: IRegister) {
        delete data.password2;
        data.email = data.email.toLowerCase();
        data.password = hashSync(data.password, 10);

        // TypeScript now infers the exact return type based on whether
        // this instance was initialized with MainClient or TestClient!
        const user = await this.prisma.user.create({ data });

        logger.info(logMessages.USER_SERV.create, { userId: user.id });
        return mapper.toClass(user);
    }
}

Now both usages work without any errors or type casting:

// In production controller:
import mainClient from "../prisma/main/client";
const service = new UserService(mainClient); // inferred as UserService<MainClient>

// In tests:
import testClient from "../prisma/test/client";
const testService = new UserService(testClient); // inferred as UserService<TestClient>

---

Solution 2: Only Depend on the Delegate (MainClient['user'])

Your service doesn't actually need the entire database engine, connection pool, or lifecycle methods ($connect, $disconnect, $transaction). It only needs user.

You can type the constructor to accept an object containing the user delegate:

import type { PrismaClient as MainClient } from "../prisma/main/generated/client";

// Extract the delegate type from your primary client
type UserDb = {
    user: MainClient["user"];
};

export default class UserService {
    constructor(private prisma: UserDb) {}

    async create(data: IRegister) {
        const user = await this.prisma.user.create({ data });
        return mapper.toClass(user);
    }
}

If the SQLite and PostgreSQL user schemas share identical fields and operations, you can pass testClient as unknown as UserDb in tests.

---

Pro-Tip: SQLite for Tests vs. PostgreSQL for Production

While running tests against SQLite is tempting for speed, SQLite and PostgreSQL behave very differently in Prisma (differences in BigInt auto-increment behavior, @db.VarChar, JSON columns, case sensitivity, and transaction locks).

The modern recommendation is to run tests against an ephemeral PostgreSQL database (e.g. using Docker, testcontainers, or a temporary test branch in Neon) sharing your single schema.prisma. That eliminates schema divergence entirely.