Greetings, readers! Welcome to this new blog where I’ll be demonstrating a couple of methods to integrate a PostgreSQL database with Playwright. While it’s generally cautioned against integrating databases directly into your end-to-end tests due to database safety concerns, there are instances where it becomes essential.
This is particularly true when you need to set up prerequisite data for your tests and no API endpoints are available yet. So, let’s quickly check these approaches and explore how to tackle this situation.
Setup
All you have to do is install the “Postgres” library. Since I’ll be showing you how to do this using TypeScript, you’ll also need to install the “types/pg” library.
DB Initialization
There are two main ways to start a connection to a database. The first way is through a client connection, where you create a connection just to run a specific SQL query , and then the connection is closed. The second way is by using a pool connection. With a pool connection, you set up the connection to the database and keep it active until you’re done with it or until the process is terminated.
Client connection with Test fixtures
The first approach to consider involves setting up a client connection with test fixtures. To begin, we create our own “DB” class and develop the necessary function to set up the client connection. This function is then integrated into our test fixtures, making it simple to invoke within our tests without the need to create new abstractions each time. in order to do so we will create the following class as shown below:
For improved error handling, we can implement the approach as shown below:
async executeQuery(query: string) {
const client = new Client(this.DBConfig);
try {
await client.connect();
const result = await client.query(query);
console.log(result.rows);
} catch (error) {
console.error("Error in connection/executing query:", error);
} finally {
await client.end().catch((error) => {
console.error("Error ending client connection:", error);
});
}
};Time to create the relevant test fixture as follow:
Now that our test fixture is set up, integrating the database into our tests becomes straightforward. We can now simulate a real-life situation where we want to perform a login with a new user. However, the registration API or functionality isn’t available yet. Therefore, we have to directly add a new user to the database.
test("[Desktop] Should be able to login", async ({ page, dataBase, loginPage }) => {
const seedPassword = "Test123";
const password = await hash(seedPassword, 12);
await dataBase.executeQuery(`INSERT INTO public."User" (username, password) VALUES ('testUser', '${password}');`);
await loginPage.loginWith("testUser", "Test123");
await expect(page).toHaveURL(/.*feedback/);
});
In the test we discussed, we put the username and password into the database after making the password hashed using the bcrypt library. Then, we used our login method to let this new user log in with the new credential. We knew the login worked as we checked the expected url.
To enhance the efficiency and reusability of our tests, we can optimize by shifting the part where we add a new user from the test itself to a beforeEach hook. This narrows down the test’s focus solely to the login functionality. Additionally, we can add a cleanup method to erase the user we created after the test is completed.
test.beforeEach(async ({ dataBase, page }) => {
const seedPassword = "Test123";
const password = await hash(seedPassword, 12);
await dataBase.executeQuery(`INSERT INTO public."User" (username, password) VALUES ('testUser', '${password}');`);
await page.goto("/");
});
test.afterEach(async ({ dataBase }) => {
await dataBase.executeQuery(`DELETE FROM public."User" WHERE username = 'testUser';`);
});
test("[Desktop] Should be able to login", async ({ page, loginPage }) => {
await loginPage.loginWith("testUser", "Test123");
await expect(page).toHaveURL(/.*feedback/);
});Pool connection in global setup
Using this method, we get our data ready before any worker starts, setting it up globally in advance. With a pool connection, we can run multiple queries while keeping the connection active. However, due to playwright nature, when the global setup function ends, the database connection process will also be terminated even with a singleton function.
We can reuse the same DB class that we have created in the earlier example with smaller tweak as shown below
import { Pool, PoolClient, QueryResult } from "pg";
export default class DB {
private pool: Pool;
private DBConfig = {
user: "db_user",
host: "localhost",
database: "local_db",
password: "test",
port: 5477,
max: 10,
idleTimeoutMillis: 30000,
connectionTimeoutMillis: 2000,
allowExitOnIdle: false,
};
async getDBConnection(): Promise<PoolClient> {
if (!this.pool) {
this.pool = new Pool(this.DBConfig);
const client = await this.pool.connect();
console.log(`---------> √ DB connection has been established! <---------`);
return client;
} else {
return this.pool.connect();
}
}
async executeQuery(query: string): Promise<void> {
try {
const client: PoolClient = await this.getDBConnection();
const result: QueryResult = await client.query(query);
console.log(result.rows);
} catch (error) {
console.error("Error executing query:", error);
}
}
}Then call our function into our globalSetup.ts file
import DB from "./db";
import { hash } from "bcrypt";
const connectToDB = new DB();
const seedPassword = "Test123";
const password = await hash(seedPassword, 12);
async function establishDBConnection() {
try {
await connectToDB.getDBConnection();
} catch (error) {
console.log(`---------> X Failed to connect to dataBase <--------- \n\n ${error}`);
process.exit(1);
}
}
async function globalSetup() {
await establishDBConnection();
await connectToDB.executeQuery(`DELETE FROM public."User" WHERE username = 'testUser';`);
await connectToDB.executeQuery('SELECT * FROM public."User"');
await connectToDB.executeQuery(`INSERT INTO public."User" (username, password) VALUES ('testUser', '${password}');`);
}
export default globalSetup;Now, there’s no need for any further execution within our test cases. This allows us to keep the focus of our test scope solely on testing the core functionality.
Pool connection approach with project dependencies
Similar to global setup we can create our own project that will execute certain test as setup then make other projects depend on this setup, so lets see how to do so:
projects: [
{
name: "prerequisites",
testMatch: "**/*/dataBaseSetup.ts",
},
{
name: "Chrome",
use: {
browserName: "chromium",
},
dependencies: ["prerequisites"]
}
];Include projects as explained earlier, where we set up a project named “prerequisites.” Use the testMatch setup to point to the “databaseSetup.ts” test configuration file.
import { test as setup } from "@playwright/test";
import DB from "../utils/db";
import { hash } from "bcrypt";
setup("Prepare the pre-requisites", async () => {
const db = new DB(); // test fixture can be used as well
await db.getDBConnection();
const seedPassword = "Test123";
const password = await hash(seedPassword, 12);
await db.executeQuery(`DELETE FROM public."User" WHERE username = 'testUser';`);
await db.executeQuery(`INSERT INTO public."User" (username, password) VALUES ('testUser', '${password}');`);
await db.executeQuery('SELECT * FROM public."User"');
});
Conclusion
While it’s not always advised to use a database in your end-to-end tests, it can also help overcome specific obstacles as discussed earlier. Throughout this blog, we’ve showcased the capability to integrate with a “postgres” database, but the fundamental idea remains the same for most of other database types. At this point, we should have understood how to employ global setup, manage test fixtures, handle project dependencies, and easily integrate tests with database or any other external services.
References
https://node-postgres.com/
https://playwright.dev/docs/test-configuration
Thank you for taking the time to read my blog — I hope it provided some insight and value into your project. Stay tuned for the new upcoming interesting blogs…
