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

NestJS SQL

This entry is part 140 of 177 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 out of the box for every new database. Let’s say we have the following model.

schema.prisma

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

migration.sql

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

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

Determining the schema to use

This is controlled through the 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 . The refers to the current user’s name that we can check through the variable.

Therefore, in our case means that PostgreSQL first tries to look for the table in the schema, then in the schema.

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

We could change the default schema by modifying the variable.

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

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

Creating new schemas

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

schema.prisma

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

schema.prisma

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

schema.prisma

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.

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

schema.prisma

Now, the migration works as expected.

migration.sql

Please notice that the above migration does not interact with the table, even though we added the 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

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

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.

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 attribute to specify the table name.

schema.prisma

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 >>
Subscribe
Notify of
guest
1 Comment
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Joao
Joao
10 months ago

That’s how I’m building a multi-tenancy app: by using a different schema for each client.