Connecting to PostgreSQL with TypeORM or Prisma in NestJS

Connecting to a PostgreSQL database is a fundamental part of many web applications. In NestJS, there are several libraries and ORMs (Object-Relational Mappers) available to facilitate this connection and manage your data with ease. Two popular choices for interacting with PostgreSQL in NestJS are TypeORM and Prisma.

In this module, we will explore how to set up and connect a NestJS application to a PostgreSQL database using both TypeORM and Prisma. We’ll discuss the pros and cons of each, and walk through practical examples to help you get started.

Table of Contents

1. Introduction to PostgreSQL in NestJS
2. Setting Up TypeORM with NestJS
3. Setting Up Prisma with NestJS
4. Comparison of TypeORM and Prisma
5. Conclusion

Introduction to PostgreSQL in NestJS

PostgreSQL is a powerful open-source relational database system that is commonly used in production environments for applications that require complex queries, data integrity, and strong ACID compliance.

In a NestJS application, you can integrate PostgreSQL using ORMs like TypeORM or Prisma, both of which provide TypeScript support and abstraction over raw SQL queries. While TypeORM follows a more traditional ORM approach with entities and repositories, Prisma is a modern ORM that focuses on performance, developer experience, and an easy-to-use query engine.

Setting Up TypeORM with NestJS

TypeORM is one of the most popular ORMs in the NestJS ecosystem. It provides a lot of useful features like migrations, lazy loading, and easy-to-use decorators to define entities and relationships.

Step 1: Install Dependencies

To integrate TypeORM with NestJS, you need to install @nestjs/typeorm and typeorm as dependencies:

bashCopyEditnpm install @nestjs/typeorm typeorm pg

pg is the PostgreSQL driver for Node.js.

Step 2: Configure TypeORM Module

In the app.module.ts, import the TypeOrmModule and configure it to connect to your PostgreSQL database. The configuration contains the database credentials like the host, port, username, password, and the name of the database.

typescriptCopyEdit// src/app.module.ts
import { Module } from '@nestjs/common';
import { TypeOrmModule } from '@nestjs/typeorm';
import { User } from './user/user.entity';
import { UserModule } from './user/user.module';

@Module({
  imports: [
    TypeOrmModule.forRoot({
      type: 'postgres',
      host: 'localhost',  // Replace with your PostgreSQL host
      port: 5432,  // Default PostgreSQL port
      username: 'postgres',  // Replace with your PostgreSQL username
      password: 'password',  // Replace with your PostgreSQL password
      database: 'nestdb',  // Replace with your PostgreSQL database name
      entities: [User],  // List of entities (models)
      synchronize: true,  // Automatically sync the database (not recommended in production)
    }),
    UserModule,
  ],
})
export class AppModule {}

Step 3: Define an Entity

An entity represents a table in your PostgreSQL database. You can define entities using decorators provided by TypeORM. Here’s an example of a User entity:

typescriptCopyEdit// src/user/user.entity.ts
import { Entity, PrimaryGeneratedColumn, Column } from 'typeorm';

@Entity()
export class User {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  name: string;

  @Column()
  email: string;
}

Step 4: Create a Repository to Interact with the Database

In TypeORM, you interact with entities through repositories. Repositories provide methods for querying the database.

You can inject a repository into your service:

typescriptCopyEdit// src/user/user.service.ts
import { Injectable } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { Repository } from 'typeorm';
import { User } from './user.entity';

@Injectable()
export class UserService {
  constructor(
    @InjectRepository(User)
    private userRepository: Repository<User>,
  ) {}

  async createUser(name: string, email: string): Promise<User> {
    const user = this.userRepository.create({ name, email });
    return this.userRepository.save(user);
  }

  async findAll(): Promise<User[]> {
    return this.userRepository.find();
  }
}

Step 5: Run the Application

After setting up the connection and defining your entities, you can run your application:

bashCopyEditnpm run start

Your NestJS app will now be connected to your PostgreSQL database, and you can start creating and retrieving records using the TypeORM repository methods.

Setting Up Prisma with NestJS

Prisma is a modern ORM designed to simplify database interactions and improve developer experience. It generates a TypeScript-safe client and provides a powerful query engine.

Step 1: Install Dependencies

To set up Prisma in your NestJS project, you need to install the @nestjs/prisma package, Prisma itself, and the PostgreSQL driver:

bashCopyEditnpm install @nestjs/prisma prisma @prisma/client pg

Step 2: Initialize Prisma

You need to initialize Prisma and create your Prisma schema. Run the following command to generate the necessary files:

bashCopyEditnpx prisma init

This will generate a prisma folder with a schema.prisma file. Edit the schema.prisma file to define your PostgreSQL connection:

prismaCopyEdit// prisma/schema.prisma
datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL") // Use environment variable for the connection string
}

generator client {
  provider = "prisma-client-js"
}

model User {
  id    Int    @id @default(autoincrement())
  name  String
  email String @unique
}

Step 3: Configure the Database Connection

You need to define the database connection URL in your .env file:

bashCopyEditDATABASE_URL="postgresql://postgres:password@localhost:5432/nestdb?schema=public"

Replace password, localhost, and nestdb with your actual database credentials.

Step 4: Generate Prisma Client

After defining your schema, generate the Prisma client:

bashCopyEditnpx prisma generate

Step 5: Using Prisma Client in NestJS

In NestJS, you can now use Prisma’s auto-generated client to interact with your database.

First, create a Prisma service:

typescriptCopyEdit// src/prisma/prisma.service.ts
import { Injectable } from '@nestjs/common';
import { PrismaClient } from '@prisma/client';

@Injectable()
export class PrismaService extends PrismaClient {}

Then, inject the PrismaService in your service to access the database:

typescriptCopyEdit// src/user/user.service.ts
import { Injectable } from '@nestjs/common';
import { PrismaService } from '../prisma/prisma.service';

@Injectable()
export class UserService {
  constructor(private prisma: PrismaService) {}

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

  async getUsers() {
    return this.prisma.user.findMany();
  }
}

Step 6: Run the Prisma Migrations

Before running the application, run Prisma migrations to ensure that the database schema is up to date:

bashCopyEditnpx prisma migrate dev --name init

Step 7: Run the Application

You can now run your NestJS application:

bashCopyEditnpm run start

Prisma will automatically generate and provide type-safe database queries based on your schema.

Comparison of TypeORM and Prisma

Both TypeORM and Prisma are powerful tools, but they have distinct characteristics:

TypeORM is more traditional and provides a lot of flexibility with advanced ORM features, while Prisma focuses on simplicity, performance, and developer experience with a modern approach.

Conclusion

In this module, we explored how to set up and connect a NestJS application to a PostgreSQL database using TypeORM and Prisma. Both tools provide robust ways to manage databases in a NestJS application, each with its own strengths. TypeORM offers a more traditional approach with powerful ORM capabilities, while Prisma provides a modern, type-safe query engine that improves developer productivity and performance.

Depending on your project’s needs, you can choose either of these tools for integrating PostgreSQL with NestJS and ensure seamless database management and data integrity in your application.