TypeOrm: clear database when testing

[jselesan]

Hi!. Nice work with the TypeOrmModule, I really enjoy it.

I'm wondering how can I clear the database (or even drop it) before each test, in order to isolate tests from pevious tests and previous runs

[jgordor]

Hi @jselesan this is the class I use, calling await reloadFixtures() before each jest test (has a simple json fixture loading system):

import { Component } from '@nestjs/common';
import { Connection, Repository } from 'typeorm';
import { DatabaseService } from './../database/database.service';

import * as fs from 'fs';

@Component()
export class TestUtils {
  databaseService: DatabaseService;

  constructor(databaseService: DatabaseService) {
    if (process.env.NODE_ENV !== 'test') {
      throw new Error('ERROR-TEST-UTILS-ONLY-FOR-TESTS');
    }
    this.databaseService = databaseService;
  }

  async shutdownServer(server) {
    await server.httpServer.close();
    await this.closeDbConnection();
  }

  async closeDbConnection() {
    const connection = (await this.databaseService.connection);
    if (connection.isConnected) {
      await (await this.databaseService.connection).close();
    }
  }

  async getEntities() {
    const entities = [];
    (await (await this.databaseService.connection).entityMetadatas).forEach(
      x => entities.push({name: x.name, tableName: x.tableName})
    );
    return entities;
  }

  async reloadFixtures() {
    const entities = await this.getEntities();
    await this.cleanAll(entities);
    await this.loadAll(entities);
  }

  async cleanAll(entities) {
    try {
      for (const entity of entities) {
        const repository = await this.databaseService.getRepository(entity.name);
        await repository.query(`TRUNCATE TABLE \`${entity.tableName}\`;`);
      }
    } catch (error) {
      throw new Error(`ERROR: Cleaning test db: ${error}`);
    }
  }

  async loadAll(entities) {
    try {
      for (const entity of entities) {
        const repository = await this.databaseService.getRepository(entity.name);
        const fixtureFile = `src/test/fixtures/${entity.name}.json`;
        if (fs.existsSync(fixtureFile)) {
          const items = JSON.parse(fs.readFileSync(fixtureFile, 'utf8'));
          await repository
            .createQueryBuilder(entity.name)
            .insert()
            .values(items)
            .execute();
        }
      }
    } catch (error) {
      throw new Error(`ERROR [TestUtils.loadAll()]: Loading fixtures on test db: ${error}`);
    }
  }

}

Best

[kamilmysliwiec]

Hi @jselesan,
It depends on what actually test framework you use to run your tests. For those kind of questions you should rather use stackoverflow instead of github 🙂 Here's a nestjs tag https://stackoverflow.com/questions/tagged/nestjs

[otroboe]

@jgordor I'm curious what your DatabaseService looks like :-P

[BrunnerLivio]

Nice approach @jgordor !

@otroboe after hours and hours of googling, I finally found a solution.

The class src/test/test.utils.ts:

import { Component } from '@nestjs/common';
import { Connection, Repository } from 'typeorm';
import { DatabaseService } from '../database/database.service';

import * as Path from 'path';
import * as fs from 'fs';

/**
 * This class is used to support database
 * tests with unit tests in NestJS.
 * 
 * This class is inspired by https://github.com/jgordor
 * https://github.com/nestjs/nest/issues/409#issuecomment-364639051
 */
@Component()
export class TestUtils {
  databaseService: DatabaseService;

  /**
   * Creates an instance of TestUtils
   */
  constructor(databaseService: DatabaseService) {
    if (process.env.NODE_ENV !== 'test') {
      throw new Error('ERROR-TEST-UTILS-ONLY-FOR-TESTS');
    }
    this.databaseService = databaseService;
  }

  /**
   * Shutdown the http server
   * and close database connections
   */
  async shutdownServer(server) {
    await server.httpServer.close();
    await this.closeDbConnection();
  }

  /**
   * Closes the database connections
   */
  async closeDbConnection() {
    const connection = (await this.databaseService.connection);
    if (connection.isConnected) {
      await (await this.databaseService.connection).close();
    }
  }

