Beginner's Guide: How to set up PostgreSQL within Playwright for Test Automation

When automating test scenarios, it's important to validate how the data is being stored, processed and retrieved. This ensures data integrity, guaranteeing the application's overall functionality and user experience remain reliable. In this blog we will learn how to set up and add database validations in your test scripts developed with Playwright. In this blog the focus will be on Postgres set up, necessary libraries and code snippets that need to be added within your test scripts.

How can Database integration help improve test automation?

Database integration will help improve the test automation in multiple ways -

1. Backend validations to ensure data integrity for end to end tests.
2. Query the database to get specific information, to continue execution of the front end tests.
3. Prepare data before test execution to save test execution time and improve test automation stability.
4. Purge data post test execution to reduce test flakiness.
5. Manipulate or update data, to enable component level testing with ease.

While scripting UI tests, one question we should always ask is -

How can I make the test more reliable and stable?

Not all validations or actions need to be performed on the UI, some of them can be easily achieved through database integrations which help improve the stability, speed of execution and reliability of the test automation suite.

How to set up postgres within your playwright scripts?

Consider a scenario where after signing up, the user receives a token in an email that needs to be used during the first time they sign in. While automating such scenarios, instead of reading the email every time, we can fetch the token directly from the database - this makes the test faster and reliable since we do not have to wait for the email to arrive at the user’s inbox and we do not have to parse the email body to get token details.

Learn how DevAssure simplifies this entire configuration and implementation.

Below is an example of how you can integrate PostgreSQL into your Playwright tests in TypeScript while supporting multiple environments (e.g., local, beta, prod). We'll use the following libraries:

• pg: A PostgreSQL client for Node.js.
• dotenv: Loads environment variables from a file so you can configure different settings per environment.

Below are step-by-step instructions along with sample code.

Learn in detail how to set up playwright.

Postgres and dotenv Installation

In your project root folder execute the following command

npm install pg dotenv

Set Up Environment-Specific Configuration Files

Postgres configurations will be different for different environments. We wouldn't want to create separate modules for each environment. We will be storing the necessary information in .env files and reading the data using the dotenv library.

.env.local

NODE_ENV=local
DB_HOST=localhost
DB_PORT=5432
DB_NAME=localhost_db
DB_USER=local_user
DB_PASSWORD=local_password

.env.beta

NODE_ENV=beta
DB_HOST=beta.sample-app.com
DB_PORT=5432
DB_NAME=beta_db
DB_USER=beta_user
DB_PASSWORD=beta_password

.env.prod

NODE_ENV=prod
DB_HOST=prod.sample-app.com
DB_PORT=5432
DB_NAME=prod_db
DB_USER=prod_user
DB_PASSWORD=prod_password

Create the base module

Within the test automation project's root folder of your playwright scripts - create a folder config and add a file index.ts

Based on the environment details provided at run time during test execution, this module will help identify the right PG database config to be read.

config/index.ts

import * as dotenv from 'dotenv';
import path from 'path';

// Determine the current environment. Default to 'local' if not specified.
const env = process.env.NODE_ENV || 'local';

// Build the path to the correct .env file (e.g., .env.local, .env.beta, .env.prod)
const envFilePath = path.resolve(process.cwd(), `.env.${env}`);
dotenv.config({ path: envFilePath });

// Export the database configuration
export const dbConfig = {
  host: process.env.DB_HOST,
  port: Number(process.env.DB_PORT || 5432),
  database: process.env.DB_NAME,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
};

Include the Database query within your playwright script

Here's a complete test script in Playwright using Typescript for the scenario mentioned above -

import { test, expect } from '@playwright/test';
import { Client } from 'pg';
import { dbConfig } from '../config';


test('Automate Signup with Token Fetch from Database', async ({ page }) => {


   // Create a new PostgreSQL client using the configuration
   const client = new Client(dbConfig);


   // Perform sign up
   await page.goto('https://appurl/login');
   await page.getByRole('link', { name: 'Join free today' }).click();
   await page.getByPlaceholder('Enter your email').click();
   await page.getByPlaceholder('Enter your email').fill('[e-mail]');
   await page.getByPlaceholder('Enter your name').click();
   await page.getByPlaceholder('Enter your name').fill('This is a test - Please ignore');
   await page.getByPlaceholder('Enter your password', { exact: true }).click();
   await page.getByPlaceholder('Enter your password', { exact: true }).fill('password');
   await page.getByPlaceholder('Enter your password again').click();
   await page.getByPlaceholder('Enter your password again').fill('password');
   await page.getByRole('button', { name: 'Sign Up' }).click();


   try{
       // Connect to the database
       await client.connect();


       // Execute a query
       const result = await client.query('SELECT token FROM your_table');


       if (result.rows.length > 0) {
           console.log('Token:', result.rows[0].token);

           // Use the token to sign in
           await page.goto('https://appurl/login?token=?' + result.rows[0].token);


           // Validate user signed in
           await expect(page).toHaveTitle('Welcome');


       } else {


           // throw error if token not found
           console.log('No token found in the query result.');
           throw new Error('No token found in the query result.');
       }
   } finally {
       // Ensure the client disconnects even if an error occurs
       await client.end();
   }
});

Explanation

const client = new Client(dbConfig);

Creates a new client using the configuration.

await client.connect();

Connects to the database.

const result = await client.query('SELECT token FROM your_table');

Executes the query.

result.rows[0].token

Fetches the values from the result set returned by the query.

await client.end();

It is important to always close the connection after test execution, irrespective of the test result.

Test Execution

When running your tests, set the NODE_ENV variable so the correct configuration is loaded. For example:

Local Environment:

NODE_ENV=local npx playwright test

Beta Environment:

NODE_ENV=beta npx playwright test

Learn how DevAssure simplifies this entire configuration and implementation.