3. Prisma (PostgreSQL)

Understanding PostgreSQL

Before we dive into Prisma, it's important to understand the database that powers our backend.

What is PostgreSQL

PostgreSQL is a powerful, open-source relational database system. It uses SQL (Structured Query Language) to store and manage data in tables, with strong support for data integrity, indexing, and relational structures.

It’s widely used in both small and enterprise-scale applications, and pairs perfectly with Prisma thanks to its structured nature and rich data types.

Why we chose PostgreSQL

In Finding Nibbles, we use PostgreSQL because:

• It’s open source and works well with Docker.

• It supports relational data (e.g. users with dietary preferences).

• It works seamlessly with Prisma, our ORM (Object-Relational Mapper).

Key concepts

• Table: A structured collection of data (similar to a spreadsheet).

• Row: One record in a table

• Column: A single attribute

• Primary Key: A unique identifier for each row

• Foreign Key: A reference to a row in another table

What is Prisma?

Prisma is an open-source ORM (Object-Relational Mapping) tool for Node.js and TypeScript. It makes working with databases easier and type-safe.

What Prisma gives you:

• A declarative schema to define your data models

• Automatically generated and strongly typed database client

• Support for migrations to update the DB schema

• Type safety and autocompletion in your editor (especially in NestJS)

Prisma database

In Prisma, a model is the term to describe a SQL table. Here, models will have a set of variables (column names) with types. Each variable in a model may contain a feature, a feature can be used to determine which variable is the primary key of the table, or to set default values. To see all the database features included in prisma click on the following link.

In the backend folder open the schema.prisma, which can be found under the prisma folder. At the bottom of this file add the following models, if there already exists a User model be sure to delete it:

schema.prisma

model User {
  id            Int           @id @default(autoincrement())
  name          String
  email         String        @unique
  password      String
  userDietaries UserDietary[]
  createdAt     DateTime      @default(now())
  updatedAt     DateTime      @updatedAt
}

model DietaryRestriction {
  id            Int           @id @default(autoincrement())
  name          String        @unique
  description   String?
  userDietaries UserDietary[]
  createdAt     DateTime      @default(now())
  updatedAt     DateTime      @updatedAt
}

model UserDietary {
  userId    Int
  dietaryId Int
  user      User               @relation(fields: [userId], references: [id])
  dietary   DietaryRestriction @relation(fields: [dietaryId], references: [id])

  @@id([userId, dietaryId])
}

The key features of the Schema is:

User:

• id: Primary key, auto-incremented.

• email: Must be unique.

• userDietaries: A list of dietary links (many-to-many relationship).

• DietaryRestriction Model

Dietary Requirement

• name: Must be unique.

• description: Optional (nullable).

• userDietaries: Links to users who follow this restriction.

• UserDietary Model — The Join Table

UserDietary Model - the Joing table

• userId + dietaryId: Composite primary key — each user-dietary pair must be unique.

• Defines foreign key relations to both User and DietaryRestriction.

After updating or changing the schema at any point you must run the following two useful commands to ensure both the database and your code are updated with the new changes.

Useful Commands:

• npx prisma migrate dev → any change you have made to schema.prisma gets updated

• npx prisma generate → creates type-safe DB access to use in the coding

Connecting the Prisma Client to our NestJS backend

In order to have Prisma work with our backend we need to connect the Prisma Client. To do this, in a new VS Code terminal go to the backend directory (cd backend) and run the following two commands. You will understand these commands more clearly in part 4.

nest g module database
nest g service database

These commands should create a database directory inside the src directory with three typescript files one for the database service and one for the database module and one for testing the database service. Inside of database.service, replace the file with the following code.

database.service.ts

import { Injectable, OnModuleInit } from '@nestjs/common';
import { PrismaClient } from '@prisma/client';

@Injectable()
export class DatabaseService extends PrismaClient implements 
    OnModuleInit {
        async onModuleInit() {
            await this.$connect();
        }
    }

What has happened here, is we have established a Prisma connection and done a dependency injection (created the connection without assigning an object to anything). Secondly, update the database.module file so that it exports DatabaseService. The new file should look like this:

database.module.ts

import { Module } from '@nestjs/common';
import { DatabaseService } from './database.service';

@Module({
  providers: [DatabaseService],
  exports: [DatabaseService],
})
export class DatabaseModule {}

Prisma queries

In order to interact with our PostgreSQL database, prisma has a set of inbuilt basic queries which can be used to access the required data in our Database. Prisma queries follow this structure. With a set of different filtering options.

await prisma.<model>.<method>({
    where: { ... },            // Filtering conditions
    data: { ... },             // Data for creating or updating records
    select: { ... },           // Fields to include in the results
    include: { ... },          // Include related data (for relationships)
})

In this structure, the model determines which table we will be performing extractions or alteration in and method is used to determine what type of basic database operation needs to happen. Below is a list of all the query methods for basic database operations. This link further elaborates on different queries.

Awesome work so far, you are up to date with Prisma, lets learn about NestJS in the next section.