Cypress PostgreSQL Database Integration

Common errors

• TypeError: postgreSQL.loadDBPlugin is not a function

  cause The `cypress-postgresql` module was imported incorrectly, or the `pg` dependency is missing.
  fix Ensure `pg` is installed (`npm install pg`) and that `cypress-postgresql` is `require`d in the plugin file (e.g., `const postgreSQL = require('cypress-postgresql');`) as it exposes `loadDBPlugin` as a method on its default export.

• Cypress encountered an error while connecting to the database. (connection refused)

  cause The PostgreSQL server is not running, or the database connection details in `cypress.json` (host, port, user, password, database) are incorrect or inaccessible from the Cypress test environment.
  fix Verify that your PostgreSQL server is running and accessible from where Cypress tests are executed. Double-check all database credentials in your `cypress.json` file for accuracy. Ensure no firewall rules are blocking the connection.

• error: relation "your_table_name" does not exist

  cause The SQL query being executed refers to a table or column that does not exist in the connected PostgreSQL database, or the database user lacks permissions to access it.
  fix Review your SQL query for typos in table or column names. Confirm that the table and columns exist in the target database. Check the database user's permissions to ensure they have read/write access to the necessary tables.

Warnings

• breaking This package relies on the deprecated `cypress/plugins/index.js` file for configuration. It is not compatible with Cypress 10.0+ out-of-the-box, which introduced `cypress.config.js` and a new configuration API. Migration requires significant manual effort.
  Fix: For Cypress 10+, manual refactoring is needed to adapt the plugin logic to the `cypress.config.js` setup. This may involve using `defineConfig` and handling `on('task', ...)` directly within the configuration file or creating a custom `setupNodeEvents` function. Alternatively, consider using a more modern Cypress database plugin.
• gotcha The `pg` (node-postgres) package is a direct dependency for the database connection and must be installed separately by the user. It is not bundled or listed as a peer dependency in the provided metadata.
  Fix: Ensure `npm install pg` or `yarn add pg` is run in your project's root directory alongside `cypress-postgresql`.
• gotcha Storing database credentials directly in `cypress.json` (as suggested by the README) is a security risk, especially if the file is committed to version control. Sensitive information should be handled securely.
  Fix: Utilize environment variables (e.g., `process.env.PG_USER`) in your `cypress.json` or `cypress.config.js` for database credentials. Pass them during test execution, for example, using a `.env` file and a package like `dotenv` or directly in your CI/CD pipeline.
• deprecated The package's primary integration method through `cypress/plugins/index.js` is considered a legacy approach in modern Cypress versions. While commands in `cypress/support/index.js` remain viable, the plugin side is outdated.
  Fix: For new projects, prefer Cypress 10+ and integrate database tasks directly into `cypress.config.js` or explore alternative, actively maintained Cypress database plugins that support the latest configuration patterns.

Install

• npm install cypress-postgresql npm
• yarn add cypress-postgresql yarn
• pnpm add cypress-postgresql pnpm

Imports

• postgreSQL
  wrong:

  import { postgreSQL } from 'cypress-postgresql';

  correct:

  import postgreSQL from 'cypress-postgresql';

  Used for loading Cypress custom commands. The package exports its main functionality as a default export.
• loadDBPlugin
  wrong:

  import { loadDBPlugin } from 'cypress-postgresql';

  correct:

  const postgreSQL = require('cypress-postgresql');
  // then tasks = postgreSQL.loadDBPlugin(pool);

  This function is exposed as a method on the default export for CommonJS environments (e.g., `cypress/plugins/index.js`).
• loadDBCommands
  wrong:

  import { loadDBCommands } from 'cypress-postgresql';

  correct:

  import postgreSQL from 'cypress-postgresql';
  postgreSQL.loadDBCommands();

  This function is exposed as a method on the default export for ES module environments (e.g., `cypress/support/index.js`).

Quickstart

This quickstart demonstrates how to configure `cypress-postgresql` in a Cypress setup (pre-Cypress 10.0), define database credentials, load custom commands, and execute a basic insert and select query within a test.

/* cypress.json or cypress.config.js equivalent */
{
  "db": {
    "user": "process.env.PG_USER ?? ''",
    "password": "process.env.PG_PASSWORD ?? ''",
    "host": "localhost",
    "port": 5432,
    "database": "test_db"
  }
}

/* cypress/plugins/index.js (Cypress < 10) or cypress.config.js setup */
const postgreSQL = require('cypress-postgresql');
const { Pool } = require('pg');
const dbConfig = require('../../cypress.json'); // Adjust path as needed

module.exports = (on, config) => {
  const pool = new Pool(dbConfig.db);
  const tasks = postgreSQL.loadDBPlugin(pool);
  on('task', tasks);
  return config;
};

/* cypress/support/commands.js */
import postgreSQL from 'cypress-postgresql';
postgreSQL.loadDBCommands();

/* cypress/e2e/db.cy.js */
describe('Database interactions', () => {
  beforeEach(() => {
    // Ensure the database is clean or seeded before each test
    cy.postgresql('TRUNCATE TABLE users RESTART IDENTITY;').then(() => {
      cy.log('Users table truncated.');
    });
  });

  it('should insert and retrieve data from PostgreSQL', () => {
    cy.postgresql(`INSERT INTO users (name, email) VALUES ('John Doe', 'john@example.com');`)
      .then(() => {
        return cy.postgresql(`SELECT name FROM users WHERE email = 'john@example.com';`);
      })
      .then((result) => {
        expect(result[0].name).to.eq('John Doe');
      });
  });
});