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. postSchema.prisma model Post { id Int @id @default(autoincrement()) title String content String author User @relation(fields: [authorId], references: [id]) authorId Int categories Category[] scheduledDate DateTime? @db.Timestamptz @@index([authorId]) }Let’s use an array instead of the simple content property. postSchema.prisma model Post { id Int @id @default(autoincrement()) title String paragraphs String[] author User @relation(fields: [authorId], references: [id]) authorId Int categories Category[] scheduledDate DateTime? @db.Timestamptz @@index([authorId]) }Now, we can generate a migration with Prisma.npx prisma migrate dev --name post_paragraphsDoing the above creates the following file: 20230517205144_post_paragraphs/migration.sql /* Warnings: - You are about to drop the column `content` on the `Post` table. All the data in the column will be lost. */ -- AlterTable ALTER TABLE "Post" DROP COLUMN "content", ADD 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. 20230517205144_post_paragraphs/migration.sql ALTER TABLE "Post" ADD COLUMN "paragraphs" TEXT[]; UPDATE "Post" SET paragraphs = ARRAY[content]; ALTER TABLE "Post" DROP 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.INSERT INTO "Post"( title, paragraphs, "authorId" ) VALUES ( 'Hello world!', ARRAY['Lorem ipsum', 'Dolor sit amet'], 1 )Instead, we can also use the curly braces notation.INSERT INTO "Post"( title, paragraphs, "authorId" ) VALUES ( 'Hello world!', '{"Lorem ipsum", "Dolor sit amet"}', 1 ) 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. createPost.dto.ts import { IsString, IsNotEmpty, IsNumber, IsOptional, IsISO8601, } from 'class-validator'; export class CreatePostDto { @IsString() @IsNotEmpty() title: string; @IsString({ each: true }) @IsNotEmpty() paragraphs: string[]; @IsOptional() @IsNumber({}, { each: true }) categoryIds?: number[]; @IsISO8601({ strict: true, }) @IsOptional() scheduledDate?: string; } Modifying arrays The most straightforward way of modifying an existing array is to replace it as a whole.UPDATE "Post" SET paragraphs = ARRAY['Lorem ipsum'] WHERE id = 1This is the operation we need in our application since the users will provide the new value for the array through the API.async updatePost(id: number, post: UpdatePostDto) { try { return await this.prismaService.post.update({ data: { ...post, id: undefined, }, where: { id, }, }); } catch (error) { if ( error instanceof PrismaClientKnownRequestError && error.code === PrismaError.RecordDoesNotExist ) { throw new PostNotFoundException(id); } throw error; } }Another possible solution is to update a single element.UPDATE "Post" SET paragraphs[1] = ARRAY['First element'] WHERE 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.UPDATE "Post" SET paragraphs[2:3] = ARRAY['Second element', 'Third element'] WHERE id = 1Another popular case is to push a new element at the end of the array. To do that, we can use the array_append function.UPDATE "Post" SET paragraphs = array_append(paragraphs, 'Fourth element') WHERE id = 1Prisma supports it through the push keyword.await this.prismaService.post.update({ where: { id: 1, }, data: { paragraphs: { push: 'New element', }, }, }); 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.SELECT * FROM "Post" WHERE 'Lorem ipsum' = ALL(paragraphs)To find an element where any element equals Lorem ipsum, we can use the ANY keyword.SELECT * FROM "Post" WHERE '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.await this.prismaService.post.findMany({ where: { paragraphs: { has: 'Lorem ipsum', }, }, });We can also look for rows where every provided value exists in the array.await this.prismaService.post.findMany({ where: { paragraphs: { hasEvery: ['First element', 'Second element'], }, }, });Prisma also allows us to check if the array contains at least one of the provided values.await this.prismaService.post.findMany({ where: { paragraphs: { hasSome: ['First element', 'Second element'], }, }, });The most strict operator we can use is the equals function, which looks for arrays that match the given value exactly.await this.prismaService.post.findMany({ where: { paragraphs: { equals: ['Lorem ipsum'], }, }, }); 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. The post API with NestJS #109. Arrays with PostgreSQL and Prisma appeared first on Marcin Wanago Blog - JavaScript, both frontend and backend.