How to use Prisma with multiple database schemas

Multiple database schema support is currently a very early, experimental preview feature. Introspection and applying your Prisma schema to your database with Prisma Migrate and db push are currently not supported. If you rely on them, please do not try this preview feature in the main branch of your project or in production.

Multiple database schema support is currently only available with the PostgreSQL and SQL Server connectors.

This guide explains how to include multiple database schemas in your Prisma schema, and how to query across multiple database schemas with Prisma Client.

What are database schemas?

Many database providers allow you to organise database tables into named groups. This can be done to make the logical structure of the data model easier to understand, or to avoid naming collisions between tables.

In PostgreSQL and SQL Server these groups are known as schemas. We will refer to them as database schemas to distinguish them from Prisma's own schema file.

How to enable the multiSchema preview feature

Multi-schema support is currently in an early, experimental preview. To enable the multiSchema preview feature, you will need to add the multiSchema feature flag to the previewFeatures field of the generator block in your Prisma schema file:

schema.prisma
1generator client {
2 provider = "prisma-client-js"
+ previewFeatures = ["multiSchema"]
4}
5
6datasource db {
7 provider = "postgresql"
8 url = env("DATABASE_URL")
9}

How to include multiple database schemas in your Prisma schema

To use multiple database schemas in your Prisma schema file, add the names of your database schemas to an array in the schemas field, in the datasource block. The following example adds a "base" and a "transactional" schema:

schema.prisma
1generator client {
2 provider = "prisma-client-js"
3 previewFeatures = ["multiSchema"]
4}
5
6datasource db {
7 provider = "postgresql"
8 url = env("DATABASE_URL")
+ schemas = ["base", "transactional"]
10}

You do not need to change your connection string. The schema value of your connection string is the default database schema that the Prisma Client connects to and uses for raw queries. All other Prisma Client queries will use the schema of the model that you are querying.

To designate a model as belonging to a specific database schema, add a @@schema attribute with the name of the database schema as a parameter. In the following example the User model is part of the "base" schema, and the Order model is part of the "transactional" schema:

schema.prisma
1model User {
2 id Int @id
3 orders Order[]
4
+ @@schema("base")
6}
7
8model Order {
9 id Int @id
10 user User @relation(fields: [id], references: [id])
11 user_id Int
12
+ @@schema("transactional")
14}

Currently it is not allowed to have models with the same name in your Prisma schema that use the @@schema attribute to reference different database schemas. All models in your Prisma schema must have unique names. Otherwise, your Prisma schema will fail to validate.

How to query across multiple database schemas with Prisma Client

You can query models in multiple database schemas without any change to your Prisma Client query syntax. For example, the following query finds all orders for a given user, using the Prisma schema above:

const orders = await prisma.order.findMany({
where: {
user: {
id: 1,
},
},
})

Learn more about the multiSchema preview feature

To learn more about future plans for the multiSchema preview feature, or to give feedback, refer to our Github issue.

Edit this page on GitHub