API with NestJS #140. Using multiple PostgreSQL schemas with Prisma – Beragampengetahuan
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
|
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.
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.
By default, it contains
“$user”, public. The
“$user” refers to the current user’s name that we can check through the
current_user variable.
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
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.
|
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
— 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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
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.
rencana pengembangan website
metode pengembangan website
jelaskan beberapa rencana untuk pengembangan website, proses pengembangan website, kekuatan dan kelemahan bisnis pengembangan website
, jasa pengembangan website, tahap pengembangan website, biaya pengembangan website
#API #NestJS #multiple #PostgreSQL #schemas #Prisma
