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!

  • Is this a theoretical question or are you looking to test a new database implementation? The reason I ask is that I'd give a different response to a theoretical question than a practical one.
    – Kate Paulk
    Commented May 28, 2014 at 11:00
  • I am trying to write tests for our application's data layer, which should, to the user code, function pretty much like a database. Commented May 28, 2014 at 17:45

2 Answers 2


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.

  • How about combinations of operations? For example, DELETEing something after an UPDATE or SELECTing something after a DELETE? Is that a sensible approach to testing? Commented May 29, 2014 at 2:31
  • Yes, it is. As I said, the exact tests you use and the method depends on how your application handles the actions.
    – Kate Paulk
    Commented May 29, 2014 at 11:07
  • Kate - Your last sentence is interesting. One question would be whether it is more useful to test the actual scenarios in the application and take "before" and "after" snapshots of the resulting data. Getting into the atomic operations may be more of a unit test in the dev environment.
    – Jeff_Lucas
    Commented Jun 2, 2014 at 17:18
  • @Jeff_Lucas - I'm inclined to agree about the atomic operations. From what the OP has said, the main concern is that a call to the data layer has the correct output to/from the database. The most effective tests for that are going to depend on how atomic the operations performed by the data layer are: it's not unheard of for a data layer to run multi-table joins or other more complex database operations. I've worked with the before/after snapshots a lot for that reason.
    – Kate Paulk
    Commented Jun 2, 2014 at 18:40

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.