API with NestJS #128. Managing JSON data with PostgreSQL and Kysely

NestJS SQL

This entry is part 128 of 158 in the API with NestJS

PostgreSQL is great for structured data and is known for its reliability in maintaining data organization and consistency. On the other hand, MongoDB stores data in flexible JSON-like documents, making it ideal for data with varying attributes and evolving requirements.

Using SQL has a lot of advantages over MongoDB, but we might need some flexibility in some cases. Fortunately, PostgreSQL supports columns that store JSON data. In this article, we’ll explore their benefits and features, showing how to use them to our benefit. We will implement examples using NestJS, PostgreSQL, and Kysely.

The JSON column

While we could store JSON in the database as a regular string, we would miss many features that PostgreSQL offers. Instead, let’s look into the column type.

20231006050231_add_products_table.ts

The first advantage of the column is that PostgreSQL validates if we use a valid JSON value. If we don’t, it throws an error.

Here, we’ve inserted our initial record into the products column. Being a book, it includes the publication year and a list of authors. The advantage of a JSON column is that we don’t have to include separate and columns in our table.

Thanks to the flexibility of the column, we can use it to store various types of products.

If we were dealing with just books and cars, creating distinct tables for each might have made sense. However, if we had numerous product types, managing them separately would become quite cumbersome.

Let’s validate the JSON value to ensure the user provides a valid dictionary.

product.dto.ts

It is crucial to remember that JSON values don’t have to be dictionaries. We can use the column to put regular strings, numbers, or arrays. We can accommodate that by using a broader type in our table definition.

productsTable.ts

If you want to be a bit more specific, you can use the type from the type-fest library.

More advanced queries

PostgreSQL offers various built-in operators and functions for working with JSON data, with one of the most crucial being the operator, which enables us to access object fields using keys.

Fortunately, Kysely supports the above through its expression builder. To access a particular property, we need to use the function.

To prevent TypeScript from complaining, we need to narrow down the type of our column.

We can also use the operator to access array elements.

Kysely supports that through the function.

The JSONB column

The column stores data in its original text format. It preserves the exact structure and order of elements, including whitespace. While it’s fast for data insertion, it’s not the best solution for complex queries due to the need to parse the JSON data each time.

Alternatively, we can use the column. It has all of the functionalities of the type and more but changes how PostgreSQL stores the data. When we input data to a column, the database converts it into a binary format. Although inserting the value might be slightly slower, it considerably reduces the processing time. Additionally, the format doesn’t preserve whitespace, duplicates, or the key order.

In addition to enhancing performance, the column offers a wider range of operators and extends the indexing capabilities.

20231006050231_add_products_table.ts

Summary

In this article, we’ve explored the process of storing JSON in a PostgreSQL database using the and columns through NestJS and Kysely.

We can use this approach in PostgreSQL when storing JSON data within your relational database, allowing you to work with semi-structured or loosely structured data. While the and columns offer flexibility, they come at the cost of abandoning some of the advantages of relational databases. Therefore, we should use this approach sparingly.

Series Navigation<< API with NestJS #127. Arrays with PostgreSQL and KyselyAPI with NestJS #129. Implementing soft deletes with SQL and Kysely >>
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments