Reseed your database with Cypress

By using a Cypress task it's possible to run code JavaScript code in Node.js.
We can leverage this API to reseed a database with data we're in control of. Having control over the data allows us to write specific scenarios to run tests against.

There are other possibilities to test a specific scenario, but they all come at a cost:

• Run a previously tested scenario to set up a specific test case, this can be time-consuming to run and to write
• Expose create, update, and delete functions to be able to set up a specific test, with the consequence you end up with "backdoor" functions in an API. To be able to run specific test cases you will write more code (and have more maintenance) to be able to test your code. By exposing these functions there's a possibility they make it to production where they can become harmful if someone finds these backdoor functions
• Because it can be hard/time-consuming to test a specific scenario, some tests will not be written

Why

But what do we gain by reseeding the database?

Well, we can write isolated test cases, this has the benefit that each test case:

• is scoped to a specific test case
• can run individually
• is fast and easy to use
• is reliable

How

Creating the task

A Cypress task can be registered, the same way as a plugin.
We can listen to tasks when we bind the task event inside the plugins/index.js file.
To register multiple tasks, we pass an object of our tasks as parameter.
The property names are used to execute the task later on in our tests, and the value must be a Promise wherein the task logic lives.

module.exports = on => {
  on('task', {
    'db:teardown': () => {
      const teardown = require('../../db/teardown.js')
      return teardown()
    },
    'db:seed': () => {
      const seed = require('../../db/seed.js')
      return seed()
    },
  })
}

We aren't covering the teardown and seed scripts because these will be different based on the chosen stack.

Running the task

To run the task we can use the cy.task(taskName) method.
In our case, we want to reseed the database before running each test.
Therefore, we use the beforeEach hook inside the support/index.js file.

beforeEach(() => {
  cy.task('db:teardown')
  cy.task('db:seed')
})

Modifying the timeout time

By default, a task will timeout after one minute.
For most of the cases this will be enough, but for the cases that are taking longer it's possible to change the timeout.

To change it at a global level, change the default timeout inside cypress.json.

{
  "defaultCommandTimeout": 120000
}

Or modify the timeout value on a task level by changing the options.

cy.task('db:teardown', null, { timeout: 30000 })
cy.task('db:seed', null, { timeout: 30000 })

---

Follow me on Twitter at @tim_deschryver | Originally published on timdeschryver.dev.

Comments

[lcpautotester]

Where is your GIT examples?

It would nice to see your JS code.

i've stayed with EF , just because there is no eazy way to setup and tear down SQL data in Cypress that I was aware of

[Tim Deschryver]

The teardown is a sql script that runs (via the mssql package) :
It basically looks for all tables to delete, removes the constraints, deletes all the data, re-enables the constraints, and reseeds the identity column

module.exports = () => {
  console.log('[DATABASE TEARDOWN] Started');
  return require('./utils/run-script.js').run`
    DECLARE @statement nvarchar(MAX);
    DECLARE @statements CURSOR;
    SET @statements = CURSOR FOR
      with tables as (
        SELECT SCHEMA_NAME(schema_id) 'SchemaName', name 'TableName', '['+SCHEMA_NAME(schema_id)+'].['+name+']' 'SchemaTableName'
        FROM sys.tables
      ),
      mocktables as (
        select *
        from tables
        where SchemaName not in ('dbo', 'Foo')
      ),
      statememts as (
        select 'ALTER TABLE ' + SchemaTableName + ' NOCHECK CONSTRAINT ALL;' as statememt
        from mocktables
        union all
        select 'DELETE FROM ' + SchemaTableName + ' ;'
        from mocktables
        union all
        select 'ALTER TABLE ' + SchemaTableName + ' CHECK CONSTRAINT ALL;'
        from mocktables
        union all
        select 'DBCC CHECKIDENT ( ''' + SchemaTableName + ''', RESEED, 0) WITH NO_INFOMSGS;'
        from mocktables
      )
    SELECT *
    FROM statememts
    OPTION (MAXRECURSION 0)
    OPEN @statements
    FETCH next FROM @statements INTO @statement
    WHILE @@fetch_status = 0
    BEGIN
        exec sp_executesql  @statement
        FETCH next FROM @statements INTO @statement
    END;
`.then(() => {
    console.log('[DATABASE TEARDOWN] Ended');
    return true;
  });
};

[rider]

Hello, I've been working on some e2e testing based in typescript and using cypress. I really like how you've taken the approach to this, as SQL data is hard to manage in cypress. Is there any way I could see how you performed your seed task? Did you use hardcoded json or yaml data as a fixture and pass that in to the server?

[Tim Deschryver]

The data is created with JS, but is just a JSON.
We did it with JS, so we could use some helper functions.
In short, we create a collection of JS objects which structure is the same as our SQL tables.
Afterward, we generate insert statements from these objects.