Using multiple PostgreSQL schemas with Prisma

In PostgreSQL, schemas act as namespaces within the database and are containers for objects such as tables and indexes. In this article, we explain how they work and what are their benefits. We also provide examples of how to use them with Prisma.

The public schema

PostgreSQL creates a schema called public out of the box for every new database. Let’s say we have the following Article model.

1model Article {
2  id      Int    @id @default(autoincrement())
3  title   String
4  content String
5}

When we generate a migration, we can see that it does not mention any schemas at all.

If you want to know more about migrations with Prisma, check out API with NestJS #115. Database migrations with Prisma

1npx prisma migrate dev --name create-article-table

1-- CreateTable
2CREATE TABLE "Article" (
3    "id" SERIAL NOT NULL,
4    "title" TEXT NOT NULL,
5    "content" TEXT NOT NULL,
6 
7    CONSTRAINT "Article_pkey" PRIMARY KEY ("id")
8);

This is because, by default, when we create a table without specifying the schema, PostgreSQL attaches it to the public schema.

Similarly, when we make a SQL query and don’t specify the schema, PostgreSQL assumes that we mean to use the public schema.

1SELECT * FROM "Article";

Determining the schema to use

This is controlled through the search_path variable built into PostgreSQL. It contains the order of schemas PostgreSQL needs to look for when we make a query without specifying the schema explicitly.

1SHOW search_path;

By default, it contains "$user", public. The "$user" refers to the current user’s name that we can check through the current_user variable.

1SELECT current_user;

Therefore, "$user", public in our case means that PostgreSQL first tries to look for the "Article" table in the admin schema, then in the public schema.

By default, the admin schema does not exist. If that’s the case, PostgreSQL ignores it.

We could change the default schema by modifying the search_path variable.

1SET search_path TO another_schema_name;

Fortunately, we can quickly go back to the default value.

1SET search_path TO DEFAULT;

If we want to be explicit in our query, we can prepend the table’s name with the schema we want to use.

1SELECT * FROM public."Article";

Creating new schemas

We need to enable the multiSchema preview feature to start using additional schemas with Prisma.

1generator client {
2  provider        = "prisma-client-js"
3  previewFeatures = ["multiSchema"]
4}

We also need to list the schemas we want to use.

1datasource db {
2  provider = "postgresql"
3  url      = env("DATABASE_URL")
4  schemas  = ["user_data"]
5}

Now, we need to use the @@schema attribute to specify which schema we want to use with a particular model in our database.

1model Address {
2  id      Int    @id @default(autoincrement())
3  street  String
4  city    String
5  country String
6  user    User?
7 
8  @@schema("user_data")
9}
10 
11model User {
12  id        Int      @id @default(autoincrement())
13  email     String   @unique
14  name      String
15  password  String
16  address   Address? @relation(fields: [addressId], references: [id])
17  addressId Int?     @unique
18 
19  @@schema("user_data")
20}

The above code uses relationships. If you want to know more, check out API with NestJS #33. Managing PostgreSQL relationships with Prisma

However, when we try to run a migration, we might encounter a problem.

1npx prisma migrate dev --name add-user-data

1234567891011 Error: Prisma schema validation - (validate wasm)Error code: P1012error: Error validating model "Article": This model is missing an `@@schema` attribute.  -->  schema.prisma:12   | 11 | 12 | model Article {13 |   id      Int    @id @default(autoincrement())14 |   title   String15 |   content String16 | }

This is because when we start using multiple PostgreSQL schemas with Prisma, we need to use the @@schema with every model. Let’s add the public schema to our list of schemas and use it with the Article model.

1datasource db {
2  provider = "postgresql"
3  url      = env("DATABASE_URL")
4  schemas  = ["public", "user_data"]
5}
6 
7model Article {
8  id      Int    @id @default(autoincrement())
9  title   String
10  content String
11 
12  @@schema("public")
13}

Now, the migration works as expected.

