Changing the Prisma Database URL During Test Execution

Introduction

In a previous article, I implemented a process to create separate Prisma schema files for testing and application purposes, allowing tests to run without mocking PrismaClient.
However, several issues arose in practice.
First, until I got used to the workflow, there were instances where I defined tables in the wrong schema file, causing them not to be properly reflected in the database.
Next, for some reason, even when the application database URL was set correctly, some projects would incorrectly load the test database.
The cause of this remains unknown and has become quite a critical issue.
As shown, splitting schema files into multiple parts still had its challenges.
Therefore, this time, I made it possible to switch between PrismaService for testing and application use using a single schema file by directly overwriting the database URL when running tests with Jest.
This setup is simpler than the previous one, and once configured, you rarely need to touch the file again, eliminating the need to worry about missing settings or operational rules for file locations.
Note that this discussion assumes a NestJS environment.
While it isn't strictly limited to NestJS, please keep this context in mind.
Also, I have only confirmed the operation in an Ubuntu environment.
Therefore, please be aware that I cannot guarantee it will work in other environments.

The highlight of this article!!!

Install dotenv-cli.
Add the following process to the scripts in your project's package.json.

"test": "expot DATABASE_URL=$(dotenv -- bash -c 'echo $DATABASE_TEST_URL')  && prisma db push && jest "

By doing this, you can connect to the test database.

Environment Setup

Before getting to the main topic, let's prepare so that tests can be executed.

Preparing the Database

Based on this article, use docker-compose.yaml to create databases for testing and application purposes.

Preparing PrismaService and the Schema File

Once you have created your project based on the NestJS Quickstart, define the following environment variable in your .env file.

DATABASE_URL = "postgresql://postgres:postgres@db:5432/postgres?schema=public";

Then, complete the steps up to "Model your data in the Prisma schema" from the Prisma Quickstart and apply the database definition using npx prisma db push.
After that, access the user table in Prisma Studio by running npx prisma studio, click "Add Record" at the top of the screen, and register some sample data.
Now the data is saved in the application database.
Once completed, create the following prisma.service.ts and prisma.module.ts files at any location under the src directory of the project.

prisma.service.ts

import { Injectable } from "@nestjs/common";
import { PrismaClient } from "@prisma/client";
@Injectable()
export class PrismaService extends PrismaClient {}

prisma.module.ts

import { Module } from "@nestjs/common";
import { PrismaService } from "./prisma.service";
@Module({
  providers: [PrismaService],
  exports: [PrismaService],
})
export class PrismaModule {}

This completes the preparation on the Prisma side.
The amount of work has decreased significantly compared to before.

Preparing the Test Environment

In fact, NestJS already has Jest configured to a certain extent by the time the project is set up.
Therefore, the preparation for testing is completed just by creating a test file like the following:

import { Test, TestingModule } from "@nestjs/testing";
import { AppController } from "./app.controller";
import { PrismaService } from "./prisma/prisma.service";

describe("AppController", () => {
  let appController: AppController;
  beforeEach(async () => {
    const app: TestingModule = await Test.createTestingModule({
      controllers: [AppController],
      providers: [PrismaService],
    }).compile();
    appController = app.get<AppController>(AppController);
  });
  it("ユーザーを取得できること", async () => {
    const result = await appController.getUsers();
    expect(result).toHaveLength(1);
  });
});

I will leave the details of the test syntax to the documentation, but to explain briefly, you call the necessary controllers and services with Test.createTestingModule.
Then, you retrieve the class containing the method you want to test using the get method.
After that, within functions like it or test that represent test cases, you actually call the method and use expect to compare the results with the expected outcome.
By the way, the getUsers method contains the following process, and this test is designed to pass once the application database is up and running.

async getUsers() {
    return await this.prismaService.user.findMany()
  }

Now that the preparation for testing is done, we will make it so that the PrismaService connected to the test database is used only when running tests.

Switching the Database Used During Test Execution

PrismaService identifies the database it connects to based on the value of the DATABASE_URL set in the environment variables.
If that's the case, it seems we can achieve our goal by overwriting DATABASE_URL when executing PrismaService.
Therefore, we will now implement the process to achieve this objective.
However, it's simple: you can fulfill the requirement by modifying the test execution script in package.json to overwrite the environment variable as shown below.

"test": "export DATABASE_URL=postgresql://postgres:postgres@db:5432/postgres_test?schema=public  && npm run db:push:local && jest "

Now, if you try running the tests, they should fail as shown below.

There we go.

As a test, if you remove the part that overwrites the environment variable at the beginning, you can confirm that the test passes.
Since no data has been added to the test database and data exists in the application database, it seems the databases are correctly being distinguished.
By the way, while the environment variable is overwritten using export, this is only valid until the command completes.
Therefore, if you include console.log(process.env.DATABASE_URL) in any TS file running NestJS, the database URL for the application should be displayed.
With this, the desired behavior has been achieved.
Just because it's for a test database, writing the database URL directly into the command is a bit concerning.
Also, you would need to rewrite it every time the test database URL changes.
Since that's a hassle, finally, we will retrieve the value from an environment variable and overwrite DATABASE_URL based on that value.
First, add a value containing the test database URL to your .env file as follows:

DATABASE_TEST_URL =
  "postgresql://postgres:postgres@db:5432/postgres_test?schema=public";

Next, to retrieve environment variable values via command, install dotenv-cli.
Run npm i -D dotenv-cli in your terminal.
Once installed, change the part where the environment variable is overwritten as follows:

export DATABASE_URL=$(dotenv -- bash -c 'echo $DATABASE_TEST_URL')

The environment variable DATABASE_URL is set to store the result of the command enclosed in $( ).
So, let's look at the content of $( ), which is dotenv -- bash -c 'echo $DATABASE_TEST_URL'.
First, the dotenv command part follows this syntax:

dotenv (option)  -- specify the command to execute

On the left side of the --, you set options for things like loading environment variables.
If no specific options are set, it reads values from the .env file.
And on the right side, you specify the command to execute.
The command to execute can be any command.
Therefore, this time we define a command to output the DATABASE_TEST_URL environment variable.
This allows the dotenv command to first retrieve values from the .env file, and then output the DATABASE_TEST_URL environment variable.
The output result is then stored in the DATABASE_URL environment variable using the export command.
With the above, we can now switch the database referenced only during testing by retrieving the value from environment variables, without having to write the test database URL directly in the command.
Actually running the test with npm run test confirms that it points to the test database and the test fails.

Conclusion

This time, I made it possible to switch between the test database and the application database using a single schema file.
Switching via commands had many limitations, and finding a solution was tough, but the fix turned out to be unexpectedly simple.
Also, I used to think it was inconvenient that overwriting environment variables in scripts commands only lasted for the duration of the command, but it was very helpful this time.
Compared to before, the database definition setup is now significantly easier to maintain, so if you want to test using Prisma but prefer not to use mocks, please give this a try.
Thank you for reading this far.