This example demonstrates how to connect to a Postgres database, create a table, generate data with the faker package, and insert that data into a table.

Prerequisites

  1. This example assumes that you have a publicly accessible Postgres database.
  2. Before continuing, gather the following environment variables:
    • PG_HOST: The address for your Postgres database, e.g., ‘public-database.cluster.id.region.rds.amazonaws.com`
    • PG_PORT: The port for your Postgres server, typically 5432
    • PG_DATABASE: The database name
    • PG_USERNAME: The database username
    • PG_PASSWORD: The database password

Code

Environment Variables
PG_HOST=pg.example.com
PG_PORT=5432
PG_DATABASE=MY_DATABASE_NAME
PG_USERNAME=MY_USERNAME
PG_PASSWORD=MY_PASSWORD
Test Spec
import { faker } from '@faker-js/faker';
import { Client } from 'pg';

class PostgresTestSpec {
  npmDeps = {
    '@faker-js/faker': '8.4.1',
    pg: '8.11.3',
  };

  async globalInit(ctx) {
    const client = new Client({
      host: process.env.PG_HOST,
      port: process.env.PG_PORT,
      database: process.env.PG_DATABASE,
      user: process.env.PG_USERNAME,
      password: process.env.PG_PASSWORD,
    });

    await client.connect();

    const TABLE_NAME = 'products';

    // Create the "products" table
    await client.query(`
      CREATE TABLE IF NOT EXISTS "${TABLE_NAME}" (
        sku VARCHAR(255) PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        price NUMERIC NOT NULL,
        description TEXT,
        updatedAt TIMESTAMP NOT NULL
      )
    `);

    await client.end;

    return { TABLE_NAME };
  }

  async vuInit(ctx) {
    const client = new Client({
      host: process.env.PG_HOST,
      port: process.env.PG_PORT,
      database: process.env.PG_DATABASE,
      user: process.env.PG_USERNAME,
      password: process.env.PG_PASSWORD,
    });

    // Each VU establishes a connection to the database
    await client.connect();

    // Pass the connection to the VU Loop
    return { client };
  }

  async vuLoop(ctx) {
    const { TABLE_NAME } = ctx.globalInitData;
    const { client } = ctx.vuInitData;

    // Generate synthetic data with faker
    const product = {
      sku: faker.string.uuid(),
      name: faker.commerce.productName(),
      price: faker.commerce.price(),
      description: faker.commerce.productDescription(),
      updatedAt: new Date(),
    };

    const startTime = Date.now();

    // Insert a "product" in the table
    await client.query(
      `
        INSERT INTO "${TABLE_NAME}" (sku, name, price, description, updatedAt)
        VALUES($1, $2, $3, $4, $5)
      `,
      [
        product.sku,
        product.name,
        product.price,
        product.description,
        product.updatedAt,
      ],
    );

    // Capture the insert query time
    ctx.metric('Insert Time', Date.now() - startTime, 'ms');
  }

  async vuCleanup(ctx) {
    const { client } = ctx.vuInitData;

    // Each VU closes the database connection
    await client.end();
  }
}