Running db migrations before integration tests

Question

Suppose I have a web app consisting of an API and a db.

I also have a bunch of db migrations. Some of them update tables structure, while others also update the data. For instance, in migration_5 I do the following:

1. Query the db for user ids whose rating is lower than 2.
2. Update some other tables based on these ids.

Now, I want to set up integration tests. From what I understand, before running tests I should do the following:

1. Have a clean empty db
2. Run all migrations so the db structure (tables, columns, etc.) is in the relevant state

And here's what I can't understand: in the described scenario the migration_5 will fail because it relies on the presence of some data in db (the registered users). As the db is empty, the attempt to query the db for user ids in migration will return nothing, hence the attempt to update other tables will throw an error, causing the migration to fail.

So how am I supposed to run such migrations on an empty db? What am I missing? I feel like I don't understand some very basic things

Answer 1

The general concept still holds true. Run your migrations first, then run your tests. You should be able to programmatically recreate an environment from source.

It sounds like your migration_5 is missing some conditional logic. For example, a DROP TABLE would check if the table exists before performing the operation. If your migration depends on data, modify the migration to check for the data first:

IF EXISTS (SELECT …)
   …
END

Regarding database migrations on an empty database, a common approach is to seed the database with required data post-migration. For example, some applications require reference data to be present for normal operation.

In terms of integration testing, depending on the nature and complexity of your testing scenarios, you may want to further seed the database with sample data that is required for testing. Some teams maintain a golden copy or a sanitized set of data from production that can be loaded into the database.

Some automated functional and integration tests populate the database with each test and then remove the data at the end of the test run. Some teams go so far as to start a transaction before the test or test run and then roll it back at the end.