CodeceptJS PostgreSQL Helper

Common errors

• Error: connect ECONNREFUSED 127.0.0.1:5432

  cause The CodeceptJS helper could not establish a connection to the PostgreSQL server at the specified host and port.
  fix Verify that the PostgreSQL server is running, listening on the specified port (`5432` by default), and accessible from the machine running CodeceptJS tests. Check firewall rules if applicable. Confirm `host` and `port` in `codecept.conf.js`.

• Error: password authentication failed for user "postgres"

  cause The provided username or password in the helper configuration is incorrect for the PostgreSQL database.
  fix Double-check the `user` and `password` values in `codecept.conf.js` against your PostgreSQL user credentials. Ensure the user has permissions to connect to the specified database.

• Error: database "testdb" does not exist

  cause The specified database name in the helper configuration does not exist on the PostgreSQL server.
  fix Ensure the `database` name in `codecept.conf.js` matches an existing database on your PostgreSQL server, or create the database if it's missing.

Warnings

• gotcha Directly embedding sensitive database credentials (host, port, user, password, database) into `codecept.conf.js` is a security risk. These values should be externalized, especially in shared or CI/CD environments.
  Fix: Use environment variables (e.g., `process.env.DB_HOST`) to pass credentials to the helper configuration. Tools like `dotenv` can assist with local development environment variables.
• gotcha Constructing SQL queries by directly concatenating unsanitized string inputs (e.g., from user input or external sources) can lead to SQL injection vulnerabilities.
  Fix: For dynamic values, ensure all inputs are properly sanitized or, if the underlying `pg` client were directly exposed, use parameterized queries. For test data originating from trusted scripts, this risk is reduced but still a good practice to be aware of.
• breaking This package is a CodeceptJS helper and is designed to run within the CodeceptJS framework. It cannot be used as a standalone Node.js PostgreSQL client library.
  Fix: If standalone PostgreSQL interaction is needed outside CodeceptJS tests, use the `pg` package directly or another dedicated PostgreSQL client library.
• gotcha Ensure your PostgreSQL database server is running and accessible from where CodeceptJS tests are executed. Network firewalls or incorrect host/port configurations can prevent connections.
  Fix: Verify the PostgreSQL service status, check firewall rules, and confirm `host` and `port` settings in `codecept.conf.js` match your database server configuration.

Install

• npm install codeceptjs-postgresqlhelper npm
• yarn add codeceptjs-postgresqlhelper yarn
• pnpm add codeceptjs-postgresqlhelper pnpm

Imports

• PostgresqlDBHelper Class

  const PostgresqlDBHelper = require('codeceptjs-postgresqlhelper');

  This is how the raw helper class module is directly imported, typically for advanced custom extensions or debugging, rather than direct use in test files.
• Configuration `require`
  wrong:

  import { PostgresqlDBHelper } from 'codeceptjs-postgresqlhelper';

  correct:

      helpers: {
        PostgresqlDBHelper: {
            require: 'codeceptjs-postgresqlhelper',
            // ... configuration options
        }
      }

  The primary way developers 'import' this package is by referencing it in the `helpers` section of `codecept.conf.js`. CodeceptJS uses Node.js `require` internally for loading helpers, and this package does not provide ESM exports.
• I.runQuery (in tests)

  await I.runQuery('SELECT * FROM my_table;');

  After the helper is configured, its methods, such as `runQuery`, become available directly on the CodeceptJS actor object `I` within test scenarios. This is the most common way to interact with the helper in tests.

Quickstart

This quickstart demonstrates how to configure the `PostgresqlDBHelper` in a `codecept.conf.js` file, utilizing environment variables for secure credential management. It then provides an example CodeceptJS scenario (`my_db_test.js`) that uses the `I.runQuery` method to perform a full database test lifecycle: creating a temporary table, inserting test data, verifying the insertion, and finally cleaning up the table, ensuring isolated tests.

const { setHeadlessWhen, setWindowSize } = require('@codeceptjs/configure');
setHeadlessWhen(process.env.CI);
setWindowSize(1200, 800);

exports.config = {
  tests: './*_test.js',
  output: './output',
  helpers: {
    PostgresqlDBHelper: {
      require: 'codeceptjs-postgresqlhelper',
      host: process.env.DB_HOST ?? '127.0.0.1',
      port: parseInt(process.env.DB_PORT ?? '5432'),
      user: process.env.DB_USER ?? 'postgres',
      password: process.env.DB_PASSWORD ?? 'postgres',
      database: process.env.DB_DATABASE ?? 'testdb',
    },
    // Include another helper if browser interaction is needed, e.g., Playwright
    // Playwright: {
    //   url: 'http://localhost',
    //   show: true,
    //   browser: 'chromium'
    // }
  },
  include: {},
  bootstrap: async () => {
    // Optional: run initial setup like creating the database if it doesn't exist
    // (requires a separate connection for initial DB creation)
  },
  teardown: null,
  mocha: {},
  name: 'codeceptjs-postgresqlhelper-quickstart',
  plugins: {
    retryFailedStep: { enabled: true },
    screenshotOnFail: { enabled: true }
  }
};

// --- my_db_test.js ---
Feature('Database Operations');

Scenario('should create a table, insert data, and verify', async ({ I }) => {
  const tableName = 'test_users_' + Date.now();
  const username = 'john_doe_' + Date.now();
  const email = username + '@example.com';

  // 1. Create a table
  await I.runQuery(`CREATE TABLE IF NOT EXISTS ${tableName} (id SERIAL PRIMARY KEY, username VARCHAR(255) UNIQUE, email VARCHAR(255));`);
  console.log(`Table ${tableName} created or already exists.`);

  // 2. Insert data
  await I.runQuery(`INSERT INTO ${tableName} (username, email) VALUES ('${username}', '${email}');`);
  console.log(`Inserted user ${username}.`);

  // 3. Verify data insertion
  const selectResult = await I.runQuery(`SELECT * FROM ${tableName} WHERE username = '${username}';`);
  I.assert(selectResult.rows.length).equals(1);
  I.assert(selectResult.rows[0].email).equals(email);
  console.log(`Verified user ${username} in table.`);

  // 4. Clean up (optional, but good practice for isolated tests)
  await I.runQuery(`DROP TABLE ${tableName};`);
  console.log(`Table ${tableName} dropped.`);
});