API with NestJS #109. Arrays with PostgreSQL and Prisma

NestJS SQL

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

PostgreSQL stands out as a feature-reach solution among other relational databases. Most of the column types available in PostgreSQL allow storing a single value. However, PostgreSQL, unlike most SQL databases, enables us to define columns as arrays. With them, we can store collections of values within a single column, reducing the need to create separate tables. This can help us achieve better performance and more efficient storage. In this article, we learn how to manage arrays through raw SQL and Prisma.

Adding the array column

In the previous parts of this series, we’ve defined the schema of a table containing posts.

postSchema.prisma

Let’s use an array instead of the simple property.

postSchema.prisma

Now, we can generate a migration with Prisma.

Doing the above creates the following file:

20230517205144_post_paragraphs/migration.sql

We need to pay close attention to a significant warning on top of our migration. Since we completely dropped the column, we would experience data loss. So instead, let’s set the value as the first element of the array.

20230517205144_post_paragraphs/migration.sql

Above, we fix the problem by performing the following three steps:

  1. adding the column
  2. settings its first element to be the value of the column
  3. removing the column.

Working with the arrays

When creating an array, we can use the keyword.

Instead, we can also use the curly braces notation.

Notice that we surround the curly braces with single quites and used the double quotes for strings.

The keyword seems easier to read, and because of that, I use it more frequently in this article.

Let’s use the library to verify if the user provided a valid array of strings.

createPost.dto.ts

Modifying arrays

The most straightforward way of modifying an existing array is to replace it as a whole.

This is the operation we need in our application since the users will provide the new value for the array through the API.

Another possible solution is to update a single element.

Please notice that to affect the first element of the array we use , not .

We can also modify a slice of an array. For example, in the code below, we update the second and the third element of the array while leaving the first element untouched.

Another popular case is to push a new element at the end of the array. To do that, we can use the function.

Prisma supports it through the keyword.

Searching through arrays

To search through arrays, we can use the and keywords.

To find a post where all paragraphs equal , we can use the operator.

To find an element where any element equals , we can use the keyword.

Prisma has multiple ways of searching through arrays too. For example, we can get a list of entities where a given value exists in the array.

We can also look for rows where every provided value exists in the array.

Prisma also allows us to check if the array contains at least one of the provided values.

The most strict operator we can use is the function, which looks for arrays that match the given value exactly.

Summary

The array columns might come in handy when we need to store multiple related values when the data fits in a list but doesn’t necessarily deserve a separate table. PostgreSQL is equipped with a variety of built-in functions and operators designed to work with arrays that allow for filtering, searching, or aggregating values.

However, arrays do not fit every use case. For example, the performance of indexing and querying arrays might not fit your needs when dealing with large data sets. Also, creating a separate table and creating a relation might be a better solution if we need to enforce various constraints on our data.

Carefully evaluate the requirements of your application and consider the pros and cons when deciding whether to use the array columns in PostgreSQL. Whatever you decide, it’s always good to have an additional tool in your toolbox.

Series Navigation<< API with NestJS #108. Date and time with Prisma and PostgreSQLAPI with NestJS #110. Managing JSON data with PostgreSQL and Prisma >>
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments