LogoLogo
  • FAC Curriculum
  • archive
    • Node & npm introduction
    • developer
      • Programming Paradigms
    • handbook
      • Asking for help
      • Coaching groups
      • Code review
      • Course rules
      • Debugging
      • Employment schedule and material delivered in FAC20
      • GitHub Workflow
      • Glossary or terms
      • Presentation Guidance
      • Equality, Diversity and Inclusion
      • Installation guide
      • Learning circles
      • Mentoring guidance
      • What to expect from your mentors
      • One-day projects
      • Pair programming
      • Portfolio
      • Questions for problem solving
      • Progress Logs
      • Final project report
      • Managing software projects
      • Project Presentations
      • Project roles
      • Projects
      • Retrospectives
      • Role Circles
      • Safeguarding policy
      • Technical Spikes
      • System requirements
      • Tech for Better
      • User Manuals
      • Wellbeing Support
      • project-docs
        • What makes a mentor?
        • Product Handover
        • Sprint Planning
        • Tech for Better Presentations
        • User Research & Usability Testing
    • foundation
      • full-stack
        • Learning Outcomes
        • project
      • testing
        • project
        • spikes
  • docs
    • Contributing to the curriculum
    • Curriculum intent
    • Curriculum process
  • src
    • About our curriculum
    • course
      • Code of Conduct
      • Docker
      • .NET and Umbraco
      • Getting started
      • Founders and Coders coursebook
      • KSB's
      • Mini projects
      • Revision checklist
      • Svelte
      • TypeScript
      • handbook
        • Software Developer Handbook
        • Software Foundation Handbook
      • precourse
        • Before you start the course
        • Installation List
      • syllabus
        • developer
          • app
            • learning-outcomes
            • project
            • schedule
            • spikes
          • introduction
            • learning-outcomes
            • project
            • resources
            • schedule
          • week00-pre-course
            • We'd like you to spend some time before starting the course working on useful fundamentals.
            • spikes
          • week01-project01-basics
            • Employability introduction
            • Homework
            • learning-outcomes
            • Week of September 9th
            • project
            • resources
            • schedule
            • spikes
          • week02-project02-chatbot
            • employability
            • Homework
            • learning-outcomes
            • Week of September 16th
            • project
            • resources
            • schedule
            • spikes
          • week03-project03-server
            • Learning Outcomes
            • Week of September 23th
            • The Amazin' Quizzer API Backend
            • resources
            • schedule
          • week04-project03-frontend
            • learning-outcomes
            • Week of September 30th
            • UI for Quizzer App
            • resources
            • schedule
          • week05-project03-test-deploy
            • Testing and deployment
            • Week of October 7th
            • project
            • resources
            • schedule
          • week06-project04-databases
            • learning-outcomes
            • Week of October 14th
            • project
            • Databases
            • schedule
          • week07-project04-authentication
            • Learning Outcomes
            • Week of October 21st
            • project
            • resources
            • schedule
          • week08-project04-test-deploy
            • Learning Outcomes
            • Week of October 28th
            • project
            • resources
            • schedule
          • week09-reading-week
            • Learning Outcomes
            • overview
            • Project
            • Resources
            • schedule
          • week10-project05-DOTNET-intro
            • Learning Outcomes
            • overview
            • project
            • Resources
            • schedule
          • week11-project05-DOTNET-testing
            • Testing and deployment
            • Week of November 18th
            • project
            • Resources
            • schedule
          • week12-project05-DOTNET-deploy
            • Learning Outcomes
            • Week of November 25th
            • project
            • Resources
            • schedule
            • Spikes
          • week13-TFB-design
            • Learning Outcomes
            • overview
            • Project
            • Resources
            • schedule
            • Design Week Spikes
          • week14-TFB-build
            • Learning Outcomes
            • overview
            • Project
            • DevOps Resources
            • schedule
            • Spikes
          • week15-TFB-build
            • Learning Outcomes
            • overview
            • Project
            • Resources
            • schedule
            • Spikes
          • projects
            • in-house-design
              • Learning Outcomes
              • Project
              • Resources
              • schedule
              • Design Week Spikes
        • foundation
          • Obsolete-full-stack
            • project
          • post-course
            • Homework
            • schedule
        • portfolio
          • fruit-shop
            • learning-outcomes
            • project
            • resources
          • game
            • learning-outcomes
            • project
            • resources
          • hobby-page
            • learning-outcomes
            • project
            • resources
          • movie-data
            • learning-outcomes
            • project
            • resources
          • project-gallery
            • learning-outcomes
            • project
            • resources
          • website
            • learning-outcomes
            • project
            • JavaScript
        • tfb
          • week 1
            • Introduction (45 minutes)
            • Further reading
          • week 10
            • content
            • resources
          • week 11
            • What will we be doing this week?
            • resources
          • week 12
            • What will we be doing this week?
            • Further reading
          • week 2
            • Discover (90 minutes)
            • resources
          • week 3
            • content
            • resources
          • week 4
            • Mapping the user journey (90 minutes)
            • resources
          • week 5
            • Figma Workshop 1 (90 minutes)
            • Further reading
          • week 6
            • Figma Workshop 2 (90 minutes)
            • resources
          • week 7
            • Product pitches & Selection (90 minutes)
            • resources
          • week 8
            • content
            • resources
          • week 9
            • content
            • resources
    • learn
      • DOTNET
        • Introduction to .NET
      • auth
        • Authenticating web apps
      • database
        • Persisting data with SQLite and Node
      • dotnet-two
        • Dependency injections and interfaces in .NET
      • form-validation
        • Form validation
      • react
        • Building client-side apps with React
      • server
        • HTTP servers with Node & Express
      • typescript
        • TypeScript
    • mentoring
      • design-week
        • Analysis Workshop
        • Code planning
        • Definition Workshop
        • Discovery Workshop
        • Figma introduction
        • Usability testing
        • User Research
    • resources
      • http
        • introduction
    • workshops
      • cookie-auth
        • index
      • creating-promises
        • index
      • css-layout
        • index
      • cypress-testing
        • index
      • database-testing
        • index
      • dev-tooling
        • Developer tooling
      • dom-challenge
        • index
      • dom-rendering
        • index
      • es-modules
        • index
      • express-middleware
        • Express middleware
      • first-class-functions
        • index
      • form-validation
        • index
      • functions-callbacks-async
        • Functions, callbacks, & async JavaScript
      • git-intro
        • Introduction to Git
      • git-terminal
        • Using Git in the terminal
      • git-workflow
        • Git workflow
      • github-projects
        • GitHub Projects Workflow Workshop
      • heroku-sql-challenge
        • index
      • html-forms
        • index
      • learn-a11y
        • index
        • starter-files
          • solution
            • Accessibility solution explanation
      • learn-fetch
        • index
      • learn-integration-testing
        • index
      • learn-testing
        • Learn testing in JavaScript
      • learn-unit-testing
        • index
      • node-error-handling
        • Node error-handling
      • node-express-server
        • Node and Express HTTP server
      • node-npm-intro
        • Node & npm introduction
      • node-postgres
        • Learn Postgres with Node
      • node-scripting-challenge
        • index
      • password-security
        • index
      • promise-practice
        • index
      • react-components
        • React components
      • react-fetch
        • index
      • react-forms
        • React forms
      • react-refactor-classes
        • index
      • react-state-effects
        • React state & effects
      • real-world-fetch
        • index
      • scope-challenge
        • Scope debugging challenge
      • semantic-html
        • index
      • server-side-forms
        • Server-side forms
      • session-auth
        • Session authentication
      • sql-intro
        • index
      • tdd-array-methods
        • index
Powered by GitBook
On this page
  • Setup
  • Isolating tests
  • Resetting state between tests
  • Challenge 1: write some tests
  • Modularising database queries
  • Challenge 2: modularise queries
Export as PDF
  1. src
  2. workshops
  3. database-testing

index

Learn how to reset database state with Cypress tasks

Most interesting apps are stateful. They save information for later in some kind of database. This can make them harder to test, since each test could change the state of the app, making subsequent tests unpredictable. We're going to learn how to write tests that work with our database.

Setup

  1. Download the starter files

  2. Change into the workshop directory

  3. Create a local DB by running: ./scripts/create_db

  4. Populate the DB by running: ./scripts/populate_db

  5. Start the server with npm run dev and check it's working

There is a very minimal Cypress testing setup. You can start Cypress with npm test—you should see the single example test from cypress/integration/test.js.

Isolating tests

It's easy to create tests that rely on each other. For example imagine we had an app for managing different types of dogs. Here are two example tests; one checks that creating a dog works, and one checks that deleting a dog works:

describe("Dog forms", () => {
  it("creates a new dog", () => {
    cy.visit("/");
    cy.get("input[name='dogName']").type("rover");
    cy.get("button[type='submit']").click();
  });
  it("deletes a dog", () => {
    cy.visit("/");
    cy.get("button[aria-label='Delete rover']").click();
    // make sure that Rover entry is removed from page
    cy.contains("Rover").should("not.exist");
  });
});

