I have a node app connecting to a MySQL database that I want to write end to end tests for.

This means I need some test data in the database but I don't want tests from ./test-1.test.js to effect tests in ./test-2.test.js.

For example my first test deletes a record:

test-1.test.js

it('Should delete record', async () => {
  await supertest(server)
    .delete(`/record/id_1`)
    .expect(OK, expectedData);
})

and then have my second test try to consume the record that was deleted in the previous test

it('Should retrieve record', async () => {
  await supertest(server)
    .get(`/record/id_1`)
    .expect(OK, expectedData);
})

Potential solutions:

1. I was considering either running a seed before I start my test. This would require a lot of variations of data and would not be clear as to what data is being used by what tests, potentially leading to flakey and unreliable tests if the order of tests gets moved around or one test file is run before the other.

2. Populate the data in a beforeEach or beforeAll block using my models. i.e.

beforeEach(async () => {
  await MyModel.create(myTestData)
});

afterEach(async () => {
  await MyModel.drop() // drops table - would need to run migrations before I run the next test
});

it('Should retrieve record', async () => {
  await supertest(server)
    .get(`/record/id_1`)
    .expect(OK, expectedData);
});

If I drop a table after each or all tests, is there a way to then re run my migration so that the table is pushed back into the schema? Rather than dropping the table, am I best to just delete the record specifically? Seems like this is creating a lot of logic in my tests though.

I'm open to suggestions. Keen to hear how you handle your test data / seeds in your e2e tests too