{"id":16314,"library":"codeceptjs-postgresqlhelper","title":"CodeceptJS PostgreSQL Helper","description":"CodeceptJS helper (version 1.0.1) designed to streamline end-to-end testing against PostgreSQL databases. This package allows developers to execute raw SQL queries directly from their CodeceptJS test scenarios, which is crucial for setting up test data, verifying database states, and cleaning up after tests. It integrates into the CodeceptJS ecosystem by being configured in `codecept.conf.js` and exposing its methods via the framework's actor object (`I`). This helper differentiates itself from general PostgreSQL client libraries by providing a test-centric interface within the CodeceptJS testing paradigm. The project is maintained by Percona-Lab, with its release cadence currently driven by community contributions and specific project needs.","status":"active","version":"1.0.1","language":"javascript","source_language":"en","source_url":"https://github.com/Percona-Lab/codeceptjs-postgresqlhelper","tags":["javascript","codeceptjs","postgresql","postgres","helper"],"install":[{"cmd":"npm install codeceptjs-postgresqlhelper","lang":"bash","label":"npm"},{"cmd":"yarn add codeceptjs-postgresqlhelper","lang":"bash","label":"yarn"},{"cmd":"pnpm add codeceptjs-postgresqlhelper","lang":"bash","label":"pnpm"}],"dependencies":[{"reason":"Runtime dependency for establishing and managing connections to PostgreSQL databases.","package":"pg","optional":false},{"reason":"Peer dependency as it extends the base CodeceptJS helper class.","package":"@codeceptjs/helper","optional":false}],"imports":[{"note":"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.","symbol":"PostgresqlDBHelper Class","correct":"const PostgresqlDBHelper = require('codeceptjs-postgresqlhelper');"},{"note":"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.","wrong":"import { PostgresqlDBHelper } from 'codeceptjs-postgresqlhelper';","symbol":"Configuration `require`","correct":"    helpers: {\n      PostgresqlDBHelper: {\n          require: 'codeceptjs-postgresqlhelper',\n          // ... configuration options\n      }\n    }"},{"note":"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.","symbol":"I.runQuery (in tests)","correct":"await I.runQuery('SELECT * FROM my_table;');"}],"quickstart":{"code":"const { setHeadlessWhen, setWindowSize } = require('@codeceptjs/configure');\nsetHeadlessWhen(process.env.CI);\nsetWindowSize(1200, 800);\n\nexports.config = {\n  tests: './*_test.js',\n  output: './output',\n  helpers: {\n    PostgresqlDBHelper: {\n      require: 'codeceptjs-postgresqlhelper',\n      host: process.env.DB_HOST ?? '127.0.0.1',\n      port: parseInt(process.env.DB_PORT ?? '5432'),\n      user: process.env.DB_USER ?? 'postgres',\n      password: process.env.DB_PASSWORD ?? 'postgres',\n      database: process.env.DB_DATABASE ?? 'testdb',\n    },\n    // Include another helper if browser interaction is needed, e.g., Playwright\n    // Playwright: {\n    //   url: 'http://localhost',\n    //   show: true,\n    //   browser: 'chromium'\n    // }\n  },\n  include: {},\n  bootstrap: async () => {\n    // Optional: run initial setup like creating the database if it doesn't exist\n    // (requires a separate connection for initial DB creation)\n  },\n  teardown: null,\n  mocha: {},\n  name: 'codeceptjs-postgresqlhelper-quickstart',\n  plugins: {\n    retryFailedStep: { enabled: true },\n    screenshotOnFail: { enabled: true }\n  }\n};\n\n// --- my_db_test.js ---\nFeature('Database Operations');\n\nScenario('should create a table, insert data, and verify', async ({ I }) => {\n  const tableName = 'test_users_' + Date.now();\n  const username = 'john_doe_' + Date.now();\n  const email = username + '@example.com';\n\n  // 1. Create a table\n  await I.runQuery(`CREATE TABLE IF NOT EXISTS ${tableName} (id SERIAL PRIMARY KEY, username VARCHAR(255) UNIQUE, email VARCHAR(255));`);\n  console.log(`Table ${tableName} created or already exists.`);\n\n  // 2. Insert data\n  await I.runQuery(`INSERT INTO ${tableName} (username, email) VALUES ('${username}', '${email}');`);\n  console.log(`Inserted user ${username}.`);\n\n  // 3. Verify data insertion\n  const selectResult = await I.runQuery(`SELECT * FROM ${tableName} WHERE username = '${username}';`);\n  I.assert(selectResult.rows.length).equals(1);\n  I.assert(selectResult.rows[0].email).equals(email);\n  console.log(`Verified user ${username} in table.`);\n\n  // 4. Clean up (optional, but good practice for isolated tests)\n  await I.runQuery(`DROP TABLE ${tableName};`);\n  console.log(`Table ${tableName} dropped.`);\n});","lang":"javascript","description":"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."},"warnings":[{"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.","message":"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.","severity":"gotcha","affected_versions":">=1.0.0"},{"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.","message":"Constructing SQL queries by directly concatenating unsanitized string inputs (e.g., from user input or external sources) can lead to SQL injection vulnerabilities.","severity":"gotcha","affected_versions":">=1.0.0"},{"fix":"If standalone PostgreSQL interaction is needed outside CodeceptJS tests, use the `pg` package directly or another dedicated PostgreSQL client library.","message":"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.","severity":"breaking","affected_versions":">=1.0.0"},{"fix":"Verify the PostgreSQL service status, check firewall rules, and confirm `host` and `port` settings in `codecept.conf.js` match your database server configuration.","message":"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.","severity":"gotcha","affected_versions":">=1.0.0"}],"env_vars":null,"last_verified":"2026-04-22T00:00:00.000Z","next_check":"2026-07-21T00:00:00.000Z","problems":[{"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`.","cause":"The CodeceptJS helper could not establish a connection to the PostgreSQL server at the specified host and port.","error":"Error: connect ECONNREFUSED 127.0.0.1:5432"},{"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.","cause":"The provided username or password in the helper configuration is incorrect for the PostgreSQL database.","error":"Error: password authentication failed for user \"postgres\""},{"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.","cause":"The specified database name in the helper configuration does not exist on the PostgreSQL server.","error":"Error: database \"testdb\" does not exist"}],"ecosystem":"npm"}