TypeORM, Prisma or Plain SQL

What TypeORM, Prisma 7 and a raw pg pool each cost, and how the choice changes the shape of your Nest services.

Syntax@InjectRepository(Order) private orders: Repository<Order> // constructor(private prisma: PrismaService) {}

Three things can talk to SQL from a Nest service: TypeORM (entity classes plus an injected Repository), Prisma (a generated client wrapped in one class), or a driver such as pg that you register yourself. All three arrive through the same door. A provider is any value Nest constructs and hands to a constructor; a token is the key it is filed under (a class, a string or a symbol); a module is the unit that declares both and decides what it exports.

What each costs

TypeORM 1.1 with @nestjs/typeorm 12.0.2 gives you one repository per entity, each under its own token, so a service names exactly the tables it touches. Prisma 7 gives you one client: services depend on a single PrismaService, which is less wiring and a coarser thing to fake in tests. A raw pool gives you SQL you can paste into psql and leaves row mapping to you. NestJS 12 also documents @nestjs/drizzle, @nestjs/sequelize, @nestjs/mongoose and @mikro-orm/nestjs.

Shorthand and longhand

Every example below uses TypeScript's parameter property. These two classes are identical:

constructor(private readonly orders: Repository<Order>) {}

// longhand: declare the field, assign it yourself
private readonly orders: Repository<Order>;
constructor(orders: Repository<Order>) { this.orders = orders; }

What trips people up

The decision leaks into every service, so changing it later is a rewrite, not a config edit. And synchronize: true is for throwaway databases only: it alters live tables to match your entity classes.

Example

Example · typescript
// src/orders/orders.service.ts
import { Injectable, NotFoundException } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { Repository } from 'typeorm';
import { Order } from './order.entity.js'; // .js suffix: the ESM layout `nest new` generates for v12

@Injectable() // marks the class as a provider Nest can construct and inject
export class OrdersService {
  constructor(
    // Asks for the provider registered under the token "OrderRepository",
    // which TypeOrmModule.forFeature([Order]) created in this module.
    @InjectRepository(Order)
    private readonly orders: Repository<Order>,
  ) {}

  findPaid(customerId: number): Promise<Order[]> {
    return this.orders.find({
      where: { customerId, status: 'paid' },
      // object form; TypeORM 1.x removed the 0.3 array form relations: ['items']
      relations: { items: true },
      order: { createdAt: 'DESC' },
      skip: 0,
      take: 20, // skip and take are meant to be used together
    });
  }

  async findOne(id: number): Promise<Order> {
    // findOneBy({ id }) is shorthand for findOne({ where: { id } })
    const order = await this.orders.findOneBy({ id });
    if (!order) {
      throw new NotFoundException(`Order ${id} not found`);
    }
    return order;
  }
}

When to use it

  • Choosing a data layer for a new Nest service before the first entity is written.
  • Adding one reporting endpoint whose SQL no ORM expresses well, without dropping the ORM elsewhere.
  • Deciding what a unit test will have to fake: one client, or one repository per table.
  • Reviewing an inherited project that uses synchronize: true and needs a migration-based schema.

More examples

Prisma 7 as a single provider

Prisma has no per-entity repository, so one class extends the generated client and the connect/disconnect calls hang off Nest's lifecycle hooks. Services then depend on PrismaService itself: constructor(private prisma: PrismaService). Reach for this when you want one typed query API over all tables and are happy to mock one object in tests.

Example · typescript
// src/prisma/prisma.service.ts
import { Injectable, OnModuleDestroy, OnModuleInit } from '@nestjs/common';
import { ConfigService } from '@nestjs/config';
import { PrismaBetterSqlite3 } from '@prisma/adapter-better-sqlite3';
import { PrismaClient } from '../generated/prisma/client.js';