1-- CreateSchema
2CREATE SCHEMA IF NOT EXISTS "user_data";
3 
4-- CreateTable
5CREATE TABLE "user_data"."Address" (
6    "id" SERIAL NOT NULL,
7    "street" TEXT NOT NULL,
8    "city" TEXT NOT NULL,
9    "country" TEXT NOT NULL,
10 
11    CONSTRAINT "Address_pkey" PRIMARY KEY ("id")
12);
13 
14-- CreateTable
15CREATE TABLE "user_data"."User" (
16    "id" SERIAL NOT NULL,
17    "email" TEXT NOT NULL,
18    "name" TEXT NOT NULL,
19    "password" TEXT NOT NULL,
20    "addressId" INTEGER,
21 
22    CONSTRAINT "User_pkey" PRIMARY KEY ("id")
23);
24 
25-- CreateIndex
26CREATE UNIQUE INDEX "User_email_key" ON "user_data"."User"("email");
27 
28-- CreateIndex
29CREATE UNIQUE INDEX "User_addressId_key" ON "user_data"."User"("addressId");
30 
31-- AddForeignKey
32ALTER TABLE "user_data"."User" ADD CONSTRAINT "User_addressId_fkey" FOREIGN KEY ("addressId") REFERENCES "user_data"."Address"("id") ON DELETE SET NULL ON UPDATE CASCADE;

Please notice that the above migration does not interact with the Article table, even though we added the @@schema attribute.

Naming the models

Whenever we interact with our models, we don’t need to provide the name of the schema they come from.

1import { Injectable } from '@nestjs/common';
2import { PrismaService } from '../database/prisma.service';
3 
4@Injectable()
5export class ArticlesService {
6  constructor(private readonly prismaService: PrismaService) {}
7 
8  getAll() {
9    return this.prismaService.article.findMany();
10  }
11 
12  // ...
13}

While convenient, all our model names must be unique, even if they come from different schemas.

One of the ways to archive some rows from a table is to create a separate table to hold the archived entities. Let’s do that, but create the new table in a separate schema.

1datasource db {
2  provider = "postgresql"
3  url      = env("DATABASE_URL")
4  schemas  = ["public", "user_data", "archive"]
5}
6 
7model Article {
8  id      Int    @id @default(autoincrement())
9  title   String
10  content String
11 
12  @@schema("public")
13}
14 
15model Article {
16  id      Int    @id @default(autoincrement())
17  title   String
18  content String
19 
20  @@schema("archive")
21}

While PostgreSQL allows us to reuse the same table name across various schemas, Prisma won’t allow us to have two models with the same name.

1npx prisma migrate dev --name add-archived-articles

12345678 Error: Prisma schema validation - (validate wasm)Error code: P1012error: The model "Article" cannot be defined because a model with that name already exists.  -->  schema.prisma:20   | 19 | 20 | model Article {   |

If we want to use the same table name in two different schemas while using Prisma, we need to come up with a different model name and use the @@map attribute to specify the table name.

1model Article {
2  id      Int    @id @default(autoincrement())
3  title   String
4  content String
5 
6  @@schema("public")
7}
8 
9model ArchivedArticle {
10  id      Int    @id @default(autoincrement())
11  title   String
12  content String
13 
14  @@schema("archive")
15  @@map("Article")
16}

Benefits of using schemas

Using multiple schemas with PostgreSQL offers a few benefits. Organizing our data into schemas can help to organize our data within the same database and make it easier to navigate the database structure. Schemas also give us better control over the access permissions in our database. We can restrict some users from interacting with a particular schema, which can be useful if we have many different users in our database.

Another benefit is that schemas can help us deal with naming conflicts. If different teams work separately on the database, they can use tables or indexes with the same names as long as they use dedicated schemas. Additionally, routine maintenance tasks such as backups can target specific schemas without affecting the entire database.

Summary

Schemas can help us manage our data in a way that increases security, efficiency, and clarity. They can be especially useful in complex or multi-user environments.

To learn how to work with them, we first interacted with our database through raw SQL queries to learn how PostgreSQL works when we don’t specify the schema explicitly. Then, we used Prisma to define additional schemas and assign models to them. Mastering schemas in PostgreSQL can make your database simpler to use and manage, especially if it grows and gets more users and tables.