# 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**](https://www.postgresql.org/) 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](https://www.prisma.io/docs/orm/reference/database-features#constraints).

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: 

{% code title="schema.prisma" %}

```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])
}
```

{% endcode %}

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](https://www.prisma.io/docs/orm/prisma-client/setup-and-configuration/databases-connections/connection-management) 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. 

<pre class="language-typescript" data-title="database.service.ts"><code class="lang-typescript"><strong>import { Injectable, OnModuleInit } from '@nestjs/common';
</strong>import { PrismaClient } from '@prisma/client';

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

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:

{% code title="database.module.ts" %}

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

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

```

{% endcode %}

### Prisma queries&#x20;

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](https://www.prisma.io/docs/orm/prisma-client/queries/filtering-and-sorting#filtering) options.

```typescript
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)
})
```

&#x20;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](https://www.prisma.io/docs/orm/prisma-client/queries/crud) further elaborates on different queries.  &#x20;

<figure><img src="https://lh7-rt.googleusercontent.com/docsz/AD_4nXfWJHl9T3McvunQbz5K4exIcZ9Kk3BwEUzuF4xWITn3r6TXMOX7P8bC3EjNmb6TfECTelvBkxTya_CewgcHa0m7XSv3G_TUnohHlFoE3DWk46l-FeMlRZU7FOfnVi9ewPnHcXizkA?key=FZqmpJRXxopvSMqIOKJzAPlV" alt=""><figcaption></figcaption></figure>

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