API with NestJS #22. Storing JSON with PostgreSQL and TypeORM

JavaScript NestJS TypeScript

This entry is part 22 of 37 in the API with NestJS

So far, in this series, we’ve used PostgreSQL to store data structured in columns. This approach has many benefits, but sometimes, we want some more flexibility, though.

Chances are you already know JSON and use it in your web applications. There are databases like MongoDB that store JSON-like documents. With this approach, we can describe any data structures with ease. Using MongoDB has its pros and cons, and we might still want to use an SQL database instead. Some parts of our schema might be fluid and change frequently, though. Fortunately, PostgreSQL has tools to deal with it.

Using a json column with PostgreSQL

In theory, we can store JSON as a regular string. We would miss on a ton of features that Postgres provides to help us work with JSON.

The first column type we want to look into is . It stores the exact copy of the text we put in. When we use the data, Postgres has to reparse it on each execution.

The type also preserves the order of keys, duplicates, and whitespace characters.

Creating tables with the json type

Let’s create a few tables and use the type.

Above, we can see that our table aside from json has other columns also. Even though some parts of our database might benefit from a flexible approach, other places might still use a more traditional approach.

Inserting JSON data

When inserting data, Postgres makes sure that it is formatted properly. If it is not, we can expect an error.

Above, we’ve added our first product. Since it is a book, it might have properties such as and . Without using JSON, we would have to add those as additional columns of the table.

Thanks to the fact that our column is flexible, we can use it to manage any type of product.

If we would have only books and cars, it might have been a good idea to create separate and tables. If we had tens or hundreds of types of products, it would have been quite a hassle, though.

Manipulating JSON data

Postgres has quite a few operators and functions built-in that handle JSON data. The most important is the operator that allows us to get object fields by key.

We can also use the operator to access array elements.

The jsonb column

There is a drawback of using the above operator with a column. Unfortunately, Postgres has to parse the data on each execution.

With PostgreSQL, we can also use the column. When we put values in, the database parses our data into a binary format. While it might be a bit slower when inserting, it significantly reduces the processing time. The format also doesn’t preserve whitespace, duplicates, and the order of keys.

Doing that gives us all of the functionalities of the type and more. Aside from the performance improvements when querying data, we also get more operators.

Another significant feature is creating indexes for our JSON data.

Above, we use the operator to convert the values to . Thanks to that, it can be used for indexing.

If you want to know more about indexes with PostgreSQL, check out API with NestJS #14. Improving performance of our Postgres database with indexes

Using the jsonb type with TypeORM

The official Postgres documentation encourages the use of the format in most cases. With TypeORM, it is very straightforward to create a column.

product.entity.ts

Above, we create a union between and .

carProperties.interface.ts

bookProperties.interface.ts

Creating such an entity allows us to start inserting the data into our database. From the API perspective, do not differ much from other columns.

Although the database does not check if our properties match any of the above interfaces, it would be a good idea to validate it. One of the possible approaches would be to save information about the fields in the category. When the user inserts a product, we would then check what fields should a product of that category contain.

Using more advanced queries with TypeORM

While TypeORM might not support all of the features that and columns provide, we can work around it. Fortunately, we can use bare SQL queries with TypeORM.

To do the above, we need to know some of the JSON operators that Postgres supports.

If we need to use parameters in our query and we worry about SQL injection, we can create a parameterized query.

Summary

In this article, we’ve explored the idea of storing JSON within a PostgreSQL database. We’ve done that both through SQL queries and TypeORM. While it is a flexible solution, it is not always fitting. It has some drawbacks, such as slower queries and higher disk usage. Knowing how it works will help us decide if it is a valid approach to the issue that we want to solve.

Series Navigation<< API with NestJS #21. An introduction to CQRSAPI with NestJS #23. Implementing in-memory cache to increase the performance >>
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments