Connecting Cypress to a PostgreSQL database

One of the frequently asked questions in the Cypress Discord channel is; how to set up a Cypress project and connect to a Database. This week I saw the questions pop up for Postgres, Oracle, and Microsoft SQL. Because of this, I thought to create a small series where I describe the various Databases and how to use Cypress to connect to them.
For my first blog, I will start with the easiest one PostgreSQL.

If you want to skip the explanation and see the results, then go directly to my GitHub project: https://github.com/Xvier/cypress-postgres-demo

Prerequisites to get started:

• Cypress project
• Basic knowledge of cy.task
• Postgres Database
• PG nodejs library

Let's first set up our Database. For this example, I will be using Docker. But feel free to install Postgres or use a cloud provider if that fits your needs.

FROM postgres
ENV POSTGRES_USER cypress_admin
ENV POSTGRES_PASSWORD cypressrules
ENV POSTGRES_DB cypress

To start the Database we will have to create a container and run it with the following commands:

docker build -t mydatabase .  
docker run -p 5432:5432 mydatabase:latest 

If everything running, you will now have a Database online and the following message in your terminal: database system is ready to accept connections.

Let us create a new Cypress project, install the Postgres client, and open Cypress.

npm init -y 
npm install -D cypress pg
npx cypress open --e2e --browser=chrome

Let us create a new spec file in the e2e folder called postgres.cy.js

describe('We will make a connection with Postgres, () => {
  it('First connection', () => {

  })
})

So how do we connect to the Database? For this, we need to utilize a cypress task. With cy.task() we can execute node code.
Example of node libraries:

• FS to write files to the operating system
• PG to call Databases
• bcrypt to hash passwords

So how do we set up a task?
In the cypress.config.js file, we will add the following code.

const { defineConfig } = require("cypress");

module.exports = defineConfig({
  e2e: {
    setupNodeEvents(on, config) {
      on("task", {
        connectDB(){
          return "Inside our task"
        }
      })
    },
  },
});

We created a task called connectDB and this will return a string Inside our task.

In our Cypress test, we will now add the following code to our test and view the output in Cypress.

cy.task("connectDB").then(cy.log)

The cy.log yields the string Inside our task.

Now that we know this works we can alter our task code to connect to the Database and get the current Database Date.

Let us change our task to connect to the Database. The following code will make a connection to our local host with the credentials we set in our DockerFile.

To find out how to connect to a Postgres Database you can also check the documentation: https://node-postgres.com/

const { defineConfig } = require("cypress");
const { Client } = require('pg')
module.exports = defineConfig({
  e2e: {
    setupNodeEvents(on, config) {
      on("task", {
        async connectDB(){
          const client = new Client({
            user: "cypress_admin",
            password: "cypressrules",
            host: "localhost",
            database: "cypress",
            ssl: false,
            port: 5432
          })
          await client.connect()
          const res = await client.query('SELECT NOW()')
          await client.end()
          return res.rows;
        }
      })
    },
  },
});

For the time being, we hard coded the query to see if we will get a valid response back.
Let us rerun our test and view the results.

We now see a valid date being send back from the Database.

Now our last step will be to remove the hard coded query value.
To remove the hard coded value we will add a query parameter to our function. With this, we can set the query from our test. Let us add the value query to our method and the await.client.query() statement.

Our final edit looks like this:

       async connectDB(query){
          const client = new Client({
            user: "cypress_admin",
            password: "cypressrules",
            host: "localhost",
            database: "cypress",
            ssl: false,
            port: 5432
          })
          await client.connect()
          const res = await client.query(query)
          await client.end()
          return res.rows;
        }

And in our test, we can pass a query when calling our task

cy.task("connectDB","SELECT NOW()").then(cy.log)

And this will give us the same result as when we did it hard coded.

Now that you have a valid connection you can add, remove and check data in your Database.

Thank you all for reading please check out my repository for a full working sample: https://github.com/Xvier/cypress-postgres-demo

For questions please reach out to me here or in the Cypress Discord Channel: https://discord.gg/cypress