# Prisma with NestJS

***

{% hint style="info" %}
This is a tutorial that provides an overview of using the tool. There are many ways to code the logic and organize your code. It's up to you to design your API according to your specific needs. My role is to guide you and offer a solid foundation.
{% endhint %}

### Introduction to Prisma

Prisma is an open-source database toolkit that simplifies database interactions by providing:

* **Type-safe database queries**
* **Easy-to-use migrations**
* **Auto-generated database schemas and models**
* **Excellent support for relational databases** like MySQL, PostgreSQL, SQLite, and more.

Prisma integrates seamlessly with NestJS applications, enhancing database access safety and efficiency. This guide extends the integration to handle multiple MySQL databases within a single NestJS project.

***

### Setting Up Prisma with NestJS

#### Step 1: Install Prisma CLI and Prisma Client

First, install Prisma and its necessary dependencies. Run the following commands in your project directory:

<pre class="language-bash"><code class="lang-bash"><strong>npm install prisma --save-dev
</strong>npm install @prisma/client
</code></pre>

#### Step 2: Initialize Prisma for Multiple Databases

Since you're working with multiple databases, it's recommended to create separate Prisma schemas for each database. This approach ensures clear separation and easier management.

1. **Create Separate Schema Files**

   Create two schema files: `schema_db1.prisma` and `schema_db2.prisma` inside a new `prisma` directory.

   ```bash
   mkdir prisma
   touch prisma/schema_db1.prisma prisma/schema_db2.prisma
   ```
2. **Initialize Each Schema**

   For each schema, specify the datasource and generator.

#### Step 3: Configure Database Connections

Your `.env` file is already set up to handle multiple databases. Here's how it looks:

```env
# CORE API ---------------------------------------------------------------------------------------------------

# The environment the application is running in
# Options: development, production, test
NODE_ENV=development

# The port number the application will listen on
PORT=3000

# CAPTCHA ---------------------------------------------------------------------------------------------------

# The secret key for Google reCAPTCHA verification https://www.google.com/recaptcha/about/
RECAPTCHA_SECRET_KEY=your-google-recaptcha-secret-key

# CONNECT PRISMA DATABASE -----------------------------------------------------------------------------------

# npm run prisma:migrate:db1
# npm run prisma:generate:db1

# Database connection settings
DB_HOST=localhost          # The hostname or IP address of the database server
DB_USER=user               # The username for connecting to the database
DB_PASS=pwd                # The password for the database user
DB_PORT=3306               # The port number the database server is listening on

DB_NAME_DB1=mydatabase_test
DB_NAME_DB2=database2

# URLs
DATABASE_URL_DB1="mysql://${DB_USER}:${DB_PASS}@${DB_HOST}:${DB_PORT}/${DB_NAME_DB1}"
DATABASE_URL_DB2="mysql://${DB_USER}:${DB_PASS}@${DB_HOST}:${DB_PORT}/${DB_NAME_DB2}"
```

***

### Generating Prisma Clients

#### Step 1: Define Your Data Models

Define the data models for each database in their respective schema files.

**`prisma/schema_db1.prisma`**

```prisma
datasource db1 {
  provider = "mysql"
  url      = env("DATABASE_URL_DB1")
}

generator client_db1 {
  provider = "prisma-client-js"
  output   = "../node_modules/@prisma/client_db1"
}

model User {
  id        Int      @id @default(autoincrement())
  name      String
  email     String   @unique
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}
```

**`prisma/schema_db2.prisma`**

```prisma
datasource db2 {
  provider = "mysql"
  url      = env("DATABASE_URL_DB2")
}

generator client_db2 {
  provider = "prisma-client-js"
  output   = "../node_modules/@prisma/client_db2"
}

model Product {
  id        Int      @id @default(autoincrement())
  name      String
  price     Float
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}
```

In these schema files:

* **Datasources**: Define the connection to each MySQL database using environment variables.
* **Generators**: Generate separate Prisma clients for each database, outputting them to different directories.
* **Models**: Define the `User` model for `db1` and the `Product` model for `db2`.

#### Step 2: Generate Prisma Clients

Add the following scripts to your `package.json` to handle migrations and client generation for each database:

