Proper pattern for setting up tests of basic database operations

Question

Trying to write tests for basic database operation, I'd like to understand what is the standard or proper pattern for setting up the data, so that only the functionality that should be tested is tested.

For example:

Trying to test INSERT. To test INSERT, the data should not be in the database, then the insert operation should be performed, and finally it should be retrieved to make sure that it did get there. However, it seems like this test depends on other functions to work well (i.e. SELECT), which could be problematic.

How far should the assumption that auxiliary functions work go? Should this be somehow delegated to a set-up function? What is the standard procedure for testing functionality like this?

Thanks very much!

Answer 1

Based on your response to my comment, you're not actually looking to test database operations but an application that employs the operations. That makes things a little simpler.

If you have access to the data store - the simplest method you can use is to perform an operation with your application and treat the data store as an oracle for verification purposes.

Some examples: - INSERT - use the application to insert some known data set. Use the database's internal toolset to validate that the data insert occurred. - SELECT - use the database's internal toolset to view a set of data. Use the application to validate that the correct data is returned.

If you want to isolate these some more, you can take a simple external data source like a CSV file and store the data you're checking in that, and use the CSV file as your oracle as to what should or should not be in the database at any point in time (this works best for small data sets).

On the other hand, if you have no concerns about the ability of the application to save data to any given data store, you can redirect the data store to a repository of your choice (e.g. instead of saving the INSERT to the database, it writes to a CSV file, which you can read and check)

Exactly what you test and how you test it depends entirely on what, exactly you need to test.

Answer 2

In Technical Perspective

• Working code is better than anything. You can have a working solution and re-factor it going forward
• Do you have any solution options ? What Challenges do you have implementing it?

Solution perspective Options

• Operations would be CRUD (Create, Read, Update Delete). For each one you need to have the previous state setup and available for you to run your tests.
• Consider the functional flow and setup the DB with prerequisites setup. Take a snapshot of the same, Run your tests and validate the results
• Alternate approach process data everytime.

More reads This link was useful on test data design

• Test Data Map Design Pattern - http://www.genrocket.com/test-data-map-design-pattern/
• Test Data Loader Design Pattern - http://www.genrocket.com/test-data-loader-design-pattern/