  /**
   * Returns the entites of the database
   */
  async getEntities() {
    const entities = [];
    (await (await this.databaseService.connection).entityMetadatas).forEach(
      x => entities.push({name: x.name, tableName: x.tableName})
    );
    return entities;
  }

  /**
   * Cleans the database and reloads the entries
   */
  async reloadFixtures() {
    const entities = await this.getEntities();
    await this.cleanAll(entities);
    await this.loadAll(entities);
  }

  /**
   * Cleans all the entities
   */
  async cleanAll(entities) {
    try {
      for (const entity of entities) {
        const repository = await this.databaseService.getRepository(entity.name);
        await repository.query(`DELETE FROM ${entity.tableName};`);
      }
    } catch (error) {
      throw new Error(`ERROR: Cleaning test db: ${error}`);
    }
  }

  /**
   * Insert the data from the src/test/fixtures folder
   */
  async loadAll(entities) {
    try {
      for (const entity of entities) {
        const repository = await this.databaseService.getRepository(entity.name);
        const fixtureFile = Path.join(__dirname, `../test/fixtures/${entity.name}.json`);
        if (fs.existsSync(fixtureFile)) {
          const items = JSON.parse(fs.readFileSync(fixtureFile, 'utf8'));
          await repository
            .createQueryBuilder(entity.name)
            .insert()
            .values(items)
            .execute();
        }
      }
    } catch (error) {
      throw new Error(`ERROR [TestUtils.loadAll()]: Loading fixtures on test db: ${error}`);
    }
  }
}

My src/test/testing.module.ts

import { Module } from '@nestjs/common';
import { TypeOrmModule } from '@nestjs/typeorm';
import { Connection } from 'typeorm';
import { DatabaseModule } from '../database/database.module';

@Module({
  imports: [
    DatabaseModule
  ],
})
export class TestingModule {
  constructor() {
  }
}

My src/database/database.module

import { Module } from '@nestjs/common';
import { TypeOrmModule } from '@nestjs/typeorm';
import { Connection } from 'typeorm';
import { getOrmConfig } from './database-ormconfig.constant';
import { DatabaseService } from './database.service';

@Module({
  imports: [
    TypeOrmModule.forRoot(getOrmConfig())
  ],
  components: [
    DatabaseService,
  ]
})
export class DatabaseModule {
  constructor() {
  }
}

My src/database/database.service.ts

import { Component, Inject } from '@nestjs/common';
import { Connection, Repository } from 'typeorm';

@Component()
export class DatabaseService {
    constructor(@Inject('Connection') public connection: Connection) { }

    async getRepository<T>(entity): Promise<Repository<T>> {
        return this.connection.getRepository(entity);
    }
}

A test case:

describe('ImageRepository', () => {
    let imageRepository: ImageRepository;
    let connection: Connection;
    let testUtils: TestUtils;
    beforeEach(async (done) => {
        const module = await Test.createTestingModule({
            imports : [
                DatabaseModule
            ],
            components: [
                DatabaseService,
                ImageRepositoryProvider,
                TestUtils
            ]
        }).compile();
        testUtils = module.get<TestUtils>(TestUtils);
        await testUtils.reloadFixtures();
        imageRepository = testUtils.databaseService.connection.getCustomRepository(ImageRepository);
        done();
    });
    
    afterEach(async done => {
        await testUtils.closeDbConnection();
        done();
    });

Sorry for the huge dump. I probably should create a repo for that, but don't have time at the moment for that.

@kamilmysliwiec can NestJs maybe provide something like the test.utils-class from @jgordor ?

[otroboe]

@BrunnerLivio Thank you for sharing your solution. I would have use that If I had this back then ;-)

But I'm sure it will help a bunch of people 👍

[krivochenko]

Hi everyone! Thanks for great solution. I have one problem. Typeorm doesn't include primary key in INSERT statement. So after every reloading of database I have new IDs. How I can resolve it?

[otroboe]

@krivochenko Why do you need sames IDs for your tests ? I think it's not a good approach for e2e tests.

If you want an automatic ID in your table/document, use @PrimaryGeneratedColumn().

[krivochenko]

@otroboe I already have automatic ID. I wanna define it by my self for some entity, which ID used as foreign key in another table. For now I have null-value in columns restricted by foreign key.

[otroboe]

@krivochenko You should open an issue on stack-overflow or come on Gitter to ask ;-)

[BrunnerLivio]

@krivochenko

Simply use `.query``

async cleanAll(entities) {
    try {
      for (const entity of entities.sort((a, b) => b.order - a.order)) {
        const repository = await this.databaseService.getRepository(entity.name);
        await repository.query(`DELETE FROM ${entity.tableName};`);
        // Reset IDs
        await repository.query(`DELETE FROM sqlite_sequence WHERE name='${entity.tableName}'`);
      }
    } catch (error) {
      throw new Error(`ERROR: Cleaning test db: ${error}`);
    }
  }

EDIT:

Created a repository, which shows the approach in my recent project.

https://github.com/BrunnerLivio/nestjs-unit-test-db-example

[krivochenko]

I resolved it by following approach:

1. I hard-coded set of entities. Because I need load data with foreign keys in defined order.
2. I changed loadAll():

  async loadAll(entities) {
    try {
      for (const entity of entities) {
        const repository = await this.databaseService.getRepository(entity.name);
        const fixtureFile = Path.join(__dirname, `../test/fixtures/${entity.name}.json`);
        if (fs.existsSync(fixtureFile)) {
          const items = JSON.parse(fs.readFileSync(fixtureFile, 'utf8'));
          const columns = Object.keys(items[0]);
          await repository
            .createQueryBuilder(entity.name)
            .insert()
            .into(entity.name, columns)
            .values(items)
            .execute();
        }
      }
    } catch (error) {
      throw new Error(`ERROR [TestUtils.loadAll()]: Loading fixtures on test db: ${error}`);
    }
  }

In last release of typeorm there is a bug, which I fixed by PR: typeorm/typeorm#1836

[0xHexE]

Hey I used this method for the clearing the database.
When you are starting the server just add.

// You can get the connection from the app or you can use the `getConnection` from `typeorm`
await getConnection().synchronize(true); // Here the true is for `dropBeforeSync`

[biels]

TypeORM needs better documentation. I may have missed it, but await getConnection().synchronize(true); can't be found there AFAIK.

[BrunnerLivio]

@biels The problem is, synchronize is part of typeorm, not NestJS. You can look it up here.

[lukeautry]

If anyone (like me) is looking for a really simple way to do this in the future, here you go:

const clearDb = async () => {
  const entities = connection.entityMetadatas;

  for (const entity of entities) {
    const repository = await connection.getRepository(entity.name);
    await repository.query(`DELETE FROM ${entity.tableName};`);
  }
};

[0xHexE]

Hey @lukeautry

If we skip synchronize like this

@Entity({ skipSync: true  })
export class Entity {  }

then this can cause problem because this table is not get created so need to check table is exists or not.

[obotor]

Hey!
Working with tests, you do not really need to perform any kind of persistence. You should use a fresh database file. So why not use simplicity?:
if (fs.existsSync(fixtureFile)) {
fs.unlinkSync(fixtureFile)
}
That way, no need to perform queries on all database entities and sequences...
Cheers.

[rhlsthrm]

Just FYI, I tried one of the solutions mentioned above:

const clearDb = async () => {
  const entities = connection.entityMetadatas;

  for (const entity of entities) {
    const repository = await connection.getRepository(entity.name);
    await repository.query(`DELETE FROM ${entity.tableName};`);
  }
};

This worked until it ran into an issue where it tried to delete over a foreign key constraint, and it won't work unless you cascade, which I don't want to modify the schema to do.

The other fix mentioned worked great though:

await connection.synchronize(true)

[Nargonath]

Based on @jgordor example you can TRUNCATE TABLE ... CASCADE

[lock]

This thread has been automatically locked since there has not been any recent activity after it was closed. Please open a new issue for related bugs.