API with NestJS #156. Arrays with PostgreSQL and the Drizzle ORM

NestJS

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

Thanks to some of its features, PostgreSQL sets itself apart from other SQL databases. Unlike many SQL databases that limit columns to single entries, PostgreSQL lets us store multiple values in a single column. This simplifies database design and can improve performance. In this article, we will explore the practical uses of arrays in PostgreSQL and show how to use them with the Drizzle ORM.

The array column

In earlier sections of this series, we created the structure for a table that holds articles.

database-schema.ts

This time, instead of a plain text column for the article’s content, let’s use an array. To do that, we need the function.

database-schema.ts

We can now use the Drizzle ORM Kit to create a migration.

Drizzle ORM Kit will ask if you want to rename the column to or create a new column from scratch. We, however, want to do something a bit more advanced since the type of our column changes from a simple string to an array of strings.

To avoid losing the data already stored in the array, let’s set it as the first element of the array and then remove the column.

0005_change-article-content-to-paragraphs.sql

Thanks to this approach, we don’t lose the data stored in our database when we run our migration.

Working with arrays using the Drizzle ORM

It’s very straightforward to insert a record into the table that contains an array.

To achieve that in a NestJS application, we need to adjust the Data Transfer Objects so that users can send arrays to our REST API.

create-article.dto.ts

Above, we use the library to ensure that the user provides a valid array of strings.

We can now adjust our service and use the property from our DTO.

articles.service.ts

Updating existing records

The most straightforward way of modifying an array is to provide a brand-new one, even if we want to change only some elements.

Aside from allowing us to set the entire array’s contents when modifying it, PostgreSQL provides various functions. However, we need to write raw SQL to achieve that.

For example, using , we can add a new element at the end of an existing array.

The tagged template is imported from the library. If you want to know more about tagged templates, check out Concatenating strings with template literals. Tagged templates

Similarly, the function adds an element to the beginning of the array.

With the function, we can remove a particular number of elements from the end of an array. For example, let’s use it to delete the last element.

Searching through arrays

With PostgreSQL, we can search through arrays with the and operators.

For example, we can find articles where all paragraphs equal a particular string using the keyword.

What might be more practical is that we can use the ANY operator to get the articles where any paragraph equals a particular string.

In addition to the above, we can use the function to filter the records based on the number of elements in the array. For example, we can find all articles with at least one paragraph.

With the second argument of the function we specify which dimension of the array we want to measure. It can be useful for multi-dimensional arrays.

Summary

In this article, we explored the use of array columns and implemented examples using the Drizzle ORM. Array columns can store multiple related values within a single column in PostgreSQL, aided by built-in functions and operators for various tasks. Unfortunately, though, arrays aren’t always the best solution.

Indexing and querying arrays can be inefficient with large datasets. In such cases, creating a separate table and defining relationships might be a better approach, especially if we want to enforce specific data constraints. Evaluating your application’s needs and weighing the advantages and disadvantages before opting for array columns in PostgreSQL is crucial. However, having an extra tool in your toolbox is always beneficial.

Series Navigation<< API with NestJS #155. Offset and keyset pagination with the Drizzle ORMAPI with NestJS #157. Handling JSON data with PostgreSQL and the Drizzle ORM >>
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments