Prisma with NestJS

We'll configure your environment, perform migrations for each database, and interact with them using Prisma's powerful query engine by creating separate Prisma services

---

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.

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:

npm install prisma --save-dev
npm install @prisma/client

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.

   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:

# 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

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

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:

{
  "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:

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

// 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

// 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

// 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

// 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

// 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:

npm run prisma:migrate:db1

For db2:

npm run prisma:migrate:db2

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

// 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

// 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

// 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

// 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

// 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

// 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

// 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

// 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.