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

May 29, 2023

This entry is part 110 of 175 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 JSON. It stores the provided data as is in a text format.

CREATE TABLE products (

id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

name text NOT NULL,

properties json

)

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

INSERT INTO products (

name,

properties

)

VALUES (

'Introduction to Algorithms',

'{ "publicationYear": 1990, "authors": ["Thomas H. Cormen", "Charles E. Leiserson", "Ronald L. Rivest", "Clifford Stein"] }'

)

Above, we’ve added our first record to the products 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 publication_year and authors columns to our table. Since the properties column is flexible, we can use it to store any product.

INSERT INTO products (

name,

properties

)

VALUES (

'A8',

'{ "brand": "Audi", "engine": { "numberOfCylinders": 6, "fuel": "petrol" } }'

)

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

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

Defining a json column with Prisma

To add the json column with Prisma, we need the @db.Json annotation.

productSchema.prisma

model Product {

id Int @id @default(autoincrement())

name String

properties Json? @db.Json

}

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

migrations/20230523001250_products/migration.sql

-- CreateTable

CREATE TABLE "Product" (

"id" SERIAL NOT NULL,

"name" TEXT NOT NULL,

"properties" JSON,

CONSTRAINT "Product_pkey" PRIMARY KEY ("id")

);

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

1	export type InputJsonValue = string \| number \| boolean \| InputJsonObject \| InputJsonArray

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

createProduct.dto.ts

import { Prisma } from '@prisma/client';

import { IsString, IsNotEmpty, IsOptional } from 'class-validator';

import IsJsonObject from '../../utils/isJsonObject';

export class CreateProductDto {

@IsString()

@IsNotEmpty()

name: string;

@IsJsonObject()

@IsOptional()

properties?: Prisma.InputJsonObject;

}

export default CreateProductDto;

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

isJsonObject.ts

import { registerDecorator, ValidationArguments } from 'class-validator';

function IsJsonObject() {

return function (object: object, propertyName: string) {

registerDecorator({

name: 'isJsonObject',

target: object.constructor,

propertyName: propertyName,

validator: {

validate(value: unknown) {

return (

typeof value === 'object' && value !== null && !Array.isArray(value)

);

defaultMessage(validationArguments?: ValidationArguments): string {

return `${validationArguments.property} must be a valid object`;

});

};

}

export default IsJsonObject;

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

Handling null values

Our properties 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.

1	JSON.stringify(null); // 'null'

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

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

async updateProduct(id: number, product: UpdateProductDto) {

try {

return await this.prismaService.product.update({

data: {

...product,

id: undefined,

properties: product.properties ?? Prisma.DbNull,

where: {

id,

});

} catch (error) {

if (

error instanceof PrismaClientKnownRequestError &&

error.code === PrismaError.RecordDoesNotExist

) {

throw new NotFoundException();

}

throw error;

}

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.

1 2	SELECT * FROM "Product" WHERE (properties->>'publicationYear')::int < 2000

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

this.prismaService.product.findMany({

where: {

properties: {

path: ['publicationYear'],

lt: 2000,

});

We can access nested properties by providing the path array with multiple strings such as ['user', 'address', 'street']

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

export type JsonNullableFilterBase = {

equals?: InputJsonValue | JsonNullValueFilter

path?: string[]

string_contains?: string

string_starts_with?: string

string_ends_with?: string

array_contains?: InputJsonValue | null

array_starts_with?: InputJsonValue | null

array_ends_with?: InputJsonValue | null

lt?: InputJsonValue

lte?: InputJsonValue

gt?: InputJsonValue

gte?: InputJsonValue

not?: InputJsonValue | JsonNullValueFilter

}

The jsonb column

The json 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 jsonb column type, which stands for “JSON Binary”. Whenever we store data in the jsonb column, PostgreSQL parses our JSON into a binary format. While this can cause the INSERT 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 jsonb column provides, it also gives us more operators we can use. It also provides us with additional indexing capabilities.

CREATE TABLE products (

id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

name text NOT NULL,

properties json

)

To create a jsonb column with Prisma, we can use the @db.JsonB annotation.

productSchema.prisma

model Product {

id Int @id @default(autoincrement())

name String

properties Json? @db.JsonB

}

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

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

migrations/20230524030331_products/migration.sql

CREATE TABLE "Product" (

"id" SERIAL NOT NULL,

"name" TEXT NOT NULL,

"properties" JSONB,

CONSTRAINT "Product_pkey" PRIMARY KEY ("id")

);

Using the jsonb column gives us all of the functionalities of the json 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 json and jsonb 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 >>