API with NestJS #140. Using multiple PostgreSQL schemas with Prisma

January 8, 2024

This entry is part 140 of 180 in the API with NestJS

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.

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

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

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

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

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

1	SET search_path TO another_schema_name;

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

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

1	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")

}

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.

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

1
2
3
4
5
6
7
8
9
10
11

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.

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

1
2
3
4
5
6
7
8

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.

Series Navigation<< API with NestJS #139. Using UUID as primary keys with Prisma and PostgreSQLAPI with NestJS #141. Getting distinct records with Prisma and PostgreSQL >>