API with NestJS #157. Handling JSON data with PostgreSQL and the Drizzle ORM

NestJS

This entry is part 157 of 173 in the API with NestJS

PostgreSQL is well-suited for handling structured data and keeping everything organized and consistent. On the other hand, MongoDB stores data in flexible JSON-like documents, making it ideal for data with varying attributes and changing requirements.

While SQL databases have many advantages, there are times when flexibility is needed. Fortunately, PostgreSQL bridges this gap by allowing the storage and querying of loosely structured JSON data. In this article, we will examine the benefits and features of these JSON columns and demonstrate how to use them effectively. We’ll provide examples using NestJS, PostgreSQL, and the Drizzle ORM.

The JSON data type

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

database-schema.ts

Now, we need to create a migration using the Drizzle Kit.

If you want to know more about migrations with the Drizzle ORM, check out API with NestJS #149. Introduction to the Drizzle ORM with PostgreSQL

The last step is to run our migration to create the table.

One of the most important benefits of the JSON column is that PostgreSQL verifies the data format for us. If the JSON is not valid, it throws an error. To prevent that, let’s use the library.

database-schema.ts

The JSON format can also store strings, numbers, booleans, and arrays. However, thanks to using the decorator, we can narrow it down to the type.

Creating new records in the database that use a JSON column is very straightforward with the Drizzle ORM.

products.service.ts

With the flexibility of the JSON column, we can store different types of products.

Above, we’ve inserted our first record into the products column. Since it’s a book, it includes the publication year and a list of authors. The advantage of using a JSON column is that we don’t need to create separate and columns in our table. However, we can still take advantage of the column being a regular text data type. Thanks to that, we could use various SQL features such as constraints.

If you want to know more about constraints with the Drizzle ORM, check out API with NestJS #152. SQL constraints with the Drizzle ORM

Creating separate tables for each might still be practical if we only deal with books and cars. However, managing separate tables would become quite a chore with a wide variety of product types.

More advanced queries

PostgreSQL provides many built-in operators and functions for handling JSON data. One of the most important is the  operator, which allows us to access object fields using keys.

Thankfully, we can implement the above with Drizzle ORM using raw SQL.

We can use the operator to access array elements as well.

It also makes sense to use operators like the one above when filtering. For example, let’s write a query that returns only Audi.

It’s important to notice that the operator accesses the data in the JSON format. To get it in a string format above, we use the operator instead.

The JSONB column

PostgreSQL offers two types of columns for handling JSON data: and . The column stores data in its original text format, preserving the exact structure and order of elements, including whitespace. This allows for fast data insertion but requires parsing the JSON data each time a query is made, which can slow down complex queries.

On the other hand, the column type changes how PostgreSQL stores the data. When we put the data into our database into a column, it converts it into a binary format. It may make the insertion slightly slower but significantly reduces query processing time. Additionally, does not preserve whitespace, duplicates, or the order of keys.

In addition to performance improvements, the column offers more operators and additional indexing capabilities.

Using with the Drizzle ORM is as simple as using the function.

database-schema.ts

Summary

In this article, we’ve explored how to store JSON in a PostgreSQL database. We’ve done that using the JSON and JSONB columns with NestJS and the Drizzle ORM. With this approach, we can work with semi-structured or loosely structured data within SQL databases.

However, while JSON and JSONB columns offer flexibility, they require sacrificing some benefits of relational databases. Therefore, it’s important to use this method sparingly and consider it carefully before making the choice.

Series Navigation<< API with NestJS #156. Arrays with PostgreSQL and the Drizzle ORMAPI with NestJS #158. Soft deletes with the Drizzle ORM >>
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments