Integration testing with PostgreSQL, Drizzle and Vitest

For a new TypeScript project, the author needed to devise a method for integration testing. It was agreed that PostgreSQL, Drizzle, and Vitest would be used as the technologies. The challenge was to set up a database for the tests to run on. The author found an article on testing Node.js and PostgreSQL applications with Drizzle and PGlite, but was unsatisfied with the performance of PGlite.

The article also mentioned Testcontainers but discouraged it due to the overhead of creating throwaway containers. The author then came across an idea of creating throwaway databases in a running PostgreSQL instance, which they followed.

Four approaches were implemented for this purpose:

1. Using PGlite for creating a database for each test file

2. Creating throwaway databases in a running PostgreSQL instance using three different methods

3. Using Testcontainers (discouraged)

4. Using Pglite (preferred)

The author implemented the POCs in a GitHub repository and added unit tests to distinguish them from integration tests. The integration tests ensure that the database is correctly cleaned up between tests. The four approaches are implemented in the repository's ddd folder and branches int-tests-*.

General notes on the usage of Vitest and Drizzle include the use of setupFiles and globalSetup, as well as the use of a function from drizzle-kit to push the schema programmatically. The tests include both integration and unit tests, and ensure that the database is properly cleaned up after each test.