Test a NestJS Drizzle CRUD service

Test a NestJS Drizzle CRUD service against real SQL in memory with PGlite, or unit test it with TestCrudFactory and a Jest mock database.

To test a NestJS Drizzle CRUD service, boot it through DrizzleCrudModule against PGlite, an in-memory Postgres, so every filter, soft delete and constraint runs as real SQL with no server or container. For narrow unit tests, the package also exports TestCrudFactory, which builds the service on a Jest mock database that runs no SQL, and BaseCrudSpecHelper, a base class around it.

A mock cannot check your SQL

The mock database accepts any query, so it cannot catch a wrong column key, a bad cast or a constraint violation. The package added its own PGlite suite after mocked tests let real bugs through. Use the mock for logic in your service, and PGlite for anything that touches the database.

How do I test against a real database?

Install PGlite and the Nest testing module

pnpm add @electric-sql/pglite @nestjs/testing -D

Create the tables in memory

Each test gets its own database, so tests never depend on each other's rows:

test/db.ts
import { PGlite } from '@electric-sql/pglite';
import { drizzle } from 'drizzle-orm/pglite';

export async function freshDb() {
  const client = await PGlite.create();
  await client.exec(`
    CREATE TABLE users (
      id serial PRIMARY KEY,
      name varchar(100) NOT NULL,
      email varchar(255) NOT NULL UNIQUE,
      created_at timestamp NOT NULL DEFAULT now(),
      updated_at timestamp NOT NULL DEFAULT now(),
      deleted_at timestamp
    );
  `);
  return { client, db: drizzle(client) };
}

If you use drizzle-kit, you can run your migrations against the client instead of writing the DDL by hand.

Build the service through the module

Register the service the way your app does, with forRoot and forFeature, so the test also covers the config merge:

users.service.spec.ts
import { Test, type TestingModule } from '@nestjs/testing';
import type { PGlite } from '@electric-sql/pglite';
import { DrizzleCrudModule, EntityNotFoundException } from '@avytrix/nestjs-drizzle-crud';
import { users } from '../src/db/schema';
import { UsersService } from '../src/users/users.service';
import { freshDb } from './db';

describe('UsersService', () => {
  let client: PGlite;
  let moduleRef: TestingModule;
  let service: UsersService;

  beforeEach(async () => {
    const fresh = await freshDb();
    client = fresh.client;
    moduleRef = await Test.createTestingModule({
      imports: [
        DrizzleCrudModule.forRoot({ dialect: 'postgresql', db: fresh.db }),
        DrizzleCrudModule.forFeature([{ service: UsersService, table: users }]),
      ],
    }).compile();
    service = moduleRef.get(UsersService);
  });

  afterEach(async () => {
    await moduleRef.close();
    await client.close();
  });

  it('creates and finds a user', async () => {
    const user = await service.create({ name: 'Ada', email: 'ada@example.com' });
    const found = await service.find(user.id);
    expect(found?.email).toBe('ada@example.com');
  });

  it('hides soft-deleted rows from findAll', async () => {
    const user = await service.create({ name: 'Ada', email: 'ada@example.com' });
    await service.softDelete(user.id);
    const { total } = await service.findAll({});
    expect(total).toBe(0);
  });

  it('throws EntityNotFoundException when updating a missing row', async () => {
    await expect(service.update(999, { name: 'X' })).rejects.toBeInstanceOf(
      EntityNotFoundException,
    );
  });
});

The module does not close a db you pass in, so close the PGlite client yourself in afterEach.

What does the package export for tests?

Two classes, both from nestjs-drizzle-crud (src/test-utils). Both call the global jest, so they only work inside Jest.

ExportMethodReturns
TestCrudFactorycreateMockDb()A stub db whose select, insert, update, delete, transaction and chain methods (from, where, limit, offset, orderBy, values, set, returning) are jest.fn(). Runs no SQL.
createMockTable()A plain object with the keys id, name, email, created_at, updated_at, deleted_at. Takes no arguments.
createMockEntity(overrides?)A row with id: 1, a name, an email and both timestamps, merged with your overrides.
createTestService(Service, db, table, config?)A service built directly, without Nest DI, with the config below.
BaseCrudSpecHelperconstructor and scenario methodsA base class that builds a service with TestCrudFactory and returns canned find, create, update, delete and findAll scenarios that stub the mock db.

createTestService builds this config, then spreads your config over it:

dialectOptional

"postgresql" | "mysql" | "sqlite"

The dialect the service compiles for.

Defaults to "postgresql"

primaryKeyOptional

string

The key of the primary key column.

Defaults to "id"

softDeleteOptional

{ enabled, column }

Soft delete, on the snake_case key the mock table has.

Defaults to { enabled: true, column: "deleted_at" }

timestampsOptional

{ createdAt, updatedAt }

Timestamp keys. Set undefined to turn them off.

Defaults to { createdAt: "created_at", updatedAt: "updated_at" }

paginationOptional

{ defaultLimit, maxLimit }

Page size and cap.

Defaults to { defaultLimit: 20, maxLimit: 100 }

sqlOptional

object

SQL flags. Writes read the row back with RETURNING.

Defaults to { caseSensitive: false, useReturning: true, ... }

The spread is shallow: passing softDelete: { column: 'deletedAt' } replaces the whole object, so also pass enabled: true.

How do I unit test with the mock database?

Stub the chain the method calls, then assert on the result and the calls. create runs db.insert(table).values(row).returning():

users.service.unit.spec.ts
import { TestCrudFactory } from '@avytrix/nestjs-drizzle-crud';
import { UsersService } from '../src/users/users.service';

it('returns the inserted row', async () => {
  const mockDb = TestCrudFactory.createMockDb();
  const row = TestCrudFactory.createMockEntity({ name: 'Ada' });
  mockDb.insert.mockReturnValue({
    values: jest.fn().mockReturnThis(),
    returning: jest.fn().mockResolvedValue([row]),
  });

  const service = TestCrudFactory.createTestService(
    UsersService,
    mockDb,
    TestCrudFactory.createMockTable(),
  );

  await expect(service.create({ name: 'Ada', email: 'test@example.com' })).resolves.toEqual(row);
  expect(mockDb.insert).toHaveBeenCalledTimes(1);
});

You can pass your real Drizzle table instead of the mock table. Then set softDelete and timestamps to its keys, or turn them off: the service checks them when it is built and throws if one is missing. With a real table, logic that reads column types works without a database, for example service.idKind() and service.parseId('12').

How do I test hooks?

Hooks are protected methods on the service. Spy on one and run the method that calls it:

users.service.spec.ts
it('passes the input to beforeCreate', async () => {
  const spy = jest.spyOn(service as any, 'beforeCreate');
  await service.create({ name: 'Ada', email: 'ada@example.com' });
  expect(spy).toHaveBeenCalledWith({ name: 'Ada', email: 'ada@example.com' });
});

Or override the hook in a test-only subclass and register that subclass in forFeature:

test/test-users.service.ts
import type { CreateUserDto } from '../src/users/dto/create-user.dto';
import { UsersService } from '../src/users/users.service';

export class TestUsersService extends UsersService {
  protected async beforeCreate(data: CreateUserDto) {
    return { ...data, name: data.name.trim() };
  }
}

How do I check the merged config?

The service keeps its merged config in the protected config property. After building it through the module, read it in a test:

users.service.spec.ts
expect((service as any).config.softDelete).toEqual({ enabled: true, column: 'deleted_at' });

Common questions

Next steps

Last updated View change history

On this page