Drop SqLite DB between tests NestJs, TypeOrm

Question

I have the following integration test written in nestJs with jest:

import { Test, TestingModule } from '@nestjs/testing';
import { getRepositoryToken, TypeOrmModule } from '@nestjs/typeorm';
import { Repository } from 'typeorm';
import { RefreshTokenEntity } from '../refresh-token/refresh-token.entity';
import { UserEntity } from './user.entity';
import { UsersService } from './users.service';

describe('UsersService', () => {
  let service: UsersService;
  let userRepository: Repository;
  let module: TestingModule;

  beforeAll(async () => {
    module = await Test.createTestingModule({
      imports: [
        TypeOrmModule.forRootAsync({
          useFactory: () => ({
            type: 'better-sqlite3',
            name: 'test-db-sqlite',
            database: ':memory:',
            dropSchema: true,
            entities: [UserEntity, RefreshTokenEntity],
            synchronize: true,
          }),
        }),
        TypeOrmModule.forFeature([UserEntity]),
      ],
      providers: [UsersService],
    }).compile();

    service = module.get(UsersService);
    userRepository = module.get>(
      getRepositoryToken(UserEntity),
    );
  });

  it('should find all users', async () => {
    const user1: UserEntity = new UserEntity();
    user1.username = 'test-user';
    user1.password = 'password';
    user1.isActive = true;
    await userRepository.save(user1);

    const users: UserEntity[] = await service.findAll();
    expect(users).toHaveLength(1); // pass
  });

  it('should find no users', async () => {
    const users: UserEntity[] = await service.findAll();
    expect(users).toHaveLength(0); // fail, user is still saved from previous test
  });
});

Between each test, I want to revert the in-memory DB back to a completely clean state, I've tried doing this via userRepository.query("DROP table users"); this works, but it won't work for future tests where foreign keys are involved.

Thanks in advance!

user_1254208 · Answer

Generally I've used 3 strategies for this sort of thing:

Delete the data you created in your test.

I use this strategy when I want the database to keep working for other reasons. In a cleanup step identify the test data and delete it. This needs to be done in order because, as you point out, foreign keys. I have a core object (device) and when they're created by the test there's an identifier (device.name like 'UnitTest%') - and I delete all dependent records then all device` records based on that identifier.

Drop all the tables in reverse order

As you're happy to drop the tables, you can do that. You just need to do them in order so you don't get foreign key violations.

Wholesale delete / copy over the database file.

This might be harder as you'll need to ensure there are no active database connections. But Sqlite is a file based database, so if there are no active connections then you can zap the file. Either delete it entirely (which means you need to be able to create it again) or have an empty one somewhere available to your test suite and copy it in.

user_13635165 · Answer

Ok so I have found a solution that works for my purposes based on this comment: https://github.com/nestjs/mongoose/issues/167#issuecomment-636396149

  TypeOrmModule.forRootAsync({
    useFactory: () => ({
      type: 'better-sqlite3',
      name: (new Date().getTime() * Math.random()).toString(16),
      database: ':memory:',
      dropSchema: true,
      entities: [UserEntity, RefreshTokenEntity],
      synchronize: true,
    }),
  }),

Essentially I am just opening a new connection before each test with a unique name, I believe this means that the old connections are still open until the test runner finishes. which could be problematic if I need to seed a lot of data at some point.

Drop SqLite DB between tests NestJs, TypeOrm

2 Answers