API with NestJS #127. Arrays with PostgreSQL and Kysely

October 2, 2023

This entry is part 127 of 180 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.

articlesTable.ts

import { Generated } from 'kysely';

export interface ArticlesTable {

id: Generated<number>;

title: string;

article_content: string;

author_id: number;

}

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

articlesTable.ts

import { Generated } from 'kysely';

export interface ArticlesTable {

id: Generated<number>;

title: string;

paragraphs: string[];

author_id: number;

}

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

20230928231458_add_paragraphs_column.ts

import { Kysely, sql } from 'kysely';

export async function up(database: Kysely<unknown>): Promise<void> {

await database.schema

.alterTable('articles')

.dropColumn('article_content')

.addColumn('paragraphs', sql`text[]`, (column) => column.notNull())

.execute();

}

export async function down(database: Kysely<unknown>): Promise<void> {

await database.schema

.alterTable('articles')

.dropColumn('paragraphs')

.addColumn('article_content', 'text', (column) => column.notNull());

}

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

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

20230928231458_add_paragraphs_column.ts

import { Migration, sql } from 'kysely';

export const up: Migration['up'] = async (database) => {

await database.schema

.alterTable('articles')

.addColumn('paragraphs', sql`text[]`)

.execute();

await database

.updateTable('articles')

.set({

paragraphs: sql`ARRAY[article_content]`,

})

.execute();

await database.schema

.alterTable('articles')

.dropColumn('article_content')

.execute();

await database.schema

.alterTable('articles')

.alterColumn('paragraphs', (column) => {

return column.setNotNull();

})

.execute();

};

export const down: Migration['down'] = async (database) => {

await database.schema

.alterTable('articles')

.dropColumn('paragraphs')

.addColumn('article_content', 'text', (column) => column.notNull());

};

Above, we perform four steps in our migrations:

we add the paragraphs column,
we set the first element of the paragraphs array to be the value from the article_content column
we remove the article_content column
we make the paragraphs 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 Article model.

article.model.ts

export interface ArticleModelData {

id: number;

title: string;

paragraphs: string[];

author_id: number;

}

export class Article {

id: number;

title: string;

paragraphs: string[];

authorId: number;

constructor({ id, title, paragraphs, author_id }: ArticleModelData) {

this.id = id;

this.title = title;

this.paragraphs = paragraphs;

this.authorId = author_id;

}

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 paragraphs array.

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

Creating and updating arrays

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

const databaseResponse = await this.database

.insertInto('articles')

.values({

title: 'My article',

paragraphs: [

'First paragraph',

'Second paragraph'

author_id: 1

})

.returningAll()

.executeTakeFirstOrThrow();

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

article.dto.ts

import { IsString, IsNotEmpty, IsOptional, IsNumber } from 'class-validator';

export class ArticleDto {

@IsString()

@IsNotEmpty()

title: string;

@IsString({ each: true })

@IsNotEmpty({ each: true })

paragraphs: string[];

@IsOptional()

@IsNumber({}, { each: true })

categoryIds?: number[];

}

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

const databaseResponse = await database

.updateTable('articles')

.set({

title: 'My article',

paragraphs: [

'My new first paragraph',

'Second paragraph',

})

.where('id', '=', 1)

.returningAll()

.executeTakeFirst();

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

const databaseResponse = await this.database

.updateTable('articles')

.set(({ ref }) => ({

paragraphs: sql`array_append(${ref('paragraphs')}, ${'Last paragraph'})`,

}))

.where('id', '=', 1)

.returningAll()

.executeTakeFirst();

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

const databaseResponse = await this.database

.updateTable('articles')

.set(({ ref }) => ({

paragraphs: sql`array_prepend(${ref('paragraphs')}, ${'First paragraph'})`,

}))

.where('id', '=', 1)

.returningAll()

.executeTakeFirst();

The trim_array 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 paragraphs array.

const databaseResponse = await this.database

.updateTable('articles')

.set(({ ref }) => ({

paragraphs: sql`trim_array(${ref('paragraphs')}, 1)`,

}))

.where('id', '=', 1)

.returningAll()

.executeTakeFirst();

Searching through arrays

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

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

const databaseResponse = await this.database

.selectFrom('articles')

.where(

sql`${'Lorem ipsum'}`,

'=',

({ ref }) => sql`ALL(${ref('paragraphs')})`,

)

.selectAll()

.execute();

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

const databaseResponse = await this.database

.selectFrom('articles')

.where(

sql`${'Lorem ipsum'}`,

'=',

({ ref }) => sql`ANY(${ref('paragraphs')})`,

)

.selectAll()

.execute();

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

const databaseResponse = await this.database

.selectFrom('articles')

.where(({ ref }) => sql`array_length(${ref('paragraphs')}, 1)`, '>', 0)

.selectAll()

.execute();

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

Summary

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 >>