Arrays with PostgreSQL and Prisma

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.

1model Post {
2  id         Int        @id @default(autoincrement())
3  title      String
4  content    String
5  author     User       @relation(fields: [authorId], references: [id])
6  authorId   Int
7  categories Category[]
8 
9  scheduledDate DateTime? @db.Timestamptz
10 
11  @@index([authorId])
12}

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

1model Post {
2  id         Int        @id @default(autoincrement())
3  title      String
4  paragraphs String[]
5  author     User       @relation(fields: [authorId], references: [id])
6  authorId   Int
7  categories Category[]
8 
9  scheduledDate DateTime? @db.Timestamptz
10 
11  @@index([authorId])
12}

Now, we can generate a migration with Prisma.

1npx prisma migrate dev --name post_paragraphs

Doing the above creates the following file:

1/*
2  Warnings:
3 
4  - You are about to drop the column `content` on the `Post` table. All the data in the column will be lost.
5 
6*/
7-- AlterTable
8ALTER TABLE "Post" DROP COLUMN "content",
9ADD COLUMN "paragraphs" TEXT[];

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

1ALTER TABLE "Post"
2ADD COLUMN "paragraphs" TEXT[];
3 
4UPDATE "Post"
5SET paragraphs = ARRAY[content];
6 
7ALTER TABLE "Post"
8DROP COLUMN content;

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

• adding the paragraphs column
• settings its first element to be the value of the content column
• removing the content column.

Working with the arrays

When creating an array, we can use the ARRAY keyword.

1INSERT INTO "Post"(
2  title,
3  paragraphs,
4  "authorId"
5) VALUES (
6  'Hello world!',
7  ARRAY['Lorem ipsum', 'Dolor sit amet'],
8  1
9)

Instead, we can also use the curly braces notation.

1INSERT INTO "Post"(
2  title,
3  paragraphs,
4  "authorId"
5) VALUES (
6  'Hello world!',
7  '{"Lorem ipsum", "Dolor sit amet"}',
8  1
9)

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

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

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

1import {
2  IsString,
3  IsNotEmpty,
4  IsNumber,
5  IsOptional,
6  IsISO8601,
7} from 'class-validator';
8 
9export class CreatePostDto {
10  @IsString()
11  @IsNotEmpty()
12  title: string;
13 
14  @IsString({ each: true })
15  @IsNotEmpty()
16  paragraphs: string[];
17 
18  @IsOptional()
19  @IsNumber({}, { each: true })
20  categoryIds?: number[];
21 
22  @IsISO8601({
23    strict: true,
24  })
25  @IsOptional()
26  scheduledDate?: string;
27}

Modifying arrays

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

1UPDATE "Post"
2SET paragraphs = ARRAY['Lorem ipsum']
3WHERE id = 1

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

1async updatePost(id: number, post: UpdatePostDto) {
2  try {
3    return await this.prismaService.post.update({
4      data: {
5        ...post,
6        id: undefined,
7      },
8      where: {
9        id,
10      },
11    });
12  } catch (error) {
13    if (
14      error instanceof PrismaClientKnownRequestError &&
15      error.code === PrismaError.RecordDoesNotExist
16    ) {
17      throw new PostNotFoundException(id);
18    }
19    throw error;
20  }
21}

Another possible solution is to update a single element.

1UPDATE "Post"
2SET paragraphs[1] = ARRAY['First element']
3WHERE id = 1

Please notice that to affect the first element of the array we use paragraphs[1], not paragraphs[0].

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.

1UPDATE "Post"
2SET paragraphs[2:3] = ARRAY['Second element', 'Third element']
3WHERE id = 1

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

1UPDATE "Post"
2SET paragraphs = array_append(paragraphs, 'Fourth element')
3WHERE id = 1

Prisma supports it through the push keyword.

1await this.prismaService.post.update({
2  where: {
3    id: 1,
4  },
5  data: {
6    paragraphs: {
7      push: 'New element',
8    },
9  },
10});

Searching through arrays

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

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

1SELECT * FROM "Post"
2WHERE 'Lorem ipsum' = ALL(paragraphs)

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

1SELECT * FROM "Post"
2WHERE 'Lorem ipsum' = ANY(paragraphs)

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.

1await this.prismaService.post.findMany({
2  where: {
3    paragraphs: {
4      has: 'Lorem ipsum',
5    },
6  },
7});

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

1await this.prismaService.post.findMany({
2  where: {
3    paragraphs: {
4      hasEvery: ['First element', 'Second element'],
5    },
6  },
7});

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

1await this.prismaService.post.findMany({
2  where: {
3    paragraphs: {
4      hasSome: ['First element', 'Second element'],
5    },
6  },
7});

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

1await this.prismaService.post.findMany({
2  where: {
3    paragraphs: {
4      equals: ['Lorem ipsum'],
5    },
6  },
7});

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.