@Injectable()
export class PrismaService
  extends PrismaClient
  implements OnModuleInit, OnModuleDestroy
{
  constructor(configService: ConfigService) {
    const adapter = new PrismaBetterSqlite3({
      url: configService.getOrThrow<string>('DATABASE_URL'),
    });
    super({ adapter }); // Prisma 7 requires an adapter here
  }

  // Runs once, after Nest has resolved every provider in this module
  // and before the HTTP server starts listening.
  async onModuleInit() {
    await this.$connect();
  }

  // Runs on shutdown, after enableShutdownHooks() has been called in main.ts.
  async onModuleDestroy() {
    await this.$disconnect();
  }
}

Plain SQL: a pg pool as a custom provider

No ORM at all: a pool built by a factory provider, exported from one module, injected by token. Use this for reporting queries, window functions and recursive CTEs that an ORM expresses badly, or for a service small enough that entity classes are overhead. The cost is that nothing maps rows to objects for you.

Example · typescript
// src/db/db.module.ts
import { Module } from '@nestjs/common';
import { ConfigModule, ConfigService } from '@nestjs/config';
import { Pool } from 'pg';

// A token. A string or symbol is needed because `Pool` comes from a library
// and you want to control how it is built.
export const PG_POOL = 'PG_POOL';

@Module({
  imports: [ConfigModule],
  providers: [
    {
      provide: PG_POOL,
      useFactory: (config: ConfigService) =>
        new Pool({ connectionString: config.getOrThrow('DATABASE_URL'), max: 10 }),
      inject: [ConfigService], // positional arguments passed to useFactory
    },
  ],
  exports: [PG_POOL], // without this, importing modules cannot inject it
})
export class DbModule {}

// src/orders/orders.repository.ts
import { Inject, Injectable } from '@nestjs/common';
import { Pool } from 'pg';
import { PG_POOL } from '../db/db.module.js';

@Injectable()
export class OrdersRepository {
  // A string token cannot be inferred from the type, so @Inject names it.
  constructor(@Inject(PG_POOL) private readonly pool: Pool) {}

  async findPaid(customerId: number): Promise<{ id: number; total_cents: number }[]> {
    const { rows } = await this.pool.query<{ id: number; total_cents: number }>(
      `select id, total_cents
         from orders
        where customer_id = $1 and status = 'paid'
        order by created_at desc
        limit 20`,
      [customerId], // always parameterised: never string-concatenate input
    );
    return rows;
  }
}

Registering TypeORM safely for a migrated schema

forRoot (or forRootAsync) creates the single DataSource; forFeature creates the per-entity repository tokens and must be repeated in every module whose services inject them. Forgetting forFeature is the usual cause of a startup error naming OrderRepository as an unresolved dependency.

Example · typescript
// src/app.module.ts
import { Module } from '@nestjs/common';
import { ConfigModule, ConfigService } from '@nestjs/config';
import { TypeOrmModule } from '@nestjs/typeorm';
import { OrdersModule } from './orders/orders.module.js';

@Module({
  imports: [
    ConfigModule.forRoot({ isGlobal: true }),
    // forRootAsync waits for ConfigService before building the DataSource.
    TypeOrmModule.forRootAsync({
      imports: [ConfigModule],
      inject: [ConfigService],
      useFactory: (config: ConfigService) => ({
        type: 'postgres' as const,
        url: config.getOrThrow<string>('DATABASE_URL'),
        autoLoadEntities: true, // picks up every entity passed to forFeature()
        synchronize: false,     // schema changes come from migrations only
        logging: ['error', 'schema'],
        maxQueryExecutionTime: 300, // log any query slower than 300ms
      }),
    }),
    OrdersModule,
  ],
})
export class AppModule {}

// src/orders/orders.module.ts
import { Module } from '@nestjs/common';
import { TypeOrmModule } from '@nestjs/typeorm';
import { Order } from './order.entity.js';
import { OrdersService } from './orders.service.js';

@Module({
  // forFeature registers one repository provider per entity, in THIS module
  imports: [TypeOrmModule.forFeature([Order])],
  providers: [OrdersService],
  exports: [OrdersService],
})
export class OrdersModule {}

Related

nest typeorm intronest prisma intronest repositorynest custom providersnest query performance

Discussion

  • Be the first to comment on this lesson.