@dankieu/cypress-sql NPM

First need run docker-compose to install container db

Configure Cypress to Use `@dankieu/cypress-sql`

1. Modify `cypress.config.ts` to Use the `@dankieu/cypress-sql` Package

import * as db from "@dankieu/cypress-sql";

export default defineConfig({
  e2e: {
    setupNodeEvents(on, config) {
      // Connect to SQL Server
      db.sqlServer(on);

      // Connect to Oracle
      db.sqlOracle(on);

      // Connect to MySQL
      db.sqlMySql(on);

      // Connect to PostgreSQL
      db.sqlPg(on);
    },
  },
});

Cypress Configuration (CommonJS Syntax)

const { defineConfig } = require("cypress");
const db = require("@dankieu/cypress-sql");

module.exports = defineConfig({
  e2e: {
    setupNodeEvents(on, config) {
      // Implement node event listeners here
      db.sqlServer(on);

      return config;
    },
  },
});

Available Database Connections

The @dankieu/cypress-sql package supports the following database connections:

SQL Server: db.sqlServer(on)
Oracle Database: db.sqlOracle(on)
MySQL: db.sqlMySql(on)
PostgreSQL: db.sqlPg(on)

Each of these methods establishes a connection to the respective database type when Cypress tests are running.

2: Import the Package in `e2e.ts`

In your cypress/support/e2e.ts file, simply import the @dankieu/cmd package. This will automatically add the custom SQL commands to Cypress.

Example: `cypress/support/e2e.ts`

import "@dankieu/cmd"; // Import @dankieu/cmd to initialize custom SQL commands

Cypress Database Connection Tests

1. SQL Server

Test: Fetch data from a SQL Server database.

Connection Config:

{
  "user": "SA",
  "password": "Alaska2017",
  "server": "localhost",
  "port": 1433,
  "database": "master",
  "options": {
    "encrypt": false,
    "trustServerCertificate": true
  }
}

SQL Query:
```
SELECT * FROM sys.tables;
```

Custom Commands:

Using cy.task('sqlServer'):

cy.task("sqlServer", { connectConfig: config, sqlQuery: sql }).then(results => {
  console.log("Query Results:SQL Server", results[0]);
});

Using custom cy.sqlServer() command:

cy.sqlServer(config, sql).then(results => {
  console.log("Query Results Custom CMD:SQL Server", results[0]);
});

2. OracleDB

Test: Fetch data from an Oracle database.

Connection Config:

 user: "my_user", 
password: "password_i_should_change",
connectString: "localhost:1521/FREEPDB1",
}

SQL Query:

SELECT 'employee_id' AS employee_id, 'first_name' AS first_name, 'last_name' AS last_name, 'salary' AS salary FROM DUAL;

Custom Commands:

Using cy.task('sqlOracle'):

cy.task("sqlOracle", { connectConfig: config, sqlQuery: sql }).then(results => {
  console.log("Query Results: Oracle", results[0]);
});

Using custom cy.sqlOracle() command:

cy.sqlOracle(config, sql).then(results => {
  console.log("Query Results Custom CMD:SQL Oracle", results[0]);
});

3. MySQL

Test: Fetch data from a MySQL database.

Connection Config:

{
  "host": "localhost",
  "user": "root",
  "password": "college",
  "database": "employees"
}

SQL Query:

SELECT dept_no, dept_name FROM employees.departments;

Custom Commands:

Using cy.task('sqlMySql'):

cy.task("sqlMySql", { connectConfig: dbConfig, sqlQuery: sql }).then(results => {
  console.log("Query Results: MySQL", results[0]);
});

Using custom cy.sqlMySql() command:

cy.sqlMySql(dbConfig, sql).then(results => {
  console.log("Query Results Custom CMD:SQL MySQL", results[0]);
});

4. PostgreSQL

Test: Fetch data from a PostgreSQL database.

Connection Config:

{
  "user": "world",
  "password": "world123",
  "host": "localhost",
  "port": 5432,
  "database": "world-db"
}

SQL Query:

SELECT code, continent, region, surface_area, indep_year, population, life_expectancy, gnp, gnp_old, local_name, government_form, head_of_state, capital, code2 FROM public.country;

Custom Commands:

Using cy.task('sqlPg'):

cy.task("sqlPg", { connectConfig: dbConfig, sqlQuery: sql }).then(results => {
  console.log("Query Results: Pg", results[0]);
});

Using custom cy.sqlPg() command:

cy.sqlPg(dbConfig, sql).then(results => {
  console.log("Query Results Custom CMD:SQL Pg", results[0]);
});

Here is the formatted version of the data:

Data Return Format

The data returned is in JSON format, structured as an array of objects.

{
  
    { "emp_id": 100, "first_name": "Steven", "last_name": "King", "salary": 24000 },
    { "emp_id": 101, "first_name": "Neena", "last_name": "Yang", "salary": 17000 },
    { "emp_id": 102, "first_name": "Lex", "last_name": "Garcia", "salary": 17000 },
    { "emp_id": 103, "first_name": "Alexander", "last_name": "James", "salary": 9000 },
    { "emp_id": 104, "first_name": "Bruce", "last_name": "Miller", "salary": 6000 }
  
}

This is the structured data in JSON format.