These tests work fine together, but if we ran the delete test on its own it would fail. It expects there to already be a dog called "Rover" in the database, but this only happens if the creation test runs first.

This is a bad idea since it makes tests brittle. If somebody comes along later and swaps the order of the tests (or removes a test) it could break things. Tests should always be self-contained and able to run on their own.

Resetting state between tests

Cypress has a handy way to run some code before every test—the beforeEach method. You can pass this a function and Cypress will run it before each test:

describe("Dog forms", () => {
  beforeEach(() => {
    console.log("I run before every test!");
  })
  it("creates a dog", () => {...})
  it("deletes a dog", () => {...})
})

We can use this to reset our database before each new test runs.

Unfortunately there's a slight complication: all the code inside our test runs in the browser. The browser doesn't have access to our Node environment, so it can't talk to our database directly.

However Cypress provides a way to execute code in your Node environment: "tasks". These are special functions you can create and then call from inside tests. Tasks are defined inside cypress/plugins/index.js:

module.exports = (on, config) => {
  on("task", {
    resetDb: () => {
      console.log("Resetting DB...");
      // we have to return something or Cypress gets mad
      return null;
    },
  });
};

We can then call this task in our tests like this:

beforeEach(() => {
  cy.task("resetDb");
});

Try creating this task now, and make sure you can call it from inside a test. You should see the log show up in your terminal, not the test browser (remember tasks are for running Node code outside the browser).

Running the populate_db script

We already have a script that can reset our DB back to its initial state: ./scripts/populate_db. We can re-use this within our Cypress task using Node's child_process module. This allows us to run terminal commands from inside Node.

In this case we want to use the execFileSync method to execute a given file:

// cypress/plugins/index.js

const { execFileSync } = require("child_process");

module.exports = (on, config) => {
  on("task", {
    resetDb: () => {
      console.log("Resetting DB...");
      return execFileSync("./scripts/populate_db");
    },
  });
};

Challenge 1: write some tests

Now you can finally write some tests without worrying about the DB.

  1. Add a test verifying the / route displays a list of users

  2. Add a test verifying you can create a user from the /create-user route

  3. Add a test verifying you can delete a user from the / route

Modularising database queries

Currently we're querying our database directly within our route handler functions. It's generally a good idea to separate out data access. Ideally our route handlers shouldn't have to do anything more than call a function like getUsers(). That way we could e.g. swap from Postgres to a totally different DB without having to change our route handlers at all.

The nice thing about already having tests is refactoring becomes much safer. You'll find out quite quickly if you break something.

Let's extract the homepage's query. Create a new file workshop/database/model.js. Since there aren't many we'll put all our SQL queries into this one file.

Create a function named getUsers. Import your database pool object and use it to get all the users, just like before:

const db = require("./connection.js");

function getUsers() {
  return db.query("SELECT * FROM users");
}

module.exports = { getUsers };

You can now import and use this function in routes/home.js:

const model = require("../database/model.js");

function get(request, response) {
  model.getUsers().then((result) => {
    const users = result.rows;
    // ...
  });
}

This should work, but we can improve it. Our handler is stuck dealing with database-specific details. I.e. it has to know about the Postgres result.rows property. Ideally we should do this data-processing inside model.js instead:

const db = require("./connection.js");

function getUsers() {
  return db.query("SELECT * FROM users").then((result) => result.rows);
}

module.exports = { getUsers };
const model = require("../database/model.js");

function get(request, response) {
  model.getUsers().then((users) => {
    // ...
  });
}

Challenge 2: modularise queries

Move the rest of the DB queries into model.js. Make sure all the tests keep passing!

  1. Write a createUser function to insert new users

  2. Write a deleteUser function to delete a user

  3. Write a getPosts function to select all the blog posts

  4. Refactor your route handlers to use the new model functions

Previousdatabase-testingNextdev-tooling

Last updated 3 years ago