CodeceptJS Database Helper

Common errors

• Error: Cannot find module 'database-js-mysql'

  cause A specific database driver for 'database-js' (e.g., 'database-js-mysql') was not installed, but its connection string was used.
  fix Install the required database driver using npm: `npm i -D database-js-YOUR_DRIVER_NAME`. For example, `npm i -D database-js-mysql`.

• TypeError: I.connect is not a function

  cause The `DbHelper` was not correctly configured in your `codecept.conf.js` file, or CodeceptJS failed to load it.
  fix Verify that `codeceptjs-dbhelper` is listed in the `helpers` section of your `codecept.conf.js` file with the correct `require` path, typically `"require": "codeceptjs-dbhelper"`.

• Error: SQLITE_CANTOPEN: unable to open database file

  cause The specified path for a SQLite database file in the connection string is incorrect, or there are insufficient file system permissions to create/access the file.
  fix Check the database file path in your connection string and ensure the test runner has read/write permissions to that directory. Use an absolute path if necessary for clarity.

Warnings

• gotcha The DbHelper relies on the `database-js` abstraction layer and specific database drivers. You must manually install `database-js` and the chosen driver (e.g., `database-js-mysql`, `database-js-sqlite`) separately from the helper itself.
  Fix: Run `npm i -D database-js database-js-YOUR_DRIVER_NAME` (e.g., `npm i -D database-js database-js-mysql`) in addition to `npm i -D codeceptjs-dbhelper`.
• gotcha When upgrading CodeceptJS from version 2 to 3, the argument signature for `Scenario`, `Before`, and `After` callbacks changed. CodeceptJS 2 passes `I` directly (e.g., `async (I) => { ... }`), while CodeceptJS 3 requires `I` to be destructured from an object (e.g., `async ({ I }) => { ... }`).
  Fix: Update your CodeceptJS test files to use object destructuring for the `I` object in callbacks: `async ({ I }) => { /* ... */ }`. Refer to the CodeceptJS upgrade documentation for more information.
• gotcha Ensure your database connection strings are correctly formatted and accessible from the environment where CodeceptJS tests are running. Common issues include incorrect host, port, username, password, or database name, as well as network/firewall restrictions.
  Fix: Verify the connection string syntax according to your chosen `database-js` driver documentation and confirm network access. It is recommended to use environment variables for sensitive credentials.

Install

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

Imports

• DbHelper Configuration
  wrong:

  import { DbHelper } from 'codeceptjs-dbhelper';

  correct:

  {
    "helpers": {
      "DbHelper": {
        "require": "codeceptjs-dbhelper",
        "host": "localhost",
        "user": "root"
      }
    }
  }

  The DbHelper is a CodeceptJS plugin loaded via the `helpers` configuration in `codecept.conf.js`. It does not expose symbols for direct import into test files. Its methods are mixed into the CodeceptJS `I` object. The 'host' and 'user' parameters are examples of common configuration properties.
• I.connect

  I.connect("mydb", "mysql://user:pass@host:port/dbname");

  Methods like `connect` are made available on the CodeceptJS `I` object after the DbHelper is configured. They are not imported directly from 'codeceptjs-dbhelper', but rather used within CodeceptJS test files.
• I.run

  await I.run("mydb", "SELECT * FROM users WHERE id = ?", userId);

  Database commands and queries are executed via `I.run`, using a connection key established with `I.connect`. Parameters are passed securely to prevent SQL injection, adhering to `database-js` conventions.

Quickstart

Demonstrates connecting to a database (MySQL example), seeding test data before the test suite and individual tests, cleaning up connections, and executing queries within a CodeceptJS scenario using the DbHelper methods available on the `I` object.

const { BeforeSuite, AfterSuite, Before, Scenario } = require('codeceptjs');

BeforeSuite(async ({ I }) => {
  // Establish a database connection named 'testdb'
  // Replace connection string with your actual database details and credentials
  I.connect("testdb", `mysql://root:password@localhost:3306/testdb`);
});

AfterSuite(async ({ I }) => {
  // Close the database connection named 'testdb'
  await I.removeConnection("testdb");
});

Before(async ({ I }) => {
  // Clean up and seed the 'user' table before each test for a clean state
  await I.run("testdb", "DELETE FROM user");
  await I.run("testdb", "INSERT INTO user (username, password) VALUES (?, ?)", "admin", "123456");
  await I.run("testdb", "INSERT INTO user (username, password) VALUES (?, ?)", "bob", "654321");
});

Scenario('should retrieve a specific user from the database', async ({ I }) => {
  // Execute a query to retrieve users and perform assertions
  const users = await I.run("testdb", "SELECT username FROM user WHERE username = ?", "admin");

  console.log('Found users:', users);
  I.assert(users.length).equals(1);
  I.assert(users[0].username).equals('admin');

  const nonExistentUser = await I.run("testdb", "SELECT username FROM user WHERE username = ?", "charlie");
  I.assert(nonExistentUser.length).equals(0);
});