```json
{
  "scripts": {
    "prisma:migrate:db1": "prisma migrate dev --schema=prisma/schema_db1.prisma --name init_db1",
    "prisma:migrate:db2": "prisma migrate dev --schema=prisma/schema_db2.prisma --name init_db2",
    "prisma:generate:db1": "prisma generate --schema=prisma/schema_db1.prisma",
    "prisma:generate:db2": "prisma generate --schema=prisma/schema_db2.prisma"
  }
}
```

Run the following commands to generate the Prisma clients:

```bash
npm run prisma:generate:db1
npm run prisma:generate:db2
```

This will create two separate Prisma clients:

* `@prisma/client_db1` for `db1`
* `@prisma/client_db2` for `db2`

***

### Configuring Prisma in Modules

#### Step 1: Create Prisma Services

Create separate Prisma services for each database to manage their respective Prisma clients.

**Create `src/prisma/db1.service.ts`**

```typescript
// src/prisma/db1.service.ts

import { Injectable, OnModuleInit, OnModuleDestroy } from '@nestjs/common';
import { PrismaClient_db1 } from '@prisma/client_db1';

@Injectable()
export class Db1Service extends PrismaClient_db1 implements OnModuleInit, OnModuleDestroy {
  async onModuleInit() {
    await this.$connect();
  }

  async onModuleDestroy() {
    await this.$disconnect();
  }
}
```

**Create `src/prisma/db2.service.ts`**

```typescript
// src/prisma/db2.service.ts

import { Injectable, OnModuleInit, OnModuleDestroy } from '@nestjs/common';
import { PrismaClient_db2 } from '@prisma/client_db2';

@Injectable()
export class Db2Service extends PrismaClient_db2 implements OnModuleInit, OnModuleDestroy {
  async onModuleInit() {
    await this.$connect();
  }

  async onModuleDestroy() {
    await this.$disconnect();
  }
}
```

#### Step 2: Create Prisma Modules

Create separate Prisma modules to provide the respective Prisma services.

**Create `src/prisma/db1.module.ts`**

```typescript
// src/prisma/db1.module.ts

import { Module } from '@nestjs/common';
import { Db1Service } from './db1.service';

@Module({
  providers: [Db1Service],
  exports: [Db1Service],
})
export class Db1Module {}
```

**Create `src/prisma/db2.module.ts`**

```typescript
// src/prisma/db2.module.ts

import { Module } from '@nestjs/common';
import { Db2Service } from './db2.service';

@Module({
  providers: [Db2Service],
  exports: [Db2Service],
})
export class Db2Module {}
```

**Update `src/app.module.ts` to Import Prisma Modules**

```typescript
// src/app.module.ts

import { Module } from '@nestjs/common';
import { Db1Module } from './prisma/db1.module';
import { Db2Module } from './prisma/db2.module';
import { UserModule } from './user/user.module';
import { ProductModule } from './product/product.module';

@Module({
  imports: [Db1Module, Db2Module, UserModule, ProductModule],
})
export class AppModule {}
```

***

### Running Migrations

Run migrations for each database separately using the scripts defined earlier.

**For `db1`:**

```bash
npm run prisma:migrate:db1
```

**For `db2`:**

<pre class="language-bash"><code class="lang-bash"><strong>npm run prisma:migrate:db2
</strong></code></pre>

These commands will generate and apply SQL migration files to their respective databases based on the defined schemas.

***

### Using Prisma in Services

#### Step 1: Inject Prisma Services in Your Services

Create separate services for each database, injecting the respective Prisma service.

**Create `src/user/user.service.ts` for `db1`**

```typescript
// src/user/user.service.ts

import { Injectable } from '@nestjs/common';
import { Db1Service } from '../prisma/db1.service';
import { Prisma } from '@prisma/client_db1';

@Injectable()
export class UserService {
  constructor(private db1: Db1Service) {}

  async createUser(data: { name: string; email: string }) {
    return this.db1.user.create({
      data,
    });
  }

  async findAllUsers() {
    return this.db1.user.findMany();
  }

  async findUserById(id: number) {
    return this.db1.user.findUnique({
      where: { id },
    });
  }
}
```

**Create `src/product/product.service.ts` for `db2`**

