API with NestJS #127. Arrays with PostgreSQL and Kysely


This entry is part 127 of 136 in the API with NestJS

PostgreSQL outshines various other SQL databases with its feature set. Unlike most SQL databases, PostgreSQL offers extensive support for array columns. Using them, we can store collections of values within a single column without creating separate tables. In this article, we explore the capabilities of arrays in PostgreSQL and implement examples using Kysely.

Creating the migration

In the previous parts of this series, we defined a table containing articles.


Let’s use an array instead of a simple column.


The most straightforward way of approaching the SQL migration would be to drop the column and add the column.


Above, we use the template tag, because Kysely does not understand the column type yet.

While the above approach would work, it has a significant downside. Dropping the column removes its contents, which means losing a lot of data. Instead, let’s use the values from the column in the array.


Above, we perform four steps in our migrations:

  1. we add the column,
  2. we set the first element of the array to be the value from the column
  3. we remove the column
  4. we make the column non-nullable since now all rows have a value for it.

Adjusting our models

The first step in making the above change work with our application is to adjust our model.


What’s great about the type-safety that Kysely offers is that it will let us know about all the places we need to adjust to accommodate for the array.

Thanks to that, we can change our repository to adjust the to in all appropriate places.

Creating and updating arrays

When using Kysely, inserting a new record into the table containing the array column is straightforward.

We can use the library to verify if the user provided a valid array of strings.


The most straightforward way of modifying the array is to provide a new value.

Besides providing the full value for the array, PostgreSQL allows us to use various functions. A good example is , which adds a new element at the end of the array.

On the other hand, adds a new element at the beginning of the array.

The function can remove a given number of elements from the end of the array. For example, we can use it to delete the last element of the array.

Searching through arrays

PostgreSQL allows us to search through arrays using the and keywords.

To find articles where all paragraphs equal a particular string, we need to use the operator.

To get the articles where any of the paragraphs equal a particular string, we need to use the   operator.

Another good example is filtering based on the number of elements in the array. To do that, we need the function. Let’s find all articles with at least one paragraph.

The second argument in the specifies which dimension of the array we want to measure and can be useful for multi-dimensional arrays.


In this article, we’ve gone through the idea of array columns and implemented examples using Kysely. Array columns can help store multiple related values in a list within PostgreSQL. They come with built-in functions and operators that help with various tasks.

However, arrays may not be suitable for every situation. Indexing and querying arrays might not perform well when dealing with large datasets. Creating a separate table and establishing relationships could be a better choice in such cases, especially if you need to enforce specific data rules.

It’s important to carefully assess your application’s requirements and weigh the pros and cons before deciding whether to use array columns in PostgreSQL. An extra tool is always a good idea, regardless of your choice.

Series Navigation<< API with NestJS #126. Improving the database performance with indexes and KyselyAPI with NestJS #128. Managing JSON data with PostgreSQL and Kysely >>
Notify of
Inline Feedbacks
View all comments