API with NestJS #110. Managing JSON data with PostgreSQL and Prisma

NestJS SQL

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

Relational databases such as PostgreSQL are great for storing structured data. This approach has many advantages, but it can lack flexibility. On the other hand, databases such as MongoDB that store JSON-like documents might give you more flexibility than you would like. Fortunately, PostgreSQL offers support for storing and querying loosely-structured JSON data. This article explores the features and benefits of using the JSON and JSONB columns.

Using the json column type

The first column type to look into is . It stores the provided data as is in a text format.

When we insert data, PostgreSQL ensures that it is a valid JSON. If not, it throws an error.

Above, we’ve added our first record to the column. Since it is a book, it has the publication year and a list of authors. Thanks to using a JSON column, we don’t need to add the and columns to our table. Since the column is flexible, we can use it to store any product.

When working with PostgreSQL, we can make parts of our data flexible while putting constraints on other columns. For example, we could add the column to group our products.

If we would only have two types of products, it would make sense to create separate and tables. However, it would get complicated if we had tens of different product types.

Defining a json column with Prisma

To add the column with Prisma, we need the annotation.

productSchema.prisma

Creating the above schema and generating a migration yields the following result:

migrations/20230523001250_products/migration.sql

It’s crucial to acknowledge that when inserting values into a json column, Prisma expects us to provide the data matching the interface. When we look under the hood of Prisma, we can see that it includes any valid JSON value.

Since we want to be an object, we need to enforce the interface built into Prisma.

createProduct.dto.ts

To achieve the above with the library, we need to create a custom decorator that checks if the provided value is a valid object.

isJsonObject.ts

Thanks to all of the above, we can now insert JSON data through an API created with NestJS.

Handling null values

Our column is nullable and does not need to contain any value. Unfortunately, this can cause confusion because the JSON format can store a null value.

To differentiate between null as a lack of value in the database and as a null value stored in the column, Prisma introduced the and constants.

Let’s assume that whenever the user sends as the value for our column, they want to remove the data from the column altogether. To achieve that, we need .

Manipulating the JSON data

PostgreSQL is equipped with quite a few different operators that help us query JSON data. One of the most important ones is , which allows us to get a value of a particular property as text.

We can achieve the same thing with Prisma thanks to the option.

We can access nested properties by providing the array with multiple strings such as

Prisma offers a wide variety of filters besides the filter, which means “less than”.

The jsonb column

The column type offers fast insertion because the data we store in the database is stored as text. But, unfortunately, PostgreSQL has to parse the value whenever we perform operations on it, and it causes a performance hit.

To deal with the above issue, PostgreSQL introduced the column type, which stands for “JSON Binary”. Whenever we store data in the column, PostgreSQL parses our JSON into a binary format. While this can cause the operations to be slightly slower, it significantly improves the querying performance. It also doesn’t preserve whitespaces and the order of properties.

Besides the performance improvements the column provides, it also gives us more operators we can use. It also provides us with additional indexing capabilities.

To create a column with Prisma, we can use the annotation.

productSchema.prisma

Since is the default column used for storing JSON in Prisma we can omit using the annotation.

Creating the above schema and generating the migration gives us the following code:

migrations/20230524030331_products/migration.sql

Using the column gives us all of the functionalities of the type and more.

Summary

In this article, we’ve gone through storing JSON in a PostgreSQL database through raw SQL and Prisma. We also compared the and column types. While the above approach gives us a lot of flexibility, it forces us to give up some of the benefits of relational databases. Therefore, we should avoid overusing it and think twice before choosing it as our solution.

Series Navigation<< API with NestJS #109. Arrays with PostgreSQL and PrismaAPI with NestJS #111. Constraints with PostgreSQL and Prisma >>
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments