# Database access and testing

> Source: https://learn-nestjs.com/database-and-testing/
> Part of Learn NestJS, free to read.

## A database provider

```ts src/database/prisma.service.ts
import { Injectable, OnModuleInit } from '@nestjs/common';
import { PrismaClient } from '@prisma/client';

@Injectable()
export class PrismaService extends PrismaClient implements OnModuleInit {
  async onModuleInit() {
    await this.$connect();
  }
}
```

```ts src/database/database.module.ts
@Global()
@Module({ providers: [PrismaService], exports: [PrismaService] })
export class DatabaseModule {}
```

`onModuleInit` is one of Nest's lifecycle hooks. `onModuleDestroy` is the matching one for cleanup, and Nest calls it on shutdown if you enable shutdown hooks (`app.enableShutdownHooks()`), which you should — otherwise connections leak on redeploy.

## Keep queries out of the controller

```ts src/users/users.service.ts
@Injectable()
export class UsersService {
  constructor(private readonly prisma: PrismaService) {}

  findAll(limit: number) {
    return this.prisma.user.findMany({ take: limit, orderBy: { createdAt: 'desc' } });
  }

  async findOne(id: string) {
    const user = await this.prisma.user.findUnique({ where: { id } });
    if (!user) throw new NotFoundException(`User ${id} not found`);
    return user;
  }
}
```

Throwing `NotFoundException` from the service is idiomatic: Nest's exception filter turns it into a 404 with a JSON body, and the controller stays a one-liner.

## Unit tests

The whole value of dependency injection shows up here.

```ts src/users/users.service.spec.ts
describe('UsersService', () => {
  let service: UsersService;
  const prisma = { user: { findUnique: jest.fn(), findMany: jest.fn() } };

  beforeEach(async () => {
    const module = await Test.createTestingModule({
      providers: [UsersService, { provide: PrismaService, useValue: prisma }],
    }).compile();
    service = module.get(UsersService);
  });

  it('throws when the user does not exist', async () => {
    prisma.user.findUnique.mockResolvedValue(null);
    await expect(service.findOne('missing')).rejects.toThrow(NotFoundException);
  });
});
```

No database, no HTTP, milliseconds. This is the suite your agent should be able to run after every edit — see [the verification loop](https://learn-python.com/ai/feedback-loops/) for why speed here matters so much.

## End-to-end tests

```ts test/users.e2e-spec.ts
describe('Users (e2e)', () => {
  let app: INestApplication;

  beforeAll(async () => {
    const moduleRef = await Test.createTestingModule({
      imports: [AppModule],
    })
      .overrideProvider(MailerService).useValue({ send: jest.fn() })
      .compile();

    app = moduleRef.createNestApplication();
    app.useGlobalPipes(new ValidationPipe({ whitelist: true, transform: true }));
    await app.init();
  });

  afterAll(() => app.close());

  it('rejects an invalid email', () =>
    request(app.getHttpServer())
      .post('/users')
      .send({ email: 'nope', name: 'Ada' })
      .expect(400));
});
```

:::warn Replicate your global pipes in tests
`useGlobalPipes` is called in `main.ts`, which the testing module does not run. If you forget to add it in the test setup, validation is off and your tests pass on payloads production would reject. This catches almost everyone once.
:::

`overrideProvider` is the mechanism that makes end-to-end tests practical: swap the mailer, the payment gateway and the queue for fakes, keep everything else real.

## Transactions in tests

The fastest reliable pattern for integration tests is a transaction per test, rolled back afterwards. It gives you a real database with no cleanup and no cross-test pollution — and it is fast enough to stay in the loop.

## Common questions

### Prisma or TypeORM?

Prisma has better type inference and a clearer migration story; TypeORM fits better if you want the ActiveRecord/decorator style throughout or need something Prisma does not support. Either works well with Nest. Do not use both.

### Should I add a repository layer on top of the ORM?

Only if you have a real reason — swapping the data store, or a domain model that differs substantially from your tables. Otherwise it is indirection with no payoff, and the ORM's types are better than the ones you will hand-write.

### How do I keep end-to-end tests fast?

Reuse one application instance across the file, run each test in a transaction that is rolled back, and mock anything crossing the network. Most slow Nest suites are slow because they build a new application per test.