```typescript
// src/product/product.service.ts

import { Injectable } from '@nestjs/common';
import { Db2Service } from '../prisma/db2.service';
import { Prisma } from '@prisma/client_db2';

@Injectable()
export class ProductService {
  constructor(private db2: Db2Service) {}

  async createProduct(data: { name: string; price: number }) {
    return this.db2.product.create({
      data,
    });
  }

  async findAllProducts() {
    return this.db2.product.findMany();
  }

  async findProductById(id: number) {
    return this.db2.product.findUnique({
      where: { id },
    });
  }
}
```

#### Step 2: Using the Services in Controllers

Create controllers to expose API endpoints for each service.

**Create `src/user/user.controller.ts`**

```typescript
// src/user/user.controller.ts

import { Controller, Get, Post, Body, Param } from '@nestjs/common';
import { UserService } from './user.service';

@Controller('users')
export class UserController {
  constructor(private readonly userService: UserService) {}

  @Post()
  createUser(@Body() body: { name: string; email: string }) {
    return this.userService.createUser(body);
  }

  @Get()
  findAllUsers() {
    return this.userService.findAllUsers();
  }

  @Get(':id')
  findUserById(@Param('id') id: string) {
    return this.userService.findUserById(Number(id));
  }
}
```

**Create `src/product/product.controller.ts`**

```typescript
// src/product/product.controller.ts

import { Controller, Get, Post, Body, Param } from '@nestjs/common';
import { ProductService } from './product.service';

@Controller('products')
export class ProductController {
  constructor(private readonly productService: ProductService) {}

  @Post()
  createProduct(@Body() body: { name: string; price: number }) {
    return this.productService.createProduct(body);
  }

  @Get()
  findAllProducts() {
    return this.productService.findAllProducts();
  }

  @Get(':id')
  findProductById(@Param('id') id: string) {
    return this.productService.findProductById(Number(id));
  }
}
```

**Register Services and Controllers in Their Modules**

**`src/user/user.module.ts`**

```typescript
// src/user/user.module.ts

import { Module } from '@nestjs/common';
import { UserService } from './user.service';
import { UserController } from './user.controller';
import { Db1Module } from '../prisma/db1.module';

@Module({
  imports: [Db1Module],
  providers: [UserService],
  controllers: [UserController],
})
export class UserModule {}
```

**`src/product/product.module.ts`**

```typescript
// src/product/product.module.ts

import { Module } from '@nestjs/common';
import { ProductService } from './product.service';
import { ProductController } from './product.controller';
import { Db2Module } from '../prisma/db2.module';

@Module({
  imports: [Db2Module],
  providers: [ProductService],
  controllers: [ProductController],
})
export class ProductModule {}
```

Now, you have separate APIs for managing users (`db1`) and products (`db2`), each interacting with their respective databases.

***

### Handling Errors

Prisma throws specific errors when operations fail. Handle these errors gracefully in each service.

**Example: Handling Unique Constraint Error in `UserService`**

```typescript
// src/user/user.service.ts

import { Injectable, ConflictException } from '@nestjs/common';
import { Db1Service } from '../prisma/db1.service';
import { Prisma } from '@prisma/client_db1';

@Injectable()
export class UserService {
  constructor(private db1: Db1Service) {}

  async createUser(data: { name: string; email: string }) {
    try {
      return this.db1.user.create({
        data,
      });
    } catch (error) {
      if (error instanceof Prisma.PrismaClientKnownRequestError) {
        if (error.code === 'P2002') {
          throw new ConflictException('Email already exists');
        }
      }
      throw error;
    }
  }

  // ... other methods
}
```

***

Example: Handling Errors in `ProductService`

```typescript
// src/product/product.service.ts

import { Injectable, BadRequestException } from '@nestjs/common';
import { Db2Service } from '../prisma/db2.service';
import { Prisma } from '@prisma/client_db2';

@Injectable()
export class ProductService {
  constructor(private db2: Db2Service) {}

  async createProduct(data: { name: string; price: number }) {
    try {
      return this.db2.product.create({
        data,
      });
    } catch (error) {
      if (error instanceof Prisma.PrismaClientKnownRequestError) {
        // Handle specific Prisma errors if needed
        throw new BadRequestException('Failed to create product');
      }
      throw error;
    }
  }

  // ... other methods
}
```

This error handling ensures that Prisma errors are caught and meaningful HTTP exceptions are thrown, providing clear feedback to API clients.