Using multiple PostgreSQL schemas with Prisma

January 8, 2024

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.

schema.prisma#

model Article {
  id      Int    @id @default(autoincrement())
  title   String
  content String
}

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

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

migration.sql#

-- CreateTable
CREATE TABLE "Article" (
    "id" SERIAL NOT NULL,
    "title" TEXT NOT NULL,
    "content" TEXT NOT NULL,
 
    CONSTRAINT "Article_pkey" PRIMARY KEY ("id")
);

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.

SELECT * 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.

SHOW 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.

SELECT 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.

SET search_path TO another_schema_name;

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

SET 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.

SELECT * FROM public."Article";

Creating new schemas#

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

schema.prisma#

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["multiSchema"]
}

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

schema.prisma#

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
  schemas  = ["user_data"]
}

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

schema.prisma#

model Address {
  id      Int    @id @default(autoincrement())
  street  String
  city    String
  country String
  user    User?
 
  @@schema("user_data")
}
 
model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String
  password  String
  address   Address? @relation(fields: [addressId], references: [id])
  addressId Int?     @unique
 
  @@schema("user_data")
}

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

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

Error: Prisma schema validation - (validate wasm)
Error code: P1012
error: 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   String
15 |   content String
16 | }

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.

schema.prisma#

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
  schemas  = ["public", "user_data"]
}
 
model Article {
  id      Int    @id @default(autoincrement())
  title   String
  content String
 
  @@schema("public")
}

Now, the migration works as expected.

migration.sql#

-- CreateSchema
CREATE SCHEMA IF NOT EXISTS "user_data";
 
-- CreateTable
CREATE TABLE "user_data"."Address" (
    "id" SERIAL NOT NULL,
    "street" TEXT NOT NULL,
    "city" TEXT NOT NULL,
    "country" TEXT NOT NULL,
 
    CONSTRAINT "Address_pkey" PRIMARY KEY ("id")
);
 
-- CreateTable
CREATE TABLE "user_data"."User" (
    "id" SERIAL NOT NULL,
    "email" TEXT NOT NULL,
    "name" TEXT NOT NULL,
    "password" TEXT NOT NULL,
    "addressId" INTEGER,
 
    CONSTRAINT "User_pkey" PRIMARY KEY ("id")
);
 
-- CreateIndex
CREATE UNIQUE INDEX "User_email_key" ON "user_data"."User"("email");
 
-- CreateIndex
CREATE UNIQUE INDEX "User_addressId_key" ON "user_data"."User"("addressId");
 
-- AddForeignKey
ALTER 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.

articles.service.ts#

import { Injectable } from "@nestjs/common"
import { PrismaService } from "../database/prisma.service"
@Injectable()
export class ArticlesService {
  constructor(private readonly prismaService: PrismaService) {}
  getAll() {
    return this.prismaService.article.findMany()
  } // ...
}

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.

migration.sql#

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
  schemas  = ["public", "user_data", "archive"]
}
 
model Article {
  id      Int    @id @default(autoincrement())
  title   String
  content String
 
  @@schema("public")
}
 
model Article {
  id      Int    @id @default(autoincrement())
  title   String
  content String
 
  @@schema("archive")
}

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.

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

Error: Prisma schema validation - (validate wasm)
Error code: P1012
error: 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.

schema.prisma#

model Article {
  id      Int    @id @default(autoincrement())
  title   String
  content String
 
  @@schema("public")
}
 
model ArchivedArticle {
  id      Int    @id @default(autoincrement())
  title   String
  content String
 
  @@schema("archive")
  @@map("Article")
}

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.